慕容的工作随笔


分享技术经验、创业副业经验、AI教程、精品素材等

Java POI Excel sheet合并

2018年3月26日 0条评论 443次阅读 0人点赞 慕容

转自：http://zhuyufufu.iteye.com/blog/2033386

由于工作上的需要，特地研究了下Excel合并的问题，现贴出来，希望能帮到有需要的同行

参考资料
http://blog.sina.com.cn/s/blog_73d38dbc0100r2ob.html
http://blog.163.com/tangweibo_good/blog/static/7749240920114265535652/

上面这个163地址的程序实现了同一个Excel不同工作簿的拷贝。
在上面的基础上上，我实现了不同Excel工作簿的拷贝，重点在样式的拷贝。
由于种种原因，在cell的样式拷贝时需要重新创建style对象

Java代码
  1. //样式的设置
  2.                 HSSFCellStyle cStyle = destWorkBook.createCellStyle();
  3.                 cStyle.cloneStyleFrom(sourceCell.getCellStyle());
  4.                 targetCell.setCellStyle(cStyle);

调用示例

Java代码
  1. public static HSSFWorkbook mergeHSSFWorkbooks(HSSFWorkbook[] workbooks) {
  2.         if(workbooks == null || workbooks.length == 0){
  3.             return null;
  4.         }else if(workbooks.length == 1){
  5.             return workbooks[0];
  6.         }
  7.         HSSFWorkbook wbFirst = workbooks[0];
  8.         HSSFSheet toSheet = wbFirst.getSheetAt(0);
  9.         for (int i = 1; i < workbooks.length; i++) {
  10.             HSSFWorkbook wb = workbooks[i];
  11.             HSSFSheet fromsheet = wb.getSheetAt(0);
  12.             copyRows(wbFirst, wb, fromsheet, toSheet, fromsheet.getFirstRowNum(), fromsheet.getLastRowNum(), toSheet.getLastRowNum());
  13.         }
  14.         return wbFirst;
  15.     }

具体的拷贝代码

Java代码
  1. /**
  2.  * @param destWorkBook 目标workbook
  3.  * @param sourceWorkBook 源workbook
  4.  * @param sourceSheet 源sheet
  5.  * @param targetSheet 目sheet
  6.  * @param pStartRow 起始读取行
  7.  * @param pEndRow 结束读取行
  8.  * @param pPosition 目标保存
  9.  */
  10. public static void copyRows(HSSFWorkbook destWorkBook, HSSFWorkbook sourceWorkBook, HSSFSheet sourceSheet,HSSFSheet targetSheet, int pStartRow, int pEndRow, int pPosition) {
  11.     HSSFRow sourceRow = null;
  12.     HSSFRow targetRow = null;
  13.     HSSFCell sourceCell = null;
  14.     HSSFCell targetCell = null;
  15.     int cType;
  16.     int i;
  17.     int j;
  18.     int targetRowFrom;
  19.     int targetRowTo;
  21.     if ((pStartRow == -1) || (pEndRow == -1)) {
  22.         return;
  23.     }
  25.     List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>();
  26.     for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
  27.         oldRanges.add(sourceSheet.getMergedRegion(i));
  28.     }
  30.     // 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式
  31.     for (int k = 0; k < oldRanges.size(); k++) {
  32.         CellRangeAddress oldRange = oldRanges.get(k);
  33.         CellRangeAddress newRange = new CellRangeAddress(oldRange
  34.                 .getFirstRow(), oldRange.getLastRow(), oldRange
  35.                 .getFirstColumn(), oldRange.getLastColumn());
  37.         if (oldRange.getFirstRow() >= pStartRow
  38.                 && oldRange.getLastRow() <= pEndRow) {
  39.             targetRowFrom = oldRange.getFirstRow() - pStartRow + pPosition;
  40.             targetRowTo = oldRange.getLastRow() - pStartRow + pPosition;
  41.             oldRange.setFirstRow(targetRowFrom);
  42.             oldRange.setLastRow(targetRowTo);
  43.             targetSheet.addMergedRegion(oldRange);
  44.             sourceSheet.addMergedRegion(newRange);
  45.         }
  46.     }
  47.     // 设置列宽
  48.     for (i = pStartRow; i <= pEndRow; i++) {
  49.         sourceRow = sourceSheet.getRow(i);
  50.         if (sourceRow != null) {
  51.             for (j = sourceRow.getLastCellNum(); j > sourceRow.getFirstCellNum(); j--) {
  52.                 targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));
  53.                 targetSheet.setColumnHidden(j, false);
  54.             }
  55.             break;
  56.         }
  57.     }
  58.     // 拷贝行并填充数据
  59.     for (; i <= pEndRow; i++) {
  60.         sourceRow = sourceSheet.getRow(i);
  61.         if (sourceRow == null) {
  62.             continue;
  63.         }
  64.         targetRow = targetSheet.createRow(i - pStartRow + pPosition);
  65.         targetRow.setHeight(sourceRow.getHeight());
  66.         for (j = sourceRow.getFirstCellNum(); j <= sourceRow.getPhysicalNumberOfCells(); j++) {
  67.             sourceCell = sourceRow.getCell(j);
  68.             if (sourceCell == null) {
  69.                 continue;
  70.             }
  71.             targetCell = targetRow.createCell(j);
  73.             //样式的设置
  74.             HSSFCellStyle cStyle = destWorkBook.createCellStyle();
  75.             cStyle.cloneStyleFrom(sourceCell.getCellStyle());
  76.             targetCell.setCellStyle(cStyle);
  78.             cType = sourceCell.getCellType();
  79.             targetCell.setCellType(cType);
  80.             switch (cType) {
  81.             case HSSFCell.CELL_TYPE_BOOLEAN:
  82.                 targetCell.setCellValue(sourceCell.getBooleanCellValue());
  83.                 // System.out.println("--------TYPE_BOOLEAN:" + targetCell.getBooleanCellValue());
  84.                 break;
  85.             case HSSFCell.CELL_TYPE_ERROR:
  86.                 targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
  87.                 // System.out.println("--------TYPE_ERROR:" + targetCell.getErrorCellValue());
  88.                 break;
  89.             case HSSFCell.CELL_TYPE_FORMULA:
  90.                 // parseFormula这个函数的用途在后面说明
  91.                 targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula()));
  92.                 // System.out.println("--------TYPE_FORMULA:" + targetCell.getCellFormula());
  93.                 break;
  94.             case HSSFCell.CELL_TYPE_NUMERIC:
  95.                 targetCell.setCellValue(sourceCell.getNumericCellValue());
  96.                 // System.out.println("--------TYPE_NUMERIC:" + targetCell.getNumericCellValue());
  97.                 break;
  98.             case HSSFCell.CELL_TYPE_STRING:
  99.                 targetCell.setCellValue(sourceCell.getRichStringCellValue());
  100.                 // System.out.println("--------TYPE_STRING:" + i + targetCell.getRichStringCellValue());
  101.                 break;
  102.             }
  103.         }
  104.     }
  105. }
  107. /**
  108.  * 处理公式
  109.  * @param pPOIFormula
  110.  * @return
  111.  */
  112. private static String parseFormula(String pPOIFormula) {
  113.     final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
  114.     StringBuffer result = null;
  115.     int index;
  116.     result = new StringBuffer();
  117.     index = pPOIFormula.indexOf(cstReplaceString);
  118.     if (index >= 0) {
  119.         result.append(pPOIFormula.substring(0, index));
  120.         result.append(pPOIFormula.substring(index + cstReplaceString.length()));
  121.     } else {
  122.         result.append(pPOIFormula);
  123.     }
  124.     return result.toString();
  125. }
点赞
No Tag
最后编辑：2018年3月26日

发表回复

电子邮件地址不会被公开。必填项已用 * 标注