Excel - POI与EasyExcel - 《Java 学习笔记》

基本功能

官网:https://yuque.com/easyexcel
狂神学习视频:https://www.bilibili.com/video/BV1Ua4y1x7BK?from=search&seid=282810424102829485&spm_id_from=333.337.0.0e

基本功能

HSSF Excel 2003 限制行数65535
XSSF Excel OOXML 2017 不限制行数
HWPF Word
HSLF PowerPoint
HDGF Visio

POI 先加载到内存 EasyExcel 会一行一行的写时间换空间
工作簿: _Workbook _workbook = _new _XSSFWorkbook(); //07
工作表: _Sheet _sheet = workbook.createSheet(“豪”);
行: _Row _row = sheet.createRow(0);
列: _Cell _cell1 = row.createCell(0);//单元格

Excel03

@Test
    public void testWrite03() throws Exception {
        //1.创建工作簿
        Workbook workbook = new HSSFWorkbook();
        //2.创建工作表
        Sheet sheet = workbook.createSheet("豪");
        //3.创建行
        Row row = sheet.createRow(0);
        //4.创建单元格
        Cell cell1 = row.createCell(0);
        cell1.setCellValue("今日新增观众");
        Cell cell2 = row.createCell(1);
        cell2.setCellValue(666);
        // 第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");
        Cell cell22 = row2.createCell(1);
        String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(s);
        //生成一张表(io流)  03版本后缀是xls结尾
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "统计表03.xls");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("豪 03生成完毕");
    }

Excel07

@Test
    public void testWrite07() throws Exception {
        //1.创建工作簿
        Workbook workbook = new XSSFWorkbook();
        //2.创建工作表
        Sheet sheet = workbook.createSheet("豪");
        //3.创建行
        Row row = sheet.createRow(0);
        //4.创建单元格
        Cell cell1 = row.createCell(0);
        cell1.setCellValue("今日新增观众");
        Cell cell2 = row.createCell(1);
        cell2.setCellValue(666);
        // 第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");
        Cell cell22 = row2.createCell(1);
        String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(s);
        //生成一张表(io流)  07版本后缀是xlsx结尾
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "统计表07.xlsx");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("豪 07生成完毕");
    }

Excel03和Excel07区别创建工作簿用的实体类不同后缀不同
03 _Workbook _Workbook = _new _HSSFWorkbook(); (不能超过65536行)
07 _Workbook _Workbook = _new _XSSFWorkbook(); (慢占用内存高)
_Workbook _workbook = _new _SXSSFWorkbook();(快能超过65536行有可能占用内存过高需清理临时文件((SXSSFWorkbook) workbook).dispose();)

读取不同类型

@Test
    public void testCellType() throws Exception {
        FileInputStream fileInputStream = new FileInputStream(PATH + "统计表03.xls");
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        //获取标题内容
        Row row = sheet.getRow(0);
        if (row != null) {
            int cellCount = row.getPhysicalNumberOfCells();
            for (int i = 0; i < cellCount; i++) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    int cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + " | ");
                }
            }
            System.out.println();
        }
        //获取表中的内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int i = 0; i < rowCount; i++) {
            Row sheetRow = sheet.getRow(i);
            if (sheetRow != null) {
                int cellCount = sheetRow.getPhysicalNumberOfCells();
                for (int j = 0; j < cellCount; j++) {
                    System.out.print("[" + (i + 1) + "-" + (j + 1) + "]");
                    //匹配列的数据类型
                    Cell cell = sheetRow.getCell(j);
                    String s = cell.toString();
                    if (cell != null) {
                        int cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING: //字符串
                                System.out.print("[String]");
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔
                                System.out.print("[boolean]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK: // 空
                                System.out.print("[blank]");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                                System.out.print("[numeric]");
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {   //日期
                                    System.out.print("日期");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                } else {
                                    //如果不是日期
                                    System.out.print("[转换为字符串输出]");
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR: // 数据类型错误
                                System.out.print("[数据类型错误]");
                                break;
                            default:
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        fileInputStream.close();
    }

注意:类型转换问题

计算公式

@Test
    public void testFormula() throws Exception{
        FileInputStream fileInputStream = new FileInputStream(PATH + "公式.xls");
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);
        //拿到计算公式
        FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        //输出单元格内容
        int cellType = cell.getCellType();
        switch (cellType){
            case HSSFCell.CELL_TYPE_FORMULA:
                String formula = cell.getCellFormula();
                System.out.println(formula);
                //计算
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String s = evaluate.formatAsString();
                System.out.println(s);
                break;
            default:
        }
    }

数字转换性别

public class SexConverter implements Converter<Integer> {
    @Override
    public Class supportJavaTypeKey() {
        return Integer.class;
    }
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    @Override
    public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return "男".equals(cellData.getStringValue()) ? 1 : 0;
    }
    @Override
    public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new WriteCellData<>(value.equals(1) ? "男" : "女");
    }
}

让converter生效

@ExcelProperty(value = "订单状态",index = 11,converter = OrderRefundStatusConverter.class)
private Integer refundStatus;