POI简介
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
开发工具及环境
开发工具：Idea
JAVA版本：jdk1.8.0_241
IDE插件：lombok
Maven 依赖
org.apache.poi
poi
4.0.1
org.apache.poi
poi-ooxml
4.0.1
准备工作
- 1.创建ExcelHeader类，该类的作用是可以自定义excel列头名称
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public 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的Excel
Workbook workbook = new SXSSFWorkbook();
// 生成Sheet表，写入第一行的列头
Sheet sheet = buildDataSheet(workbook);
//构建每行的数据内容
int rowNum = 1;
for (Iterator it = dataList.iterator(); it.hasNext(); ) {
T data = 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
@NoArgsConstructor
public 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);
}
}
效果图
自定义列头
