• Excel连接字符串解析


            /// <summary>
            /// 导入Excel成DataTable
            /// </summary>
            /// <param name="filePath">文件路径 </param>
            /// <param name="version">Exl的版本(2003,2007)</param>
            /// <returns> </returns>
            public DataTable ExcelImportToDataTable(string filePath, string version)
            {
                DataTable dt = null;
                OleDbConnection Excelconn = null;
                LogHelper.WriteLog("初始化");
                if (version == "2003")
                {
                    Excelconn =
                      new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + @"';Extended Properties='Excel 8.0;HDR=NO;IMEX=1'" + @";");
                }
                else if (version == "2007")
                {
                    LogHelper.WriteLog("判断为OleDbConnection");
                    Excelconn =
                      new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + @"';Extended Properties='Excel 12.0;HDR=NO;IMEX=1'" + @";");

               //当 IMEX=0 时为“汇出模式”,这个模式开启的 Excel 档案只能用来做“写入”用途。
               //当 IMEX=1 时为“汇入模式”,这个模式开启的 Excel 档案只能用来做“读取”用途。
               //当 IMEX=2 时为“连結模式”,这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
                    LogHelper.WriteLog("判断为OleDbConnection实例化成功");
                }
                else
                {
                    throw new Exception("当前文件正在编辑,请关闭重试!");
                }

                //OleDbCommand cmd = null;
                //OleDbDataReader rdr = null;
                LogHelper.WriteLog("连接字符串初始化成功字符串的值:" + Excelconn.ConnectionString);

                try
                {
                    Excelconn.Open();
                    //cmd = Excelconn.CreateCommand();
                    LogHelper.WriteLog("打开成功");
                    dt = Excelconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    //string sheetName = "[" + dt.Rows[0]["TABLE_NAME"] + "]";  // TABLE_NAME 按照字母顺序排列 Rows[0]["TABLE_NAME"] 并不总是第一个sheet
                    string sheetName = "";
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (dt.Rows[i]["TABLE_NAME"].ToString() == "Sheet1$")
                        {
                            sheetName = "[Sheet1$]";
                            isDefaultSheetNameExist = true;
                            break;
                        }
                    }

                    if (!isDefaultSheetNameExist) throw new Exception("默认的Sheet1不存在,请勿修改默认的Sheet名!");
                    //cmd.CommandText = "SELECT * FROM " + sheetName + "";
                    //rdr = cmd.ExecuteReader();
                    string strSql = "SELECT * FROM " + sheetName + "";

                    LogHelper.WriteLog("sql语句:" + strSql + "\n");
                    OleDbDataAdapter da = new OleDbDataAdapter(strSql, Excelconn);
                    LogHelper.WriteLog("初始化适配器");
                    dt = new DataTable();
                    LogHelper.WriteLog("初始化表格");
                    da.Fill(dt);
                    LogHelper.WriteLog("成功返回dt!");
                    return dt;
                }
                catch (Exception etc)
                {
                    LogHelper.WriteLog("ADO.NET连接EXCEL获取其中的数据异常:", etc);
                    LogHelper.WriteLog(etc.Message + "\n");
                    if (!(etc.InnerException == null))
                        LogHelper.WriteLog("内部异常", etc.InnerException);
                    throw etc;
                }
                finally
                {
                    //rdr.Close();
                    Excelconn.Close();
                    LogHelper.WriteLog("关闭ADO.NET数据连接,释放连接资源");
                }
            }

        /// <summary>
            /// 根据 MIME type 来判断文件的类型
            /// </summary>
            /// <returns></returns>
            private static string getExcelType()
            {
                if (_filemimeType == "application/vnd.ms-excel")
                {
                    return "2003";
                }
                else if (_filemimeType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ||
                         _filemimeType == "application/x-zip-compressed")   // IE6 下Excel2007 为 application/x-zip-compressed
                {
                    return "2007";
                }
                else
                {
                    return "";
                }
            }

  • 相关阅读:
    zookeeper logs is missing zookeeper 日志丢失
    Java Spring IoC 学习(3)
    jsp中include的两种用法
    java多态
    Referenced file contains errors
    类的初始化顺序
    PHP处理session跨域
    Apache中按天分割日志(Windows)
    redis和memcached的区别(总结)
    PHP中的traits简单理解
  • 原文地址:https://www.cnblogs.com/zl253539819/p/2891513.html
Copyright © 2020-2023  润新知