Java POI实现excel导出
一、依赖
<!-- excel导入导出 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
二、自定义工具类
ExcelUtil.java
public class ExcelUtil {
/**
* 导出excel
* @param sheetName sheet名称
* @param title 标题
* @param values 内容
* @param wb HSSFWorkbook对象
*/
public static HSSFWorkbook getHssfWorkbook(String sheetName, String []title, String [][]values, HSSFWorkbook workbook) {
//第一步,创建一个HSSFWorkbook,对应一个Excel文件
if (workbook == null) {
workbook = new HSSFWorkbook();
}
//第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = workbook.createSheet(sheetName);
//设置列宽
sheet.setDefaultColumnWidth(15);
//第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0);
//第四步,创建单元格,并设置值表头,设置表头居中
HSSFCellStyle style = workbook.createCellStyle();
//样式
style.setAlignment(HorizontalAlignment.CENTER);//创建一个居中格式
//声明列对象
HSSFCell cell = null;
//创建标题
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
//创建内容
for (int i = 0; i < values.length; i++) {
row = sheet.createRow(i+1);
for (int j = 0; j < values[i].length; j++) {
//将内容按顺序赋给对应的列对象
row.createCell(j).setCellValue(values[i][j]);
}
}
return workbook;
}
}
三、业务部分
/**
* 导出xx项目excel
* contents:具体内容
* fileName:文件名
* title:列头
* sheetName:sheet名
*/
private HSSFWorkbook excelOutProject(List<ProjectInfoTable> contents, String[] title, String sheetName) {
String [][] content = new String[contents.size()][];
//遍历内容
for (int i=0; i < contents.size(); i++) {
content[i] = new String[title.length];
ProjectInfoTable info = contents.get(i);
content[i][0] = info.getCategory();
content[i][1] = info.getName();
}
//创建HSSFWorkbook
HSSFWorkbook workbook = ExcelUtil.getHssfWorkbook(sheetName, title, content, null);
return workbook;
}
四、controller
//报表导出
@GetMapping("/excel")
@Override
public void excelExport(HttpServletResponse response){
HSSFWorkbook workbook = officeService.excelExport(sort);
String fileName = "xxx项目" + System.currentTimeMillis() + ".xls";
//响应到客户端
try {
//设置响应头
this.setResponseHeader(response, fileName);
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
//刷新流
outputStream.flush();
//关闭流
outputStream.close();
}catch (Exception e){
e.printStackTrace();
}
}
//发送响应流方法
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), StandardCharsets.ISO_8859_1);
} catch (Exception e) {
e.printStackTrace();
}
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}