依赖文件
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
</dependency>
定义excel注解
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelColumn {
int value() default 0;
String name() default "";
int index() default 1;
}
Excel文件工具类
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
@Slf4j
public class ExcelMapper {
/**
* 通过索引来注入
*
* @param file
* @param clazz
* @param <T>
* @return
* @throws Exception
*/
public static <T> List<T> mapExcelToObject(File file, Class<T> clazz) throws Exception {
List<T> objects = new ArrayList<>();
Workbook workbook = WorkbookFactory.create(file);
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (row.getRowNum() == 0) {
continue; // 跳过标题行
}
T object = clazz.getDeclaredConstructor().newInstance();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
int columnIndex = cell.getColumnIndex();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
if (excelColumn != null && excelColumn.value() == columnIndex) {
field.setAccessible(true);
setFieldValue(field, object, cell);
break;
}
}
}
objects.add(object);
}
workbook.close();
return objects;
}
/**
* 通过字段名注入（改进测试版）
* <p>
* 逻辑，将Cell全部转化为String格式 再读取
* 根据java实体类的类型再做转化
*
* @param file
* @param clazz
* @param <T>
* @return
* @throws IOException
*/
public static <T> List<T> readExcel3(File file, Class<T> clazz) throws IOException {
List<T> dataList = new ArrayList<>();
try (FileInputStream fis = new FileInputStream(file);
Workbook workbook = new XSSFWorkbook(fis)) {
Sheet sheet = workbook.getSheetAt(0); // 假设只有一个 sheet
// 读取表头
Row headerRow = sheet.getRow(0);
Map<Integer, String> columnIndexToFieldName = new HashMap<>();
for (int i = 0; i < headerRow.getLastCellNum(); i++) {
Cell cell = headerRow.getCell(i);
String fieldName = cell.getStringCellValue().trim();
columnIndexToFieldName.put(i, fieldName);
}
// 读取数据行
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row dataRow = sheet.getRow(i);
try {
T instance = clazz.getDeclaredConstructor().newInstance();
for (int j = 0; j < dataRow.getLastCellNum(); j++) {
Cell cell = dataRow.getCell(j);
String fieldName = columnIndexToFieldName.get(j);
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
if (excelColumn != null && excelColumn.name().equals(fieldName)) {
field.setAccessible(true);
setFieldValue3(field, instance, cell);
break;
}
}
}
dataList.add(instance);
} catch (Exception e) {
e.printStackTrace();
}
}
}
return dataList;
}
/**
* 判断属性的类型并且注入
*
* @param field
* @param object
* @param cell
* @throws IllegalAccessException
*/
private static void setFieldValue(Field field, Object object, Cell cell) throws IllegalAccessException {
Class<?> fieldType = field.getType();
if (fieldType.isAssignableFrom(String.class)) {
field.set(object, cell.getStringCellValue().trim());
} else if (fieldType == int.class || fieldType.isAssignableFrom(Integer.class)) {
field.set(object, Integer.valueOf((int) cell.getNumericCellValue()));
} else if (fieldType == long.class || fieldType.isAssignableFrom(Long.class)) {
field.set(object, Long.valueOf((long) cell.getNumericCellValue()));
} else if (fieldType == double.class || fieldType.isAssignableFrom(Double.class)) {
field.set(object, cell.getNumericCellValue());
}
}
/**
* 转为cell格式 读取方式
*
* @param field
* @param object
* @param cell
* @throws IllegalAccessException
*/
private static void setFieldValue3(Field field, Object object, Cell cell) throws IllegalAccessException {
Class<?> fieldType = field.getType();
if (fieldType.isAssignableFrom(String.class)) {
field.set(object, getCellValue3(cell).trim());
} else if (fieldType == int.class || fieldType.isAssignableFrom(Integer.class)) {
String vaule = getCellValue3(cell);
if (StringUtils.isBlank(vaule)) {
field.set(object, 0);
} else {
field.set(object, Integer.parseInt(vaule));
}
} else if (fieldType == long.class || fieldType.isAssignableFrom(Long.class)) {
String vaule = getCellValue3(cell);
if (StringUtils.isBlank(vaule)) {
field.set(object, 0L);
} else {
field.set(object, Long.parseLong(vaule));
}
} else if (fieldType == double.class || fieldType.isAssignableFrom(Double.class)) {
String vaule = getCellValue3(cell);
if (StringUtils.isBlank(vaule)) {
field.set(object, 0.0);
}
if (vaule.matches("-?\\d+(\\.\\d+)?")) {
field.set(object, Double.valueOf(vaule));
;
} else {
field.set(object, 0.0);
}
}
}
private static Object getCellValue(Cell cell) {
if (cell == null) {
return "";
}
CellType cellType = cell.getCellType();
if (cellType == CellType.STRING) {
return cell.getStringCellValue();
} else if (cellType == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else {
return cell.getNumericCellValue();
}
} else if (cellType == CellType.BOOLEAN) {
return cell.getBooleanCellValue();
} else if (cellType == CellType.FORMULA) {
return cell.getCellFormula();
} else if (cellType == CellType.ERROR) {
return cell.getErrorCellValue();
} else {
return "";
}
}
private static String getCellValue3(Cell cell) {
if (cell == null) {
return "";
}
CellType cellType = cell.getCellType();
if (cellType == CellType.STRING) {
return cell.getStringCellValue();
} else if (cellType == CellType.NUMERIC) {
/* cell.setCellType(CellType.STRING);
return cell.getStringCellValue();*/
DataFormatter dataFormatter = new DataFormatter();
return dataFormatter.formatCellValue(cell);
}
return "";
}
public static <T> void exportToExcel(List<T> dataList, HttpServletResponse response) {
response.reset();
try {
// 创建工作簿
Workbook workbook = new XSSFWorkbook();
// 创建工作表
Sheet sheet = workbook.createSheet("Sheet1");
// 创建标题行
Row headerRow = sheet.createRow(0);
CellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 获取数据对象的类型
Class<?> dataType = dataList.get(0).getClass();
// 获取所有字段
Field[] fields = dataType.getDeclaredFields();
for (Field field : fields) {
// 检查字段是否有@ExcelColumn注解
if (field.isAnnotationPresent(ExcelColumn.class)) {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
int columnIndex = annotation.index();
String columnName = annotation.name();
// 创建标题单元格
Cell cell = headerRow.createCell(columnIndex);
cell.setCellValue(columnName);
cell.setCellStyle(headerCellStyle);
}
}
// 填充数据行
for (int i = 0; i < dataList.size(); i++) {
Row dataRow = sheet.createRow(i + 1);
T dataItem = dataList.get(i);
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelColumn.class)) {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
int columnIndex = annotation.index();
// 设置单元格值
Cell cell = dataRow.createCell(columnIndex);
field.setAccessible(true);
try {
Object value = field.get(dataItem);
cell.setCellValue(value != null ? value.toString() : "");
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
}
// 生成临时文件
File tempFile = File.createTempFile("temp", ".xlsx");
try (FileOutputStream outputStream = new FileOutputStream(tempFile)) {
workbook.write(outputStream);
}
// 设置响应头信息
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename=output.xlsx");
// 将临时文件内容写入响应输出流
try (InputStream inputStream = new FileInputStream(tempFile);
OutputStream outputStream = response.getOutputStream()) {
byte[] buffer = new byte[8192];
int bytesRead;
while ((bytesRead = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, bytesRead);
}
}
System.out.println("Excel导出成功！");
} catch (IOException e) {
e.printStackTrace();
System.out.println("Excel导出失败：" + e.getMessage());
}
}
/**
* 导出Excel文件
* 此方法通过缓存的方式，多次读取，避免内存占用过大
* 适合大文件到处
*
* @param filePath 文件路径
* @param response
* @param <T>
*/
public static <T> void exportToExcel(String filePath, HttpServletResponse response) {
String fileName = filePath.substring(filePath.lastIndexOf(File.separator) + 1);
response.reset();
// 设置响应头信息
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
try {
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
} catch (UnsupportedEncodingException e) {
throw new RuntimeException(e);
}
response.setCharacterEncoding("utf-8");
// 设置文件长度
File file = new File(filePath);
long fileLength = file.length();
response.setContentLength((int) fileLength);
// 将临时文件内容写入响应输出流
try (InputStream inputStream = Files.newInputStream(file.toPath());
OutputStream outputStream = response.getOutputStream()) {
byte[] buffer = new byte[8192];
int bytesRead;
while ((bytesRead = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, bytesRead);
}
} catch (Exception e) {
e.printStackTrace();
log.info("Excel导出失败：" + e.getMessage());
}
}
public static <T> void exportToExcel2(String file, HttpServletResponse response) {
// 读取表格文件内容为字节数组
Path path = Paths.get(file);
String fileName = path.getFileName().toString();
// fileName = file.startsWith()
//
// 设置文件下载响应的相关信息
try (OutputStream os = new BufferedOutputStream(response.getOutputStream())) {
byte[] fileBytes = Files.readAllBytes(path);
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.addHeader("Content-Length", "" + fileBytes.length);
response.setCharacterEncoding("utf-8");
os.write(fileBytes);
os.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
}
定义一个Excel实体类
@Data
public class SeparateLinesExcel {
/**
* 姓名
*/
@ApiModelProperty(value="姓名")
@ExcelColumn(name = "姓名")
private String name;
/**
* 账号
*/
@ApiModelProperty(value="账号")
@ExcelColumn(name = "账号")
private String protal;
/**
* 地市
*/
@ApiModelProperty(value="地市")
@ExcelColumn(name = "地市")
private String city;
/**
* 条线
*/
@ApiModelProperty(value="条线")
@ExcelColumn(name = "条线")
private String line;
/**
* 条线ID
*/
@ApiModelProperty(value="条线ID")
@ExcelColumn(name = "条线ID")
private Long lineId;
/**
* 工单号
*/
@ApiModelProperty(value="工单号")
@ExcelColumn(name = "工单号")
private String workNum;
}
只需要将excel实体注入进方法里面就可以自动解析文件并且得到对应的List
// 保存文件
String newFileName = FileUtil.saveFile(file, loginId, uploadDir);
// 读取文件
List<T> excels = readExcel(newFileName,clazz);