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();
   }
}

来源：https://www.cnblogs.com/sword-successful/p/16152450.html