Loading

Data export from Excel to XML

Data export from Excel to XML

Basit Khan

8y914.NET

Hi,

I wrote the below code to export data from excel to xml, but in excel file if column cell is empty then in xml that column/element is not showing.

i want whereever empty cell in xml should show as 0

Private Function exportExceltoXML(ByVal excelPath As String) As String
Dim cnExcel As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties=Excel 12.0;")
cnExcel.Open()
Dim ds As New DataSet
Dim da As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", cnExcel)
da.Fill(ds)
cnExcel.Close()
Dim sXML As String
sXML = ds.GetXml()
ds.WriteXml(TextBox1.Text & "\Trn_Daily3.xml")
ds.Dispose()
da.Dispose()
MsgBox("Exported successfully")
Return sXML
End Function

Thanks

Basit.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

alice yang8y ago
Hi,

You can use spire.xls to convert excel to xml, the code is very simple.
Workbook wb = new Workbook();
wb.LoadFromFile("sample.xlsx");
wb.SaveAsXml("result.xml");
For more information, check this link: https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Export-Excel-to-XML-and-Import-XML-to-Excel.html
Suraj Kumar8y ago
Hello,
You can use IIF for all cell in your select statment query as below.
select IIF(ColumnName1 IS NULL, 0, ColumnName1 ) AS ColumnName1, IIF(ColumnName2 IS NULL, 0, ColumnName2 ) AS ColumnName2 FROM [Sheet1$]
So in place of * write column name with IIF condition.
Thanks
Basit Khan8y ago
This is same thing showing what showing with .net code.
Sagar Pandurang Kap8y ago
Hi,
Refer bewlo link it might help :-

https://bitwizards.com/Thought-Leadership/Blog/2010/November-2010/How-To-Export-an-Excel-2010-Worksheet-to-XML
http://www.excel-easy.com/examples/xml.html

Hope it helps...