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]] 4.文件格式 C:\\Users\\hdwang\\Desktop\\test.xlsx C:\\Users\\hdwang\\Desktop\\test2.xlsx |
|