java excel查询数据库_java将数据库中查询到的数据导入到Excel表格

1 /**

2 * 黑名单查询3 *@paramblackListBean4 *@return

5 */

6 @RequestMapping(value = "/blackList", method =RequestMethod.POST)7 @ResponseBody8 publicBaseReturnModel blackList(9 @RequestBody BlackListBean blackListBean10 ) {11 BaseReturnModel baseReturnModel = newBaseReturnModel();12 List 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 = newSXSSFWorkbook();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 = newSimpleDateFormat74 ("yyyy-MM-dd HH:mm:ss");75 List list = JSON.parseArray(s, BlackListBean.class);76 for(BlackListBean obj : list) {77 xssfRow = sheet.createRow(b++);78

79 //时间戳转换

80 Long aLong = newLong(obj.getCreateTime());81 Date date = newDate(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 = newFile(imgpath);107 if (!file.exists()) {108 file.mkdirs();109 }110 try{111 String imgFilePath = imgpath + "blackList" + ".xlsx";112 OutputStream outputStream = newFileOutputStream(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 returnbaseReturnModel;139 } else{140 baseReturnModel.setCode(500);141 baseReturnModel.setMess("没有可提供下载得内容");142 returnbaseReturnModel;143 }144 }145 baseReturnModel.setCode(200);//状态

146 baseReturnModel.setMess("成功");147 baseReturnModel.setObj(resultList);148 baseReturnModel.setTotal(total);149 returnbaseReturnModel;150 }