CODEWITHSUNDEEP
.netexceloledb
idophir
idophir

Reputation: 212

Using .NET to get data from Excel as a database

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.

Upvotes: 3

Views: 5530

Answers (6)

Francis B.
Francis B.

Reputation: 7208

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();
    da.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.

Upvotes: 0

to StackOverflow
to StackOverflow

Reputation: 124696

You can use IMEX=1 in the connection string to force all columns to be read as text, rather than having the OleDB attempt to infer the data type.

I believe this will respect formatting of the cells containing numeric values, but will leave it to you to test and confirm.

Upvotes: 0

Christian
Christian

Reputation: 4342

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

Upvotes: 5

JDunkerley
JDunkerley

Reputation: 12495

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();

Upvotes: 0

Joe Erickson
Joe Erickson

Reputation: 7217

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

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300529

Please see:

Upvotes: 4

Related Questions