赞
踩
推荐使用easyExcel,简单好用
对于稍微复杂一点的表格,个人建议用jxl
以下代码中包含的操作:
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
利用jxl最后生成的表格:
@GetMapping("/jxl") public void jxlExport(HttpServletResponse response) throws Exception { String fileName = "jxl_test.xls"; response.reset(); response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8")); //不能是ServletOutputStream OutputStream outputStream = response.getOutputStream(); //创建工作簿 WritableWorkbook workbook = Workbook.createWorkbook(outputStream); //创建sheet WritableSheet sheet = workbook.createSheet("意向院校", 0); //设置表头1 String header1 = "院校信息汇总表"; WritableFont writableFont = new WritableFont(WritableFont.createFont("黑体"),17); WritableCellFormat format = new WritableCellFormat(writableFont); //居中 format.setAlignment(Alignment.CENTRE); //合并单元格,从(0,0)到(0,6) sheet.mergeCells(0, 0, 6, 0); sheet.addCell(new Label(0,0,header1,format)); //设置表头2 String[] headers2 = {"序号", "学校名称", "是否985", "是否211", "是否双一流", "地域", "学校类型"}; //为表头2单元格 设置字体格式:11号宋体,加粗,无下划线,红色字体 WritableFont headerFont = new WritableFont(WritableFont.createFont("宋体"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED); WritableCellFormat headerFormat = new WritableCellFormat(headerFont); //为表头2设置单元格格式 headerFormat.setAlignment(Alignment.CENTRE); //设置居中 headerFormat.setBorder(Border.ALL, BorderLineStyle.THIN); //设置边框线 headerFormat.setBackground(Colour.GOLD); //设置单元格的背景颜色 //在第1行创建表头2的单元格 for (int i = 0; i < headers2.length; i++) { //c:列 r:行 Label label = new Label(i, 1, headers2[i], headerFormat); sheet.addCell(label); } //设置数据单元格字体:11号宋体 WritableFont font = new WritableFont(WritableFont.createFont("宋体"),11); WritableCellFormat cellFormat = new WritableCellFormat(font); //原始数据 List<RecSchool> recSchools = recSchoolMapper.findAll(); //从第2行开始创建数据 for (int i = 2; i < recSchools.size()+2; i++) { sheet.addCell(new Label(0,i, "" + (i-1),cellFormat)); sheet.addCell(new Label(1,i,recSchools.get(i-2).getSchoolName(),cellFormat)); sheet.addCell(new Label(2,i,recSchools.get(i-2).getF985(),cellFormat)); sheet.addCell(new Label(3,i,recSchools.get(i-2).getF211(),cellFormat)); sheet.addCell(new Label(4,i,recSchools.get(i-2).getDualClass(),cellFormat)); sheet.addCell(new Label(5,i,recSchools.get(i-2).getSchoolProvince(),cellFormat)); sheet.addCell(new Label(6,i,recSchools.get(i-2).getSchoolType(),cellFormat)); } //写入数据 workbook.write(); //关闭工作簿 workbook.close(); }
在浏览器中执行该接口即可生成excel
导入依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
@GetMapping("/poi") public void poiExport(HttpServletResponse response) throws IOException { String fileName = "poi_test.xlsx"; SXSSFWorkbook workbook = new SXSSFWorkbook(); //创建sheet表格 SXSSFSheet sheet = workbook.createSheet(); //设置表头1字体格式 Font font1 = workbook.createFont(); font1.setFontName("黑体"); font1.setBold(true); //加粗 font1.setFontHeightInPoints((short) 17); //字体大小 //设置表头1单元格格式 CellStyle cellStyle1 = workbook.createCellStyle(); cellStyle1.setAlignment(HorizontalAlignment.CENTER); //居中 cellStyle1.setFont(font1); //设置表头2字体格式 Font font2 = workbook.createFont(); font2.setFontName("宋体"); font2.setBold(true); font2.setFontHeightInPoints((short) 11); font2.setColor(Font.COLOR_RED); //设置表头2单元格格式 CellStyle cellStyle2 = workbook.createCellStyle(); cellStyle2.setAlignment(HorizontalAlignment.CENTER); //设置单元格背景颜色 cellStyle2.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置上下左右边框 cellStyle2.setBorderBottom(BorderStyle.valueOf(String.valueOf(BorderStyle.THIN))); cellStyle2.setBorderTop(BorderStyle.valueOf(String.valueOf(BorderStyle.THIN))); cellStyle2.setBorderLeft(BorderStyle.valueOf(String.valueOf(BorderStyle.THIN))); cellStyle2.setBorderRight(BorderStyle.valueOf(String.valueOf(BorderStyle.THIN))); cellStyle2.setFont(font2); //设置数据字体格式 CellStyle cellStyle3 = workbook.createCellStyle(); Font font3 = workbook.createFont(); font3.setFontName("宋体"); font3.setBold(false); font3.setFontHeightInPoints((short) 11); cellStyle3.setFont(font3); //合并单元格(0,0)到(0,6) CellRangeAddress region = new CellRangeAddress(0, 0, 0, 6); sheet.addMergedRegion(region); //填充表头1数据 SXSSFRow row1 = sheet.createRow(0); SXSSFCell cell1 = row1.createCell(0); cell1.setCellStyle(cellStyle1); cell1.setCellValue("院校信息汇总表"); //填充表头2数据 String[] headers2 = {"序号", "学校名称", "是否985", "是否211", "是否双一流", "地域", "学校类型"}; SXSSFRow row2 = sheet.createRow(1); for (int i = 0; i < headers2.length; i++) { SXSSFCell cell = row2.createCell(i); cell.setCellStyle(cellStyle2); cell.setCellValue(headers2[i]); } //填充具体数据 List<RecSchool> recSchools = recSchoolMapper.findAll(); for (int i = 0; i < recSchools.size(); i++) { SXSSFCell cell; //从第三行开始 SXSSFRow row = sheet.createRow(i + 2); cell = row.createCell(0); cell.setCellValue(""+(i+1)); cell.setCellStyle(cellStyle3); cell = row.createCell(1); cell.setCellValue(recSchools.get(i).getSchoolName()); cell.setCellStyle(cellStyle3); cell = row.createCell(2); cell.setCellValue(recSchools.get(i).getF985()); cell.setCellStyle(cellStyle3); cell = row.createCell(3); cell.setCellValue(recSchools.get(i).getF211()); cell.setCellStyle(cellStyle3); cell = row.createCell(4); cell.setCellValue(recSchools.get(i).getDualClass()); cell.setCellStyle(cellStyle3); cell = row.createCell(5); cell.setCellValue(recSchools.get(i).getSchoolProvince()); cell.setCellStyle(cellStyle3); cell = row.createCell(6); cell.setCellValue(recSchools.get(i).getSchoolType()); cell.setCellStyle(cellStyle3); } response.reset(); response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName ,"UTF-8")); workbook.write(response.getOutputStream()); }
官方文档:https://www.yuque.com/easyexcel/doc
超级详细
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。