ExcelConsole.java

package com.yanzuoguang.excel;

import com.yanzuoguang.db.impl.DbRow;
import com.yanzuoguang.util.YzgError;
import com.yanzuoguang.util.helper.CheckerHelper;
import com.yanzuoguang.util.helper.FileHelper;
import com.yanzuoguang.util.helper.StringHelper;
import com.yanzuoguang.util.table.TableHead;
import com.yanzuoguang.util.table.TableHeadHelper;
import com.yanzuoguang.util.table.TableHeadItem;
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.SXSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * 控制台导出程序
 *
 * @param <T> 导出的数据
 * @author 颜佐光
 */
public class ExcelConsole<T extends Object> implements DbRow<T> {
    private final Pattern chinese = Pattern.compile("[^x00-xff]");
    /**
     * 配置信息
     */
    private final ExportData config;
    /**
     * 行数据处理
     */
    private final ExcelRow<T> rowHandle;

    /**
     * 行状态
     */
    private final ExcelStatus<T> excelStatus;

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

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

    /**
     * 单元格央视
     */
    private CellStyle style;

    /**
     * 行号
     */
    private int rowIndex;
    /**
     * 行数据
     */
    private int rowData;

    /**
     * 缓存行数
     */
    private int cacheRow = 1000;

    /**
     * 合并列表組
     */
    private Map<String, List<String>> mergerGroup;

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

    /**
     * 最大字节长度
     */
    private Map<Integer, Integer> columnBytes;

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

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

    public int getCacheRow() {
        return cacheRow;
    }

    public void setCacheRow(int cacheRow) {
        this.cacheRow = cacheRow;
    }

    /**
     * 配置信息
     *
     * @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.excelStatus != null) {
            excelStatus.excelInit(this);
        }
        if (this.workbook != null) {
            throw YzgError.getRuntimeException("034");
        }
        // 创建合并对象数据检测
        mergerGroup = new HashMap<>();
        mergerGroupData = new HashMap<>();
        columnBytes = new HashMap<>();

        if (this.cacheRow < 1) {
            this.cacheRow = 1000;
        }

        // 创建工作簿对象
        workbook = new SXSSFWorkbook(this.cacheRow);
        workbook.setCompressTempFiles(true);
        sheet = workbook.createSheet();

        style = initColumnCenterstyle(workbook);

        // 行和列都是从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();

    }

    /**
     * 写入标题
     *
     * @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 = createCell(row, 0, content, false);
        // 这里是合并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 = createCell(row, headItem.getColumn(), headItem.getName(), true);

            // 判断是否需要合并列头
            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);
            }
        }

        // 合并数据配置
        for (ExportColumn column : this.config.getColumns()) {
            if (!column.isMerger()) {
                continue;
            }
            // 获取需要合并的組，不能为null
            String group = StringHelper.getFirst(column.getMegerGroup());
            column.setMegerGroup(group);
            if (!mergerGroup.containsKey(group)) {
                mergerGroup.put(group, new ArrayList<>());
                mergerGroupData.put(group, new ExcelMergerData());
            }
            // 当前合并組中添加需要合并的列
            mergerGroup.get(group).add(column.getName());
        }
    }

    /**
     * 开始生成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 (Map.Entry<String, List<String>> groupKvp : mergerGroup.entrySet()) {
            // 将当前組生成值密钥
            StringBuilder sb = new StringBuilder();
            for (String columnName : groupKvp.getValue()) {
                String value = StringHelper.getFirst(rowHandle.get(t, columnName));
                sb.append(value.replace(":", "::"));
                sb.append(":");
            }
            String groupValue = StringHelper.md5(sb.toString());
            // 更新合并内容
            ExcelMergerData mergerData = mergerGroupData.get(groupKvp.getKey());
            mergerData.updateMerger(rowIndex, groupValue);
        }

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

            // 当不需要合并历史记录时，则创建新的内容
            Cell cell = createCell(row, columnPos, value, true);
            mergerColumn(column, columnPos, false);
            columnPos++;
        }

        rowIndex++;
        rowData++;

        if (this.excelStatus != null) {
            excelStatus.excelRow(t, rowIndex);
        }
    }

    /**
     * 合并列
     *
     * @param column    需要合并的列
     * @param columnPos 需要合并的列位置
     * @param last      是否最后一行，最后一行，则合并之前的数据
     */
    private void mergerColumn(ExportColumn column, int columnPos, boolean last) {
        // 判断列是否需要合并
        if (column.isMerger()) {
            ExcelMergerData mergerData = mergerGroupData.get(column.getMegerGroup());
            // 判断是否需要合并历史记录
            if (mergerData.isMergerFlag() || last) {
                // 合并历史记录单元格
                mergerData(mergerData, columnPos, last);
            }
        }
    }

    /**
     * 合并数据
     *
     * @param mergerColumn 需要合并的列
     * @param columnPos    合并的列位置
     * @param last         是否最后一行，最后一行，则合并之前的数据
     */
    private void mergerData(ExcelMergerData mergerColumn, int columnPos, boolean last) {
        int rowStart = mergerColumn.getRowIndexHistory();
        int rowEnd = rowStart + mergerColumn.getRowCellHistory() - 1;
        if (last) {
            rowStart = mergerColumn.getRowIndex();
            rowEnd = rowStart + mergerColumn.getRowCell() - 1;
        }

        CellRangeAddress region = new CellRangeAddress(rowStart, rowEnd, columnPos, columnPos);
        sheet.addMergedRegion(region);

        Cell cell = sheet.getRow(rowStart).getCell(columnPos);
    }

    /**
     * 会自动在生成完毕调用该函数
     */
    public ExcelConsole save() {
        if (this.excelStatus != null) {
            excelStatus.excelFinish(this);
        }
        if (workbook == null) {
            return this;
        }
        // 合并数据配置
        if (this.rowData > 0) {
            int columnPos = 0;
            for (ExportColumn column : this.config.getColumns()) {
                mergerColumn(column, columnPos, true);

                if (column.getWidth() < 1) {
                    //设置单元格长度, 这里要乘上256
                    int maxBytes = columnBytes.get(columnPos);
                    sheet.setColumnWidth(columnPos, maxBytes * 256);
                } else {
                    sheet.setColumnWidth(columnPos, getUnit(column.getWidth()));
                }
                columnPos++;
            }
        }

        try {
            String fileNameTemp = this.getFileNameTemp();
            // 保存为临时文件
            OutputStream out = new FileOutputStream(fileNameTemp);
            try {
                workbook.write(out);
                out.flush();
            } catch (IOException e) {
                e.printStackTrace();
                throw YzgError.getRuntimeException("035");
            } finally {
                out.close();
            }
            // 判断临时文件是否存在
            File file = new File(fileNameTemp);
            if (!file.exists()) {
                throw YzgError.getRuntimeException("036", this.getFileName());
            }
            // 重命名成正式文件
            String fileName = this.getFileName();
            file.renameTo(new File(fileName));
        } catch (IOException e) {
            e.printStackTrace();
            throw YzgError.getRuntimeException("035");
        }
        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 YzgError.getRuntimeException("012", file.getName());
            }
        }
        return this;
    }

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

    /**
     * 获取保存文件名（全路径）
     *
     * @return 文件名
     */
    public String getFileNameTemp() {
        return getFileName() + ".tmp";
    }

    /**
     * 创建单元格
     *
     * @param row
     * @param column
     * @param content
     * @return
     */
    private Cell createCell(Row row, int column, String content, boolean isMax) {
        // 获取字节数、用于设置最大宽度
        if (isMax) {
            int chinaCount = (int) Math.round(content.length() * 1.5);
            int maxBytes = Math.max(columnBytes.getOrDefault(column, 0), chinaCount);
            columnBytes.put(column, maxBytes);
        }

        Cell cell = row.createCell(column);
        cell.setCellStyle(style);
        cell.setCellValue(content);
        return cell;
    }

    /**
     * <br>
     * <b>功能：</b>单元格的默认样式<br>
     * <b>作者：</b>yixq<br>
     * <b>@param wb
     * <b>@return</b>
     */
    public CellStyle initColumnCenterstyle(Workbook wb) {

        CellStyle cellStyle = wb.createCellStyle();

        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);

        // 左右居中
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        // 上下居中
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyle.setWrapText(true);
        cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        cellStyle.setBorderLeft((short) 1);
        cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
        cellStyle.setBorderRight((short) 1);
        // 设置单元格的边框为粗体
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        // 设置单元格的边框颜色．
        cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置单元格的背景颜色．
        cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        return cellStyle;
    }
}