public DataTable ReadExcelToTable(string filepath) { string excelfilePath = Server.MapPath("Excel/Excel.xls"); Excel.Application myExcel = new Excel.ApplicationClass(); Excel.Workbooks myBooks = myExcel.Application.Workbooks; object oMissing = System.Reflection.Missing.Value; Excel.Workbook myBook = myBooks.Open(excelfilePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); //Excel.Workbook myBook = myExcel.Workbooks[1]; int sheetint = myBook.Worksheets.Count;//能得到sheet的数量 Excel.Worksheet mySheet = (Excel.Worksheet)myBook.Worksheets[1]; int rowsint = mySheet.UsedRange.Cells.Rows.Count; //得到行数 int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数 System.Data.DataTable dt = new System.Data.DataTable("mytable"); for (int i = 1; i < columnsint; i++) { dt.Columns.Add("F" + i.ToString(), System.Type.GetType("System.String")); } DataSet myDs = new DataSet(); myDs.Tables.Add(dt); DataRow myRow; myDs.Clear(); for (int i = 2; i < rowsint; i++) //第一行为标题,不读取 { myRow = myDs.Tables["mytable"].NewRow(); for (int j = 1; j < columnsint; j++) { Excel.Range r = (Excel.Range)mySheet.Cells[i, j]; string strValue = r.Text.ToString(); string aa = strValue; string columnname = "F" + j.ToString(); myRow[columnname] = strValue; } myDs.Tables["mytable"].Rows.Add(myRow); } return myDs.Tables["mytable"]; }
转载于:https://my.oschina.net/FG8RzS9pB95/blog/11417