[RESOLVED] Export more than one worksheet (tabs) to Excel

[MMock]

This question will be easier than my other Excel thread.

There are actually two questions. I just saw that the ASP app I'm migrating is creating an Excel file with three worksheets. What do I need to do differently to create three tabs?

Also, I need to do some formatting. Attached is a screenshot of an example. I'm not sure if it all needs to be a gridview. If I were putting this to a page, I'd have the gridview be just the four headings and the one row.

I know nothing about exporting to Excel, except for what I started learning yesterday afternoon and this morning, (which has been a disaster because of Excel 2007 problems). Please forgive my beginner-level questions.

Thanks for the help.

[MMock]

I guess I needed a template.

I've got one, thanks to the previous ASP programmer!

So now I have a couple questions. The code I found from google doesn't do exactly what I want. So I want a mixture of Besoup's code (thread http://www.vbforums.com/showthread.php?t=521066 ) and the code I just found that opens more than one worksheet. Here is the new code:

        ' Create Excel Application, Workbook, and WorkSheets

        Dim xlExcel As New Excel.Application

        Dim xlBooks As Excel.Workbooks

        Dim xlBook As Excel.Workbook

        Dim xlSheets As Excel.Sheets

        Dim stdSheet As Excel.Worksheet

        Dim xlCells As Excel.Range

        Dim sFile As String

        Dim sTemplate As String

        Dim rescSheet As Excel.Worksheet



        sFile = Server.MapPath(Request.ApplicationPath) & "\Excel.xls"


        ' Formatted template the way you want. 

        ' If you want to change the format, change this template

        sTemplate = Server.MapPath(Request.ApplicationPath) & "\XLTemplate.xls"

        xlExcel.Visible = False : xlExcel.DisplayAlerts = False

        ' Get all workbooks and open first workbook

        xlBooks = xlExcel.Workbooks

        xlBooks.Open(Server.MapPath(Request.ApplicationPath) & "\XLTemplate.xls")

        xlBook = xlBooks.Item(1)

        ' Get all sheets available in first book 

        xlSheets = xlBook.Worksheets

        ' Get first sheet, change its name and get all cells

        stdSheet = CType(xlSheets.Item(1), Excel.Worksheet)

        stdSheet.Name = "First Sheet"

        xlCells = stdSheet.Cells

        ' Fill all cells with data 

        GenerateExcelFile(ds.Tables(0), xlCells) 'Fill in the data



        ' Get second sheet, change its name and get all cells

        rescSheet = CType(xlSheets.Item(2), Excel.Worksheet)

        rescSheet.Name = "Second Sheet "

        xlCells = rescSheet.Cells

        ' Fill all cells with data

        GenerateExcelFile(ds.Tables(1), xlCells)


        ' Save created sheets as a file 

        xlBook.SaveAs(sFile)



        ' Make sure all objects are disposed

        xlBook.Close()

        xlExcel.Quit()

        ReleaseComObject(xlCells)

        ReleaseComObject(stdSheet)

        ReleaseComObject(xlSheets)

        ReleaseComObject(xlBook)

        ReleaseComObject(xlBooks)

        ReleaseComObject(xlExcel)

        xlExcel = Nothing

        xlBooks = Nothing

        xlBook = Nothing

        xlSheets = Nothing

        stdSheet = Nothing

        xlCells = Nothing

        rescSheet = Nothing

        ' Let GC know about it 

        GC.Collect()



        ' Export Excel for download

        ' -- This is saying page not found: Response.Redirect(sFile)

It is doing exactly what I want except it is trying to open the Excel file in a page (which doesn't work but that's ok, it's now what I want to do anyway). What I want to do is launch and go to Excel. Is it obvious what I need to change?

Also, the two code snippets are very different... Is the above inferior to Besoup's?

Thanks.

[MMock]

You probably want to see this piece of code too:

    ' Generates Excel sheet for the given DataTable's data

    Private Sub GenerateExcelFile(ByRef table As DataTable, ByVal xlCells As Excel.Range)

        Dim dr As DataRow, ary() As Object

        Dim iRow As Integer, iCol As Integer

        'Output Column Headers

        For iCol = 0 To table.Columns.Count - 1

            xlCells(1, iCol + 1) = table.Columns(iCol).ToString

        Next

        'Output Data

        For iRow = 0 To table.Rows.Count - 1

            dr = table.Rows.Item(iRow)

            ary = dr.ItemArray

            For iCol = 0 To UBound(ary)

                xlCells(iRow + 2, iCol + 1) = ary(iCol).ToString

                Response.Write(ary(iCol).ToString & vbTab)

            Next

        Next

    End Sub

[mendhak]

I'll assume that the excel code works and generates what you want. What do you mean you want to launch and go to excel, do you want to do a Response.WriteFile() to the user so that they get the open/save/cancel dialog?

[MMock]

I'll assume that the excel code works and generates what you want. What do you mean you want to launch and go to excel, do you want to do a Response.WriteFile() to the user so that they get the open/save/cancel dialog?

Yes.

[MMock]

I must be doing the Response.WriteFile in the wrong context. It is not opening Excel, it is dumping the file contents, in binary, to my aspx page in the browser. I see the file contents, then the original page that drives the creation of the Excel file follows.

I'll keep investigating, I'm sure I'm just missing an instruction or two, but if you happen to check back to this thread, this is where I left off.

Thanks mendhak.

[mendhak]

It's saving the file

xlBook.SaveAs(sFile)

So you could just do a Response.WriteFile(). However, since you're doing Response.Writes() to create the excel file in the first place, you may be running into conflicting situations.

Either save the file, then response.writefile() it, or generate the file with the content type set to excel. Your code in post 2 creates an .xls file on the hard disk on the server. But post 3 has a Response.Write in it, is that necessary?

[MMock]

Wow, your icon just got a lot different!

Anway, here is my code. An interesting (and much desired) side-effect is that I am no longer getting the message about the excel file being the wrong format or corrupted...which was my other thread (I'll resolve it next!).

So here are the working parts:

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        GenerateThreeTabs(CInt(Me.ddAgent.SelectedValue))

    End Sub

    Sub GenerateThreeTabs(ByVal sUsrIk As Integer)
        Dim ds As New DataSet

        Dim connString As String = _
            ConfigurationManager.ConnectionStrings("RESNEW").ConnectionString

        Dim conn As New SqlConnection(connString)

        Dim command As New SqlCommand()
        command.CommandType = CommandType.StoredProcedure
        command.Connection = conn

        command.CommandText = "up_AgentCommissions"

        command.Parameters.Add(New SqlParameter("@usr_ik", Data.SqlDbType.Int))
        command.Parameters("@usr_ik").Value = sUsrIk

        Dim adapter As New SqlDataAdapter(command)
        Try
            adapter.Fill(ds)
        Catch ex As Exception
            Response.Write("Submit() Exception " + ex.Message)
            conn.Close()
            conn.Dispose()
            conn = Nothing
            Exit Sub
        End Try

        ' Create Excel Application, Workbook, and WorkSheets
        Dim xlExcel As New Excel.Application
        Dim xlBooks As Excel.Workbooks
        Dim xlBook As Excel.Workbook
        Dim xlSheets As Excel.Sheets
        Dim stdSheet As Excel.Worksheet
        Dim xlCells As Excel.Range
        Dim sFile As String
        Dim rescSheet As Excel.Worksheet
        Dim rescSheet2 As Excel.Worksheet

        sFile = Server.MapPath(Request.ApplicationPath) & "\Excel.xls"

        xlExcel.Visible = False : xlExcel.DisplayAlerts = False

        ' Get all workbooks and open first workbook
        xlBooks = xlExcel.Workbooks
        xlBooks.Open(Server.MapPath(Request.ApplicationPath) & "\Commissions.xls")
        xlBook = xlBooks.Item(1)

        ' Get all sheets available in first book 
        xlSheets = xlBook.Worksheets

        ' Process the summary sheet with results from stored proc's first query - tables(0).
        stdSheet = CType(xlSheets.Item(1), Excel.Worksheet)
        xlCells = stdSheet.Cells
        GenerateSummaryTab(ds.Tables(0), xlCells)

        ' Get the month details sheet with results from stored proc's second query - tables(1).
        rescSheet = CType(xlSheets.Item(2), Excel.Worksheet)
        xlCells = rescSheet.Cells
        GenerateMonthDetails(ds.Tables(1), xlCells)

        ' Get the meter signup history sheet with results from stored proc's third query - tables(2).
        '  Also pass in the agent's name from tables(0).
        rescSheet2 = CType(xlSheets.Item(3), Excel.Worksheet)
        xlCells = rescSheet2.Cells
        GenerateMeterSignupHistoryTab(ds.Tables(0).Rows(0).Item("Agent").ToString, ds.Tables(2), xlCells)

        xlExcel.Visible = True


        ' -- I'm not sure what of these can be deleted and what I should execute.  I'm just so happy right now the
        ' --  process is working!

        ' Save created sheets as a file 

        ' xlBook.SaveAs(sFile)

        ' Make sure all objects are disposed

        'xlBook.Close()

        'xlExcel.Quit()

        'ReleaseComObject(xlCells)

        'ReleaseComObject(stdSheet)

        'ReleaseComObject(xlSheets)

        'ReleaseComObject(xlBook)

        'ReleaseComObject(xlBooks)

        'ReleaseComObject(xlExcel)

        'xlExcel = Nothing

        'xlBooks = Nothing

        'xlBook = Nothing

        'xlSheets = Nothing

        'stdSheet = Nothing

        'xlCells = Nothing

        'rescSheet = Nothing

        '' Let GC know about it 

        'GC.Collect()

        ' Response.WriteFile(sFile)

        ' Export Excel for download

        ' -- This is saying page not found: Response.Redirect(sFile)
        ' Response.Redirect(sFile)

        ' ShowItInExcel()


    End Sub

    Private Sub GenerateMonthDetails(ByRef table As DataTable, ByVal xlCells As Excel.Range)

        Dim dr As DataRow, ary() As Object

        Dim iRow As Integer, iCol As Integer

        'Output Column Headers

        For iCol = 0 To table.Columns.Count - 1

            xlCells(1, iCol + 1) = table.Columns(iCol).ToString

        Next

        'Output Data

        Try

            For iRow = 0 To table.Rows.Count - 1

                dr = table.Rows.Item(iRow)

                ary = dr.ItemArray

                For iCol = 0 To UBound(ary)

                    xlCells(iRow + 2, iCol + 1) = ary(iCol).ToString

                Next

            Next
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try

    End Sub

    Private Sub GenerateSummaryTab(ByRef table As DataTable, ByVal xlCells As Excel.Range)

        xlCells(1, 2) = table.Rows(0).Item("Agent").ToString
        xlCells(5, 2) = table.Rows(0).Item("Commission").ToString
        xlCells(5, 3) = table.Rows(0).Item("Usage").ToString
        xlCells(5, 4) = table.Rows(0).Item("Storage").ToString
        xlCells(5, 5) = table.Rows(0).Item("Margin").ToString

    End Sub

    Private Sub GenerateMeterSignupHistoryTab(ByVal sAgentName As String, ByRef table As DataTable, ByVal xlCells As Excel.Range)

        Dim iRow As Integer

        xlCells(1, 1) = sAgentName

        For iRow = 0 To table.Rows.Count - 1

            xlCells(iRow + 2, 2) = table.Rows(iRow).Item("mnth").ToString

            xlCells(iRow + 2, 3) = table.Rows(iRow).Item("cnt").ToString

        Next

    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)

    End Sub

[mendhak]

Wow, your icon just got a lot different!

It's one of my original ones. Ever noticed the ?

And I've actually (actually) been meaning to tell you, it's about time you got one too!

Anway, here is my code. An interesting (and much desired) side-effect is that I am no longer getting the message about the excel file being the wrong format or corrupted...which was my other thread (I'll resolve it next!).

I'm not sure what's been changed, but how are you writing the excel file out now? And if it works for you, go ahead and delete the commented out code. Boy, if I were in charge of your code reviews...

[MMock]

Code review?

[MMock]

Ever noticed the ?

You must be kidding. How could I *not* have noticed it?!

[MMock]

And I've actually (actually) been meaning to tell you, it's about time you got one too!

Okay, I'll post next week and ask how.

[mendhak]

Code review?

Sorry, just being self-depreciating about my pedantry. Looking forward to the avatar

(I'll forget) (Have a nice weekend)

[szlamany]

Wow - between these two threads - this was quite a read...

At any rate - I'm about to do the exact same thing. We had been giving our customers access to ODC files that open in EXCEL. The ODC file is a little XML standard from MS that stores connection info and a database/table or view to open in the app.

We want to instead have them go into our new webpage - choose some data to look at - and have us open them into excel with that data.

This appears to be what you are doing here.

Would you mind posting some of you now cleaned up code - both the HTML and the VB side?

Thanks in advance!

[MMock]

The cleaned up code is just what I posted before without the chunk of commented-out code at the bottom.

The html code is really nothing, I select from two dropdownlists then hit the submit button. However, there is this, I'm not sure if it's necessary. It's something I threw in when I was looking up what was causing my problems:

<%@ Page Language="VB" EnableEventValidation ="false"  AutoEventWireup="false" CodeFile="Commissions.aspx.vb" Inherits="rbs_Reports_Commissions" %>

Is that enough for what you need, szlamany?