export data into excel

Sigh...the eternal struggle. As you may know, we have some code, a couple versions actually, of a function that takes a gridview from a webpage and exports it into Excel. 2 problems: 1) sometimes it would break if the gridview used .NET controls it hadn't already been written to handle, and 2) what got put into excel was really an HTML table. Thus if someone tried to import it into certain statistical programs for analysis it might not be correctly read as real data. Not ideal!

A couple months ago, Dave found an article about putting a dataset into excel - nice! You don't even need to make a gridview on a webpage, you could create a dataset and send it straight into an excel file. I spent some time this weekend adding it to the toolbox, and making a version that would work with a datatable*. Then I read about a possible issue - what it is exporting into excel is XML formatted data. This could be a problem, not all statistical software our clients use will be able to import XML is my concern.

A little more research and I found an article by someone who explained how to send data into excel without a gridview, and it's not XML formatted. It essentially sends a tab-delimited, plain text translation of the datatable. I created another class based on this method. I ran into one very odd bug - a Microsoft bug, natch - that if the first column is called 'ID' in caps, it break. No comment. 

Please note that in the case of the both the XML formatted data and the plain data exports, since the gridview is not used, the data in Excel is exactly what is in the dataset/datatable. Therefore, if you have a gridview that formats the phone number for display purposes, but you use one of these classes to export the same data that binds the gridview, the end user will see the un-formatted data.

In conclusion, I plan to implement the plain text to Excel version from now on. It is in the Toolbox under DCReports, called ExportData.cs. The version that creates XML data from your data, then exports, is called Workbook Engine. I added the original sample code, and a version that handles datatables.

Questions? Comments? I long to hear the feedback!  ;)

Helper Functions for gridview

I added a class for helper funtions to format gridview output, that can be called from the gridview in the aspx page like so:

Amount:
< % # DCToolbox.DCReports.GridviewHelpers.FormatShort(Eval("grantStartDate")) %>

FormatShort, for example, takes the object and if it's not a dbnull then it converts it to a short and displays that value, as long as the short is not one of the missing value constants for short in the DCConstants class.

There are only a couple of functions in there so far, please feel free to add more. I didn't find anything like this in the toolbox, but I didn't look hard so please let me know if it's redundant.

Debugging in Visual Studio in Firefox

I just found out how to make the Visual Studio to debug in Firefox:
1. Right-click on the file in the Solution Explorer
2. Select "Browse with"
3. In the dialog box select Firefox. If Firefox is not there, click Add and browse to the firefox.exe on your computer.
4. Click "Set as Default"

The next time you run the project it will be in Firefox. It's quite easy to switch between IE and Firefox. One thing I noticed is that while using Firefox, closing the browser window won't stop the debug mode, you have to use Shift + F5, or some toolbar/menu item that I can't find, to stop debugging.

the membership provider

For my latest project, I decided to try out the built in login/membership stuff that .NET provides. I am following these tutorials. So far it has been easy to follow and the built in stuff has a lot of stuff that is good from a security standpoint. I'll keep you posted!

ORDER BY in a view or a nested select statement

I noticed one different behavior in SQL Server 2005 comparing with 2000:

If you run “select * from (select top 100 percent * from [table name] order by lastname) as temp” on SqlServer 2000 and on SqlServer 2005, you will see the difference. In 2000, the list you get is still sorted while in 2005 it's not.

The bottomline is: don’t say “order by” in a view or in a nested select statement. Putting "order by" in a view is never a good practice, in my opinion, because:
1. You have to say "top 100 percent" in order to do that
2. We should put "order by" in the most outer select so it costs minimum workload for the Sql Server (it only needs to sort the things you need at the end).

Form authentication with SSL

This is the settings we need in the Web.config:
<system.web>
<sessionState timeout="180" regenerateExpiredSessionId="false"/>
<authentication mode="Forms">
<forms name="XXX" loginUrl="login.aspx" protection="All" timeout="180" requireSSL="true"/>
</authentication>
<authorization>
<deny users="?"/>
</authorization>
<customErrors mode="RemoteOnly"/>
<compilation debug="false"></compilation>
</system.web>

DateTime ToString Formats

To format a DateTime:
string a = date.ToString("MM/dd/yyyy"); the string will look like "03/02/2008"

You can also do "MMM dd, yyyy", then you will have "Mar 02, 2008". For the entire list of format options, please see http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx.