mr.nothing mr.nothing - 1 year ago 292
Java Question

apache poi apply one style to different workbooks

I'm trying to apply one cell style to defferent woekbooks. It works well, when I apply it to first workbook, but when I'm trying to do this with second and next workbooks - no style is applied and the following exception is thrown.

Exception in thread "Thread-3" java.lang.IllegalArgumentException: This Style does not belong to the supplied Workbook Stlyes Source. Are you trying to assign a style from one workbook to the cell of a differnt workbook?
at org.apache.poi.xssf.usermodel.XSSFCellStyle.verifyBelongsToStylesSource(
at org.apache.poi.xssf.usermodel.XSSFCell.setCellStyle(
at CoreLayer.ExportManager.ExcelExproter.applyStyle(
at CoreLayer.ExportManager.ExcelExproter.groupSchedule(
at UILayer.ExportDialog$

The following code is used:

public void professorSchedule(Professor professor) {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet(TextConstants.SCHEDULE);
String safeName = WorkbookUtil.createSafeSheetName(professor.toString() + ".xlsx");

LinkedHashMap<ScheduleSlot, Lesson> professorSchedule = data.getSchedule().getProfessorSchedule(professor);
fillProfessorSchedule(sheet, professorSchedule);

applyStyle(wb, sheet);
try {
FileOutputStream fileOutputStream = new FileOutputStream(settings.getSchedulesPath() + safeName);
} catch (IOException e) {

private void applyStyle(Workbook wb, Sheet sheet) {
CellStyle style = wb.createCellStyle();

int columnNumber = 0;
for (Row row : rows) {
for (Cell cell : row) {
sheet.setColumnWidth(columnNumber++, 5000);

Thanks everybody in advance!

Answer Source

You can't do that, CellStyle objects are specific to one workbook. They're quite deep objects, and much of the style is held in the workbook, so you can't just re-use. You even get a helpful exception which explains this to you!

What you need to do instead is to use the cloneStyleFrom(CellStyle) method, to copy the details of the style over. Something like:

Workbook wb = WorkbookFactory.create(new File("existing.xls"));
CellStyle origStyle = wb.getCellStyleAt(1); // Or from a cell

Workbook newWB = new XSSFWorkbook();
Sheet sheet = newWB.createSheet();
Row r1 = sheet.createRow(0);
Cell c1 = r1.createCell(0);

CellStyle newStyle = newWB.createCellStyle();

newWB.write(new FileOutpuStream("new.xlsx"));
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download