pom文件增加依赖
1
2
3
4
5
6
7
8
9
10
| <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
|
做一个excel模板文件放到src/main/resources目录下
最好再建个子目录，这样资源更方便管理。
使用模板调格式会方便很多（宽度、字体、数字格式等等，节省代码，容易修改）
数据导出
下面的例子是写在spring controller内的，如果不是controler，需要变化的只有只有最后返回的部分。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
| @ResponseBody
@GetMapping("/export")
public ResponseEntity<byte[]> exportExcel() throws Exception{
logger.trace("exportExcel");
HttpHeaders responseHeaders = new HttpHeaders();
responseHeaders.setContentDispositionFormData("attachment",new String("导出的文件名.xlsx".getBytes(), "ISO8859-1"));
responseHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
InputStream templateIs = this.getClass().getResourceAsStream("/excel-templates/templet.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(templateIs);
XSSFSheet sheet = workbook.getSheetAt(0);
List<SampleItem> list = getDataList();
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd"));
for (int i=0; i<list.size(); i++) {
SampleItem si = list.get(i);
XSSFRow row = sheet.createRow(i + 1);
Cell cell1 = row.createCell(0);
cell1.setCellValue(si.getDate());
cell1.setCellStyle(cellStyle);
Cell cell2 = row.createCell(1);
cell2.setCellValue(si.getName());
Cell cell3 = row.createCell(2);
cell3.setCellValue(si.getScore());
}
ByteArrayOutputStream bos = new ByteArrayOutputStream();
workbook.write(bos);
workbook.close();
return new ResponseEntity<byte[]>(bos.toByteArray(), responseHeaders, HttpStatus.OK);
}
|