POI简介
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能 。开发工具及环境
【Java超级好用的Excel动态生成工具】开发工具：IdeaJAVA版本：jdk1.8.0_241
IDE插件：lombok
Maven 依赖
org.apache.poi poi4.0.1 org.apache.poi poi-ooxml4.0.1 准备工作
- 1.创建ExcelHeader类，该类的作用是可以自定义excel列头名称
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;@Data@AllArgsConstructor@NoArgsConstructor@Builderpublic class ExcelHeader {private String fieldName;private String columnName;}- 2.创建Excel工具类
import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import javax.servlet.http.HttpServletResponse;import java.beans.PropertyDescriptor;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.lang.reflect.Method;import java.util.Arrays;import java.util.Iterator;import java.util.List;import java.util.stream.Collectors;public class ReflectExcelWriter {private List headers;private Class clazz;public ReflectExcelWriter(List headers, Class clazz) {this.headers = headers;this.clazz = clazz;}public ReflectExcelWriter(Class clazz) {this.clazz = clazz;this.initHeaders();}private ReflectExcelWriter() {}private void initHeaders() {this.headers = Arrays.asList(this.clazz.getDeclaredFields()).stream().map(field -> ExcelHeader.builder().fieldName(field.getName()).columnName(field.getName()).build()).collect(Collectors.toList());}public void writeFile(String fileName, List dataList) {FileOutputStream fileOut = null;Workbook workbook = exportData(dataList);try {File file = new File(fileName);if (!file.exists()) {file.createNewFile();}fileOut = new FileOutputStream(file);workbook.write(fileOut);} catch (Exception e) {e.printStackTrace();} finally {try {if (null != fileOut) fileOut.close();if (null != workbook) workbook.close();} catch (Exception e) {e.printStackTrace();}}}public void downFile(List dataList, HttpServletResponse response) throws IOException {response.setContentType("application/vnd.ms-excel;charset=UTF-8");response.addHeader("Content-Disposition", "attachment;fileName=response.xlsx");// 设置文件名OutputStream out = response.getOutputStream();Workbook workbook = exportData(dataList);try {workbook.write(out);out.flush();} catch (Exception e) {e.printStackTrace();} finally {try {if (null != workbook) workbook.close();} catch (Exception e) {e.printStackTrace();}}}/*** 生成Excel并写入数据信息** @param dataList 数据列表* @return 写入数据后的工作簿对象*/public Workbook exportData(List dataList) {// 生成xlsx的ExcelWorkbook workbook = new SXSSFWorkbook();// 生成Sheet表，写入第一行的列头Sheet sheet = buildDataSheet(workbook);//构建每行的数据内容int rowNum = 1;for (Iterator it = dataList.iterator(); it.hasNext(); ) {T data = https://www.itzhengshu.com/excel/it.next();if (data == null) {continue;}//输出行数据Row row = sheet.createRow(rowNum);try {convertDataToRow(data, row);} catch (IllegalAccessException e) {e.printStackTrace();} catch (InstantiationException e) {e.printStackTrace();}}return workbook;}/*** 生成sheet表，并写入第一行数据（列头）** @param workbook 工作簿对象* @return 已经写入列头的Sheet*/private Sheet buildDataSheet(Workbook workbook) {Sheet sheet = workbook.createSheet();// 设置列头宽度for (int i = 0; i < headers.size(); i) {sheet.setColumnWidth(i, 4000);}// 设置默认行高sheet.setDefaultRowHeight((short) 400);// 写入第一行各列的数据Row head = sheet.createRow(0);for (int i = 0; i < headers.size(); i) {Cell cell = head.createCell(i);cell.setCellValue(headers.get(i).getColumnName());}return sheet;}/*** 将数据转换成行** @param t源数据* @param row 行对象* @return*/private void convertDataToRow(T t, Row row) throws IllegalAccessException, InstantiationException {int index = 0;try {for (ExcelHeader header : headers) {Cell cell = row.createCell(index);PropertyDescriptor descriptor = new PropertyDescriptor(header.getFieldName(), clazz);Method getMethod = descriptor.getReadMethod();if (getMethod != null) {Object result = getMethod.invoke(t);cell.setCellValue(null == result ?"" : result.toString());}}} catch (Exception e) {e.printStackTrace();}}} - 3.创建实体类
@Data@AllArgsConstructor@NoArgsConstructorpublic static class User{private String name;private String phone;private String birthdate;private String email;}- 4.测试类
- 默认列头
public class Test {public static void main(String[] args) {List users = Arrays.asList(new User("Eric", "13900000000", "1999-02-10", "Eric@163.com"),new User("Alice", "18600000000", "2002-03-21", "Alice@163.com"),new User("Jack", "13800000000", "1972-12-11", "Jack@163.com"),new User("Bob", "15500000000", "1986-06-15", "Bob@163.com"));String output = "learning/data/toutiao/user.xlsx";ReflectExcelWriter writer = new ReflectExcelWriter<>(User.class);writer.writeFile(output, users);}} 效果图
使用默认列头
- 自定义列头
public class Test {public static void main(String[] args) {List users = Arrays.asList(new User("Eric", "13900000000", "1999-02-10", "Eric@163.com"),new User("Alice", "18600000000", "2002-03-21", "Alice@163.com"),new User("Jack", "13800000000", "1972-12-11", "Jack@163.com"),new User("Bob", "15500000000", "1986-06-15", "Bob@163.com"));List headers = Arrays.asList(ExcelHeader.builder().fieldName("name").columnName("名字").build(),ExcelHeader.builder().fieldName("phone").columnName("联系电话").build(),ExcelHeader.builder().fieldName("birthdate").columnName("生日").build(),ExcelHeader.builder().fieldName("email").columnName("邮箱").build());String output = "learning/data/toutiao/user.xlsx"ReflectExcelWriter writer = new ReflectExcelWriter<>(headers, User.class);writer.writeFile(output, users);}} 效果图
自定义列头