Excel导出工具

项目中经常会有导出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;
	}
	
}

五，下载结果