關於Excel 不同版本在C#導入時的應用

 前段時間,在進行Excel導入程序編寫時,就使用網上一些人寫的方法,直接進行導入. 代碼如下:

 

        public KPISExcel(string fileName)
        {
            try {
                //Start Excel and get Application object.
                oXL = new Excel.Application();//Microsoft.Office.Interop.Excel.Application();
                oXL.Visible = false;
               
                //Get a new workbook.
                //Open a excel file ,which contain bom data, and do not update links ,and readonly.
                oWB = oXL.Workbooks.Open(fileName, 0, true,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing);

                oSheet = (_Worksheet)oWB.Sheets[1];
            } catch (Exception e) {
                throw e;
            }
        }

 

本來以為,基本沒有沒有任何問題. 程序正式通過測試,並且上線. 後來,一些業務人員不斷的反映它的Excel無法導入,都是使用Excel 97-2003的版本的. 想了半天,無法比較出到底有什麼差別. 後來,仔細比較後,發現文件大小不相同. 同樣的數據的Excel,得到的大小確是不相同,真是讓我鬱悶死了.  後來,使用UltraEdit進行比較,的確是有些不相同.

 

後來,在網上查詢資料時發現,Excel 2000與Excel 2003存儲的資料大小不相同,而且使用導入時,使用的Excel 2003時,調用的Open的參數是15位. 而使用Excel 2000時,使用的是13位. 因此,出現這樣的狀況.

 

程序更改如下:

 

      public KPISExcel(string fileName)
        {
            try {
                //Start Excel and get Application object.
                oXL = new Excel.Application();//Microsoft.Office.Interop.Excel.Application();
                oXL.Visible = false;
               
                //Get a new workbook.
                //Open a excel file ,which contain bom data, and do not update links ,and readonly.
                //oWB = oXL.Workbooks.Open(fileName, 0, true,
                //    Type.Missing, Type.Missing, Type.Missing,
                //    Type.Missing, Type.Missing, Type.Missing,
                //    Type.Missing, Type.Missing, Type.Missing,
                //    Type.Missing, Type.Missing, Type.Missing);

                //上面的方法只支持Excel2003
                //下面的方法可以支持Excel2000與Excel2003
                oWB = oXL.Workbooks._Open(fileName, 0, true,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing);

                oSheet = (_Worksheet)oWB.Sheets[1];
            } catch (Exception e) {
                close();
                throw e;
            }
        }