Excel导入DataTable

很多时候，我们需要操作Excel中的数据。而要操作Excel中的数据，首先要解决的问题就是把Excel中的数据导入到程序的某个对象中，比如DataTable等。

      这里介绍一种常用的Excel导入DataTable的方法。Excel其实也是一种数据库，既然是数据库，就可以用一般的操作数据库的方法来做处理。

         /// <summary>
        /// 包含表头的连接
        /// </summary>
        private  const string EXCELCONNECTIN = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';";

        /// <summary>
        /// 不包含表头的连接
        /// </summary>
        private  const string EXCELCONNECTINNOHDR = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=NO;IMEX=1;';";


        /// <summary>
        /// 导入Excel数据到DataTable
        /// </summary>
        /// <param name="strFileName">文件名称</param>
        /// <param name="isHead">是否包含表头</param>

        /// <param name="iSheet">要导入的sheet</param>
        /// <returns>datatable</returns>
        public static DataTable GetDataFromExcel(string strFileName, bool isHead,int iSheet)
        {
            if (!strFileName.ToUpper().EndsWith(".XLS"))
            {
                return null;
            }

            DataTable dtReturn = new DataTable();
            string strConnection = string.Empty;
            if (isHead)
            {
                strConnection = EXCELCONNECTIN + "Data Source=" + strFileName + ";";
            }
            else
                strConnection = EXCELCONNECTINNOHDR + "Data Source=" + strFileName + ";";

            OleDbConnection connection = new OleDbConnection(strConnection);
            connection.Open();
            try
            {
                string str = "Select * from [Sheet" + iSheet + "$]";
                OleDbDataAdapter adapter = new OleDbDataAdapter(str, connection);
                adapter.Fill(dtReturn);

            }
            catch(Exception ex)
            {
                dtReturn = null;
            }
            finally
            {
                connection.Close();
            }

            return dtReturn;
        }

     这种方法导入DataTable有两个不足：

      一、在前面介绍了两种导入数据到Excel的方法，对于第二种以写文件的形式写入Excel的时候，曾经说过这种方式导出的Excel在导入的时候会有问题。如果你的Excel是通过写文件的形式导出Excel，再通过该方法导入到DataTable的时候，发现程序会报一个错误提示“外部表不是预期的格式。”。解决这个错误的方法是将该Excel另存为XLS的文件。

 

     二、曾经遇到过Excel的某列前面部分是数字，后面部分是字符。通过这种方法导入的时候发现后面字符部分的数据并没有导入。调试后发现，导入的时候，系统会根据Excel里面的内容为DataTable的列设置类型。前面是数字，这一列就是整型的了，后面的字符当然导不进去了。

 

    将在下面介绍另一种导入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;

        }