Export Excel data to XML and replace Empty value with Zero (0) using C# and VB.Net in ASP.Net

Hi basit0079,

I have modified your code. Check the modified code and change as per your requirement.

Code

C#

private string exportExceltoXML(string excelPath)
{
    OleDbConnection cnExcel = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties=Excel 12.0;");
    cnExcel.Open();
    DataSet ds = new DataSet();
    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", cnExcel);
    da.Fill(ds);
    cnExcel.Close();
    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
        for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
        {
            if (string.IsNullOrEmpty(ds.Tables[0].Rows[i][j].ToString()))
            {
                ds.Tables[0].Rows[i][j] = "0";
                ds.Tables[0].AcceptChanges();
            }
        }
    }
    string sXML;
    sXML = ds.GetXml();
    ds.WriteXml(@"C:\Users\dharmendra\Desktop\Trn_Daily3.xml");
    ds.Dispose();
    da.Dispose();
    return sXML;
}

VB.Net

Private Function exportExceltoXML(ByVal excelPath As String) As String
    Dim cnExcel As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & excelPath & ";Extended Properties=Excel 12.0;")
    cnExcel.Open()
    Dim ds As DataSet = New DataSet()
    Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", cnExcel)
    da.Fill(ds)
    cnExcel.Close()
    For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
        For j As Integer = 0 To ds.Tables(0).Columns.Count - 1
            If String.IsNullOrEmpty(ds.Tables(0).Rows(i)(j).ToString()) Then
                ds.Tables(0).Rows(i)(j) = "0"
                ds.Tables(0).AcceptChanges()
            End If
        Next
    Next

    Dim sXML As String
    sXML = ds.GetXml()
    ds.WriteXml("C:\Users\dharmendra\Desktop\Trn_Daily3.xml")
    ds.Dispose()
    da.Dispose()
    Return sXML
End Function

Excel Sheet Data

After Export Xml Data

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <CustomerId>1</CustomerId>
    <Name>Mudassar Khan</Name>
    <Country>India</Country>
  </Table>
  <Table>
    <CustomerId>2</CustomerId>
    <Name>Maria </Name>
    <Country>Austria</Country>
  </Table>
  <Table>
    <CustomerId>3</CustomerId>
    <Name>Ana Trujillo </Name>
    <Country>France</Country>
  </Table>
  <Table>
    <CustomerId>4</CustomerId>
    <Name>Antonio Moreno </Name>
    <Country>0</Country>
  </Table>
  <Table>
    <CustomerId>5</CustomerId>
    <Name>Christina Berglund</Name>
    <Country>Ireland</Country>
  </Table>
</NewDataSet>