由于工作上的需要,特地研究了下Excel合并的问题,现贴出来,希望能帮到有需要的同行
参考资料
[url]http://blog.sina.com.cn/s/blog_73d38dbc0100r2ob.html[/url]
[url]http://blog.163.com/tangweibo_good/blog/static/7749240920114265535652/[/url]
上面这个163地址的程序实现了同一个Excel不同工作簿的拷贝。
在上面的基础上上,我实现了不同Excel工作簿的拷贝,重点在样式的拷贝。
由于种种原因,在cell的样式拷贝时需要重新创建style对象
调用示例
具体的拷贝代码
参考资料
[url]http://blog.sina.com.cn/s/blog_73d38dbc0100r2ob.html[/url]
[url]http://blog.163.com/tangweibo_good/blog/static/7749240920114265535652/[/url]
上面这个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<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>();
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();
}