ExcelUtil 工具类
package com.base.utils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;
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.io.InputStream;
import java.lang.reflect.Method;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.regex.Pattern;
@Slf4j
public class ExcelUtil {
/**
*
* @param file 导入的excel 文件
* @param excelRowToTitleMap excel 的表头对应关系， key 为第几列（从 1 开始）， value 为 列名
* @param excelTitleToPojoMap 列名对应java 中实体类属性名
* @param sheetList 要导入的 sheetList 的列表，不传默认全部的 sheet 页
* @param startRow 从第几行开始导入数据
* @param clazz list 列表中的元素类型
*/
public static <T> List<T> importExcelByFile(MultipartFile file,
Map<Integer, String> excelRowToTitleMap,
Map<String, String> excelTitleToPojoMap,
List<String> sheetList, Integer startRow, Class<T> clazz) {
List<T> list = new ArrayList<>();
try (InputStream fis = file.getInputStream()){
list = importExcel(fis, excelRowToTitleMap, excelTitleToPojoMap, sheetList, startRow, clazz);
} catch (Exception e) {
log.info("import excel exception：{}", e.getMessage());
}
return list;
}
/**
*
* @param excelRowToTitleMap excel 的表头对应关系， key 为第几列（从 1 开始）， value 为 列名
* @param excelTitleToPojoMap 列名对应java 中实体类属性名
* @param sheetList 要导入的 sheetList 的列表，不传默认全部的 sheet 页
* @param startRow 从第几行开始导入数据
* @param clazz list 列表中的元素类型
*/
public static <T> List<T> importExcel(InputStream inputStream,
Map<Integer, String> excelRowToTitleMap,
Map<String, String> excelTitleToPojoMap,
List<String> sheetList, Integer startRow, Class<T> clazz) {
Set<Integer> rows = excelRowToTitleMap.keySet();
List<T> list = new ArrayList<>();
try (Workbook workbook = WorkbookFactory.create(inputStream)) {
for (Sheet sheet : workbook) {
String sheetName = sheet.getSheetName();
// 如果指定了要导入的 sheet 页，就需要过滤其他的
if (sheetList != null && !sheetList.contains(sheetName)) {
continue;
}
for (Row row : sheet) {
// 去掉表头
if (row.getRowNum() + 1 < startRow) {
continue;
}
// 反射获取对象
T obj = clazz.newInstance();
for (int i = 0; i < row.getLastCellNum(); i++) {
Cell cell = row.getCell(i);
String cellValue = cell != null ? cell.toString() : null;
if (rows.contains(i + 1)) {
String rowTitleName = excelRowToTitleMap.get(i + 1);
String propertyName = excelTitleToPojoMap.get(rowTitleName);
if (propertyName != null) {
// 反射获取字段的set方法
PropertyDescriptor propertyDescriptor = new PropertyDescriptor(propertyName, clazz);
Method writeMethod = propertyDescriptor.getWriteMethod();
if (writeMethod != null) {
Class<?> propertyType = propertyDescriptor.getPropertyType();
Object value = convertValue(cellValue, propertyType);
writeMethod.invoke(obj, value);
}
}
}
}
list.add(obj);
}
}
} catch (Exception e) {
log.info("import excel exception：{}", e.getMessage());
}
return list;
}
/**
* 将 list 导入 excel
* @param list list 数据列表
* @param obj list 的实体对象
* @param header 表头数组 （为 null 时，实体类中属性名作为表头）
* @param rows 导出的列 (为 null 导出所有列)
* @param fileName 文件名称
* @param response response
*/
public static void exportListToExcel(List<?> list, Class obj, String[] header, String[] rows, String fileName, HttpServletResponse response) {
try {
//1.创建工作簿
Workbook book = new XSSFWorkbook();
//2.创建 sheet 页
Sheet sheet = book.createSheet();
//获取 get 方法
List<Method> methods = new LinkedList<>();
if (rows == null || rows.length == 0) {
//获取实体类中所有列 get 方法
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 {
//获取指定列的 get 方法
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);
}
}
//3.创建表头
Row headerRow = sheet.createRow(0);
CellStyle headerStyle = getHeaderStyle(book);
setHeaderRow(header, headerRow, headerStyle);
//4.填充数据
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 ++;
}
//getLastRowNum 获取行标（表头行标为 0）
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() : "-");
}
}
}
//5.将 excel 表格导出
response.setContentType("application/-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 Object convertValue(String cellValue, Class<?> targetType) {
if (cellValue == null || targetType == null) {
return null;
}
if (String.class.equals(targetType)) {
return cellValue;
} else if (Integer.class.equals(targetType) || int.class.equals(targetType)) {
return Integer.valueOf(cellValue);
} else if (Double.class.equals(targetType) || double.class.equals(targetType)) {
return Double.valueOf(cellValue);
} else if (Boolean.class.equals(targetType) || boolean.class.equals(targetType)) {
return Boolean.valueOf(cellValue);
}
// 可以根据需要添加其他类型的转换
return cellValue;
}
/**
* 设置表头
* @param header
* @param headerRow
* @param headerStyle
*/
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);
}
}
/**
* 表头样式
* @param book
*/
private static CellStyle getHeaderStyle(Workbook book) {
CellStyle headerStyle = book.createCellStyle();
Font headerFont = book.createFont();
headerFont.setBold(true); //设置粗体
headerFont.setFontName("表头加粗字体");
headerStyle.setFont(headerFont);
return headerStyle;
}
}