public static DataSet ExtractData(string path)
{
try
{
DataSet Data
= new DataSet
(Path
.GetFileNameWithoutExtension(path
)); string ConnectionString = string.Format(
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=No;IMEX=1\";",
path);
using (var Connection
= new OleDbConnection
(ConnectionString
)) {
Connection.Open();
var Command
= new OleDbCommand
("", Connection
); // Get all Sheets in Excel File
DataTable Sheets = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// Loop through all Sheets to get data
foreach (DataRow Row in Sheets.Rows)
{
string SheetName = Row["TABLE_NAME"].ToString();
if (!SheetName.EndsWith("$"))
continue;
// Get all rows from the Sheet
Command.CommandText = "SELECT * FROM [" + SheetName + "]";
DataTable Sheet
= new DataTable
(); Sheet.TableName = SheetName;
OleDbDataAdapter adapter
= new OleDbDataAdapter
(Command
); adapter.Fill(Sheet);
Data.Tables.Add(Sheet);
}
Connection.Close();
return Data;
}
}
catch (System.Exception e)
{
throw e.InnerException;
}
}