How to read the xls and xlsx files using c#

Question

How to read the xls and xlsx files using c# with OpenXML format Without using the OLEDB connection. I am looking for Open XML format procedure.

Below is the code in which I used the OLEDB preocedure. But I am looking for OpenXML format.

public static DataTable ConvretExcelToDataTable(string FilePath)
{
    string strConn = string.Empty;

     if (FilePath.Trim().EndsWith(".xlsx"))
     {
         strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", FilePath);
     }
     else if (FilePath.Trim().EndsWith(".xls"))
     {
         strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", FilePath);
     }

    OleDbConnection conn = null;
    OleDbCommand cmd = null;
    OleDbDataAdapter da = null;
    DataTable dt = new DataTable();
    try
    {
        conn = new OleDbConnection(strConn);
        conn.Open();
        cmd = new OleDbCommand(@"SELECT * FROM [Sheet1$]", conn);
        cmd.CommandType = CommandType.Text;
        da = new OleDbDataAdapter(cmd);
        da.Fill(dt);
    }
    catch (Exception exc)
    {
        Console.WriteLine(exc.ToString());
        Console.ReadLine();
    }
    finally
    {
        if (conn.State == ConnectionState.Open)
            conn.Close();
        conn.Dispose();
        cmd.Dispose();
        da.Dispose();
    }
    return dt;
}

Requirement is to implement the above conversion in OpenXML format. Thanks.

XLS is not OpenXML. So you cannot use the OpenXML SDK to read XLS files. You can use it only for XLSX files. — Darin Dimitrov, Oct 08 '12 at 14:22
I answered a similar question recently [here](http://stackoverflow.com/a/12746187/969613) — JMK, Oct 08 '12 at 14:41
Anyone please clarify my doubt in the above comment. Thanks. — , Oct 08 '12 at 14:55
You are correct, if Excel is not present the code will not work — JMK, Oct 08 '12 at 16:13
Does above mentioned OLEDB procedure will work in case target machines doesn't have Office installed? Please suggest.. — SST, Nov 10 '12 at 12:04

score 4 · Accepted Answer · edited Aug 20 '14 at 10:35

You'll want the OpenXml SDK for the xlsx:

http://www.microsoft.com/en-gb/download/details.aspx?id=30425

But for the XLS, you won't be able to use this the XLS format is not based on xml.

I use the NPOI library for accessing older files:

http://npoi.codeplex.com/

The NPOI library also supports xlsx, so this would give you a consistent way of accessing them. Downside is you'll have to loop through sheets/rows/columns manually, and build up the dataset which will probably affect performance if you have large workbooks. If you want to use queries to access the data, OLEDB is the only method I've found.

score 2 · Answer 2 · answered Oct 08 '12 at 14:29

2

If you have Excel installed, you could use Microsoft.Office.Interop.Excel;

http://support.microsoft.com/kb/302084

answered Oct 08 '12 at 14:29

Craig Graham

1,161
11
35

3

In Client machine, Excel may not be installed. That's the main reason, I am looking for an alternate solution like OpenXML format. Thanks. – Oct 08 '12 at 14:30
looking for the same solution – Frank Liu Sep 07 '16 at 07:22
Thing is if you're requiring the spreadsheet to be initially saved in a format other than .xls then you're not actually wanting to read an Excel spreadsheet any more and simpler solutions become available- bog standard csv. – Craig Graham Sep 07 '16 at 07:26

score 0 · Answer 3 · edited May 26 '15 at 06:33

0

Remove \ from the connection string. You can give as below.

conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+filepath.ToString() + ";Extended Properties=Excel 12.0 Xml;");

edited May 26 '15 at 06:33

serenesat

4,611
10
37
53

answered May 26 '15 at 06:24

Vivek

1

How to read the xls and xlsx files using c#

3 Answers3

Linked

Related