Excel_To_DataTable

/// <summary>
        /// Read data in excel file to datatable
        /// </summary>
        /// <param name="filename">Excel file name</param>
        /// <param name="sheetNum">Which sheet to read</param>
        /// <returns></returns>
        public System.Data.DataTable GetExcelTable(string filename, int sheetNum)
        {
            Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();
            object missing = Missing.Value;
            Workbook myBook = myExcel.Application.Workbooks.Open(filename, missing, missing, missing,
             missing, missing, missing, missing, missing, missing,
             missing, missing, missing, missing, missing);  //open excel file

            if (myBook != null)
            {
                myExcel.Visible = false;

                Worksheet mySheet = (Worksheet)myBook.Worksheets[sheetNum];
                System.Data.DataTable dt = new System.Data.DataTable();

                for (int j = 1; j <= mySheet.Cells.CurrentRegion.Columns.Count; j++)
                    dt.Columns.Add();

                for (int i = 1; i <= mySheet.Cells.CurrentRegion.Rows.Count; i++)
                {
                    DataRow myRow = dt.NewRow();

                    for (int j = 1; j <= mySheet.Cells.CurrentRegion.Columns.Count; j++)
                    {
                        Microsoft.Office.Interop.Excel.Range temp = (Microsoft.Office.Interop.Excel.Range)mySheet.Cells[i, j];
                        string strValue = temp.Text.ToString();
                        myRow[j - 1] = strValue;
                    }
                    dt.Rows.Add(myRow);
                }
                myExcel.Quit();

                //Kill excel process
                System.Diagnostics.Process[] myProcesses = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                foreach (System.Diagnostics.Process instance in myProcesses)
                {
                    instance.Kill();
                }

                return dt;
            }

            return null;
        }

 

转载于:https://www.cnblogs.com/johnsmith/p/3180006.html