Showing posts with label .NET 3.0. Show all posts
Showing posts with label .NET 3.0. Show all posts

Thursday, July 15, 2010

Firing TreeView TreeNodeCheckChanged Event

Normally in ASP.Net TreeView you can enable it to show checkboxes in its node levels. So if you need to do any actions when a node is checked or unchecked you need to use the TreeNodeCheckChanged event. But the problem is TreeNodeCheckChanged event will not fire when a checkbox is clicked. The cause for this is because the TreeNode class which represents a node in the TreeView will not add onClick event on the checkbox.

But if you carefully check it the event is getting fired at the next page postback. Since I needed to do some actions when the checkbox statuses are changed I made the page post back when a checkbox is checked or unchecked by the following java script. What it does is simply it will check the element which caused the event is a checkbox and do a postback of the page.





  1. <script type="text/javascript">
  2.     function TreeViewCheckBoxClicked(Check_Event) {
  3.         var objElement;
  4.         try {
  5.             // Get the element which fired the event.
  6.             objElement = window.event.srcElement;
  7.         }
  8.         catch (Error) {
  9.             //srcElement is failing, objElement is null.
  10.         }
  11.         if (objElement != null) {
  12.             // If the element is a checkbox do postback.
  13.             if (objElement.tagName == "INPUT" && objElement.type == "checkbox") {
  14.                 __doPostBack("", "");
  15.             }
  16.         }
  17.         else {
  18.             //    If the srcElement is failing due to browser incompatibility determine
  19.             // whether the element is and HTML input element and do postback.
  20.             if (Check_Event != null) {
  21.                 if (Check_Event.target.toString() == "[object HTMLInputElement]") {
  22.                     __doPostBack("", "");
  23.                 }
  24.             }
  25.         }
  26.     }
  27. </script>




To make this work you need to bind the onClick event with the javascript shown above as shown below.





  1. protected void Page_Load(object sender, EventArgs e)
  2. {
  3.     // Adding the onClick script to the TreeView.
  4.     TreeView1.Attributes.Add("onClick", "TreeViewCheckBoxClicked(event)");
  5. }




Even though I have done this in Page Load this can be done in other places of code as well, for example if you are adding the TreeView by code you can use this right before adding it to the page.

The above will make the page post back when ever a checkbox in the TreeView is clicked, but nothing special will happen. You need to implement the code for TreeNodeCheckChanged event to get some task done out of it, for example I am using the following code to check or uncheck the child nodes depending on the action done for the parent node.





  1. protected void TreeView1_TreeNodeCheckChanged(object sender, TreeNodeEventArgs e)
  2. {
  3.     // Loop through all the child nodes and change the checked status as required.
  4.     foreach (TreeNode tn in e.Node.ChildNodes)
  5.     {
  6.         if (e.Node.Checked)
  7.             tn.Checked = true;
  8.         else
  9.             tn.Checked = false;
  10.     }
  11. }




Tuesday, July 13, 2010

Changing GridView Column Headers

If you are wondering a way to change the column header appearing in the .Net GridView control in run time then this post will help you to get it done.

For example think that you need to change the normal grid headers shown in Screen A into something like shown in Screen B.

Screen A

Screen B

I have removed few columns from the grid header and then I did add few customized column headers. The added “Edit” column and “Id” column are spanning to 2 rows, the “Temp Columns” column is spanning to 2 columns.

I think the code is self explanatory. This way you will be able to create complex gridview headers.





  1. protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
  2. {
  3.      if (e.Row.RowType == DataControlRowType.Header)
  4.      {
  5.          // Remving the first two colummn headers.
  6.          e.Row.Cells.RemoveAt(0);
  7.          e.Row.Cells.RemoveAt(0);
  8.          // Creating the gridview row object.
  9.          GridViewRow headerGridRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Selected);
  10.          headerGridRow.ID = "hdrGridRow";
  11.          TableCell HeaderCell;
  12.          // Creating and adding the first header cell.
  13.          HeaderCell = new TableCell();
  14.          HeaderCell.Text = "Edit";
  15.          HeaderCell.ID = "cellEdit";
  16.          HeaderCell.Font.Bold = false;
  17.          HeaderCell.HorizontalAlign = HorizontalAlign.Center;
  18.          HeaderCell.RowSpan = 2; // Spans across 2 rows.
  19.          HeaderCell.ColumnSpan = 1;
  20.          HeaderCell.BackColor = System.Drawing.Color.LightGray;
  21.          HeaderCell.BorderColor = System.Drawing.Color.White;
  22.          HeaderCell.ForeColor = System.Drawing.Color.Red;
  23.          headerGridRow.Cells.Add(HeaderCell);
  24.          // Creating and adding the second header cell.
  25.          HeaderCell = new TableCell();
  26.          HeaderCell.Text = "Id";
  27.          HeaderCell.ID = "cellId";
  28.          HeaderCell.Font.Bold = false;
  29.          HeaderCell.HorizontalAlign = HorizontalAlign.Center;
  30.          HeaderCell.RowSpan = 2;
  31.          HeaderCell.ColumnSpan = 1;
  32.          HeaderCell.BackColor = System.Drawing.Color.LightGray;
  33.          HeaderCell.BorderColor = System.Drawing.Color.White;
  34.          HeaderCell.ForeColor = System.Drawing.Color.Red;
  35.          headerGridRow.Cells.Add(HeaderCell);
  36.          // Creating and adding the third header cell.
  37.          HeaderCell = new TableCell();
  38.          HeaderCell.Text = "Temp Columns";
  39.          HeaderCell.ID = "cellTempColumns";
  40.          HeaderCell.Font.Bold = false;
  41.          HeaderCell.HorizontalAlign = HorizontalAlign.Center;
  42.          HeaderCell.RowSpan = 1;
  43.          HeaderCell.ColumnSpan = 2; // Spans across 2 columns.
  44.          HeaderCell.BackColor = System.Drawing.Color.LightGray;
  45.          HeaderCell.BorderColor = System.Drawing.Color.White;
  46.          HeaderCell.ForeColor = System.Drawing.Color.Red;
  47.          headerGridRow.Cells.Add(HeaderCell);
  48.          // Adding the header row to the gridview.
  49.          GridView1.Controls[0].Controls.AddAt(0, headerGridRow);
  50.      }
  51. }




Sunday, June 27, 2010

Class not registered

Recently when I tried to build and run an existing .Net application code I got the following error which generated from a component named “axShockwaveFlash1” which is an object used to embed flash content on to the application.

System.Runtime.InteropServices.COMException was unhandled
  Message=Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))
  Source=System.Windows.Forms
  ErrorCode=-2147221164
  StackTrace:
       at System.Windows.Forms.UnsafeNativeMethods.CoCreateInstance(Guid& clsid, Object punkOuter, Int32 context, Guid& iid)
       at System.Windows.Forms.AxHost.CreateWithoutLicense(Guid clsid)
       at System.Windows.Forms.AxHost.CreateWithLicense(String license, Guid clsid)
       at System.Windows.Forms.AxHost.CreateInstanceCore(Guid clsid)
       at System.Windows.Forms.AxHost.CreateInstance()
       at System.Windows.Forms.AxHost.GetOcxCreate()
       at System.Windows.Forms.AxHost.TransitionUpTo(Int32 state)
       at System.Windows.Forms.AxHost.CreateHandle()
       at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       at System.Windows.Forms.AxHost.EndInit()
       at Tour_Management_System.Loading_Form.InitializeComponent() in D:\Projects\Acc\TMS_20100205_Desktop\Tour_Management_System 2010-01-19\Tour_Management_System\Form1.Designer.cs:line 99
       at Tour_Management_System.Loading_Form..ctor() in D:\Projects\Acc\TMS_20100205_Desktop\Tour_Management_System 2010-01-19\Tour_Management_System\Form1.cs:line 30
       at Tour_Management_System.Program.Main() in D:\Projects\Acc\TMS_20100205_Desktop\Tour_Management_System 2010-01-19\Tour_Management_System\Program.cs:line 18
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

After some investigation I found that the reason for this was Flash Player’s incompatibility with 64 bit. Since my machine is running Windows 64 bit version it generates an error while trying to start Flash player. Good thing is Adobe is planning to release a Flash player which supports 64 bit soon.

For people who cannot wait until that (like me) can fix the problem in the following manner by running the application as an x86 application by changing the target platform in project properties.

Step 1 - Right click on your project and click on the properties.

Step 2 - Click on the Build tab and select x86 as the Platform target:.

Step 3 - Rebuild the application and run it, you will not get the error.

Tuesday, June 22, 2010

Changing the Tab Container

Are you seeking a way to customize the tabcontainer which comes with AJAX toolkit? AJAX tab container has several Cascade Style Sheet (CSS) properties which you can use to change the look of the tab container. But you need to make sure that you are not changing the keywords used in the CSS (ajax__tab_header, ajax__tab_outer, etc…).

You can change the look of the tabs by two methods. Tab container is using few images to give the 3D look it is having in its default view. so the first method is by changing the images and associating the new images to the tab container using a style sheet, second method is to change the tabs using the CSS without images, by using the CSS you can change the colors, sizes etc of the tabs.

Following are the default images that are shipped with the toolkit.

tab tab
tab-active tab-active
tab-active-left tab-active-left
tab-active-right tab-active-right
tab-hover tab-hover
tab-hover-left tab-hover-left
tab-hover-right tab-hover-right
tab-left tab-left
tab-right tab-right
tab-line tab-line

 

In the following code I am changing the tabcontainer look by using CSS and then placing the updated images to the relevant (Images/Controls) folder.





  1. .tabCont .ajax__tab_header
  2. {
  3.     font-family: verdana,tahoma,helvetica;
  4.     font-size: 11px;
  5.     background: url(../Images/Controls/tab-line.gif) repeat-x bottom;
  6. }
  7. .tabCont .ajax__tab_outer
  8. {
  9.     padding-right: 4px;
  10.     background: url(../Images/Controls/tab-right.gif) no-repeat right;
  11.     height: 21px;
  12. }
  13. .tabCont .ajax__tab_inner
  14. {
  15.     padding-left: 3px;
  16.     background: url(../Images/Controls/tab-left.gif) no-repeat;
  17. }
  18. .tabCont .ajax__tab_tab
  19. {
  20.     height: 13px;
  21.     padding: 4px;
  22.     margin: 0px;
  23.     background: url(../Images/Controls/tab.gif) repeat-x;
  24. }
  25. .tabCont .ajax__tab_hover .ajax__tab_outer
  26. {
  27.     cursor: pointer;
  28.     background: url(../Images/Controls/tab-hover-right.gif) no-repeat right;
  29. }
  30. .tabCont .ajax__tab_hover .ajax__tab_inner
  31. {
  32.     cursor: pointer;
  33.     background: url(../Images/Controls/tab-hover-left.gif) no-repeat;
  34. }
  35. .tabCont .ajax__tab_hover .ajax__tab_tab
  36. {
  37.     cursor: pointer;
  38.     background: url(../Images/Controls/tab-hover.gif) repeat-x;
  39. }
  40. .tabCont .ajax__tab_active .ajax__tab_outer
  41. {
  42.     background: url(../Images/Controls/tab-active-right.gif) no-repeat right;
  43. }
  44. .tabCont .ajax__tab_active .ajax__tab_inner
  45. {
  46.     background: url(../Images/Controls/tab-active-left.gif) no-repeat;
  47. }
  48. .tabCont .ajax__tab_active .ajax__tab_tab
  49. {
  50.     background: url(../Images/Controls/tab-active.png) repeat-x;
  51. }
  52. .tabCont .ajax__tab_disabled
  53. {
  54.     color: #A0A0A0;
  55. }
  56. .tabCont .ajax__tab_body
  57. {
  58.     font-family: verdana,tahoma,helvetica;
  59.     font-size: 10pt;
  60.     border: 0px solid #999999;
  61.     border-top: 0;
  62.     padding: 8px;
  63.     background-color: #f1f1f1;
  64.     margin: 12px;
  65. }




 

To apply the style to the tab container you can use the following code.

<asp:TabContainer ID="tcnManageUsers" runat="server" ActiveTabIndex="0" CssClass="tabCont">

Following is the full code of my tab container.





  1. <asp:TabContainer ID="tcnManageUsers" runat="server" ActiveTabIndex="0" CssClass="tabCont">
  2.      <asp:TabPanel runat="server" HeaderText="Add" ID="tplAdd" >
  3.          <ContentTemplate>
  4.              <uc1:CreateUser ID="AddCreateUser" runat="server" />
  5.          </ContentTemplate>  
  6.      </asp:TabPanel>
  7.      <asp:TabPanel ID="tplEdit" runat="server" HeaderText="Edit" >
  8.          <ContentTemplate >
  9.             <asp:Button ID="btnSelectUser" runat="server" Text="Select User" OnClick="btnSelectUser_Click"/>
  10.             <uc1:CreateUser ID="EditCreateUser" runat="server" />
  11.          </ContentTemplate>
  12.      </asp:TabPanel>
  13.  </asp:TabContainer>




Friday, June 18, 2010

Creating Rounded Corners

When designing web sites it always looks nicer if you could round the sharp corners, for example see below, what looks nicer to you?

Sharp Corners

Rounded Corners

My feeling is the rounded corners are better looking than the normal one. But the problem is, there is no easy way to create this. You need some Cascade Style Sheet (CSS) knowledge to do this. The easy way to round the required corners is to use the following code.





  1. <html xmlns="http://www.w3.org/1999/xhtml">
  2. <head runat="server">
  3.     <title></title>
  4.     <style type="text/css">
  5.         .CornerTopLeft
  6.         {
  7.             float: right;
  8.             width: 100%;
  9.             margin-top: 10px;
  10.             background: url(../Images/TopLeft.png) 0 0 no-repeat rgb(237,28,36);
  11.             border: 1px double #f1f1f1;
  12.         }
  13.         .CornerTopRight
  14.         {
  15.             float: right;
  16.             width: 100%;
  17.             background: url(../Images/TopRight.png) 100% 0 no-repeat;
  18.         }
  19.         .CornerBottomLeft
  20.         {
  21.             float: right;
  22.             width: 100%;
  23.             background: url(../Images/BottomLeft.png) 0 100% no-repeat;
  24.             height: 100%;
  25.         }
  26.         .CornerBottomRight
  27.         {
  28.             float: right;
  29.             width: 100%;
  30.             background: url(../Images/BottomRight.png) 100% 100% no-repeat;
  31.         }
  32.         div#Content
  33.         {
  34.             margin: 15px;
  35.         }
  36.     </style>
  37. </head>
  38. <body bgcolor="rgb(232,232,232)">
  39.     <form id="form1" runat="server">
  40.     <div class="CornerTopLeft">
  41.         <div class="CornerTopRight">
  42.             <div class="CornerBottomLeft">
  43.                 <div class="CornerBottomRight">
  44.                     <div id="Content">
  45.                         Your content here.....
  46.                         <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
  47.                         <asp:Button ID="Button1" runat="server" Text="Button" />
  48.                     </div>
  49.                 </div>
  50.             </div>
  51.         </div>
  52.     </div>
  53.     </form>
  54. </body>
  55. </html>




The above code is self explanatory so I didn’t put any comments there. To make it work you need the following 4 images placed on your Images directory.

TopLeft.png –

TopRight.png –

BottomLeft.png –

BottomRight.png –

If I am to explain the code, the corners are curved by using 4 different DIVs and placing the above images on them. For example the following DIVs does the trick.

<div class="CornerTopLeft">
<div class="CornerTopRight">
<div class="CornerBottomLeft">
<div class="CornerBottomRight">





  1. <div class="CornerTopLeft">
  2.         <div class="CornerTopRight">
  3.             <div class="CornerBottomLeft">
  4.                 <div class="CornerBottomRight">
  5.                     <div id="Content">
  6.                         Your content here.....
  7.                         <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
  8.                         <asp:Button ID="Button1" runat="server" Text="Button" />
  9.                     </div>
  10.                 </div>
  11.             </div>
  12.         </div>
  13.     </div>




The content you need to put in the middle should be wrapped by these DIVs. In the above code I used another DIV for placing content having the ID Content.

Inserting curved images to the corners of the DIVs are done by using a style per DIV. The CSS classes which are used to change these DIVs are as of below.





  1. <style type="text/css">
  2.         .CornerTopLeft
  3.         {
  4.             float: right;
  5.             width: 100%;
  6.             margin-top: 10px;
  7.             background: url(../Images/TopLeft.png) 0 0 no-repeat rgb(237,28,36);
  8.             border: 1px double #f1f1f1;
  9.         }
  10.         .CornerTopRight
  11.         {
  12.             float: right;
  13.             width: 100%;
  14.             background: url(../Images/TopRight.png) 100% 0 no-repeat;
  15.         }
  16.         .CornerBottomLeft
  17.         {
  18.             float: right;
  19.             width: 100%;
  20.             background: url(../Images/BottomLeft.png) 0 100% no-repeat;
  21.             height: 100%;
  22.         }
  23.         .CornerBottomRight
  24.         {
  25.             float: right;
  26.             width: 100%;
  27.             background: url(../Images/BottomRight.png) 100% 100% no-repeat;
  28.         }
  29.         div#Content
  30.         {
  31.             margin: 15px;
  32.         }
  33.     </style>




The filling of the required area is done by assiging a color at the background tag in CornerTopLeft style which will be applied to CornerTopLeft DIV.

background: url(../Images/TopLeft.png) 0 0 no-repeat rgb(237,28,36);

Note the last div#Content which was placed to style the Content DIV making the contents placed centered so the final output will be nicer.

Thursday, May 06, 2010

"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

If you are getting the above error while trying to connect to a database, the reason is you are using Windows Authentication to login to the SQL Server while being in another untrusted domain.

For example if the SQL Server machine is a member of the CompanyDomain and if you are in MyDomain or if you are in a Workgroup the you will face the above issue while trying to connect to the SQL Server.

The connection string used in the web.config while the above error is generated is as follows.





  1. <connectionStrings>
  2.   <add name="cnnStr" connectionString="Data Source=BI-SVR;Initial Catalog=BBIDatabase;Integrated Security=True"/>
  3. </connectionStrings>




 

There are three ways to fix this problem.

1. Use the SQL Authentication to login to the SQL Server.

You can get this done by changing the connection string to use SQL authentication while connecting. But you need to know the credentials of an account which is having permissions to your required database or the System Administrator (SA) password. For simplicity I will use SA account details in the connection string.





  1. <connectionStrings>
  2.   <add name="cnnStr" providerName="System.Data.OleDb" connectionString="Data Source=BI-SVR;Persist Security Info=True;Password=YourPassword;User ID=sa;Initial Catalog=BBIDatabase"/>
  3. </connectionStrings>




 

2. Login to your machine using the same Domain.

If you login to your machine using a domain account which the SQL Server is added to, then this error will vanish. But for this you need to add your machine to the same domain which the SQL Server machine is added to (CompanyDomain) also to properly get authenticated the account used should have proper permissions set to access the database in the SQL Server.

3. Make the account trusted in SQL Server.

By making the account you use to login to your machine trusted account in SQL Server and giving it appropriate permissions to access databases will also permit you to fix this error.

Sunday, April 11, 2010

.Net Nullable Types

A variable of a nullable type can be used to store the normal range of values allowed by the underlying data type plus the Null. Nullable types are instances of System.Nullable.

Since reference types already supports Null, nullable types represent value type variables which supports null.

For example Nullable<bool> (or spoken like nullable of bool) can be used to store true, false and null.

There are two ways to declare a nullable variable.

Method 1




int? i; // Declaring
i = null; // Initializing

int? i = 4; // Declariong and initializing




Method 2




Nullable<int> a; // Declaring
a = 4; // Initializing

Nullable<int> a = null; // Declariong and initializing




As you see above assigning values to a nullable is same as for a normal variable. But when retrieving the value you need to be little careful.

Method 1 – Using GetValueOrDefault

GetValueOrDefault property is available for nullable variables. If the variable is null it will get the default value for the type otherwise the actual value it contains.




textBox1.Text += "Value of i - " + i.GetValueOrDefault();




Method 2 – Using variable.Value

When you are going to retrieve the value inside the variable using .Value be careful to first check whether there is actually a value in the variable, otherwise .Net will generate an InvalidOperationException with a description of “Nullable object must have a value”.




// \r\n is used to insert a new line.
if (i == null)
    textBox1.Text += "\r\n" + "i is Null";
else
    textBox1.Text += "\r\n" + "i is - " + i.Value;




or




// Environment.NewLine is equal to placing a new line or \r\n.
if (a.HasValue)
    textBox1.Text += Environment.NewLine + "a has - " + a.Value;
else
    textBox1.Text += Environment.NewLine + "a has Null";




The full code would look like the following.





  1. // Declaring Method 1
  2. int? i; // Declaring
  3. i = null; // Initializing
  4. //int? i = 4; // Declariong and initializing
  5. // Get value using GetValueOrDefault().
  6. textBox1.Text += "Value of i - " + i.GetValueOrDefault();
  7. // \r\n is used to insert a new line.
  8. if (i == null)
  9.     textBox1.Text += "\r\n" + "i is Null";
  10. else
  11.     textBox1.Text += "\r\n" + "i is - " + i.Value;
  12. // Declaring Method 2
  13. //Nullable<int> a; // Declaring
  14. //a = 4; // Initializing
  15. Nullable<int> a = 4; // Declariong and initializing
  16. // Environment.NewLine is equal to placing a new line or \r\n.
  17. if (a.HasValue)
  18.     textBox1.Text += Environment.NewLine + "a has - " + a.Value;
  19. else
  20.     textBox1.Text += Environment.NewLine + "a has Null";




The output would be,

Value of i - 0
i is Null
a has – 4

You could use the ?? operator to assign the default value for a non nullable variable while the nullable contains null as the current value.




int? Null_X = null;
int NonNull_y = Null_X ?? -1;




Sunday, April 04, 2010

How to Read and Write Text

Reading and writing to text files had been made easy by .Net.

Following code will create a text file and insert 2 lines to it and will then read it and display.





  1. // System.IO is required for file handling.
  2. using System.IO;








  1. // Creating a stream writer object.
  2. // @ is used before the string to avoid the string getting broken by the /.
  3. StreamWriter stWriter = new StreamWriter(@"C:\Backup\File.txt");
  4. // Writing into the file.
  5. stWriter.Write("It is now ");
  6. stWriter.WriteLine(DateTime.Now);
  7. stWriter.WriteLine("The End");
  8. // Closing the file.
  9. stWriter.Close();
  10. // Reading the file.
  11. // Checking the availability of the file.
  12. if (File.Exists(@"C:\Backup\File.txt"))
  13. {
  14.     // Without using the @, \\ also can be used to represent \ inside of a string.
  15.     using (StreamReader stReader = File.OpenText("C:\\Backup\\File.txt"))
  16.     {
  17.         // Temporary variable.
  18.         string str = "";
  19.         while ((str = stReader.ReadLine()) != null)
  20.         {
  21.             // Assigning to a label.
  22.             label1.Text += str + "\n";
  23.         }
  24.     }
  25. }
  26. else
  27.     MessageBox.Show("Error, File cannot be found.");




Saturday, March 13, 2010

Reading and Writing to Excel Files

Even though I posted similar 2 articles in 2008, the code of that article was having few errors. So thought to put more complete post on this.

The code will read an Excel file using an OleDbConnection and will write the same data back to another Excel file. This will explain how to read and write to Excel files. One thing to remember when running this code is to make sure the source Excel file is open. Otherwise you will get an error similar to “External table is not in the expected format.”

Also note that there are few connection string parameters you can use while opening Excel files.

HDR = Yes – Use when first row contains column headers.

HDR = No  - Use when first row contains data.

Excel xx.x – Use the following Table as a guide.

Parameter Value

Excel Version

Excel 12.0 Excel 2007 (Released in 2007)
Excel 11.0 Excel 2003 (Released in 2003)
Excel 10.0 Excel XP (Released in 2001)
Excel 9.0 Excel 2000 (Released in 1999)
Excel 8.0 Excel 97 (Released in 1997)

 

IMEX=1 – Use this when you want to treat all your data in the file as text.

For example - >

Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

To make the code to work properly you need to refer the Microsoft.Office.Interop.Excel assembly by adding a reference to your project.





  1. using System;
  2. using System.Data;
  3. using System.Windows.Forms;
  4. using System.Data.OleDb;
  5. using Microsoft.Office.Interop.Excel;
  6. namespace TestApp
  7. {
  8.     public partial class Form1 : Form
  9.     {
  10.         public Form1()
  11.         {
  12.             InitializeComponent();
  13.         }
  14.         private void button1_Click(object sender, EventArgs e)
  15.         {
  16.             openFileDialog1.ShowDialog();
  17.             // Create an OLEDBConnection to connect to the Excel file.
  18.             // I'm getting the required file by using a file dialog.
  19.             // The @ symbol makes the string to contain any special characters inside the string without breaking the string.
  20.             OleDbConnection dbConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName.ToString() + @";Extended Properties=""Excel 8.0;HDR=Yes;""");
  21.             // Open the connection.
  22.             dbConnection.Open();
  23.             // Create a command object to work on the data.
  24.             // Note that I have given the sheet name as [Sheet1$] to retrieve data from that named sheet in the particular Excel file.
  25.             OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", dbConnection);
  26.             // Creating a data reader to read data.
  27.             OleDbDataReader dbReader = dbCommand.ExecuteReader();
  28.             // If needed you can get the position of any column (e.g. Age), this will only work if you use HDR=Yes.
  29.             int SearchingItem = dbReader.GetOrdinal("Age");
  30.             // Create the Excel Application object.
  31.             ApplicationClass ExcelApp = new ApplicationClass();
  32.             // Set the visibility of the application.
  33.             ExcelApp.Visible = true;
  34.             // Create a new Excel Workbook.
  35.             Workbook ExcelWorkbook = ExcelApp.Workbooks.Add(Type.Missing);
  36.             // Create a new Excel Sheet.
  37.             Worksheet ExcelSheet = (Worksheet)ExcelWorkbook.Sheets.Add(ExcelWorkbook.Sheets.get_Item(1), Type.Missing, 1, XlSheetType.xlWorksheet);
  38.             // Will keep the current row index. This should start from 1 since the first row is 1.
  39.             int CurrentRowIndex = 1;
  40.             try
  41.             {
  42.                 // Read through the data.
  43.                 while (dbReader.Read())
  44.                 {
  45.                     // Traverse through all the data columns.
  46.                     for (int i = 0; i < dbReader.VisibleFieldCount; i++)
  47.                     {
  48.                         ExcelSheet.Cells[CurrentRowIndex, i + 1] = dbReader.GetValue(i);
  49.                     }
  50.                     CurrentRowIndex++;
  51.                 }
  52.                 // Save the Excel sheet.
  53.                 // The @ symbol makes the string to contain any special characters inside the string without breaking the string.
  54.                 ExcelApp.Save(@"C:\Projects\Ex.xls");
  55.             }
  56.             catch (Exception ex)
  57.             {
  58.                 MessageBox.Show(ex.ToString());
  59.             }
  60.         }
  61.     }
  62. }