Intro

Parse an XML file and put the data into arrays for use in an ExcelTemplate report.

The parseXml method of this demo reads an XML file and parses it using XML DOM.  The data is read into an array of type Object[][], and then bound to the report with the BindData method.

This sample requires OfficeWriter Enterprise Edition to be installed because the OfficeWriter Grouping and Nesting is only available in the Enterprise Edition of the product.

Code

public class XmlImport 
    {
         ///<summary>
         ///Build the report with ExcelTemplate
         ///</summary>
        public void GenerateReport()
        {
            //These are the names of the values in WeatherData array 
            string[] colNames = { "Date", "Temp", "Humidity", "Wind" };

            //Read the XML document into an object array 
            object[][] weatherData = ParseXml();

            //Create an instance of SoftArtisans ExcelTemplate 
            ExcelTemplate xlt = new ExcelTemplate();

            //Open the template workbook 
            string templatePath = @"..\..\ExcelTemplateFiles\XMLImportTemplate.xlsx";
            xlt.Open(templatePath);

            //Bind the WeatherData array to the template
            // %%=Weather.Date, %%=Weather.Temp
            // %%=Weather.Humidity, %%=Weather.Wind
            
            DataBindingProperties bindingProperties = xlt.CreateDataBindingProperties();
            bindingProperties.MaxRows = ExcelTemplate.ALL_ROWS;
            bindingProperties.Transpose = true;
            xlt.BindData(weatherData, colNames, "Weather", bindingProperties);

            //Process the template to populate it with the Data Source data 
            xlt.Process();

            //Save the report 
            xlt.Save(@"..\..\ExcelOutputFiles\XmlWeatherReport_output.xlsx");
        }

         ///<summary> Load the XML weather report data into a 2-D
         ///Object array.
         ///</summary>
        /// <returns> Parsed XML data to be used with ExcelTemplate
        /// </returns>

        private object[][] ParseXml()
        {
            //Create an array with an element for each day of weather
            // reported in the XML document
            // There will be four elements in the first dimension
            // to hold "Date", "Temp", "Humidity", and "Wind" data
            // and one element in the second dimension for every day
            // reported in the XML document
            

            XmlDocument doc = new XmlDocument();
            doc.Load(@"..\..\ExcelData\XMLWeatherData.xml");
            
            int NodeCount = doc.DocumentElement.ChildNodes.Count;

            string[][] WeatherData = new string[4][];
            
            for (int i = 0; i < WeatherData.Length; i++)
                WeatherData[i] = new string[NodeCount];

            //Loop through the days of weather 

            for (int i = 0; i < NodeCount; i++)
            {
                XmlNode DayNode = doc.DocumentElement.ChildNodes[i];

                
                //Read the weather values from the XML document into the array
                if (DayNode.Name == "today")
                    WeatherData[0][i] = DateTime.Now.ToString("d");
                else
                    WeatherData[0][i] = DateTime.Now.AddDays(i).ToString("d");
               
                WeatherData[1][i] = DayNode.SelectSingleNode("temparature").InnerText;    
                WeatherData[2][i] = (Convert.ToDouble(DayNode.SelectSingleNode("humidity").InnerText) / 100).ToString();
                WeatherData[3][i] = DayNode.SelectSingleNode("wind").InnerText;     
            }
            return WeatherData;
        }
    }

 

Downloads