question

SpellmanLau-1757 avatar image
0 Votes"
SpellmanLau-1757 asked SpellmanLau-1757 commented

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

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.
128434-good-example.png

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.
128443-bad-example.png

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.

dotnet-csharp
good-example.png (45.5 KiB)
bad-example.png (30.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

JackJJun-MSFT avatar image
0 Votes"
JackJJun-MSFT answered SpellmanLau-1757 commented

@SpellmanLau-1757 , 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:

129514-image.png


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.





image.png (49.9 KiB)
image.png (50.3 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Jack,

Setting the format to 'n3' worked for the floating point cells. But unfortunately, there are text cells and integer cells in the same column. As I mentioned in the UPDATE of the original question, I have to put those text cells on the top of the column to make everything work. If I put those text cells on the bottom of the column, all text cells will be cleared and the integers are changed to the wrong format too. Please see the attached screenshot. Thanks.

128799-image-5.png


0 Votes 0 ·
image-5.png (19.2 KiB)

@SpellmanLau-1757, What is your cell format in your cell related to your commented cell? I set the format of all the columns to the above format I mentioned. I can show the text and float value normally. Could you have a try?

0 Votes 0 ·

Hi,

That column has three types of cells:
1. floating point, from 999.001 to 999.059
2. Integer number, 998001 to 998007
3. Text, FVxxx, HICxxx, UCxxx


Set .DefaultCellStyle.Format to "N3" works on floating point only.


0 Votes 0 ·
Show more comments