question

CzarR-3851 avatar image
0 Votes"
CzarR-3851 asked JackJJun-MSFT answered

C# code to export xml response to Excel


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>












dotnet-csharpwindows-apioffice-scripts-excel-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

JackJJun-MSFT avatar image
1 Vote"
JackJJun-MSFT answered

@CzarR-3851, 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:

146410-image.png




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.


image.png (21.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.