ExcelConsole.java

package com.yanzuoguang.excel;

import com.yanzuoguang.db.impl.DbRow;
import com.yanzuoguang.util.exception.CodeException;
import com.yanzuoguang.util.helper.CheckerHelper;
import com.yanzuoguang.util.table.TableHead;
import com.yanzuoguang.util.table.TableHeadHelper;
import com.yanzuoguang.util.table.TableHeadItem;
import org.apache.poi.ss.usermodel.Cell;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;

/**
 * 控制台导出程序
 *
 * @param <T> 导出的数据
 * @author 颜佐光
 */
public class ExcelConsole<T extends Object> implements DbRow<T> {
    /**
     * 配置信息
     */
    private final ExportData config;
    /**
     * 行数据处理
     */
    private final ExcelRow<T> rowHandle;

    /**
     * 工作薄
     */
    private Workbook workbook;

    /**
     * 工作Sheet
     */
    private Sheet sheet;

    /**
     * 行号
     */
    private int rowIndex;

    /**
     * 是否需要合并单元格
     */
    private Map<Integer, ExcelMergerData> mergerData;

    /**
     * 控制台输出Excel
     *
     * @param exportData 导出数据
     */
    public ExcelConsole(ExportData exportData) {
        this(exportData, null);
    }

    /**
     * 导出成Excel
     *
     * @param config    导出信息
     * @param rowHandle 导出下载信息
     */
    public ExcelConsole(ExportData config, ExcelRow<T> rowHandle) {
        this.config = config;
        this.rowHandle = rowHandle;
    }

    /**
     * 配置信息
     *
     * @return 配置数据
     */
    public ExportData getConfig() {
        return config;
    }

    /**
     * 行处理方式
     *
     * @return
     */
    public ExcelRow<T> getRowHandle() {
        return rowHandle;
    }

    /**
     * 获取单位计算后的只
     *
     * @param from 来源值
     * @return 目标值
     */
    private short getUnit(int from) {
        return (short) (from * ExportData.ROW_HEIGHT_UNIT);
    }

    /**
     * 检测参数是否异常
     */
    public ExcelConsole check() {
        CheckerHelper check = CheckerHelper.newInstance()
                .notBlankCheck("导出xls配置", this.config)
                .notBlankCheck("导出xls.标题", this.config.getTitle())
                .notBlankCheck("导出xls.子标题", this.config.getSubTitle())
                .notBlankCheck("导出xls.服务器路径", this.config.getServerPath())
                .notBlankCheck("导出xls.文件名", this.config.getFileName())
                .notBlankListCheck("导出xls.列", this.config.getColumns())
                .checkException();
        for (ExportColumn column : this.config.getColumns()) {
            check.notBlankCheck("导出xls.列名", column.getName())
                    .notBlankCheck("导出xls.标题", column.getTitle())
                    .checkException();
        }

        return this;
    }

    /**
     * 初始化Excel对象
     *
     * @return 需要初始化的Excel对象
     */
    protected TableHead initHead() {
        String[] columns = new String[this.config.getColumns().size()];
        int pos = 0;
        for (ExportColumn column : this.config.getColumns()) {
            columns[pos++] = column.getTitle();
        }
        return TableHeadHelper.getTableHead(columns);
    }

    /**
     * 初始化Excel对象
     */
    protected void initExcel(TableHead head) {
        if (this.workbook != null) {
            throw new CodeException("Excel已初始化");
        }
        // 创建工作簿对象
        workbook = new XSSFWorkbook();
        sheet = workbook.createSheet();
        // 行和列都是从0开始计数，且起始结束都会合并

        // 写入标题、子标题
        rowIndex = 0;
        int columnLength = this.config.getColumns().size() - 1;
        writeTitle(rowIndex++, this.config.getTitle(), this.config.getTitleHeight(), columnLength);
        writeTitle(rowIndex++, this.config.getSubTitle(), this.config.getSubTitleHeight(), columnLength);

        writeHead(head);
        rowIndex += head.getTotalRow();

        // 创建合并对象数据检测
        mergerData = new HashMap<>(head.getTotalColumn());
    }

    /**
     * 写入标题
     *
     * @param rowIndex     行号
     * @param content      内容
     * @param rowHeight    高度
     * @param columnLength 合并宽度
     */
    private void writeTitle(int rowIndex, String content, short rowHeight, int columnLength) {
        // 创建一行
        Row row = sheet.createRow(rowIndex);
        row.setHeight(getUnit(rowHeight));
        Cell cell = row.createCell(0);
        cell.setCellValue(content);
        // 这里是合并excel中多列为1列
        CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 0, columnLength);
        sheet.addMergedRegion(region);
    }

    /**
     * 写入列头
     *
     * @param head 需要写入的列头
     */
    private void writeHead(TableHead head) {
        // 创建行
        Row[] rows = new Row[head.getTotalRow()];
        for (int i = 0; i < rows.length; i++) {
            rows[i] = sheet.createRow(i + rowIndex);
            rows[i].setHeight(getUnit(this.config.getHeadHeight()));
        }

        // 写入列头
        for (TableHeadItem headItem : head.getColumns()) {
            Row row = rows[headItem.getRow()];
            Cell cell = row.createCell(headItem.getColumn());
            cell.setCellValue(headItem.getName());

            // 判断是否需要合并列头
            if (headItem.getColumnCell() > 1 || headItem.getRowCell() > 1) {
                int rowStart = rowIndex + headItem.getRow();
                int rowEnd = rowStart + headItem.getRowCell() - 1;
                int columnStart = headItem.getColumn();
                int columnEnd = columnStart + headItem.getColumnCell() - 1;

                CellRangeAddress region = new CellRangeAddress(rowStart, rowEnd, columnStart, columnEnd);
                sheet.addMergedRegion(region);
            }
        }
    }

    /**
     * 开始生成Excel文件
     */
    public ExcelConsole open() {
        this.check();
        TableHead head = this.initHead();
        initExcel(head);

        return this;
    }

    /**
     * 循环处理单行数据
     *
     * @param t 需要处理的单行数据
     */
    @Override
    public void handle(T t) {
        ExcelRow rowHandle = this.rowHandle;
        if (rowHandle == null) {
            rowHandle = ExcelRowDefault.getInstance();
        }

        // 创建一行
        Row row = sheet.createRow(rowIndex);
        row.setHeight(getUnit(this.config.getRowHeight()));

        // 写入本行内容
        for (int columnPos = 0; columnPos < this.config.getColumns().size(); columnPos++) {
            ExportColumn column = this.config.getColumns().get(columnPos);
            String columnName = column.getName();
            String value = rowHandle.get(t, columnName);

            // 判断列是否需要合并
            if (column.isMerger()) {
                // 获取合并历史记录配置
                if (!mergerData.containsKey(columnPos)) {
                    mergerData.put(columnPos, new ExcelMergerData());
                }
                ExcelMergerData mergerColumn = mergerData.get(columnPos);
                // 判断是否需要合并历史记录
                if (mergerColumn.isMergerHistory(value)) {
                    // 合并历史记录单元格
                    mergerData(mergerColumn, columnPos);
                } else {
                    // 当不需要合并历史记录时，则创建新的内容
                    Cell cell = row.createCell(columnPos);
                    cell.setCellValue(value);
                }
                // 更新合并内容
                mergerColumn.updateMerger(rowIndex, value);
            } else {
                // 不合并时直接写入单元格内容
                Cell cell = row.createCell(columnPos);
                cell.setCellValue(value);
            }
        }

        rowIndex++;
    }

    /**
     * 合并数据
     *
     * @param mergerColumn 需要合并的列
     * @param columnPos    合并的列位置
     */
    private void mergerData(ExcelMergerData mergerColumn, int columnPos) {
        int rowStart = mergerColumn.getRowIndex();
        int rowEnd = rowStart + mergerColumn.getRowCell() - 1;
        CellRangeAddress region = new CellRangeAddress(rowStart, rowEnd, columnPos, columnPos);
        sheet.addMergedRegion(region);
    }

    /**
     * 会自动在生成完毕调用该函数
     */
    public ExcelConsole save() {
        if (workbook == null) {
            return this;
        }

        // 写入最后的合并内容
        for (int columnPos = 0; columnPos < this.config.getColumns().size(); columnPos++) {
            ExportColumn column = this.config.getColumns().get(columnPos);
            if (column.isMerger() && mergerData.containsKey(columnPos)) {
                ExcelMergerData mergerColumn = mergerData.get(columnPos);
                if (mergerColumn.isMergerHistory()) {
                    mergerData(mergerColumn, columnPos);
                }
            }
        }

        try {
            OutputStream out = new FileOutputStream(this.getFileName());
            try {
                workbook.write(out);
                out.flush();
            } catch (IOException e) {
                e.printStackTrace();
                throw new CodeException("保存失败");
            } finally {
                out.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
            throw new CodeException("保存失败");
        }
        return this;
    }

    /**
     * 删除生成的Excel文件
     *
     * @return
     */
    public ExcelConsole remove() {
        if (this.workbook != null) {
            workbook = null;
        }
        File file = new File(this.getFileName());
        if (file.exists()) {
            if (!file.delete()) {
                throw new CodeException("文件删除失败");
            }
        }
        return this;
    }

    /**
     * 获取保存文件名（全路径）
     *
     * @return 文件名
     */
    public String getFileName() {
        String fileName = String.format("%s/%s", this.config.getServerPath(), this.config.getFileName());
        return fileName;
    }
}