先上代码:
定义需要导出的实体类
@Data
public class Student implements Serializable {
private Integer id;
private String studentName;
private Integer age;
private LocalDate birth;
private String desc;
private Double weight;
private Double height;
}
定义导出Excel的工具类
public class ExcelUtil {
/**
* 数据量过大时可通过此属性拆分多个sheet页
*/
private static final int MAX_ROW_NUM = 5000;
/**
* 通过反射的方式导出Excel
*
* @param dataList
* @param path
* @param fields
* @param <K>
* @throws Exception
*/
public static <K> void export(List<K> dataList, String path, Map<String, String> fields) throws Exception {
if (dataList == null || dataList.size() == 0) {
throw new Exception("dataList is empty");
}
if (fields == null || fields.isEmpty()) {
throw new ExcelException("fields map is empty");
}
// int sheetSize = dataList.size() / MAX_ROW_NUM;
// if (dataList.size() % MAX_ROW_NUM != 0) {
// sheetSize += 1;
// }
XSSFWorkbook workbook = new XSSFWorkbook();
String[] fieldNames = new String[fields.keySet().size()];
String[] displayNames = new String[fields.keySet().size()];
int i = 0;
for (String fieldName : fields.keySet()) {
fieldNames[i] = fieldName;
displayNames[i] = fields.get(fieldName);
i ++;
}
XSSFSheet sheet = workbook.createSheet();
XSSFRow row = sheet.createRow(0);
// 初始化列名
for (int cellNum = 0; cellNum < displayNames.length; cellNum++) {
row.createCell(cellNum).setCellValue(displayNames[cellNum]);
}
XSSFCell cell;
K data;
for (int j = 0; j < dataList.size(); j++) {
row = sheet.createRow(j + 1);
data = dataList.get(j);
for (int cellNum = 0; cellNum < fieldNames.length; cellNum++) {
cell = row.createCell(cellNum);
// 通过反射的方式取值赋给单元格
Field field = data.getClass().getDeclaredField(fieldNames[cellNum]);
field.setAccessible(true);
if (field.get(data) == null) {
cell.setCellValue("");
} else if (field.getType() == String.class) {
cell.setCellValue((String) field.get(data));
} else if (field.getType() == Integer.class) {
cell.setCellValue((Integer)field.get(data));
} else if (field.getType() == Double.class) {
cell.setCellValue((Double)field.get(data));
} else if (field.getType() == LocalDate.class) {
cell.setCellValue(((LocalDate)field.get(data)).format(DateTimeFormatter.ISO_DATE));
}
}
}
OutputStream out = null;
try{
out = new FileOutputStream(path);
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (out != null) {
out.close();
}
}
}
}
定义测试类
@org.junit.Test
public void test() throws Exception {
Map<String, String> fields = new HashMap<>();
fields.put("id", "编号");
fields.put("studentName", "姓名");
fields.put("age", "年龄");
fields.put("birth", "生日");
fields.put("desc", "简介");
fields.put("weight", "体重");
fields.put("height", "身高");
List<Student> dataList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Student student = new Student();
student.setId(i + 1);
student.setStudentName("学生" + i);
student.setAge(i + 20);
student.setBirth(LocalDate.now());
student.setHeight(175.67D);
student.setWeight(120.66D);
dataList.add(student);
}
String path = "D://student.xlsx";
ExcelUtil.export(dataList, path, fields);
}
查看效果
缺点:需要定义实体类
优点:使用反射的方式比较灵活,减少了很多获取值的硬编码
关于第一列标题的顺序也可以优化,目前就不做了,自己可以选择性优化一下。
另外如果数据量很大不要使用XSSFWorkBook ,建议使用SXSSFWorkBook。XSSFWorkBook对于内存的耗费特别严重,关于SXXFWorkBook的用法可以查看官方文档