Bug 47520

Summary: [PATCH] Implementation of an XLSX to XML Exporter using Custom XML Mapping
Product: POI Reporter: Roberto Manicardi <roberto.manicardi>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.5-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Diff for file XSSFRelation
New files (implementation of the exporter and the Custom XML mapping part)
changed the implementation to use CellReference instead of XSSFCellReference
Changes to XSSFRelation

Description Roberto Manicardi 2009-07-13 08:32:57 UTC
Created attachment 23968 [details]
Diff for file XSSFRelation

In a XSLX you can set one or more mapping rule to define how to exchange data from and to an external XML. This part is called Custom XML Mapping (see http://openxmldeveloper.org/articles/2268.aspx for a pratical example)

This patch implements the Custom XML mapping part and an XML exporter that, using the mapping saved in an XLSX, exports the data from the worksheets to an XML stream.

The following example shows how to create an XML with the mapping identified by mapId and prints it to console:


 int mapId = // your map ID
 XSSFWorkbook wb = // load an XLSX file with mapping informations	  
 MapInfo mapInfo = null;
		  
 for(POIXMLDocumentPart p : wb.getRelations()){
     if(p instanceof MapInfo){
         mapInfo = (MapInfo) p;
	 XSSFMap map = mapInfo.getXSSFMapById(mapId);
	 XSSFExportToXml exporter = new XSSFExportToXml(map);
	 ByteArrayOutputStream os = new ByteArrayOutputStream();
         exporter.exportToXML(os,true);
	 String xml = os.toString("UTF-8");
         System.out.println(xml);
    }
 }
Comment 1 Roberto Manicardi 2009-07-13 08:33:57 UTC
Created attachment 23969 [details]
New files (implementation of the exporter and the Custom XML mapping part)
Comment 2 Yegor Kozlov 2009-07-13 11:11:39 UTC
Roberto,

Thanks for the patch. I gave a quick look and have the following comments: 

 - Did you make any changes to XSSFFactory?  I guess you did, otherwise workbook.getRelations()  would never return instances of MapInfo. Please re-attach the full diff. 
 - Please normalize your code to use org.apache.poi.ss.util.CellReference instead of XSSFCellReference. The latter class is redundant. 
 - Can you provide an example based on the posted code so that we include it in the XSSF examples? It would be very handy.

Regards,
Yegor
Comment 3 Roberto Manicardi 2009-07-14 06:22:51 UTC
Yegor

> - Did you make any changes to XSSFFactory?  I guess you did, otherwise
> workbook.getRelations()  would never return instances of MapInfo. Please
> re-attach the full diff. 

XSSFFactory delegates to XSSFRelation the goal of choosing the right relation class to instantiate. The new relation classes were added in the XSSFRelation class

> - Please normalize your code to use org.apache.poi.ss.util.CellReference
> instead of XSSFCellReference. The latter class is redundant. 

Mmm, I've reinvented the wheel! :)
I'll send a new version of the patch


>  - Can you provide an example based on the posted code so that we include it in
> the XSSF examples? It would be very handy.

I've added three XLSX examples in src/testcases/org/apache/poi/hssf/data directory (it's the one where XSSFTestDataSamples searches for test files)
- Custom XML complex type.xlsx
- CustomXmlMappings-inverse-order.xlsx
- CustomXMLMappings.xlsx

You can find also some examples in the test class org.apache.poi.xssf.extractor.TestXSSFExportToXML


Thank for your feedback,
Roberto
Comment 4 Roberto Manicardi 2009-07-14 08:50:18 UTC
Created attachment 23979 [details]
changed the implementation to use CellReference instead of XSSFCellReference

I've changed the implementation to use CellReference instead of XSSFCellReference and removed the class XSSFCellReference.

I've added some automated conditions to check the output in the test org.apache.poi.xssf.extractor.TestXSSFExportToXML.testMultiTable to verify the output.

I've also changed the test file CustomXML complex types.xlsx to CustomXMLMappings-complex-types.xlsx because I had problems in managing files with spaces in filename (expecially when executing the script to generate the patch).
Comment 5 Roberto Manicardi 2009-07-14 08:51:41 UTC
Created attachment 23980 [details]
Changes to XSSFRelation

No changes from the previous, committed just to make sure that the patch is consistent
Comment 6 Yegor Kozlov 2009-07-15 22:56:04 UTC
Roberto,

Thanks for the patch, I committed it with a few tweaks in r794539. 

 - The procedure of getting MapInfo can be simplified. I added XSSFWorkbook.getCustomXMLMappings() which returns a collections of XSSFMap objects. With this change your example looks much simpler:

  //export all xml mappings defined in this workbook
  XSSFWorkbook wb = ..;
  for (XSSFMap map : wb.getCustomXMLMappings()) {
    XSSFExportToXml exporter = new XSSFExportToXml(map);

  }

 - I moved XSSFXmlColumnPr and XSSFSingleXmlCell to org.apache.poi.xssf.usermodel.helpers, they are really helper wrappers around XML beans.

Other than that, it is a excellent contribution. 

Regards,
Yegor
Comment 7 Roberto Manicardi 2009-07-15 23:33:57 UTC
Yegor,

thank for your work and your support, I'll checkout the last revision!

Regards,
Roberto
Comment 8 Roberto Manicardi 2009-07-16 00:07:18 UTC
Yegor,

there is an error in the file src/ooxml/testcases/org/apache/poi/xssf/model/TestMapInfo.java


the following piece of code in method testMapInfoExists

for (XSSFMap map : mapInfo) {
                    Node xmlSchema = map.getSchema();
                    assertNotNull(xmlSchema);
}

should be substituted by

for(XSSFMap map: mapInfo.getAllXSSFMaps()){
                    Node xmlSchema = map.getSchema();
                    assertNotNull(xmlSchema);
}


(add .getAllXSSFMaps() to mapInfo)

otherwhile the code won't compile.

Regards,
Roberto
Comment 9 Yegor Kozlov 2009-07-16 00:27:59 UTC
My bad. I was going to make MapInfo Iterable but reverted this change at the last moment.

Fixed in r794563

Yegor
Comment 10 Maxim Valyanskiy 2009-07-16 01:27:21 UTC
File names 'CustomXmlMappings.xlsx' and '
CustomXMLMappings.xlsx' differs on Linux:

Testsuite: org.apache.poi.xssf.extractor.TestXSSFExportToXML
Tests run: 4, Failures: 0, Errors: 1, Time elapsed: 1.143 sec

Testcase: testExportToXML took 0.001 sec
        Caused an ERROR
Sample file 'CustomXmlMappings.xlsx' not found in data dir '/home/maxcom/svn-checkout/poi/src/testcases/org/apache/poi/hssf/data'
java.lang.RuntimeException: Sample file 'CustomXmlMappings.xlsx' not found in data dir '/home/maxcom/svn-checkout/poi/src/testcases/org/apache/poi/hssf/data'
        at org.apache.poi.hssf.HSSFTestDataSamples.openSampleFileStream(HSSFTestDataSamples.java:77)
        at org.apache.poi.xssf.XSSFTestDataSamples.openSampleWorkbook(XSSFTestDataSamples.java:42)
        at org.apache.poi.xssf.extractor.TestXSSFExportToXML.testExportToXML(TestXSSFExportToXML.java:46)

Testcase: testExportToXMLInverseOrder took 0.554 sec
Testcase: testXPathOrdering took 0.218 sec
Testcase: testMultiTable took 0.354 sec
Comment 11 Yegor Kozlov 2009-07-16 03:52:35 UTC
Fixed in r794621. 
There were two tests containing wrong file names: TestXSSFExportToXML and TestMapInfo. 

Regards,
Yegor

(In reply to comment #10)
> File names 'CustomXmlMappings.xlsx' and '
> CustomXMLMappings.xlsx' differs on Linux:
> 
> Testsuite: org.apache.poi.xssf.extractor.TestXSSFExportToXML
> Tests run: 4, Failures: 0, Errors: 1, Time elapsed: 1.143 sec
> 
> Testcase: testExportToXML took 0.001 sec
>         Caused an ERROR
> Sample file 'CustomXmlMappings.xlsx' not found in data dir
> '/home/maxcom/svn-checkout/poi/src/testcases/org/apache/poi/hssf/data'
> java.lang.RuntimeException: Sample file 'CustomXmlMappings.xlsx' not found in
> data dir '/home/maxcom/svn-checkout/poi/src/testcases/org/apache/poi/hssf/data'
>         at
> org.apache.poi.hssf.HSSFTestDataSamples.openSampleFileStream(HSSFTestDataSamples.java:77)
>         at
> org.apache.poi.xssf.XSSFTestDataSamples.openSampleWorkbook(XSSFTestDataSamples.java:42)
>         at
> org.apache.poi.xssf.extractor.TestXSSFExportToXML.testExportToXML(TestXSSFExportToXML.java:46)
> 
> Testcase: testExportToXMLInverseOrder took 0.554 sec
> Testcase: testXPathOrdering took 0.218 sec
> Testcase: testMultiTable took 0.354 sec
Comment 12 Andrew Rubalcaba 2010-01-20 12:15:48 UTC
Roberto,

Where can I find CustomXMLMappings.xlsx I'm trying to run through some of the examples myself.

Thank you,
Andrew