TODO:
Have you ever wanted to convert an Excel SpreadSheet to a DataSet?
SOLUTION:
public bool ConvertXLSToDataset(ref DataSet InDataSet, string InFileName, bool ProcessAllSheets)
{
DataSet currentSheetDS = null;
DataTable spreadsheets = null;
string TableName = "";
try
{
InDataSet = new DataSet();
string ConnectionString = String.Format("Data Source={0};HDR=true", InFileName);
//now see if it has an xlsx extension
if (InFileName.ToLower().Contains(".xlsx"))
ConnectionString = String.Format("{0};Format=xlsx", ConnectionString);
//get the connection stuff
using (ExcelConnection excelConnection = new ExcelConnection(ConnectionString))
{
excelConnection.Open();
spreadsheets = excelConnection.GetExcelSchemaTable(ExcelConnection.ExcelClientSchemaType.Tables);
//make sure we have rows
if (spreadsheets.Rows != null)
{
foreach (DataRow datarow in spreadsheets.Rows)
{
currentSheetDS = new DataSet();
TableName = datarow["TABLE_NAME"].ToString();
//Create an ExcelCommand to specify from which spreadsheets inside of the workbook to query data
using (ExcelCommand excelCommand = new ExcelCommand(TableName, excelConnection))
{
excelCommand.CommandType = CommandType.TableDirect; //It is always TableDirect
//Create an ExceldataAdapter to retrieve data into the DataSet
using (ExcelDataAdapter loDataAdapter = new ExcelDataAdapter(excelCommand))
{
//Get data
loDataAdapter.Fill(currentSheetDS);
InDataSet.Merge(currentSheetDS);
}
}//using
//if we are not processing all sheets, then break out so we do the first
if (!ProcessAllSheets)
break;
}//foreach
}//if
}//using
return true;
}
catch (Exception x)
{
return false;
}
}//method
NOTES:
You will need the VM.xPort.ExcelClient dll. You can buy it here.