java使用poi生成excel

使用poi生成excel通常包含一下几个步骤

• 创建一个工作簿
• 创建一个sheet
• 创建一个Row对象
• 创建一个cell对象(1个row+1个cell构成一个单元格)
• 设置单元格内容
• 设置单元格样式. 字体 字体大小 是否加粗
• 保存
• 关闭流对象

生成一个工作簿

2010以上格式使用XSSFWorkBook对象, 2003格式使用HSSFWorkBook对象, 其他对象操作基本一样.

生成2003格式

public void test1() {     HSSFWorkbook workbook = new HSSFWorkbook();          CellStyle cellStyle = workbook.createCellStyle();     cellStyle.setBorderBottom(BorderStyle.THIN);     cellStyle.setBorderLeft(BorderStyle.THIN);     cellStyle.setBorderRight(BorderStyle.THIN);     cellStyle.setBorderTop(BorderStyle.THIN);          Font font = workbook.createFont();     font.setFontName("宋体");      font.setFontHeightInPoints((short) 12);     cellStyle.setFont(font);         HSSFSheet sheet = workbook.createSheet("Sheet1");     //设置单元格宽度     sheet.setColumnWidth(0, 30 * 256);     sheet.setColumnWidth(1, 30 * 256);     sheet.setColumnWidth(2, 30 * 256);          Row row0 = sheet.createRow(0);     Cell cell0 = row0.createCell(0);     cell0.setCellValue("序号");     cell0.setCellStyle(cellStyle);          Cell cell1 = row0.createCell(1);     cell1.setCellValue("姓名");          Cell cell2 = row0.createCell(2);     cell2.setCellValue("成绩");          OutputStream os = null;     try {         os = new FileOutputStream("d:\\测试生成2003.xls");         workbook.write(os);         os.close();     } catch (Exception e) {         e.printStackTrace();     } }

生成2010以上格式

@Test public void test2() {     XSSFWorkbook workbook = new XSSFWorkbook();          CellStyle cellStyle = workbook.createCellStyle();     cellStyle.setBorderBottom(BorderStyle.THIN);     cellStyle.setBorderLeft(BorderStyle.THIN);     cellStyle.setBorderRight(BorderStyle.THIN);     cellStyle.setBorderTop(BorderStyle.THIN);          Font font = workbook.createFont();     font.setFontName("宋体");     font.setFontHeightInPoints((short) 12);     cellStyle.setFont(font);               XSSFSheet sheet = workbook.createSheet("Sheet1");     Row row0 = sheet.createRow(0);     Cell cell0 = row0.createCell(0);     cell0.setCellValue("序号");     cell0.setCellStyle(cellStyle);          Cell cell1 = row0.createCell(1);     cell1.setCellValue("姓名");          Cell cell2 = row0.createCell(2);     cell2.setCellValue("成绩");          OutputStream os = null;     try {         os = new FileOutputStream("d:\\测试生成2010.xlsx");         workbook.write(os);         os.close();     } catch (Exception e) {         e.printStackTrace();     } }

合并单元格

合并单元格在生成excel中算常见的一个场景, 通常先合并单元, 单元格内容居中,并设置单元格边框.
poi合并单元格使用CellRangeAddress类, 构造函数包括4个参数firstRow, lastRow, firstCol, lastCol根据自己需要传入行和列.

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) {          }

合并单元格后设置边框poi已提供了RegionUtil静态类, 可直接使用.

CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2); sheet.addMergedRegion(region);  RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet); RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet); RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet); RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);

设置单元格样式

左右居中 上下居中 自动换行

cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setWrapText(true);

使用SpringMVC/SpringBoot导出excel

@Controller @GetMapping("/excel2003") public void excel2003(HttpServletResponse httpServletResponse){     try {         //2010格式设置         //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");         //2003格式设置         response.setContentType("application/vnd.ms-excel");         httpServletResponse.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("学生成绩单.xls", "utf-8"));          ServletOutputStream outputStream = httpServletResponse.getOutputStream();          HSSFWorkbook workbook = new HSSFWorkbook();          CellStyle cellStyle = workbook.createCellStyle();         cellStyle.setBorderBottom(BorderStyle.THIN);         cellStyle.setBorderLeft(BorderStyle.THIN);         cellStyle.setBorderRight(BorderStyle.THIN);         cellStyle.setBorderTop(BorderStyle.THIN);          Font font = workbook.createFont();         font.setFontName("宋体");         font.setFontHeightInPoints((short) 12);         cellStyle.setFont(font);          HSSFSheet sheet = workbook.createSheet("Sheet1");         Row row0 = sheet.createRow(0);         Cell cell0 = row0.createCell(0);         cell0.setCellValue("序号");         cell0.setCellStyle(cellStyle);          Cell cell1 = row0.createCell(1);         cell1.setCellValue("姓名");          Cell cell2 = row0.createCell(2);         cell2.setCellValue("成绩");          workbook.write(outputStream);     } catch (Exception e) {         e.printStackTrace();     } }

总结

如果你在web项目中导出excel后,打开excel文件时提示文件已损坏,但是文件还可以打开, 则需要在HttpServletResponse上设置响应头, 2003和2010设置方式不同
2003
response.setContentType("application/vnd.ms-excel");
2010
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

下载文件名如果包含中文的话需要编码
httpServletResponse.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("学生成绩单.xls", "utf-8"));
这种设置在浏览器里下载文件中文是没问题的, 只是如果你使用Swagger或者Postman测试的话,文件名还是经过编码的, 这个没问题说明文件下载已经没问题.