上接 Java POI Excel sheet 合并
http://zhuyufufu.iteye.com/blog/2033386
Java POI Excel sheet 合并问题解决
http://zhuyufufu.iteye.com/blog/2035033
上面的博文生成的拷贝Excel程序在Cell非常多的时候还会出问题
Caused by: java.lang.IllegalStateException: The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook
解决的办法就是，每一种cell样式存储起来，共用。
从中也可以看出来office Excel在实现样式的时候做了很多
限制。
上具体的代码
class="java">
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);
if(i == 1){
copyRows(wbFirst, wb, fromsheet, toSheet, fromsheet.getFirstRowNum(), fromsheet.getLastRowNum(), (toSheet.getLastRowNum()));
}else{
copyRows(wbFirst, wb, fromsheet, toSheet, fromsheet.getFirstRowNum(), fromsheet.getLastRowNum(), (toSheet.getLastRowNum() + 1));
}
}
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;
}
}
List<HSSFCellStyle> cellStyleList = new ArrayList<HSSFCellStyle>();
// 拷贝行并填充数据
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 = compareStoreAndGetCellStyle(cellStyleList, sourceCell.getCellStyle(), destWorkBook, sourceWorkBook);
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;
default:
targetCell.setCellValue(sourceCell.getRichStringCellValue());
// System.out.println("--------TYPE_STRING:" + i + targetCell.getRichStringCellValue());
break;
}
}
}
}
/**
* 比较存储并获得cellstyle
* @param cellStyleList
* @param cellStyle
* @param sourceWorkBook
* @return
*/
private static HSSFCellStyle compareStoreAndGetCellStyle(
List<HSSFCellStyle> cellStyleList,
HSSFCellStyle sourceCellStyle,
HSSFWorkbook destWorkBook,
HSSFWorkbook sourceWorkBook) {
for (int index = 0; index < cellStyleList.size(); index++) {
HSSFCellStyle cellStyle = cellStyleList.get(index);
if(isEqual(cellStyle, sourceCellStyle, destWorkBook, sourceWorkBook)){
return cellStyle;
}
}
//拷贝新的样式到列表
HSSFCellStyle cStyle = destWorkBook.createCellStyle();
cStyle = copyCellStyle(cStyle, sourceCellStyle, sourceWorkBook);
cellStyleList.add(cStyle);
return cStyle;
}
/**
* 两个cellStyle是否相同
* @param cellStyle
* @param sourceCellStyle
* @param sourceWorkBook
* @param destWorkBook
* @return
*/
private static boolean isEqual(HSSFCellStyle cellStyle,
HSSFCellStyle sourceCellStyle, HSSFWorkbook destWorkBook, HSSFWorkbook sourceWorkBook) {
//判断换行样式是否一样
if(cellStyle.getWrapText() != sourceCellStyle.getWrapText()){
return false;
}
//对齐方式是否一样
if(cellStyle.getAlignment() != sourceCellStyle.getAlignment()){
return false;
}
if(cellStyle.getVerticalAlignment()!= sourceCellStyle.getVerticalAlignment()){
return false;
}
//边框是否一样
if(cellStyle.getBorderBottom()!= sourceCellStyle.getBorderBottom()){
return false;
}
if(cellStyle.getBorderLeft()!= sourceCellStyle.getBorderLeft()){
return false;
}
if(cellStyle.getBorderRight()!= sourceCellStyle.getBorderRight()){
return false;
}
if(cellStyle.getBorderTop()!= sourceCellStyle.getBorderTop()){
return false;
}
if(cellStyle.getBottomBorderColor()!= sourceCellStyle.getBottomBorderColor()){
return false;
}
if(cellStyle.getLeftBorderColor()!= sourceCellStyle.getLeftBorderColor()){
return false;
}
if(cellStyle.getRightBorderColor()!= sourceCellStyle.getRightBorderColor()){
return false;
}
if(cellStyle.getTopBorderColor()!= sourceCellStyle.getTopBorderColor()){
return false;
}
//字体是否一样
HSSFFont sourceFont = sourceCellStyle.getFont(sourceWorkBook);
HSSFFont destFont = cellStyle.getFont(destWorkBook);
if(destFont.getBoldweight() != sourceFont.getBoldweight()){
return false;
}
if(destFont.getCharSet() != sourceFont.getCharSet()){
return false;
}
if(destFont.getColor() != sourceFont.getColor()){
return false;
}
if(destFont.getColor() != sourceFont.getColor()){
return false;
}
if(destFont.getFontHeight() != sourceFont.getFontHeight()){
return false;
}
if(destFont.getFontHeightInPoints() != sourceFont.getFontHeightInPoints()){
return false;
}
if(destFont.getIndex() != sourceFont.getIndex()){
return false;
}
if(destFont.getItalic() != sourceFont.getItalic()){
return false;
}
if(destFont.getUnderline() != sourceFont.getUnderline()){
return false;
}
if(destFont.getStrikeout() != sourceFont.getStrikeout()){
return false;
}
if(!destFont.getFontName().equals(sourceFont.getFontName())){
return false;
}
//别的样式是否一样
return true;
}
/**
* 样式拷贝
* @param cStyle 目标style
* @param sourceCellStyle 源style
* @param sourceWorkBook 源workBook
* @return
*/
private static HSSFCellStyle copyCellStyle(HSSFCellStyle cStyle, HSSFCellStyle sourceCellStyle, HSSFWorkbook sourceWorkBook) {
if(sourceCellStyle == null || cStyle == null){
return cStyle;
}
//是否换行
cStyle.setWrapText(sourceCellStyle.getWrapText());
//字体拷贝
cStyle.setFont(sourceCellStyle.getFont(sourceWorkBook));
// cStyle.cloneStyleFrom(sourceCellStyle);
//拷贝对齐方式
cStyle.setAlignment(sourceCellStyle.getAlignment());
cStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment());
//边框拷贝
cStyle.setBorderBottom(sourceCellStyle.getBorderBottom());
cStyle.setBorderLeft(sourceCellStyle.getBorderLeft());
cStyle.setBorderRight(sourceCellStyle.getBorderRight());
cStyle.setBorderTop(sourceCellStyle.getBorderTop());
cStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor());
cStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor());
cStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor());
cStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor());
//别的样式的拷贝
return cStyle;
}
/**
* 处理公式
* @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();
}
相关文章
