1.Maven需要的依赖
1<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> 2<dependency> 3 <groupId>org.apache.poi</groupId> 4 <artifactId>poi</artifactId> 5 <version>3.14</version> 6</dependency> 7 8 9 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> 10 <dependency> 11 <groupId>org.apache.poi</groupId> 12 <artifactId>poi-ooxml</artifactId> 13 <version>3.14</version> 14 </dependency> 15 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-examples --> 16 <dependency> 17 <groupId>org.apache.poi</groupId> 18 <artifactId>poi-examples</artifactId> 19 <version>3.14</version> 20 </dependency> 21 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-excelant --> 22 <dependency> 23 <groupId>org.apache.poi</groupId> 24 <artifactId>poi-excelant</artifactId> 25 <version>3.14</version> 26 </dependency> 27 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas --> 28 <dependency> 29 <groupId>org.apache.poi</groupId> 30 <artifactId>poi-ooxml-schemas</artifactId> 31 <version>3.14</version> 32 </dependency>
2.导入示例
1 1 /** 2 2 * 黑名单查询 3 3 * @param blackListBean 4 4 * @return 5 5 */ 6 6 @RequestMapping(value = "/blackList", method = RequestMethod.POST) 7 7 @ResponseBody 8 8 public BaseReturnModel blackList( 9 9 @RequestBody BlackListBean blackListBean 10 10 ) { 11 11 BaseReturnModel baseReturnModel = new BaseReturnModel(); 12 12 List<BlackListBean> resultList = null; 13 13 int total = 0; 14 14 try { 15 15 if (blackListBean.getDownLoad() == null) { 16 16 int start = (blackListBean.getPagination() - 1) * blackListBean.getRownum(); // 分页处理 //rownum页数 17 17 blackListBean.setStart(start); 18 18 } 19 19 if (blackListBean.getUserIds() != null && !"".equals(blackListBean.getUserIds())) { 20 20 blackListBean.setIdUsers(blackListBean.getUserIds().split(",")); 21 21 } 22 22 resultList = blackListSerivce.selectBlackList(blackListBean); 23 23 total = blackListSerivce.selectResult(blackListBean); 24 24 25 25 } catch (Exception e) { 26 26 // TODO: handle exception 27 27 e.printStackTrace(); 28 28 baseReturnModel.setCode(200);// 状态 29 29 baseReturnModel.setMess("系统错误"); 30 30 } 31 31 if (blackListBean.getDownLoad() != null) { 32 32 if (resultList.size() > 0) { 33 33 //创建一个表格 34 34 SXSSFWorkbook workbook = new SXSSFWorkbook(); 35 35 //创建一个工作表 36 36 SXSSFSheet sheet = workbook.createSheet("sheet1"); 37 37 //设置表头行 38 38 SXSSFRow xssfRow = sheet.createRow(0); 39 39 //设置单元格格式居中 40 40 CellStyle cellStyle = workbook.createCellStyle(); 41 41 cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); 42 42 //添加表头内容 43 43 SXSSFCell headCell = xssfRow.createCell(0); 44 44 headCell.setCellValue("站点ID"); 45 45 headCell.setCellStyle(cellStyle); 46 46 // 单元格列宽 0代表列位置 47 47 sheet.setColumnWidth(0, 5500); 48 48 sheet.setColumnWidth(1, 5500); 49 49 sheet.setColumnWidth(2, 5500); 50 50 sheet.setColumnWidth(3, 5500); 51 51 sheet.setColumnWidth(4, 5500); 52 52 53 53 //设置每列的参数 54 54 headCell = xssfRow.createCell(1); 55 55 headCell.setCellValue("站点名称"); 56 56 headCell.setCellStyle(cellStyle); 57 57 58 58 headCell = xssfRow.createCell(2); 59 59 headCell.setCellValue("黑名单手机号/单号"); 60 60 headCell.setCellStyle(cellStyle); 61 61 62 62 headCell = xssfRow.createCell(3); 63 63 headCell.setCellValue("理由"); 64 64 headCell.setCellStyle(cellStyle); 65 65 66 66 headCell = xssfRow.createCell(4); 67 67 headCell.setCellValue("时间"); 68 68 headCell.setCellStyle(cellStyle); 69 69 70 70 71 71 int b = 1; 72 72 String s = JSON.toJSONString(resultList); 73 73 SimpleDateFormat sdf = new SimpleDateFormat 74 74 ("yyyy-MM-dd HH:mm:ss"); 75 75 List<BlackListBean> list = JSON.parseArray(s, BlackListBean.class); 76 76 for (BlackListBean obj : list) { 77 77 xssfRow = sheet.createRow(b++); 78 78 79 79 //时间戳转换 80 80 Long aLong = new Long(obj.getCreateTime()); 81 81 Date date = new Date(aLong); 82 82 83 83 //创建单元格,并添加值 84 84 SXSSFCell cell = xssfRow.createCell(0); 85 85 cell.setCellValue(obj.getIdUser()); 86 86 cell.setCellStyle(cellStyle); 87 87 88 88 cell = xssfRow.createCell(1); 89 89 cell.setCellValue(obj.getName()); 90 90 cell.setCellStyle(cellStyle); 91 91 92 92 cell = xssfRow.createCell(2); 93 93 cell.setCellValue(obj.getMobile()); 94 94 cell.setCellStyle(cellStyle); 95 95 96 96 cell = xssfRow.createCell(3); 97 97 cell.setCellValue(obj.getNote()); 98 98 cell.setCellStyle(cellStyle); 99 99 100100 cell = xssfRow.createCell(4); 101101 cell.setCellValue(sdf.format(date)); 102102 cell.setCellStyle(cellStyle); 103103 } 104104 //保存文件 105105 String imgpath = "/usr/local/tomcat/apache-tomcat-8.5.32/webapps/excel/"; 106106 File file = new File(imgpath); 107107 if (!file.exists()) { 108108 file.mkdirs(); 109109 } 110110 try { 111111 String imgFilePath = imgpath + "blackList" + ".xlsx"; 112112 OutputStream outputStream = new FileOutputStream(imgFilePath); 113113 baseReturnModel.setObj("/excel/" + "blackList" + ".xlsx"); 114114 baseReturnModel.setCode(200); 115115 baseReturnModel.setMess("正在下载"); 116116 117117 try { 118118 workbook.write(outputStream); 119119 } catch (IOException e) { 120120 e.printStackTrace(); 121121 baseReturnModel.setCode(500); 122122 baseReturnModel.setMess("写入失败"); 123123 baseReturnModel.setObj(""); 124124 } 125125 126126 try { 127127 outputStream.close(); 128128 workbook.close(); 129129 } catch (IOException e) { 130130 e.printStackTrace(); 131131 } 132132 133133 } catch (FileNotFoundException e) { 134134 e.printStackTrace(); 135135 baseReturnModel.setCode(500); 136136 baseReturnModel.setMess("下载失败"); 137137 } 138138 return baseReturnModel; 139139 } else { 140140 baseReturnModel.setCode(500); 141141 baseReturnModel.setMess("没有可提供下载得内容"); 142142 return baseReturnModel; 143143 } 144144 } 145145 baseReturnModel.setCode(200);// 状态 146146 baseReturnModel.setMess("成功"); 147147 baseReturnModel.setObj(resultList); 148148 baseReturnModel.setTotal(total); 149149 return baseReturnModel; 150150 }