C#利用OLEDB實現(xiàn)將DataTable寫入Excel文件中

更新時間：2023年02月15日 10:14:58 作者：￡冷☆月№

這篇文章主要為大家詳細介紹了C#如何利用OLEDB實現(xiàn)將DataTable寫入Excel文件中，文中的示例代碼簡潔易懂，具有一定的借鑒價值，需要的可以參考一下

OLEDB

定義：

OLE DB（OLEDB）是微軟設計的通向不同的數(shù)據(jù)源的低級應用程序接口。OLE DB不僅包括微軟資助的標準數(shù)據(jù)接口開放數(shù)據(jù)庫連通性（ODBC）的結構化查詢語言（SQL）能力，還具有面向其他非SQL數(shù)據(jù)類型的通路。作為微軟的組件對象模型（COM）的一種設計，OLE DB是一組讀寫數(shù)據(jù)的方法（在過去可能被稱為渠道）。OLE DB中的對象主要包括數(shù)據(jù)源對象、階段對象、命令對象和行組對象。

優(yōu)缺點：

優(yōu)點：簡單快速,能夠操作高版本Excel

缺點：只能夠進行有限的操作(讀、寫)

應用

利用OLEDB將DataTable數(shù)據(jù)寫入Excel文件中，如果數(shù)據(jù)量過多，執(zhí)行效率很緩慢，大數(shù)據(jù)量不推薦使用此方法。

/// <summary>
        /// 創(chuàng)建DataTable
        /// </summary>
        /// <returns></returns>
        private DataTable CreateDataTable()
        {
            var dt = new DataTable();
            dt.Columns.Add("Field1");
            dt.Columns.Add("Field2"); 
            return dt;
        }

        /// <summary>
        /// 根據(jù)文件后綴名判斷Excel版本 鏈接字符串
        /// 參數(shù)HDR的值：
        /// HDR=Yes，這代表第一行是標題，不做為數(shù)據(jù)使用 ，如果用HDR=NO，則表示第一行不是標題，做為數(shù)據(jù)來使用。
        /// 參數(shù)IMEX的值：
        /// 當 IMEX = 0 時為“匯出模式”，這個模式開啟的 Excel 檔案只能用來做“寫入”用途。
        /// 當 IMEX = 1 時為“匯入模式”，這個模式開啟的 Excel 檔案只能用來做“讀取”用途。
        /// 當 IMEX = 2 時為“鏈接模式”，這個模式開啟的 Excel 檔案可同時支援“讀取”與“寫入”用途。
        /// </summary>
        /// <param name="filepath">文件目錄和文件名</param>
        /// <param name="pagesize">每頁記錄數(shù)</param>
        /// <returns></returns>
        public static string GetExcelConnStr(string filepath, out int pagesize)
        {
            StringBuilder sb = new StringBuilder();
            string extension = Path.GetExtension(filepath);
            if (extension == ".xlsx")
            {
                pagesize = 1048575; //實際行數(shù) 1048576
                sb.Append("Provider=Microsoft.Ace.OleDb.12.0;Data Source=");
                sb.Append(filepath);
                sb.Append(";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2'");
            }
            else
            {
                pagesize = 65535; //實際行數(shù) 65536
                sb.Append("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=");
                sb.Append(filepath);
                sb.Append(";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'");
            }
            return sb.ToString();
        }
        /// <summary>
        /// 使用OLEDB導出Excel
        /// Excel程序支持的文件類型不止一種。
        /// 在excel早期版本中，默認的工作薄擴展名為".xls"，這種格式的文件最多可以包含255個工作頁(Worksheet)，每個zhidao工作頁中包含65535行(Row)和256列(Column)。
        /// 自Office2007版本起，excel默認的工作薄擴展名為".xlsx"，這種格式的文件中每個工作頁包含1048576行(Row),16384列(Column)。
        /// </summary>
        /// <param name="dt">數(shù)據(jù)集</param>
        /// <param name="filepath">文件目錄和文件名</param>
        /// <param name="tablename">SHEET頁名稱</param>
        /// <param name="pagesize">每頁記錄數(shù)</param>
        public static void Export(DataTable dt, string filepath, string tablename, int pagesize = 0)
        {
            int pagecount = 0;
            string connString = GetExcelConnStr(filepath, out pagecount);
            if (pagesize > 0)
            {
                pagecount = pagesize;
            }

            try
            {
                using (OleDbConnection con = new OleDbConnection(connString))
                {
                    con.Open();

                    DataTable dtSheet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    var sheetCount = dtSheet.Rows.Count;

                    //總記錄數(shù)
                    var recordCount = dt.Rows.Count;
                    //列數(shù)
                    var columnCount = dt.Columns.Count;

                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = con;

                    //開始分頁
                    int page = (recordCount + pagecount - 1) / pagecount; //總頁數(shù)
                    for (int i = 0; i < page; i++)
                    {
                        //新的Sheet名稱
                        string tabname = tablename + (i + 1).ToString();

                        //獲取已存在的表
                        if (sheetCount > 0)
                        {
                            int m = 0;
                            foreach (DataRow dr in dtSheet.Rows)
                            {
                                if (m == i)
                                {
                                    tabname = dr["TABLE_NAME"].ToString();

                                    cmd.CommandText = "DROP TABLE [" + tabname + "]";
                                    cmd.ExecuteNonQuery();// 執(zhí)行創(chuàng)建sheet的語句
                                }
                                m++;
                            }
                        }

                        //建新sheet和表頭
                        StringBuilder createSQL = new StringBuilder();
                        createSQL.Append("CREATE TABLE ").Append("[" + tabname + "]"); //每60000項建一頁
                        createSQL.Append("(");
                        for (int j = 0; j < columnCount; j++)
                        {
                            createSQL.Append("[" + dt.Columns[j].ColumnName + "] text,");
                        }
                        createSQL = createSQL.Remove(createSQL.Length - 1, 1);
                        createSQL.Append(")");

                        cmd.CommandText = createSQL.ToString();
                        cmd.ExecuteNonQuery();


                        StringBuilder strfield = new StringBuilder();
                        for (int z = 0; z < columnCount; z++)
                        {
                            if (z > 0)
                            {
                                strfield.Append(",");
                            }
                            strfield.Append("[" + dt.Columns[z].ColumnName + "]");
                        }

                        //準備逐條插入數(shù)據(jù)
                        for (int j = i * pagecount; j < (i + 1) * pagecount; j++)
                        {
                            if (i == 0 || j < recordCount)
                            {
                                StringBuilder insertSQL = new StringBuilder();
                                StringBuilder strvalue = new StringBuilder();
                                for (int z = 0; z < columnCount; z++)
                                {
                                    if (z > 0)
                                    {
                                        strvalue.Append(",");
                                    }
                                    strvalue.Append("'" + dt.Rows[j][z].ToString() + "'");
                                }

                                insertSQL.Append(" insert into [" + tabname + "]( ")
                                        .Append(strfield.ToString())
                                        .Append(") values (").Append(strvalue).Append(") ");

                                cmd.CommandText = insertSQL.ToString();
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }

                    con.Close();
                }
            }
            catch (Exception ex)
            {
                GC.Collect();
            }
        }

具體使用方法：

DataTable dt = CreateDataTable();
            for (int i = 0; i < 70000; i++)
            {
                DataRow dr = dt.NewRow();
                dr[0] = i;
                dr[1] = "Value" + i.ToString();
                dt.Rows.Add(dr);
            }
            string filepath = Server.MapPath("~/") + "數(shù)據(jù)_" + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xls";
            Export(dt, filepath, "數(shù)據(jù)");

到此這篇關于C#利用OLEDB實現(xiàn)將DataTable寫入Excel文件中的文章就介紹到這了,更多相關C# OLEDB DataTable寫入Excel內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: