DataSet 操作Excel导入sql

来源：互联网发布：数据的最小单位是( ) 编辑：程序博客网时间：2024/06/16 13:58

用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;            }        }