因为项目中之前的做法是用反射获取属性，所以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;
}
}
内容来源于网络如有侵权请私信删除
- 还没有人评论，欢迎说说您的想法！
客服
