ASP.NET/VB.NET: Export DataGrid to MS Excel.

Here's a quick snippet that will export a datagrid's contents to Microsoft Excel. This assumes that you have a button named btnExport on your webform and of course Excel installed on your pc.

Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExport.Click

Dim sw As New StringWriter
Dim hw As New HtmlTextWriter(sw)

Try

Response.Clear()
Response.AddHeader("content-disposition", "attachment; filename=test.xls")
Response.Charset = ""
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = "application/vnd.xls"
Me.dgMain.RenderControl(hw)
Response.Write(sw.ToString)
Response.End()

Catch ex As Exception

Me.lblMsg.Text = ex.Message

Finally

sw = Nothing
hw = Nothing

End Try

End Sub

ASP.NET Page Caching (aka Page Jumping)

I was recently having an issue with page jumping. Page jumping? Ya, that was my question as well and unfortunately the way it was being described to me by my end users. It seemed that when a customer service agent would view a customer the next agent who performed a search was "jumped" to the previous agents customer rather than the intended one.

It turns out that it had nothing to do with any kind of page jumping at all. The fact that it was being described to me in that way is what threw me off. After further investigation I determined page caching to be the culprit.

Because there were so many agents using the system, if another agent did a search for a customer and loaded the UI within a minute from the last search they were presented with a cached page of the last agents search. Walla! Instant page jumping (I described it as an undocumented feature, HA!).

.Net provides many different ways to control caching. I had used the @Output Cache Duration="1" VaryByParam="none" in my page declarations thinking that I was doing the right thing. However, (I wrote the code a long time ago) just the VaryByParam="none" alone was wrong. I tried setting the vary by to star (*) but I continued to have issues. I could have also turned off caching completely at the sacrifice of performance but this was not what I wanted. So, after a little searching (thanks Google) I found a simple solution.

In my Page_Load event I included the following little snippet:

Response.Cache.SetCacheability(HttpCacheability.NoCache)

The "page jumping" is an issue no more with the ability to turn off caching at an individual page level rather than an entire app.

Recipe: Reading a text file into an ADO Recordset.

Recently frustrated with trying to handle multiple imports into Microsoft Access® I decided to research if there was an easier way of handling external text files similar to .NET's simplicity of reading a text file into a stream or data set only with VBA instead. What I came across was the following article:

Much ADO about Text Files: http://msdn2.microsoft.com/en-us/library/ms974559.aspx

The gist of this article was that with a few lines of code, I would no longer need to import my data to work with it, I could use an in memory (ADO Recordset) copy of it instead. To accomplish this is rather simple, so simple in fact I can not believe I have not been using it all along.

So in a nutshell, here is a really simple example (I highly recommend visiting the aforementioned link for a much greater in-depth look, especially using/creating schema.ini files or using similar functionality with late binding techniques):

I assume that you have...

• Microsoft Access® with a new Module open.
• A sample delimited file: C:\TestData.csv with a header row (optional schema.ini). For my example I have a header row FirstName,LastName and a few first and last name records delimited by commas.
• Familiar with some basic VBA syntax

Public Sub TextToADO()

'Create a variable that holds the location of your text file.
Dim myTextFile As String
myTextFile = "C:\TestData.csv"

'Create the connection string that will connect to the file. For more info
'on the Extended Properties of the string refer to the above link, however
'as you have probably guessed, HDR stands for Header and you should
'adjust accordingly.
Dim myCnnString As String
myCnnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & myTextFile & ";" & _
"Extended Properties=text;HDR=Yes;FMT=Delimited"

'Create an ADO recordset variable and a sql string for the select statement.
Dim rs As ADODB.Recordset, sql As String
sql = "SELECT * FROM TestData.csv"
Set rs = New ADODB.Recordset
rs.Open sql, myCnnString, adOpenStatic, adLockOptimistic, adCmdText

While Not rs.EOF

Debug.Print rs("LastName") & ", " & rs("FirstName")

rs.MoveNext
Wend

rs.Close
Set rs = Nothing

End Sub

As anyone who is familiar with Access knows, it can balloon in size rather rapidly and if you don't regularly compact and occasionally decompile it, it can adversely affect performance. What this solution provides is a way of not having to import data that I want to work with (reducing Access' tendency to grow) but rather reading it into memory so it can be manipulated as if it were an actual table in my database. This was a fantastic find and it really got me thinking about ways to further use what I had learned, like linking to external text files. I have always linked to an external Excel spreadsheet but I had never given linking to a text file any thought before.

What this taught me was that I'm (we) are always learning and what one person takes for granted as assumed knowledge may be a hidden gem for someone else, hence this blog.

I hope you found this useful and as beneficial as I did.