Excep Import

最新推荐文章于 2025-12-12 12:12:20 发布

原创最新推荐文章于 2025-12-12 12:12:20 发布 · 687 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#import #dataset #excel #string #path #properties

#region 导入Excel

        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="Path">带文件名称的Excel路径</param>
        /// <returns>返回一个数据集</returns>
        public static DataSet ImportExcelToDataSet(string Path)
        {
            DataSet ds = new DataSet();
            try
            {
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";

                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    Workbook workbook = GetExcelObj(Path);
                    conn.Open();
                    foreach (object workbookNew in workbook.Sheets)
                    {
                        Worksheet woorksheet = (Worksheet)workbookNew;//(Worksheet)workbook.ActiveSheet;

                        string strExcel = "select * from [" + woorksheet.Name + "$]";
                        OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
                        myCommand.Fill(ds, woorksheet.Name);
                    }
                }
            }
            finally
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            return ds;
        }

        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="Path">带文件名称的Excel路径</param>
        /// <returns>返回一个数据表</returns>
        public static System.Data.DataTable ImportExcelToDataTable(string Path)
        {
            System.Data.DataTable dtSource = new System.Data.DataTable();
            try
            {
                string tableName = "";
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    Workbook workbook = GetExcelObj(Path);
                    Worksheet woorksheet = (Worksheet)workbook.ActiveSheet;
                    tableName = woorksheet.Name;
                    workbook.Close(false, null, false);

                    conn.Open();
                    string strExcel = "select * from [" + tableName + "$]";
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
                    DataSet ds = new DataSet();
                    myCommand.Fill(ds, tableName);
                    dtSource = ds.Tables[tableName];
                }
            }
            catch (OleDbException olex)
            {
                throw new Exception("该Excel文件正在使用中...未能覆盖,请先关闭目标文件,重新导入!");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            return dtSource;
        }

#region 私用方法

        /// <summary>
        /// 获取Excel对象
        /// </summary>
        private static Workbook GetExcelObj(string Path)
        {
            ApplicationClass excelApp = new ApplicationClass();
            Workbook workbook = excelApp.Workbooks.Open(Path, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            return workbook;
        }

#endregion