C# code to export xml response to Excel

Question

HI, I have a c# API call program written(below) that gets an xml response from which I need help to export the data from the "Query Data" node to an Excel sheet in a shared location with today date as the name of the excel file. Data in the query data node is has columns separated by space. Please help. Thanks in advance.

string requestXml = $@"<?xml version=""1.0"" encoding=""UTF-8"" ?>  
            <CWL_DATA >  
            <CWL_REQUEST RequestType=""Query"" ClientID= ""{ClientID}"" UserID =""{UserID}"" Password=""{Password}"">  
            <CRITERIA QueryName=""DetailRpt"">  
            <FILTERBY DateFrom=""2021-06-27"" />  
            <FILTERBY DateTo=""2021-11-19"" />  
            </CRITERIA>  
            </CWL_REQUEST>  
            </CWL_DATA>";  
  
            
  
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(destinationUrl);  
                byte[] bytes;  
                bytes = System.Text.Encoding.ASCII.GetBytes(requestXml);  
                request.ContentType = "text/xml; encoding='utf-8'";  
                request.ContentLength = bytes.Length;  
                request.Method = "POST";  
                Stream requestStream = request.GetRequestStream();  
                requestStream.Write(bytes, 0, bytes.Length);  
                requestStream.Close();  
                HttpWebResponse response;  
                response = (HttpWebResponse)request.GetResponse();  
                if (response.StatusCode == HttpStatusCode.OK)  
                {  
                    Stream responseStream = response.GetResponseStream();  
                    string responseStr = new StreamReader(responseStream).ReadToEnd();  
                    Console.WriteLine(responseStr);  
                    //return responseStr;  
                }  

XML response:

<?xml version="1.0" encoding="UTF-8"?>  
<CWL_DATA>  
 <CWL_RESPONSE RequestType="Query" ClientID="XXX ResponseDateTime="11/3/2021 4:31:57 PM">  
 <CRITERIA QueryName="DetailRpt">  
 <FILTERBY DateFrom="2021-06-27" />  
 <FILTERBY DateTo="2021-11-19" />  
 </CRITERIA>  
 <QUERY_DATA AdvanceDt="10/19/2021 9:28:38 PM" ClientNum="75491dsf42"   
 CNum="13461610" BorlastName="Kadire" RequestType="W" RequestWireAmt="85000.00" Amt="94300.00"  
 AdvanceAmt="91471.00" AdjustmentRsn="" OverUnderPosting="-6471.00" ClosingAgent="CBR"  
 ABANum="086500634" AcctNum="081509070" DraftNum="" OrigAdvAmt="91471.00" FedRefNum=""  
 FedExTrackNum="" />  
 <QUERY_DATA AdvanceDt="10/19/2021 10:57:54 PM" ClientNum="75491dsf47"   
 CNum="13461614" BorlastName="Gutha" RequestType="W" RequestWireAmt="90000.00" Amt="94700.00"  
 AdvanceAmt="91859.00" AdjustmentRsn="" OverUnderPosting="-1859.00" ClosingAgent="AR"  
 ABANum="086500634" AcctNum="081509070" DraftNum="" OrigAdvAmt="91859.00" FedRefNum=""  
 FedExTrackNum="" />  
 </CWL_RESPONSE>  
</CWL_DATA>  

Answer 1

@CzarR , Welcome to Microsoft Q&A, you could try the following code to export the data from the "Query Data" node to an Excel file.

Please install the nuget-package Microsoft.Office.Interop.Excel first of all.

using System;  
using System.Collections.Generic;  
using System.Data;  
using System.Linq;  
using System.Xml.Linq;  
using Excel = Microsoft.Office.Interop.Excel;  

 static void Main(string[] args)  
        {  
            string path = "D:\\test.xml";  
            XDocument doc = XDocument.Load(path);  
            var result = doc.Descendants("QUERY_DATA");  
            List<string> collist = new List<string>();  
            foreach (var item in result)  
            {  
                var atts = item.Attributes();  
                foreach (var att in atts)  
                {  
                    collist.Add(att.Name.ToString());  
                }  
            }  
            collist = collist.Distinct().ToList();  
            DataTable table = new DataTable();  
            foreach (var item in collist)  
            {  
                table.Columns.Add(item);  
            }  
            foreach (var item in result)  
            {  
                var arr = item.Attributes().Select(i => i.Value.ToString()).ToArray();  
                table.Rows.Add(arr);  
            }  
            string path1 = "D:\\test.xlsx";  
            ExportToExcel(table, path1);  
  
  
  
  
        }  
  
        public static void ExportToExcel(DataTable tbl, string excelFilePath = null)  
        {  
            try  
            {  
                if (tbl == null || tbl.Columns.Count == 0)  
                    throw new Exception("ExportToExcel: Null or empty input table!\n");  
  
                // load excel, and create a new workbook  
                var excelApp = new Excel.Application();  
                excelApp.Workbooks.Add();  
  
                // single worksheet  
                Excel._Worksheet workSheet = excelApp.ActiveSheet;  
  
                // column headings  
                for (var i = 0; i < tbl.Columns.Count; i++)  
                {  
                    workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName;  
                }  
  
                // rows  
                for (var i = 0; i < tbl.Rows.Count; i++)  
                {  
                    // to do: format datetime values before printing  
                    for (var j = 0; j < tbl.Columns.Count; j++)  
                    {  
                        workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j];  
                    }  
                }  
  
                // check file path  
                if (!string.IsNullOrEmpty(excelFilePath))  
                {  
                    try  
                    {  
                        workSheet.SaveAs(excelFilePath);  
                        excelApp.Quit();  
                        Console.WriteLine("Excel file saved!");  
                    }  
                    catch (Exception ex)  
                    {  
                        throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"  
                        + ex.Message);  
                    }  
                }  
                else  
                { // no file path is given  
                    excelApp.Visible = true;  
                }  
            }  
            catch (Exception ex)  
            {  
                throw new Exception("ExportToExcel: \n" + ex.Message);  
            }  
        }  

Note: I noticed that your xml has an error, Please modify it to the correct xml.(Missing ")

Change

<CWL_RESPONSE RequestType="Query" ClientID="XXX ResponseDateTime="11/3/2021 4:31:57 PM">  

Into:

<CWL_RESPONSE RequestType="Query" ClientID="XXX"  ResponseDateTime="11/3/2021 4:31:57 PM">  

Result in excel file:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.