poi的基础用法

个人使用Poi的记录

依赖：

<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>4.0.1</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>4.0.1</version>
	</dependency>

Collertor层的方法:
其中的数据来源需要自己改

public ActionForward toExcel(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
        DynaBean dynaBean = (DynaBean) form;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        OutputStream out=null;
        EntpOrderSettlement entpOrderSettlement = new EntpOrderSettlement();
        entpOrderSettlement.getMap().put("payExport","true");
        List<EntpOrderSettlement> entpOrderSettlement1 = super.getFacadeWhq().getEntpOrderSettlementService().findEntpOrderSettlement(entpOrderSettlement);
        String excelName="xxx";
        String fileName=excelName+ DateFormatUtils.format(new Date(),"yyyyMMdd")+".xls";
        fileName=new String(fileName.getBytes("utf-8"),"iso8859-1");
        String[] title = {"店铺名称","金额","文惠劵金额","开始时间","结束时间","提交时间","状态"};
        String[][] values = new String[entpOrderSettlement1.size()][title.length];
        String[] params = new String[title.length];
        for (int i = 0; i < entpOrderSettlement1.size(); i++) {
            EntpOrderSettlement entpOrderSettlement2 = entpOrderSettlement1.get(i);
            String entpName1 = (String) entpOrderSettlement2.getMap().get("entpName");
            BigDecimal product_money = entpOrderSettlement2.getProduct_money();
            BigDecimal coupon_money = entpOrderSettlement2.getCoupon_money();
            Date start_time = entpOrderSettlement2.getStart_time();
            Date end_time = entpOrderSettlement2.getEnd_time();
            Date create_time = entpOrderSettlement2.getCreate_time();
            Integer status = entpOrderSettlement2.getStatus();

            params[0]=entpName1;
            params[1]=String.valueOf(product_money);
            params[2]=String.valueOf(coupon_money);
            params[3]=simpleDateFormat.format(start_time);
            params[4]=simpleDateFormat.format(end_time);
            params[5]=simpleDateFormat.format(create_time);
            if(status==3){
                params[6]="待拨付";
            }
            if(status==4){
                params[6]="已拨付";
            }
            if(status==5){
                params[6]="完成拨付";
            }
            for (int j = 0; j < title.length; j++) {
                values[i][j]=params[j];
            }
        }
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

        Workbook workbook = ExcelExportUtilUpgrade.getWorkbook(excelName, title, values, hssfWorkbook);
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setContentType("application/x-download");
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
        response.flushBuffer();
        out = response.getOutputStream();
        workbook.write(out);
        out.flush();
        return null;
    }

生成Workbook的工具类：
有待改进

public static Workbook getWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook workbook) {
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //在workbook中添加一个sheet,对应Excel文件中的sheet
        Sheet sheet = workbook.createSheet(sheetName);

        Row row = sheet.createRow(0);
        for (int i = 0; i < title.length; i++) {
            Cell cell = row.createCell(i);
        }
        //创建一个单元格合并的对象
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, title.length-1);
        sheet.addMergedRegion(cellRangeAddress);
        //设置合并单元格的边框，并且设置居中
        RegionUtil.setBorderBottom(1, cellRangeAddress, sheet, workbook);
        RegionUtil.setBorderLeft(1, cellRangeAddress, sheet, workbook);
        RegionUtil.setBorderRight(1, cellRangeAddress, sheet, workbook);
        RegionUtil.setBorderTop(1, cellRangeAddress, sheet, workbook);
        row.getCell(0).setCellValue(sheetName);
        //设置合并单元格样式
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        Font font = workbook.createFont();
        font.setFontName("黑体");
        font.setBoldweight((short) 5);
        font.setFontHeightInPoints((short) 20);
        style.setFont(font);
        row.getCell(0).setCellStyle(style);

        //设置表头单元格样式
        CellStyle style2 = workbook.createCellStyle();
        style2.setAlignment(CellStyle.ALIGN_CENTER);
        style2.setBorderBottom((short) 1);
        style2.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style2.setBorderLeft((short) 1);
        style2.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style2.setBorderRight((short) 1);
        style2.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style2.setBorderTop((short) 1);
        style2.setTopBorderColor(IndexedColors.BLACK.getIndex());
        //背景色
        style2.setFillForegroundColor(HSSFColor.LIME.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        Font font2 = workbook.createFont();
        font2.setFontName("微软雅黑");
        font2.setFontHeightInPoints((short) 14);
        style2.setFont(font2);
        //拿到palette颜色板
        HSSFPalette customPalette = workbook.getCustomPalette();
        //这个是重点，具体的就是把之前的颜色 HSSFColor.LIME.index
        //替换为  RGB(51,204,204) 宝石蓝这种颜色
        //你可以改为 RGB(0,255,127)226,107,10
        customPalette.setColorAtIndex(HSSFColor.LIME.index, (byte) 226, (byte) 107, (byte) 10);

        //设置数据单元格样式
        CellStyle style3 = workbook.createCellStyle();
        style3.setAlignment(CellStyle.ALIGN_CENTER);
        style3.setBorderBottom((short) 1);
        style3.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style3.setBorderLeft((short) 1);
        style3.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style3.setBorderRight((short) 1);
        style3.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style3.setBorderTop((short) 1);
        style3.setTopBorderColor(IndexedColors.BLACK.getIndex());
        Font font3 = workbook.createFont();
        font3.setFontName("微软雅黑");
        font3.setFontHeightInPoints((short) 12);
        style3.setFont(font3);

        //设置表头
        Row row1 = sheet.createRow(1);
        for (int i = 0; i < title.length; i++) {
            Cell cell = row1.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style2);
        }
        //设置数据
        for (int i = 0; i < values.length; i++) {
            Row row2 = sheet.createRow(i + 2);
            for (int j = 0; j < values[0].length; j++) {
                Cell cell = row2.createCell(j);
                cell.setCellValue(values[i][j]);
                cell.setCellStyle(style3);
            }
        }
        //设置自动调整列宽
        for (int i = 0; i < title.length; i++) {
            sheet.autoSizeColumn((short)i);
        }
        return workbook;
    }

读取Excel文件中的数据到程序中：
2.2.1 主程序入口类代码：

/**
 * Author: Dreamer-1
 * Date: 2019-03-01
 * Time: 10:13
 * Description: 示例程序入口类
 */
public class MainTest {

    public static void main(String[] args) {
        // 设定Excel文件所在路径
        String excelFileName = "/Users/Dreamer-1/Desktop/myBlog/java解析Excel/readExample.xlsx";
        // 读取Excel文件内容
        List<ExcelDataVO> readResult = ExcelReader.readExcel(excelFileName);
        
        // todo 进行业务操作
    }

}

读取和写入时封装每一“行”数据的ExcelDataVO.java代码如下：

/**
 * Author: Dreamer-1
 * Date: 2019-03-01
 * Time: 11:33
 * Description: 读取Excel时，封装读取的每一行的数据
 */
public class ExcelDataVO {

    /**
     * 姓名
     */
    private String name;

    /**
     * 年龄
     */
    private Integer age;

    /**
     * 居住地
     */
    private String location;

    /**
     * 职业
     */
    private String job;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }
}

2.2.2 Excel解析类的代码：

/**
 * Author: Dreamer-1
 * Date: 2019-03-01
 * Time: 10:21
 * Description: 读取Excel内容
 */

public class ExcelReader {

    private static Logger logger = Logger.getLogger(ExcelReader.class.getName()); // 日志打印类

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     * @param inputStream 读取文件的输入流
     * @param fileType 文件后缀名类型（xls或xlsx）
     * @return 包含文件数据的工作簿对象
     * @throws IOException
     */
    public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

/**
 * 读取Excel文件内容
 * @param fileName 要读取的Excel文件所在路径
 * @return 读取结果列表，读取失败时返回null
 */
public static List<ExcelDataVO> readExcel(String fileName) {

    Workbook workbook = null;
    FileInputStream inputStream = null;

    try {
        // 获取Excel后缀名
        String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
        // 获取Excel文件
        File excelFile = new File(fileName);
        if (!excelFile.exists()) {
            logger.warning("指定的Excel文件不存在！");
            return null;
        }

        // 获取Excel工作簿
        inputStream = new FileInputStream(excelFile);
        workbook = getWorkbook(inputStream, fileType);

        // 读取excel中的数据
        List<ExcelDataVO> resultDataList = parseExcel(workbook);

        return resultDataList;
    } catch (Exception e) {
        logger.warning("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
        return null;
    } finally {
        try {
            if (null != workbook) {
                workbook.close();
            }
            if (null != inputStream) {
                inputStream.close();
            }
        } catch (Exception e) {
            logger.warning("关闭数据流出错！错误信息：" + e.getMessage());
            return null;
        }
    }
}

 /**
     * 解析Excel数据
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    private static List<ExcelDataVO> parseExcel(Workbook workbook) {
       List<ExcelDataVO> resultDataList = new ArrayList<>();
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }

            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                logger.warning("解析Excel失败，在第一行没有读取到任何数据！");
            }

            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 1;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);

                if (null == row) {
                    continue;
                }

                ExcelDataVO resultData = convertRowToData(row);
                if (null == resultData) {
                    logger.warning("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                    continue;
                }
                resultDataList.add(resultData);
            }
              }

                return resultDataList;
            }

/**
 * 将单元格内容转换为字符串
 * @param cell
 * @return
 */
private static String convertCellValueToString(Cell cell) {
    if(cell==null){
        return null;
    }
    String returnValue = null;
    switch (cell.getCellType()) {
        case NUMERIC:   //数字
            Double doubleValue = cell.getNumericCellValue();

            // 格式化科学计数法，取一位整数
            DecimalFormat df = new DecimalFormat("0");
            returnValue = df.format(doubleValue);
            break;
        case STRING:    //字符串
            returnValue = cell.getStringCellValue();
            break;
        case BOOLEAN:   //布尔
            Boolean booleanValue = cell.getBooleanCellValue();
            returnValue = booleanValue.toString();
            break;
        case BLANK:     // 空值
            break;
        case FORMULA:   // 公式
            returnValue = cell.getCellFormula();
            break;
        case ERROR:     // 故障
            break;
        default:
            break;
             }
              return Value;
       }
       

    /**
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     *
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     */
    private static ExcelDataVO convertRowToData(Row row) {
        ExcelDataVO resultData = new ExcelDataVO();

        Cell cell;
        int cellNum = 0;
        // 获取姓名
        cell = row.getCell(cellNum++);
        String name = convertCellValueToString(cell);
        resultData.setName(name);
        // 获取年龄
        cell = row.getCell(cellNum++);
        String ageStr = convertCellValueToString(cell);
        if (null == ageStr || "".equals(ageStr)) {
            // 年龄为空
            resultData.setAge(null);
        } else {
            resultData.setAge(Integer.parseInt(ageStr));
        }
        // 获取居住地
        cell = row.getCell(cellNum++);
        String location = convertCellValueToString(cell);
        resultData.setLocation(location);
        // 获取职业
        cell = row.getCell(cellNum++);
        String job = convertCellValueToString(cell);
        resultData.setJob(job);

        return resultData;
    }
}

2.2.3 应用场景补充
一般我们会有这样的应用场景，即：在前台页面的文件上传入口上传本地的Excel文件到后台，后台收到Excel文件后进行解析并做对应的业务操作；

这里假设前台已经有了上传文件的入口，再简单贴一下后台的解析代码；

后台接收前台数据的Controller层代码示例：

@PostMapping("/uploadExcel")
public ResponseEntity<?> uploadImage(MultipartFile file) {

    // 检查前台数据合法性
    if (null == file || file.isEmpty()) {
        logger.warning("上传的Excel商品数据文件为空！上传时间：" + new Date());
        return new ResponseEntity<>(HttpStatus.BAD_REQUEST);
    }

    try {
        // 解析Excel
        List<ExcelDataVO> parsedResult = ExcelReader.readExcel(file);
        // todo 进行业务操作

        return new ResponseEntity<>(HttpStatus.OK);
    } catch (Exception e) {
        logger.warning("上传的Excel商品数据文件为空！上传时间：" + new Date());
        return new ResponseEntity<>(HttpStatus.BAD_REQUEST);
    }

}

ExcelReader.java中的 readExcel() 方法需要做一定的修改，代码如下：

/**
 * 读取Excel文件内容
 * @param file 上传的Excel文件
 * @return 读取结果列表，读取失败时返回null
 */
public static List<ExcelDataVO> readExcel(MultipartFile file) {

    Workbook workbook = null;

    try {
        // 获取Excel后缀名
        String fileName = file.getOriginalFilename();
        if (fileName == null || fileName.isEmpty() || fileName.lastIndexOf(".") < 0) {
            logger.warning("解析Excel失败，因为获取到的Excel文件名非法！");
            return null;
        }
        String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());

        // 获取Excel工作簿
        workbook = getWorkbook(file.getInputStream(), fileType);

        // 读取excel中的数据
        List<ExcelDataVO> resultDataList = parseExcel(workbook);

        return resultDataList;
    } catch (Exception e) {
        logger.warning("解析Excel失败，文件名：" + file.getOriginalFilename() + " 错误信息：" + e.getMessage());
        return null;
    } finally {
        try {
            if (null != workbook) {
                workbook.close();
            }
        } catch (Exception e) {
            logger.warning("关闭数据流出错！错误信息：" + e.getMessage());
            return null;
        }
    }
}