Help on POI for Excel Import/Export Feature

["Somasundaran, Rekha" <RS...@thegoldensource.com>]

Hi,

I need help in creating the custom xml mappings on an excel workbook. This is the code I managed to start with but I am stuck at the place where the mapping needs to be placed on the sheet. Can you please share an example where this is done


            XSSFWorkbook wb = new XSSFWorkbook();

            POIXMLDocumentPart pd = wb.createRelationship(XSSFRelation.CUSTOM_XML_MAPPINGS, XSSFFactory.getInstance());
            String exampleString = "<xml-fragment SelectionNamespaces=\"\" xmlns:main=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><main:MapInfo><main:Schema ID=\"Schema1\"><xsd:schema xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"><xsd:element nillable=\"true\" name=\"root\"><xsd:complexType><xsd:sequence minOccurs=\"0\"><xsd:element minOccurs=\"0\" maxOccurs=\"unbounded\" nillable=\"true\" name=\"GoldenSourceObjectMessage\" form=\"unqualified\"><xsd:complexType><xsd:sequence minOccurs=\"0\"><xsd:element minOccurs=\"0\" nillable=\"true\" name=\"RatingsValue\" form=\"unqualified\"><xsd:complexType><xsd:sequence minOccurs=\"0\"><xsd:element minOccurs=\"0\" nillable=\"true\" name=\"GSOHeader\" form=\"unqualified\"><xsd:complexType><xsd:sequence minOccurs=\"0\"><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"DateFormat\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"User\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"VendorSource\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"Language\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"DefaultTimestamp\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"TimeZone\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"MessageClassification\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"MainEntityId\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"MainEntityName\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"MainEntityType\" form=\"unqualified\"/></xsd:sequence></xsd:complexType></xsd:element><xsd:element minOccurs=\"0\" nillable=\"true\" name=\"RatingValueToRatingCore.RatingSetInformation\" form=\"unqualified\"><xsd:complexType><xsd:sequence minOccurs=\"0\"><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingSetMnemonic\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingSetDescriptions\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingSetApplicablity\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingSetTenor\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingSetCurrency\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingSetType\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingSetName\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingSetStartDateTime\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingSetEndDateTime\" form=\"unqualified\"/></xsd:sequence></xsd:complexType></xsd:element><xsd:element minOccurs=\"0\" nillable=\"true\" name=\"RatingInformation\" form=\"unqualified\"><xsd:complexType><xsd:sequence minOccurs=\"0\"><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RankNumber\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingDescription\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingCodeNumber\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"LongOrShortTermType\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingName\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingValue\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingValueStartDateTime\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingValueIdentifier\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"ExternalRatingValue\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingInfoStartTime\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingInfoEndTime\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"ExternalRatingValueName\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"ExternalRatingValueDescription\" form=\"unqualified\"/><xsd:element minOccurs=\"0\" nillable=\"true\" type=\"xsd:string\" name=\"RatingValueEndDateTime\" form=\"unqualified\"/></xsd:sequence></xsd:complexType></xsd:element></xsd:sequence></xsd:complexType></xsd:element></xsd:sequence></xsd:complexType></xsd:element></xsd:sequence></xsd:complexType></xsd:element></xsd:schema></main:Schema><main:Map ID=\"1\" Name=\"root_Map\" RootElement=\"root\" SchemaID=\"Schema1\" ShowImportExportValidationErrors=\"false\" AutoFit=\"true\" Append=\"false\" PreserveSortAFLayout=\"true\" PreserveFormat=\"true\"/></main:MapInfo></xml-fragment>";
            InputStream stream = new ByteArrayInputStream(exampleString.getBytes(StandardCharsets.UTF_8));
            ((MapInfo)pd).readFrom(stream);
            FileOutputStream outputStream = new FileOutputStream("C:\\JavaBooks.xlsx");
                wb.write(outputStream);
                outputStream.close();


Thanks and Regards,
Rekha Somasundaran, Consultant | GoldenSource Corporation | Plot 13-A, Paper Box House, Off Mahakali Caves Road, Andheri (E), Mumbai - 400 093, India | T: +91 (22) 6645 4000 | D: +91 (22) 6645 4297 | M: +91 9819975596
rsomasundaran@thegoldensource.com<ma...@thegoldensource.com>

[Description: cid:image002.png@01CEC9D8.008B4850]<http://thegoldensource.com/>

www.thegoldensource.com<http://www.thegoldensource.com/> | Follow us on LinkedIn<http://www.linkedin.com/groups?gid=1773709> and Twitter<http://twitter.com/GoldenSource> |

This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any erroneous transmission. If you receive this message in error, please immediately destroy it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, or copy any part of this message if you are not the intended recipient.