How to Import from Excel to a Dataset Using Microsoft.Office.Interop.Excel

How do I import from Excel to a DataSet using Microsoft.Office.Interop.Excel?

What about using Excel Data Reader (previously hosted here) an open source project on codeplex? Its works really well for me to export data from excel sheets.

The sample code given on the link specified:

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

//5. Data Reader methods
while (excelReader.Read())
{
//excelReader.GetInt32(0);
}

//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

UPDATE

After some search around, I came across this article: Faster MS Excel Reading using Office Interop Assemblies. The article only uses Office Interop Assemblies to read data from a given Excel Sheet. The source code is of the project is there too. I guess this article can be a starting point on what you trying to achieve. See if that helps

UPDATE 2

The code below takes an excel workbook and reads all values found, for each excel worksheet inside the excel workbook.

private static void TestExcel()
    {
        ApplicationClass app = new ApplicationClass();
        Workbook book = null;
        Range range = null;

        try
        {
            app.Visible = false;
            app.ScreenUpdating = false;
            app.DisplayAlerts = false;

            string execPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);

            book = app.Workbooks.Open(@"C:\data.xls", Missing.Value, Missing.Value, Missing.Value
                                              , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                             , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                            , Missing.Value, Missing.Value, Missing.Value);
            foreach (Worksheet sheet in book.Worksheets)
            {

                Console.WriteLine(@"Values for Sheet "+sheet.Index);

                // get a range to work with
                range = sheet.get_Range("A1", Missing.Value);
                // get the end of values to the right (will stop at the first empty cell)
                range = range.get_End(XlDirection.xlToRight);
                // get the end of values toward the bottom, looking in the last column (will stop at first empty cell)
                range = range.get_End(XlDirection.xlDown);

                // get the address of the bottom, right cell
                string downAddress = range.get_Address(
                    false, false, XlReferenceStyle.xlA1,
                    Type.Missing, Type.Missing);

                // Get the range, then values from a1
                range = sheet.get_Range("A1", downAddress);
                object[,] values = (object[,]) range.Value2;

                // View the values
                Console.Write("\t");
                Console.WriteLine();
                for (int i = 1; i <= values.GetLength(0); i++)
                {
                    for (int j = 1; j <= values.GetLength(1); j++)
                    {
                        Console.Write("{0}\t", values[i, j]);
                    }
                    Console.WriteLine();
                }
            }

        }
        catch (Exception e)
        {
            Console.WriteLine(e);
        }
        finally
        {
            range = null;
            if (book != null)
                book.Close(false, Missing.Value, Missing.Value);
            book = null;
            if (app != null)
                app.Quit();
            app = null;
        }
    }

In the above code, values[i, j] is the value that you need to be added to the dataset. i denotes the row, whereas, j denotes the column.

Convert excel to datatable in c#?

You can use below function which will convert excel to datatable. You just need to pass excel path to this function and this function will return the datatable:

public DataTable READExcel(string path)
    {
        Microsoft.Office.Interop.Excel.Application objXL = null;
        Microsoft.Office.Interop.Excel.Workbook objWB = null;
        objXL = new Microsoft.Office.Interop.Excel.Application();
        objWB = objXL.Workbooks.Open(path);
        Microsoft.Office.Interop.Excel.Worksheet objSHT = objWB.Worksheets[1];

        int rows = objSHT.UsedRange.Rows.Count;
        int cols = objSHT.UsedRange.Columns.Count;
        DataTable dt = new DataTable();
        int noofrow = 1;

        for (int c = 1; c <= cols; c++)
        {
            string colname = objSHT.Cells[1, c].Text;
            dt.Columns.Add(colname);
            noofrow = 2;
        }

        for (int r = noofrow; r <= rows; r++)
        {
            DataRow dr = dt.NewRow();
            for (int c = 1; c <= cols; c++)
            {
                dr[c - 1] = objSHT.Cells[r, c].Text;
            }

            dt.Rows.Add(dr);
        }

        objWB.Close();
        objXL.Quit();
        return dt;
    }

How to import all the Excel sheets to DataSet in C#

This is a code I came up with and it works perfect but I saw someone else already added an answer:

static DataSet Parse(string fileName)
{
    string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);

    DataSet data = new DataSet();

    foreach(var sheetName in GetExcelSheetNames(connectionString))
    {
        using (OleDbConnection con = new OleDbConnection(connectionString))
        {    
            var dataTable = new DataTable();
            string query = string.Format("SELECT * FROM [{0}]", sheetName);
            con.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
            adapter.Fill(dataTable);
            data.Tables.Add(dataTable);
        }
    }

    return data;
}

static string[] GetExcelSheetNames(string connectionString)
{
        OleDbConnection con = null;
        DataTable dt = null;
        con= new OleDbConnection(connectionString);
        con.Open();
        dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if (dt == null)
        {
            return null;
        }

        String[] excelSheetNames = new String[dt.Rows.Count];
        int i = 0;

        foreach (DataRow row in dt.Rows)
        {
            excelSheetNames[i] = row["TABLE_NAME"].ToString();
            i++;
        }

        return excelSheetNames;
}

Exporting dataset to xlsx with Microsoft.Office.Interop.Excel

This apparently is a bug which was fixed in .NET 4.0 (source): if the regional setting of Excel is different from the regional setting of the PC, you will get this error.

From Microsoft:

You receive this error calling an Excel method when the following conditions are true:

-The method requires an LCID (locale identifier).

-You run an English version of Excel. However, the regional settings for the computer are configured for a non-English language.

If the client computer runs the English version of Excel and the locale for the current user is configured for a language other than English, Excel will try to locate the language pack for the configured language. If the language pack is not found, the error is reported.

To work around this problem, you can use one of the following methods:

-Install the Multilingual User Interface Pack for your version of Office.

-Execute the Excel method or property by using InvokeMember so that you can specify the CultureInfo for the call. For example, the following code illustrates how you can invoke the Workbooks object Add method with "en-US" as the CultureInfo

-Or, set the CultureInfo prior to calling the Excel method.

see this link for code examples of the suggested workarounds: http://support.microsoft.com/kb/320369

---

Related Topics