c# wrong reading from excel with oledb

Question

 private void OnCreated(object sender, FileSystemEventArgs e)
        {
            excelDataSet.Clear();
            string extension = Path.GetExtension(e.FullPath);
            if (extension == ".xls" || extension == ".xlsx")
            {
                string ConnectionString = "";
                if (extension == ".xls") { ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = '" + e.FullPath + "';Extended Properties=\"Excel 8.0;HDR=YES;\""; }
                if (extension == ".xlsx") { ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = '" + e.FullPath + "';Extended Properties=\"Excel 12.0;HDR=YES;\""; }

                using (OleDbConnection conn = new OleDbConnection(ConnectionString))
                {
                    conn.Open();
                    OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [Sheet1$]", conn);
                    objDA.Fill(excelDataSet);
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

This is my code. It's working when my filewatcher triggers. Problem is the excel file I read has 1 header row and 3 row that has values. When I use this code and check my dataset row count I get 9.. I've no idea where does it take that 9 from, am I doing something wrong? I'm checking my code for last 30-35 min and still couldn't find what I'm doing wrong.. I get the column's right but the rows are not working. I don't need the header line btw

Update: my example excel file had 3 rows and I was getting 9 as row count. I just copied these rows and made my file 24 row + 1 header row and when I did rows.count I got 24 as answer. So it worked fine? Is that normal?

Deleted rown in excel can appear as existing rows when doing an unfiltered select via OleDb. if you know a column that will have data in the excel, add a `where` clause with that column — Cleptus, Jun 11 '18 at 13:05

İremE · Accepted Answer · 2021-10-11T17:38:21.877

1

There is a Nuget called Linq to Excel. I used this nuget in several projects to query the data inside .csv and .xlsx files without any difficulty, it is easy to implement. It might be poor in performance but it can resolve your problem.

Here is the documentation of Linq to Excel

edited Oct 11 '21 at 17:38

answered Jun 11 '18 at 13:18

İremE

28
1
8

Thanks for the link, I searched and found a few more documentary about linq to excel and used it. Everything works perfect now – ShinoLexTazy Jun 11 '18 at 13:54

score 0 · Answer 2 · answered Jun 11 '18 at 13:05

I would highly recommend you to take a look at EPPLUS library https://github.com/JanKallman/EPPlus/wiki

I have plently of trouble with oledb until i found EPPLUS. It's really easy to use for creating and updating excel files. There are plenty of good examples out there like the one under which is from How do i iterate through rows in an excel table using epplus?

var package = new ExcelPackage(new FileInfo("sample.xlsx"));

ExcelWorksheet workSheet = package.Workbook.Worksheets[1];
var start = workSheet.Dimension.Start;
var end = workSheet.Dimension.End;
for (int row = start.Row; row <= end.Row; row++)
{ // Row by row...
    for (int col = start.Column; col <= end.Column; col++)
    { // ... Cell by cell...
        object cellValue = workSheet.Cells[row, col].Text; // This got me the actual value I needed.
    }
}

Can't use any extra dll's. I was using excel interop but I find out I can't use that without excel because its using excel dll's. And the program I'm writing will run on a server so there will be no excel on the machine — ShinoLexTazy, Jun 11 '18 at 13:14

c# wrong reading from excel with oledb

2 Answers2