1000字范文 > java 通用工具 POI XSSF导出.xls或者.xlsx

java 通用工具 POI XSSF导出.xls或者.xlsx

时间：2022-02-24 11:14:06

我是用的java类反射方式生成的excel

1. pom依赖

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

2. 创建一个单元格格式工具类 CustomStyle ,这个只实现了默认格式,可以自定义相关格式

package excelTool.style;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFWorkbook;/*** 自定义单元格样式* 单元格样式(cellStyle) HSSFCellStyle cellStyle = wb.createCellStyle();* 单元格内容样式(dataFormat) HSSFDataFormat format = wb.createDataFormat();*/public class CustomStyle {/*** 默认的单元格样式** 常用单元格数据样式* "General", "0", "0.00", "#,##0", "#,##0.00","\"$\"#,##0_);(\"$\"#,##0)", "\"$\"#,##0_);[Red](\"$\"#,##0)", "\"$\"#,##0.00_);(\"$\"#,##0.00)",* "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)","0%", "0.00%", "0.00E+00", "# ?/?", "# ??/??", "m/d/yy", "d-mmm-yy", "d-mmm", "mmm-yy", "h:mm AM/PM",* "h:mm:ss AM/PM", "h:mm", "h:mm:ss", "m/d/yy h:mm", "reserved-0x17","reserved-0x18", "reserved-0x19", "reserved-0x1A", "reserved-0x1B",* "reserved-0x1C", "reserved-0x1D", "reserved-0x1E", "reserved-0x1F","reserved-0x20", "reserved-0x21", "reserved-0x22","reserved-0x23",* "reserved-0x24", "#,##0_);(#,##0)", "#,##0_);[Red](#,##0)", "#,##0.00_);(#,##0.00)", "#,##0.00_);[Red](#,##0.00)",* "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)","_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)",* "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)", "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)",* "mm:ss", "[h]:mm:ss", "mm:ss.0", "##0.0E+0", "@", "TEXT"*/public static XSSFCellStyle defaultCellStyle(XSSFWorkbook workbook,String fieldType) {XSSFCellStyle cellStyle = workbook.createCellStyle();short format = 164;if ("Date".equals(fieldType)){format = workbook.createDataFormat().getFormat("m/d/yy");}else if ("BigDecimal".equals(fieldType)){format = workbook.createDataFormat().getFormat("#,##0.00");}else {format = workbook.createDataFormat().getFormat("TEXT");}cellStyle.setDataFormat(format);return cellStyle;}}

3.导出工具类ExcelExport,里面有一个main方法可以导出相应数据其中 dataList 参数就是你从数据库中查询出来实体类集合,tableHeaders 是你自己定义的汉字和实体类字段的映射表头,定义好这些就可以了

import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.lang.reflect.Field;import java.util.ArrayList;import java.util.Date;import java.util.LinkedHashMap;import java.util.List;import static excelTool.pojo.StaticVar.arrayList;import static excelTool.style.CustomStyle.defaultCellStyle;/*** HSSF提供读写XLS格式档案的功能。* XSSF提供读写XLSX格式档案的功能。* HWPF提供读写DOC格式档案的功能。* XWPF提供读写DOCX格式档案的功能。* * <artifactId>poi</artifactId> //适用于低版本，也就是xls结尾的Excel表格* <artifactId>poi-ooxml</artifactId>//适用于高版本，也就是xlsx结尾的Excel表格* //pom文件中只能导入上面两个jar包其中一个，否则会导致jar包版本冲突* //或者可以去官网下载jar包* <p>* //一个excel表格* HSSFWorkbook wb = new HSSFWorkbook();* //一个工作表格(sheet)* HSSFSheet sheet = wb.createSheet("sheet1");* //一行(row)* HSSFRow row = sheet.createRow(0);* //一个单元格(cell)* HSSFCell cell = row.createCell(0);*/public class ExcelExport {// 使用方法public static void main(String[] args) {// 导出地址String exportPath = "./poi_tool/src/main/java/excelTool/test.xlsx";// 生成拼装的数据集合List<List<ValuePojo>> dataList = assemblyDataList(tableHeaders, arrayList);// 简单excel导出simpleExport(dataList,exportPath,"sheet1");}/*** excel导出通用类*/static class ValuePojo {// 字段值private Object fieldValue;// 字段类型(JAVA类型)private String fieldType;// 所在行列private int row;private int col;public Object getFieldValue() {return fieldValue;}public void setFieldValue(Object fieldValue) {this.fieldValue = fieldValue;}public String getFieldType() {return fieldType;}public void setFieldType(String fieldType) {this.fieldType = fieldType;}public int getRow() {return row;}public void setRow(int row) {this.row = row;}public int getCol() {return col;}public void setCol(int col) {this.col = col;}}/*** 汉字名称和字段映射关系,需要自定义* 格式需要按照 tableHeaders.put("表头汉字名称","需要导出的实体类的字段名");* 顺序就是向map中添加的顺序,使用的LinkedHashMap,有序集合*/public static final LinkedHashMap<String, String> tableHeaders = new LinkedHashMap();static {tableHeaders.put("序号", "id");tableHeaders.put("名称", "name");tableHeaders.put("年龄", "age");tableHeaders.put("地址", "address");tableHeaders.put("信息", "infomation");tableHeaders.put("创建时间", "createTime");}/*=========================================================以下是可以调用的导出相关方法=========================================================================*//*** 实体类数据拼装成通用导出数据集合** @param tableHeaders excel表头集合* @param dataList数据实体类集合*/public static List<List<ValuePojo>> assemblyDataList(LinkedHashMap<String, String> tableHeaders, List<Object> dataList) {// 所有数据行集合List<List<ValuePojo>> allRowList = new ArrayList<>();// 组装需要导出的表头数据,指定为第一行row=0List<ValuePojo> firstRowList = new ArrayList<>();String[] headerKeys = tableHeaders.keySet().toArray(new String[tableHeaders.size()]);for (int col = 0; col < headerKeys.length; col++) {ValuePojo dataValuePojo = new ValuePojo();dataValuePojo.setFieldValue(headerKeys[col]);dataValuePojo.setRow(0);dataValuePojo.setCol(col);firstRowList.add(dataValuePojo);}// 添加表头数据到集合allRowList.add(firstRowList);try {// 组装需要导出的数据for (int row = 0; row < dataList.size(); row++) {List<ValuePojo> rowList = new ArrayList<>();Object dataObj = dataList.get(row);for (int col = 0; col < headerKeys.length; col++) {//使用反射获取字段的值.并且设置允许访问私有变量Field field = dataObj.getClass().getDeclaredField(tableHeaders.get(headerKeys[col]));field.trySetAccessible();String typeName = field.getType().getName();ValuePojo valuePojo = new ValuePojo();//设置字段值valuePojo.setFieldValue(field.get(dataObj));//设置字段类型valuePojo.setFieldType(typeName.substring(typeName.lastIndexOf(".") + 1));//设置单元格数据行列(在合并的时候才需要)valuePojo.setRow(row + 1);valuePojo.setCol(col);rowList.add(valuePojo);}// 添加数据到全部集合allRowList.add(rowList);}} catch (NoSuchFieldException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}return allRowList;}/*** 简单excel导出** @param allRowList* @param exportPath* @param sheetName*/public static void simpleExport(List<List<ValuePojo>> allRowList, String exportPath, String sheetName) {// 导出,生成文档try {XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet(sheetName);for (int row = 0; row < allRowList.size(); row++) {XSSFRow sheetRow = sheet.createRow(row);for (int col = 0; col < allRowList.get(row).size(); col++) {XSSFCell cell = sheetRow.createCell(col);//设置单元格格式和单元格数据格式String fieldType = allRowList.get(row).get(col).getFieldType();Object fieldValue = allRowList.get(row).get(col).getFieldValue();cell.setCellStyle(defaultCellStyle(workbook,fieldType));//设置单元格数值if ("Date".equals(fieldType)){cell.setCellValue((Date) fieldValue);}else if ("BigDecimal".equals(fieldType)){cell.setCellValue((Double) fieldValue);}else {cell.setCellValue(fieldValue.toString());}}}OutputStream outputStream = new FileOutputStream(exportPath);workbook.write(outputStream);workbook.close();outputStream.close();} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}}}

附: 下面是样例数据代码, 实际使用的时候不需要!!!

import java.util.Date;/*** Excel导出示例数据* 实际使用的时候不需要这个类!!!* */public class SampleDataE {private Long id;private String name;private Integer age;private String address;private String infomation;private Date createTime;public SampleDataE(Long id, String name, Integer age, String address, String infomation, Date createTime) {this.id = id;this.name = name;this.age = age;this.address = address;this.infomation = infomation;this.createTime = createTime;}public Long getId() {return id;}public void setId(Long id) {this.id = id;}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 getAddress() {return address;}public void setAddress(String address) {this.address = address;}public String getInfomation() {return infomation;}public void setInfomation(String infomation) {this.infomation = infomation;}public Date getCreateTime() {return createTime;}public void setCreateTime(Date createTime) {this.createTime = createTime;}}

import java.util.*;public class StaticVar {/*** 这段是手动创建的样例数据,实际使用时没用* */public static final List<Object> arrayList = new ArrayList<>();static {arrayList.add(new SampleDataE(10000L,"古拉加斯",30,"弗雷尔卓德","对古拉加斯而言，喝酒，是唯一一件比战斗还要重要的事。他对酒劲更强的麦酒有着难以抑制的渴求。在这种渴求的驱使下，他不断寻找着酒劲最强、最不寻常的原料来进行蒸馏。这个行事冲动、难以预测的吵闹酒徒，最喜欢的活动就是砸酒桶和砸脑袋。",new Date()));arrayList.add(new SampleDataE(20000L,"卡萨丁",400,"虚空裂隙","卡萨丁最初只是一名被遗弃的婴儿，在沙漠上受雇于商队，并且保护着他们的货物，自己充当幼儿，将掠食者引开，他很多次都侥幸的活下来，并且在市场上成为招牌，他不再是诱饵，而是商队的向导了，直到有一天他爱上了一名来自沙漠的女人，并且带着妻子和女儿，前往南方的一个小村子，他经常在外奔波，并且总是能带着故事回来，终于有一天，噩耗传来，他的家园受到了大灾，就像是从地底张开了一个大口子，吃掉了村子里的所有人。",new Date()));arrayList.add(new SampleDataE(30000L,"崔斯特",20,"蟒河流域","崔斯特·菲特是一名声名狼藉的纸牌高手和诈骗惯犯，世界上任何有人烟的地方都有他施展魅力和赌艺的足迹，让那些富人和痴人既羡慕又嫉恨。他很少会认真起来干一件事，总是用一抹轻蔑的微笑和一副漫不经心的随性面对每一天。无论做任何事情，崔斯特永远都会在袖子里藏一张王牌。",new Date()));}}