Read and Import XML file to Excel using C# and VB.Net in ASP.Net

Last Reply 19 days ago By pandeyism

Posted 22 days ago

Hi,

i want to import this xml file then after uploading it, It should be converted into this excel file format. 

This xml file

<?xml version='1.0' encoding='UTF-8'?>
<records>
<record>
<CH_Code>3DR</CH_Code>
<Channel>DR3</Channel>
<AirDate>2018-01-01</AirDate>
<Start>06:10</Start>
<Duration>150</Duration>
<ProgrammeTitle>Morgengymnastikken</ProgrammeTitle>
<Original_Title>Morgengymnastikken</Original_Title>
<Aka_Title></Aka_Title>
<Episode_Title>Tonet krop, løbeteknik og fridans</Episode_Title>
<Ori_Episode_Title>Tonet krop, løbeteknik og fridans</Ori_Episode_Title>
<Episode_Number></Episode_Number>
<Channel_Country>Denmark</Channel_Country>
<Synopsis>Morgengymnastikken på DR3 er et program der giver dig en dejlig fornemmelse i kroppen. I dette afsnit skal vi lave tre forskellige træninger. Vi starter med at tone vores krop med Yvonne, og så skal vi lave løbetræning med Peter.</Synopsis>
<Actors></Actors>
<Director></Director>
<Presenter></Presenter>
<Guests></Guests>
<Production></Production>
<Distrib></Distrib>
<Country>Denmark</Country>
<Product_Year></Product_Year>
<Chn_Category></Chn_Category>
<Language>Danish              </Language>
<Ori_Language>Danish</Ori_Language>
<Category>Factual</Category>
<Type>How to...</Type>
<Content>Health</Content>
<Event_ID>2106932372</Event_ID>
<Program_ID>28193738</Program_ID>
<Emission_Duration>0</Emission_Duration>
<Season_Number></Season_Number>
<Series_ID>23576258</Series_ID>
<Part_Number></Part_Number>
<Tot_Nb_Parts></Tot_Nb_Parts>
<Live/>
<Pilot/>
<First_Showing/>
<Last_Showing/>
<Subtitled/>
</record>
</records>
Posted 19 days ago

Hey SonaliB,

Please refer below sample.

I have used ClosedXml Libraries to export to Excel.

You can refer below article for more details.

Export DataTable to Excel in ASP.Net using C# and VB.Net

HTML

<div>
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button Text="Upload" runat="server" OnClick="ExportToExcel" />
</div>

Namespaces

C#

using System.Data;
using ClosedXML.Excel;
using System.IO;

VB.Net

Imports System.Data
Imports ClosedXML.Excel
Imports System.IO

Code

C#

protected void ExportToExcel(object sender, EventArgs e)
{
    string excelPath = Server.MapPath("~/File/") + Path.GetFileName(FileUpload1.PostedFile.FileName);        
    FileUpload1.SaveAs(excelPath);
    DataSet ds = new DataSet();
    ds.ReadXml(excelPath);
    using (XLWorkbook wb = new XLWorkbook())
    {
        wb.Worksheets.Add(ds.Tables[0], "Customers");

        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}

VB.Net

Protected Sub ExportToExcel(ByVal sender As Object, ByVal e As EventArgs)
    Dim excelPath As String = Server.MapPath("~/File/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(excelPath)
    Dim ds As DataSet = New DataSet()
    ds.ReadXml(excelPath)
    Using wb As XLWorkbook = New XLWorkbook()
        wb.Worksheets.Add(ds.Tables(0), "Customers")
        Response.Clear()
        Response.Buffer = True
        Response.Charset = ""
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx")
        Using MyMemoryStream As MemoryStream = New MemoryStream()
            wb.SaveAs(MyMemoryStream)
            MyMemoryStream.WriteTo(Response.OutputStream)
            Response.Flush()
            Response.End()
        End Using
    End Using
End Sub