C# 通过 oledb 操作Excel

最新推荐文章于 2024-05-10 11:40:09 发布

转载最新推荐文章于 2024-05-10 11:40:09 发布 · 128 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/lizikang2013/p/6930833.html

本文介绍了一种使用C#语言通过OLE DB连接器读写Excel文件的方法，包括创建表格、插入更新数据及读取Excel中所有工作表的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

public string GetConnectionString()
        {
            Dictionary<string, string> props = new Dictionary<string, string>();
 
            // XLSX - Excel 2007, 2010, 2012, 2013
            props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
            props["Extended Properties"] = "Excel 12.0 XML";
            props["Data Source"] = @"C:\tools\MyExcel.xlsx";
 
            // XLS - Excel 2003 and Older
            //props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
            //props["Extended Properties"] = "Excel 8.0";
            //props["Data Source"] = "C:\\MyExcel.xls";
 
            var sb = new StringBuilder();
 
            foreach (KeyValuePair<string, string> prop in props)
            {
                sb.Append(prop.Key);
                sb.Append('=');
                sb.Append(prop.Value);
                sb.Append(';');
            }
 
            return sb.ToString();
        }
 
        public void WriteExcelFile()
        {
            string connectionString = GetConnectionString();
 
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
 
                cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";
                cmd.ExecuteNonQuery();
 
                cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');";
                cmd.ExecuteNonQuery();
 
                cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(2, 'BBBB','2014-01-03');";
                cmd.ExecuteNonQuery();
 
                cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(3, 'CCCC','2014-01-03');";
                cmd.ExecuteNonQuery();
 
                cmd.CommandText = "UPDATE [table1] SET name = 'DDDD' WHERE id = 3;";
                cmd.ExecuteNonQuery();
 
                conn.Close();
            }
        }
 
        public DataSet ReadExcelFile()
        {
            DataSet ds = new DataSet();
 
            string connectionString = GetConnectionString();
 
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
 
                // Get all Sheets in Excel File
                DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
 
                // Loop through all Sheets to get data
                foreach (DataRow dr in dtSheet.Rows)
                {
                    string sheetName = dr["TABLE_NAME"].ToString();
 
                    if (!sheetName.EndsWith("$"))
                        continue;
 
                    // Get all rows from the Sheet
                    cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
 
                    DataTable dt = new DataTable();
                    dt.TableName = sheetName;
 
                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    da.Fill(dt);
 
                    ds.Tables.Add(dt);
                }
 
                cmd = null;
                conn.Close();
            }
 
            return ds;
        }

转载于:https://www.cnblogs.com/lizikang2013/p/6930833.html