Convert excel sheet to dataset

I had to convert an excel file with different languages into a xml/dataset.

Below is the sample excel sheet I had

English	French	Spanish	Portugese
Guinea	Guinéee	Guinea	Guiné
Côte d’Ívoire	Côte d’Ívoire	Costa de Marfil	Costa do Marfim
South Africa	Afrique du Sud	Sudáfrica	África do Sul

For Excel 97-2003 format Microsoft Jet OLEDB Driver 4.0 is used. A sample connection string as follows.For Excel 2007 format the new Microsoft Ace OLEDB Driver 4.0 is used.

Code to convert is shown below:

//Connection String for Excel 97-2003 Format (.XLS)

String strExcelConn = “Provider=Microsoft.Jet.OLEDB.4.0;”

+ “Data Source=C:\\Gitolekha\\test.xls;“

+ “Extended Properties=’Excel 8.0;HDR=Yes’“;

//Connect to excel sheet

OleDbConnection connExcel = new OleDbConnection(strExcelConn);

OleDbDataAdapter da = new OleDbDataAdapter();

OleDbCommand cmdExcel = new OleDbCommand();

cmdExcel.Connection = connExcel;

//Access the sheet

connExcel.Open();

DataTable dtExcelSchema;

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

DataSet ds = new DataSet();

string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();//It fetches the first sheet Sheet1$

cmdExcel.CommandText = “SELECT * From [" + SheetName + "]“;

da.SelectCommand = cmdExcel;

da.Fill(ds);

connExcel.Close();

StringWriter sw = new StringWriter();

ds.WriteXml(sw, XmlWriteMode.IgnoreSchema);

string textToConvert = sw.ToString();

//Encoding xml as it contains non ASCII characters of different languages

Encoding latin = Encoding.GetEncoding(28591);

Encoding iso8= Encoding.GetEncoding(“iso-8859-8“);

Byte[] latinBytes= latin.GetBytes(textToConvert);

Byte[] iso8bytes = Encoding.Convert(latin, iso8, latinBytes);

string str = Encoding.UTF8.GetString(iso8bytes);

return str;

There is one point to remember if you are running this on a 64-bit platform:

I kept on getting eror “‘Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine“, after much searching on the net, I dicovered :