public static void copyRows(HSSFWorkbook wb, String pSourceSheetName, String pTargetSheetName, int pStartRow,
int pEndRow, int pPosition) {
HSSFRow sourceRow = null;
HSSFRow targetRow = null;
HSSFCell sourceCell = null;
HSSFCell targetCell = null;
HSSFSheet sourceSheet = null;
HSSFSheet targetSheet = null;
int cType;
int i;
int j;
int targetRowFrom;
int targetRowTo;
if ((pStartRow == -1) || (pEndRow == -1)) {
return;
}
sourceSheet = wb.getSheet(pSourceSheetName);
targetSheet = wb.getSheet(pTargetSheetName);
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);
// targetCell.setEncoding(((Object) sourceCell).getEncoding());
targetCell.setCellStyle(sourceCell.getCellStyle());
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;
}
}
}
}
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();
}
|