Java中Excel处理

ゝ一纸荒年。 2022-11-30 04:11 334阅读 0赞

# Java中Excel处理 #

> 本文记录Java中常用的Excel类库，以便查用。

## EasyExcel ##

快速、简单避免OOM的java处理Excel工具。

[https://github.com/alibaba/easyexcel][https_github.com_alibaba_easyexcel]

https://mvnrepository.com/artifact/com.alibaba/easyexcel

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.2.6</version>
    </dependency>

### 使用 ###

#### 使用基础数据类型 ####

List<String> head = Lists.newArrayList("日期", "价格");
    List<List<Object>> data = Lists.newArrayList(
        Lists.newArrayList("2020-09-24", 100),
        Lists.newArrayList("2020-09-25", 110),
        Lists.newArrayList("2020-09-26", 120),
        Lists.newArrayList("2020-09-27", 90));
    
    File outFile = new File(FileSystemView.getFileSystemView().getHomeDirectory(), "价格趋势.xlsx");
    //写Excel
    EasyExcelFactory.write(outFile).sheet().head(head.stream().map(Collections::singletonList).collect(Collectors.toList())).doWrite(data);
    
    //读Excel 同步读取 可以改变ReadListener实现异步读取避免OOM
    List<LinkedHashMap<Integer, Object>> mapList = EasyExcel.read(outFile, new SyncReadListener()).doReadAllSync();
    //[{0:"2020-08-23 12:07:56",1:"12"},{0:"2020-08-22 12:07:56",1:"41"}]

#### 使用Java实体对象 ####

//定义实体对象
    public class OrderDTO { 
        @ExcelProperty("订单号")
        private String orderId;
        
        @ExcelProperty("买家名称")
        private String buyerName;
        
        @ExcelProperty("订单总额")
        @NumberFormat(",##0.00")
        private BigDecimal orderAmount;
        
        @ExcelProperty("创建时间")
        @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
        private Date createTime;
        
        //constructer getter setter 略
    }

List<OrderDTO> orderDTOS = Lists.newArrayList(
        new OrderDTO("202001010005", "超级买家", new BigDecimal("120019.001"), new Date()),
        new OrderDTO("202001010006", "一般买家", new BigDecimal("5421487.45"), new Date()));
    
    File outFile = new File(FileSystemView.getFileSystemView().getHomeDirectory(), "订单数据.xlsx");
    //写Excel
    EasyExcel.write(outFile).sheet().head(OrderDTO.class).doWrite(orderDTOS);
    
    //读Excel
    List<OrderDTO> objects = EasyExcel.read(outFile, OrderDTO.class, new SyncReadListener()).doReadAllSync();
    //[{"buyerName":"超级买家","createTime":1598155887000,"orderAmount":120019.0,"orderId":"202001010005"},
    //{"buyerName":"一般买家","createTime":1598155887000,"orderAmount":5421487.45,"orderId":"202001010006"}]

### 说明 ###

EasyExcel是一个快速的能够避免OOM的Excel类库，支持使用基础数据类型直接读写Excel，直接Java对象映射Excel文档读写。支持同步/异步读写。

## XXL-TOOL ##

一个灵活的Java对象和Excel文档相互转换的工具。一行代码完成Java对象和Excel文档之间的转换。同时保证性能和稳定。

[https://www.xuxueli.com/xxl-tool/][https_www.xuxueli.com_xxl-tool]

[https://mvnrepository.com/artifact/com.xuxueli/xxl-tool][https_mvnrepository.com_artifact_com.xuxueli_xxl-tool]

<dependency>
        <groupId>com.xuxueli</groupId>
        <artifactId>xxl-tool</artifactId>
        <version>1.2.0</version>
    </dependency>

### 使用 ###

//定义Java实体对象
    @ExcelSheet(name = "商户列表", headColor = HSSFColor.HSSFColorPredefined.LIGHT_GREEN)
    public class ShopDTO { 
        
        @ExcelField(name = "商户ID")
        private int shopId;
        
        @ExcelField(name = "商户名称")
        private String shopName;
        
        //constructer getter setter 略
    }

List<ShopDTO> sheet1Data = new ArrayList<>();
    sheet1Data.add(new ShopDTO(1, "一号商户"));
    sheet1Data.add(new ShopDTO(2, "二号商户"));
    //写Excel文件
    ExcelTool.exportToFile(Collections.singletonList(sheet1Data), "商户数据.xlsx");
    //读Excel文件
    List<Object> objects = ExcelTool.importExcel("商户数据.xlsx", ShopDTO.class);

### 说明 ###

XXL-TOOL中的Excel模块是一个`简单的`快速Java对象和Excel文件互相转换工具，使用它必须要定义一个**Java实体对象**来映射**Excel文件**，不支持直接使用基础数据类型读写Excel文件，但能够同时导出多个sheet。

Excel模块代码简单，以一个`ExcelTool`提供了几个静态方法调用，并提供`ExcelSheet`、`ExcelField`两个注解来注解Java实体对象信息。

## Hutool ##

针对POI中Excel和Word的封装

[https://hutool.cn/docs/\#/poi/概述][https_hutool.cn_docs_poi]

[https://mvnrepository.com/artifact/cn.hutool/hutool-all][https_mvnrepository.com_artifact_cn.hutool_hutool-all]

[https://mvnrepository.com/artifact/cn.hutool/hutool-poi][https_mvnrepository.com_artifact_cn.hutool_hutool-poi]

<dependency>
        <groupId>cn.hutool</groupId>
        <artifactId>hutool-all</artifactId>
        <version>5.7.13</version>
    </dependency>

<dependency>
        <groupId>cn.hutool</groupId>
        <artifactId>hutool-poi</artifactId>
        <version>5.7.13</version>
    </dependency>

可以单独引入`hutool-poi`或引入`hutool-all`

### 使用 ###

#### 使用基础数据类型 ####

File outFile = new File(FileSystemView.getFileSystemView().getHomeDirectory(), "价格趋势.xlsx");
    List<String> header = Lists.newArrayList("日期", "价格");
    List<List<Object>> data = Lists.newArrayList(
        Lists.newArrayList("2020-09-24", 100),
        Lists.newArrayList("2020-09-25", 110),
        Lists.newArrayList("2020-09-26", 120),
        Lists.newArrayList("2020-09-27", 90));
    
    ExcelWriter writer = ExcelUtil.getWriter(outFile);
    writer.writeHeadRow(header);
    writer.write(data);
    //设置列宽自适应，必须在写出数据行后调用才有效果
    writer.autoSizeColumnAll();
    writer.close();
    
    ExcelReader reader = ExcelUtil.getReader(outFile);
    List<List<Object>> read = reader.read();
    //[[日期, 价格], [2020-09-24, 100], [2020-09-25, 110], [2020-09-26, 120], [2020-09-27, 90]]
    List<List<Object>> read1 = reader.read(1);
    //[[2020-09-24, 100], [2020-09-25, 110], [2020-09-26, 120], [2020-09-27, 90]]
    List<Map<String, Object>> maps = reader.readAll();
    //[{日期=2020-09-24, 价格=100}, {日期=2020-09-25, 价格=110}, {日期=2020-09-26, 价格=120}, {日期=2020-09-27, 价格=90}]

### 使用Java实体对象 ###

### 说明 ###

## Apache POI ##

Apache POI提供了API给Java程序对Microsoft Office（Excel、WORD、PowerPoint、Visio等）格式档案读和写的功能。

[https://mvnrepository.com/artifact/org.apache.poi/poi][https_mvnrepository.com_artifact_org.apache.poi_poi]

[https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml][https_mvnrepository.com_artifact_org.apache.poi_poi-ooxml]

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

POI中有几个关键的对象:`Workbook`、`Sheet`、 `Row`和`Cell`。其中**Workbook**有3个常用实现：

`HSSFWorkbook`：支持Excel2003及以前版本，扩展名是.xls

> HSSFWorkbook是最常见的方式，最多支持行数为65535行，超过65535行后会报错

`XSSFWorkbook`：支持Excel2007以及更高版本，扩展名是.xlsx

> XSSFWorkbook可以突破65535行的限制，但是有OOM问题，原因是创建的Workbook、Sheet、 Row和Cell保存在内存中并没有持久化

`SXSSFWorkbook`：支持Excel2007以及更高版本，扩展名是.xlsx

> 从POI 3.8开始，提供SXSSFWorkbook方式，不会发生OOM问题， 原理是会将数据缓存到硬盘中

### 使用 ###

List<String> head = new ArrayList<>();
    Collections.addAll(head, "日期", "价格");
    List<List<Object>> data = new ArrayList<>();
    for (int i = 0; i < 10; i++) { 
        List<Object> row = new ArrayList<>();
        row.add(new Date(System.currentTimeMillis() - TimeUnit.DAYS.toMillis(i)));
        row.add(new Random().nextInt(100));
        data.add(row);
    }
    
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    SXSSFSheet sheet1 = workbook.createSheet("工作表1");
    //写标题行
    SXSSFRow headRow = sheet1.createRow(0);
    for (int i = 0; i < head.size(); i++) { 
        headRow.createCell(i).setCellValue(head.get(i));
    }
    //写数据行
    for (int i = 0; i < data.size(); i++) { 
        SXSSFRow row = sheet1.createRow(i + 1);
        for (int j = 0; j < data.get(i).size(); j++) { 
            Object o = data.get(i).get(j);
            if (o instanceof Date) { 
                row.createCell(j).setCellValue((Date) o);
            }
            if (o instanceof Integer) { 
                row.createCell(j).setCellValue((Integer) o);
            }
        }
    }
    //自定样式
    Font font = workbook.createFont();
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setFont(font);
    //...
    
    try { 
        File file = new File(FileSystemView.getFileSystemView().getHomeDirectory(), "价格趋势.xlsx");
        workbook.write(new FileOutputStream(file));
    } catch (IOException e) { 
        e.printStackTrace();
    }

单元格格式

//设置单元格格式为日期类型
    CellStyle dateCellStyle = workbook.createCellStyle();
    dateCellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("yyyy-MM-dd"));
    
    //设置单元格格式为百分比
    CellStyle percentageCellStyle = workbook.createCellStyle();
    percentageCellStyle.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));

列宽

//POI中Sheet列宽是通过字符个数来确定的，列宽单位为一个字符宽度的1/256 
    //每列可以显示的最大字符数为255 
    sheet.setColumnWidth(0, 12 * 256);

### 说明 ###

Apache POI能够很细粒度的操作Excel，可以自定丰富的样式、图表（折线图、饼状图和柱状图）、支持公式等功能，基本上Microsoft Excel软件能做到的POI都能做到。

### 折线图 ###

List<String> head = new ArrayList<>();
    Collections.addAll(head, "日期", "价格", "销量");
    List<List<Object>> data = new ArrayList<>();
    for (int i = 0; i < 10; i++) { 
        List<Object> row = new ArrayList<>();
        row.add(new Date(System.currentTimeMillis() - TimeUnit.DAYS.toMillis(i)));
        row.add(new Random().nextInt(100));
        row.add(new Random().nextInt(1000));
        data.add(row);
    }
    
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("工作表1");
    //写标题行
    XSSFRow headRow = sheet.createRow(0);
    for (int i = 0; i < head.size(); i++) { 
        headRow.createCell(i).setCellValue(head.get(i));
    }
    Font font = workbook.createFont();
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setFont(font);
    
    //写数据行
    for (int i = 0; i < data.size(); i++) { 
        XSSFRow row = sheet.createRow(i + 1);
        for (int j = 0; j < data.get(i).size(); j++) { 
            Object o = data.get(i).get(j);
            if (o instanceof Date) { 
                row.createCell(j).setCellValue((Date) o);
            }
            if (o instanceof Integer) { 
                row.createCell(j).setCellValue((Integer) o);
            }
        }
    }
    
    XSSFDrawing drawingPatriarch = sheet.createDrawingPatriarch();
    //折线图画图区域 两个dx 不知干嘛 row1:图顶部线位置 row2:图底部线位置 col1:图左边线位置 col2:图右边线位置
    //col row指的是和这些行列的偏移量 改变列宽 行高 图会保持距离
    XSSFChart chart = drawingPatriarch.createChart(drawingPatriarch.createAnchor(0, 0, 0, 0, 4, 1, 14, 24));
    
    //图例
    XDDFChartLegend legend = chart.getOrAddLegend();
    legend.setPosition(LegendPosition.TOP);
    
    //底部X轴
    XDDFCategoryAxis xAxis = chart.createCategoryAxis(AxisPosition.TOP);
    
    
    //左侧Y轴
    XDDFValueAxis yAxis = chart.createValueAxis(AxisPosition.LEFT);
    yAxis.setTitle("价格");
    yAxis.setCrosses(AxisCrosses.AUTO_ZERO);
    //设置y轴主要网格线
    XDDFShapeProperties orAddMajorGridProperties = yAxis.getOrAddMajorGridProperties();
    //刻度线标记 内部
    yAxis.setMajorTickMark(AxisTickMark.IN);
    
    //折线图
    XDDFLineChartData lineChartData = (XDDFLineChartData) chart.createData(ChartTypes.LINE, xAxis, yAxis);
    
    //x轴标题 取第一列数据
    XDDFDataSource<String> xTitleDataSource = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1, data.size(), 0, 0));
    
    //折线图
    for (int i = 1; i < head.size(); i++) { 
        XDDFNumericalDataSource<Double> yDataSource = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, data.size(), i, i));
        XDDFLineChartData.Series series = (XDDFLineChartData.Series) lineChartData.addSeries(xTitleDataSource, yDataSource);
        series.setTitle(head.get(i), null);
        series.setSmooth(false);
        series.setMarkerStyle(MarkerStyle.NONE);
    }
    
    chart.plot(lineChartData);
    
    try { 
        File file = new File(FileSystemView.getFileSystemView().getHomeDirectory(), "价格趋势.xlsx");
        workbook.write(new FileOutputStream(file));
    } catch (IOException e) { 
        e.printStackTrace();
    }

![在这里插入图片描述][202008231323191.png]

## 高度封装 ##

/** * 面向无实体的Excel处理 * 目标：封装 一个方法调用就能实现 * * @author zhanjixun * @date 2021-08-05 11:37:34 */
    @UtilityClass
    public class FastExcel { 
    
        /** * 并行流遍历某列的所有行 * * @param fileName * @param rowNo * @param consumer */
        public void parallelEachLineOnGivenRow(String fileName, int rowNo, Consumer<Object> consumer) { 
            File outFile = new File(FileSystemView.getFileSystemView().getHomeDirectory(), fileName);
            List<LinkedHashMap<Integer, Object>> mapList = EasyExcel.read(outFile, new SyncReadListener()).doReadAllSync();
            mapList.parallelStream().map(m -> m.get(rowNo)).forEach(consumer);
        }
    
    }

[https_github.com_alibaba_easyexcel]: https://github.com/alibaba/easyexcel
[https_www.xuxueli.com_xxl-tool]: https://www.xuxueli.com/xxl-tool/
[https_mvnrepository.com_artifact_com.xuxueli_xxl-tool]: https://mvnrepository.com/artifact/com.xuxueli/xxl-tool
[https_hutool.cn_docs_poi]: https://hutool.cn/docs/#/poi/%E6%A6%82%E8%BF%B0
[https_mvnrepository.com_artifact_cn.hutool_hutool-all]: https://mvnrepository.com/artifact/cn.hutool/hutool-all
[https_mvnrepository.com_artifact_cn.hutool_hutool-poi]: https://mvnrepository.com/artifact/cn.hutool/hutool-poi
[https_mvnrepository.com_artifact_org.apache.poi_poi]: https://mvnrepository.com/artifact/org.apache.poi/poi
[https_mvnrepository.com_artifact_org.apache.poi_poi-ooxml]: https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
[202008231323191.png]: /images/20221124/812763c78a1843eeb11fc321f6e3230c.png