CODEWITHSUNDEEP
javaexcelapache-poi
abinmorth
abinmorth

Reputation: 507

Copying Styles from one Excel Workbook to another

I want to copy one sheet of a workbook (including the styles) to a new workbook.

I tried iteration over all Cells and

CellStyle newCellStyle = workbook.createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
newCell.setCellStyle(newCellStyle);

throws java.lang.IllegalStateException: The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook

CellStyle newCellStyle = oldCell.getCellStyle();
newCell.setCellStyle(newCellStyle);

throws java.lang.IllegalArgumentException: This Style does not belong to the supplied Workbook. Are you trying to assign a style from one workbook to the cell of a differnt workbook?

What is the correct way to copy the styles?

Upvotes: 1

Views: 4759

Answers (3)

Alok verma
Alok verma

Reputation: 1

Use below code out of iteration:

CellStyle newCellStyle = workbook.createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());

and this within iteration which you are using to create new cells:

newCell.setCellStyle(newCellStyle);

will solve this.

Upvotes: 0

abinmorth
abinmorth

Reputation: 507

solved it with a hashmap containing the styles

HashMap<Integer, CellStyle> styleMap = new HashMap<Integer, CellStyle>();
public void copyCell(Cell oldCell, Cell newCell){
       int styleHashCode = oldCell.getCellStyle().hashCode();
           CellStyle newCellStyle = styleMap.get(styleHashCode);
           if(newCellStyle == null){
               newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
               newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
               styleMap.put(styleHashCode, newCellStyle);
           }
           newCell.setCellStyle(newCellStyle);
}

Upvotes: 2

teylyn
teylyn

Reputation: 35955

Styles in Excel are a curly mess. If you want to copy a sheet and all its styles between workbooks, using Excel desktop, it is normally sufficient to just copy the sheet between the workbooks. The styles will come as baggage automatically.

But styles copied this way can also easily get corrupted and cause a lot of problems. Your message of exceeding the limit points to style corruption, because no healthy workbook would have more than 4000 styles.

Upvotes: 0

Related Questions