Bug 60184 - Saved file that uses a XSSFFont without a specifically set FontFamily won't open in Excel
Summary: Saved file that uses a XSSFFont without a specifically set FontFamily won't o...
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.15-FINAL
Hardware: PC All
: P2 blocker with 4 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2016-09-28 15:27 UTC by anna
Modified: 2017-05-05 21:52 UTC (History)
1 user (show)

unit test (2.20 KB, patch)
2016-09-29 06:44 UTC, Javen O'Neal
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description anna 2016-09-28 15:27:54 UTC
Steps to reproduce:
* open a spreadsheet created with Excel.
* create a new cell style with workbook.createCellStyle()
* create a new XSSFFont with workbook.createFont()
* add the font to the new style
* add the style to one cell
* save the sheet to a new file
* try to open the created file in Excel

If you add FontFamily to the new font, the resulting file opens without issues. Otherwise it complains about corrupted style.

The issue is apparently also present in some sheets with pre-existing styling that are simply loaded to POI and then saved into another sheet. See https://dev.vaadin.com/ticket/20180 for example files and more details.
Comment 1 Javen O'Neal 2016-09-29 06:44:29 UTC
Created attachment 34312 [details]
unit test

I am unable to reproduce this on the trunk (currently a few commits after 3.15) using LibreOffice I can check with Microsoft Excel later.

Is this behavior a regression in 3.15?
Can you verify that my unit test is the same as yours (if not, could you submit your unit test)?
Comment 2 anna 2016-09-29 06:47:09 UTC
LibreOffice doesn't have the problem, Excel does. Also reproducible in Excel Viewer.
Comment 3 anna 2016-09-29 13:55:51 UTC
Does the status change mean that you couldn't reproduce the issue with Excel Viewer?
Comment 4 anna 2016-09-29 14:48:25 UTC
Behaviour was already present in 3.12, not sure about older ones. Don't have a unit test, tested manually in existing project. I tested the files attached to the linked ticket on Excel 2013 and Excel Viewer, with Libre Office they worked fine.
Comment 5 anna 2016-10-03 11:05:17 UTC
Not-so-nice workaround:

Copy over StylesTable.java to local projet, change writeTo to contain

        for (XSSFFont f : fonts) {
            if (FontFamily.NOT_APPLICABLE.getValue() == f.getFamily()) {
            ctfnt[idx++] = f.getCTFont();

instead of

        for(XSSFFont f : fonts) ctfnt[idx++] = f.getCTFont();

and do that again every time you update the version.
Comment 6 Marek Branicky 2016-12-09 08:47:54 UTC
Guys is this fixed in version POI 3.15 or do you plan to fix it in near future?
Comment 7 ps26oct 2017-01-21 10:34:31 UTC
I obtained poi src from git read only repo and built it accordingly. Using MS Excel 2016 (with a MS office home subscription, which expired recently but doesn't restrict much of the functionality of excel 2016 that I needed for trying to reproduce the error), I was unable to reproduce the issue. Here's the code which I used:

import java.io.*;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class bz60184 {

	public static void main(String[] args) throws IOException, InvalidFormatException {

		File f = new File("file.xlsx"); //already created with excel in the user.dir
		XSSFWorkbook wb = new XSSFWorkbook(f);
		XSSFCellStyle styl  = wb.createCellStyle();
		XSSFFont font = wb.createFont();
		File f2 = new File("save.xlsx");
		FileOutputStream os = new FileOutputStream(f2);


//OS - WIN 10 ; JDK - jdk_8u102
Comment 8 Greg Woolsey 2017-05-05 21:45:03 UTC
I just now found this issue, wish I'd seen it earlier.  I see the problem now.

XSSFFont can be created without a family.  This is what happens when calling XSSFWorkbook.createFont().  This is fine per the OOXML XSD.

However, XSSFFont.getFamily() creates an empty family element if none exist.  This is invalid, as the family element is required to have a val attribute.

Further, it's really bad practice for things that are defined as property getters to have side effects like this.

Since the API's been out there a while, we should maintain backward compatibility, but I don't like it too much.

I think in the missing element case it should just return 0, the index for NOT_APPLICABLE, which appears to coincide with "auto" in the spec, but NOT create a family element.

Better would be a method that returns an instance of the enum or null if not defined, or NOT_APPLICABLE if null is undesirable.  For now though, I'll just update to not create the empty element if missing.
Comment 9 Greg Woolsey 2017-05-05 21:52:32 UTC
Fixed in 3.17, r1794111.  No longer creating an empty family entry as a side effect of calling the getter.