poi

admin管理员组

文章数量:1122850

poi

1.效果图

2.Java代码

/*** * @Title: exportExcel * @Description: 导出excel* @param request* @param response* @return* @throws Exception * @author 大都督* @date 2018年11月30日* @return String*/@RequestMapping(value = "/export_excel", method = RequestMethod.GET)public String exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception{return poiService.exportExcel(response);}

@Overridepublic String exportExcel(HttpServletResponse response) throws Exception {HSSFWorkbook wb = new HSSFWorkbook();String sheetName = "测试";HSSFSheet sheet = wb.createSheet(sheetName);HSSFCellStyle style = wb.createCellStyle();style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式sheet.setDefaultColumnWidth(15);//合并单元格（行开始，行结束，列开始，列结束）从零开始sheet.addMergedRegion(new CellRangeAddress(0,0,0,8));sheet.addMergedRegion(new CellRangeAddress(1,1,1,8));sheet.addMergedRegion(new CellRangeAddress(2,2,1,2));sheet.addMergedRegion(new CellRangeAddress(2,2,3,4));sheet.addMergedRegion(new CellRangeAddress(2,2,5,8));sheet.addMergedRegion(new CellRangeAddress(3,3,1,8));sheet.addMergedRegion(new CellRangeAddress(4,7,0,0));sheet.addMergedRegion(new CellRangeAddress(5,7,1,1));sheet.addMergedRegion(new CellRangeAddress(5,7,2,2));sheet.addMergedRegion(new CellRangeAddress(5,7,8,8));HSSFRow row = null;HSSFCell cell = null;//第1行row = sheet.createRow(0);//第1行-第1列cell = row.createCell(0);cell.setCellValue("A1-I1");cell.setCellStyle(style);//第2行row = sheet.createRow(1);//第2行-第1列cell = row.createCell(0);cell.setCellValue("A2");cell.setCellStyle(style);cell = row.createCell(1);cell.setCellValue("B2-I2");cell.setCellStyle(style);//第3行row = sheet.createRow(2);//第3行-第1列cell = row.createCell(0);cell.setCellValue("A3");cell.setCellStyle(style);//第3行-第2列cell = row.createCell(1);cell.setCellValue("B3-C3");cell.setCellStyle(style);//第3行-第3列cell = row.createCell(3);cell.setCellValue("D3-E3");cell.setCellStyle(style);//第3行-第4列cell = row.createCell(5);cell.setCellValue("F3-I3");cell.setCellStyle(style);//第4行row = sheet.createRow(3);//第4行-第1列cell = row.createCell(0);cell.setCellValue("A4");cell.setCellStyle(style);//第4行-第2列cell = row.createCell(1);cell.setCellValue("B4-I4");cell.setCellStyle(style);//第5行row = sheet.createRow(4);cell = row.createCell(0);cell.setCellValue("A5-A8");cell.setCellStyle(style);cell = row.createCell(1);cell.setCellValue("姓名");cell.setCellStyle(style);cell = row.createCell(2);cell.setCellValue("电话");cell.setCellStyle(style);cell = row.createCell(3);cell.setCellValue("");cell.setCellStyle(style);cell = row.createCell(4);cell.setCellValue("一级");cell.setCellStyle(style);cell = row.createCell(5);cell.setCellValue("特约");cell.setCellStyle(style);cell = row.createCell(6);cell.setCellValue("品牌");cell.setCellStyle(style);cell = row.createCell(7);cell.setCellValue("零售");cell.setCellStyle(style);cell = row.createCell(8);cell.setCellValue("总金额");cell.setCellStyle(style);int i = 0, num = 4;row = sheet.createRow(i+num);cell = row.createCell(0);cell.setCellValue("A5-A8");cell.setCellStyle(style);cell = row.createCell(1);cell.setCellValue("B5");cell.setCellStyle(style);cell = row.createCell(2);cell.setCellValue("C5");cell.setCellStyle(style);cell = row.createCell(3);cell.setCellValue("D5");cell.setCellStyle(style);cell = row.createCell(4);cell.setCellValue("E5");cell.setCellStyle(style);cell = row.createCell(5);cell.setCellValue("F5");cell.setCellStyle(style);cell = row.createCell(6);cell.setCellValue("G5");cell.setCellStyle(style);cell = row.createCell(7);cell.setCellValue("H5");cell.setCellStyle(style);cell = row.createCell(8);cell.setCellValue("I5");cell.setCellStyle(style);//第i+5行row = sheet.createRow(i+num+1);cell = row.createCell(1);cell.setCellValue("B6-B8");cell.setCellStyle(style);cell = row.createCell(2);cell.setCellValue("C6-C8");cell.setCellStyle(style);cell = row.createCell(3);cell.setCellValue("D6");cell.setCellStyle(style);cell = row.createCell(4);cell.setCellValue("E6");cell.setCellStyle(style);cell = row.createCell(5);cell.setCellValue("F6");cell.setCellStyle(style);cell = row.createCell(6);cell.setCellValue("G6");cell.setCellStyle(style);cell = row.createCell(7);cell.setCellValue("H6");cell.setCellStyle(style);cell = row.createCell(8);cell.setCellValue("I6-I8");cell.setCellStyle(style);//第i+6行row = sheet.createRow(i+num+2);cell = row.createCell(3);cell.setCellValue("D7");cell.setCellStyle(style);cell = row.createCell(4);cell.setCellValue("E7");cell.setCellStyle(style);cell = row.createCell(5);cell.setCellValue("F7");cell.setCellStyle(style);cell = row.createCell(6);cell.setCellValue("G7");cell.setCellStyle(style);cell = row.createCell(7);cell.setCellValue("H7");cell.setCellStyle(style);//第i+7行row = sheet.createRow(i+num+3);cell = row.createCell(3);cell.setCellValue("D8");cell.setCellStyle(style);cell = row.createCell(4);cell.setCellValue("E8");cell.setCellStyle(style);cell = row.createCell(5);cell.setCellValue("F8");cell.setCellStyle(style);cell = row.createCell(6);cell.setCellValue("G8");cell.setCellStyle(style);cell = row.createCell(7);cell.setCellValue("H8");cell.setCellStyle(style);ByteArrayOutputStream os = new ByteArrayOutputStream();try {wb.write(os);} catch (IOException e) {e.printStackTrace();}byte[] content = os.toByteArray();InputStream is = new ByteArrayInputStream(content);// 设置response参数，可以打开下载页面response.reset();response.setContentType("application/vnd.ms-excel;charset=utf-8");response.setHeader("Content-Disposition", "attachment;filename=" + new String((sheetName).getBytes(), "iso-8859-1"));ServletOutputStream out = response.getOutputStream();BufferedInputStream bis = null;BufferedOutputStream bos = null;try {bis = new BufferedInputStream(is);bos = new BufferedOutputStream(out);byte[] buff = new byte[2048];int bytesRead;// Simple read/write loop.while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {bos.write(buff, 0, bytesRead);}} catch (final IOException e) {throw e;} finally {if (bis != null)bis.close();if (bos != null)bos.close();}return null;}

打开浏览器直接访问该接口地址即可自动生成excel表格

本文标签： poi