java使用poi生成excel的步骤

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 jaEAJQmUWsheet = 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(BordjaEAJQmUWerStyle.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设置方式不同2003response.setContentType("application/vnd.ms-excel");2010response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。