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

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

  1. public void test1() {
  2.     HSSFWorkbook workbook = new HSSFWorkbook();
  3.     
  4.     CellStyle cellStyle = workbook.createCellStyle();
  5.     cellStyle.setBorderBottom(BorderStyle.THIN);
  6.     cellStyle.setBorderLeft(BorderStyle.THIN);
  7.     cellStyle.setBorderRight(BorderStyle.THIN);
  8.     cellStyle.setBorderTop(BorderStyle.THIN);
  9.     
  10.     Font font = workbook.createFont();
  11.     font.setFontName("宋体"); 
  12.     font.setFontHeightInPoints((short) 12);
  13.     cellStyle.setFont(font);
  14.    
  15.     HSSFSheet sheet = workbook.createSheet("Sheet1");
  16.     //设置单元格宽度
  17.     sheet.setColumnWidth(0, 30 * 256);
  18.     sheet.setColumnWidth(1, 30 * 256);
  19.     sheet.setColumnWidth(2, 30 * 256);
  20.     
  21.     Row row0 = sheet.createRow(0);
  22.     Cell cell0 = row0.createCell(0);
  23.     cell0.setCellValue("序号");
  24.     cell0.setCellStyle(cellStyle);
  25.     
  26.     Cell cell1 = row0.createCell(1);
  27.     cell1.setCellValue("姓名");
  28.     
  29.     Cell cell2 = row0.createCell(2);
  30.     cell2.setCellValue("成绩");
  31.     
  32.     OutputStream os = null;
  33.     try {
  34.         os = new FileOutputStream("d:\\测试生成2003.xls");
  35.         workbook.write(os);
  36.         os.close();
  37.     } catch (Exception e) {
  38.         e.printStackTrace();
  39.     }
  40. }
  1. @Test
  2. public void test2() {
  3.     XSSFWorkbook workbook = new XSSFWorkbook();
  4.     
  5.     CellStyle cellStyle = workbook.createCellStyle();
  6.     cellStyle.setBorderBottom(BorderStyle.THIN);
  7.     cellStyle.setBorderLeft(BorderStyle.THIN);
  8.     cellStyle.setBorderRight(BorderStyle.THIN);
  9.     cellStyle.setBorderTop(BorderStyle.THIN);
  10.     
  11.     Font font = workbook.createFont();
  12.     font.setFontName("宋体");
  13.     font.setFontHeightInPoints((short) 12);
  14.     cellStyle.setFont(font);
  15.     
  16.     
  17.     XSSFSheet sheet = workbook.createSheet("Sheet1");
  18.     Row row0 = sheet.createRow(0);
  19.     Cell cell0 = row0.createCell(0);
  20.     cell0.setCellValue("序号");
  21.     cell0.setCellStyle(cellStyle);
  22.     
  23.     Cell cell1 = row0.createCell(1);
  24.     cell1.setCellValue("姓名");
  25.     
  26.     Cell cell2 = row0.createCell(2);
  27.     cell2.setCellValue("成绩");
  28.     
  29.     OutputStream os = null;
  30.     try {
  31.         os = new FileOutputStream("d:\\测试生成2010.xlsx");
  32.         workbook.write(os);
  33.         os.close();
  34.     } catch (Exception e) {
  35.         e.printStackTrace();
  36.     }
  37. }

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

  1. public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) {
  2.         
  3. }

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

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

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

  1. cellStyle.setAlignment(HorizontalAlignment.CENTER);
  2. cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
  3. cellStyle.setWrapText(true);
  1. @Controller
  2. @GetMapping("/excel2003")
  3. public void excel2003(HttpServletResponse httpServletResponse){
  4.     try {
  5.         //2010格式设置
  6.         //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  7.         //2003格式设置
  8.         response.setContentType("application/vnd.ms-excel");
  9.         httpServletResponse.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("学生成绩单.xls", "utf-8"));
  11.         ServletOutputStream outputStream = httpServletResponse.getOutputStream();
  13.         HSSFWorkbook workbook = new HSSFWorkbook();
  15.         CellStyle cellStyle = workbook.createCellStyle();
  16.         cellStyle.setBorderBottom(BorderStyle.THIN);
  17.         cellStyle.setBorderLeft(BorderStyle.THIN);
  18.         cellStyle.setBorderRight(BorderStyle.THIN);
  19.         cellStyle.setBorderTop(BorderStyle.THIN);
  21.         Font font = workbook.createFont();
  22.         font.setFontName("宋体");
  23.         font.setFontHeightInPoints((short) 12);
  24.         cellStyle.setFont(font);
  26.         HSSFSheet sheet = workbook.createSheet("Sheet1");
  27.         Row row0 = sheet.createRow(0);
  28.         Cell cell0 = row0.createCell(0);
  29.         cell0.setCellValue("序号");
  30.         cell0.setCellStyle(cellStyle);
  32.         Cell cell1 = row0.createCell(1);
  33.         cell1.setCellValue("姓名");
  35.         Cell cell2 = row0.createCell(2);
  36.         cell2.setCellValue("成绩");
  38.         workbook.write(outputStream);
  39.     } catch (Exception e) {
  40.         e.printStackTrace();
  41.     }
  42. }

如果你在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测试的话,文件名还是经过编码的, 这个没问题说明文件下载已经没问题.