[C#] Excel to DataTable by Microsoft.Office.Interop.Excel

  • 2119
  • 0

摘要:[C#] Excel to DataTable by Microsoft.Office.Interop.Excel


public DataTable getExcel(string strPath, out string strMsg)
        {
            try
            {
                //Excel to Array[][]
                Excel.Application xlsApp = new Excel.Application();
                xlsApp.Visible = true;
                Excel.Workbook xlsBook = xlsApp.Workbooks.Open(txtFile.Text);
                Excel.Worksheet xlsSheet = xlsBook.ActiveSheet;
                Excel.Range xlsRangeFirstCell = xlsSheet.get_Range("A1");
                Excel.Range xlsRangeLastCell = xlsSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
                Excel.Range xlsRange = xlsSheet.get_Range(xlsRangeFirstCell, xlsRangeLastCell);
                object[,] objValue = xlsRange.Value2 as object[,];

                //Array[][] to DataTable 
                long lngColumnCount = objValue.GetLongLength(1);
                long lngRowCount = objValue.GetLongLength(0);
                DataTable dtExcel = new DataTable();
                for (int j = 1; j <= lngColumnCount; j++)
                {
                    dtExcel.Columns.Add(objValue[1, j].ToString());
                }
                for (int i = 1; i <= lngRowCount; i++)
                {
                    DataRow drRow = dtExcel.NewRow();
                    for (int j = 1; j <= lngColumnCount; j++)
                    {
                        drRow[j - 1] = objValue[i, j].ToString();
                    }
                    dtExcel.Rows.Add(drRow);
                }

                xlsBook.Close();
                xlsApp.Quit();
                strMsg = "";
                return dtExcel;
            }
            catch (Exception ex)
            {
                strMsg = ex.Message;
                return null;
            }
        }