Hidden Worksheet in an Excel File

I am automating a report where I have to pull data from excel file to database. When doing this, sometimes I get an error keep telling me that I get the wrong report format.

After debug on my program, I found out that some of the excel files do have a few hidden worksheet with different report format. That’s why I keep getting this error.

Assume that only visible worksheet will be used to pull data into database, so I need to know which worksheet is visible and which is not. This can be done by using Worksheet.Visible property, which can determine whether the worksheet is visible or hide.

...
Excel.Application xlsApp = new Excel.ApplicationClass();
Excel.Workbook xlsWorkbook;
Excel.Worksheet xlsWorksheet;

xlsWorkbook = xlsApp.Workbooks.Open("C:\\test.xls",0,false,5,"","",true,Excel.XlPlatform.xlWindows,"\t",false,false,0,true,1,0);

// get the collection of sheets in the workbook
Excel.Sheets sheets = xlsWorkbook.Worksheets;

foreach(Worksheet sht in sheets) {
     if (sht.Visible == Excel.XlSheetVisibility.xlSheetVisible) {
         // your process here…
     }
     else {
         // Do nothing…
     }
}
...

As you can see from the code above, if the worksheet is visible, I will continue with my process, else I just ignore the worksheet.

Comparison of ADO .NET DataSet and ADO RecordSet

1. Number of Tables. Dataset can hold several tables which are called data tables at once. A dataset also stores relationships and constraints on the tables. For example, if a dataset contains a table about customers and another table about customers’ product, it could also contain a relationship connecting each row of the customers table with the corresponding rows of the product table. A recordset contains only the rows returned by the corresponding query. For example, if a recordset want to retrieve data from several tables, it must use a JOIN query to assemble those data into a single result table.

2. Data Navigation. In ADO you loop through the rows of the recordset using MoveNext method. In ADO.NET, rows are represented as collections, so you can loop through a table as you would through any collection, or access particular rows via ordinal or primary key index.

3. Minimized Open Connections. Dataset is designed to work connectionless to the original data source. You can read from a dataset without stay connected to the data source. In ADO, recordset can also provide disconnected access, but it is designed primarily for connected access.

4. Sharing Data between Applications. Transmitting a database between applications is much easier than transmitting a recordset. To transmit a disconnected recordset from one component to another, you use COM marshalling. While ADO .NET uses XML format for transmitting data between applications.

5. Richer Data Types. Dataset is in an XML format, so there is no restriction on data types. COM marshalling provides a limited set of data types (recordset is an instant of a COM object).

6. Performance. ADO .NET does not require data-type conversions. ADO, which requires COM marshalling to transmit data among components, does require the ADO data types be converted to COM data types.

7. Penetrating Firewalls. Components exchange datasets using XML, so it can pass safely over firewalls. While firewalls prevent system-level requests (such as COM marshalling) from passing.

For the complete information, please refer to MSDN – Comparison of ADO .NET and ADO

Access is denied in ASP .NET

Description: An unhandled exception occurred during the execution of the current web request.

Exception Details: System.UnauthorizedAccessException: Access is denied. ASP.NET is not authorized to access the requested resource.

Solution:
1. Consider granting access rights to the resource to the ASP.NET request identity. ASP.NET has a base process identity (typically {MACHINE}\ASPNET on IIS 5 or Network Service on IIS 6) that is used if the application is not impersonating. If the application is impersonating via <identity impersonate="true"/ >, the identity will be the anonymous user (typically IUSR_MACHINENAME) or the authenticated request user.

2. To grant ASP.NET write access to a file, right-click the file in Explorer, choose "Properties" and select the Security tab. Click "Add" to add the appropriate user or group. Highlight the ASP.NET account, and check the boxes for the desired access.

Timeout expired error in ASP .NET

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Had you seen this Timeout expired error in your application before?
I have an application which will do a lot of database reads and updates. While the application is running to update records into database, suddenly I am getting this error.

I tried to add max pool size to a bigger size (Max Pool Size, default = 100), but in the end I still getting the same error, so I suspect that I am leaking connections.

public object Execute(string SQLStmt)
{
   try {
       m_cmd = new SqlCommand(SQLStmt, Connection);
       object a = m_cmd.ExecuteNonQuery();
       m_cmd.Dispose();
       return a;
   }
   catch (Exception e) {
       m_oErr = e;
       return null;
   }
}

Note that if my ExecuteNonQuery() throws an exception, the Dispose() will never get called. And after running a few hundreds of query, this timeout exception will be seen.

Obviously, I need to make sure that the Dispose() gets called even an exception has thrown. I had added finally block after the catch block.

public object Execute(string SQLStmt)
{
   try {
       m_cmd = new SqlCommand(SQLStmt, Connection);
       object a = m_cmd.ExecuteNonQuery();
       //m_cmd.Dispose();
       return a;
   }
   catch (Exception e) {
       m_oErr = e;
       return null;
   }
   finally {
       m_cmd.Dispose();
   }
}

So even the ExecuteNonQuery() throws an exception, the code in the finally block still will get called. Definitely this solves my problem.

For more detailed information, you can refer to this article - Connection Pooling and the "Timeout expired" exception FAQ .

Excel Drop Down List

When inputting data into Excel, often you will find yourself have to type the same data into cells. And now, in Excel, you can create a drop down list for the cell, instead of typing the same data over and over again. A drop down list in Excel makes data entry easier, avoids typo error in a cell, and limit entries to certain items.

Next, I will show you how to create a drop down list step by step.

Imagine that we want to create a drop down list which contains a list of sections as below:

Now we have the source for the drop down list.

Highlight cells E2 to E10 to create a drop down list for each cell within this range.

Click the Data | Validation. A Data Validation dialog box will pop out.

Go to Settings tab. Choose List from the Allow drop down menu. Click on the icon from the Source field.

Highlight the sources from cell A1 to A18. Press Enter and click OK.

Now all of the selected cells in the Section column should have a drop down list, as below:

That’s it. Cheers!

How to Highlight a row in DataGrid

In this post I will discuss about how to highlight a row in datagrid when mouse moves over a row, by using onmouseover and onmouseout javascript events.

Datagrid has two events, ItemCreated and ItemDataBound, which allow us to access the data items and create an event handler for the events. ItemCreated event is raised when an item in the datagrid control is created, while ItemDataBound event is raised after an item in the datagrid control is created.

In this example, I will work with the ItemDataBound event, adding handlers for onmouseover and onmouseout javascript events for each of the row in the datagrid.

As you can see from the code snippet below, a row will be highlighted when mouse over and switched to normal when mouse out.

private void dgRows_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
     ListItemType itemType;
     itemType = (ListItemType)e.Item.ItemType;

     if(itemType == ListItemType.Item || itemType == ListItemType.AlternatingItem)
     {
         e.Item.Attributes.Add("onmouseover", "this.style.backgroundColor = 'gray';");
         e.Item.Attributes.Add("onmouseout", "this.style.backgroundColor = '#FFFFFF';");
     }
}

That’s it. Cheers!