Excel导出工具类封装

1.调用方式

ExcelUtils.exportExcel(resultOrderList, fieldLinkedMap, response.getOutputStream(), fileName, suffix);

2.exportExcel(List> resultOrderList, Map headOrderMap, OutputStream os, String sheetTitle, String version)方法参数介绍

　　resultOrderList：从数据库获取到的Map类型的数据集合。集合示例：[{"file_name":"hadoop规划表"，"dept_name":"数据部"},{"file_name":"财年数据运营统计表"，"dept_name":"运营部"}]

　　headOrderMap：标题有序集合，可以使用LinkedHashMap。集合示例：{"file_name":"文件名称"，"dept_name":"所属部门"}

　　sheetTitle：sheet页的名称。

　　version：excel的版本，可以是xls或者xlsx。

3.工具类代码（放到java文件中可以直接使用）

import java.io.IOException;
import java.io.OutputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel工具类
 * Author zhangjifu
 * Date    2020年11月12日-下午6:00:50
 */
public class ExcelUtils {

    /**
     * 导出Excel表格
     * @param resultOrderList 结果集列表
     * @param headOrderMap 头部有序集合
     * @param sheetTitle sheet页标题
     * @param os 输出流
     * @param version Excel版本：xls或者xlsx
     */
    public static void exportExcel(List> resultOrderList, Map headOrderMap, OutputStream os, String sheetTitle, String version) {
        if (StringUtils.isBlank(version) || "xls".equals(version.trim())) {
            exportHSExcel(resultOrderList, headOrderMap, sheetTitle, os);
        } else {
            exportXSExcel(resultOrderList, headOrderMap, sheetTitle, os);
        }
    }

    /**
     * 2003版Excel表格导出，后缀为xls
     * @param resultOrderList 结果集列表
     * @param headOrderMap 头部有序集合
     * @param os 输出流
     */
    private static void exportHSExcel(List> resultOrderList, Map headOrderMap, String sheetTitle, OutputStream os) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(sheetTitle);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeightInPoints(24);
        // 指定宽度
//        setColumnWidth(sheet, excelComplex.getColumnWidths());
        // 生成头部样式
        HSSFCellStyle headStyle = workbook.createCellStyle();
        // 设置头部样式
        setHeadCellStyle(headStyle);
        // 生成头部字体
        HSSFFont headFont = workbook.createFont();
        // 设置头部字体
        headFont.setFontName("宋体");
        headFont.setColor(IndexedColors.WHITE.getIndex());
        headFont.setFontHeightInPoints((short) 12);
        // 把头部字体应用到头部样式
        headStyle.setFont(headFont);
        // 生成数据行样式
        HSSFCellStyle resultStyle = workbook.createCellStyle();
        setResultCellStyle(resultStyle);
        // 生成数据行字体
        HSSFFont resultFont = workbook.createFont();
        resultFont.setBold(true);
        // 把数据行字体应用到数据行样式
        resultStyle.setFont(resultFont);
        // 遍历头部集合，产生标题行
        int headRowIndex = 0;
        int headCellIndex = 0;
        HSSFRow row = sheet.createRow(headRowIndex);
        HSSFCell cellHeader;
        Iterator> headIterator = headOrderMap.entrySet().iterator();        
        while(headIterator.hasNext()){
            Entry nextEntry = headIterator.next();
            cellHeader = row.createCell(headCellIndex);
            cellHeader.setCellStyle(headStyle);
            cellHeader.setCellValue(new HSSFRichTextString(StringUtils.isBlank((String) nextEntry.getValue())?"":String.valueOf(nextEntry.getValue())));
            headCellIndex++;
        }
        // 遍历集合数据，产生数据行
        int resultRowIndex = headRowIndex+1;
        int resultCellIndex = 0;
        HSSFCell cell;
        for (Map resultMap : resultOrderList) {
            row = sheet.createRow(resultRowIndex);
            headIterator = headOrderMap.entrySet().iterator(); 
            while(headIterator.hasNext()){
                String cellValue = String.valueOf(resultMap.get(headIterator.next().getKey()));
                cell = row.createCell(resultCellIndex);
                cell.setCellStyle(resultStyle);
                if(StringUtils.isNotBlank(cellValue) && "null"!=cellValue){
                    cell.setCellValue(new HSSFRichTextString(cellValue));
                }
                resultCellIndex++;
            }
            resultCellIndex=0;
            resultRowIndex++;
        }
        // 将Excel发送到客户端
        workBookWrite(os, workbook);
    }

    /**
     * 2007版Excel导出，后缀为xlsx
     * @param resultOrderList 结果集列表
     * @param headOrderMap 头部有序集合
     * @param sheetTitle sheet页标题
     * @param os 输出流
     */
    private static void exportXSExcel(List> resultOrderList, Map headOrderMap, String sheetTitle, OutputStream os) {
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet(sheetTitle);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeightInPoints(24);
        // 指定宽度
//        setColumnWidth(sheet, excelComplex.getColumnWidths());
        // 生成头部样式
        XSSFCellStyle headStyle = workbook.createCellStyle();
        // 设置头部样式
        setHeadCellStyle(headStyle);
        // 生成头部字体
        XSSFFont headFont = workbook.createFont();
        headFont.setColor(IndexedColors.WHITE.getIndex());
        headFont.setFontHeightInPoints((short) 12);
        headFont.setBold(true);
        // 把头部字体应用到头部样式
        headStyle.setFont(headFont);
        // 生成数据行样式
        XSSFCellStyle resultStyle = workbook.createCellStyle();
        // 设置数据行央视
        setResultCellStyle(resultStyle);
        // 生成数据行字体
        XSSFFont resultFont = workbook.createFont();
        // 设置数据行字体
        resultFont.setBold(true);
        // 把数据行字体应用数据行样式
        resultStyle.setFont(resultFont);
        // 遍历头部集合，产生标题行
        int headRowIndex = 0;
        int headCellIndex = 0;
        XSSFRow row = sheet.createRow(headRowIndex);
        XSSFCell cellHeader;
        Iterator> headIterator = headOrderMap.entrySet().iterator();        
        while(headIterator.hasNext()){
            Entry nextEntry = headIterator.next();
            cellHeader = row.createCell(headCellIndex);
            cellHeader.setCellStyle(headStyle);
            cellHeader.setCellValue(new XSSFRichTextString(StringUtils.isBlank((String) nextEntry.getValue())?"":String.valueOf(nextEntry.getValue())));
            headCellIndex++;
        }
        // 遍历集合数据，产生数据行
        int resultRowIndex = headRowIndex+1;
        int resultCellIndex = 0;
        XSSFCell cell;
        for (Map resultMap : resultOrderList) {
            row = sheet.createRow(resultRowIndex);
            headIterator = headOrderMap.entrySet().iterator(); 
            while(headIterator.hasNext()){
                String cellValue = String.valueOf(resultMap.get(headIterator.next().getKey()));
                cell = row.createCell(resultCellIndex);
                cell.setCellStyle(resultStyle);
                if(StringUtils.isNotBlank(cellValue) && "null"!=cellValue){
                    cell.setCellValue(new XSSFRichTextString(cellValue));
                }
                resultCellIndex++;
            }
            resultCellIndex=0;
            resultRowIndex++;
        }
        // 将Excel发送到客户端
        workBookWrite(os, workbook);
    }

    private static void setColumnWidth(Sheet sheet, Integer[] columnWidths) {
        for (int i = 0; i < columnWidths.length; i++) {
            sheet.setColumnWidth(i, 256 * columnWidths[i] + 184);
        }
    }

    /**
     * 设置头部样式
     * @param headStyle 头部样式
     */
    private static void setHeadCellStyle(CellStyle headStyle) {
        headStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setBorderBottom(BorderStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderTop(BorderStyle.THIN);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直
    }

    /**
     * 设置数据行样式
     * @param resultStyle 数据行样式
     */
    private static void setResultCellStyle(CellStyle resultStyle) {
        resultStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        resultStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        resultStyle.setBorderBottom(BorderStyle.THIN);
        resultStyle.setBorderLeft(BorderStyle.THIN);
        resultStyle.setBorderRight(BorderStyle.THIN);
        resultStyle.setBorderTop(BorderStyle.THIN);
        resultStyle.setAlignment(HorizontalAlignment.CENTER);
        resultStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    }

    /**
     * 将Excel输出到客户端
     * @param os 输出流
     * @param workbook 工作簿
     */
    private static void workBookWrite(OutputStream os, Workbook workbook) {
        try {
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(os);
        }
    }

}