Poiexcel导出
1.无模板导出
exportGzp() { this.$confirm( '可能占用较长一段时间，确定要导出吗?', {}, { confirmButtonText: '确定', cancelButtonText: '取消', type: 'success', center: true } ) .then(() => { exportExcel('/a/b', { params: JSON.stringify( this.query ) }) .then(res => { const link = document.createElement('a'); let blob = new Blob([res.data], { type: 'application/vnd.ms-excel' }); link.style.display = 'none'; link.href = URL.createObjectURL(blob); // eslint-disable-next-line no-unused-vars let num = ''; for (let i = 0; i < 10; i++) { num += Math.ceil(Math.random() * 10); } link.setAttribute('download', '信息' + '.xls'); document.body.appendChild(link); link.click(); document.body.removeChild(link); }) .catch(error => { console.log(error); }); }) .catch(() => {}); },
@GetMapping(value ="/a") @ApiOperation(value ="导出") @ApiImplicitParams({ @ApiImplicitParam(name = "params",value = "",dataType = "String"), }) public void a(String params,HttpServletResponse response){ service.a(params,response); }
OutputStream out = null; try { HSSFWorkbook workbook = new HSSFWorkbook();//创建Excel文件(Workbook) HSSFSheet sheet = workbook.createSheet();//创建工作表(Sheet) sheet.setDefaultColumnWidth(30);//设置工作表的默认行宽 String[] exportTitleArray = {"序号", "类型.", "编号", "运号", "内容", "人", "状态", "计划", "结束"};//得到标题行 HSSFCellStyle titleStype = titleStype(workbook); //创建标题行 HSSFRow row = sheet.createRow(0); for (int i = 0; i < exportTitleArray.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(exportTitleArray[i]); cell.setCellStyle(titleStype); } //创建值行 HSSFCellStyle listStype = ListStype(workbook); for (int i = 0; i < entityList.size(); i++) { HSSFRow listRow = sheet.createRow(i + 1); Map<String, Object> m = entityList.get(i); //序号 HSSFCell cell = listRow.createCell(0); cell.setCellStyle(listStype); cell.setCellValue(i + 1); //类型 HSSFCell cell1 = listRow.createCell(1); cell1.setCellStyle(listStype); if (m.get("") != null) { cell1.setCellValue(m.get("").toString()); } //编号 HSSFCell cell2 = listRow.createCell(2); cell2.setCellStyle(listStype); if (m.get("") != null) { cell2.setCellValue(m.get("").toString()); } //运 HSSFCell cell3 = listRow.createCell(3); cell3.setCellStyle(listStype); if (m.get("") != null) { cell3.setCellValue(m.get("").toString()); } //工 HSSFCell cell4 = listRow.createCell(4); cell4.setCellStyle(listStype); if (m.get("") != null) { cell4.setCellValue(m.get("").toString()); } //工 HSSFCell cell5 = listRow.createCell(5); cell5.setCellStyle(listStype); if (m.get("") != null) { cell5.setCellValue(m.get("").toString()); } //状态 HSSFCell cell6 = listRow.createCell(6); cell6.setCellStyle(listStype); if (m.get("") != null) { cell6.setCellValue(m.get("").toString()); } //开始 HSSFCell cell10 = listRow.createCell(7); cell10.setCellStyle(listStype); if (m.get("") != null) { cell10.setCellValue(m.get("").toString()); } //结束 HSSFCell cell11 = listRow.createCell(8); cell11.setCellStyle(listStype); if (m.get("") != null) { cell11.setCellValue(m.get("").toString()); } } response.setContentType("application/ms-excel;charset=UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode("信息.xls", "UTF-8"))));//设置选择导出地址的 out = response.getOutputStream(); workbook.write(out);// 将数据写出去 out.flush(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (out != null) { out.close(); } } catch (IOException e) { e.printStackTrace(); } }
//导出标题样式 private HSSFCellStyle titleStype(HSSFWorkbook workbook) { HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderBottom(BorderStyle.THIN); //下边框 cellStyle.setBorderLeft(BorderStyle.THIN); //左边框 cellStyle.setBorderRight(BorderStyle.THIN); //右边框 cellStyle.setBorderTop(BorderStyle.THIN); //上边框 //水平居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置单元格内容垂直居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 创建字体 HSSFFont font = workbook.createFont(); //设置为粗体 font.setBold(true); cellStyle.setFont(font); //设置自动换行 cellStyle.setWrapText(true); //设置背景颜色 cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); //solid 填充 foreground 前景色 cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); return cellStyle; } //导出list样式 private HSSFCellStyle ListStype(HSSFWorkbook workbook) { HSSFCellStyle cellStyle = workbook.createCellStyle(); //水平居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置单元格内容垂直居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置自动换行 cellStyle.setWrapText(false); cellStyle.setBorderBottom(BorderStyle.THIN); //下边框 cellStyle.setBorderLeft(BorderStyle.THIN); //左边框 cellStyle.setBorderRight(BorderStyle.THIN); //右边框 cellStyle.setBorderTop(BorderStyle.THIN); //上边框 return cellStyle; }
2.模板导出
private void ex(List<TSbtzFjzz> list, HttpServletResponse response){ Workbook wbs =null; OutputStream out =null; FileInputStream inputStream =null; try { // 将文档模板转化流 inputStream = new FileInputStream(templateFilePath+"/sb.xlsx"); //创建工作簿 wbs = new XSSFWorkbook(inputStream); // 使用的是模板，所以已经创建了表格的，获取就行。 Sheet sheet = wbs.getSheetAt(0); CellStyle style = ListStype(wbs); for (TSbtzFjzz jbxx:list) { Row row = sheet.createRow(rowNumber); row.createCell(0).setCellValue(jbxx()); row.createCell(1).setCellValue(jbxx()); } response.setContentType("application/ms-excel;charset=UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode("设备.xlsx", "UTF-8")))); out=response.getOutputStream(); wbs.write(out);// 将数据写出去 out.flush(); } catch (IOException e) { e.printStackTrace(); }finally { try { if(inputStream!=null){ inputStream.close(); // 关闭流 } if(wbs!=null){ wbs.close(); // 关闭流 } if(out!=null){ out.close(); // 关闭流 } } catch (IOException e) { e.printStackTrace(); } } }
