Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Feasibility of XML import with complex data for Excel

Copper Contributor

Question I have is, for some complex XML to populate a report, should I stick to C#.NET with Excel automation or attempt an XML import with an XSD or something? Preferably without an XSD if possible. Probably use an Excel template as the base. The more I write the more I think I should just stick to C# and they would need to find someone possibly from another department to do updates after I am gone.

 

Primarily I am a C# .NET programmer. I do some Office automation from C#, have done some VBA quite a while back, and done some formulas and fields in Excel. 

 

Idea would be to have something that can be updated by a layperson with basic Excel skills for adding or modifying the simpler elements that may change in the xml over time. Although the more complex calculations would have to be done by a programmer likely.

Reason I ask is because

--some values are based on element names constructed with the portion of another elements value.

--Also, there would be 1 to many sheets based on the number of sections (i.e. SALESORDERs) containing data (in addition to 2 always included sheets). 

--Elements may often need to be filtered based on other elements at the same level.

--Also on the fixed sheets a portion of that will have values in tabular data where a section would populate a column of it. So there would be a varying number of columns based number of salesorders.

 

As an example of complexity, in the following xml:
The value 5.2 from the item with itemname MyItem1 would be used to get the value from item with item name "MyItem23_2".
Where the _2 comes from number 2 after the decimal point.
And where the itempath has the number 5 from before the decimal point between the square brackets [] at the end of the item path as seen here "...class4[5]"
So it takes both the itemname and the itempath to differentiate a specific item when names are the same.

...
<SOAP-ENV:Body>
 <asx:abap xmlns:asx="http://www...." version="1.0">
   <asx:values>
     <SALESORDER>
       <Section1>...
       <ActiveSection>
         <item>
           <some elements not used etc.>
           <itemname>MyItem1</itemname>
           <value>5.2</value>
           <itempath>class1.class2[3].class3</itempath>
         </item>
         <item>
           <some elements not used etc.>
           <itemname>MyItem23_2</CHARC>
           <value>Some text or numeric value</value>
           <itempath>class1.class23[2].class4[5]</itempath>
        </item>
...	
3 Replies
Have you tried to "just" open the XML in Excel? Or even better, use Data, From File, From XML?
Wish I could do it that way. Problem is there exists a report already that I am basing the new one on for another product line. And there are more than 1 section(table) on a page. Plus the XML will actually have many unused items for the report that won't be included. Different sections have items that are in different locations in the XML file and not relative to each other. So would need a way to identify what goes where, and can appear on more than 1 page or section. The deeper I go in the existing code and data, the less likely there are tools that would be beneficial. Have considered XSLT transform into a workable format but that is getting too complex since even the resulting data would need to be interpreted. Or doing a deeper dive into the XSD import schema. Or putting fields in a XLT template with either formulas or VBA as needed to manipulate data and combine item data as needed. But Don't see an easy mapping to the XML file items and relations. If Xpath or something like that is an option from Excel, then it would get too long and cumbersome with the filtering to identify an item, get the value, and map it to a text string for display.
Pretty sure I am abandoning the idea and sticking with C# and Excel automation. With possible DB data mapping and some configuration options in a config file. Things I need to do would get a bit outside of VBAs design as far as I can see. Plus I do have some existing tools I made which I can reuse for part of what I need to do in C#.
Thanx for the response either way.
VBA would be totally capable of doing stuff like this, but it would likely take more coding to get it done as there aren't as many libraries available to do XML manipulation (though there is the Microsoft XML library you can refer to). Use what you know best I'd say.