Using .NET to get data from Excel as a database

Question

I would like get data from Excel files using .NET The Excel file is being used so I can't open it. What I do is connect to it as a datasource using OleDB.

The problem is that I get a cell's data but not its style. A date value in Excel changes into a datetime format in .NET and a cell set as currency in Excel is showing as an integer in .NET. When exporting the data from Excel using OleDB you don't know if the cell contains a date, currency, etc. For example, if the cell was set as text and contained five digits starting with zero, you will just get four digits.

I'm looking for a way to get the data as it would appear if you'd view the data in Excel.

Using .NET 3.5 and Excel 2007.

Answer 1

Please see:

• Reading Excel files from C#
• recommend a tutorial to read Excel file in C#
• How to read data of an excel file using c# ?
• Suggestions for reading data from excel in .net c#
• C# 2.0 Fastest way to parse Excel spreadsheet

Answer 2

To connect to an excel file you need the appropriate connection string:

string connString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=<YourExcelPath>;Extended Properties=\"Excel 12.0;HDR=YES;\"";

After use the OleDb classes to query the information from the file:

string selectCmd = "SELECT * FROM <SheetName>";

using(OleDbConnection excelConn = new OleDbConnection(connString))
{
    excelConn.Open(); 
    OleDbCommand command = new OleDbCommand(selectCmd, excelConn);
    OleDbDataAdapter da = new OleDbDataAdapter(command);

    DataTable sheetInfo = new DataTable();
    dataAdapter.Fill(sheetInfo);

    //Do something with the data.
}

So you need to replace "YourExcelPath" with the path of your excel file and "SheetName" with the name of the sheet where you want to retrieve data.

Answer 3

SpreadsheetGear for .NET can open an Excel 2007 xlsx workbook while Excel 2007 has the workbook open.

Excel stores dates, times, currency, numbers, etc... as numbers (C# doubles). The number format informs Excel how to display the number. Excel and SpreadsheetGear have a property to return the formatted text - Range.Text in Excel and IRange.Text in SpreadsheetGear.

SpreadsheetGear also has a property which tells you the type of the number format of a cell so you can determine whether the cell is formatted as a date, currency, etc... if that is important to your application. See the IRange.NumberFormatType property for more information.

You can download a free trial here if you want to try it out.

Disclaimer: I own SpreadsheetGear LLC

Answer 4

It isnt pretty, but you could use COM Interop onto the Excel application to read the sheet.

This would allow you to access anything you like about the cells, but is a lot more prone to error.

        System.Reflection.Missing ms = System.Reflection.Missing.Value;
        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        xlApp.Visible = true;
        Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Open(@"C:\Demo.xlsx", ms, ms, ms, ms, ms, ms, ms, ms, ms, ms, ms, ms, ms, ms);
        Microsoft.Office.Interop.Excel.Worksheet xlSheet = xlBook.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
        Microsoft.Office.Interop.Excel.Range rng = xlSheet.Cells[1, 1] as Microsoft.Office.Interop.Excel.Range;
        System.Console.WriteLine(String.Format("{0} {1} {2}", rng.Value2, rng.Text, rng.NumberFormat));
        xlBook.Close(false, ms, ms);
        xlApp.Quit();

        System.Console.ReadLine();

Answer 5

In order to find out the data type of the columns, you have to use the FillSchema method:

OleDbConnection conn = new OleDbConnection(...);
conn.Open();

DataSet dataSet = new DataSet();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT * FROM <table>", conn);
dataAdapter.FillSchema(dataSet, SchemaType.Source);
dataAdapter.Fill(dataSet);

//Now you can access the data type like this
dataSet.Tables[0].Columns[0].DataType