Excel导入DataTable

///  <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  System.Data. DataTable  GetDataFromExcel(  string  strFileName,  bool  isHead,  int  iSheet,

            string  strErrorMessage,  int  iRowsIndex)

        {

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

            {

                strErrorMessage =  "文件类型与系统设定不一致，请核对！"  ;

                 return  null  ;

            }

 

            Microsoft.Office.Interop.Excel.  Application  appExcel =  new  Microsoft.Office.Interop.Excel. Application  ();

            Microsoft.Office.Interop.Excel.  Workbook  workbookData;

            Microsoft.Office.Interop.Excel.  Worksheet  worksheetData;

 

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

                     System.Reflection.  Missing .Value, System.Reflection. Missing  .Value, System.Reflection. Missing  .Value, System.Reflection. Missing .Value, System.Reflection.  Missing .Value, System.Reflection. Missing  .Value, System.Reflection. Missing .Value);

 

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

 

 

            Microsoft.Office.Interop.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;

 

            System.Data.  DataTable  dt =  new  System.Data. DataTable ();

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

            {

                 if  (isHead)

                    dt.Columns.Add(appExcel.Cells[iRowsIndex, i].FormulaLocal);

                 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;

 

        }

转载于:https://www.cnblogs.com/yixiaozi/p/3593597.html