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); } }
Created attachment 23969 [details] New files (implementation of the exporter and the Custom XML mapping part)
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
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
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).
Created attachment 23980 [details] Changes to XSSFRelation No changes from the previous, committed just to make sure that the patch is consistent
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
Yegor, thank for your work and your support, I'll checkout the last revision! Regards, Roberto
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
My bad. I was going to make MapInfo Iterable but reverted this change at the last moment. Fixed in r794563 Yegor
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
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
Roberto, Where can I find CustomXMLMappings.xlsx I'm trying to run through some of the examples myself. Thank you, Andrew