maven 依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
工具类
package com.kurumi.util;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.MethodDescriptor;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.nio.charset.StandardCharsets;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Pattern;
public class ExcelUtil {
public static void writeListToExcel(List<?> list, Class obj, String fileName, HttpServletResponse response) {
ExcelUtil.writeListToExcel(list,obj,null,null,fileName,response);
}
public static void writeListToExcel(List<?> list, Class obj,String[] rows,String fileName, HttpServletResponse response) {
ExcelUtil.writeListToExcel(list,obj,null,rows,fileName,response);
}
public static void writeListToExcel(List<?> list, Class obj, String[] header, String[] rows,String fileName, HttpServletResponse response) {
try {
Workbook book = new XSSFWorkbook();
Sheet sheet = book.createSheet();
List<Method> methods = new LinkedList<>();
if (rows == null || rows.length == 0) {
BeanInfo beanInfo = Introspector.getBeanInfo(obj);
MethodDescriptor[] methodDescriptors = beanInfo.getMethodDescriptors();
for (int i = 0; i < methodDescriptors.length; i++) {
String method = methodDescriptors[i].getName();
if (Pattern.matches("[g][e][t]\\w*",method) && !"getClass".equals(method)) {
methods.add(methodDescriptors[i].getMethod());
}
}
} else {
for (int i = 0; i < rows.length; i++) {
PropertyDescriptor propertyDescriptor = new PropertyDescriptor(rows[i], obj);
methods.add(propertyDescriptor.getReadMethod());
}
}
if (header == null || header.length == 0) {
header = new String[methods.size()];
for (int i = 0; i < header.length; i++) {
String methodName = methods.get(i).getName();
header[i] = methodName.substring(3,4).toLowerCase() + methodName.substring(4);
}
}
Row headerRow = sheet.createRow(0);
CellStyle headerStyle = getHeaderStyle(book);
setHeaderRow(header, headerRow, headerStyle);
int sheetFlag = 0;
if (!CollectionUtils.isEmpty(list)) {
for (int i = 0; i < list.size(); i++) {
if ((i+1)%1048575 == 0) {
sheet = book.createSheet("sheet" + sheetFlag);
headerRow = sheet.createRow(0);
setHeaderRow(header, headerRow, headerStyle);
sheetFlag ++;
}
Row row = sheet.createRow(sheet.getLastRowNum() + 1);
Object o = list.get(i);
for (int j = 0; j < methods.size(); j++) {
Object value = methods.get(j).invoke(o);
row.createCell(j).setCellValue(value != null ? value.toString() : "-");
}
}
}
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes(StandardCharsets.UTF_8),"ISO8859-1") + ".xlsx");
ServletOutputStream outputStream = response.getOutputStream();
book.write(outputStream);
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static void setHeaderRow(String[] header, Row headerRow, CellStyle headerStyle) {
for (int i = 0; i < header.length; i++) {
Cell headerCell = headerRow.createCell(i);
headerCell.setCellValue(header[i]);
headerCell.setCellStyle(headerStyle);
}
}
private static CellStyle getHeaderStyle(Workbook book) {
CellStyle headerStyle = book.createCellStyle();
Font headerFont = book.createFont();
headerFont.setBold(true);
headerFont.setFontName("表头加粗字体");
headerStyle.setFont(headerFont);
return headerStyle;
}
}
|