c# reading Excel data using OleDb does not work well with certain Excel file

Question

Hi,

I created a c# winfrom app to read the Excel and put the data in a datagridview. Somehow, it works for one Excel file, but not for the other one.

Here is the good example. I can read 999.010 in datagridview without any issue.

Here is the bad example. 999.010 is indicated as 999.01. The same issue to 999.020, etc. And 999.028 became 999.028000000001.

Both Excel files are .xlsx file created in MS Office 365. The cell format is Number, with 3 decimals. No idea why the result is different. Could you please help?

I use this code to convert the spreadsheet to datatable:
public DataTable dtCfgSheet(string FileName, string SheetName)
{
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FileName + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
OleDbConnection oleConn = new OleDbConnection(strConn);
OleDbCommand oleCmd = new OleDbCommand();
DataTable dtCfg = new DataTable();
oleCmd.Connection = oleConn;
oleCmd.CommandType = CommandType.Text;
oleCmd.CommandText = "SELECT * FROM [" + SheetName + "$]";
OleDbDataAdapter oleDA = new OleDbDataAdapter(oleCmd);
oleDA.Fill(dtCfg);
oleConn.Close();
return dtCfg;
}

And use this code to plot the datatable in the datagridview:

                    dtCfg = dtCfgSheet(tbxCfgSht.Text, cboxShts.Text);                          
                    dgvCfg.DataSource = dtCfg;  

Thanks.

[UPDATE]
Well. I found the difference between those two Excel files. In the 'good' one, the few cells on the top of the 3rd column is text. But in the 'bad' one, those cells are numbers. I do have some text cell in it, but they are at the bottom. After I moved those text cells to the top, the issue is resolved. But I still want to know why and what the proper way to do. Thanks.

Answer 1

@Spellman.Lau ， based on my test, I reproduced your problem. I find that the problem is that the converted datatable will the datatype System.Double after we use OLEDB to read the excel. Therefore, the datatable can not have the type system.string.

For solving the problem, I recommend that you used another method to convert excel to datatable without the above problem.

Please install nuget-package Microsoft.Office.Interop.Excel first.

Then, Please use the following code.

  public DataTable READExcel(string path)  
    {  
        Microsoft.Office.Interop.Excel.Application objXL = null;  
        Microsoft.Office.Interop.Excel.Workbook objWB = null;  
        objXL = new Microsoft.Office.Interop.Excel.Application();  
        objWB = objXL.Workbooks.Open(path);  
        Microsoft.Office.Interop.Excel.Worksheet objSHT = objWB.Worksheets[1];  
  
        int rows = objSHT.UsedRange.Rows.Count;  
        int cols = objSHT.UsedRange.Columns.Count;  
        DataTable dt = new DataTable();  
        int noofrow = 1;  
  
        for (int c = 1; c <= cols; c++)  
        {  
            string colname = objSHT.Cells[1, c].Text;  
            dt.Columns.Add(colname);  
            noofrow = 2;  
        }  
  
        for (int r = noofrow; r <= rows; r++)  
        {  
            DataRow dr = dt.NewRow();  
            for (int c = 1; c <= cols; c++)  
            {  
                dr[c - 1] = objSHT.Cells[r, c].Text;  
            }  
  
            dt.Rows.Add(dr);  
        }  
  
        objWB.Close();  
        objXL.Quit();  
        return dt;  
    }  

Finally, you can use the following code to set the format in the datagirview based on the values.

private void Form1_Load(object sender, EventArgs e)  
    {  
        DataTable dt = READExcel("D:\\test1.xlsx");  
        dataGridView1.DataSource = dt;  
        float f = 0;  
        int t = 0;  
        for (int i = 0; i <dataGridView1.Rows.Count-1; i++)  
        {  
            string value = dataGridView1.Rows[i].Cells[2].Value.ToString();  
            
            if(int.TryParse(value,out t))  
            {  
                dataGridView1.Rows[i].Cells[2].Style.Format = "##";  
            }  
            else if(float.TryParse(value,out f))  
            {  
                dataGridView1.Rows[i].Cells[2].Style.Format = "N3";  
            }  
            else  
            {  
                dataGridView1.Rows[i].Cells[2].Style.Format = String.Format("c");   
            }  
        }  
  
  
    }  

Result:

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.