Ten Laws to Design By

Ten Laws to Design By

Exporting webforms to Excel files

In this post I will explain a simple way to generate excel files out of webforms using mime types.

It is very easy, just follow this steps:

1. In the "@ page" declaration, add these attributes: EnableViewState="false" CodePage="1252". By disabling viewstate you prevent some errors when opening the file with Excel and the CodePage is for allowing unicode characters to be displayed correctly in the worksheet.

2. In the Page_Load handler, type the following line:
     Response.ContentType = "application/vnd.ms-excel"
    It is important to use this value because others (application/excel and application/ms-excel) does not work always.

3. Do not use CSS files. Since your content will be generated as an Excel file, you cannot link to external files. To allow styles, put all your css in the same page.

That's it, by performing these steps you can now export an aspx page to a Excel file.



Related articles by Zemanta

• VBA/VBScript to Load Excel Files In A Folder Into SQL Server 2000 table (edugeek.net)
• Create A List Of Excel Worksheets (lockergnome.com)
• Excel Optical Illusions Week #24 (excelhero.com)
• Create A List Of Excel Worksheets (lockergnome.com)
• Top 3 Websites To Download Useful Free Excel Programs (makeuseof.com)
• Excel Templates - Free Excel Templates, Excel Downloads, Excel Charts, VBA Macros and More | Chandoo.org - Learn Microsoft Excel Online (chandoo.org)
• Using the Right Excel File Extension (brighthub.com)
• Using a Microsoft Excel Reconciliation Template for Your Banking (brighthub.com)
• Creating Cross Browser HTML5 Forms Now, Using modernizr, webforms2 and html5Widgets (useragentman.com)

Readable queries

It is important to every developer, to write semantic query to allow himself or other to easily understand what a query is intended to do. In this post I'm going to describe a simple technique that I use every day to make my database queries easy to read and understand.

Let us suppose that we have the following database:

Casing
Write your queries using Pascal Casing, i.e, capitalize the first character of every word as in:

Select * From Student

Joins

When writing 'joins', put the main entity in the first place. For instance, if I need to know all the students and their courses, notice that here I am talking about the students, so the query would look like this:

Select *

From Student s Join StudentCourse sc

On s.StudentId = sc.StudentId

 
In this query, notice that I used an alias for each table, since my tables are named using Pascal Casing, I can use the first character of every word for compounding the corresponding alias. Also notice that 'On' statement is indented used one tab which commonly is equivalent to four white spaces. The comparison of values for the join is made following the same order of the tables, first for Student and then for StudentCourse.

If you need to write longer joins as in 'students with their corresponding payment and the courses', the query would be written as follows:

      Select *

      From Student s Join Payment p          
         On s.StudentId = p.StudentId
      Join StudentCourse sc
         On s.StudentId = sc.StudentId

 
Here I put the second join in a new line and keep the intent for the on statement. This is very useful when writing very long queries that imply joining data between several tables.

Select
In the select statement put the columns separated by comma in the same line except for those that either has an alias or is inside any function or both like this:

Select Name, LastName, Hobby,

Datepart(Year, Isnull(DoB, getDate())) Year,

Amount, Description

From Student s Join Payment p

   On s.StudentId = p.StudentId

 
Notice that in this query, the Select and the From are separated by a blank line, this is easily separate the sections of the query. This query is written in Ms Sql Server but the idea applies for other engines as well.

Sub-queries
When writing sub-queries, follow the same rules but tab each sub-query according to its level and use the parenthesis as in the following example:

Select *
From Student s Join (
    Select StudentId
    From Payment
    Where Amount > 0
) HavePayments
   On s.StudentId = Havepayments.StudentId

In and not in

Put the in or the not in at the end of the where statement, this avoid confusing when reading the query. Compare these two queries:

Select *
From Student s
Where DoB = getDate()
And Name = 'Bob'
And StudentId Not In(
    Select StudentId
    From Payment
    Where Amount > 0

)

 

Select *
From Student s
Where DoB = getDate()
And StudentId Not In(
    Select StudentId
    From Payment
    Where Amount > 0

)
And Name = 'Bob'

The first query is easier to understand because after the not in there is no more query to read so you don't have to keep in mind the rest of the predicates in the where statement.

Conclusion

This techniques help to write more semantic queries, easy to read and easy to understand. The examples given above are written in sql server but the main idea can be applied to other database engies.

Every query is different and no rules can be taken so seriously because you cannot force your query requirements fit these recommendations, but certainly you will notice the benefits of applying these techniques to your queries specially then they turn very long and complex.