Export Datagrid to Excel? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Export Datagrid to Excel?

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-01 : 11:32:06

A quick question. When I export my datagrid into Excel using data reader, it will not export any records that has empty field. Is there a way to allow it to export even when a record has some empty field?

Example:

ID Description Qty Price
012 Tape 12 2.50
013 Rubber 6 10.00
014 Plastic

With the above example from my datagrid, all will transfer except for ID 014 which is the plastic since qty and price is empty. Any ideas?

Here's an example of my code:


Sub Export_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim sbExcel As New StringBuilder
        Const midDelimiter = ""","""
        Const endDelimiter = """"
        Const tnDelimiter = ""","
        Const ntDelimiter = ","""
       	
  	Dim connectionString As String
        Dim user As String = System.Configuration.ConfigurationSettings. _
            AppSettings("mappedname")
        Dim pass As String = System.Configuration.ConfigurationSettings. _
            AppSettings("mappedkey")
        connectionString = "data source=wse-vis; Database=" & Session("AuthDB")
        connectionString &= ";user id=" & user
        connectionString &= ";password=" & pass

     
        Dim strConnect As String = connectionString

        Dim conExport As New SqlConnection(strConnect)

        'create select statement
        Dim sbSelect As New StringBuilder
        With sbSelect
            .Append("SELECT id, line_no, name, contact_first_name + ' ' + contact_last_name as contact, ")
            .Append("contact_phone as phone, state, q.user_6 as endUser, description, ")
            .Append("isnull(calc_unit_cost,0) as calc_unit_cost, ")
            .Append("case isnull(calc_unit_cost,0) when 0 then 0 else (unit_price-calc_unit_cost)/ calc_unit_cost end as mu,  ")
            .Append("unit_price, qty, unit_price * qty as total, quote_date, printed_date, ")
            .Append("case win_probability when 1 then case status when 'w' then win_probability else 0 end else win_probability end as win_probability, ")
            .Append("expected_win_date, followup_date, expiration_date, won_loss_date, won_loss_reason, territory, status ")
            .Append("from quote q, quote_line ql, quote_price qp ")
            .Append("where q.id = ql.quote_id ")
            .Append("and (ql.quote_id = qp.quote_id and ql.line_no = qp.quote_line_no) ")
            .Append("and status in (")
            .Append(dlStatus.SelectedItem.Value)
            .Append(") ")
            .Append("and territory like '")
            .Append(dlTerritory.SelectedItem.Value)
            .Append("' ")
            .Append(gstrDateFilterExp)
            .Append("order by id, line_no")
        End With

        Dim cmdExport As New SqlCommand(sbSelect.ToString, conExport)
        conExport.Open()
        Dim drExport = cmdExport.ExecuteReader(CommandBehavior.CloseConnection)

        'emit header labels for data
        With sbExcel
            .Append(endDelimiter)
            .Append("ID").Append(midDelimiter)
            .Append("Line_No").Append(midDelimiter)
            .Append("Name").Append(midDelimiter)
            .Append("Contact").Append(midDelimiter)
            .Append("Phone").Append(midDelimiter)
            .Append("State").Append(midDelimiter)
            .Append("End User").Append(midDelimiter)
            .Append("Description").Append(midDelimiter)
            .Append("Unit Cost").Append(midDelimiter)
            .Append("Mark Up").Append(midDelimiter)
            .Append("Unit Price").Append(midDelimiter)
            .Append("Qty").Append(midDelimiter)
            .Append("Total").Append(midDelimiter)
            .Append("Quote Date").Append(midDelimiter)
            .Append("Printed Date").Append(midDelimiter)
            .Append("Probability").Append(midDelimiter)
            .Append("Quote No.").Append(midDelimiter)
            .Append("Expected Date").Append(midDelimiter)
            .Append("Followup Date").Append(midDelimiter)
            .Append("Expiry Date").Append(midDelimiter)
            .Append("Won-Loss Date").Append(midDelimiter)
            .Append("Won-Loss Reason").Append(midDelimiter)
            .Append("Territory").Append(midDelimiter)
            .Append("Status").Append(endDelimiter)
            .Append(Chr(13))
            '			.Append("<br>")
        End With

        ' interate through the datareader
        Do While drExport.read()
            ' emit data
            With sbExcel
                .Append(endDelimiter)
                .Append(drExport.Item("id").ToString).Append(tnDelimiter)
                .Append(drExport.Item("line_no")).Append(ntDelimiter)
                .Append(drExport.Item("name").ToString).Append(midDelimiter)
                .Append(drExport.Item("contact").ToString).Append(midDelimiter)
                .Append(drExport.Item("phone").ToString).Append(midDelimiter)
                .Append(drExport.Item("state").ToString).Append(midDelimiter)
                .Append(drExport.Item("endUser").ToString).Append(midDelimiter)
                .Append(Replace(drExport.Item("description").ToString, """", """""")).Append(tnDelimiter)
                .Append(drExport.Item("calc_unit_cost")).Append(",")
                .Append(drExport.Item("mu")).Append(",")
                .Append(drExport.Item("unit_price")).Append(",")
                .Append(drExport.Item("qty")).Append(",")
                .Append(drExport.Item("total")).Append(ntDelimiter)
                .Append(drExport.Item("quote_date").ToString).Append(midDelimiter)
                .Append(drExport.Item("printed_date").ToString).Append(tnDelimiter)
                .Append(drExport.Item("win_probability")).Append(ntDelimiter)
                .Append(drExport.Item("id").ToString).Append(midDelimiter)
                .Append(drExport.Item("expected_win_date").ToString).Append(midDelimiter)
                .Append(drExport.Item("followup_date").ToString).Append(midDelimiter)
                .Append(drExport.Item("expiration_date").ToString).Append(midDelimiter)
                .Append(drExport.Item("won_loss_date").ToString).Append(midDelimiter)
                .Append(drExport.Item("won_loss_reason").ToString).Append(midDelimiter)
                .Append(drExport.Item("territory").ToString).Append(midDelimiter)
                .Append(drExport.Item("status").ToString).Append(endDelimiter)
                .Append(Chr(13))
            End With
        Loop

        'Send the data to the browser as a CSV file
        Response.ContentType = "application/vnd.excel"
        Response.AddHeader("Content-Disposition", "attachment; filename=quoteLog.csv")
        Response.Write(sbExcel.ToString)
        Response.End()
    End Sub

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-01 : 11:57:32

I forgot to mention that any field that within the datagrid that has no value will not export that record, but any other fields that's not within the datagrid will export it regardless if it's empty or not.

Example:



ID	Description	Qty	Price	Date
012	Tape		12	2.50	01/01/2004
013	Rubber		6	10.00

As you can see this example compared with the first example, the Date range is not within the datagrid, but it will export it when I export it to Excel even though some fields has Null value.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-01 : 12:31:04

After careful walkthrough the code section that was provided, it seems like 3 of the fields needs to be filled in for it to export the record to excel.


.Append(drExport.Item("line_no")).Append(ntDelimiter)

.Append(drExport.Item("qty")).Append(",")

.Append(drExport.Item("territory").ToString).Append(midDelimiter)

By looking at those code, anyone might know why it's required to have values in those field in order to export that specific record to Excel? I see no difference from any other fields and I'm stumped.

Any help?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-01 : 13:27:52

You should explicitly convert the contents of ALL columns ToString() -- not just some of them -- for the export, since you are building strings.

Do you have OPTION STRICT ON ?

- Jeff

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-01 : 14:34:52

Jon,

It seems like it's my query that's the problem. It's pulling record base on my "where clause" so that's why it's not pulling any that is within my clause.

Do you know how I would use a Union ALL within ASP.NET?

Base on my query I provided, I had created a second similar but will have different criteria to pull those records that it's no pulling.

Example:



.APPEND("SELECT ID, LINE_NO, NAME, ETC..... ")
.APPEND("FROM QUOTE Q LEFT OUTER JOIN QUOTE_LINE QL ")
.APPEND("ON Q.ID = QL.QUOTE_ID ") 
.APPEND("LEFT OUTER JOIN QOUTE_PRICE QP ")
.APPEND("(ON QL.QUOTE_ID = QP.QUOTE_ID AND QL.LINE_NO = QP.QUOTE_LINE_NO) ")


.APPEND("UNION ALL ")

.APPEND("SELECT ID, LINE_NO, NAME, ETC..... ")
.APPEND("FROM QUOTE Q LEFT OUTER JOIN QUOTE_LINE QL ")
.APPEND("ON Q.ID = QL.QUOTE_ID ") 
.APPEND("LEFT OUTER JOIN QOUTE_PRICE QP ")
.APPEND("ON QL.QUOTE_ID = QP.QUOTE_ID ")

My UNION ALL here does not seem to take affect at all, so I'm not sure what I need to do.

Any Help?

BTW I have OPTION EXPLICIT ON and OPTION STRICT OFF. Should I have it turn on?

jhermiz

3564 Posts

Posted - 2005-06-01 : 15:20:10

Funny he calls you Jon and calls me Jeff...boy cant you feel the love!

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-01 : 15:21:20

1. My name is "Jeff" :)

2. Read up on what Option Strict Does, and why it is important. It should always be on. You should never allow for implicit conversions in your code.

3. If it is a SQL Server problem, shut down Visual Studio, Open up query analyzer, and get to work. Don't test/troubleshoot SQL by appending and building string variables in some other language and then by executing them in custom applications! Don't make things more complicated! If you did this right away, how much time would you have saved if you don't think your VB.NET code was causing the dropped rows in the output? One step at 1 time, isolate things and work on them independently and be sure it all works before combining technologies and putting the pieces together.

4. And then, when you are done, put it in a stored procedure. I'm pretty sure we've suggested using stored procedures to you somewhere between 2 and 100 times so far ... Again, if you had done this in the first place, you wouldn't have wasted anyone's time examining the VB.NET client code.

- Jeff

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-01 : 15:36:12

Sorry guys. I keep mixing the two of you all the time. Will double check name in the future before referencing either one of you.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-01 : 15:46:22

This Query was done in Query Analyzer before putting it into my code. The problem was not noticable until we try generating the application and I wasn't sure where the source of the problem was until looking at the query again. That is why I posted and hopefully someone can see if I did messed up in my code or not.

The UNION ALL also works fine in Query Analyzer with both query, but will not work in my program when combined like the above example. If I knew what to do, this wouldn't seem so complicated, but I'm stuck that's why I post.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-01 : 15:47:40

So why aren't you using stored procedures still even after it has been suggested numerous times? It'll simplify things for you and also for us.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-06-01 : 16:15:47

Hi Tara,

True it will be easier to work with if it's stored procedure. Not sure how to incorporate the SP with my variables since I'm using quite a few of them. For example like drop downs, dates, which database they login, userid and password, and a ton of session variables. I just thought easier if I test the query in QA and programmed it straight into my application.

Also I'm not sure how to incorporate that with the exporting to excel in this section.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources