Click here to Skip to main content
12,633,623 members (33,084 online)
Click here to Skip to main content
Add your own
alternative version

Stats

1.3M views
24.1K downloads
225 bookmarked
Posted

Reading and Writing Excel using OLEDB

, 28 Oct 2004
Rate this:
Please Sign up or sign in to vote.
Shows how to use OLEDB to read from and write to Excel workbook files.

Application screenshot

Introduction

This project contains an ExcelReader class. This class reads Excel files by using the OLEDB driver. Many articles already have been written about it. This class however is an easy way to read and write Excel values. It's possible to read or write single values or data tables.

However, due to restrictions in the Excel driver, it is not possible to delete rows from a table. Updating a empty range is also not an option. It's possible to read an range and updating or inserting an existing range. Excel has his own way of datatatyping the column. DaberElay made a response according to my article with:

<TABLE width="60%" border=1

How does it happen?

Apparently, the engine reads the first 8 cells of each column and check it's data type. if most of the first 8 cells are int / double, the problem remains.

Is this solveable ? Yes and No. We can ask that the engine will check more than 8 cells ( setting the registry value

HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows to 0 which will check the first 16,000 rows, and holds a small performance hit ).

But if all your first 16000 rows are numeric and only then you have textual values, you are in a problem.

Another thing we can do is set the TypeGuessRows to 1,and set the connection string's extended property - HDR to No, so if you always have headers in your excel it will read the first row and decide that its a text field.

Notice however, that this means you will have to create the column names from the unnecessary extra first row you now have in your rows.

If the OLEDB solution does not fit your needs, you may buy a component for it. There are some components to read or wrrite the Excel files without MS Excel and are able to edit.

Here are some ideas.

Background

For a project, I needed to read and write MS Excel-files on a web server. The MS Excel file would be uploaded and be read on the server into a SQL Server database.

Normally, I would have used the XML grammar Microsoft has published for the web. Unfortunately, this is a grammar supported by MS Excel 2002 or higher. It makes it easier to make a component to modify and read Excel workbooks. Only in my projects, the clients always use older software like MS Excel '97 that do not support this XML. I also like to choose a solution which uses just one version of Excel 2002 with a programmed converter class. Only this process will run on a web server where MS Office and its components are not scalable and not allowed. Also read what Microsoft says about it. So, I started a class that uses the OLEDB driver that can do some primary tasks with the uploaded Excel file.

Using the code

The demo form uses the following code to initialize the ExcelReader class:

exr = new ExcelReader();
_dt = new DataTable("par");
exr.KeepConnectionOpen =true;
exr.ExcelFilename = _strExcelFilename;
exr.Headers =false;
exr.MixedData =true;
exr.SheetName = this.txtSheet.Text;
exr.SheetRange = this.txtRange.Text;
exr.SetPrimaryKey(0);
_dt = exr.GetTable();

First, create a new instance of this class. Also declare a DataTable. I prefer it to have it as a private class variable. After updating a grid, I will use the table variable to update the table with the ExcelReader class. The keepconnection open property keeps the connection open after an ExcelReader operation, and saves time. The header options mean, if there is a rowheader row in MS Excel to explain the columndata. The MixedData property uses the IMEX option (0=export, 1=import, 2=linked). By default, the property is true and IMEX =2. If false, there is no IMEX option in the connection string. Also set the sheetname and the range.

The primary key is needed to be able to update the Excel sheet. It now supports just one primary key, but the class can be extended. If the table has no primary key, the DataAdapter will not work. The Excel driver does not discover primary keys, so it must be set manually. The DataColumnNumber 0 is the first column of the range set. The GetTable() returns the data of the requested Excel range in a DataTable. Updating of the range in the Excel file itself can be done with the SetTable(DataTable) method. Just download the demo and take a look.

How it's done

First, set the connection:

private string ExcelConnection()
{
    return
        @"Provider=Microsoft.Jet.OLEDB.4.0;" + 
        @"Data Source=" + _strExcelFilename  + ";" + 
        @"Extended Properties=" + Convert.ToChar(34).ToString() + 
        @"Excel 8.0;"+ ExcelConnectionOptions() + Convert.ToChar(34).ToString(); 
}
#endregion

Open the connection:

_oleConn = new OleDbConnection(ExcelConnection());
_oleConn.Open();

And just make a OledbCommand to select with a text like select * from [sheetname$[range].

_oleCmdSelect =new OleDbCommand(
    @"SELECT * FROM [" 
    + _strSheetName 
    + "$" + _strSheetRange
    + "]", _oleConn);

Fill the table with the select command to retrieve the data actually:

OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
oleAdapter.SelectCommand = _oleCmdSelect;
DataTable dt = new DataTable(strTableName);
oleAdapter.FillSchema(dt,SchemaType.Source);
oleAdapter.Fill(dt);

Updating the table:

First set the primary key(s). Then call the update method to update the excel table. Try in the demo to update an empty range! An error will occur.
if (this._intPKCol>-1)
{
    int[] intPKCols = new int[]  { _intPKCol};
    _exr.PKCols = intPKCols;
}
_exr.SetTable(_dt); 

History

  • 1.1 Fixed some bugs and added some functions
    • The Excel sheetnames can be retrieved with a method call.
    • Functions to retrieve the real excel column names or the columnnumbers.
    • Fixed some bugs in connection string and SetSheetQueryAdapter.
  • 1.0 Initial ExcelReader class.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Dieder Timmerman
Web Developer
Netherlands Netherlands
I am Dieder Timmerman. I work as Senior Software Engineer at Ordina. I have MCSD.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralRe: Sheet Range Error Pin
Henrik Jørgensen15-Dec-08 0:36
memberHenrik Jørgensen15-Dec-08 0:36 
GeneralNull Values Returned with Mixed Data Types Pin
SMJHUNT6-Feb-07 11:49
memberSMJHUNT6-Feb-07 11:49 
GeneralRe: Null Values Returned with Mixed Data Types Pin
Johnny Glenn16-Apr-12 22:26
memberJohnny Glenn16-Apr-12 22:26 
GeneralTruncates at 255 characters Pin
sjgregory11-Jan-07 22:27
membersjgregory11-Jan-07 22:27 
GeneralRe: Truncates at 255 characters Pin
sjgregory29-Jan-07 0:19
membersjgregory29-Jan-07 0:19 
GeneralRe: Truncates at 255 characters [modified] Pin
tridy26-May-08 2:33
membertridy26-May-08 2:33 
QuestionCreating M$ Excel file using OleDB Pin
Slawomir3-Jan-07 1:00
memberSlawomir3-Jan-07 1:00 
GeneralExcellent Pin
Marcelo Calado19-Dec-06 8:29
memberMarcelo Calado19-Dec-06 8:29 
QuestionSetTable Pin
Ali Webster12-Dec-06 6:14
memberAli Webster12-Dec-06 6:14 
QuestionReading a single digit number from Excel file returns dbnull Pin
tua67112-Oct-06 10:28
membertua67112-Oct-06 10:28 
AnswerRe: Reading a single digit number from Excel file returns dbnull Pin
Kim Young Gi13-Apr-07 0:26
memberKim Young Gi13-Apr-07 0:26 
GeneralOLE DB Results are Dubious Pin
mcljava15-Mar-06 7:54
membermcljava15-Mar-06 7:54 
GeneralProblem with SaveData Pin
peterhat5-Mar-06 3:13
memberpeterhat5-Mar-06 3:13 
GeneralTo Read Active Sheet Pin
Steven Zhao17-Feb-06 4:58
memberSteven Zhao17-Feb-06 4:58 
GeneralBug - won't read column 72+ Pin
humptydumpty2831-Jan-06 12:08
memberhumptydumpty2831-Jan-06 12:08 
Generaldemo project Pin
gr47730-Nov-05 0:30
membergr47730-Nov-05 0:30 
GeneralAlternative way Pin
Jan Gex27-Sep-05 4:32
memberJan Gex27-Sep-05 4:32 
GeneralRe: Alternative way Pin
nsimeonov10-Nov-05 7:22
membernsimeonov10-Nov-05 7:22 
GeneralReading Excel using OLEDB_ error format Pin
vythu16-Sep-05 18:49
membervythu16-Sep-05 18:49 
GeneralThanks!! Pin
afhall9-Sep-05 9:00
memberafhall9-Sep-05 9:00 
GeneralReading data from excel sheet and copying it to SQL Server Pin
AshishKaistha25-Aug-05 8:32
memberAshishKaistha25-Aug-05 8:32 
GeneralRe: Reading data from excel sheet and copying it to SQL Server Pin
Navneet Kedar25-Aug-05 10:10
sussNavneet Kedar25-Aug-05 10:10 
GeneralRe: Reading data from excel sheet and copying it to SQL Server Pin
Lotharurs_swe12-Sep-05 2:41
memberLotharurs_swe12-Sep-05 2:41 
GeneralExternal table is not in the expected format. Pin
walkinator21-Aug-05 9:58
memberwalkinator21-Aug-05 9:58 
GeneralSelect Range bug Pin
Pär Sandgren2-Aug-05 4:59
sussPär Sandgren2-Aug-05 4:59 
GeneralRe: Select Range bug Pin
Prem Joy22-Apr-06 2:46
memberPrem Joy22-Apr-06 2:46 
GeneralRe: Select Range bug Pin
Kikoz6818-Aug-06 10:27
memberKikoz6818-Aug-06 10:27 
GeneralHelp Regarding Excel Files Pin
Wahaj Khan22-Jun-05 2:55
memberWahaj Khan22-Jun-05 2:55 
GeneralProblem with Excel Column Pin
R.Aravind26-May-05 21:00
memberR.Aravind26-May-05 21:00 
GeneralRe: Problem with Excel Column Pin
Sujo John6-Jun-06 10:14
memberSujo John6-Jun-06 10:14 
QuestionHow to write a string in one cell ??? Pin
AlexSchilcher19-May-05 9:45
memberAlexSchilcher19-May-05 9:45 
AnswerRe: How to write a string in one cell ??? Pin
xoxoxoxoxoxox18-Jan-06 16:21
memberxoxoxoxoxoxox18-Jan-06 16:21 
AnswerRe: How to write a string in one cell ??? Pin
hellspawnfr8-Nov-06 7:35
memberhellspawnfr8-Nov-06 7:35 
GeneralSQL query for Excel Sheets Pin
Abinash Patra6-Apr-05 21:25
sussAbinash Patra6-Apr-05 21:25 
GeneralRe: SQL query for Excel Sheets Pin
Darsin4-May-05 2:51
memberDarsin4-May-05 2:51 
Generalabout the column names Pin
newyork100312-Mar-05 15:20
membernewyork100312-Mar-05 15:20 
Generalit read wrong row Pin
newyork100310-Mar-05 10:53
membernewyork100310-Mar-05 10:53 
GeneralReading Excel Sheet Problem - GetExcelSheetNames() Pin
godfathers2-Mar-05 16:27
membergodfathers2-Mar-05 16:27 
GeneralRe: Reading Excel Sheet Problem - GetExcelSheetNames() Pin
newyork100311-Mar-05 12:11
membernewyork100311-Mar-05 12:11 
GeneralRe: Reading Excel Sheet Problem - GetExcelSheetNames() Pin
resn8r24-Feb-06 21:20
memberresn8r24-Feb-06 21:20 
GeneralRe: Reading Excel Sheet Problem - GetExcelSheetNames() Pin
alsan wong20-May-06 15:00
memberalsan wong20-May-06 15:00 
GeneralHeader Row Pin
bml3k1-Mar-05 8:41
memberbml3k1-Mar-05 8:41 
GeneralHeader Row Pin
bml3k1-Mar-05 7:45
sussbml3k1-Mar-05 7:45 
Generala problem about 'Dieder Timmerman' sample for reading Excel Pin
mkomasi23-Feb-05 0:44
membermkomasi23-Feb-05 0:44 
GeneralRe: a problem about 'Dieder Timmerman' sample for reading Excel Pin
resn8r24-Feb-06 23:49
memberresn8r24-Feb-06 23:49 
GeneralUnable to update spreadsheet Pin
MasterRacker27-Dec-04 6:58
memberMasterRacker27-Dec-04 6:58 
GeneralError while opening an excel file in c# Pin
praveennerd13-Dec-04 1:19
memberpraveennerd13-Dec-04 1:19 
GeneralHelp Needed -- Excel Ranges Pin
aLvInDaHouSe15-Nov-04 20:08
memberaLvInDaHouSe15-Nov-04 20:08 
GeneralRe: Help Needed -- Excel Ranges Pin
Dieder Timmerman21-Nov-04 9:09
memberDieder Timmerman21-Nov-04 9:09 
GeneralRe: Help Needed -- Excel Ranges Pin
Jeff Firestone3-May-05 13:21
memberJeff Firestone3-May-05 13:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.161208.2 | Last Updated 29 Oct 2004
Article Copyright 2004 by Dieder Timmerman
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid