POI excel导出工具类

package com.nbis.cloud.common.util;

import com.nbis.cloud.common.base.StatusCodeEnum;
import com.nbis.cloud.common.exception.BizException;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class POIExcelUtil {

	private static final Logger logger = LoggerFactory.getLogger(POIExcelUtil.class);

	private static final String ARIAL = "Arial";

	public static void outputExcel(HttpServletResponse response, String sheetName, String excelName,
			List<Object[]> headerList, List<String> titleList, List<Object[]> objectList) {
		OutputStream outputStream = null;
		try {
			//文件名称
			generateResponseHeader(response, excelName);
			outputStream = response.getOutputStream();
			createExcel(outputStream, sheetName, headerList, titleList, objectList);
			outputStream.flush();
		} catch (Exception e) {
			logger.error("Export excel failure", e);
			throw new BizException(StatusCodeEnum.EXPORT_FAILURE, sheetName);
		} finally {
			try {
				if (outputStream != null) {
					outputStream.close();
				}
			} catch (IOException e) {
				logger.error("Export stream close failure", e);
			}
		}
	}

	/**
	 * function: 输出excel到流
	 *
	 * @param outputStream 输出流
	 * @param sheetName 工作表名
	 * @param headerList 标题栏列表
	 * @param dataList 数据
	 */
	public static void createExcel(OutputStream outputStream, String sheetName,
			List<Object[]> headerList, List<String> titleList, List<Object[]> dataList) throws Exception {
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet worksheet;
		//获取工作表
		if (StringUtils.isNotBlank(sheetName)) {
			worksheet = workbook.createSheet(sheetName);
		} else {
			worksheet = workbook.createSheet();
		}
		//设置header样式
		CellStyle headerStyle = createStyle(workbook, true, ARIAL, 10, HorizontalAlignment.LEFT,
				true);
		//设置title样式
		CellStyle titleStyle = createStyle(workbook, true, ARIAL, 10, HorizontalAlignment.CENTER,
				false);
		//设置内容样式
		CellStyle cellsStyle = createStyle(workbook, false, ARIAL, 10, HorizontalAlignment.LEFT,
				false);
		XSSFRow xssfRow;
		XSSFCell cell;
		//设置header
		int headerSize = headerList.size();
		for (int row = 0; row < headerSize; row++) {
			Object[] objects = headerList.get(row);
			xssfRow = worksheet.createRow(row);
			xssfRow.setHeight((short) 300);
			for (int column = 0; column < objects.length; column++) {
				String cellValue = objects[column] == null ? "" : objects[column].toString();
				cell = xssfRow.createCell(column);
				cell.setCellValue(cellValue);
				cell.setCellStyle(headerStyle);
				worksheet.setColumnWidth(column, (50*256));
			}
		}

		xssfRow = worksheet.createRow(headerSize);
		for (int column = 0; column < titleList.size(); column++) {
			String cellValue = titleList.get(column) == null ? "" : titleList.get(column).trim();
			cell = xssfRow.createCell(column);
			cell.setCellValue(cellValue);
			cell.setCellStyle(titleStyle);
			if (column > 0) {
				worksheet.setColumnWidth(column, (30*256));
			}
		}

		//写入数据
		for (int row = 0; row < dataList.size(); row++) {
			Object[] objects = dataList.get(row);
			xssfRow = worksheet.createRow(row + 1 + headerSize);
			for (int column = 0; column < objects.length; column++) {
				String cellValue = objects[column] == null ? "" : objects[column].toString();
				cell = xssfRow.createCell(column);
				cell.setCellValue(cellValue);
				cell.setCellStyle(cellsStyle);
			}
		}
		workbook.write(outputStream);
	}

	private static CellStyle createStyle(XSSFWorkbook workbook, Boolean bold, String fontName,
			int size, HorizontalAlignment textAlignment, Boolean textWrapped) {
		CellStyle style = workbook.createCellStyle();
		// 左右位置
		style.setAlignment(textAlignment);
		// 上下位置
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setWrapText(textWrapped);
		// 字体
		XSSFFont font = workbook.createFont();
		font.setFontName(fontName);
		font.setBold(bold);
		font.setFontHeightInPoints((short) size);
		style.setFont(font);
		return style;
	}

	private static void generateResponseHeader(HttpServletResponse response, String excelName)
			throws UnsupportedEncodingException {
		response.reset();
		response.setContentType("application/msexcel;charset=utf-8");
		response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
		response.setHeader("content-disposition",
				"attachment;fileName=" + excelName + ".xlsx");
		response.setHeader("Pragma", "no-cache");
		response.setHeader("Cache-Control", "no-cache");
		response.setDateHeader("Expires", 0);
	}

}

impl实现类例子

/**
     * 导出
     */
    @Override
    public void vmExportAll(GetVMPSFilesQueryRequestDTO getVMPSFilesQueryRequestDTO,
                            HttpServletResponse response)  {
        List<GetVMPSFilesDTO> vmpsFiles = getVMPSFilesMapper.getVMPSFiles(getVMPSFilesQueryRequestDTO);
        List<Object[]> excelContentList = new ArrayList<>();
        for (GetVMPSFilesDTO vmpsFile : vmpsFiles) {
            Object[] objects = new Object[9];
            objects[0] = vmpsFile.getReportType();
            objects[1] = vmpsFile.getReportPeriod();
            objects[2] = vmpsFile.getOutletName();
            objects[3] = vmpsFile.getDealerCode();
            objects[4] = vmpsFile.getDealerRegion();
            objects[5] = vmpsFile.getDealerGroup();
            objects[6] = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(vmpsFile.getSubmitTime());
            objects[7] = vmpsFile.getLastUpdateTime() == null ? "" : new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(vmpsFile.getLastUpdateTime());
            objects[8] = getFileStatus(vmpsFile.getStatus());
            excelContentList.add(objects);
        }
        String sheetName="Vmps Data";
        String excelName = sheetName + CommonConstant.FILE_NAME_SPLIT + new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        POIExcelUtil.outputExcel(response,sheetName,excelName,new ArrayList<>(),titleList(),excelContentList);
}

    /**
     * 导出excel title
     * @return
     */
    private List<String> titleList(){
        List<String> titleList = new ArrayList<>();
        titleList.add("Report Type");
        titleList.add("Report Period");
        titleList.add("Outlet Name");
        titleList.add("Outlet Code");
        titleList.add("Dealer Region");
        titleList.add("Dealer Group");
        titleList.add("Submission Time");
        titleList.add("Last Operation Time");
        titleList.add("Status");
        return titleList;
    }