POI Excel Merging Causeing "Repaired Records: Format from /xl/styles.xml part (Styles)"

Question

I combined the two excel files using the code shown here

http://www.coderanch.com/t/614715/Web-Services/java/merge-excel-files

this block applying styles to my cell merges

if (styleMap != null) { if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) { newCell.setCellStyle(oldCell.getCellStyle()); } else { int stHashCode = oldCell.getCellStyle().hashCode(); XSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); } newCell.setCellStyle(newCellStyle); } } 

Everything works as expected and works well in creating my XSSFWorkbook.

Problem trying to open it:

Below I see an error

and my error report is below

  <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <logFileName>error072840_01.xml</logFileName> <summary>Errors were detected in file 'XYZ.xlsx'</summary> <repairedRecords summary="Following is a list of repairs:"> <repairedRecord>Repaired Records: Format from /xl/styles.xml part (Styles)</repairedRecord> </repairedRecords> </recoveryLog> 

After all this, my sheet opens perfectly, but without styles. I know that there is a limit on the number of styles that need to be created, and the created styles are counted, and I hardly see 4 created ones. I even know that this problem is associated with too many styles.

Unfortunately, the POI only supports HSSFWorkbook optimization ( Apache POI removes CellStyle from the book )

Any help on how to mitigate this problem would be great.

Answer 1

Well, after debugging the bit of the POI code and the way styles are applied.

Doing below solves the problem

 newCellStyle.getCoreXf().unsetBorderId(); newCellStyle.getCoreXf().unsetFillId(); 

Answer 2

I had the same problem. You should minimize instances of styles and fonts, because each instance is placed in xl/styles.xml

Create styles and fonts only once for one book.

Answer 3

I had the same problem using the xlxswriter Python library with Pandas. After I stopped trying to use the date_format Pandas specification, I stopped getting the error.

 import pandas as pd data = pd.read_excel('somefile.xlsx') grp = data.groupby('Property Manager') for i, (pm, g) in enumerate(grp): writer = pd.ExcelWriter(p + f.format(pm[:30]), engine='xlsxwriter') #,date_format='%m/%d/%Y') g[cols].to_excel(writer, sheet_name='Summary', index=False) writer.save()