1.Maven需要的依赖
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-examples -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-excelant -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.14</version>
</dependency>
2.导入示例
1 /**
2 * 黑名单查询
3 * @param blackListBean
4 * @return
5 */
6 @RequestMapping(value = "/blackList", method = RequestMethod.POST)
7 @ResponseBody
8 public BaseReturnModel blackList(
9 @RequestBody BlackListBean blackListBean
10 ) {
11 BaseReturnModel baseReturnModel = new BaseReturnModel();
12 List<BlackListBean> resultList = null;
13 int total = 0;
14 try {
15 if (blackListBean.getDownLoad() == null) {
16 int start = (blackListBean.getPagination() - 1) * blackListBean.getRownum(); // 分页处理 //rownum页数
17 blackListBean.setStart(start);
18 }
19 if (blackListBean.getUserIds() != null && !"".equals(blackListBean.getUserIds())) {
20 blackListBean.setIdUsers(blackListBean.getUserIds().split(","));
21 }
22 resultList = blackListSerivce.selectBlackList(blackListBean);
23 total = blackListSerivce.selectResult(blackListBean);
24
25 } catch (Exception e) {
26 // TODO: handle exception
27 e.printStackTrace();
28 baseReturnModel.setCode(200);// 状态
29 baseReturnModel.setMess("系统错误");
30 }
31 if (blackListBean.getDownLoad() != null) {
32 if (resultList.size() > 0) {
33 //创建一个表格
34 SXSSFWorkbook workbook = new SXSSFWorkbook();
35 //创建一个工作表
36 SXSSFSheet sheet = workbook.createSheet("sheet1");
37 //设置表头行
38 SXSSFRow xssfRow = sheet.createRow(0);
39 //设置单元格格式居中
40 CellStyle cellStyle = workbook.createCellStyle();
41 cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
42 //添加表头内容
43 SXSSFCell headCell = xssfRow.createCell(0);
44 headCell.setCellValue("站点ID");
45 headCell.setCellStyle(cellStyle);
46 // 单元格列宽 0代表列位置
47 sheet.setColumnWidth(0, 5500);
48 sheet.setColumnWidth(1, 5500);
49 sheet.setColumnWidth(2, 5500);
50 sheet.setColumnWidth(3, 5500);
51 sheet.setColumnWidth(4, 5500);
52
53 //设置每列的参数
54 headCell = xssfRow.createCell(1);
55 headCell.setCellValue("站点名称");
56 headCell.setCellStyle(cellStyle);
57
58 headCell = xssfRow.createCell(2);
59 headCell.setCellValue("黑名单手机号/单号");
60 headCell.setCellStyle(cellStyle);
61
62 headCell = xssfRow.createCell(3);
63 headCell.setCellValue("理由");
64 headCell.setCellStyle(cellStyle);
65
66 headCell = xssfRow.createCell(4);
67 headCell.setCellValue("时间");
68 headCell.setCellStyle(cellStyle);
69
70
71 int b = 1;
72 String s = JSON.toJSONString(resultList);
73 SimpleDateFormat sdf = new SimpleDateFormat
74 ("yyyy-MM-dd HH:mm:ss");
75 List<BlackListBean> list = JSON.parseArray(s, BlackListBean.class);
76 for (BlackListBean obj : list) {
77 xssfRow = sheet.createRow(b++);
78
79 //时间戳转换
80 Long aLong = new Long(obj.getCreateTime());
81 Date date = new Date(aLong);
82
83 //创建单元格,并添加值
84 SXSSFCell cell = xssfRow.createCell(0);
85 cell.setCellValue(obj.getIdUser());
86 cell.setCellStyle(cellStyle);
87
88 cell = xssfRow.createCell(1);
89 cell.setCellValue(obj.getName());
90 cell.setCellStyle(cellStyle);
91
92 cell = xssfRow.createCell(2);
93 cell.setCellValue(obj.getMobile());
94 cell.setCellStyle(cellStyle);
95
96 cell = xssfRow.createCell(3);
97 cell.setCellValue(obj.getNote());
98 cell.setCellStyle(cellStyle);
99
100 cell = xssfRow.createCell(4);
101 cell.setCellValue(sdf.format(date));
102 cell.setCellStyle(cellStyle);
103 }
104 //保存文件
105 String imgpath = "/usr/local/tomcat/apache-tomcat-8.5.32/webapps/excel/";
106 File file = new File(imgpath);
107 if (!file.exists()) {
108 file.mkdirs();
109 }
110 try {
111 String imgFilePath = imgpath + "blackList" + ".xlsx";
112 OutputStream outputStream = new FileOutputStream(imgFilePath);
113 baseReturnModel.setObj("/excel/" + "blackList" + ".xlsx");
114 baseReturnModel.setCode(200);
115 baseReturnModel.setMess("正在下载");
116
117 try {
118 workbook.write(outputStream);
119 } catch (IOException e) {
120 e.printStackTrace();
121 baseReturnModel.setCode(500);
122 baseReturnModel.setMess("写入失败");
123 baseReturnModel.setObj("");
124 }
125
126 try {
127 outputStream.close();
128 workbook.close();
129 } catch (IOException e) {
130 e.printStackTrace();
131 }
132
133 } catch (FileNotFoundException e) {
134 e.printStackTrace();
135 baseReturnModel.setCode(500);
136 baseReturnModel.setMess("下载失败");
137 }
138 return baseReturnModel;
139 } else {
140 baseReturnModel.setCode(500);
141 baseReturnModel.setMess("没有可提供下载得内容");
142 return baseReturnModel;
143 }
144 }
145 baseReturnModel.setCode(200);// 状态
146 baseReturnModel.setMess("成功");
147 baseReturnModel.setObj(resultList);
148 baseReturnModel.setTotal(total);
149 return baseReturnModel;
150 }
