本文不建議閱讀捡遍。作者已有更好的解決方案:戳這里
前言
最近做公司項(xiàng)目，用到了POI實(shí)現(xiàn)Excel導(dǎo)入導(dǎo)出的功能竹握。
整個(gè)功能做下來(lái)画株，發(fā)現(xiàn)很多大牛的文章都已經(jīng)過(guò)時(shí)，會(huì)報(bào)類似The method setBorderBottom(short) from the type HSSFCellStyle is deprecated的警告涩搓，直接使用@SupressWarnings忽略警告顯然是不負(fù)責(zé)任的。因此我直接去翻了Apache POI的官方文檔劈猪，使用了目前官方推薦的新實(shí)現(xiàn)方法昧甘。
順便發(fā)出來(lái)，方便沒(méi)有翻閱英文官方文檔習(xí)慣的大家战得。
過(guò)兩天發(fā)實(shí)現(xiàn)導(dǎo)入Excel的示例文章充边。
項(xiàng)目結(jié)構(gòu)簡(jiǎn)介
- Student.java:學(xué)生實(shí)體。
- StudentService.java:業(yè)務(wù)類常侦。
-
StudentController.java: web層浇冰。
（具體每一步的介紹在代碼中都有詳細(xì)的解釋，此處不再贅述聋亡。如果疑惑的地方歡迎留言討論肘习。）
Excel導(dǎo)出代碼展示
Student.java
@Entity
@NoArgsConstructor
@Getter
@Setter
public class Student implements Serializable{
/**
* @fields serialVersionUID
*/
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(generator = "idGenerator")
@GenericGenerator(name = "idGenerator", strategy = "uuid")
private String id;
private String sName;
}
StudentService.java(僅貼出實(shí)現(xiàn)導(dǎo)出功能的方法)
public void exportExcel(String[] ids, OutputStream out) {
// 根據(jù)傳入的id將要導(dǎo)出的Student對(duì)象放入集合中
List<Student> list = new ArrayList<>();
for (int i = 0; i < ids.length; i++) {
Student student = repository.findOne(ids[i]);
list.add(student);
}
// 設(shè)置excel表頭數(shù)據(jù)
String[] headers = { "學(xué)生ID", "學(xué)生姓名"};
// 創(chuàng)建excel工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 創(chuàng)建sheet
HSSFSheet sheet = workbook.createSheet("數(shù)據(jù)導(dǎo)出");
// 設(shè)置默認(rèn)列寬為15
sheet.setDefaultColumnWidth(15);
// 合并標(biāo)題欄單元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
// 定義標(biāo)題欄樣式
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColorPredefined.SKY_BLUE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont font = workbook.createFont();
font.setColor(HSSFColorPredefined.VIOLET.getIndex());
font.setFontHeightInPoints((short) 18);
style.setFont(font);
// 定義表頭樣式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColorPredefined.GREEN.getIndex());
style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style2.setBorderBottom(BorderStyle.THIN);
style2.setBorderLeft(BorderStyle.THIN);
style2.setBorderRight(BorderStyle.THIN);
style2.setBorderTop(BorderStyle.THIN);
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont font2 = workbook.createFont();
font2.setFontHeightInPoints((short) 12);
style2.setFont(font2);
// 定義數(shù)據(jù)行樣式
HSSFCellStyle style3 = workbook.createCellStyle();
style3.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex());
style3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style3.setBorderBottom(BorderStyle.THIN);
style3.setBorderLeft(BorderStyle.THIN);
style3.setBorderRight(BorderStyle.THIN);
style3.setBorderTop(BorderStyle.THIN);
style3.setAlignment(HorizontalAlignment.CENTER);
style3.setVerticalAlignment(VerticalAlignment.CENTER);
// 創(chuàng)建行、單元格對(duì)象
HSSFRow row = null;
HSSFCell cell = null;
// 寫(xiě)入標(biāo)題行
row = sheet.createRow(0);
row.setHeightInPoints(25);
cell = row.createCell(0);
cell.setCellStyle(style);
HSSFRichTextString textTitle = new HSSFRichTextString("數(shù)據(jù)導(dǎo)出");
cell.setCellValue(textTitle);
// 寫(xiě)入表頭
row = sheet.createRow(1);
row.setHeightInPoints(17);
for (int i = 0; i < headers.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(style2);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
// 寫(xiě)入數(shù)據(jù)行
Iterator<Student> it = list.iterator();
int index = 2;
while (it.hasNext()) {
row = sheet.createRow(index++);
Student student = (Student) it.next();
cell = row.createCell(0);
cell.setCellStyle(style3);
cell.setCellValue(student.getId());
cell = row.createCell(1);
cell.setCellStyle(style3);
cell.setCellValue(student.getSName());
}
// 獲取輸出流寫(xiě)文件坡倔。
try {
workbook.write(out);
out.flush();
workbook.close();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
StudentController.java(僅貼出實(shí)現(xiàn)導(dǎo)出功能的方法)
@RequestMapping(value = "/student/export" , method = RequestMethod.GET)
public void exporMemberFormExcel(@RequestParam(value = "ids", defaultValue = "", required = false) String[] ids, HttpServletResponse res) throws IOException {
logger.info("---------------導(dǎo)出列表到Excel--------------------");
res.setContentType("application/msexcel;charset=UTF-8");
res.addHeader("Content-Disposition", "attachment;filename=members.xls");
OutputStream out = res.getOutputStream();
studentService.exportExcel(ids, out);
}
