Excel导入DataTable（二）

来源：互联网发布：淘宝店铺装修工具免费编辑：程序博客网时间：2024/05/12 17:12

这种方法导入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;

        }