Read Data from XLSX in c#

Question

I am new to c# and am trying to read an XLSX file in c# with the following code:

string Connection = "Provider=Microsoft.ACE.OLEDB.12.0;DataSource=c:\\Temp\\source.xlsx;Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";

//code to read the content of format file 
OleDbConnection con = new OleDbConnection(Connection);
OleDbCommand command = new OleDbCommand();

DataTable dt = new DataTable();
OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Tabelle1$]", con);

myCommand.Fill(dt);
Console.Write(dt.Rows.Count);

I get t a correct count from the output, but I have 2 more questions:

1.How do make a select where statement (how to access the rows)?

 select * from [Tabelle1$] where A = '123' (A being an existing Excel row)

will throw an error mentioning wrong parameters...

2.can anyone supply me with a tutorial link or short sample how to access the data?

Make sure you read the tutorial linked by Eugen very well. You will realize why A = '123' doesn't work (but turning HDR=No and writing WHERE F1 = '123' will) — nantito, May 04 '11 at 16:24
I got it to work with F1, but dont understand why A is not working, and how to access the default table names ... — RRZ Europe, May 05 '11 at 07:45
HDR expresses if there is a HeaDer Row. If it's set to "YES", it means that instead of having set the automated "F1, F2...", you will have the first row's cell as an indicator of the column name. — nantito, May 05 '11 at 15:05
As for the question why "A, B..." are not working I will take a wild guess. When selecting data within the OleDbDataAdapter, you are able to define your sheet after the "FROM" statement. In there as well, you can append your desired range (as shown in the link). Now, the result coming back from such a select, will be a structure of data not having relationship with the default Excel column naming. It would suck if you were able to write SELECT * FROM [Tabelle1$A1:B10] where C = '123' (that doesn't make any sense). — nantito, May 05 '11 at 15:15
check this tutorial http://support.microsoft.com/kb/316934 from MS. — Eugen, May 04 '11 at 16:09

score 20 · Accepted Answer · answered Apr 25 '12 at 13:13

Please refer the following sample code:

private DataTable LoadXLS(string strFile, String sheetName, String column, String value)
{
    DataTable dtXLS = new DataTable(sheetName);

    try
    {
       string strConnectionString = "";

       if(strFile.Trim().EndsWith(".xlsx")) {

           strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strFile);

       } else if(strFile.Trim().EndsWith(".xls")) {

           strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);

       }

       OleDbConnection SQLConn = new OleDbConnection(strConnectionString);

       SQLConn.Open();

       OleDbDataAdapter SQLAdapter = new OleDbDataAdapter();

       string sql = "SELECT * FROM [" + sheetName + "$] WHERE " + column + " = " + value;

       OleDbCommand selectCMD = new OleDbCommand(sql, SQLConn);

       SQLAdapter.SelectCommand = selectCMD;

       SQLAdapter.Fill(dtXLS);

       SQLConn.Close();
    }

    catch (Exception e)
    {
       Console.WriteLine(e.ToString());
    }

    return dtXLS;

}

Download from http://www.microsoft.com/en-us/download/details.aspx?id=13255. either 32bit or 64 bit depends on OS. — Romil Kumar Jain, Oct 12 '12 at 12:05
You forgot to close the SQLConn in a finally or by using. So your code has memory leaks. For anyone implementing it: Change `OleDbConnection SQLConn = new OleDbConnection(strConnectionString);` to `using OleDbConnection SQLConn = new OleDbConnection(strConnectionString) {` and add a closing `}` after `SQLConn.Close();` — Tedd Hansen, Jan 05 '16 at 10:02

score 1 · Answer 2 · answered Nov 29 '18 at 03:15

I know this is an old question with a great answer but this page came up high on google's results for "import xlsx c#" so I wanted to add a more modern and simpler way to read xls/xlsx data using the NPOI library. I want to make sure new c# developers know that there is an easier way to import Excel data rather than using ado.net.

I use a combination of NPOI and Npoi.Mapper (from donnytian: https://github.com/donnytian/Npoi.Mapper) to import Excel files with ease. Add a nuget reference to NPOI and Npoi.Mapper and then you can import xls/xlsx data using strongly typed classes that correlate directly to the columns that you want to import.

```using System.IO; using System.Linq; using Npoi.Mapper; using Npoi.Mapper.Attributes; using NPOI.SS.UserModel; using UserManagementService.Models;

namespace JobCustomerImport.Processors { public class ExcelEmailProcessor { private UserManagementServiceContext DataContext { get; }

    public ExcelEmailProcessor(int customerNumber)
    {
        DataContext = new UserManagementServiceContext();
    }

    public void Execute(string localPath, int sheetIndex)
    {
        IWorkbook workbook;
        using (FileStream file = new FileStream(localPath, FileMode.Open, FileAccess.Read))
        {
            workbook = WorkbookFactory.Create(file);
        }

        var importer = new Mapper(workbook);
        var items = importer.Take<MurphyExcelFormat>(sheetIndex);
        foreach(var item in items)
        {
            var row = item.Value;
            if (string.IsNullOrEmpty(row.EmailAddress))
                continue;

            UpdateUser(row);
        }

        DataContext.SaveChanges();
    }

    private void UpdateUser(MurphyExcelFormat row)
    {
        //LOGIC HERE TO UPDATE A USER IN DATABASE...
    }

    private class MurphyExcelFormat
    {
        [Column("District")]
        public int District { get; set; }

        [Column("DM")]
        public string FullName { get; set; }

        [Column("Email Address")]
        public string EmailAddress { get; set; }

        [Column(3)]
        public string Username { get; set; }

        public string FirstName
        {
            get
            {
                return Username.Split('.')[0];
            }
        }

        public string LastName
        {
            get
            {
                return Username.Split('.')[1];
            }
        }
    }
}

} ```

If you're interested, I've covered some of the finer points on my blog: How to easily import excel files.

Thanks! Dan

Read Data from XLSX in c#

2 Answers2