java poi sheet_Java POI Excel sheet合并

http://blog.sina.com.cn/s/blog_73d38dbc0100r2ob.html

http://blog.163.com/tangweibo_good/blog/static/7749240920114265535652/

上面这个163地址的程序实现了同一个Excel不同工作簿的拷贝。

在上面的基础上上，我实现了不同Excel工作簿的拷贝，重点在样式的拷贝。

由于种种原因，在cell的样式拷贝时需要重新创建style对象

//样式的设置

HSSFCellStyle cStyle = destWorkBook.createCellStyle();

cStyle.cloneStyleFrom(sourceCell.getCellStyle());

targetCell.setCellStyle(cStyle);

调用示例

public static HSSFWorkbook mergeHSSFWorkbooks(HSSFWorkbook[] workbooks) {

if(workbooks == null || workbooks.length == 0){

return null;

}else if(workbooks.length == 1){

return workbooks[0];

}

HSSFWorkbook wbFirst = workbooks[0];

HSSFSheet toSheet = wbFirst.getSheetAt(0);

for (int i = 1; i < workbooks.length; i++) {

HSSFWorkbook wb = workbooks[i];

HSSFSheet fromsheet = wb.getSheetAt(0);

copyRows(wbFirst, wb, fromsheet, toSheet, fromsheet.getFirstRowNum(), fromsheet.getLastRowNum(), toSheet.getLastRowNum());

}

return wbFirst;

}

具体的拷贝代码

/**

* @param destWorkBook 目标workbook

* @param sourceWorkBook 源workbook

* @param sourceSheet 源sheet

* @param targetSheet 目sheet

* @param pStartRow 起始读取行

* @param pEndRow 结束读取行

* @param pPosition 目标保存

*/

public static void copyRows(HSSFWorkbook destWorkBook, HSSFWorkbook sourceWorkBook, HSSFSheet sourceSheet,HSSFSheet targetSheet, int pStartRow, int pEndRow, int pPosition) {

HSSFRow sourceRow = null;

HSSFRow targetRow = null;

HSSFCell sourceCell = null;

HSSFCell targetCell = null;

int cType;

int i;

int j;

int targetRowFrom;

int targetRowTo;

if ((pStartRow == -1) || (pEndRow == -1)) {

return;

}

List oldRanges = new ArrayList();

for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {

oldRanges.add(sourceSheet.getMergedRegion(i));

}

// 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式

for (int k = 0; k < oldRanges.size(); k++) {

CellRangeAddress oldRange = oldRanges.get(k);

CellRangeAddress newRange = new CellRangeAddress(oldRange

.getFirstRow(), oldRange.getLastRow(), oldRange

.getFirstColumn(), oldRange.getLastColumn());

if (oldRange.getFirstRow() >= pStartRow

&& oldRange.getLastRow() <= pEndRow) {

targetRowFrom = oldRange.getFirstRow() - pStartRow + pPosition;

targetRowTo = oldRange.getLastRow() - pStartRow + pPosition;

oldRange.setFirstRow(targetRowFrom);

oldRange.setLastRow(targetRowTo);

targetSheet.addMergedRegion(oldRange);

sourceSheet.addMergedRegion(newRange);

}

}

// 设置列宽

for (i = pStartRow; i <= pEndRow; i++) {

sourceRow = sourceSheet.getRow(i);

if (sourceRow != null) {

for (j = sourceRow.getLastCellNum(); j > sourceRow.getFirstCellNum(); j--) {

targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));

targetSheet.setColumnHidden(j, false);

}

break;

}

}

// 拷贝行并填充数据

for (; i <= pEndRow; i++) {

sourceRow = sourceSheet.getRow(i);

if (sourceRow == null) {

continue;

}

targetRow = targetSheet.createRow(i - pStartRow + pPosition);

targetRow.setHeight(sourceRow.getHeight());

for (j = sourceRow.getFirstCellNum(); j <= sourceRow.getPhysicalNumberOfCells(); j++) {

sourceCell = sourceRow.getCell(j);

if (sourceCell == null) {

continue;

}

targetCell = targetRow.createCell(j);

//样式的设置

HSSFCellStyle cStyle = destWorkBook.createCellStyle();

cStyle.cloneStyleFrom(sourceCell.getCellStyle());

targetCell.setCellStyle(cStyle);

cType = sourceCell.getCellType();

targetCell.setCellType(cType);

switch (cType) {

case HSSFCell.CELL_TYPE_BOOLEAN:

targetCell.setCellValue(sourceCell.getBooleanCellValue());

// System.out.println("--------TYPE_BOOLEAN:" + targetCell.getBooleanCellValue());

break;

case HSSFCell.CELL_TYPE_ERROR:

targetCell.setCellErrorValue(sourceCell.getErrorCellValue());

// System.out.println("--------TYPE_ERROR:" + targetCell.getErrorCellValue());

break;

case HSSFCell.CELL_TYPE_FORMULA:

// parseFormula这个函数的用途在后面说明

targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula()));

// System.out.println("--------TYPE_FORMULA:" + targetCell.getCellFormula());

break;

case HSSFCell.CELL_TYPE_NUMERIC:

targetCell.setCellValue(sourceCell.getNumericCellValue());

// System.out.println("--------TYPE_NUMERIC:" + targetCell.getNumericCellValue());

break;

case HSSFCell.CELL_TYPE_STRING:

targetCell.setCellValue(sourceCell.getRichStringCellValue());

// System.out.println("--------TYPE_STRING:" + i + targetCell.getRichStringCellValue());

break;

}

}

}

}

/**

* 处理公式

* @param pPOIFormula

* @return

*/

private static String parseFormula(String pPOIFormula) {

final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$

StringBuffer result = null;

int index;

result = new StringBuffer();

index = pPOIFormula.indexOf(cstReplaceString);

if (index >= 0) {

result.append(pPOIFormula.substring(0, index));

result.append(pPOIFormula.substring(index + cstReplaceString.length()));

} else {

result.append(pPOIFormula);

}

return result.toString();

}

0

顶

0

踩

分享到：

2014-03-19 10:59

浏览 5750

评论