Working with MS Excel(xls / xlsx) Using MDAC and Oledb

• Sample Tool in C# with XLSX (Office 2007) support - 24.99 KB
• Sample Tool in C# - 24.61 KB
• Sample Tool in VB.NET with XLSX (Office 2007) Support - 26.88 KB
• Sample Tool for VB.NET - 26.28 KB 
• Sample CreateWorkBook download in ASP.NET - 21.62 KB 

Contents

1. Introduction
2. Available Ways to work with Excel Workbooks 
3. Background 
4. Working with Excel Workbook
   1. Anatomy of ConnectionString 
   2. Creating Excel Workbook 
   3. Getting Schema Defination 
   4. Retrieve Data By Worksheet Name 
   5. Retrieve Data Using Range 
   6. Manipulating Data (Insert / Update / Delete) 
   7. Drop Excel Worksheet 
5. Description and Usage of Sample Tool 
6. Code Explanation and Usage Info 
7. History

Introduction   

Hi Folks, It’s long since I wrote my last article. Meanwhile, I came across with a lot of stuffs, and want to share with you one by one. This article is regarding all we need to work with Excel through our programs. 

 

While searching in google with this topic I came across some of the links, but none of them could make you a clear and concise idea of how to work with data in Excel in the most easiest way from .NET.  So I decided to jot down everything that may appear with this topic in this article.   

 

 

To work with Excel workbooks, you can do through 3 different ways :  

1. You need 3^rd party library which acts as an interface between your program and the Excel.

2. You can make use of Excel InterOp Objects, but this requires you to have Excel installed in the development environment. This is a binding if you are going to make a product which is to be distributed.

3. You can use OleDb data providers for Excel which comes for free with Windows. But there is one limitation though, you can access only data using this technique. You cannot do formatting through this technique.

4. You can use XML to create excel objects which will open in MSExcel correctly. This is easier, just you need to work with xml through programming. It also supports xml stylesheets. I will also try to discuss this in another article, for the time being you may look into ExcelXMLDemo  . 

 

In this topic I am going to discuss about the 3^rd method which is the most common one that we use while working with Excel.   

Background 

Excel is the most common and popular format of showing data to the client nowadays. After the most common one (PDF) you need to place another format which may show the reports to the client. Excel would be the right choice for that. Now we often come up with a requirement to generate the data in an Excel Workbook. Recently while developing, I have got one requirement to dump some data in excel sheet. Thus I thought of writing this one.

Another important requirement is to read data from MS Excel 2007 format, which is also an unusual task to learn the entire structure of Excel 2007 objects. Using MDac one can easily work with both of them without changing any of the code whatsoever.

Working with Excel workbook

The rows and columns of Excel workbook closely resembles the rows and columns of a database table. We can use MDac (Microsoft Data Access Tool) that comes free with windows update to work with excel worksheet. In case of Excel Workbooks, each worksheet acts as a table and each workbook is actually a database. You can create, insert drop excel objects through OleDb data clients from your program.

Now let us look how the connectionstring will look like : 

Normal ConnectionString : (work for xls files)

Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;\""

Office 2007 ConnectionString : (work for xlsx files)

Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;\""

Here Data Source will be placed with a proper filename like C:\\test.xls or C:\\test.xlsx. If you want to create a workbook just place the one that is not existing and use Create Table to create a workbook.

The connectionstring has some parts :
1. Provider : It is the main oledb provider that is used to open the excel sheet. This will be Microsoft.Jet.OLEDB.4.0 for Excel 97 onwards Excel file format and Microsoft.ACE.OLEDB.12.0 for Excel 2007 or higher Excel file format (One with xlsx extension)

2. Data Source : It is the entire path of the excel workbook. You need to mention a dospath that corresponds to an excel file. Thus it will look like  : Data Source=C:\\testApp.xls".

3. Extended Properties (Optional) : Extended properties can be applied to Excel workbooks which may change the overall activity of the excel workbook from your program. The most common one are the following :

• HDR : It represents Header of the fields in the excel table. Default is YES. If you dont have fieldnames in the header of your worksheet, you can specify HDR=NO which will take the columns of the tables that it finds as f1,f2 etc.
• ReadOnly : You can also open excel workbook in readonly mode by specifying ReadOnly=true; By Default Readonly attribute is false, so you can modify data within your workbook.
• FirstRowHasNames : It is the same as HDR, it is always set to 1 ( which means true) you can specify it as false if you dont have your header row. If HDR is YES provider disregards this property. You can change the default behaviour of your environment by changing the Registry Value [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\FirstRowHasNames] to 00 (which is false)  
• MaxScanRows : Excel does not provide the detailed schema defination of the tables it finds. It need to scan the rows before deciding the data types of the fields. MaxScanRows specifies the number of cells to be scanned before deciding the data type of the column. By default the value of this is 8. You can specify any value from 1 - 16 for 1 to 16 rows. You can also make the value to 0 so that it searches all existing rows before deciding the data type. You can change the default behaviour of this property by changing the value of [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows] which is 8 by default.  Currently MaxScanRows is ignored, so you need only to depend on TypeGuessRows Registry value. Hope Microsoft fixes this issue to its later versions.
• IMEX : (A Caution) As mentioned above excel have to guess a number or rows to select the most appropriate data type of the column, a serious problem may occur of you have mixed data in one column. Say you have data of both integer and text on a single column, in that case excel will choose its data type based on majority of the data. Thus it selects the data for the majority data type that is selected, and returns NULL for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.
  For example,  In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.   
  To work around this problem for data, set "IMEX=1" in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting.  You can change the enforcement of type by changing [HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes] to numeric as well. 

  Thus if you look into the simple connectionstring with all of them, it will look like:

  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\testexcel.xls;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;MAXSCANROWS=15;READONLY=FALSE\""
  
  or

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\testexcel.xlsx;Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;MAXSCANROWS=15;READONLY=FALSE\""
  
  We need to place extended properties into Quotes(") as there are multiple no of values.

Can we create Excel Workbook through this technique? 

If you are eager to know if we can create excel workbook directly through OleDB, your answer is yes. The only thing that you need to do is to specify a non-existing file in the Data Source of the connectionstring.  

string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;
                          Data Source=c:\\testexcel.xls;
                          Extended Properties\"Excel 8.0;HDR=YES\"";
string createTableScript = "CREATE TABLE newTable(a1 MEMO,a2 INT,a3 CHAR(255))";
using(conObj = new OleDbConnection(connectionstring))
{
   using (OleDbCommand cmd = new OleDbCommand(createTableScript, conObj)
   {
     if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
     cmd.ExecuteNonQuery();
   }
}

This will create a new workbook with one worksheet if the datasource file (testexcel.xls) is not existing in the location.

To Retrieve Schema Information of Excel Workbook : 

You can get the worksheets that are present in the excel workbook using GetOleDbSchemaTable. Use the following snippet.

DataTable dtSchema = null;
dtSchema = conObj.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

Here dtSchema will hold the list of all workbooks. Say we have two workbooks : wb1, wb2. The above code will return a list of wb1, wb1$,wb2,wb2$. We need to filter out $ elements.

Selecting Data From a WorkBook (Specifying Range)  

You can run simple query to select Data from an excel workbook. Say your workbook contains tables like w1,w2. Now If write SELECT * FROM [w1] or SELECT * FROM 'w1' , it will return you the whole datatable with all the data.

You can also specify the Range of selection, just write query like :

SELECT * FROM [w1$A10:B10]

Thus it will select the data only from excel Cell A10 : B10 Range.

string cmdText = "SELECT * FROM [w1$A10:B10]";
using(OleDbCommand cmd = new OleDbCommand(cmdText))
{
      cmd.Connection = this.Connection;
      OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
      DataSet ds = new DataSet();
      adpt.Fill(ds,"w1");
}

NOTE :  

A caution about specifying worksheets: The provider assumes that your table of data begins with the upper-most, left-most, non-blank cell on the specified worksheet. In other words, your table of data can begin in Row 3, Column C without a problem. However, you cannot, for example, type a worksheeet title above and to the left of the data in cell A1.

A caution about specifying ranges: When you specify a worksheet as your source, the provider adds new records below existing records in the worksheet as space allows. When you specify a range (named or unnamed), Jet also adds new records below the existing records in the range as space allows. However, if you requery on the original range, the resulting recordset does not include the newly added records outside the range. Using MDAC you cannot add new rows beyond the defined limits of the range, otherwise you will receive Exception: "Cannot expand named range"

Runing DML Statement: 

You can run any DML statement like the same way you do for other databases. Samples :

INSERT INTO [w1] VALUES('firsttextcol', 2, '4/11/2009', '10:20');
[We assume First Column is either memo or Char field, 2nd col is int, 3rd is Date, 4th is Time data type]

DELETE FROM [w1] Where secondintcol=2;

UPDATE [w1] SET secondintcol = 3 where firsttextcol = 'firsttextcol';

We can use [] (Square brackets) to allow spaces within columnnames and tablenames as we do for databases.

Droping Excel WorkSheet

To drop an excel worksheet, just use  

Drop Table [w1]

This will drop the worksheet.

If this is the last worksheet, it will not delete the workbook file. You need to do it yourself.

Using the sample Tool : 

I have added one sample application that demonstrates the problem. It includes one class  called ExcelObject which allows you to work with excel. You can use the code to work in your own application easily.

1. Choose Browse and select a xls file. If you want to create the workbook just click on Create table to Create a table with workbook.



2. Click on Retrieve to get the Tables present in the workbook. These are mainly worksheets.



3. You can create tables using the window. Just write the column name and click on Insert. Specify Tablename and a new worksheet will be created for you.



4. Generate Insert statements from the dynamic screen.



5. You can use Go to get the data loaded into the Grid.

NOTE :

This is just a demo application. You can use the Class associated with the application call functions to do your job easy.

Using the Code 

The code for ExcelObject Class will be like this :  

using System.IO;
using System.Data.OleDb;
using System.Text;
using System.Data;
using System.Windows.Forms;

public class ExcelObject
{
     private string excelObject = = "Provider=Microsoft.{0}.OLEDB.{1};Data Source={2};
                                     Extended Properties=\"Excel {3};HDR=YES\"";
     private string filepath = string.Empty;
     private OleDbConnection con = null;

        public delegate void ProgressWork(float percentage);
        private event ProgressWork Reading;
        private event ProgressWork Writeing;
        private event EventHandler connectionStringChange;

        public event ProgressWork ReadProgress
        {
            add
            {
                Reading += value;
            }
            remove
            {
                Reading -= value;
            }
        }

        public virtual void onReadProgress(float percentage)
        {
            if (Reading != null)
                Reading(percentage);
        }


        public event ProgressWork WriteProgress
        {
            add{ Writeing += value; }
            remove{ Writeing -= value; }
        }

        public virtual void onWriteProgress(float percentage)
        {
            if (Writeing != null)
                Writeing(percentage);
        }


        public event EventHandler ConnectionStringChanged
        {
            add{ connectionStringChange += value; }
            remove { connectionStringChange -= value; }
        }

        public virtual void onConnectionStringChanged()
        {
            if (this.Connection != null && 
                !this.Connection.ConnectionString.Equals(this.ConnectionString))
            {
                if (this.Connection.State == ConnectionState.Open)
                    this.Connection.Close();
                this.Connection.Dispose();
                this.con = null;

            }
            if (connectionStringChange != null)
            {
                connectionStringChange(this, new EventArgs());
            }
        }
        //ConnectionString
        public string ConnectionString
        {
            get
            {
                if (!(this.filepath == string.Empty))
                {
                   //Check for File Format
                    FileInfo fi = new FileInfo(this.filepath);
                    if (fi.Extension.Equals(".xls"))
                    {
                        // For Excel Below 2007 Format
                        return string.Format(this.excelObject, 
                                   "Jet", "4.0", this.filepath, "8.0");
                    }
                    else if (fi.Extension.Equals(".xlsx"))
                    {
                        // For Excel 2007 File  Format
                        return string.Format(this.excelObject, 
                                   "Ace", "12.0", Me.filepath, "12.0");
                    }
                }
                else
                {
                    return string.Empty;
                }
            }
        }
        //OleDbConnection to the current File
        public OleDbConnection Connection
        {
            get
            {
                if (con == null)
                {
                    OleDbConnection _con = new OleDbConnection { 
                                ConnectionString = this.ConnectionString };
                    this.con = _con;
                }
                return this.con;
            }
        }


        public ExcelObject(string path)
        {

            this.filepath = path;
            this.onConnectionStringChanged();
        }
        // Reads the Schema Information
        public DataTable GetSchema()
        {
            DataTable dtSchema = null;
            if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
            dtSchema = this.Connection.GetOleDbSchemaTable(
                   OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            return dtSchema;
        }
        //Reads table and returns the DataTable
        public DataTable ReadTable(string tableName)
        {
            return this.ReadTable(tableName, "");
        }

        public DataTable ReadTable(string tableName, string criteria)
        {

            try
            {
                DataTable resultTable = null;
                if (this.Connection.State != ConnectionState.Open)
                {
                    this.Connection.Open();
                    onReadProgress(10);
                    
                }
                string cmdText = "Select * from [{0}]";
                if (!string.IsNullOrEmpty(criteria))
                {
                    cmdText += " Where " + criteria;
                }
                OleDbCommand cmd = new OleDbCommand(string.Format(cmdText, tableName));
                cmd.Connection = this.Connection;
                OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
                onReadProgress(30);
                
                DataSet ds = new DataSet();
                onReadProgress(50);
                
                adpt.Fill(ds, tableName);
                onReadProgress(100);
                
                if (ds.Tables.Count == 1)
                {
                    return ds.Tables[0];
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                MessageBox.Show("Table Cannot be read");
                return null;
            }
        }
        //Generates DropTable statement and executes it.
        public bool DropTable(string tablename)
        {

            try
            {
                if (this.Connection.State != ConnectionState.Open)
                {
                    this.Connection.Open();
                    onWriteProgress(10);
                    
                }
                string cmdText = "Drop Table [{0}]";
                using (OleDbCommand cmd = new OleDbCommand(
                         string.Format(cmdText, tablename), this.Connection))
                {
                    onWriteProgress(30);
                    
                    cmd.ExecuteNonQuery();
                    onWriteProgress(80);
                    
                }
                this.Connection.Close();
                onWriteProgress(100);
                
                return true;
            }
            catch (Exception ex)
            {
                onWriteProgress(0);
                
                MessageBox.Show(ex.Message);
                return false;
            }
        }
        // Creates Create Table Statement and runs it.
        public bool WriteTable(string tableName, Dictionary<string, string> 
                                                             tableDefination)
        {
            try
            {
                using (OleDbCommand cmd = new OleDbCommand(
                this.GenerateCreateTable(tableName, tableDefination), this.Connection))
                {
                    if (this.Connection.State != ConnectionState.Open)
                    this.Connection.Open();
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }
        // Generates Insert Statement and executes it
        public bool AddNewRow(DataRow dr)
        {

            using (OleDbCommand cmd = new OleDbCommand(
                          this.GenerateInsertStatement(dr), this.Connection))
            {
               cmd.ExecuteNonQuery();
            }
            return true;
        }
        // Create Table Generation based on Table Defination
        private string GenerateCreateTable(string tableName, 
                            Dictionary<string, string> tableDefination)
        {

            StringBuilder sb = new StringBuilder();
            bool firstcol = true;
            sb.AppendFormat("CREATE TABLE [{0}](", tableName);
            firstcol = true;
            foreach (KeyValuePair<string, string> keyvalue in tableDefination)
            {
                if (!firstcol)
                {
                    sb.Append(",");
                }
                firstcol = false;
                sb.AppendFormat("{0} {1}", keyvalue.Key, keyvalue.Value);
            }

            sb.Append(")");
            return sb.ToString();
        }
        //Generates InsertStatement from a DataRow.
        private string GenerateInsertStatement(DataRow dr)
        {
            StringBuilder sb = new StringBuilder();
            bool firstcol = true;
            sb.AppendFormat("INSERT INTO [{0}](", dr.Table.TableName);


            foreach (DataColumn dc in dr.Table.Columns)



            {
                if (!firstcol)
                {
                    sb.Append(",");
                }
                firstcol = false;

                sb.Append(dc.Caption);
            }

            sb.Append(") VALUES(");
            firstcol = true;
            for (int i = 0; i <= dr.Table.Columns.Count - 1; i++)
            {
                if (!object.ReferenceEquals(dr.Table.Columns[i].DataType, typeof(int)))
                {
                    sb.Append("'");
                    sb.Append(dr[i].ToString().Replace("'", "''"));
                    sb.Append("'");
                }
                else
                {
                    sb.Append(dr[i].ToString().Replace("'", "''"));
                }
                if (i != dr.Table.Columns.Count - 1)
                {
                    sb.Append(",");
                }
            }

            sb.Append(")");
            return sb.ToString();
        }


    }

After looking through the code you are clear that we are actually generating DDL and DML statements based on the Schema Defination. I know we can easily do this using OleDbCommandBuilder object, but I thought of making them myself. Funcions Exposed through this class are :

Methods :

1. GetSchema :  It returns the Schema defination datatable of the currently selected xls file. You can call this if you have connected with an existing Excel Workbook.
2. ReadTable : It automatically generates Select statement on the tablename passed and based on the criteria provided. It returns the DataTable of the currently selected excel worksheet.
3. DropTable : Drops the table name passed, and which results in actual deletion of one worksheet from the workbook. The Function returns true if successful.
4. AddNewRow : This function creates an Insert statement and inserts a new row based on the DataRow passed in.

Properties :

1. ConnectionString : You can get connectionstring of the filepath passed.
2. Connection : Returns OleDbConnection Object.

Events :   

1. ReadProgress : It generates a callback to the calling procedure on the percentage of Read of the file. You can handle this event to get the percentage progress value.
2. WriteProgress : Same as ReadProgress, only it is called during actual insert of data.
3. ConnectionStringChanged: This event occurs if FileName is changed somehow or a new file is created.

I have also provided the same class in VB.NET for those people who wants it in VB.NET.

You can find both of them from here :

Version 1:

Download ExcelWrite_Csharp.zip - 24.61 KB
Download ExcelWrite_VB.NET.zip - 26.28 KB

Version 2: 

Download ExcelWrite_Csharp_V2.zip - 24.99 KB
Download ExcelWrite_VBNET_V2.zip - 26.88 KB

I have also added one example for ASP.NET users to dynamically create one excel file and download it to clients.

You can find that from here:
Download ExcelDownload.zip - 21.62 KB

History    

1st Release : 07th June, 2009
Looking forward to update the article with new things. Hope you like this article.  
2nd Release : 10th June, 2009
Support for xlsx files (Office 2007 Files). Hope this would help you.

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 31 (Total in Forum: 31) (Refresh) FirstPrevNext

This was the error I got when trying to open an existing xls. mnajoe 7:01 18 Nov '09   System.IO.FileNotFoundException was unhandled    FileName="System.EnterpriseServices.Wrapper.dll"    FusionLog=""    Message="Could not load file or assembly 'System.EnterpriseServices.Wrapper.dll' or one of its dependencies. The system cannot find the file specified."    Source="System.EnterpriseServices"    StackTrace:          at System.EnterpriseServices.ContextUtil.get_IsInTransaction()      at System.Data.Common.ADP.IsSysTxEqualSysEsTransaction()      at System.Data.Common.ADP.NeedManualEnlistment()      at System.Data.OleDb.OleDbConnection.Open()      at ExcelWrite.ExcelObject.GetSchema() in C:\Documents and Settings\Josef\Desktop\ExcelWrite_VB.NET\ExcelWrite\ExcelWrite\ExcelObject.vb:line 113      at ExcelWrite.Form1.btnRetrieve_Click(Object sender, EventArgs e) in C:\Documents and Settings\Josef\Desktop\ExcelWrite_VB.NET\ExcelWrite\ExcelWrite\Form1.vb:line 26      at System.Windows.Forms.Control.OnClick(EventArgs e)      at System.Windows.Forms.Button.OnClick(EventArgs e)      at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)      at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)      at System.Windows.Forms.Control.WndProc(Message& m)      at System.Windows.Forms.ButtonBase.WndProc(Message& m)      at System.Windows.Forms.Button.WndProc(Message& m)      at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)      at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)      at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)      at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)      at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)      at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)      at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)      at System.Windows.Forms.Application.Run(ApplicationContext context)      at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()      at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()      at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)      at ExcelWrite.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81      at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)      at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)      at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()      at System.Threading.ThreadHelper.ThreadStart_Context(Object state)      at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)      at System.Threading.ThreadHelper.ThreadStart()    InnerException: System.IO.FileNotFoundException          FileName="System.EnterpriseServices.Wrapper.dll"          FusionLog=""          Message="Could not load file or assembly 'System.EnterpriseServices.Wrapper.dll' or one of its dependencies. The system cannot find the file specified."          InnerException: System.IO.FileNotFoundException                   Message="The system cannot find the file specified. (Exception from HRESULT: 0x80070002)"                   InnerException: Sign In·View Thread·PermaLink Re: This was the error I got when trying to open an existing xls. Abhishek Sur 10:04 18 Nov '09   This happens when corrupted installation prevents to access Oledb from the computer. Actually System.EnterpriseServices.Wrapper.DLL is a gateway to OLEDB based drivers. So you need to Reinstall .NET framework to prevent this to occur. 1. Uninstall .NET Framework 2.0 Service Pack 1. 2. Uninstall .NET Framework 2.0. 3. Re-install .NET Framework 2.0. 4. Re-install .NET Framework 2.0 Service Pack 1. 5. Reboot your system. I think some windows patch somewhat corrupted the installation. Abhishek Sur Don't forget to click "Good Answer" if you like this Solution. My Latest Articles--> Simplify Code Using NDepend Basics of Bing Search API using .NET Microsoft Bing MAP using Javascript Sign In·View Thread·PermaLink Re: This was the error I got when trying to open an existing xls. mnajoe 4:12 20 Nov '09   Reloading .net worked. However, oledb is not registered on my machine. I downloaded all drivers from microsoft, still no luck. I am running vista 64bit. This might be part of the problem, What other connection should I make to excel? My ultimate goal is to have a user form that will send info to specific cells in my excel file, the excel file performs the calculations and sends back the ifo to the form, the form will save this processed data to a db. I just need excel to run the numbers. Am I moving in the right direction or should I try something else? Thanx. Sign In·View Thread·PermaLink Re: This was the error I got when trying to open an existing xls. Abhishek Sur 2hrs 49mins ago  If you are not using Excel to store data, then dont use OleDb to do this. Why do you need to do calculations in Excel ?? You can easily do it in your program and update to database easily. You will get every function that is associated with Excel in .NET library and in a more flexible way. Use it to do the calculations and get the Result. Yes, I never tried it to 64 bit. I think this would be the issue. Abhishek Sur Don't forget to click "Good Answer" if you like this Solution. My Latest Articles--> Simplify Code Using NDepend Basics of Bing Search API using .NET Microsoft Bing MAP using Javascript Sign In·View Thread·PermaLink Filtering Out tables ending in $ ComplexityChaos 7:20 17 Nov '09   (from dr in dt.AsEnumerable() where !dr["TABLE_NAME"].ToString().Trim().EndsWith("$") select dr["TABLE_NAME"]).ToList(); Why? They seem to be valid tables. Sign In·View Thread·PermaLink Re: Filtering Out tables ending in $ Abhishek Sur 22:31 17 Nov '09   If you try this carefully, the schema datatable will hold duplicate entries for each sheet. I need to do that because of that. Abhishek Sur Don't forget to click "Good Answer" if you like this Solution. My Latest Articles--> Simplify Code Using NDepend Basics of Bing Search API using .NET Microsoft Bing MAP using Javascript Sign In·View Thread·PermaLink Re: Filtering Out tables ending in $ ComplexityChaos 2:00 18 Nov '09   Unfortunately, when I scan through the results with and without the $, I get all sorts of tab/sheet name issues. Say I have a tab "Part1", the only reference to it might be Part1$_Filtered... Sign In·View Thread·PermaLink Re: Filtering Out tables ending in $ Abhishek Sur 10:06 18 Nov '09   I dont know if this is the case. But whereever I tried to access excel pages, it always showed me the duplicate sheets with $. If this is ur situation, you might omit the line which filters out the sheets with $. Sorry for inconvenience. Abhishek Sur Don't forget to click "Good Answer" if you like this Solution. My Latest Articles--> Simplify Code Using NDepend Basics of Bing Search API using .NET Microsoft Bing MAP using Javascript Sign In·View Thread·PermaLink Schema has no rows??? ChrisCohen 4:32 14 Sep '09   Hi, I'm running this on x64 W2k3, driver is installed but not Excel - I have already found I need to compile for x86 specifically in order to use the provider. However, despite the schema returning the expected columns no objects (rows) are returned, meaning the Retrieve button does not work. Any ideas please? Thanks, Chris Sign In·View Thread·PermaLink 1.00/5 (1 vote) About Data type when use the Jet 4.0 hippolyte.hu 23:04 26 Aug '09   Hi, Abhishek First of all, thank you for yout article. I am also to read the xls file data, your article gives me many help. Now I have a problem when read the long string in the cell, whose length is more than 255 (varchar). If the first line the string has enough length larger 255, like 375, the program will set right data in the datatable cell using jet 4.0 to read the xls file. But the first string's length is less the 255, not only the follow cell has the string larger than 255, but also the program will just read the first of 255 char into datatable cell. I try to use the "IMEX = 1; IMEX = 0; IMEX = 2" to revise this problem, but there is not any help. And I have no authority to modify the registry table. That's all, I hlep me to revise this problem. Thanks, Hippolyte Sign In·View Thread·PermaLink Retreiving numeric values starting with a zero FreddieH85 5:45 14 Aug '09   Hi, In a project of my I import excel-sheets which are send by mail in MSSQL-tables. In these excel-sheet it is possible to have numeric values starting with a zero (like 0201234567 or 0031201234567). When I retreive the values from excel, the oledbdataadapter returns the value without zero. Do you know a workaround for this so I get the values with the leading zero('s)? Thanx in advance Sign In·View Thread·PermaLink Re: Retreiving numeric values starting with a zero Abhishek Sur 13:33 15 Aug '09   Get data as string. Integer field disallows leading 0's. Abhishek Sur My Latest Articles Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB Don't forget to click "Good Answer" if you like to. Sign In·View Thread·PermaLink build error in latest c# project FreddieH85 5:41 14 Aug '09   In your class ExcelObject.cs on row 112 it says Me.filepath instead of this.filepath, so you get a build error there Sign In·View Thread·PermaLink Re: build error in latest c# project Abhishek Sur 13:31 15 Aug '09   Oh.. I it. It must be a problem while I was changing the VB.NET project to C#. Sorry for inconvenience. I will fix it as soon as possible. Thanks. Abhishek Sur My Latest Articles Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB Don't forget to click "Good Answer" if you like to. Sign In·View Thread·PermaLink Excel 2007 as Binary gmelim 15:56 6 Aug '09   I created a new excel 2007 'xlsx' file with ACE driver. Using this code as reference (CREATE TABLe follow by INSERT INTO) When I tried to open the file with Excel it propmted an error about file extension. I discovered that if I changed the extension of the file to xlsb excel could open it. It seems that create table method could not be used with ACE driver to create a new xlsx file. Can you help me? Sign In·View Thread·PermaLink Thanks satalaj 6:05 6 Aug '09   After setting TypeGuessRows = 500 or max it resolved my problem. Thanks for posting this article Thanks Sign In·View Thread·PermaLink Re: Thanks Abhishek Sur 13:22 15 Aug '09   Thank you .. Abhishek Sur My Latest Articles Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB Don't forget to click "Good Answer" if you like to. Sign In·View Thread·PermaLink Great Buddy Abhijit Jana 11:48 15 Jul '09   Nice one.. You have already got my 5 cheers, Abhijit CodeProject MVP Web Site:abhijitjana.net My Latest Article : IIS Remote Debugging Sign In·View Thread·PermaLink Re: Great Buddy Abhishek Sur 23:29 15 Jul '09   Thank you so much... dear... Abhishek Sur My Latest Articles Working with Excel using MDAC Basics on LINQ and Lambda Expressions Create .NET Templates Sign In·View Thread·PermaLink You rocks man!!! suresh suthar 21:26 17 Jun '09   An awesome article.... Thanks a lot for sharing this.. 5 from me... Be an Eagle, Sky is Yours. Sign In·View Thread·PermaLink 5.00/5 (1 vote) Re: You rocks man!!! Abhishek Sur 22:26 17 Jun '09   It is lovely to see such comments.. Thanks so much for appreciating. Abhishek Sur My Latest Articles Working with Excel using MDAC Basics on LINQ and Lambda Expressions Create .NET Templates Sign In·View Thread·PermaLink Good article martyn_mcfly 20:34 17 Jun '09   It just what I need. Thank you =) Sign In·View Thread·PermaLink Re: Good article Abhishek Sur 22:18 17 Jun '09   Thank you so much for your appreciation. Abhishek Sur My Latest Articles Working with Excel using MDAC Basics on LINQ and Lambda Expressions Create .NET Templates Sign In·View Thread·PermaLink Thanks! Joakim Westin 20:51 15 Jun '09   Thank you for a nice article! There are a lot of articles about Excel and working with Excel files, but your article is the first that I found that covers my needs. great work! Cheers, Joakim ps. Save a tree - don't print this ds. Sign In·View Thread·PermaLink Re: Thanks! Abhishek Sur 22:51 15 Jun '09   you are most welcome sir... Abhishek Sur My Latest Articles Working with Excel using MDAC Basics on LINQ and Lambda Expressions Create .NET Templates Sign In·View Thread·PermaLink

Last Visit: 12:44 21 Nov '09     Last Update: 12:44 21 Nov '09 12 Next »