1

I have a problem when I try to read a Excel file in C#

filePath = txtExcelFile.Text;
                string[] fileSpit = filePath.Split('.');
                if (filePath.Length > 1 && fileSpit[1] == "xls")
                {
                    connString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=No'";
                }
                else
                {
                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=No'";
                }
OleDbCommand cmd = new OleDbCommand(@"Select * from [" +comboBox1.SelectedValue.ToString() + "]", ole);
OleDbDataAdapter oledata = new OleDbDataAdapter();
oledata.SelectCommand = cmd;
DataSet ds = new DataSet();
oledata.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;

But it only shows only partial data. I have attached images.

Kate Mid
  • 33
  • 1
  • 8
  • Please include the code that sets up the `Connection` to the Excel spreadsheet. – mjwills Aug 10 '17 at 11:28
  • Possible duplicate of [Can I specify the data type for a column rather than letting linq-to-excel decide?](https://stackoverflow.com/questions/21739269/can-i-specify-the-data-type-for-a-column-rather-than-letting-linq-to-excel-decid) – mjwills Aug 10 '17 at 11:28
  • Yap, I added code Connection Excel. But it's still dont work :( – Kate Mid Aug 11 '17 at 02:43

3 Answers3

1

It reads the first few records and decides that the column is of type integer, which fails when it finds data which is not of integer type.

You need to set HDR=No as a property in your connection string and IMEX=1

Rob Anthony
  • 1,743
  • 1
  • 13
  • 17
  • Thanks Rob, but i edited HDR=No, but it's dont work :( I dont know what's the error :( – Kate Mid Aug 11 '17 at 02:45
  • Do you have a header in your file? If not, put one in as HDR=No works by reading the header as days and forcing all the data to be read as text. I've also suggested the IMEX=1 change too. – Rob Anthony Aug 11 '17 at 06:26
  • Yes, thanks Rob. I added IMEX=1 and it's work. But i have a new problem.DataGridview show data lines than excel file. – Kate Mid Aug 11 '17 at 07:21
  • Exp: Excel file has 20 rows but datagridview show 25 rows, 20 rows in excel file and 5 blank rows . So, i cant right RowCount DataGridView @@ – Kate Mid Aug 11 '17 at 07:23
  • try deleting the 'blank' rows in Excel, they may not actually be blank. – Rob Anthony Aug 11 '17 at 07:38
  • Yes, i understant@@I will try deleting.. Thank you very much :v – Kate Mid Aug 11 '17 at 08:29
1

I ran into this same type of issue before and no matter the settings in the connection string I still had missing column data. In order to work around it, I ended up using the open source ExcelDataReader.DataSet nuget package(https://github.com/ExcelDataReader/ExcelDataReader) which can read Excel files without using the oledb driver or having excel installed.

Below is the code that should work for you as I created both an .xls and .xlsx using your sample data, and the dataset returned contained all values. The ExcelDataReader will also detect whether it's an .xls or .xlsx.

       var filePath = txtExcelFile.Text;

        using (var stream = File.Open(filePath,FileMode.Open,FileAccess.Read))
        {
            using (var reader = ExcelReaderFactory.CreateReader(stream))
            {
                var ds = reader.AsDataSet();
            }

            dataGridView1.DataSource = ds.Tables[0].DefaultView;
        }
Darthchai
  • 757
  • 8
  • 17
  • Yes, I undertand and thanks Darthchai. I added HDR and IMEX into connection code and it's work. – Kate Mid Aug 11 '17 at 07:26
  • OMG thank you for this! The ExcelDataReader works great! I have been fussing with connection strings for a couple of DAYS now, trying to figure out the occasionally missing data (it was certainly not a type issue, as at least a hundred thousand rows had mixed string/numeric data). OH and yes, this is considerable faster. – gojimmypi Dec 24 '20 at 00:33
0

It might also help if you specify the column data type in excel an set it to text. I think this works.

If not, you could also try inserting a dummy row on line 2 (or 1 if hdr=no) and skip over it when processing the file.

Or you could use an other sollution to read the file. I personally prefer ooxml because you won't have to depend on the oledb driver beeing installed.

P. Zantinge
  • 185
  • 1
  • 15