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.

Ether · Accepted Answer · 2009-10-15 23:36:10Z

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

edited Oct 15, 2009 at 23:36

Ether

54.1k13 gold badges90 silver badges162 bronze badges

answered Sep 16, 2009 at 13:31

Christian

4,34225 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:10:52Z

4

Please see:

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Sep 15, 2009 at 12:09

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

2 Comments

idophir Over a year ago

Thanks but none of these solve my problem. I know how to connect to Excel using OleDB, but the data I get is incomplete since I cannot know the format type of each cell.

Anonymous Type Over a year ago

The OP is really asking to determine the cells format (refers to as style in the question), not how to connect via OLEDB. Although if you read the 4 links provided by Mitch this would also give you the answer.

Joe Erickson · Accepted Answer · 2009-09-16 13:20:15Z

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

Thanks Joe. I will definitely give your product a try and will report back.

JDunkerley · Accepted Answer · 2009-09-16 13:31:47Z

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

The question is asking for a way to determine the format of the cells using OLEDB, not how to use another method (such as com interop).

to StackOverflow · Accepted Answer · 2018-12-01 20:21:34Z

0

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.

answered Dec 1, 2018 at 20:21

to StackOverflow

125k34 gold badges212 silver badges345 bronze badges

Comments

Jeroen · Accepted Answer · 2025-01-17 18:21:53Z

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.

Collectives™ on Stack Overflow

Using .NET to get data from Excel as a database

6 Answers 6

Comments

2 Comments

1 Comment

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

1 Comment

1 Comment

Comments

Comments

Linked

Related