poi导出excel抽象模版

poi导出excel抽象模版

文中使用到的相关jar包和版本，org.apache.poi:poi-ooxml:3.17，下面是文中主要涉及到的相关知识点：

• 利用模版模式构建导出操作
• 兼容IE浏览器下中文文件名乱码
• 使用SXSSFWorkbook操作sheet，避免oom
• 可设置是否需要excel表头、表尾
• 导出的excel单元格对日期类型，数字类型做转换(主要是去掉单元格左上角的小绿帽)

---

代码块

核心代码如下：

import com.dy.analysis.utils.UrlUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

public abstract class AbstractExportTemplate<T> {


    private String sheetName = "sheet";
    private String[] titleName;
    private List<T> dataList;
    private List<Map<String, Object>> footList;
    private boolean needHeader = false;
    private int sheetMaxRowCount = 10000;
    private String headerName = sheetName;
    private static final String POINT = ".";
    private static final String XLS = POINT + "xls";
    private static final String XLSX = POINT + "xlsx";
    public static final String IE = "msie";
    public static final String IE_11 = "trident";
    public static final String WIN_10 = "edge";

    public void download(HttpServletRequest request, HttpServletResponse response, String fileName) throws Exception {
        fileName = initFileSuffix(fileName);
        String userAgent = request.getHeader("User-Agent").toLowerCase();
        String formFileName = fileName;
        //对ie内核的浏览器文件名乱码处理
        if (userAgent.contains(IE) || userAgent.contains(IE_11) || userAgent.contains(WIN_10)) {
            formFileName = UrlUtils.encode(formFileName);
            formFileName = formFileName.replace("+", "%20");
        } else {
            formFileName = new String(fileName.getBytes(UrlUtils.UTF_8), UrlUtils.ISO);
        }
        String headStr = "attachment; filename=\"" + formFileName + "\"";
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", headStr);
        OutputStream out = response.getOutputStream();
        initExport().write(out);
        out.close();
    }

    /**
     * 拼装excel表内容，由子类实现
     *
     * @param sheetRowNum      每个sheet的初始行号
     * @param sheetMaxRowCount 每个sheet的最大数据条数
     * @param sheet            sheet对象
     * @param style            每个单元格样式对象
     * @param workbook         workbook对象
     */
    protected abstract void packageData(int sheetRowNum, int sheetMaxRowCount, SXSSFSheet sheet, CellStyle style, SXSSFWorkbook workbook);

    /**
     * 拼装excel表底部内容，由子类实现，非必须
     *
     * @param sheetMaxRowCount 每个sheet的最大数据条数
     * @param sheet            sheet对象
     * @param style            每个单元格样式对象
     * @param workbook         workbook对象
     */
    protected abstract void packageFoot(int sheetMaxRowCount, SXSSFSheet sheet, CellStyle style, SXSSFWorkbook workbook);


    /**
     * 组装数据前的准备工作
     */
    private Workbook initExport() {
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        CellStyle columnTopStyle = getColumnTopStyle(workbook);
        CellStyle style = getStyle(workbook);
        int rowsCount = dataList.size();
        int sheetIndex = rowsCount / sheetMaxRowCount;
        for (int a = 0; a <= sheetIndex; a++) {
            SXSSFSheet sheet = workbook.createSheet(sheetName + a);
            if (needHeader) {
                SXSSFRow rowm = sheet.createRow(0);
                SXSSFCell cellTiltle = rowm.createCell(0);
                sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (titleName.length - 1)));
                cellTiltle.setCellStyle(columnTopStyle);
                cellTiltle.setCellValue(headerName);
            }

            int columnNum = titleName.length;
            SXSSFRow rowName;
            if (needHeader) {
                // 在索引2的位置创建行(最顶端的行开始的第三行)
                rowName = sheet.createRow(2);
            } else {
                // 在索引0的位置创建行(最顶端的行开始的第一行)
                rowName = sheet.createRow(0);
            }
            for (int n = 0; n < columnNum; n++) {
                SXSSFCell cellRowName = rowName.createCell(n);
                cellRowName.setCellType(CellType.STRING);
                XSSFRichTextString text = new XSSFRichTextString(titleName[n]);
                cellRowName.setCellValue(text);
                cellRowName.setCellStyle(columnTopStyle);
                sheet.setColumnWidth(n, (text.toString().getBytes().length + 8) * 256);
            }
            int sheetRowNum = a * sheetMaxRowCount;
            int lastRowCount = rowsCount % sheetMaxRowCount;
            if (a == sheetIndex) {
                sheetMaxRowCount = lastRowCount;
            }
            //组装具体数据
            packageData(sheetRowNum, sheetMaxRowCount, sheet, style, workbook);
            packageFoot(sheetMaxRowCount, sheet, getFootStyle(workbook), workbook);
        }
        return workbook;
    }


    /**
     * 表头单元格样式
     *
     * @param workbook workbook对象
     * @return 单元格样式对象
     */
    private CellStyle getColumnTopStyle(SXSSFWorkbook workbook) {
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);
        font.setBold(true);
        font.setFontName("微软雅黑");
        CellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setFont(font);
        style.setWrapText(false);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /**
     * 表格单元格样式
     *
     * @param workbook workbook对象
     * @return 单元格样式对象
     */
    private CellStyle getStyle(SXSSFWorkbook workbook) {
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 10);
        font.setFontName("微软雅黑");
        CellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setFont(font);
        style.setWrapText(true);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /**
     * 表尾单元格样式，一般为总计，合计等
     *
     * @param workbook workbook对象
     * @return 单元格样式对象
     */
    private CellStyle getFootStyle(SXSSFWorkbook workbook) {
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 10);
        font.setBold(true);
        font.setFontName("微软雅黑");
        CellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setFont(font);
        style.setWrapText(true);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    private String initFileSuffix(String fileName) {
        if (StringUtils.isEmpty(fileName)) {
            fileName = System.currentTimeMillis() + XLSX;
        } else {
            if (fileName.lastIndexOf(POINT) != -1) {
                if (!fileName.endsWith(XLS) && !fileName.endsWith(XLSX)) {
                    throw new IllegalArgumentException("The fileName's type is not support,[" + fileName + "]");
                }
            } else {
                fileName = fileName + XLSX;
            }
            if (fileName.endsWith(XLS)) {
                fileName = fileName.substring(0, fileName.lastIndexOf(".")) + XLSX;
            }
        }
        return fileName;
    }

    public void setSheetMaxRowCount(int sheetMaxRowCount) {
        this.sheetMaxRowCount = sheetMaxRowCount;
    }

    public void setNeedHeader(boolean needHeader) {
        this.needHeader = needHeader;
    }

    public void setHeaderName(String headerName) {
        this.headerName = headerName;
    }

    void setTitleName(String[] titleName) {
        this.titleName = titleName;
    }

    void setDataList(List<T> dataList) {
        this.dataList = dataList;
    }

    void setFootList(List<Map<String, Object>> footList) {
        this.footList = footList;
    }

    void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    String[] getTitleName() {
        return titleName;
    }

    List<T> getDataList() {
        return dataList;
    }

    List<Map<String, Object>> getFootList() {
        return footList;
    }

    boolean isNeedHeader() {
        return needHeader;
    }

}

利用的模版模式，把导出相关的公共操作定义到抽象类里。子类只需实现具体拼装表格的方法，以及定义导出数据对象的泛型即可。文件名乱码这个经测试ie11，edge，chrome，firefox均不会出现乱码的问题。
例如下面的demo，其中如果不需要表尾的话，packageFoot方法实现后为空即可：

import com.dy.analysis.dto.TestDTO;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.util.List;
import java.util.Map;


public class TestExcel extends AbstractExportTemplate<TestDTO> {


    public TestExcel(String sheetName, String[] titleName, List<TestDTO> dataList, List<Map<String, Object>> footList) {
        setSheetName(sheetName);
        setTitleName(titleName);
        setDataList(dataList);
        setFootList(footList);
    }

    @Override
    protected void packageData(int sheetRowNum, int sheetMaxRowCount, SXSSFSheet sheet, CellStyle style, SXSSFWorkbook workbook) {
        CellStyle dateStyle = workbook.createCellStyle();
        dateStyle.cloneStyleFrom(style);
        dateStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
        for (int i = 0; i < sheetMaxRowCount; i++) {
            SXSSFRow row;
            if (isNeedHeader()) {
                //需加上表头占用的两行
                row = sheet.createRow(i + 3);
            } else {
                row = sheet.createRow(i + 1);
            }
            TestDTO dto = getDataList().get(sheetRowNum + i);
            SXSSFCell cell = row.createCell(0, CellType.STRING);
            cell.setCellValue(dto.getDate());
            cell.setCellStyle(dateStyle);
            SXSSFCell cell2 = row.createCell(1, CellType.STRING);
            cell2.setCellValue(dto.getUsername());
            cell2.setCellStyle(style);
            SXSSFCell cell3 = row.createCell(2, CellType.STRING);
            cell3.setCellValue(dto.getNickname());
            cell3.setCellStyle(style);
            SXSSFCell cell4 = row.createCell(3, CellType.NUMERIC);
            cell4.setCellValue(dto.getAge());
            cell4.setCellStyle(style);
        }
    }

    @Override
    protected void packageFoot(int sheetMaxRowCount, SXSSFSheet sheet, CellStyle style, SXSSFWorkbook workbook) {
        if (getFootList() == null || getFootList().isEmpty()) {
            return;
        }
        String allAge = String.valueOf(getFootList().get(0).get("allAge"));
        SXSSFRow row;
        if (isNeedHeader()) {
            //需加上表头占用的两行
            row = sheet.createRow(sheetMaxRowCount + 3);
        } else {
            row = sheet.createRow(sheetMaxRowCount + 1);
        }
        SXSSFCell cell = row.createCell(0, CellType.STRING);
        cell.setCellValue("总计");
        cell.setCellStyle(style);
        SXSSFCell cell2 = row.createCell(1, CellType.STRING);
        cell2.setCellValue("/");
        cell2.setCellStyle(style);
        SXSSFCell cell3 = row.createCell(2, CellType.STRING);
        cell3.setCellValue("/");
        cell3.setCellStyle(style);
        SXSSFCell cell4 = row.createCell(3, CellType.NUMERIC);
        cell4.setCellValue(Double.parseDouble(allAge));
        cell4.setCellStyle(style);
    }
}

下面是入口和测试相关的代码了

import com.dy.analysis.dto.TestDTO;
import com.dy.analysis.export.TestExcel;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Controller
@RequestMapping("/export")
public class ExportController {

    @GetMapping("/excel")
    public void excel(HttpServletRequest request, HttpServletResponse response) {
        List<TestDTO> testDTOS = new ArrayList<>();
        TestDTO testDTO = new TestDTO("abc", "abc", 21, "2018-07-01 01:25:45");
        TestDTO testDTO2 = new TestDTO("def", "def", 20, "2018-07-02 01:25:45");
        TestDTO testDTO3 = new TestDTO("hij", "hij", 18, "2018-07-03 01:25:45");
        TestDTO testDTO4 = new TestDTO("klm", "klm", 19, "2018-07-04 01:25:45");
        TestDTO testDTO5 = new TestDTO("nop", "nop", 25, "2018-07-05 01:25:45");
        testDTOS.add(testDTO);
        testDTOS.add(testDTO2);
        testDTOS.add(testDTO3);
        testDTOS.add(testDTO4);
        testDTOS.add(testDTO5);
        List<Map<String, Object>> footList = new ArrayList<>();
        Map<String, Object> map = new HashMap<>();
        map.put("allAge", 125);
        footList.add(map);
        String[] title = new String[]{"日期", "名称", "昵称", "年龄"};
        TestExcel excel = new TestExcel("sheetName", title, testDTOS, footList);
        try {
            //excel.setNeedHeader(true);
            //excel.setHeaderName("Excel Head");
            excel.download(request, response, "啦啦啦，卖报的小玩家");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

import lombok.Getter;
import lombok.Setter;

@Setter
@Getter
public class TestDTO {

    private String username;
    private String nickname;
    private int age;
    private String date;

    public TestDTO(String username, String nickname, int age, String date) {
        this.username = username;
        this.nickname = nickname;
        this.age = age;
        this.date = date;
    }
}

如果需要表头的话把注释的两行放开即可，使用起来也是很简单，new一个导出类的对象，调用download方法即可

下面放两张效果图

---

其中代码没有什么难度，主要就是一个抽象概念，之前在项目中导出excel用的比较多，代码又比较凌乱，就花了点时间把这块整理了一下