private static DataSet GetExcelAsDataSet(string fileName, bool firstRowIsHeader) { OleDbConnection excelConnection = null; OleDbDataAdapter adapter = null; DataSet ds = new DataSet(); System.Data.DataTable dtTables = new System.Data.DataTable(); //fileName = "C:\\myfolder\\UserInformationList.xlsx"; string myconnection1 = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=YES;'"; using (OleDbConnection Connection1 = new OleDbConnection(myconnection1)) { Connection1.Open(); //to get the schema of the workbook. dtTables = excelConnection.GetSchema(); //get the tables in the workbook dtTables = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); String[] excelSheets = null; if ((dtTables != null)) { excelSheets = new String[dtTables.Rows.Count]; int i = 0; // Add the sheet name to the string array. foreach (DataRow row in dtTables.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i++; } } //prepare dataset from the tables in the workbook foreach (string sheet in excelSheets) { OleDbCommand cmd = new OleDbCommand(); cmd.Connection = excelConnection; cmd.CommandText = "Select * from [" + sheet + "]"; System.Data.DataTable dtItems = new System.Data.DataTable(); string SheetName = sheet.Trim('\''); SheetName = SheetName.Substring(0, SheetName.IndexOf("$")); dtItems.TableName = SheetName; if (!ds.Tables.Contains(SheetName)) { adapter = new OleDbDataAdapter(); adapter.SelectCommand = cmd; adapter.Fill(dtItems); ds.Tables.Add(dtItems); } } } return ds; }
<identity impersonate="true" /> to <identity impersonate="false" />
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)