How To Convert An Excel Spreadsheet To A DataSet



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.