Bug 47839

Summary: can't open XLSX after updaten the custom properties
Product: POI Reporter: waldek
Component: POI OverallAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: 3.5-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   

Description waldek 2009-09-14 13:05:48 UTC

i tried to update custom properties of a remote xlsx document, which is located in a local network, via outputstream.

after writing the file, i can't open it, because some parts seems to be damaged.

here is the code:

		OPCPackage opcpackage = OPCPackage.open(file.getAbsolutePath());

		POIXMLProperties props = new POIXMLProperties(opcpackage);
		org.openxmlformats.schemas.officeDocument.x2006.customProperties.CTProperty property = props.getCustomProperties().getUnderlyingProperties()

		// props.commit();
		XSSFWorkbook workbook = new XSSFWorkbook(opcpackage);
		FileOutputStream out = new FileOutputStream(file);

Comment 1 Yegor Kozlov 2009-10-04 03:03:55 UTC
POI provides low-level access to the underlying xml beans but in this case the caller is responsible for setting correct XML and supplying all needed attributes. In your case two required properties of the CTProperty bean were not set, see the OOXML markup Language Reference, section
<complexType name="CT_Property">
 <attribute name="fmtid" use="required" type="vt:ST_Clsid"/>
 <attribute name="pid" use="required" type="xsd:int"/>

fmtid is a Global Unique Identifier (GUID) in the form of {HHHHHHHHHHHH-HHHH-HHHH-HHHHHHHH} where each H is a hexidecimal.
pid is a sequential integer. 

The spec does not impose any additional requirements on fmtid and pid. In my first fix I tried to generate GUID using java.util.UUID and set pid using a sequence starting with 1. Unfortunately it didn't work.  

Searching MSDN I found more requirements that are not in the official ECMA spec:
 - Each property element contains an fmtid attribute with the same GUID value ({D5CDD505-2E9C-101B-9397-08002B2CF9AE}). 
Properties with other values are ignored and not visible to Excel. 
 - pid always starts with 2. The value of 1 results in corrupted workbook. 

See http://msdn.microsoft.com/en-us/magazine/cc337894.aspx

So, in your case the code should be as follows:


Current API for OOXML custom properties is pretty much low-level and inappropriate usage can result in unreadable workbook. I simplified it and added four addProperty methods that can set string, float, integer and boolean custom properties. Fmtid and pid are set behind the scene - user does not have to carry about such low-level things.

user code may look something like this:

        POIXMLProperties props = workbook.getProperties();

        POIXMLProperties.CustomProperties cust =  props.getCustomProperties();
        cust.addProperty("Author", "John Smith");
        cust.addProperty("Year", 2009);
        cust.addProperty("Price", 45.50);
        cust.addProperty("Available", true);
The fix was committed in r821496