JAVA高级面试进阶训练营视频教程

admin

       最近由于工作上用到了读取Excel操作，完成过程中遇到了各种各样的问题，最后为了以后读取Excel时不再如此的麻烦，特意做成了一个小组件，方便日后使用。现在总结一下过程中遇到的问题及相应的解决办法。 

                                                                                                  一、Excel读取方式及问题

       第一种方式：通过OleDb连接，把excel文件作为数据源来读取。

       优点：读取的效率很快。

       缺点：1、读取过程中需要依赖于计算机安装的office版本，不同的版本读取过程中用到的代码会有所不同，这就需要在实现的过程中必须判断客户端机器安装的office版本（如果客户端未装任何版本的office软件，则程序将会报错）；

               2、读取Excel得到的工作表名称即Sheet名称会和Excel表格中实际的Sheet名称有所不一致 ，不过这并不妨碍程序的运行；

               3、如果Excel表格中存在这样的列：列中的数据类型不一致，比如说列中一些行为数字，一些行为字符串，那么将有一种类型的数据部分解析不出来，显示为空，产生这种问题的根源与Excel ISAM[3]（Indexed Sequential Access Method，即索引顺序存取方法）驱动程序的限制有关，Excel ISAM 驱动程序通过检查前几行中实际值确定一个 Excel 列的类型，然后选择能够代表其样本中大部分值的数据类型[4]。也即Excel ISAM查找某列前几行（默认情况下是8行），把占多的类型作为其处理类型。例如如果数字占多，那么其它含有字母等文本的数据项就会置空；相反如果文本居多，纯数字的数据项就会被置空。 若要修改默认情况下的8行，需要修改注册表值TypeGuessRows，TypeGuessRows 值决定了ISAM 驱动程序从前几条数据采样确定数据类型，默认为“8”。可以通过修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的该注册表值来更改采样行数。但是这种改进还是没有根本上解决问题。遇到这种情况时应当考虑不再使用这种读取Excel表格的方式。

        第二种方式：引用Com组件读取Excel文件。

        优点：无需考虑客户端机器安装的Office版本，读取的Sheet名称和Excel表格中完全一致，也不需要考虑Excel表格中列中的数据类型是否不一致，读取的数据十分准确。

      缺点：1、效率比较低，读取的过程耗时严重；

              2、需要做好内存的释放、Excel进程的关闭工作。

                                                                                               二、读取方式详解及问题解决办法总结 

      第一种方式：通过OleDb连接，把excel文件作为数据源来读取。

      1、方式详解：该方式主要的部分为两步：第一步，需要解析Excel表中的所有Sheet名称，如下面的代码，我将得到的所有Sheet名称放到了一个Lsit<string>集合集合中（_workSheets），并在代码段将该集合绑定到了下拉框cmbxWorkSheets;

string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + SourceExcelFile + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"" ;

OleDbConnection conn = new OleDbConnection(connString);
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
conn.Close();

foreach (DataRow row in dt.Rows)
     {
         string name = row["TABLE_NAME"].ToString();
         if (!name.Contains("$"))
             continue;

         _workSheets.Add(name);
     }

 

         第二步，根据选择的Sheet，读取excel表格中相应sheet名称的数据

string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + SourceExcelFile + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"" ;

string sql = string.Format("select * from [{0}]", cmbxWorkSheets.Text);
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet();

da.Fill(ds);
if (ds != null && ds.Tables.Count > 0)
       dt = ds.Tables[0];

这样，读取过程基本完成。
      2、方式问题及解决：

      问题1：在上面用到的连接字符串 string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + SourceExcelFile + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"" ，其中Provider=Microsoft.ACE.OLEDB.12.0，对于不同的offcie版本，Provider对应的值会有所不同，07及以上版本为Microsoft.ACE.OLEDB.12.0，而03版本的office却为Microsoft.Jet.OLEDB.4.0，因此需要判断客户端安装的office版本以确定Provider的值，而不能像上面那样直接写死。

  /// <summary>
        /// 判断Excel的版本号。
        /// </summary>
        /// <returns></returns>
        private static double CheckExcelVer()
        {
            Type objExcelType = Type.GetTypeFromProgID("Excel.Application");
            if (objExcelType == null)
            {
                return 0;
            }
            object objApp = Activator.CreateInstance(objExcelType);

            if (objApp == null)
            {
                return 0;
            }

            object objVer = objApp.GetType().InvokeMember("Version", BindingFlags.GetProperty, null, objApp, null);
            double iVer = Convert.ToDouble(objVer.ToString());

            objVer = null;
            objApp = null;
            objExcelType = null;

            GC.Collect();
            return iVer;
        }

        /// <summary>
        /// 根据Excel版本号获得Excel的版本名称。
        /// </summary>
        /// <returns></returns>
        public static String GetExcelVerStr()
        {
            String version;
            double excelver;
            excelver = CheckExcelVer();// ExistsExcelRegedit();
            version = "Office ";

            if (excelver == 0)
            {
                MessageBox.Show("无法识别Excel的版本", "错误", MessageBoxButtons.OK, MessageBoxIcon.Information);
                version = "无法识别 office 版本";
            }
            else if (excelver >= 14) version += "2010或以上";
            else if (excelver >= 12) version += "2007";
            else if (excelver >= 11) version += "2003";
            else if (excelver >= 10) version += "XP";
            else if (excelver >= 9) version += "2000";
            else if (excelver >= 8) version += "97";
            else if (excelver >= 7) version += "95";

            return version;
        }

        /// <summary>
        /// 根据Excel的版本名称获得Provider。
        /// </summary>
        /// <returns></returns>
        public static string GetExcelProvider()
        {
            string provider = string.Empty;

            string version = GetExcelVerStr();
            switch (version)
            {
                case "Office 2010或以上":
                case "Office 2007":
                    provider = "Microsoft.ACE.OLEDB.12.0";
                    break;
                case "Office 2003":
                case "Office 2000":
                case "Office XP":
                case "Office 97":
                case "Office 95":
                    provider = "Microsoft.Jet.OLEDB.4.0";
                    break;
            }

            return provider;
        }

这样就可以根据用户客户端安装的office版本来读取Excel，只要客户端安装了office解析Excel时便不会出错。

      问题二：Excel表格中某些列中数据类型不一致，导致列中后面的数据读取不出来。这种方式在读取Excel表的时候，无法定义和修改读取列的数据类型，string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + SourceExcelFile + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"" ，虽然“IMEX = 1”被认为是用来解决数字与字符混合时,识别不正常的情况，但其实它并未真正解决问题，表现为如果某字段前8条记录中全部为纯数字的话，那么在该字段随后的记录中含有字母或汉字的项将仍然变为空，但是如果该字段前8条记录中有一条不为纯数字，将能得到预期想要的结果，也就是说它其实只对前8行中不一致的数据类型起作用，8行之后的数据若出现数据不一致，便会出现为空的情况。此时，若要更好的解决问题，需要用第二种方式来读取Excel表。

        第二种方式：引用Com组件读取Excel文件。

      1、方式详解

      这种方式需要添加COM中的程序集Microsoft Excel 11.0 Object Library.dll,修改程序集中的属性“嵌入互操作类型”为False,不然会提示错误： 无法嵌入互操作类型“Microsoft.Office.Interop.Excel.ApplicationClass”。读取Excel的代码：

        private Microsoft.Office.Interop.Excel.ApplicationClass _excelApp;
        private Microsoft.Office.Interop.Excel._Workbook _workbook;
        private Microsoft.Office.Interop.Excel.Sheets _workSheets;

        _excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
        _excelApp.Visible = true;

        _workbook = _excelApp.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Microsoft.Office.Interop.Excel.WorkbookClass;
        _workSheets = _workbook.Sheets;

    
        private void ReadExcel()
        {
            Excel.Range selectedRange = _excelApp.Selection as Excel.Range;

            try
            {
                if (ResultTable != null)
                {
                    ResultTable.Clear();
                    ResultTable.Dispose();
                }

                ResultTable = new DataTable();

                string colName = string.Empty;
                if (IncludeHeader)
                {
                    for (int i = 1; i <= selectedRange.Columns.Count; i++)
                    {
                        Excel.Range cell = selectedRange[1, i] as Excel.Range;
                        if (cell != null && cell.Value != null)
                            colName = cell.Value.ToString().Trim();
                        else
                            colName = string.Empty;

                        if (string.IsNullOrEmpty(colName))
                            colName = "列 " + i.ToString();

                        if (ResultTable.Columns.Contains(colName))
                            throw new Exception(string.Format("导入数据存在重复的列：{0}。", colName));
                        ResultTable.Columns.Add(new DataColumn(colName));
                    }
                }
                else // 若不含标题行，则列名自动命名为“列 1”、“列 2”等。
                    for (int i = 1; i <= selectedRange.Columns.Count; i++)
                        ResultTable.Columns.Add(new DataColumn("列 " + i.ToString()));

                int firstDataLineNo = IncludeHeader ? 2 : 1;    // 若不含标题行，数据从第一行开始。
                int count = selectedRange.Rows.Count;
                for (int i = firstDataLineNo; i <= count; i++)
                {
                    //if (((Excel.Range)selectedRange[i, 1]).Value == null)
                    //    continue;

                    DataRow dr = ResultTable.NewRow();

                    for (int j = 0; j < selectedRange.Columns.Count; j++)
                        dr[j] = ((Excel.Range)selectedRange[i, j + 1]).Value;

                    ResultTable.Rows.Add(dr);

                    SetProgressValue(count - firstDataLineNo, i - firstDataLineNo);
                }
            }

            catch (Exception exp)
            {
                MessageBox.Show(this, string.Format("读Excel错误：{0}", exp.Message), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                _excelApp.Visible = true;
            }
        }
    

       方式问题及解决：

       这种方式效率很慢，而且需要做好善后工作。释放内存：

        private void DisposeExcelResource()
        {
            try
            {
                if (_workbook != null)
                    _workbook.Close(false, Missing.Value, false);
                if (_excelApp != null)
                    _excelApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(_excelApp);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_workbook);
                //System.Runtime.InteropServices.Marshal.ReleaseComObject( _selectedRange );

                GC.Collect();
            }
            catch
            {
            }
        }

      但上面这些做不到关闭打开的Excel文件，若需要做到关闭Excel文件：

        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out   int ID);

        public void BeforeClosing()
        {
            if (_excelApp != null)
            {
                IntPtr t = new IntPtr(_excelApp.Hwnd);
                int k = 0;
                GetWindowThreadProcessId(t, out k);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);

                p.Kill();
            }

        }

                                                                                                   三、总结
        花了一定的时间，工作上想实现的实现了，同时也将问题的解决总结了下来。这个过程自己有很大的收获，以后遇到问题时也一定要像现在这样将问题深入全面的了解并解决，多解决问题，多总结解决问题的过程，这样才能让自己寒酸的技术和表达能力一点一点的得到提高。