因为项目中之前的做法是用反射获取属性, 所以 demo 中也是用的反射, 我看网上很多文章都是存入一个 List<Map > 中, 不知道这两种哪种更何合适一点, 或者有没有什么更好的方法.
首先引入 pom,lombok 可选
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>3.13</version>
- </dependency>
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi-ooxml</artifactId>
- <version>3.13</version>
- </dependency>
- <dependency>
- <groupId>org.projectlombok</groupId>
- <artifactId>lombok</artifactId>
- <optional>true</optional>
- </dependency>
- entity
- @Data
- @AllArgsConstructor
- public class Student {
- public String name;
- public int score;
- public int age;
- }
- Service
- import org.springframework.stereotype.Service;
- import java.util.ArrayList;
- import java.util.List;
- @Service
- public class StudentService {
- public List<Student> getList(Student student, int index, int size) {
- Student student1 = new Student("张三", 90, 18);
- Student student2 = new Student("李四", 85, 17);
- Student student3 = new Student("王五", 70, 19);
- List<Student> list = new ArrayList<>();
- list.add(student1);
- list.add(student2);
- list.add(student3);
- return list;
- }
- }
- Controller
- import lombok.Cleanup;
- import org.apache.poi.ss.usermodel.Workbook;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.http.HttpHeaders;
- import org.springframework.http.HttpStatus;
- import org.springframework.http.ResponseEntity;
- import org.springframework.web.bind.annotation.RequestMapping;
- import org.springframework.Web.bind.annotation.RestController;
- import java.io.ByteArrayOutputStream;
- import java.io.IOException;
- import java.io.UnsupportedEncodingException;
- import java.NET.URLEncoder;
- import java.util.List;
- @RestController
- public class StudentController {
- @Autowired
- public StudentService studentService;
- @RequestMapping("/exportStudentExcel")
- public ResponseEntity<byte[]> exportExcel(Student student) {
- List<Student> list = studentService.getList(student, 0, 10); // 每次只需要改这几行
- String fileName = "学生成绩统计表"; // 每次只需要改这几行
- String[] getters = {"getName", "getScore", "getAge"}; // 每次只需要改这几行
- String[] headers = {"姓名", "分数", "年龄"}; // 每次只需要改这几行
- Workbook wb = ExcelUtils.createWorkBook(list, getters, headers,student.getClass()); // 每次只需要改这几行
- @Cleanup ByteArrayOutputStream os = new ByteArrayOutputStream();
- try {
- wb.write(os);
- } catch (IOException e) {
- e.printStackTrace();
- }
- byte[] content = os.toByteArray();
- HttpHeaders httpHeaders = new HttpHeaders();
- try {
- fileName = URLEncoder.encode(fileName, "UTF-8");
- } catch (UnsupportedEncodingException e) {
- e.printStackTrace();
- }
- httpHeaders.setContentDispositionFormData("attachment", fileName + ".xlsx");
- return new ResponseEntity<byte[]>(content, httpHeaders, HttpStatus.OK);
- }
- }
- ExcelUtils
- public class ExcelUtils {
- /**
- * 创建 Excel 文档
- *
- * @param getters list 中 map 的 key 数组集合
- * @param headers Excel 的列名
- */
- public static Workbook createWorkBook(List list, String[] getters, String[] headers, Class clazz) {
- List<Method> methods = getMethodsByStrs(getters, clazz);
- // 创建. xlsx 工作簿
- Workbook wb = new XSSFWorkbook();
- // 创建第一个 sheet(页), 并命名
- Sheet sheet = wb.createSheet("sheet1");
- // 手动设置列宽. 第一个参数表示要为第几列设, 第二个参数表示列的宽度, n 为列高的像素数.
- for (int i = 0; i <getters.length; i++) {
- sheet.setColumnWidth((short) i, (short) (35.7 * 200));
- }
- // 创建第一行
- Row header = sheet.createRow(0);
- // 创建两种单元格格式
- CellStyle cellStyle1 = wb.createCellStyle();
- CellStyle cellStyle2 = wb.createCellStyle();
- // 创建两种字体
- Font font1 = wb.createFont(); // 标题字体
- Font font2 = wb.createFont(); // 正文字体
- // 标题加粗
- font1.setBoldweight(Font.BOLDWEIGHT_BOLD);
- // 设置两种单元格的样式
- setCellStype(cellStyle1, font1);
- setCellStype(cellStyle2, font2);
- // 设置 header
- for (int i = 0; i < headers.length; i++) {
- Cell cell = header.createCell(i);
- cell.setCellValue(headers[i]);
- cell.setCellStyle(cellStyle1);
- }
- // 设置 data
- int headersNum = 1;
- for (int i = 0; i < list.size(); i++) {
- Row row = sheet.createRow(i + headersNum);
- for (int j = 0; j < methods.size(); j++) {
- try {
- Object invoke = methods.get(j).invoke(list.get(i));
- if (invoke != null) {
- row.createCell(j).setCellValue(invoke.toString());
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }
- return wb;
- }
- private static void setCellStype(CellStyle cellStyle, Font font) {
- font.setFontHeightInPoints((short) 10);
- font.setColor(IndexedColors.BLACK.getIndex());
- cellStyle.setFont(font);
- cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
- cellStyle.setBorderRight(CellStyle.BORDER_THIN);
- cellStyle.setBorderTop(CellStyle.BORDER_THIN);
- cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
- cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
- }
- private static List<Method> getMethodsByStrs(String[] getters, Class clazz) {
- List<Method> list = new ArrayList<>();
- for (String getter : getters) {
- try {
- list.add(clazz.getDeclaredMethod(getter));
- } catch (NoSuchMethodException e) {
- e.printStackTrace();
- }
- }
- return list;
- }
- }
来源: http://www.bubuko.com/infodetail-3102900.html