使用c#中的OleDbConnection读取Excel表时丢失的数据

时间:2022-08-16 21:50:53

I need to read the excel data and store in database. when I try to read the data, there are some data got missed, I couldn't able to find.

我需要读取excel数据并存储在数据库中。当我试图读取数据时,有一些数据被遗漏了,我无法找到。

It's my excel sheet 使用c#中的OleDbConnection读取Excel表时丢失的数据

这是我的excel表

My code :

我的代码:

private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";

 [![//Get the name of the First Sheet.
            using (OleDbConnection con = new OleDbConnection(conStr))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = con;
                    con.Open();
                    DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    sheetName = dtExcelSchema.Rows\[0\]\["TABLE_NAME"\].ToString();
                    con.Close();
                }
            }

            //Read Data from the First Sheet.
            using (OleDbConnection con = new OleDbConnection(conStr))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    using (OleDbDataAdapter oda = new OleDbDataAdapter())
                    {

                        cmd.CommandText = "SELECT * From \[" + sheetName + "\]";
                        cmd.Connection = con;
                        con.Open();
                        oda.SelectCommand = cmd;
                        oda.Fill(dtExcelData);
                        con.Close();

                        //Populate DataGridView.
                        //dataGridView1.DataSource = dt;
                    }
                }
            }

            dtExcelData = dtExcelData.AsEnumerable().Skip(2).Take(1).CopyToDataTable();

            return dtExcelData;][2]][2]

Data after reading through in oledbConnection :

在oledbConnection中通读后的数据:

使用c#中的OleDbConnection读取Excel表时丢失的数据

2 个解决方案

#1


1  

You have some merged cell in your excel Sheet , it's better to access your cells directly

在excel表格中有一些合并的单元格,最好直接访问您的单元格。

I think here also can help you:

我认为这也可以帮助你:

Excel - Reading Merged Cells (rows) in .NET using C#

Excel -使用c#读取.NET中的合并单元格(行)

#2


0  

You can use this class to your project easily; https://exceldatareader.codeplex.com/

您可以轻松地将这个类用于项目;https://exceldatareader.codeplex.com/

#1


1  

You have some merged cell in your excel Sheet , it's better to access your cells directly

在excel表格中有一些合并的单元格,最好直接访问您的单元格。

I think here also can help you:

我认为这也可以帮助你:

Excel - Reading Merged Cells (rows) in .NET using C#

Excel -使用c#读取.NET中的合并单元格(行)

#2


0  

You can use this class to your project easily; https://exceldatareader.codeplex.com/

您可以轻松地将这个类用于项目;https://exceldatareader.codeplex.com/