com.github.dreamroute.excel.helper.util.ExcelUtil Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of excel-helper Show documentation
excel export base on apache poi 3.17
The newest version!
package com.github.dreamroute.excel.helper.util;

import com.github.dreamroute.excel.helper.annotation.CellProps;
import com.github.dreamroute.excel.helper.annotation.HeaderProps;
import com.github.dreamroute.excel.helper.cache.CacheFactory;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
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.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.util.Collection;
import java.util.Date;
import java.util.List;

import static com.github.dreamroute.excel.helper.cache.CacheFactory.findHeaderValues;
import static com.github.dreamroute.excel.helper.cache.CacheFactory.findSheetName;

/**
 * Export workbook util
 * 
 * @author [email protected]
 *
 */
public final class ExcelUtil {

    private ExcelUtil() {}

    /**
     * export as a {@link Workbook}, maybe include one or more sheet.
     * 
     * @param sheets it's a array, every Collection will create a sheet.
     * @return return a {@link Workbook}
     */
    public static Workbook create(ExcelType type, Collection... sheets) {
        Workbook workBook;
        if (type == ExcelType.XLS) {
            workBook = new HSSFWorkbook();
        } else {
            workBook = new SXSSFWorkbook();
        }

        createWorkbook(workBook, sheets);
        return workBook;
    }

    private static void createWorkbook(Workbook workBook, Collection... sheets) {
        for (Collection sheetData : sheets) {
            if (CollectionUtils.isNotEmpty(sheetData)) {
                createSheet(workBook, sheetData);
            }
        }
    }

    private static void createSheet(Workbook workbook, Collection sheetData) {
        Class dataCls = sheetData.iterator().next().getClass();
        String sheetName = findSheetName(dataCls);
        Sheet sheet = workbook.createSheet(sheetName);

        // excel content (header + data)
        HeaderProps[] headerProps = CacheFactory.findHeaderProps(dataCls);
        List headerValues = findHeaderValues(dataCls);

        List> data = DataAssistant.createData(sheetData, dataCls);
        CellType[] cellType = CacheFactory.findCellType(dataCls);
        Integer[] columnWith = CacheFactory.findColumnWidth(dataCls);
        CellProps[] cellProps = CacheFactory.findCellProps(dataCls);
        CellStyle[] css = createDataCellStyle(workbook, cellProps);
        List formulas = CacheFactory.findFormulaValues(dataCls);

        // create header row, create data rows
        createHeader(sheet, headerValues, headerProps, workbook);
        createData(sheet, data, cellType, css, formulas);
        setColumnWidth(sheet, columnWith);
    }

    /**
     * 根据列对应的CellProps创建对应的CellStyle数组，因为如果每个cell都创建一个CellStyle，那么导出大量数据时候会出现内存崩溃， 所以每一列对应一个CellStyle
     * 
     * @param cps CellProps
     * 
     */
    private static CellStyle[] createDataCellStyle(Workbook workbook, CellProps[] cps) {
        CellStyle[] css = null;
        if (cps != null && cps.length > 0) {
            css = new CellStyle[cps.length];
            for (int i = 0, len = css.length; i < len; i++) {
                CellProps cp = cps[i];
                CellStyle cs = workbook.createCellStyle();
                cs.setAlignment(cp.getHorizontal());
                cs.setVerticalAlignment(cp.getVertical());
                cs.setWrapText(true);

                String targetDateFormate = cp.getTargetDateFormate();
                if (targetDateFormate != null && targetDateFormate.trim().length() > 0) {
                    DataFormat format = workbook.createDataFormat();
                    cs.setDataFormat(format.getFormat(targetDateFormate));
                }

                css[i] = cs;
            }
        }

        return css;
    }

    private static void createHeader(Sheet sheet, List headerValues, HeaderProps[] hps, Workbook workbook) {
        Row row = sheet.createRow(0);
        for (int i = 0; i < headerValues.size(); i++) {
            Cell cell = row.createCell(i);
            // Header CellType must be CellType.STRING.
            cell.setCellType(CellType.STRING);
            cell.setCellValue(headerValues.get(i));

            // cell style
            CellStyle cellStyle = workbook.createCellStyle();
            processHeaderCellStyle(cellStyle, hps[i]);
            cell.setCellStyle(cellStyle);
        }
    }

    private static void createData(Sheet sheet, List> data, CellType[] cellType, CellStyle[] css, List formulaValues) {
        for (int i = 0; i < data.size(); i++) {
            // 0 row is header, data row from 1.
            Row row = sheet.createRow(i + 1);
            List