Excel導入DataTable（二）

    這種方法導入DataTable是利用 Excel.Range的Value2屬性。和導出Excel（一）是一個相反的過程。一般情況下不建議使用這種方法。

         /// <summary>

        /// Excel導入DataTable

        /// </summary>

        /// <param name="strFileName">檔案名稱</param>

        /// <param name="isHead">是否包含表頭</param>

        /// <param name="iSheet">Sheet</param>

        /// <param name="strErrorMessage">錯誤資訊</param>

        /// <param name="iRowsIndex">導入的Excel的開始行</param>

        /// <returns></returns>

         public static DataTable GetDataFromExcel(string strFileName, bool isHead,  int iSheet,

            ref string strErrorMessage,int iRowsIndex)

        {

            if (!strFileName.ToUpper().EndsWith(".XLS"))

            {

                strErrorMessage = "檔案類型與系統設定不一緻，請核對！";

                return null;

            }

            Excel.Application appExcel = new Excel.Application();

            Excel.Workbook workbookData;

            Excel.Worksheet worksheetData;

            workbookData = appExcel.Workbooks.Open(strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,

                     Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            worksheetData = (Excel.Worksheet)workbookData.Sheets[iSheet];

            Excel.Range xlRang = null;

            int iRowCount = worksheetData.UsedRange.Cells.Rows.Count;

            int iParstedRow = 0, iCurrSize = 0;

            int iEachSize = 1000;   // each time you

            int iColumnAccount = worksheetData.UsedRange.Cells.Columns.Count;

            int iHead = iRowsIndex;

            if (isHead)

                iHead = iRowsIndex+1;

            DataTable dt = new DataTable();

            for (int i = 1; i <= iColumnAccount; i++)

            {

                if (isHead)

                    dt.Columns.Add(worksheetData.get_Range(worksheetData.Cells[iRowsIndex, i], worksheetData.Cells[iRowsIndex, i]).Text.ToString());

                else

                    dt.Columns.Add("Columns" + i.ToString());

            }

            object[,] objVal = new object[iEachSize, iColumnAccount];

            try

            {

                iCurrSize = iEachSize;

                while (iParstedRow < iRowCount)

                {

                    if ((iRowCount - iParstedRow) < iEachSize)

                        iCurrSize = iRowCount - iParstedRow;

                    xlRang = worksheetData.get_Range("A" + ((int)(iParstedRow + iHead)).ToString(), ((char)('A' + iColumnAccount - 1)).ToString()

                        + (((int)(iParstedRow + iCurrSize + 1)).ToString()));

                    objVal = (object[,])xlRang.Value2;

                    int iLength = objVal.Length / iColumnAccount;

                    for (int i = 1; i < iLength; i++)

                    {

                        DataRow dr = dt.NewRow();

                        for (int j = 1; j <= iColumnAccount; j++)

                        {

                            if (objVal[i, j] != null)

                            {

                                dr[j - 1] = objVal[i, j].ToString();

                            }

                        }

                        dt.Rows.Add(dr);

                    }

                    iParstedRow = iParstedRow + iCurrSize;

                }

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang);

                xlRang = null;

            }

            catch (Exception ex)

            {

                appExcel.Quit();

                strErrorMessage = ex.Message;

                return null;

            }

            appExcel.Quit();

            return dt;

        }