excel to xml conversion using ado

Question

strong text

can any one help me in converting excel data into xml file using ado.net

Answer 1

I've not used ado.net, but I've used xquery very successfully for this. Use excel export to create an XML file, then write xquery/xpath commands to convert as you want. Excel XML export format is pretty gnarly but it does do the job. Use the Oxygen 30 day eval license to lighten the xquery debug job.

Answer 2

You can use the Microsoft Jet OLEDB 4.0 Data Provider to read the Excel file. Information about how to establish a connection to an Excel file can be found here.

This article explains how to read an Excel file using the provider. Once you have read the data, you can compose your XML document using LINQ to XML or the System.Xml classes.

Answer 3

In Excel, you can save the file to XML by using the File menu and changing the saved file type to XML spreadsheet.

If you want to read an Excel XML file with ADO.Net, try the XmlReader.

Or see this step-by-step example from Microsoft.

Answer 4

use this code :

 public static DataSet exceldata(string filelocation)

{

DataSet ds = new DataSet();

OleDbCommand excelCommand = new OleDbCommand();OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();

string excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filelocation + "; Extended Properties =Excel 8.0;";
OleDbConnection excelConn = new OleDbConnection(excelConnStr);

excelConn.Open();
DataTable dtPatterns = new DataTable();excelCommand = new OleDbCommand("SELECT UUID, `PATTERN` as PATTERN, `PLAN` as PLAN FROM [PATTERNS$]", excelConn);

excelDataAdapter.SelectCommand = excelCommand;

excelDataAdapter.Fill(dtPatterns);
dtPatterns.TableName = "Patterns";

ds.Tables.Add(dtPatterns);
return ds;

}

and then convert returned datatable to xml with DataTable.WriteXml()