Excel读取

 

private static void ToDataBase(DataSet ds, string dbTableName)
        {
            string connectionString = "server=.;database=...;uid=sa;pwd=;";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    string values = string.Empty;
                    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    {
                        if (i != ds.Tables[0].Columns.Count - 1)
                        {
                            values += "'" + dr[i].ToString().Replace("'", "''") + "'" + ",";
                        }
                        else
                        {
                            values += "'" + dr[i].ToString().Replace("'", "''") + "'";
                        }
                    }
                    string sql = "insert into [" + dbTableName + "] values(" + values + ")";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }

     

        private static DataSet ImportExcel(string strFileName)
        {
            if (strFileName.Length < 0) return null;


　　//HDR有两个值:yes和no.默认是yes,表示第一行是字段名.no表示第一行不是字段名. 
　　//IMEX=1:解决数字与字符混合时,识别不正常的情况.
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();

            IList<string> tblNames = new List<string>();
            foreach (DataRow dr in dtSchema.Rows)
            {
                tblNames.Add((string)dr["TABLE_NAME"]);
            }

            OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + tblNames[0] + "]", strConn);

            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message.ToString());
                System.Console.ReadLine();
            }

            return ds;
        }