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

Question

I have merged two excel files using the code specied here

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

this the block applying the styles for my merging cells

 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);
  }
}

It all working as expected and going well in generating my XSSFWorkbook.

Problem starting when I try to open it:

I see below error

and my error report contains 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 these my sheet opens up fine but without styles. I know there is a limitation on number of styles to be created and have counted the styles being created and I hardly see 4 created. I even know that this issue is with too many styles.

Unfortunately, POI has support to optimise only HSSFWorkbook (Apache POI delete CellStyle from workbook)

Any help in how to mitigate with this issue will be great.

Solution 1

Well, after debugging bit of POI code and how styles are being applied and so.

Doing below solved the problem

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

Solution 2

I had the same issue. You should to minimize instances of styles and fonts because each instance is placed into xl/styles.xml

Make styles and fonts only once for one book.

Solution 3

I had the same issue using the Python library xlxswriter with Pandas. After I stopped trying to use Pandas' date_format 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()