Java操作Excel
一、小册子(原生POI)
gitee地址：https://gitee.com/zhang-zhixi/springboot-poi-excel.git
GitHub地址：https://github.com/zhangzhixi0305/springboot-poi-excel.git
1、新建SpringBoot项目导入Pom
<dependencies>
<!--对Excel操作使用到的jar包-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
</dependency>
</dependencies>
2、进行上传Excel的表单数据
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<form action="/importExcel" method="post" enctype="multipart/form-data">
选择文件：<input type="file" name="file"/><br>
<input type="submit" value="上传"/>
</form>
</body>
</html>
3、处理Excel上传下载请求的Controller
package com.zhixi.controller;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @author zhangzhixi
* @version 1.0
* @date 2021-12-28 16:36
*/
@RestController
public class ExcelController {
private final Logger logger = LoggerFactory.getLogger(this.getClass());
@GetMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) throws Exception {
// 模拟从数据库查询数据
List<Student> studentList = new ArrayList<>();
studentList.add(new Student(1L, "周深（web导出）", 28, "贵州", new SimpleDateFormat("yyyy-MM-dd").parse("1992-9-29"), 161.0, true));
studentList.add(new Student(2L, "李健（web导出）", 46, "哈尔滨", new SimpleDateFormat("yyyy-MM-dd").parse("1974-9-23"), 174.5, true));
studentList.add(new Student(3L, "周星驰（web导出）", 58, "香港", new SimpleDateFormat("yyyy-MM-dd").parse("1962-6-22"), 174.0, false));
// 读取模板（实际开发可以放在resources文件夹下，随着项目一起打包发布）
InputStream excelInputStream = new ClassPathResource("static/excel/student_info.xlsx").getInputStream();
// XSSFWorkbook除了直接接收Path外，还可以传入输入流
XSSFWorkbook workbook = new XSSFWorkbook(excelInputStream);
// 获取模板sheet
XSSFSheet sheet = workbook.getSheetAt(0);
// 找到数据起始行（前两行是标题和表头，要跳过，所以是getRow(2)）
XSSFRow dataTemplateRow = sheet.getRow(2);
// 构造一个CellStyle数组，用来存放单元格样式。一行有N个单元格，数组初始长度就设置为N
CellStyle[] cellStyles = new CellStyle[dataTemplateRow.getLastCellNum()];
for (int i = 0; i < cellStyles.length; i++) {
// 收集每一个格子对应的格式，你可以理解为准备了一把“格式刷”
cellStyles[i] = dataTemplateRow.getCell(i).getCellStyle();
}
// 创建单元格，并设置样式和数据
for (int i = 0; i < studentList.size(); i++) {
// 注意是i+2，模板前两行是大标题和表头。你可能看着难受，想把上面for的i改为i+2，千万别。因为studentList必须从0开始取值
XSSFRow row = sheet.createRow(i + 2);
// 为每一行创建单元格并设置数据
Student student = studentList.get(i);
XSSFCell nameCell = row.createCell(0);// 创建单元格
nameCell.setCellValue(student.getName()); // 设置值
nameCell.setCellStyle(cellStyles[0]); // 设置单元格样式
XSSFCell ageCell = row.createCell(1);
ageCell.setCellValue(student.getAge());
ageCell.setCellStyle(cellStyles[1]);
XSSFCell addressCell = row.createCell(2);
addressCell.setCellValue(student.getAddress());
addressCell.setCellStyle(cellStyles[2]);
/**
* 你可能有疑问，这里是日期类型，是不是要和上一次一样，设置单元格样式为日期类型？
* 这回不用了，因为上面已经拷贝了模板的样式，生日一栏就是按日期类型展示的
*/
XSSFCell birthdayCell = row.createCell(3);
birthdayCell.setCellValue(student.getBirthday());
birthdayCell.setCellStyle(cellStyles[3]);
XSSFCell heightCell = row.createCell(4);
heightCell.setCellValue(student.getHeight());
heightCell.setCellStyle(cellStyles[4]);
XSSFCell mainLandChinaCell = row.createCell(5);
mainLandChinaCell.setCellValue(student.getIsMainlandChina());
mainLandChinaCell.setCellStyle(cellStyles[5]);
}
/**
* 之前通过本地文件流输出到桌面：
* FileOutputStream out = new FileOutputStream("/Users/kevin/Documents/study/student_info_export.xlsx");
* 现在用网络流：response.getOutputStream()
* 注意，response的响应流没必要手动关闭，交给Tomcat关闭
*/
String fileName = new String("学生信息表.xlsx".getBytes("UTF-8"), "ISO-8859-1");
response.setContentType("application/octet-stream");
response.setHeader("content-disposition", "attachment;filename=" + fileName);
response.setHeader("filename", fileName);
workbook.write(response.getOutputStream());
workbook.close();
logger.info("导出学生信息表成功！");
}
@PostMapping("/importExcel")
public Map importExcel(MultipartFile file) throws Exception {
// 直接获取上传的文件流，传入构造函数
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
// 获取工作表。一个工作薄中可能有多个工作表，比如sheet1 sheet2，可以根据下标，也可以根据sheet名称。这里根据下标即可。
XSSFSheet sheet = workbook.getSheetAt(0);
// 收集每一行数据（跳过标题和表头，所以int i = 2）
int lastRowNum = sheet.getLastRowNum();
List<Student> studentList = new ArrayList<>();
for (int i = 2; i <= lastRowNum; i++) {
// 收集当前行所有单元格的数据
XSSFRow row = sheet.getRow(i);
short lastCellNum = row.getLastCellNum();
List<String> cellDataList = new ArrayList<>();
for (int j = 0; j < lastCellNum; j++) {
cellDataList.add(getValue(row.getCell(j)));
}
// 把当前行数据设置到POJO。由于Excel单元格的顺序和POJO字段顺序一致，也就是数据类型一致，所以可以直接强转
Student student = new Student();
student.setName(cellDataList.get(0));
student.setAge(Integer.parseInt(cellDataList.get(1)));
student.setAddress(cellDataList.get(2));
// getValue()方法返回的是字符串类型的 1962-6-22 00:00:00，这里按"yyyy-MM-dd HH:mm:ss"重新解析为Date
student.setBirthday(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(cellDataList.get(3)));
student.setHeight(Double.parseDouble(cellDataList.get(4)));
student.setHeight(Double.parseDouble(cellDataList.get(4)));
student.setIsMainlandChina(Boolean.valueOf(cellDataList.get(5)));
studentList.add(student);
}
// 插入数据库
saveToDB(studentList);
logger.info("导入{}成功！", file.getOriginalFilename());
Map<String, Object> result = new HashMap<>();
result.put("code", 200);
result.put("data", null);
result.put("msg", "success");
return result;
}
private void saveToDB(List<Student> studentList) {
if (CollectionUtils.isEmpty(studentList)) {
return;
}
// 直接打印，模拟插入数据库
studentList.forEach(System.out::println);
}
/**
* 提供POI数据类型 --> Java数据类型的转换
* 由于本方法返回值设为String，所以不管转换后是什么Java类型，都要以String格式返回
* 所以Date会被格式化为yyyy-MM-dd HH:mm:ss
* 后面根据需要自己另外转换
*
* @param cell
* @return
*/
private String getValue(Cell cell) {
if (cell == null) {
return "";
}
switch (cell.getCellType()) {
case STRING:
return cell.getRichStringCellValue().getString().trim();
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
// DateUtil是POI内部提供的日期工具类，可以把原本是日期类型的NUMERIC转为Java的Data类型
Date javaDate = DateUtil.getJavaDate(cell.getNumericCellValue());
String dateString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(javaDate);
return dateString;
} else {
/*
* 无论Excel中是58还是58.0，数值类型在POI中最终都被解读为Double。
* 这里的解决办法是通过BigDecimal先把Double先转成字符串，如果是.0结尾，把.0去掉
* */
String strCell = "";
Double num = cell.getNumericCellValue();
BigDecimal bd = new BigDecimal(num.toString());
if (bd != null) {
strCell = bd.toPlainString();
}
// 去除 浮点型 自动加的 .0
if (strCell.endsWith(".0")) {
strCell = strCell.substring(0, strCell.indexOf("."));
}
return strCell;
}
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
default:
return "";
}
}
@Data
@NoArgsConstructor
@AllArgsConstructor
static class Student {
private Long id;
private String name;
private Integer age;
private String address;
private Date birthday;
private Double height;
private Boolean isMainlandChina;
}
}
二、狂神说Java(原生POI)
读操作用到的Excel表：会员消费商品明细表
1、创建一个普通的Java项目，导入依赖
<dependencies>
<!--原生POI依赖-->
<!--xLs(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.2</version>
</dependency>
<!--xLsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.2</version>
</dependency>
<!--test-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
2、简要说明
HSSFWorkbook ：对应 Excel 03 版本，最多支持65535行
XSSFWorkbook：对应 Excel 07 版本，行数无限制
SXSSFWorkbook：缓存版本的操作Excel方式。
03版最多支持到65536行，而07版不受限制，理论上无限
3、案例测试
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;
/**
* @ClassName ExcelWriter
* @Author zhangzhixi
* @Description POI操作Excel
* @Date 2022/8/29 13:06
* @Version 1.0
*/
public class ExcelWriterReadTest {
/**
* 将Excel文件写入到什么地方
*/
public final static String WRITER_PATH = "F:\\StudyProject\\狂神说\\Excel操作\\zhjixi-poi\\src\\main\\resources\\";
/**
* 写入Excel-03版本
*/
@Test
public void excelTarget03() {
// 1、创建工作簿
Workbook workbook = new HSSFWorkbook();
// 2、创建工作表
Sheet sheet = workbook.createSheet("考核成绩表");
// 3、创建一行
Row row1 = sheet.createRow(0);
// 4、创建一个列(一个单元格)
Cell cell11 = row1.createCell(0);
cell11.setCellValue("语文");
Cell cell12 = row1.createCell(1);
cell12.setCellValue("数学");
Cell cell13 = row1.createCell(2);
cell13.setCellValue("英语");
// 创建一行
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
Cell cell22 = row2.createCell(1);
Cell cell23 = row2.createCell(2);
cell21.setCellValue("75");
cell22.setCellValue("70");
cell23.setCellValue("66");
// 5、生成工作簿
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(WRITER_PATH + "考核成绩表03.xls");
workbook.write(fileOutputStream);
System.out.println("工作表生成成功！");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
assert fileOutputStream != null;
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 写入Excel-07版本
*/
@Test
public void excelTarget07() {
// 1、创建工作簿
Workbook workbook = new XSSFWorkbook();
// 2、创建工作表
Sheet sheet = workbook.createSheet("考核成绩表");
// 3、创建一行
Row row1 = sheet.createRow(0);
// 4、创建一个列(一个单元格)
Cell cell11 = row1.createCell(0);
cell11.setCellValue("语文");
Cell cell12 = row1.createCell(1);
cell12.setCellValue("数学");
Cell cell13 = row1.createCell(2);
cell13.setCellValue("英语");
// 创建一行
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
Cell cell22 = row2.createCell(1);
Cell cell23 = row2.createCell(2);
cell21.setCellValue("75");
cell22.setCellValue("70");
cell23.setCellValue("66");
// 5、生成工作簿
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(WRITER_PATH + "考核成绩表07.xlsx");
workbook.write(fileOutputStream);
System.out.println("工作表生成成功！");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
assert fileOutputStream != null;
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 写入大数据量Excel-03版本
*/
@Test
public void excelTargetBigData03() {
long beginTime = System.currentTimeMillis();
// 1、创建工作簿
Workbook workbook = new HSSFWorkbook();
// 2、创建工作表
Sheet sheet = workbook.createSheet("考核成绩表");
for (int rowNum = 0; rowNum < 6553; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int coluNum = 0; coluNum < 10; coluNum++) {
Cell cell = row.createCell(coluNum);
cell.setCellValue(coluNum);
}
}
// 5、生成工作簿
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(WRITER_PATH + "考核成绩表大数据写入03.xls");
workbook.write(fileOutputStream);
System.out.println("工作表生成成功！");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
assert fileOutputStream != null;
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
System.out.println("耗时：" + ((double) (System.currentTimeMillis()) - beginTime) / 1000 + "s");
}
/**
* 写入大数据量Excel-07-普通版
* 写入10w条数据：9.149s
*/
@Test
public void excelTargetBigData07() {
long beginTime = System.currentTimeMillis();
// 1、创建工作簿
Workbook workbook = new XSSFWorkbook();
// 2、创建工作表
Sheet sheet = workbook.createSheet("考核成绩表");
for (int rowNum = 0; rowNum < 100000; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int coluNum = 0; coluNum < 10; coluNum++) {
Cell cell = row.createCell(coluNum);
cell.setCellValue(coluNum);
}
}
// 5、生成工作簿
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(WRITER_PATH + "考核成绩表大数据写入07.xlsx");
workbook.write(fileOutputStream);
System.out.println("工作表生成成功！");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
assert fileOutputStream != null;
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
System.out.println("耗时：" + ((double) (System.currentTimeMillis()) - beginTime) / 1000 + "s");
}
/**
* 写入大数据量Excel-07-缓存版本
* 写入10w条数据：2.161s
*/
@Test
public void excelTargetBigDataCache07() {
long beginTime = System.currentTimeMillis();
// 1、创建工作簿
Workbook workbook = new SXSSFWorkbook();
// 2、创建工作表
Sheet sheet = workbook.createSheet("考核成绩表");
for (int rowNum = 0; rowNum < 100000; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int coluNum = 0; coluNum < 10; coluNum++) {
Cell cell = row.createCell(coluNum);
cell.setCellValue(coluNum);
}
}
// 5、生成工作簿
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(WRITER_PATH + "考核成绩表大数据写入Cache07.xls");
workbook.write(fileOutputStream);
System.out.println("工作表生成成功！");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
assert fileOutputStream != null;
fileOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
System.out.println("耗时：" + ((double) (System.currentTimeMillis()) - beginTime) / 1000 + "s");
}
/**
* 读取文件并判断列的类型
*
* @throws Exception 异常
*/
@Test
public void testCellType() throws Exception {
//获取文件流
FileInputStream fis = new FileInputStream(WRITER_PATH + "会员消费商品明细表.xls");
//创建一个工作簿。使用 excel能操作的这边他都可以操作
Workbook workbook = new HSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
//获取标题内容
Row rowTitle = sheet.getRow(0);
if (rowTitle != null) {
//得到一行有多少列有数据
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if (cell != null) {
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + "|");
}
}
System.out.println();
}
// 遍历所有行
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
// 遍历一行中的所有列
if (rowData != null) {
assert rowTitle != null;
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");
Cell cell = rowData.getCell(cellNum);
//匹配列的数据类型
if (cell != null) {
CellType cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
//字符
case STRING:
System.out.print("【 String】");
cellValue = cell.getStringCellValue();
break;
//布尔
case BOOLEAN:
System.out.print("【 BOOLEAN】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
//空
case BLANK:
System.out.print("【 BLANK】");
break;
//数字(日期、普通数字)
case NUMERIC:
System.out.print("【 NUMERIC】");
// 日期
if (DateUtil.isCellDateFormatted(cell)) {
System.out.print("--【日期】");
LocalDateTime localDateTimeCellValue = cell.getLocalDateTimeCellValue();
cellValue = localDateTimeCellValue.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
} else {
//不是日期格式，防止数字过长！
System.out.print("--【转换为字符串输出】");
cell.setCellType(CellType.STRING);
cellValue = cell.toString();
}
break;
//错误
case ERROR:
System.out.print("【 数据类型错误】");
break;
default:
break;
}
System.out.println(cellValue);
}
}
}
}
//关闭流
fis.close();
}
public static int f(String s1, String s2) {
if (s1.length() == 0 || s2.length() == 0) {
return 0;
}
if (s1.charAt(0) == s2.charAt(0)) {
return f(s1.substring(1), s2.substring(1)) + 1;
} else {
return Math.max(f(s1.substring(1), s2), f(s1, s2.substring(1)));
}
}
}
三、EasyExcel(推荐)
官方文档地址：https://easyexcel.opensource.alibaba.com/docs/current/
自己跟着文档练习即可。
导入Maven坐标：
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
四、POI操作：使用自定义注解补充信息（Excel导出）
创建Maven项目，依赖如下
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.3.23</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.2</version>
</dependency>
</dependencies>
定义注解
/**
* @author zhixi
*/
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.TYPE})
@Documented
public @interface Excel {
/**
* 字段别名
*
* @return 字段名称
*/
String name() default "";
/**
* 字典值
*
* @return 字典值，用逗号分割，比如："0=女,1=男"
*/
String dictValue() default "";
}
实体类
public class User {
@Excel(name = "用户ID")
private String id;
@Excel(name = "姓名")
private String name;
@Excel(name = "年龄")
private int age;
/**
* 性别 0-女 1-男
*/
@Excel(name = "性别", dictValue = "0=女,1=男")
private int gender;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getGender() {
return gender;
}
public void setGender(int gender) {
this.gender = gender;
}
@Override
public String toString() {
return "User{" +
"name='" + name + '\'' +
", age=" + age +
", gender=" + gender +
'}';
}
public User(String name, int age, int gender) {
this.name = name;
this.age = age;
this.gender = gender;
}
public User(String id, String name, int age, int gender) {
this.id = id;
this.name = name;
this.age = age;
this.gender = gender;
}
}
导出Excel
package com.zhixi;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.Stream;
public class ExcelExporter {
public static void export(List<User> users, OutputStream outputStream) {
try (Workbook workbook = new XSSFWorkbook()) {
// 创建一个工作表
Sheet sheet = workbook.createSheet();
// 获取所有的属性
Field[] declaredFields = User.class.getDeclaredFields();
// 获取所有包含Excel注解的字段
List<Field> filteredFields = Stream.of(declaredFields)
.filter(f -> f.isAnnotationPresent(Excel.class))
.collect(Collectors.toList());
// 写入标题行
writeTitleRow(workbook,sheet, filteredFields);
// 写入数据行
for (int i = 0; i < users.size(); i++) {
User user = users.get(i);
// 数据从第二行开始写入（一行是标题）
Row userRow = sheet.createRow(i + 1);
writeDataRow(userRow, user, filteredFields);
}
// 写入文件
workbook.write(outputStream);
} catch (IOException | IllegalAccessException e) {
throw new RuntimeException(e);
}
}
private static void writeTitleRow(Workbook workbook,Sheet sheet, List<Field> filteredFields) {
// 创建新行
Row row = sheet.createRow(0);
// 设置表头加粗
CellStyle cellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
cellStyle.setFont(font);
for (int i = 0; i < filteredFields.size(); i++) {
// 在所在行内创建新单元格
Cell cell = row.createCell(i);
Excel excel = filteredFields.get(i).getAnnotation(Excel.class);
cell.setCellValue(excel.name());
cell.setCellStyle(cellStyle);
}
}
private static void writeDataRow(Row userRow, User user, List<Field> filteredFields) throws IllegalAccessException {
// 构造字段的值字典(处理dictValue这个属性)
Map<Field, Map<String, String>> fieldDict = buildFieldDict(filteredFields);
for (int i = 0; i < filteredFields.size(); i++) {
// 在行上面创建列，i表示列的下标
Cell cell = userRow.createCell(i);
Field field = filteredFields.get(i);
field.setAccessible(true);
// dictValue这个属性不为空，就从map字典中取出对应的值
Excel annotation = field.getAnnotation(Excel.class);
if (StringUtils.hasLength(annotation.dictValue())) {
String key = String.valueOf(field.get(user));
cell.setCellValue(fieldDict.get(field).get(key));
} else {
cell.setCellValue(String.valueOf(field.get(user)));
}
field.setAccessible(false);
}
}
private static Map<Field, Map<String, String>> buildFieldDict(List<Field> filteredFields) {
Map<Field, Map<String, String>> fieldDict = new HashMap<>();
for (Field field : filteredFields) {
Excel annotation = field.getAnnotation(Excel.class);
if (StringUtils.hasLength(annotation.dictValue())) {
String d = annotation.dictValue();
String[] kvs = d.split(",");
Map<String, String> map = new HashMap<>();
for (String kv : kvs) {
String[] split = kv.split("=");
map.put(split[0], split[1]);
}
fieldDict.put(field, map);
}
}
return fieldDict;
}
public static void main(String[] args) throws IOException {
List<User> users = new ArrayList<>();
users.add(new User("1", "张三", 20, 1));
users.add(new User("2", "李四", 30, 0));
users.add(new User("3", "王五", 40, 1));
System.out.println(Paths.get(".").getFileName());
OutputStream outputStream = Files.newOutputStream(Paths.get("users.xlsx"));
ExcelExporter.export(users, outputStream);
}
}
