POI按照源单元格设置目标单元格格式

原文：http://jjw198874.blog.163.com/blog/static/1889845522011102401854234/

POI按照源单元格设置目标单元格格式

poi按照一个源单元格设置目标单元格格式，如果两个单元格不在同一个workbook，

要用

HSSFCellStyle下的cloneStyleFrom()方法，

而不能用

HSSFCell下的setCellStyle()方法。

 

public void copyHssfRow(HSSFRow destRow, HSSFRow sourceRow){

int currentColumnNum = 0;

HSSFCell tempCell = null;

HSSFCellStyle tempCellStyle = null;

System.out.println("ExcelHandler.java copy specific excel row starts------");

for(Iterator i = sourceRow.cellIterator(); i.hasNext();){

tempCell = (HSSFCell)i.next();

//确保这个destRow的待赋值cell有值

if(destRow.getCell(currentColumnNum) == null){

destRow.createCell(currentColumnNum);

}

destRow.getCell(currentColumnNum).setCellValue(tempCell.getStringCellValue());

//红色字体的目的是把目的单元格的格式设置为源单元格的格式，这是正确的方式

tempCellStyle = tempCell.getCellStyle();

destRow.getCell(currentColumnNum).getCellStyle().cloneStyleFrom(tempCellStyle);

System.out.println("DestCell name: " + destRow.getCell(currentColumnNum).getStringCellValue()

+ " SourceCell name: " + tempCell.getStringCellValue());

currentColumnNum++;

}

System.out.println("ExcelHandler.java copy specific excel row ends------");

}

 

最初红色的代码是这样的：

tempCellStyle = tempCell.getCellStyle();

destRow.getCell(currentColumnNum).setCellStyle(tempCellStyle);

然后出现了报错：

java.lang.IllegalArgumentException: This Style does not belong to the supplied Workbook.

 

再去google搜索这个报错，发现poi手册中HSSFCellStyle有一个方法会抛出这个异常。

verifyBelongsToWorkbook

public void verifyBelongsToWorkbook(HSSFWorkbook wb)

Verifies that this style belongs to the supplied Workbook. Will throw an exception if it belongs to a different one. This is normally called when trying to assign a style to a cell, to ensure the cell and the style are from the same workbook (if they're not, it won't work)

Throws:

java.lang.IllegalArgumentException- if there's a workbook mis-match

再往下一看，就柳暗花明了。poi要求一个单元格的格式和单元格本身都在一个worksheet，不同的worksheet间不能共用HSSFCellStyle。想了下，这样是对的，这样就可以保证同一个style不会同时属于两个不同sheet的不同单元格，如果对一个单元格做了修改，另一个也会受到牵连。单元格之间的格式相当一个格式集，格式集中的参数数值我们可以一样，但是我们的格式集不能是同一个。

cloneStyleFrom

public void cloneStyleFrom(CellStyle source)Clones all the style information from another HSSFCellStyle, onto this one. This HSSFCellStyle will then have all the same properties as the source, but the two may be edited independently. Any stylings on this HSSFCellStyle will be lost! The source HSSFCellStyle could be from another HSSFWorkbook if you like. This allows you to copy styles from one HSSFWorkbook to another.

Specified by:

cloneStyleFrom
CellStyle