用dataset读取Excel数据并导入到sql,在已知列数前提下,去掉空行插入只插入数据行
以下 是按钮事件下的方法
private void button2_Click(object sender, EventArgs e)
{
try
{
#region DataSet读取Excel
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\121.xlsx; Extended Properties='Excel 8.0'";
string strExcel = string.Format("select * from [第一个工作表$]");
OleDbConnection connExcel = new OleDbConnection(strConn);
OleDbCommand comm = new OleDbCommand(strExcel, connExcel);
OleDbDataAdapter oledbDA = new OleDbDataAdapter(comm);
DataSet ds = new DataSet();
connExcel.Open();
oledbDA.Fill(ds, "shee1");
connExcel.Close();
#endregion
int rowCount = ds.Tables[0].Rows.Count;
string sqlCom = "insert into AE_Workflow_Exam values(";
string sqlCom1 = sqlCom;
#region 在已知列数情况下判断空行,
for (int rows = 0; rows < rowCount; rows++)
{
int number = 0;
for (int col = 0; col < 11; col++)
{
//判断某行 的单元格内容是否为空,为空就累加
if (ds.Tables[0].Rows[rows][col].ToString().Trim() == "")
{
number++;
}
//当空行的个数等于 列数 则获得当前行索引,
if (number == 11)
{
rowCount = rows;
break;
}
}
}
#endregion
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < 11; j++)
{
sqlCom = sqlCom + "'" + ds.Tables[0].Rows[i][j].ToString().Replace("'", "'") + "',";
}
sqlCom = sqlCom.Substring(0, sqlCom.Length - 1) + ");";
SqlCommand commAdd = new SqlCommand(sqlCom, conn);
commAdd.ExecuteNonQuery();
sqlCom = sqlCom1;
}
}
catch (Exception)
{
throw;
}
}