JAVA使用hutool poi工具读取Excel模板并写值输出文件

1.pom依赖配置

       <!-- huTool工具箱 -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.9</version>
        </dependency>

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

2.测试代码

package com.hdwang.exceltest;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Main {
    public static void main(String[] args) {
        File templateFile = new File("C:\\Users\\hdwang\\Desktop\\test.xlsx");
        File outputFile = new File("C:\\Users\\hdwang\\Desktop\\test2.xlsx");
        produceExcelByTemplate(templateFile, outputFile);
    }


    /**
     * 根据模板文件产生Excel文件
     *
     * @param templateFile 模板文件
     * @param outputFile   输出文件
     */
    private static void produceExcelByTemplate(File templateFile, File outputFile) {
        //===========================读取测试=====================================
        ExcelReader excelReader = ExcelUtil.getReader(templateFile, 0);

        //读取数据，按照行列方式读取所有数据
        List<List<Object>> rowObjects = excelReader.read();
        System.out.println(rowObjects);

        //读取数据，指定标题行和起始数据行
        List<Map<String, Object>> rowMaps = excelReader.read(1, 2, Integer.MAX_VALUE);
        System.out.println(rowMaps);

        //读取数据，指定标题行和起始数据行，转换为对象
        excelReader.addHeaderAlias("名称", "name");
        excelReader.addHeaderAlias("数值", "value");
        List<ZhenquanReport> reports = excelReader.read(1, 2, Integer.MAX_VALUE, ZhenquanReport.class);
        System.out.println(reports);

        //读取指定单元格
        String value = String.valueOf(excelReader.readCellValue(2,2));
        System.out.println(value);

        //关闭
        excelReader.close();

        //===========================写入测试=====================================
        ExcelWriter excelWriter = new ExcelWriter(templateFile);
        excelWriter.writeCellValue(3, 2, "error"); //写入值，x=列、y=行号

        //设置单元格样式
        CellStyle cellStyle = excelWriter.createCellStyle(3, 2);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置背景色
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        cellStyle.setBorderBottom(BorderStyle.DASHED); //设置边框线条与颜色
        cellStyle.setBottomBorderColor(IndexedColors.PINK.getIndex());
        Font font = excelWriter.createFont();
        font.setColor(IndexedColors.RED.getIndex());
        cellStyle.setFont(font); //设置字体

        //设置输出文件路径
        excelWriter.setDestFile(outputFile);
        excelWriter.close();
    }
}

3.读取的内容

[[证券月报, 证券月报, 证券月报, 证券月报, ], [null, 名称, 数值], [资产, 净资产, 10000], [资产, 市值, 20000], [null, 标题], [利润, 净利润, 1000]]
[{A=资产, 名称=净资产, 数值=10000}, {A=资产, 名称=市值, 数值=20000}, {A=null, 名称=标题, 数值=null}, {A=利润, 名称=净利润, 数值=1000}]
[ZhenquanReport{name='净资产', value='10000'}, ZhenquanReport{name='市值', value='20000'}, ZhenquanReport{name='标题', value='null'}, ZhenquanReport{name='净利润', value='1000'}]
10000

4.文件格式

C:\\Users\\hdwang\\Desktop\\test.xlsx

C:\\Users\\hdwang\\Desktop\\test2.xlsx