Bug 47839 - can't open XLSX after updaten the custom properties
Summary: can't open XLSX after updaten the custom properties
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: 3.5-dev
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-09-14 13:05 UTC by waldek
Modified: 2009-10-04 03:03 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description waldek 2009-09-14 13:05:48 UTC
hello

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()
				.addNewProperty();
		property.setName("FileID");
		property.setI4(123);

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

thanks
waldek
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 7.3.2.2:
<complexType name="CT_Property">
...
 <attribute name="fmtid" use="required" type="vt:ST_Clsid"/>
 <attribute name="pid" use="required" type="xsd:int"/>
</complexType>

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:

        property.setName("FileID");
        property.setI4(123);
        property.setFmtid("{D5CDD505-2E9C-101B-9397-08002B2CF9AE}");
        property.setPid(2);


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

Regards,
Yegor