Java POI Excel sheet 合并遇到的问题解决2

51 阅读 0 评论 34 点赞

我是靠谱客的博主优雅西装，最近开发中收集的这篇文章主要介绍Java POI Excel sheet 合并遇到的问题解决2，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

上接 Java POI Excel sheet 合并
[url]http://zhuyufufu.iteye.com/blog/2033386[/url]
Java POI Excel sheet 合并问题解决
[url]http://zhuyufufu.iteye.com/blog/2035033[/url]

上面的博文生成的拷贝Excel程序在Cell非常多的时候还会出问题

[b]Caused by: java.lang.IllegalStateException: The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook[/b]

解决的办法就是，每一种cell样式存储起来，共用。

从中也可以看出来office Excel在实现样式的时候做了很多限制。

上具体的代码


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();
	}