项目中经常会有导出excel的需求,这里使用poi写了一套导出工具。
一,导入pom包
<!-- excel解析 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
二,新建测试类
package com.example.controller;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import com.example.util.ExportUtil;
import com.example.util.FileExportParams;
/**
* @description 导出excel文件
* @author qihh
* @date 2017-08-15
*/
@Controller
@RequestMapping("/excel")
public class ExportController {
/**
* 测试导出-xlsx -- ok
* @param file
* @param response
*/
@RequestMapping(value = "/testExport", method = RequestMethod.GET)
public void bathInternalUser(HttpServletResponse response) {
List<Map<String,Object>> testData = new ArrayList<>();
Map<String,Object> map = new HashMap<>();
map.put("name", "大熊");
map.put("phone", "13500000000");
map.put("gender", "男");
map.put("birthday", "2018-01-17");
map.put("address", "中国");
map.put("companyName", "技术有限公司");
map.put("position", "工程师");
map.put("remark", "无用数据不需要导出");
testData.add(map);
// 导出数据
FileExportParams fileExportParams = new FileExportParams();
fileExportParams.setFileName("测试导出EXCEL报表");
fileExportParams.setDatas(testData);
// 文件首栏顺序对应数据的Key值
fileExportParams.setHeaders(new String[]{"姓名","性别","手机","地址","生日","公司","职位"});
fileExportParams.setKeys(new String[]{"name","gender","phone","address","birthday","companyName","position"});
ExportUtil.outputFile(fileExportParams, response);
}
}
三,工具类
package com.example.util;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ExportUtil {
private final static Logger logger = LoggerFactory.getLogger(ExportUtil.class);
/**
* 输出文件到浏览器
* @param params
* @param response
*/
public static void outputFile(FileExportParams params, HttpServletResponse response){
try {
InputStream in = new ByteArrayInputStream(exportExcel(params));
// 设置response的Header
response.reset();
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(params.getFullName(), "UTF-8"));
response.setContentType("application/octet-stream");
IOUtils.copy(in, response.getOutputStream());
} catch (IOException e) {
logger.error("【导出excel出错】",e);
throw new RuntimeException("导出excel出错");
}
}
/**
* 导出到excel
* @param params
* @return
* @throws IOException
*/
public static byte[] exportExcel(FileExportParams params) throws IOException {
if (".xlsx".equalsIgnoreCase(params.getFileType())) {
logger.info("导出.xlsx文件");
return createExcel(params,new XSSFWorkbook());
}
if (".xls".equalsIgnoreCase(params.getFileType())) {
logger.info("导出.xls文件");
return createExcel(params,new HSSFWorkbook());
}
return new byte[0];
}
/**
* 创建Excel文件
* @return 临时文件保存地址
* @throws IOException
*/
private static byte[] createExcel(FileExportParams params,Workbook workBook) throws IOException {
createSheet(params,workBook);
ByteArrayOutputStream os = new ByteArrayOutputStream();
workBook.write(os);
return os.toByteArray();
}
/**
* 导出到excel - 写到临时文件输出
* @param params
* @return
* @throws IOException
*/
public static String getExcelPath(FileExportParams params) throws IOException {
if (".xlsx".equalsIgnoreCase(params.getFileType())) {
logger.info("导出.xlsx文件");
return createExcelPath(params,new XSSFWorkbook());
}
if (".xls".equalsIgnoreCase(params.getFileType())) {
logger.info("导出.xls文件");
return createExcelPath(params,new HSSFWorkbook());
}
return null;
}
/**
* 创建Excel文件 - 写到临时文件输出
* @return 临时文件保存地址
* @throws IOException
*/
private static String createExcelPath(FileExportParams params,Workbook workBook) throws IOException {
createSheet(params,workBook);
/*
* 输出新建的excel,保存到本地临时文件
*/
String filePath = params.getFilePath();
File tempFile = new File(filePath);
if (!tempFile.exists()) {
tempFile.mkdirs();
}
filePath = params.getFilePath().concat("/").concat(params.getFullName());
FileOutputStream fileOut = new FileOutputStream(filePath);
workBook.write(fileOut);
fileOut.flush();
fileOut.close();
logger.info("导出excel临时文件存储地址:{}",filePath);
return filePath;
}
/**
* 创建一个工作表,设置格式
* @param params
* @param workBook
*/
private static void createSheet(FileExportParams params, Workbook workBook) {
// 创建一个工作薄对象
Sheet sheet = workBook.createSheet();
/*
* 创建样式对象
*/
CellStyle titleCellStyle = workBook.createCellStyle();
// 指定单元格居中对齐,边框为细
titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// 设置填充色
titleCellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 指定当单元格内容显示不下时自动换行
titleCellStyle.setWrapText(true);
// 设置单元格字体
Font titleFont = workBook.createFont();
titleFont.setFontHeightInPoints((short) 12);
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleCellStyle.setFont(titleFont);
/*
* 创建单元格样式
*/
CellStyle cellStyle = workBook.createCellStyle();
// 指定单元格居中对齐,边框为细
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// 设置单元格字体
Font font = workBook.createFont();
font.setFontHeightInPoints((short) 11);
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(font);
setExportData(sheet,titleCellStyle,cellStyle,params);
}
/**
* 设置导出数据
* @param result
*/
private static void setExportData(Sheet sheet,CellStyle titleCellStyle,CellStyle cellStyle,FileExportParams params) {
/*
* 设置表头
*/
Row headerRow = sheet.createRow(0);
Cell headerCell = null;
String[] headers = params.getHeaders();
for (int c = 0; c < headers.length; c++) {
headerCell = headerRow.createCell(c);
headerCell.setCellStyle(titleCellStyle);
headerCell.setCellValue(headers[c]);
sheet.setColumnWidth(c, (30 * 160));
}
/*
* 设置正文
*/
List<Map<String,Object>> datas = params.getDatas();
String[] keys = params.getKeys();
int i = 1;//正文从第二行开始
for (Map<String,Object> map : datas) {
Row row = sheet.createRow(i++);
int j = 0;//开始列
for (String key : keys) {
if (ArrayUtils.contains(keys, key)) {
Object value = map.get(key) == null ? "" : map.get(key);
Cell cell = row.createCell(j++);
cell.setCellStyle(cellStyle);
cell.setCellValue(value.toString());
}
}
}
}
}
四,参数类
package com.example.util;
import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.StringUtils;
public class FileExportParams implements Serializable{
private static final long serialVersionUID = -600144284024163528L;
/**
* 导出数据
*/
private List<Map<String,Object>> datas;
/**
* 导出文件类型,默认=".xlsx"
*/
private String fileType = ".xlsx";
/**
* 导出文件名称
*/
private String fileName;
/**
* 导出excel表头字段
*/
private String[] headers;
/**
* 导出文件存储目录,例如:E:/work/git
*/
private String filePath;
/**
* 导出excel表头字段对应的数据Key值
*/
private String[] keys;
public List<Map<String, Object>> getDatas() {
return datas;
}
public void setDatas(List<Map<String, Object>> datas) {
this.datas = datas;
}
public String getFileType() {
return fileType;
}
public void setFileType(String fileType) {
this.fileType = fileType;
}
public String getFilePath() {
return filePath;
}
public void setFilePath(String filePath) {
this.filePath = filePath;
}
public String[] getHeaders() {
return headers;
}
public void setHeaders(String[] headers) {
this.headers = headers;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
/**
* 拼接文件名和后缀
* @return
*/
public String getFullName() {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss");
String fileName = (sdf.format(new Date())).concat(this.fileType);
if (StringUtils.isNotBlank(this.fileName)) {
fileName = this.fileName.concat("-").concat(fileName);
}
return fileName;
}
public String[] getKeys() {
return keys;
}
public void setKeys(String[] keys) {
this.keys = keys;
}
}
五,下载结果