Introduction

Excel is a Microsoft Office component. We often need to convert an Excel document to a tag-based format like XML.

Here I will write a program in C# to convert an Excel file to XML.

First of all we see our requirement of what type of XML file we want, in other words which column comes first and so on.

So, here we are starting.

Excel file

Here we have an Excel file named Birthday list Feb 15 for team.xlsx.

In which we have 5 columns and we sorted them by day in ascending order.

xml data

And now we will convert this file to a XML document.

So now start your Visual Studio.

Create a new project, ASP.NET web application, named ExcelXml.

Create a new projec

In Solution Explorer click on Default.aspx.

solution explorer

And place the following 3 controls:

  1. A file upload control.
  2. A button.
  3. A Gridvew to display Excel data is coming or not.

Gridvew

Now we will write C# code to convert an Excel file. So in Default.aspx.cs we write our code as in the following:

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using System.IO;
  8. using System.Data.OleDb;
  9. using System.Data;
  10. using System.Xml;
  11. using System.Text;
  13. namespace ExcelXml
  14. {
  15. public partial class _Default : System.Web.UI.Page
  16. {
  17. protected void Page_Load(object sender, EventArgs e)
  18. {
  20. }
  22. protected void btncnvrt_Click(object sender, EventArgs e)
  23. {
  24. string day, day1 = "";
  25. string connStr = "";
  26. int i = 0;
  27. int oItem = 0;
  29. if (file1.HasFile)
  30. {
  31. string filename = Path.GetFileName(file1.PostedFile.FileName);
  32. string fileExtension = Path.GetExtension(file1.PostedFile.FileName);
  33. string filelocation = "C:/Users/VKumar/Desktop/" + filename;
  34. if (fileExtension == ".xls" || fileExtension == ".XLS")
  35. {
  36. connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filelocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
  38. }
  39. else if (fileExtension == ".xlsx" || fileExtension == ".XLSX")
  40. {
  41. connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filelocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
  42. }
  44. OleDbConnection conn = new OleDbConnection(connStr);
  45. OleDbCommand cmd = new OleDbCommand();
  46. cmd.Connection = conn;
  47. OleDbDataAdapter da = new OleDbDataAdapter(cmd);
  48. DataTable dt = new DataTable();
  49. conn.Open();
  50. DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
  51. string sheetName = dtSheet.Rows[0]["table_name"].ToString();
  52. cmd.CommandText = "select * from [" + sheetName + "]";
  53. da.SelectCommand = cmd;
  54. da.Fill(dt);
  55. conn.Close();
  56. grdExcel.DataSource = dt;
  57. grdExcel.DataBind();
  59. DataSet ds = new DataSet();
  60. ds.Tables.Add(dt);
  61. oItem = dt.Rows.Count;
  62. oItem -= 1;
  65. XmlDocument doc = new XmlDocument();
  66. XmlDeclaration declaire = doc.CreateXmlDeclaration("1.0", "utf-8", null);
  67. // -----------------------create root-----------------------------
  68. XmlElement rootnode = doc.CreateElement("root");
  69. doc.InsertBefore(declaire, doc.DocumentElement);
  70. doc.AppendChild(rootnode);
  72. while (i < oItem)
  73. {
  75. day = dt.Rows[i].ItemArray[0].ToString();
  77. if (day != day1)
  78. {
  79. day1 = day;
  80. DateTime d = Convert.ToDateTime(dt.Rows[i].ItemArray[4]);
  82. string str = Convert.ToDateTime(d.ToString("dd-MMMM-yyyy")).ToString("MMMM-dd-yyyy");
  84. string finaldate = str.Replace(d.Year.ToString(), DateTime.Now.ToString("yyyy"));
  86. XmlElement dobEle = doc.CreateElement("DOB");
  88. dobEle.SetAttribute("date", finaldate);
  91. do
  92. {
  93. XmlElement emp = doc.CreateElement("EmpDetails");
  94. XmlElement name = doc.CreateElement("Name");
  95. XmlElement desig = doc.CreateElement("Designation");
  96. XmlElement dept = doc.CreateElement("Dept");
  97. XmlElement loc = doc.CreateElement("Location");
  99. name.InnerText = dt.Rows[i].ItemArray[1].ToString();
  100. desig.InnerText = dt.Rows[i].ItemArray[2].ToString();
  101. dept.InnerText = desig.InnerText;
  102. loc.InnerText = dt.Rows[i].ItemArray[3].ToString();
  104. emp.AppendChild(name);
  105. emp.AppendChild(desig);
  106. emp.AppendChild(dept);
  107. emp.AppendChild(loc);
  109. dobEle.AppendChild(emp);
  110. i++;
  111. } while (day1 == dt.Rows[i].ItemArray[0].ToString() && i < oItem);
  114. doc.DocumentElement.AppendChild(dobEle);
  116. }
  117. }
  118. doc.Save("C:/Users/VKumar/Desktop/Output.xml");
  119. Response.Write("Created");
  120. }
  121. }
  122. }
  123. }
Now we need to build our program.

Press F6 to build.

build

Run the project.

Press F5.

Run the project

Choose your file from your desktop or where you put your file.

Choose your file

Then click the button.

You get the following result in your browser.

result

To find the desired output file in XML go to your desktop and look for Output.xml.

Here it is.

Output.xml

Output

Conclusion

So in this way we convert an Excel file to XML. One thing we need to remember is that we need to sort the columns of the Excel file depending on which column comes first.