14 Replies Latest reply on Feb 7, 2019 12:05 AM by Margot Emmers

    export to XML, postprocessing to contain this xml into XLSX structure

    Margot Emmers Active Member

      Hello,

       

      I need to export xml files to load in an XLSX file (with post-processing) while XSLX is able to manage more rows then a XLS document.

      https://www.google.be/search?ei=R3xIXPS9I4ikwAKrzZ6IBQ&q=maximum+of+columns++xls+and+xlsx&oq=maximum+of+columns++xls+and… 

       

      I've prepared a XLSX document (header + 6 rows of content)

      Changed the document  extension from XLSX to zip.

      Now I'm trying to use the XML contained in the .zip file as an example to write exports.

      Which XML files do I need to generate and have to be replaced?

       

      Following XML files are contained in the generated XLSX converted to .zip

      1. zip > xl > sharedStrings.xml

         contains 'count="106" uniqueCount="72" ' : How to fill this information Or is it just optional information and may It be left out?

      2. zip > xl > worksheets > sheet1.xml

      3. zip > xl > Styles.xml?

      4. zip > xl > workbook.xml?

      5. zip > xl > theme > them1.xml

      6. zip > [Content_Types].xml

       

      Would you be able to send me a small example to startoff and some documentation?

       

      KInd regards,

      Margot

       

        • 1. Re: export to XML, postprocessing to contain this xml into XLSX structure
          Jonathon Bowring New Member

          Hi Margot,

           

          Yes, you can definitely achieve this and I have done something similar.

           

          The solution that I came up with was to have Product 360 export a "Sheet1.xml" file and then use 7-Zip to place this file inside an existing XLSX file.

           

          The main benefit is it reduced the complexity of producing all of the various component files of an XLSX file, and it also made it easy to create various XLSX templates (different styles sheets etc) and then have Product 360 to simply insert the data on one sheet.

           

          I have also added some post processing that ensures that only the resulting XLSX file is presented to the users for download (i.e. they are not presented multiple XML files and an XLSX file when they try to downloaded the result).

           

          The way I achieved this was Product 360 exports the Sheet1 XML data into a file named "Result.xlsx" (the same name as the container XLSX file) and then after the XML data is placed inside the XLSX container the container is renamed and the original XML file is deleted.

           

          The high level steps are:

           

          1) Export the Sheet1 data into an XML file named "Result.xlsx".

          2) Post export copy the container XLSX file into the export directory.

          3) Post export rename the Sheet1 XML file to "Sheet1.xml" and then copy it inside the container XLSX file.

          4) Delete the original Sheet1 XML data file from the export directory.

          5) Rename the container XLSX file to "Result.xlsx".

           

          Please find attached my example which includes all of the files and instructions that you would need to install it on your system.

           

          If you have any questions then please let me know.

           

          Cheers

          Jon

          3 of 3 people found this helpful
          • 2. Re: export to XML, postprocessing to contain this xml into XLSX structure
            Daniel Walter Guru

            This is really cool stuff, thanks so much for sharing Jonathon!

            Cheers Daniel

            • 3. Re: export to XML, postprocessing to contain this xml into XLSX structure
              Margot Emmers Active Member

              Hello Jonathan,

               

              Thanks for the work you put into this answer.

              I'm going to give it a try :-)

               

              Kin dregards,

              Margot

              • 4. Re: export to XML, postprocessing to contain this xml into XLSX structure
                Margot Emmers Active Member

                Hello Jonathan,

                 

                Maybe you can help me out with one issue.

                I use the code below which gives me the correct output.

                Problem is that it results in empty rows in my excel file for items that don't answer the criteria.

                Do you know how I can avoid these?

                I tried to set the NumberIncrement...  below the criteria before <row> but same result.

                 

                {?NumberIncrement "row", 1 }{!

                }{?IfNotEmptyThenNotEnc {&Item.ERP: Item Number (varSalesOrg)}, {!

                }{?IfNotEmptyThenNotEnc {?Compare {?Right {&Item.Structure group(s) (WebShop ERIKS).Structure group identifier}, "2"}, "99", "", "1"}, {!

                }{?Compare {&Item.ERP: Publication / Export Status (varSalesOrg)}, "Approved public cat B2B and B2C", {!

                }{?Concat {!

                }<row r="{?NumberGet "row" }" spans="1:2" x14ac:dyDescent="0.25">

                     <c r="A{?NumberGet "row" }" t="inlineStr"><is><t>{%varSalesOrg}</t></is></c>

                     <c r="B{?NumberGet "row" }" t="inlineStr"><is><t>{%varLanguage}</t></is></c>

                     <c r="C{?NumberGet "row" }" t="inlineStr"><is><t>{%varCurrency}</t></is></c>

                     <c r="D{?NumberGet "row" }" t="inlineStr"><is><t>{&Item.ERP: Item Number (varSalesOrg)}</t></is></c>

                     <c r="E{?NumberGet "row" }" t="inlineStr"><is><t>{?Replace {&Item.Short description (varLanguage)}, ";", ","}</t></is></c>

                </row>}, ""}}}

                 

                Thanks in advance,

                Kind regards,

                Margot

                • 5. Re: export to XML, postprocessing to contain this xml into XLSX structure
                  Jonathon Bowring New Member

                  Hi Margot,

                   

                  Unfortunately the NumberIncrement function will still increment even if the IfNotEmptyThenNotEnc function evaluates as false.

                   

                  Looking at your field selectors it appears that you are returning data at the item entity level and not the sub entity level, is that correct?

                   

                  If it is correct then I would suggest creating a dynamic assortment with an item search that applies the same filter as your IfNotEmtpyThenNotEnc expressions. That should then prevent any blank records from being added into the file.

                   

                  Cheers

                  Jon

                  1 of 1 people found this helpful
                  • 6. Re: export to XML, postprocessing to contain this xml into XLSX structure
                    Margot Emmers Active Member

                    Good morning Jonathan,

                     

                    Yes this is data exporting on entity level.
                    I already had the feeling, I experienced this with other functions aswel.

                    Indeed the only workaround is to start from an assortment with all the filters contained.

                     

                    Thanks for sharing your knowledge, it's very much appreciated.

                     

                    Kind regards,

                    Margot

                    1 of 1 people found this helpful
                    • 7. Re: export to XML, postprocessing to contain this xml into XLSX structure
                      Daniel Walter Guru

                      An alternative approach might be to exclude items via the export validations if this is what you are looking for.

                      You can select a field, set a validation and set the action to "Remove data record".

                      Validations are dependent on the field type but could also include complex things like regular Expressions.

                       

                       

                      1 of 1 people found this helpful
                      • 8. Re: export to XML, postprocessing to contain this xml into XLSX structure
                        Margot Emmers Active Member

                        Hello Jonathan and Daniel,

                         

                        Both given solutions would work with item numbers of just one Sales Organization.

                        Since my assortment contains items of 6 Sales Organizations (ERP: Item number (varSalesOg)

                        - Adding the restriction in my assortment would end up of building  6 assortments (per SalesOrg), 6 modules to assign the assortment,....

                        - Adding the restriction to the Data validation rules would not work while you can't put restrictions there to result in nothing ("")

                         

                        But I found another solutions, adding the restrictions in the function 'NumberIncrement', this way It will create only as many rows as needed. The function creates as default 1 more row per exported line but you can change the default:

                        - if it does not answer the criteria add '0' lines

                        - if it does answer to the criteria add '1' line

                        {?NumberIncrement "row", {!

                        }{?Compare {&Item.ERP: Item Number (varSalesOrg)}, "", 0, {!

                        }{?Compare {?Right {&Item.Structure group(s) (WebShop ERIKS).Structure group identifier}, "2"}, "99", 0, {!

                        }{?Compare {&Item.ERP: Publication / Export Status (varSalesOrg)}, "Approved public cat B2B and B2C", 1, 0}}}}

                         

                        Thanks anyway for the great input, it helped me a lot and together we know more right!

                         

                        Kind regards,

                        Margot

                        • 9. Re: export to XML, postprocessing to contain this xml into XLSX structure
                          Jonathon Bowring New Member

                          Very interesting both! Thanks Margot and Daniel

                          • 10. Re: export to XML, postprocessing to contain this xml into XLSX structure
                            Margot Emmers Active Member

                            Hello again,

                             

                            I got it to work for an XSLX with only 1 sheet containing but does somebody has an example where two worksheets need to be filled so 1 XSLX with sheet 1 + sheet 2?

                             

                            Thanks in advance

                            Margot

                            • 11. Re: export to XML, postprocessing to contain this xml into XLSX structure
                              Jonathon Bowring New Member

                              Good morning Margot,

                               

                              In my original version of the Excel export I had it so that you could re-use the "SaveSheet.bat" command to add multiple sheets into an Excel file, however the downside was the user was presented with the resulting Excel file and all of the XML data sheets when downloading the results.

                               

                              Because of this I decided to scale it back to one sheet only, however the logic is essentially the same if you'd like to adjust the post processing script.

                               

                              If you happen to find a neat solution for adding multiple sheets then please let me know also!

                               

                              Cheers

                              Jon

                              • 12. Re: export to XML, postprocessing to contain this xml into XLSX structure
                                Margot Emmers Active Member

                                Good morning Jon,

                                 

                                Thanks for your feedback and I will keep you posted if I will come up with a solution.
                                What I really need is to export multiple xlsx files from one export template. Therefore I need to use file management and can't define a default filename.xlsx.

                                (I have been searching if this could be add somewhere in the plugin_customization.ini but didn't find it yet.)

                                 

                                Making used of File Management \ Extended options result in a file with a different extension (csv, txt, xml,...). So my idea is to change the extension of the file to XLSX in the post-processing and add different templates in the template folder?

                                If you have other ideas please?

                                 

                                Kind regards

                                Margot

                                • 13. Re: export to XML, postprocessing to contain this xml into XLSX structure
                                  Jonathon Bowring New Member

                                  Hi Margot,

                                   

                                  Sure thing. You should still be able to achieve this. I haven't yet done it myself however the theory would be to do something like the following:

                                   

                                  1. Create two container excel files with the desired filenames of the resulting Excel files (in my example I will use "customers.xlsx" and "products.xlsx").
                                  2. Ensure that the property "com.heiler.ppm.texttemplate.core/additional_file_extensions" has "xlsx" included as a value within your plugin_customization.ini file.
                                  3. Create your PIM export and setup two output files "customers.xlsx" and "products.xlsx" on the "File Management" tab (screenshot attached).
                                  4. Add your XML modules for your first file and set the target file to "customers.xlsx".
                                  5. Add your XML modules for your second file and set the target file to "products.xlsx".
                                  6. Add two execute "SaveWorksheet.bat" commands to your post processing steps.
                                  7. For the first command set it to copy the "customers.xlsx" XML file into the "customers.xlsx" container spreadsheet.
                                  8. For the first command set it to copy the "products.xlsx" XML file into the "products.xlsx" container spreadsheet.

                                   

                                  I hope that the above makes sense. If you face any issues then please let me know.

                                   

                                  Thanks

                                  Jon