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.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.usermodel.XSSFWorkbook; 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.Matcher; import java.util.regex.Pattern; /** * 控制台导出程序 * * @param <T> 导出的数据 * @author 颜佐光 */ public class ExcelConsole<T extends Object> implements DbRow<T> { private Pattern chinese = Pattern.compile("[^x00-xff]"); /** * 配置信息 */ private final ExportData config; /** * 行数据处理 */ private final ExcelRow<T> rowHandle; /** * 工作薄 */ private Workbook workbook; /** * 工作Sheet */ private Sheet sheet; /** * 单元格央视 */ private CellStyle style; /** * 行号 */ private int rowIndex; /** * 合并列表組 */ private Map<String, List<String>> mergerGroup; /** * 是否需要合并单元格 */ private Map<String, ExcelMergerData> mergerGroupData; /** * 最大字节长度 */ private Map<Integer, Integer> columnBytes; /** * 控制台输出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已初始化"); } // 创建合并对象数据检测 mergerGroup = new HashMap<>(); mergerGroupData = new HashMap<>(); columnBytes = new HashMap<>(); // 创建工作簿对象 workbook = new XSSFWorkbook(); 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); } // 写入本行内容 for (int columnPos = 0; columnPos < this.config.getColumns().size(); columnPos++) { ExportColumn column = this.config.getColumns().get(columnPos); String columnName = column.getName(); String value = StringHelper.getFirst(rowHandle.get(t, columnName)); // 当不需要合并历史记录时,则创建新的内容 Cell cell = createCell(row, columnPos, value, true); mergerColumn(column, columnPos, false); } 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 (workbook == null) { return this; } // 合并数据配置 for (int columnPos = 0; columnPos < this.config.getColumns().size(); columnPos++) { ExportColumn column = this.config.getColumns().get(columnPos); 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())); } } 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() { FileHelper.createDirectory(this.config.getServerPath()); String fileName = String.format("%s/%s", this.config.getServerPath(), this.config.getFileName()); return fileName; } /** * 创建单元格 * * @param row * @param column * @param content * @return */ private Cell createCell(Row row, int column, String content, boolean isMax) { // 获取字节数、用于设置最大宽度 if (isMax) { int chinaCount = 0; Matcher matcher = chinese.matcher(content); while (matcher.find()) { chinaCount++; } int bytes = (content.length() - chinaCount) + (int) Math.round(chinaCount * 1.5); int maxBytes = Math.max(columnBytes.getOrDefault(column, 0), bytes); 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; } }