Copy a XSSF/HSSF-Cells into a new XSSFWorkbook

Question

My problem

I need to exactly copy cells from XSSFWorkbooks and HSSFWorkbooks to a new XSSFWorkbook. So my cells can be of both types: XSSFCell and HSSFCell.

By exactly, I mean that I also need to copy the CellStyle including the CellBorder and CellFill properties as well as the DefaultRowHeight and DefaultColumnWidth of the workbook itself. Also the height and width for each row and column should be copied. (Copying CellStyle sometimes results in strange behaviour like I already asked here).

My question

What's the best way to do this? I don't want to copy each property manually by myself. Especially if I don't know if my input cells are of type XSSFCell or HSSFCell.

Answer 1

My solution

I've solved my problem through downscaling of the requirements. Now I only concentrate on XSSFWorkbooks.

Exactly copying a XSSFWorkbook is really easy. To copy a XSSFCellStyle to another workbook just use the following code:

// Copy cell style from `sourceCell` to `targetCell`
XSSFCellStyle sourceCellStyle = sourceCell.getCellStyle();
XSSFCellStyle clonedCellStyle = newWorkbook.createCellStyle();
clonedCellStyle.cloneStyleFrom(sourceCellStyle);
targetCell.setCellStyle(clonedCellStyle);

It's important that the target workbook has the same style source as the source workbook. Otherwise the cloned cell style will look differently.

final StylesTable sourceStylesSource = sourceWorkbook.getStylesSource();
final StylesTable tagetStylesSource = targetWorkbook.getStylesSource();

sourceStylesSource.getFonts().forEach(font -> targetStylesSource.putFont(font, true));
sourceStylesSource.getFills().forEach(fill -> targetStylesSource.putFill(new XSSFCellFill(fill.getCTFill())));
sourceStylesSource.getBorders().forEach(border -> targetStylesSource.putBorder(new XSSFCellBorder(border.getCTBorder())));

I hope this helps someone!
Regards, winklerrr

---

PS

If someone can't downscale the requirements, then maybe it's helpful to split up the task into two smaller tasks:

1. Convert a HSSFWorkbook to a XSSFWorkbook and vice versa.
2. Copy a XSSFWorkbook exactly.