23 ExcelUtils

2023-10-26 08:28:41

 

一个 基于 poi 的 封装 

用户只需要关心下一个 cell 多大, 在哪里, 里面值是什么, 然后 一个一个 apeend 进去之后, 使用 ExcelUtils 相关的 API 进行渲染即可 

几种单元格的类型[当然除了 前两个之外其他的都不算单元格] : SIMPLE, MERGED, CRLF, OFFSET, GOTO

初始化一个 光标在 [0, 0]

假设 添加了一个 SIMPLE[s] 的单元格, 光标更新为 [1, 0]

假设再 添加了一个 MERGE[m][2, 2] 的单元格, 光标更新为 [3, 0]

假设再 添加了一个 CRLF的单元格, 光标更新为 [0, 2]

假设再 添加了一个 OFFSET[3, 0]的单元格, 光标更新为 [3, 2]

假设再添加了一个 GOTO[5, 5]的单元格, 光标更新为 [5, 5]

ExcelBuilder. build(), 用于创建 ExcelTable 

ExcelBuilder. debug(), 用于调试 ExcelTable 

如果 存在某些单元格重合, 在 build, debug 的过程中 会抛出异常 

对于 上面的流程, debug 出来的结果如下 : "*"表示 被占用的单元格, "o"表示空闲的单元格, "x"表示光标当前的位置[对应于上图的箭头右下的第一个单元格]

 *  x  o  o  o  o o  o  o  o  o  o o  o  o  o  o  o o  o  o  o  o  o o  o  o  o  o  o o  o  o  o  o  o 
info, cell :{"type":"SIMPLE", "direction":"EAST", "value":"s"}
------------------------ ^0^ ------------------------*  *  *  x  o  o o  *  *  o  o  o o  o  o  o  o  o o  o  o  o  o  o o  o  o  o  o  o o  o  o  o  o  o 
info, cell :{"type":"MERGED", "direction":"EAST", "value":"m", "width":2, "height":2}
------------------------ ^1^ ------------------------*  *  *  o  o  o o  *  *  o  o  o x  o  o  o  o  o o  o  o  o  o  o o  o  o  o  o  o o  o  o  o  o  o 
info, cell :{"type":"CRLF", "direction":"EAST", "value":null}
------------------------ ^2^ ------------------------*  *  *  o  o  o o  *  *  o  o  o o  o  o  x  o  o o  o  o  o  o  o o  o  o  o  o  o o  o  o  o  o  o 
info, cell :{"type":"OFFSET", "direction":"EAST", "xOff":3, "yOff":0}
------------------------ ^3^ ------------------------*  *  *  o  o  o o  *  *  o  o  o o  o  o  o  o  o o  o  o  o  o  o o  o  o  o  o  o o  o  o  o  o  x 
info, cell :{"type":"GOTO", "direction":"EAST", "value":null, "newX":5, "newY":5}
------------------------ ^4^ ------------------------

 

ok, 直接上相关的核心的代码了 

ExcelUtils.java

/*** ExcelUtils** @author Jerry.X.He* @date 2018/4/6 17:40*/
public final class ExcelUtils {// disable constructorprivate ExcelUtils() {Tools.assert0(false, "can't instantiate !");}/*** 新建一个 excelBuilder** @return* @author Jerry.X.He* @date 4/6/2018 4:55 PM* @since 1.0*/public static ExcelBuilder newBuilder() {return ExcelBuilder.newBuilder();}public static ExcelBuilder newBuilder(int estimateSize) {return ExcelBuilder.newBuilder(estimateSize);}public static ExcelBuilder newBuilder(String sheetTitle, int estimateSize) {return ExcelBuilder.newBuilder(sheetTitle, estimateSize);}/*** 根据给定的表格的元数据, 创建一个 Workbook** @param table table* @return org.apache.poi.ss.usermodel.Workbook* @author Jerry.X.He* @date 2018/4/6 17:50*/public static Workbook createExcel(ExcelTable table) {Workbook workBook = null;workBook = new HSSFWorkbook();Sheet sheet = workBook.createSheet(table.getSheetTitle());Map type2Handler = CollectionUtils.asMap(new ExcelCellType[]{ExcelCellType.CRLF, ExcelCellType.OFFSET,ExcelCellType.GOTO, ExcelCellType.SIMPLE, ExcelCellType.MERGED}, new CrlfCellHandler(), new OffsetCellHandler(),new GotoCellHandler(), new SimpleCellHandler(), new MergeCellHandler());CellHandleContext context = new SimpleCellHandleContext().setWorkbook(workBook).setSheet(sheet).setExcelTable(table).setStartRow(table.getStartRow()).setStartCol(table.getStartCol()).setRowIdx(table.getStartRow()).setColIdx(table.getStartCol()).setMaxRowOff(1).setMaxColOff(1);int cellIdx = 0;while (table.hasNext()) {ExcelCell exCell = table.next();CellHandler handler = type2Handler.get(exCell.type());if (handler == null) {Log.err(" 发现了一个匹配不上 type 的 exCell : " + JSONObject.fromObject(exCell).toString() + ", idx : " + cellIdx + "l, 忽略掉 ");continue;}context.setExcelCellIdx(cellIdx).setExcelCell(exCell);handler.handle(context);cellIdx++;}return workBook;}/*** 根据给定的表格的元数据, 创建一个 Workbook** @param table table* @return org.apache.poi.ss.usermodel.Workbook* @author Jerry.X.He* @date 2018/4/6 17:50*/public static Sheet fillExcel(ExcelTable table, Sheet sheet) {Map type2Handler = CollectionUtils.asMap(new ExcelCellType[]{ExcelCellType.CRLF, ExcelCellType.OFFSET,ExcelCellType.GOTO, ExcelCellType.SIMPLE, ExcelCellType.MERGED}, new CrlfCellHandler(), new OffsetCellHandler(),new GotoCellHandler(), new SimpleCellHandler(), new MergeCellHandler());CellHandleContext context = new SimpleCellHandleContext().setWorkbook(null).setSheet(sheet).setExcelTable(table).setStartRow(table.getStartRow()).setStartCol(table.getStartCol()).setRowIdx(table.getStartRow()).setColIdx(table.getStartCol()).setMaxRowOff(1).setMaxColOff(1);int cellIdx = 0;while (table.hasNext()) {ExcelCell exCell = table.next();CellHandler handler = type2Handler.get(exCell.type());if (handler == null) {Log.err(" 发现了一个匹配不上 type 的 exCell : " + JSONObject.fromObject(exCell).toString() + ", idx : " + cellIdx + "l, 忽略掉 ");continue;}context.setExcelCellIdx(cellIdx).setExcelCell(exCell);handler.handle(context);cellIdx++;}return sheet;}/*** 使用给定的 路径渲染表格的信息** @param table   table* @param sheet   sheet* @param handler handler* @return org.apache.poi.ss.usermodel.Workbook* @author Jerry.X.He* @date 2018/4/27 15:28*/public static void handleCells(ExcelTable table, Sheet sheet, PoiCellHandler handler) {Map type2Handler = CollectionUtils.asMap(new ExcelCellType[]{ExcelCellType.CRLF, ExcelCellType.OFFSET,ExcelCellType.GOTO, ExcelCellType.SIMPLE, ExcelCellType.MERGED}, new CrlfCellHandler(), new OffsetCellHandler(),new GotoCellHandler(), new SimpleCellHandler(), new MergeCellHandler());CellHandleContext context = new SimpleCellHandleContext().setWorkbook(null).setSheet(sheet).setExcelTable(table).setStartRow(table.getStartRow()).setStartCol(table.getStartCol()).setRowIdx(table.getStartRow()).setColIdx(table.getStartCol()).setMaxRowOff(1).setMaxColOff(1);int cellIdx = 0;while (table.hasNext()) {ExcelCell exCell = table.next();CellHandler cellHandler = type2Handler.get(exCell.type());if (cellHandler == null) {Log.err(" 发现了一个匹配不上 type 的 exCell : " + JSONObject.fromObject(exCell).toString() + ", idx : " + cellIdx + "l, 忽略掉 ");continue;}context.setExcelCellIdx(cellIdx).setExcelCell(exCell);cellHandler.handle(context);Cell cell = context.getCell();if (cell != null) {handler.handleCell(cell);}cellIdx++;}}/*** 使用给定的handler处理sheet中目标区域内的所有单元格, 如果不存在 则新建单元格** @param sheet    当前编辑的sheet* @param handler  处理目标区域内每一个Cell的Handler* @param firstRow 目标区域的第一行索引* @param lastRow  目标区域的最后一行索引* @param firstCol 目标区域的第一列索引* @param lastCol  目标区域的最后一列索引* @return void* @date 2017/5/4 14:18*/public static void handleCells(Sheet sheet, PoiCellHandler handler,int firstRow, int lastRow, int firstCol, int lastCol) {for (int heightOff = firstRow; heightOff <= lastRow; heightOff++) {Row row = sheet.getRow(heightOff);if (row == null) {row = sheet.createRow(heightOff);}for (int widthOff = firstCol; widthOff <= lastCol; widthOff++) {Cell cell = row.getCell(widthOff);if (cell == null) {cell = row.createCell(widthOff);}handler.handleCell(cell);}}}/*** 方向处理** @param leftUp leftUp* @param cell   cell* @return void* @author Jerry.X.He* @date 4/6/2018 4:38 PM* @since 1.0*/public static void handleDirection(Point leftUp, ExcelCell cell) {if (cell == null || cell.direction() == null) {return;}int xOff = 0, yOff = 0;Direction direction = cell.direction();if (direction == Direction.EAST || direction == Direction.WEST) {if (direction == Direction.EAST) {xOff = cell.width();} else {xOff = -1;}} else {if (direction == Direction.NORTH) {yOff = 1;} else {yOff = cell.height();}}leftUp.x += xOff;leftUp.y += yOff;}public static void handleDirection(CellHandleContext context) {ExcelCell cell = context.getExcelCell();if (cell == null || cell.direction() == null) {return;}int xOff = 0, yOff = 0;Direction direction = cell.direction();if (direction == Direction.EAST || direction == Direction.WEST) {if (direction == Direction.EAST) {xOff = cell.width();} else {xOff = -1;}} else {if (direction == Direction.NORTH) {yOff = 1;} else {yOff = cell.height();}}context.setColIdx(context.getColIdx() + xOff);context.setRowIdx(context.getRowIdx() + yOff);}/*** 如果给定的行 不存在的话, 创建一行** @param sheet  sheet* @param rowIdx rowIdx* @return org.apache.poi.ss.usermodel.Row* @author Jerry.X.He* @date 4/6/2018 11:37 AM* @since 1.0*/public static Row createRowIfNeed(Sheet sheet, int rowIdx) {Row row = sheet.getRow(rowIdx);if (row == null) {row = sheet.createRow(rowIdx);}return row;}/*** 如果给定的单元格 不存在的话, 创建一个单元格** @param row    row* @param colIdx colIdx* @return org.apache.poi.ss.usermodel.Row* @author Jerry.X.He* @date 4/6/2018 11:37 AM* @since 1.0*/public static Cell createCellIfNeed(Row row, int colIdx) {Cell cell = row.getCell(colIdx);if (cell == null) {cell = row.createCell(colIdx);}return cell;}/*** handleCell** @param exCell exCell* @param cell   cell* @return void* @author Jerry.X.He* @date 4/6/2018 8:21 PM* @since 1.0*/public static void handleCell(ExcelCell exCell, Cell cell, ExcelTable table, int idx) {cell.setCellValue(exCell.value());PoiCellHandler handler = table.getCellHandler(idx);if (handler != null) {handler.handleCell(cell);}}/*** 打印 dirtyTable** @param dirtyTable dirtyTable* @return void* @author Jerry.X.He* @date 7/21/2018 11:00 AM* @since 1.0*/public static void printDirtyTable(boolean[][] dirtyTable, CellHandleContext context) {StringBuilder sb = new StringBuilder();String tStr = " * ", fStr = " o ", curStr = " x ";int rowIdx = 0, colIdx = 0;for (boolean[] row : dirtyTable) {colIdx = 0;for (boolean cell : row) {boolean isCurrentPos = (rowIdx == context.getRowIdx() && (colIdx == context.getColIdx()));sb.append(isCurrentPos ? curStr : (cell ? tStr : fStr));colIdx++;}rowIdx++;sb.append(Tools.CRLF);}sb.append("info, cell :").append(context.getExcelCell().toDebugString());sb.append(Tools.CRLF);sb.append("------------------------ ^").append(context.getExcelCellIdx()).append("^ ------------------------");info(sb.toString());}// ---------------------------------- 辅助方法 --------------------------------}

 

ExcelBuilder.java

/*** ExcelBuilder** @author Jerry.X.He* @date 2018/4/6 17:43*/
public class ExcelBuilder {/*** 默认的表格名称*/public static final String DEFAULT_SHEET_NAME = "sheet1";/*** 存放单元格集合*/private List list;/*** 索引 -> CellHandler*/private Map idx2Handler;/*** 表格标题*/private String sheetTitle;/*** 渲染开始的行数*/private int startRow;/*** 渲染开始的列数*/private int startCol;/*** 用于后面的额校验, 可能的最大的行数, 列数*/private int estimateRowMax = 1;private int estimateColMax = 1;/*** 新建一个 excelBuilder** @return* @author Jerry.X.He* @date 4/6/2018 4:55 PM* @since 1.0*/public static ExcelBuilder newBuilder() {return new ExcelBuilder(DEFAULT_SHEET_NAME);}public static ExcelBuilder newBuilder(int estimateSize) {return new ExcelBuilder(DEFAULT_SHEET_NAME, estimateSize);}public static ExcelBuilder newBuilder(String sheetTitle, int estimateSize) {return new ExcelBuilder(sheetTitle, estimateSize);}public ExcelBuilder(String sheetTitle) {this(sheetTitle, 16);}public ExcelBuilder(String sheetTitle, int estimateSize) {this(sheetTitle, estimateSize, 0, 0);}public ExcelBuilder(String sheetTitle, int estimateSize, int startRow, int startCol) {this.sheetTitle = sheetTitle;this.startRow = startRow;this.startCol = startCol;this.list = new ArrayList<>(estimateSize);idx2Handler = new HashMap<>(Tools.estimateMapSize(estimateSize));}public ExcelBuilder add(ExcelCell cell) {return add(cell, null);}public ExcelBuilder add(ExcelCell cell, PoiCellHandler handler) {if (cell == null) {return this;}if (handler != null) {idx2Handler.put(list.size(), handler);}list.add(cell);return this;}public void setSheetTitle(String sheetTitle) {this.sheetTitle = sheetTitle;}public void setStartRow(int startRow) {this.startRow = startRow;}public void setStartCol(int startCol) {this.startCol = startCol;}public ExcelTable build() {if (CollectionUtils.isEmpty(list)) {return new ExcelTable(list, idx2Handler, -1, 0, sheetTitle, startRow, startCol);}Map type2Handler = CollectionUtils.asMap(new ExcelCellType[]{ExcelCellType.CRLF, ExcelCellType.OFFSET,ExcelCellType.GOTO, ExcelCellType.SIMPLE, ExcelCellType.MERGED}, new CrlfCellHandler(), new OffsetCellHandler(),new GotoCellHandler(), new SimpleCellHandler(), new MergeCellHandler());//  仅仅校验直接跑一次是否存在覆盖的单元格estimateMaxBounds();boolean[][] dirtyTable = new boolean[estimateRowMax][estimateColMax];CellHandleContext context = new SimpleCellHandleContext().setStartRow(startRow).setStartCol(startCol).setRowIdx(startRow).setColIdx(startCol).setMaxRowOff(1).setMaxColOff(1).setExtra(dirtyTable);for (int i = 0, len = list.size(); i < len; i++) {ExcelCell exCell = list.get(i);CellHandler handler = type2Handler.get(exCell.type());if (handler == null) {Log.err(" 发现了一个匹配不上 type 的 exCell : " + JSONObject.fromObject(exCell).toString() + ", idx : " + i + "l, 忽略掉 ");continue;}context.setExcelCellIdx(i).setExcelCell(exCell);handler.handle(context);}return new ExcelTable(list, idx2Handler, -1, 0, sheetTitle, startRow, startCol);}public ExcelTable debug() {if (CollectionUtils.isEmpty(list)) {return new ExcelTable(list, idx2Handler, -1, 0, sheetTitle, startRow, startCol);}Map type2Handler = CollectionUtils.asMap(new ExcelCellType[]{ExcelCellType.CRLF, ExcelCellType.OFFSET,ExcelCellType.GOTO, ExcelCellType.SIMPLE, ExcelCellType.MERGED}, new com.hx.test07.handler.debug.CrlfCellHandler(),new com.hx.test07.handler.debug.OffsetCellHandler(),new com.hx.test07.handler.debug.GotoCellHandler(),new com.hx.test07.handler.debug.SimpleCellHandler(),new com.hx.test07.handler.debug.MergeCellHandler());//  仅仅校验直接跑一次是否存在覆盖的单元格estimateMaxBounds();boolean[][] dirtyTable = new boolean[estimateRowMax][estimateColMax];CellHandleContext context = new SimpleCellHandleContext().setStartRow(startRow).setStartCol(startCol).setRowIdx(startRow).setColIdx(startCol).setMaxRowOff(1).setMaxColOff(1).setExtra(dirtyTable);for (int i = 0, len = list.size(); i < len; i++) {ExcelCell exCell = list.get(i);CellHandler handler = type2Handler.get(exCell.type());if (handler == null) {Log.err(" 发现了一个匹配不上 type 的 exCell : " + JSONObject.fromObject(exCell).toString() + ", idx : " + i + "l, 忽略掉 ");continue;}context.setExcelCellIdx(i).setExcelCell(exCell);handler.handle(context);}return new ExcelTable(list, idx2Handler, -1, 0, sheetTitle, startRow, startCol);}// ---------------------------------- 辅助方法 --------------------------------/*** 计算可能的最大边界** @return void* @author Jerry.X.He* @date 4/6/2018 5:11 PM* @since 1.0*/private void estimateMaxBounds() {if (CollectionUtils.isEmpty(list)) {return;}//  仅仅校验直接跑一次是否存在覆盖的单元格Point leftUp = new Point(startCol, startRow);int maxRowOff = 1, maxColOff = 1;for (int i = 0, len = list.size(); i < len; i++) {ExcelCell exCell = list.get(i);if (ExcelCellType.CRLF == exCell.type()) {leftUp.y += maxRowOff;leftUp.x = startCol;maxRowOff = 1;} else if (ExcelCellType.OFFSET == exCell.type()) {OffsetCell exCellImpl = (OffsetCell) exCell;leftUp.y += exCellImpl.getYOff();leftUp.x += exCellImpl.getXOff();maxRowOff = 1;} else if (ExcelCellType.GOTO == exCell.type()) {GotoCell exCellImpl = (GotoCell) exCell;leftUp.y = exCellImpl.getNewY();leftUp.x = exCellImpl.getNewX();maxRowOff = 1;} else if (ExcelCellType.SIMPLE == exCell.type()) {ExcelUtils.handleDirection(leftUp, exCell);} else if (ExcelCellType.MERGED == exCell.type()) {maxRowOff = (exCell.height() > maxRowOff) ? exCell.height() : maxRowOff;maxColOff = (exCell.width() > maxColOff) ? exCell.width() : maxColOff;ExcelUtils.handleDirection(leftUp, exCell);} else {// assert}if (leftUp.x > estimateColMax) {estimateColMax = leftUp.x;}if (leftUp.y > estimateRowMax) {estimateRowMax = leftUp.y;}}estimateColMax++;estimateRowMax++;}}

 

ExcelCellUtils.java 

/*** ExcelCellUtils** @author Jerry.X.He* @date 2018/4/6 17:45*/
public final class ExcelCellUtils {private ExcelCellUtils() {Tools.assert0("can't instantiate !");}/*** 创建一个 SimpleCell** @param value value* @return com.hx.test05.Test17PoiBuilder.ExcelCell* @author Jerry.X.He* @date 4/6/2018 11:29 AM* @since 1.0*/public static ExcelCell newSimpleCell(String value) {return new SimpleCell(value);}public static ExcelCell newSimpleCell(String value, Direction direction) {return new SimpleCell(value, direction);}/*** 创建一个 simple excel cell** @param width  width* @param height height* @param value  value* @return com.hx.test05.Test17PoiBuilder.ExcelCell* @author Jerry.X.He* @date 4/6/2018 11:29 AM* @since 1.0*/public static ExcelCell newMergedCell(int width, int height, String value) {return new MergedCell(width, height, value);}public static ExcelCell newMergedCell(int width, int height, String value, Direction direction) {return new MergedCell(width, height, value, direction);}/*** 创建一个 CrlfCell** @return com.hx.test05.Test17PoiBuilder.ExcelCell* @author Jerry.X.He* @date 4/6/2018 11:29 AM* @since 1.0*/public static ExcelCell newCrlfCell() {return CrlfCell.getInstance();}/*** 创建一个 OffsetCell** @param xOff xOff* @param yOff yOff* @return com.hx.test05.Test17PoiBuilder.ExcelCell* @author Jerry.X.He* @date 4/6/2018 11:29 AM* @since 1.0*/public static ExcelCell newOffsetCell(int xOff, int yOff) {return new OffsetCell(xOff, yOff);}/*** 创建一个 GotoCell** @param newX newX* @param newY newY* @return com.hx.test05.Test17PoiBuilder.ExcelCell* @author Jerry.X.He* @date 4/6/2018 11:29 AM* @since 1.0*/public static ExcelCell newGotoCell(int newX, int newY) {return new GotoCell(newX, newY);}}

 

ExcelCell.java

/*** ExcelCell** @author Jerry.X.He* @date 2018/4/6 17:44*/
public interface ExcelCell {/*** 获取 当前单元格的宽** @return* @author Jerry.X.He* @date 4/6/2018 11:12 AM* @since 1.0*/int width();/*** 获取 当前单元格的高** @return* @author Jerry.X.He* @date 4/6/2018 11:12 AM* @since 1.0*/int height();/*** 获取 当前单元格的类型** @return* @author Jerry.X.He* @date 4/6/2018 11:14 AM* @since 1.0*/ExcelCellType type();/*** 当前单元格操作之后的单元格的方向** @return* @author Jerry.X.He* @date 4/6/2018 4:24 PM* @since 1.0*/Direction direction();/*** 获取 当前单元格的数据** @return* @author Jerry.X.He* @date 4/6/2018 11:12 AM* @since 1.0*/String value();/*** toString** @return* @author Jerry.X.He* @date 7/21/2018 11:11 AM* @since 1.0*/String toDebugString();}

 

CellHandler.java

/*** CellHandler** @author Jerry.X.He <970655147@qq.com>* @version 1.0* @date 7/21/2018 10:04 AM*/
public interface CellHandler {/*** ExcelBuilder, ExcelUtils 操作 poi 的相关逻辑的封装** @param context context* @return* @author Jerry.X.He* @date 7/21/2018 10:04 AM* @since 1.0*/void handle(CellHandleContext context);}

 

CellHandleContext.java

/*** HandleCellContext** @author Jerry.X.He <970655147@qq.com>* @version 1.0* @date 7/21/2018 9:43 AM*/
public interface CellHandleContext {// ---------------------------------- getter 方法 --------------------------------/*** 获取 excel model** @return* @author Jerry.X.He* @date 7/21/2018 9:44 AM* @since 1.0*/Workbook getWorkbook();/*** 获取 poi表格** @return* @author Jerry.X.He* @date 7/21/2018 9:44 AM* @since 1.0*/Sheet getSheet();/*** 获取 ExcelTable表格** @return* @author Jerry.X.He* @date 7/21/2018 10:19 AM* @since 1.0*/ExcelTable getExcelTable();/*** 获取 startRow** @return* @author Jerry.X.He* @date 7/21/2018 9:44 AM* @since 1.0*/int getStartRow();/*** 获取 startCol** @return* @author Jerry.X.He* @date 7/21/2018 9:44 AM* @since 1.0*/int getStartCol();/*** 获取 当前操作的 ExcelCell 的索引[在 ExcelTable中]** @return* @author Jerry.X.He* @date 7/21/2018 10:21 AM* @since 1.0*/int getExcelCellIdx();/*** 获取 纵坐标** @return* @author Jerry.X.He* @date 7/21/2018 9:44 AM* @since 1.0*/int getRowIdx();/*** 获取 横坐标** @return* @author Jerry.X.He* @date 7/21/2018 9:44 AM* @since 1.0*/int getColIdx();/*** 获取 当前行操作过程彭中最大的 纵向偏移** @return* @author Jerry.X.He* @date 7/21/2018 9:47 AM* @since 1.0*/int getMaxRowOff();/*** 获取 当前行操作过程彭中最大的 横向偏移** @return* @author Jerry.X.He* @date 7/21/2018 9:47 AM* @since 1.0*/int getMaxColOff();/*** 获取 当前 ExcelCell** @return* @author Jerry.X.He* @date 7/21/2018 9:46 AM* @since 1.0*/ExcelCell getExcelCell();/*** 获取当前 Cell poi 单元格** @return* @author Jerry.X.He* @date 7/21/2018 9:46 AM* @since 1.0*/Cell getCell();/*** 预留对象** @return* @author Jerry.X.He* @date 7/21/2018 9:59 AM* @since 1.0*/Object getExtra();// ---------------------------------- setter 方法 --------------------------------/*** 配置 excel model** @return* @author Jerry.X.He* @date 7/21/2018 9:44 AM* @since 1.0*/CellHandleContext setWorkbook(Workbook workbook);/*** 配置表格** @return* @author Jerry.X.He* @date 7/21/2018 9:44 AM* @since 1.0*/CellHandleContext setSheet(Sheet sheet);/*** 配置 ExcelTable表格** @return* @author Jerry.X.He* @date 7/21/2018 10:19 AM* @since 1.0*/CellHandleContext setExcelTable(ExcelTable table);/*** 获取 startRow** @return* @author Jerry.X.He* @date 7/21/2018 9:44 AM* @since 1.0*/CellHandleContext setStartRow(int startRow);/*** 获取 startCol** @return* @author Jerry.X.He* @date 7/21/2018 9:44 AM* @since 1.0*/CellHandleContext setStartCol(int startCol);/*** 配置 当前操作的 ExcelCell 的索引[在 ExcelTable中]** @return* @author Jerry.X.He* @date 7/21/2018 10:21 AM* @since 1.0*/CellHandleContext setExcelCellIdx(int excelCellIdx);/*** 配置 纵坐标** @return* @author Jerry.X.He* @date 7/21/2018 9:44 AM* @since 1.0*/CellHandleContext setRowIdx(int row);/*** 配置 横坐标** @return* @author Jerry.X.He* @date 7/21/2018 9:44 AM* @since 1.0*/CellHandleContext setColIdx(int col);/*** 配置 当前行操作过程彭中最大的 纵向偏移** @return* @author Jerry.X.He* @date 7/21/2018 9:47 AM* @since 1.0*/CellHandleContext setMaxRowOff(int maxRowOff);/*** 配置 当前行操作过程彭中最大的 横向偏移** @return* @author Jerry.X.He* @date 7/21/2018 9:47 AM* @since 1.0*/CellHandleContext setMaxColOff(int maxColOff);/*** 配置 当前 ExcelCell** @return* @author Jerry.X.He* @date 7/21/2018 9:46 AM* @since 1.0*/CellHandleContext setExcelCell(ExcelCell excelCell);/*** 配置当前 Cell poi 单元格** @return* @author Jerry.X.He* @date 7/21/2018 9:46 AM* @since 1.0*/CellHandleContext setCell(Cell cell);/*** 预留对象** @return* @author Jerry.X.He* @date 7/21/2018 9:59 AM* @since 1.0*/CellHandleContext setExtra(Object extra);}

 

ExcelTable.java

/*** ExcelTable** @author Jerry.X.He* @date 2018/4/6 17:43*/
public class ExcelTable {/*** 存放单元格集合*/private List list;/*** 索引 -> CellHandler*/private Map idx2Handler;/*** 标记的索引*/private int marked;/*** 当前的索引*/private int current;/*** 表格标题*/private String sheetTitle;/*** 渲染开始的行数*/private int startRow;/*** 渲染开始的列数*/private int startCol;public ExcelTable(List list, Map idx2Handler, int marked, int current,String sheetTitle, int startRow, int startCol) {this.list = list;this.idx2Handler = idx2Handler;this.marked = marked;this.current = current;this.sheetTitle = sheetTitle;this.startRow = startRow;this.startCol = startCol;}public boolean hasNext() {return current < list.size();}public ExcelCell next() {if (current >= list.size()) {return null;}return list.get(current++);}public void mark() {marked = current;}public void reset() {if (marked >= 0) {current = marked;marked = -1;}}public String getSheetTitle() {return sheetTitle;}public int getStartRow() {return startRow;}public int getStartCol() {return startCol;}public PoiCellHandler getCellHandler(int idx) {return idx2Handler.get(idx);}}

 

ExcelCell 相关实现 

 

SimpleCell .java : 单个单元格 

/*** SimpleCell** @author Jerry.X.He* @date 2018/4/6 17:46*/
public class SimpleCell implements ExcelCell {/*** value*/private String value;/*** direction*/private Direction direction;public SimpleCell(String value) {this(value, Direction.EAST);}public SimpleCell(String value, Direction direction) {this.value = value;this.direction = direction;}@Overridepublic int width() {return 1;}@Overridepublic int height() {return 1;}@Overridepublic ExcelCellType type() {return ExcelCellType.SIMPLE;}@Overridepublic Direction direction() {return direction;}@Overridepublic String value() {return value;}@Overridepublic String toDebugString() {return new JSONObject().element("type", String.valueOf(type())).element("direction", String.valueOf(direction())).element("value", value).toString();}
}

 

MergedCell .java

/*** MergedCell** @author Jerry.X.He* @date 2018/4/6 17:46*/
public class MergedCell implements ExcelCell {/*** 宽*/private int width;/*** 高*/private int height;/*** value*/private String value;/*** direction*/private Direction direction;public MergedCell(int width, int height, String value, Direction direction) {this.width = width;this.height = height;this.value = value;this.direction = direction;}public MergedCell(int width, int height, String value) {this(width, height, value, Direction.EAST);}public MergedCell(String value) {this(1, 1, value);}@Overridepublic int width() {return width;}@Overridepublic int height() {return height;}@Overridepublic ExcelCellType type() {return ExcelCellType.MERGED;}@Overridepublic Direction direction() {return direction;}@Overridepublic String value() {return value;}@Overridepublic String toDebugString() {return new JSONObject().element("type", String.valueOf(type())).element("direction", String.valueOf(direction())).element("value", value()).element("width", width).element("height", height).toString();}
}

 

附上一个 测试

/*** Test18ExportExBill** @author Jerry.X.He <970655147@qq.com>* @version 1.0* @date 4/6/2018 5:59 PM*/
public class Test18ExportExBill {// Test18ExportExBillpublic static void main(String[] args) throws Exception {//        CellHandler alignCenterHandler = new CachedAlignCenterCellHandler();PoiCellHandler alignLeftHandler = AlignLeftPoiCellHandler.getInstance();PoiCellHandler alignCenterHandler = AlignCenterPoiCellHandler.getInstance();
//        CellHandler alignCenterHandler = null;ExcelTable table = ExcelBuilder.newBuilder().add(ExcelCellUtils.newMergedCell(20, 3, "查看对账异常单")).add(ExcelCellUtils.newCrlfCell()).add(ExcelCellUtils.newMergedCell(2, 1, "对账主题类型")).add(ExcelCellUtils.newMergedCell(2, 1, "汽车客运站")).add(ExcelCellUtils.newMergedCell(1, 1, "")).add(ExcelCellUtils.newMergedCell(2, 1, "对账主体名称")).add(ExcelCellUtils.newMergedCell(2, 1, "自贡汽车客运总站")).add(ExcelCellUtils.newMergedCell(1, 1, "")).add(ExcelCellUtils.newMergedCell(2, 1, "账单日期")).add(ExcelCellUtils.newMergedCell(2, 1, "2018-04-05")).add(ExcelCellUtils.newMergedCell(1, 1, "")).add(ExcelCellUtils.newMergedCell(2, 1, "对账时间")).add(ExcelCellUtils.newMergedCell(2, 1, "2018-04-05 17:00:00")).add(ExcelCellUtils.newMergedCell(1, 1, "")).add(ExcelCellUtils.newCrlfCell()).add(ExcelCellUtils.newSimpleCell("序号")).add(ExcelCellUtils.newMergedCell(2, 1, "系统订单号")).add(ExcelCellUtils.newMergedCell(2, 1, "系统交易时间")).add(ExcelCellUtils.newSimpleCell("交易类型")).add(ExcelCellUtils.newMergedCell(2, 1, "始发站")).add(ExcelCellUtils.newMergedCell(2, 1, "目的地")).add(ExcelCellUtils.newSimpleCell("班次类型")).add(ExcelCellUtils.newSimpleCell("车次号")).add(ExcelCellUtils.newSimpleCell("座位号")).add(ExcelCellUtils.newMergedCell(2, 1, "发车时间")).add(ExcelCellUtils.newSimpleCell("票号")).add(ExcelCellUtils.newSimpleCell("乘车人")).add(ExcelCellUtils.newSimpleCell("车站交易金额(元)")).add(ExcelCellUtils.newSimpleCell("系统交易金额(元)")).add(ExcelCellUtils.newSimpleCell("差异金额(元)")).add(ExcelCellUtils.newCrlfCell()).add(ExcelCellUtils.newSimpleCell("1")).add(ExcelCellUtils.newMergedCell(2, 1, "123456")).add(ExcelCellUtils.newMergedCell(2, 1, "2018-03-09 12:00:00")).add(ExcelCellUtils.newSimpleCell("购票")).add(ExcelCellUtils.newMergedCell(2, 1, "北门车站")).add(ExcelCellUtils.newMergedCell(2, 1, "眉山")).add(ExcelCellUtils.newSimpleCell("固定班")).add(ExcelCellUtils.newSimpleCell("x001")).add(ExcelCellUtils.newSimpleCell("23")).add(ExcelCellUtils.newMergedCell(2, 1, "2018-04-11 10:00")).add(ExcelCellUtils.newSimpleCell("1324312")).add(ExcelCellUtils.newSimpleCell("小牛")).add(ExcelCellUtils.newSimpleCell("216.00")).add(ExcelCellUtils.newSimpleCell("200.00")).add(ExcelCellUtils.newSimpleCell("16.00")).add(ExcelCellUtils.newCrlfCell()).add(ExcelCellUtils.newMergedCell(20, 1, "备注")).add(ExcelCellUtils.newCrlfCell()).add(ExcelCellUtils.newMergedCell(20, 1, "系统交易金额记录错误")).add(ExcelCellUtils.newCrlfCell()).add(ExcelCellUtils.newMergedCell(20, 1, "结算方式")).add(ExcelCellUtils.newCrlfCell()).add(ExcelCellUtils.newMergedCell(20, 1, "按照系统金额中增加6.00元后结算")).add(ExcelCellUtils.newCrlfCell()).add(ExcelCellUtils.newMergedCell(2, 1, "处理人员")).add(ExcelCellUtils.newMergedCell(2, 1, "张三")).add(ExcelCellUtils.newMergedCell(2, 1, "运营中心复审人员")).add(ExcelCellUtils.newMergedCell(2, 1, "李四")).add(ExcelCellUtils.newMergedCell(2, 1, "运营中心复审主管")).add(ExcelCellUtils.newMergedCell(2, 1, "王五")).add(ExcelCellUtils.newMergedCell(2, 1, "财务复核人员")).add(ExcelCellUtils.newMergedCell(2, 1, "刘七")).add(ExcelCellUtils.newMergedCell(2, 1, "车站复核人员")).add(ExcelCellUtils.newMergedCell(2, 1, "胡八")).add(ExcelCellUtils.newCrlfCell()).add(ExcelCellUtils.newMergedCell(2, 1, "日期")).add(ExcelCellUtils.newMergedCell(2, 1, "2018-05-01")).add(ExcelCellUtils.newMergedCell(2, 1, "日期")).add(ExcelCellUtils.newMergedCell(2, 1, "2018-05-02")).add(ExcelCellUtils.newMergedCell(2, 1, "日期")).add(ExcelCellUtils.newMergedCell(2, 1, "2018-05-03")).add(ExcelCellUtils.newMergedCell(2, 1, "日期")).add(ExcelCellUtils.newMergedCell(2, 1, "2018-05-04")).add(ExcelCellUtils.newMergedCell(2, 1, "日期")).add(ExcelCellUtils.newMergedCell(2, 1, "2018-05-05")).debug();Workbook workBook = ExcelUtils.createExcel(table);//        CellStyle cellStyle = workBook.createCellStyle();
//        Row row = workBook.getSheetAt(0).getRow(0);
//        Cell cell = row.getCell(0);
//        cellStyle.cloneStyleFrom(cell.getCellStyle());
//        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//        cell.setCellStyle(cellStyle);ExcelUtils.handleCells(workBook.getSheetAt(0), alignCenterHandler, 0, 3, 0, 20);OutputStream os = new BufferedOutputStream(new FileOutputStream(Tools.getTmpPath("exBill", Tools.XLS)));workBook.write(os);os.flush();}}

使用效果

 

 

======================= add at 2019.03.23 ======================= 

在补充 一些扩展, 一些思路 来自于我们同事, zhou兄, hu兄, wang兄 

1. 使用给定的 List> 作为 表格, inflate 到 给定的位置 

    public static Sheet fillExcel(ExcelListListEntity params, Sheet sheet) {AssertUtils.assert0(params != null, "'params' can't be null ");AssertUtils.assert0(sheet != null, "'sheet' can't be null ");if (CollectionUtils.isEmpty(params.getTable())) {return sheet;}// 填充 title, 数据列 到 excelTable 模型ExcelBuilder builder = ExcelBuilder.newBuilder();builder.setStartRow(params.getStartRow()).setStartCol(params.getStartCol());if (!CollectionUtils.isEmpty(params.getColumnTitles())) {for (String str : params.getColumnTitles()) {builder.add(ExcelCellUtils.newSimpleCell(str));}builder.add(ExcelCellUtils.newCrlfCell());}java.util.List> table = params.getTable();for (java.util.List row : table) {for (String cell : row) {builder.add(ExcelCellUtils.newSimpleCell(cell));}builder.add(ExcelCellUtils.newCrlfCell());}// 填充数据到 sheetfillExcel(builder.build(), sheet);return sheet;}

 

2. 使用给定的 List> 作为每一行数据容器, List 作为 key 取数, 整合的逻辑表格, inflate 到 给定的位置  

    public static Sheet fillExcel(ExcelListMapEntity params, Sheet sheet) {AssertUtils.assert0(params != null, "'params' can't be null ");AssertUtils.assert0(sheet != null, "'sheet' can't be null ");if (CollectionUtils.isEmpty(params.getTable())) {return sheet;}// 如果 columnKeys 为空, 填充默认的 columnKeysif (CollectionUtils.isEmpty(params.getColumnKeys())) {Map sample = params.getTable().get(0);java.util.List columnKeys = new ArrayList<>(sample.keySet());params.setColumnKeys(columnKeys);}// 填充 title, 数据列 到 excelTable 模型ExcelBuilder builder = ExcelBuilder.newBuilder();builder.setStartRow(params.getStartRow()).setStartCol(params.getStartCol());if (!CollectionUtils.isEmpty(params.getColumnTitles())) {for (String str : params.getColumnTitles()) {builder.add(ExcelCellUtils.newSimpleCell(str));}builder.add(ExcelCellUtils.newCrlfCell());}java.util.List> table = params.getTable();for (Map row : table) {for (String columnKey : params.getColumnKeys()) {builder.add(ExcelCellUtils.newSimpleCell(row.get(columnKey)));}builder.add(ExcelCellUtils.newCrlfCell());}// 填充数据到 sheetfillExcel(builder.build(), sheet);return sheet;}

 

3. 基于已有的 excel 模板, 以及 JSONPath 来渲染数据 

   /*** 使用给定的 excel 模板, 渲染给定的 上下文的数据** @param context context* @param sheet   需要填充到的 sheet* @return org.apache.poi.ss.usermodel.Sheet* @author Jerry.X.He* @date 2019/2/2 9:45*/public static Sheet fillExcelByTemplate(JSONObject context, Sheet sheet, String varPrefix, String varSuffix, String defaultContent) {AssertUtils.assert0(context != null, "'context' can't be null ");AssertUtils.assert0(sheet != null, "'sheet' can't be null ");int lastRowIdx = sheet.getLastRowNum();String contextStr = JSON.toJSONString(context);for (int rowIdx = 0; rowIdx <= lastRowIdx; rowIdx++) {Row row = createRowIfNeed(sheet, rowIdx);// step1. 统计是否有 表达式可以提取多行内容, 进行多行特殊处理BoxedInt maxRowInExpr = new BoxedInt(1);BoxedBoolean columnWithCollection = new BoxedBoolean(false);java.util.List columnContents = new ArrayList<>();collectPathValuesByJSONPath(maxRowInExpr, columnWithCollection, columnContents, contextStr, row, varPrefix, varSuffix);// step2. 对于单行数据处理 和 多行数据处理 分开处理if ((maxRowInExpr.getValue() == 1) && (!columnWithCollection.getValue())) {handleForSingleRowResult(sheet, columnContents, rowIdx, defaultContent);continue;}handleForMultiRowResult(sheet, columnContents, rowIdx, maxRowInExpr.getValue(), defaultContent);int copiedRowCnt = maxRowInExpr.getValue() - 1;lastRowIdx += copiedRowCnt;rowIdx += copiedRowCnt;}return sheet;}public static Sheet fillExcelByTemplate(JSONObject context, Sheet sheet, String defaultContent) {return fillExcelByTemplate(context, sheet, "{", "}", defaultContent);}public static Sheet fillExcelByTemplate(JSONObject context, Sheet sheet) {return fillExcelByTemplate(context, sheet, "{", "}", Constants.EMPTY_STR);}/*** 移动给定的 开始行数, 结束行数 到给定的位置, 注意 : 向前 或者 向后拷贝* refer : https://mawj0326-163-com.iteye.com/blog/1563244** @param sheet       表格* @param startRowIdx 开始行数* @param endRowIdx   结束行数* @param newRowIdx   复制到的行数* @return void* @author Jerry.X.He* @date 2019/2/2 11:01*/public static void moveExcelRows(Sheet sheet, int startRowIdx, int endRowIdx, int newRowIdx) {AssertUtils.assert0(sheet != null, "'sheet' can't be null ");AssertUtils.assert0(startRowIdx >= 0, "'startRowIdx' must gt 0");AssertUtils.assert0(endRowIdx >= startRowIdx, "'endRowIdx' must gte 'startRowIdx'");AssertUtils.assert0(newRowIdx >= 0, "'newRowIdx' must gt 0");// 如果移动当前行, 到当前行, do nothingif (startRowIdx == newRowIdx) {return;}int rowOff = (newRowIdx - startRowIdx);boolean positiveOrNegative = newRowIdx > startRowIdx;// add IdxIterator ??, to generate idx of positive or negativeint numMergedRegions = sheet.getNumMergedRegions();IIdxIterator idxIte = positiveOrNegative ? new IncIdxIterator(numMergedRegions - 1, -1, numMergedRegions): new IncIdxIterator(0, 1, numMergedRegions);// 检查所有的 合并的单元格, 并进行拷贝while (idxIte.hasNext()) {int i = idxIte.next();CellRangeAddress origRegion = sheet.getMergedRegion(i);if ((origRegion.getFirstRow() >= startRowIdx) && (origRegion.getLastRow() <= endRowIdx)) {int newRowFrom = origRegion.getFirstRow() + rowOff, newRowTo = origRegion.getLastRow() + rowOff;CellRangeAddress newRegion = origRegion.copy();newRegion.setFirstRow(newRowFrom);newRegion.setFirstColumn(origRegion.getFirstColumn());newRegion.setLastRow(newRowTo);newRegion.setLastColumn(origRegion.getLastColumn());sheet.addMergedRegion(newRegion);// 复制 mergedCell 并重置 srcRegion, 并移除原有的 mergedRegioncopyMergedRegionCell(sheet, origRegion, newRegion);resetCell(createCellIfNeed(createRowIfNeed(sheet, origRegion.getFirstRow()), origRegion.getFirstColumn()));sheet.removeMergedRegion(i);}}// 检查所有的 单个单元格, 并进行拷贝int movedRowCnt = endRowIdx - startRowIdx + 1;IIdxIterator rowIdxIte = positiveOrNegative ? new IncIdxIterator(endRowIdx, -1, movedRowCnt): new IncIdxIterator(startRowIdx, 1, movedRowCnt);while (rowIdxIte.hasNext()) {int rowIdx = rowIdxIte.next();Row row = createRowIfNeed(sheet, rowIdx);Row newRow = createRowIfNeed(sheet, rowIdx + rowOff);newRow.setHeight(row.getHeight());int lastCol = row.getLastCellNum();// 复制给定的单元格for (int colIdx = 0; colIdx < lastCol; colIdx++) {// merged cell already proceedif (!isMergedCell(sheet, rowIdx + rowOff, colIdx)) {Cell cell = createCellIfNeed(row, colIdx);Cell newCell = createCellIfNeed(newRow, colIdx);copyCell(cell, newCell);resetCell(cell);}}}}/*** 复制给定的 开始行数, 结束行数 到给定的位置, 注意 : 向前 或者 向后拷贝* refer : https://mawj0326-163-com.iteye.com/blog/1563244** @param sheet       表格* @param startRowIdx 开始行数* @param endRowIdx   结束行数* @param newRowIdx   复制到的行数* @return void* @author Jerry.X.He* @date 2019/2/2 11:01*/public static void copyExcelRows(Sheet sheet, int startRowIdx, int endRowIdx, int newRowIdx) {AssertUtils.assert0(sheet != null, "'sheet' can't be null ");AssertUtils.assert0(startRowIdx >= 0, "'startRowIdx' must gt 0");AssertUtils.assert0(endRowIdx >= startRowIdx, "'endRowIdx' must gte 'startRowIdx'");AssertUtils.assert0(newRowIdx >= 0, "'newRowIdx' must gt 0");// 如果拷贝当前行, 到当前行, do nothingif (startRowIdx == newRowIdx) {return;}int rowOff = (newRowIdx - startRowIdx);boolean positiveOrNegative = newRowIdx > startRowIdx;// add IdxIterator ??, to generate idx of positive or negativeint numMergedRegions = sheet.getNumMergedRegions();IIdxIterator idxIte = positiveOrNegative ? new IncIdxIterator(numMergedRegions - 1, -1, numMergedRegions): new IncIdxIterator(0, 1, numMergedRegions);// 检查所有的 合并的单元格, 并进行拷贝while (idxIte.hasNext()) {int i = idxIte.next();CellRangeAddress origRegion = sheet.getMergedRegion(i);if ((origRegion.getFirstRow() >= startRowIdx) && (origRegion.getLastRow() <= endRowIdx)) {int targetRowFrom = origRegion.getFirstRow() + rowOff, targetRowTo = origRegion.getLastRow() + rowOff;CellRangeAddress newRegion = origRegion.copy();newRegion.setFirstRow(targetRowFrom);newRegion.setFirstColumn(origRegion.getFirstColumn());newRegion.setLastRow(targetRowTo);newRegion.setLastColumn(origRegion.getLastColumn());sheet.addMergedRegion(newRegion);// 复制 mergedCellcopyMergedRegionCell(sheet, origRegion, newRegion);}}// 检查所有的 单个单元格, 并进行拷贝int movedRowCnt = endRowIdx - startRowIdx + 1;IIdxIterator rowIdxIte = positiveOrNegative ? new IncIdxIterator(endRowIdx, -1, movedRowCnt): new IncIdxIterator(startRowIdx, 1, movedRowCnt);while (rowIdxIte.hasNext()) {int rowIdx = rowIdxIte.next();Row row = createRowIfNeed(sheet, rowIdx);Row newRow = createRowIfNeed(sheet, rowIdx + rowOff);newRow.setHeight(row.getHeight());int lastCol = row.getLastCellNum();// 复制给定的单元格for (int colIdx = 0; colIdx < lastCol; colIdx++) {// merged cell already proceedif (!isMergedCell(sheet, rowIdx + rowOff, colIdx)) {Cell cell = createCellIfNeed(row, colIdx);Cell newCell = createCellIfNeed(newRow, colIdx);copyCell(cell, newCell);}}}}// ---------------------------------- 辅助方法 --------------------------------/*** 解析给定的行 的解析出的结果, 放于 columnContents, 最大的行数 放于 maxRowInExpr** @param maxRowInExpr   改行分析出的结果, 最大的行数* @param columnContents 该行每一列, 解析出来的结果 [每一个元素, 可能是单个元素, 可能是集合元素]* @param contextStr     上下文的数据 字符串* @param row            当前行的元素* @param varPrefix      变量声明的 prefix* @param varSuffix      变量声明的 suffix* @return void* @author Jerry.X.He* @date 2019/2/2 16:33*/private static void collectPathValuesByJSONPath(BoxedInt maxRowInExpr, BoxedBoolean columnWithCollection,java.util.List columnContents, String contextStr,Row row, String varPrefix, String varSuffix) {int lastColIdx = row.getLastCellNum();for (int j = 0; j < lastColIdx; j++) {Cell cell = createCellIfNeed(row, j);String value = cell.getStringCellValue();if (!(value.startsWith(varPrefix) && value.endsWith(varSuffix))) {columnContents.add(value);continue;}String expr = value.substring(varPrefix.length(), value.length() - varSuffix.length());Object valFromJSONPath = JSONPath.read(contextStr, expr);if (valFromJSONPath instanceof Collection) {columnWithCollection.setValue(true);int szInJSONPath = CollectionUtils.size((Collection) valFromJSONPath);if (szInJSONPath > maxRowInExpr.getValue()) {maxRowInExpr.setValue(szInJSONPath);}}columnContents.add(valFromJSONPath);}}/*** 如果当前行 解析出单个数据 的情况下的业务处理, 直接 inflate 这一行的数据** @param sheet          需要填充的表格* @param columnContents 需要填充数据的多列[每一个元素, 均是单个元素]* @param currentRowIdx  当前行的索引* @return void* @author Jerry.X.He* @date 2019/2/2 16:31*/private static void handleForSingleRowResult(Sheet sheet, java.util.List columnContents, int currentRowIdx, String defaultContent) {Row row = createRowIfNeed(sheet, currentRowIdx);int lastColIdx = row.getLastCellNum();for (int columnIdx = 0; columnIdx < lastColIdx; columnIdx++) {Cell cell = createCellIfNeed(row, columnIdx);Object replacedObject = columnContents.get(columnIdx);String replacedValue = defaultContent;if (replacedObject != null) {replacedValue = String.valueOf(replacedObject);}cell.setCellValue(replacedValue);}}/*** 如果当前行 解析出多个数据 的情况下的业务处理** @param sheet          需要填充的表格* @param columnContents 需要填充数据的多列[每一个元素, 可能是单个元素, 可能是集合元素[集合元素 通过rowOffIdx获取对应的数据]]* @param currentRowIdx  当前行的索引* @return void* @author Jerry.X.He* @date 2019/2/2 16:31*/private static void handleForMultiRowResult(Sheet sheet, List columnContents, int currentRowIdx, int maxRowInExpr, String defaultContent) {int lastRowIdx = sheet.getLastRowNum();// `maxRowInExpr - 1`, 给定的 原始模板 会占用一行moveExcelRows(sheet, currentRowIdx + 1, lastRowIdx, currentRowIdx + (maxRowInExpr));for (int rowOffIdx = 0, rowOffMax = maxRowInExpr; rowOffIdx < rowOffMax; rowOffIdx++) {// 复制 第 i 行 到 i + rowOffIdx 行, 以免格式存在 可能和 第 i 行 不一样copyExcelRows(sheet, currentRowIdx, currentRowIdx, currentRowIdx + rowOffIdx);Row copiedRow = createRowIfNeed(sheet, currentRowIdx + rowOffIdx);int lastColIdx = copiedRow.getLastCellNum();for (int colIdx = 0; colIdx < lastColIdx; colIdx++) {Object columnContent = columnContents.get(colIdx);Cell copiedCell = createCellIfNeed(copiedRow, colIdx);// if `columnContent == null`, then the following instanceof should return trueif (!(columnContent instanceof Collection)) {copiedCell.setCellValue(String.valueOf(columnContent));} else {Object replacedObject = iterateNOfCollection((Collection) columnContent, rowOffIdx);String replacedValue = defaultContent;if (replacedObject != null) {replacedValue = String.valueOf(replacedObject);}copiedCell.setCellValue(replacedValue);}}}} 

 
 

4. 添加一个合并的单元格 
 

    /*** 在给定的 sheet 里面创建一个 给定的矩阵的 合并的单元格** @param sheet    sheet* @param firstRow firstRow* @param firstCol firstCol* @param rowCnt   rowCnt* @param colCnt   colCnt* @param content  content* @return void* @author Jerry.X.He* @date 2019/2/14 18:26*/public static void addMergedRegion(Sheet sheet, int firstRow, int firstCol, int rowCnt, int colCnt, String content) {CellRangeAddress newRegion = new CellRangeAddress(firstRow, firstRow + rowCnt - 1, firstCol, firstCol + colCnt - 1);sheet.addMergedRegion(newRegion);Cell newRegionCell = createCellIfNotExists(sheet, newRegion.getFirstRow(), newRegion.getFirstColumn());newRegionCell.setCellValue(content);}
 

 
 

使用效果 
 


  
 

 
 

 
 

相关代码下载 : https://download.csdn.net/download/u011039332/10597077
 

 
                        

                        

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！

                        
                        

                    

                    



                    


    
    




                    

    

    




                    

    

        
        

            
相关文章

        

                

            
Duilib中list控件支持ctrl和shif多行选中的实现

        

                

            
[ICML2015]Batch Normalization:Accelerating Deep Network Training by Reducing Internal Covariate Shif

        

                

            
win10系统 微软输入法 于eclipse ctrl+shif+f冲突间接处理办法

        

                

            
Codeforces Round #259 (Div. 2) B. Little Pony and Sort by Shif

        

                

            
读LDD3，内存映射与DMA--PAGE_SHIF…

        

                

            
VMware虚拟机安装XP【要先分区，再设置BOOT 启动CD，shif+上移】

        

                

            
更换iBus五笔的左与右Shif

        

                

            
sublime ctrl+shif+f 没用解决办法

        

                

            
idea 对 ctrl + z 的撤销 是 ctrl + shif + z

        

                

            
计算机最早的设计师应用于,计算机应用基础选择题doc.doc

        

                

            
win10自带截图神器：Win+Shift+S

        

                

            
Python基础之文件目录操作

        

                

            
python简述目录_Python基础之文件目录操作(示例代码)

        

                

            
tp5 如何做数据采集

        

                

            
任务2-7(服务器字体+阿里巴巴矢量库)

        

                

            
html标签（1)：h1~h6,p,br,pre,hr

        

                

            
TI 电量计介绍与芯片选型指南

        

                

            
几款TI电源芯片简介

        

                

            
TI DSP芯片C2000系列读取FLASH数据

        

                

            
德州仪器(Ti)平台嵌入式开发基础

        

                

            
TI三相电机智能栅极驱动芯片特点分类

        

                

            
省选模拟（12.08） T3 圈圈圈圈圈圈圈圈

        

                

            
Hadoop生态圈技术栈（上）

        

                

            
大数据开发基础入门与项目实战（三）Hadoop核心及生态圈技术栈之6.Impala交互式查询

        

                

            
小猿圈之Linux下Mysql 操作命令

        

                

            
大数据Hadoop生态圈常用面试题

        

                

            
大数据开发基础入门与项目实战（三）Hadoop核心及生态圈技术栈之4.Hive DDL、DQL和数据操作

        

                

            
备战Noip2018模拟赛11（B组）T3 Monogatari 物语

        

                

            
【智能优化算法-圆圈搜索算法】基于圆圈搜索算法Circle Search Algorithm求解单目标优化问题附matlab代码

        

                

            
NYOJ 78 圈水池

        

                

            
递归问题 跑道 汽车 绕圈问题 Python实现

        

                

            
Hadoop生态圈（三）：MapReduce