OLE 读取excel文件

最新推荐文章于 2021-03-17 00:08:10 发布

tulasgao

最新推荐文章于 2021-03-17 00:08:10 发布

阅读量990

点赞数

分类专栏： .net 文章标签： ole excel 讀取

本文链接：https://blog.csdn.net/tulasgao/article/details/12949461

版权

.net 专栏收录该内容

33 篇文章 0 订阅

订阅专栏

OLEDB讀取存在一個問題，當某列前8行為數字，后面的行為文本，那讀取時會把文本也當成數字，導致讀不出來.....

/// <summary>
        /// 轉換Excel資料至DataSet
        /// </summary>
        /// <param name="filePath">Excel檔案路徑</param>
        /// <param name="sheetNames">Excel Sheet Name</param>
        /// <param name="firstRowIsHead">第一列資料是否為標頭</param>
        /// <returns>轉換Excel資料至DataSet</returns>
        public static DataSet ExcelToDataSet(string filePath, string[] sheetNames, bool firstRowIsHead)
        {
            DataSet dsData = new DataSet();

            if (sheetNames != null && sheetNames.Length > 0)
            {
                OleDbConnection cn = null;
                OleDbDataAdapter adapter = null;

                try
                {
                    FileInfo file = new FileInfo(filePath);

                    if (file.Exists && (file.Extension == ".xls" || file.Extension == ".xlsx"))
                    {
                        //string connectionString = String.Format(
                        //    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1};IMEX=1'",
                        //    filePath, firstRowIsHead ? "YES" : "NO");
                        string connectionString = String.Format(
                            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'",
                            filePath, firstRowIsHead ? "YES" : "NO");

                        using (cn = new OleDbConnection(connectionString))
                        {
                            cn.Open();

// 取得所有Excel Sheet Name
List<string> excelSheetNames = GetSheetName(cn);

                            if (excelSheetNames.Count > 0)
                            {
                                OleDbCommand cmd = cn.CreateCommand();

                                // 若沒指定sheet Name時, 預設為第一個Sheet Name
                                if (sheetNames.Length == 1 && sheetNames[0].Length == 0)
                                {
                                    sheetNames[0] = excelSheetNames[0].Replace("$", "");
                                }

                                using (adapter = new OleDbDataAdapter())
                                {
                                    foreach (string sheetName in sheetNames)
                                    {
                                        // 檢查是否有該Sheet Name
                                        if (excelSheetNames.Contains(String.Format("{0}$", sheetName.ToUpper())))
                                        {
                                            cmd.CommandText =
                                                String.Format("SELECT * FROM [{0}$]", sheetName);

adapter.SelectCommand = cmd;

DataTable dt = new DataTable(sheetName);

adapter.Fill(dt);

                                            dsData.Tables.Add(dt);
                                        }
                                    }
                                }
                            }

                            cn.Close();
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw;
                }
                finally
                {
                    if (adapter != null)
                    {
                        adapter.Dispose();
                    }

                    if (cn != null)
                    {
                        cn.Close();
                        cn.Dispose();
                    }
                }
            }

return dsData;
}

        /// <summary>
        /// 取得Excel所有的Sheet Name
        /// </summary>
        /// <param name="cn">OleDbConnection object</param>
        /// <returns>Excel所有Sheet Name</returns>
        private static List<string> GetSheetName(OleDbConnection cn)
        {
            List<string> sheetNames = new List<string>();

            // 查詢Excel所有Sheet Name
            DataTable dtExcel =
                cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dtExcel != null && dtExcel.Rows.Count > 0)
            {
                foreach (DataRow row in dtExcel.Rows)
                {
                    sheetNames.Add(row["TABLE_NAME"].ToString().Replace("'", "").ToUpper());
                }
            }

return sheetNames;
}