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数据并存储在数据库中。当我试图读取数据时,有一些数据被遗漏了,我无法找到。
这是我的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中通读后的数据:
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/