Reading and Writing Excel using OLEDB

• Download demo project - 12 Kb

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:

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.

• Aspose Excel & Word
• SoftArtisan's Officewriter component

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.

You must Sign In to use this message board.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 25 of 83 (Total in Forum: 83) (Refresh) FirstPrevNext

Inserting takes too much time adamshuv 2:16 30 Apr '09   Hi! I have an application which saves its data in an excel sheet. There are about 4000 rows to add to the sheet, each containing 14 columns. Each insertion is done by creating a new instance of an OleDbCommand object, setting the connection (which is already open) to it, setting the command text and performing ExecuteNonQuery. My problem is that the whole process takes more than 1 minute, which is too much. I have identified that the reason for slowing the whole thing down is in the following code that actually does the insertion (most probably in the execution itself of the query): System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand(); objCmd.Connection = _oleConn; objCmd.CommandText = sql; // A preset string objCmd.ExecuteNonQuery(); the 'sql' variable is pre-set to a string that has the following format more or less (the column and sheet names were replaced with generic names): "Insert into [Worksheetname$A1:R65536] ([Index], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8], [Column9], [Column10], [Column11], [Column12], [Column13], [Column14]) values ('II54959', 'II54970', 'NTI295', 'TTI347', 'TI226', 1569, 1, 'Tx', '', '10840', 'NO', 'NA', 'UK', 'NO', '')" I didn't write this code myself, and I don't know why each time the INSERT is done to Row 65536. I guess that it automatically pushes it to the last row. Maybe this is the cause of the bad performance?! Any suggestions? Thanks for the help while (i == live) { have(fun); } Sign In·View Thread·PermaLink 2.00/5 (1 vote) data retrieve from Excel viki246 18:19 27 Apr '09   Hi, I am doing oledb connection with excel from my c# application. I am writting query to select some coulmn. Query is like this. Select [Fund],[statement$ Details], [Currency Hit$] From Sheet1 $ A1:N30 Instead of coulmn name, can i give the coulmn name ??? Ex:- Select A1, K1,P1,N1 From Sheet1 $ A1:N30 I don't want to give coulmn name because it has special characters. Sign In·View Thread·PermaLink Read Formula mrtom84 12:57 18 Nov '07   Is it possible to Read the Formula behind the Cell? Like =A1+A2 ??? If yes, how can i do this? I already implemented the Code, and it works! but i need the Formula behind the Cells. Can anybody Help me? Sign In·View Thread·PermaLink 1.50/5 (2 votes) Range Problem Large Sheet Name rmoreirao 5:34 24 Oct '07   Hi All, Im working with excel and ado.net now. I faced the following problem : Whenever I try to open the sheet "CLEAVEDB62305offer_0529163742Ge" , it opens with no problem. But when I try to set a range for it, like "CLEAVEDB62305offer_0529163742Ge$A1:B1", Jet shows me an error telling me it cannot be found. Can anyone help me? Its killing me.. =/ plz, mail me : rmoreirao@ixlink.com Rodrigo Goes Moreirao Sign In·View Thread·PermaLink 2.00/5 (1 vote) Good Job Yasin HINISLIOGLU 5:01 24 Jul '07   Congrat man. Good job Sign In·View Thread·PermaLink 1.00/5 (1 vote) Fixed BUG in 'GetExcelSheetNames' Cabbi 3:11 18 May '07   Yet another BUG in this class: when a sheet name has spaces the name returns within ''. So here is the fix: // Add the sheet name to the string array. foreach(DataRow row in dt.Rows) { string strSheetTableName = row["TABLE_NAME"].ToString(); strSheetTableName = strSheetTableName.Trim(new char[]{'\''}); excelSheets[i] = strSheetTableName.Substring(0,strSheetTableName.Length-1); i++; } Enjoy, Cabbi Sign In·View Thread·PermaLink 1.00/5 (1 vote) Fixed BUG in 'SetValue' Cabbi 22:00 17 May '07   The 'SetValue' method had a BUG by setting string values. So here my little contribution to this class nice but BUGGY: private bool SetSheetQuerySingelValUpdate(object value) { try { if (m_Conn == null) { throw new Exception("Connection is unassigned or closed."); } if (m_SheetName.Length ==0) throw new Exception("Sheetname was not assigned."); m_CmdUpdate = new OleDbCommand(@"Update ["+m_SheetName+"$"+m_SheetRange+"] set F1=@value", m_Conn); OleDbParameter dbParam = m_CmdUpdate.CreateParameter(); dbParam.DbType = DbType.Object; dbParam.ParameterName = "@value"; dbParam.Value = value; m_CmdUpdate.Parameters.Add(dbParam); return true; } catch (Exception ex) { throw ex; } } Enjoy, Cabbi Sign In·View Thread·PermaLink 2.00/5 (1 vote) how to populate column in this Ravi shuk 4:00 11 Apr '07   can anyone please help me out with the code in this project:( Sign In·View Thread·PermaLink 1.50/5 (2 votes) help me plzzz Ravi shuk 3:15 10 Apr '07   plzzz help me reagrding no of columns in excel..:( Sign In·View Thread·PermaLink 1.00/5 (1 vote) problem with no of columns in excel Ravi shuk 3:13 10 Apr '07   I have my Excel sheet which has 15 column n some has 10..i don't know how to read with respect to no of columns required and read the excel file respectively..I'm reading excel file with three columns only and its not taking more than three columns. can anyone plz help me with codes..coz m really new to it. Plz can anyone help me out. Thanks Ravi Sign In·View Thread·PermaLink 2.00/5 (1 vote) problems with no of columns in Excel Ravi shuk 21:51 9 Apr '07   I have my Excel sheet which has 15 column n some has 10..i don't know how to read with respect to no of columns required and read the excel file respectively..I'm reading excel file with three columns only and its not taking more than three columns. can anyone plz help me with codes..coz m really new to it. Plz can anyone help me out. Thanks Ravi Sign In·View Thread·PermaLink 2.00/5 (1 vote) SetSheetQueryAdapter bugs YI Tan 18:34 12 Mar '07   In SetSheetQueryAdapter method: Line 303: for (int iPK=0;iPK{ strWhere = AddWithAnd(strWhere,dt.Columns[iPK].ColumnName + "=?"); } Should be changed to: for (int iPK=0;iPK{ strWhere = AddWithAnd(strWhere,dt.Columns[PKCols[iPK]].ColumnName + "=?"); } Line 337: for (int iPK=0;iPK{ oleParUpd = new OleDbParameter("?",dt.Columns[iPK].DataType.ToString()); oleParUpd.SourceColumn =dt.Columns[iPK].ColumnName; oleParUpd.SourceVersion = DataRowVersion.Original; oleda.UpdateCommand.Parameters.Add(oleParUpd); } Should be changed to: for (int iPK=0;iPK{ oleParUpd = new OleDbParameter("?",dt.Columns[PKCols[iPK]].DataType.ToString()); oleParUpd.SourceColumn =dt.Columns[PKCols[iPK]].ColumnName; oleParUpd.SourceVersion = DataRowVersion.Original; oleda.UpdateCommand.Parameters.Add(oleParUpd); } Sign In·View Thread·PermaLink Sheet Range Error YI Tan 17:20 12 Mar '07   If I set SheetRange "A10:C15", then I get an error when call GetTable(); I found this error is due to the row number "10" which is greater than 1 digit, stemming from the following code: string FirstCol = _strSheetRange.Substring(0,_strSheetRange.IndexOf(":")-1); in GetTable(string strTableName) method, which gets a wrong column name "A1". Sign In·View Thread·PermaLink 1.00/5 (1 vote) Re: Sheet Range Error Henrik Jørgensen 0:36 15 Dec '08   It's basically because the input start field is expected to be within the first nine lines of the spreadsheet. This should be more stable: // pick out the column name from the input int intIlen =0; for (int intI = 0; intI < _strSheetRange.Substring(0, _strSheetRange.IndexOf(":")).Length; intI++) { // we read until we find a non-alfabetic value, i.e a number if (Char.IsLetter(_strSheetRange[intI])) intIlen = intI; else break; } string FirstCol = _strSheetRange.Substring(0,intIlen+1); Sign In·View Thread·PermaLink Null Values Returned with Mixed Data Types SMJHUNT 11:49 6 Feb '07   FYI I had a strange problem where the first column of the spreadsheet I was reading contained document heading numbers (e.g. 1.1, 1.2.8, etc.) Entries like 1.2.8 would be returned as is but entries like 1.1 would return null. Apparently, the OLEDB driver decided 1.1 was a number and 1.2.8 was a string and only wanted to return strings. I tried setting the MixedData property but this did not solve the problem. Upon further investigation, I determined that the a value of IMEX=1 solves the problem whereas setting MixedData property to true sets IMEX=2. Sign In·View Thread·PermaLink 2.40/5 (5 votes) Truncates at 255 characters sjgregory 22:27 11 Jan '07   I've done something similar in my application (wish I'd found this sample before - it would have saved me a lot of work). I'm getting a problem where the text being read from each cell is truncated at 255 characters - but only on certain PCs. I have tried this sample project and it too suffers the same problem. Has anyone else experienced this, and do you know the solution? Sign In·View Thread·PermaLink 2.00/5 (2 votes) Re: Truncates at 255 characters sjgregory 0:19 29 Jan '07   OK, I've found the answer: HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows was set to 1. I've changed it to a larger value and it now works. See http://support.microsoft.com/kb/281517[^] Sign In·View Thread·PermaLink Re: Truncates at 255 characters [modified] tridy 2:33 26 May '08   You might consider using ODBC instead of OLEDB which will eliminate the problem of 255 symbols. In my case I am not able to access every customer registry and I might not even have right for that. OdbcConnection con = new OdbcConnection(@"Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\temp\testbook.xls"); OdbcCommand cmd = new OdbcCommand(@"SELECT * FROM [Workbook1$]"); cmd.Connection = con; OdbcDataAdapter da = new OdbcDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); At least this solved my problem with importing XLS files with fields with more than 255 symbols. gl & hf [tridy] Stockholm, Sweden modified on Monday, May 26, 2008 7:46 AM Sign In·View Thread·PermaLink 2.00/5 (1 vote) Creating M$ Excel file using OleDB Slawomir 1:00 3 Jan '07   Is there any way to create a new file using this data access technology ? It's great idea to be able to read and write excel files without having excel installed, but how to create excel file ? big thanx for any responce Slawomir Sign In·View Thread·PermaLink 1.42/5 (7 votes) Excellent Marcelo Calado 8:29 19 Dec '06   Congratulations for this article, fantastic! Need develop a Professional Software with low cost? Contact me! Sign In·View Thread·PermaLink 1.00/5 (1 vote) SetTable Ali Webster 6:14 12 Dec '06   Very useful utility - thanks for your article. After successfully reading in the data, I'm trying to build a new datatable and export all data to a named worksheet. I've tried updating using the SetTable method, which does work in a fashion, however every time I run the utility it adds a number of blank rows (z) before inserting the records (interestingly as many blank rows as is in the dataset). The second time I try = 2 * z blank rows ; 3rd time I try 3* z blank rows etc etc heres my code snippet for exporting ... _dtPrev is what gets sucked in using GetTable() ... DataTable dtTest = _dtPrev.Clone(); exr = new ExcelReader(); exr.KeepConnectionOpen = false; exr.ExcelFilename = "C:\\temp\\a.xls"; exr.Headers = false; exr.MixedData = true; exr.SheetName = "TASK"; exr.SheetRange = "A1:U65535"; for (int i = 0; i < _dtPrev.Rows.Count; i++) { dtTest.ImportRow(_dtPrev.Rows[i]); dtTest.Rows[i].SetAdded(); } int[] intPKCols = new int[] { 0 }; exr.PKCols = intPKCols; exr.SetTable(dtTest); Any ideas would be greatly appreciated Sign In·View Thread·PermaLink 1.67/5 (3 votes) Reading a single digit number from Excel file returns dbnull tua671 10:28 12 Oct '06   Using the demo, I read from an excel file. The problem is if the row has a numeric value it is returning me a db null. If I change the type of the column from General to Text that it returns the value if the value is atleast 2 digits longs. If the value is 1 digit it still returns null. It absolutely isn't making any sense to me since I am doing a select * on the sheet. Is there something wrong with the demo? As a matter fact, when you look at the introduction, it seems like even single digit numbers are read withno problem. Something fishy going on. Please help! Sign In·View Thread·PermaLink 2.25/5 (4 votes) Re: Reading a single digit number from Excel file returns dbnull Kim Young Gi 0:26 13 Apr '07   Change Source code like : private string ExcelConnectionOptions() { string strOpts = ""; if (this.MixedData == true) strOpts += "Imex=2;"; if (this.Headers == true) strOpts += "HDR=Yes;"; else strOpts += "HDR=No;"; return strOpts; } to : private string ExcelConnectionOptions() { string strOpts = ""; if (this.MixedData == true) strOpts += "Imex=1;"; if (this.Headers == true) strOpts += "HDR=Yes;"; else strOpts += "HDR=No;"; return strOpts; } Reference : http://msdn2.microsoft.com/en-us/library/ms254500.aspx[^] Sign In·View Thread·PermaLink OLE DB Results are Dubious mcljava 7:54 15 Mar '06   It appears that if the Excel sheet you are reading has errors in the cells, the JET ISAM driver does not handle the data but instead swaps in a NULL for the cells it doesn't like. I have seen a number of similar problems like this from other users and I suspect it is related to a limitation. After searching MSDN and the Internet I have found no way to tell the OleDbDataAdapter to simply return the data verbatim (as-is). If anyone know hows to do this, please email me at mike_luster@hotmail.com. This could not be much simpler, I only wish that it worked! string query = string.Format( "SELECT * FROM [{0}${1}]", tableName, range ); _oleCmdSelect = new OleDbCommand( query, _oleCon ); //Fill table OleDbDataAdapter oleAdapter = new OleDbDataAdapter( ); oleAdapter.SelectCommand = _oleCmdSelect; DataSet ds = new DataSet( ); int n = oleAdapter.Fill( ds ); Note I can accomplish reading the spreadsheet no problem if I use the Excel Object Model but that's a lot more code to maintain so unless I have to go that route I'd rather avoid it. Mike Luster CTI/IVR/Telephony SME Sign In·View Thread·PermaLink 3.38/5 (5 votes) Problem with SaveData peterhat 3:13 5 Mar '06   Hi Unless I select a small range for the Range: parameter, when I click on SaveData I get an error message: "These columns don't currently have unique values". I have put in all the fixes described on this forum, and I have the column names correctly displayed as they are on the Excel worksheet. Regardless of which column I use for Primary Key Col, I get the same result. Also, I don't understand how the primary key works. Can someone please explain it to me - like why it's needed. UPDATE: I've now figured out the primary key bit, and I'm no longer getting the above error - however I'm now getting a "One or more required parameters do not have values" error. -- modified at 2:51 Tuesday 7th March, 2006 Sign In·View Thread·PermaLink 1.40/5 (5 votes)

Last Visit: 13:06 6 Jul '09     Last Update: 13:06 6 Jul '09 1234 Next »