我是用的java类反射方式生成的excel
1. pom依赖
<!-- <artifactId>poi</artifactId>
<!-- <artifactId>poi-ooxml</artifactId>
<!-- pom文件中只能导入上面两个jar包其中一个,否则会导致jar包版本冲突-->
<!--或者可以去官网下载jar包 -->
<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;
public class CustomStyle {
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;
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);
simpleExport(dataList,exportPath,"sheet1");
}
static class ValuePojo {
private Object fieldValue;
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;
}
}
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");
}
public static List<List<ValuePojo>> assemblyDataList(LinkedHashMap<String, String> tableHeaders, List<Object> dataList) {
List<List<ValuePojo>> allRowList = new ArrayList<>();
List<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;
}
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;
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()));
}
}
导出结果如下
代码还在优化,后续更新表格样式,导入等功能!!