Convert XML to Excel file using Interop Library in C# and VB.Net

Hi gokuldas,

gokuldas says:

XmlNodeList rows = xmlDoc.SelectNodes("//Row");

But make sure the the XML structure has <Row> elements.

gokuldas says:

workbook.Save();

You need to make use of workbook.SaveAs method.

Except that there is no issue in the code.

I have created an example. Please refer it.

The XML file

<?xml version="1.0" standalone="yes"?>
<Customers>
	<Customer>
		<CustomerId>1</CustomerId>
		<Name>John Hammond</Name>
		<Country>United States</Country>
	</Customer>
	<Customer>
		<CustomerId>2</CustomerId>
		<Name>Mudassar Khan</Name>
		<Country>India</Country>
	</Customer>
	<Customer>
		<CustomerId>3</CustomerId>
		<Name>Suzanne Mathews</Name>
		<Country>France</Country>
	</Customer>
	<Customer>
		<CustomerId>4</CustomerId>
		<Name>Robert Schidner</Name>
		<Country>Russia</Country>
	</Customer>
</Customers>

Namespaces

C#

using System.IO;
using System.Xml;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

VB.Net

Imports System.IO
Imports System.Xml
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel

Code

Here i am selection the node as Customer as per my XML structure in the SelectNodes method.

C#

private void OnExport(object sender, EventArgs e)
{
    XmlDocument xmlDoc = new XmlDocument();
    xmlDoc.Load(@"D:\Files\Customers.xml");

    // Get XML rows (assuming the XML structure has <Customer> elements)
    XmlNodeList rows = xmlDoc.SelectNodes("//Customer");

    // Create a new Excel application
    Excel.Application excelApp = new Excel.Application
    {
        Visible = false, // Set to true if you want to see the Excel application
        DisplayAlerts = false // Disable any Excel alerts or messages
    };

    // Create a new Workbook and Worksheet
    Excel.Workbook workbook = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];

    int rowIndex = 1;

    // Loop through XML rows and insert them into the Excel sheet
    foreach (XmlNode row in rows)
    {
        int columnIndex = 1;
        foreach (XmlNode cell in row.ChildNodes)
        {
            // Set the cell valuecvbnmmmkkkjhgfdsaqwertyyo
            worksheet.Cells[rowIndex, columnIndex] = cell.InnerText;
            columnIndex++;
        }
        rowIndex++;
    }
    string excelFilePath = @"D:\Files\Customers.xlsx";
    if (File.Exists(excelFilePath))
    {
        File.SetAttributes(excelFilePath, FileAttributes.Normal); // Ensure it's not read-only
    }

    // Save the Excel file with the specified path and format
    workbook.SaveAs(
                    excelFilePath, // Path to save the file
                    Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing  // .xlsx format
                );

    excelApp.Quit();

    //Release the COM objects to prevent memory leaks
    Marshal.ReleaseComObject(worksheet);
    Marshal.ReleaseComObject(workbook);
    Marshal.ReleaseComObject(excelApp);

    MessageBox.Show("XML successfully converted to Excel!");
}

VB.Net

Private Sub OnExport(sender As Object, e As EventArgs) Handles btnExport.Click
    Dim xmlDoc As New XmlDocument()
    xmlDoc.Load("D:\Files\Customers.xml")

    ' Get XML rows (assuming the XML structure has <Customer> elements)
    Dim rows As XmlNodeList = xmlDoc.SelectNodes("//Customer")

    ' Create a new Excel application
    Dim excelApp As New Excel.Application With {
        .Visible = False, ' Set to True if you want to see the Excel application
        .DisplayAlerts = False ' Disable any Excel alerts or messages
    }

    ' Create a new Workbook and Worksheet
    Dim workbook As Excel.Workbook = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
    Dim worksheet As Excel.Worksheet = CType(workbook.Worksheets(1), Excel.Worksheet)

    Dim rowIndex As Integer = 1

    ' Loop through XML rows and insert them into the Excel sheet
    For Each row As XmlNode In rows
        Dim columnIndex As Integer = 1
        For Each cell As XmlNode In row.ChildNodes
            ' Set the cell value
            worksheet.Cells(rowIndex, columnIndex) = cell.InnerText
            columnIndex += 1
        Next
        rowIndex += 1
    Next

    Dim excelFilePath As String = "D:\Files\Customers.xlsx"
    If File.Exists(excelFilePath) Then
        File.SetAttributes(excelFilePath, FileAttributes.Normal) ' Ensure it's not read-only
    End If

    ' Save the Excel file with the specified path and format
    workbook.SaveAs(
        excelFilePath, ' Path to save the file
        Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing ' .xlsx format
    )

    excelApp.Quit()

    ' Release the COM objects to prevent memory leaks
    Marshal.ReleaseComObject(worksheet)
    Marshal.ReleaseComObject(workbook)
    Marshal.ReleaseComObject(excelApp)

    MessageBox.Show("XML successfully converted to Excel!")
End Sub

The converted Excel