Wrox Programmer Forums
|
ASP.NET 1.0 and 1.1 Professional For advanced ASP.NET 1.x coders. Beginning-level questions will be redirected to other forums. NOT for "classic" ASP 3 or the newer ASP.NET 2.0 and 3.5
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 12th, 2007, 11:09 AM
Authorized User
 
Join Date: Jan 2007
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default export from asp.net to excel

ok guys ur help is needed....any hints code snippets will be great.
My "beautifull" client wants a detailed report of the weekly data for his salesmen.He sended me an excel sheet which i should do something similar..actually which i should do..I have no clue were to start.I found many articles on the net and on p2p about this topic but i honestly dont know if its really what i want. I dont know if i should export a datagrid into an excel sheet(Most of the figures in the excel he sended are of datagrids previously made on the web)..so should i do this or i should create a template sheet and fill each cell in this template with the result from the database.If thats teh case then i have no clue were to begin....

 
Old February 12th, 2007, 11:17 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

I have only ever exported a Datagrid into an excel file (I have never had a reason to manually fill cells and such.) To that end, it is very easy to write your datagrid into an Excel file and this article does a wonderful job of how it is accomplished:

http://www.c-sharpcorner.com/UploadF...idToExcel.aspx

Why would you need to fill each cell individually?

================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
 
Old February 12th, 2007, 11:39 AM
Authorized User
 
Join Date: Jan 2007
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

10x for the reply i am checking it right now...
well in the report i need to calculate a percentage (actually i can do that from sql) and according to this average (lets say between 80-90 excellent ...70-80 very good and so on...) i think thats y i need to fill each cell individually...i dont know if its a good idea :S...and i asked a fellow in the office about it and he told me that this is how they do it here and they didnt do it in asp.net before..they did it in VB6 and vb.net...(maybe u are correct there is no need to it like that..i may be try to touch my right ear with my left hand..)
plus the report should be shown at the end of a series of pages starting by the weekly work,then promoters report,promoter detailed report,promoter daily detailed report and a couple of other reports to get to that...
now if i want to use the method u told me about..do i need to rebuild all the datagrids and redo all the sql statements over again (which i should do eventually if i want to fill each cell individually...) all i got till now is this piece of code..
Dim oExcel As New Excel.Application
        Dim oBooks As Excel.Workbooks, oBook As Excel.Workbook
        Dim oSheets As Excel.Sheets, oSheet As Excel.Worksheet
        Dim oCells As Excel.Range
        Dim sFile As String, sTemplate As String
        'Dim dt As DataTable = CType(Application.Item("MyDataTable"), DataTable)

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

        oBooks = oExcel.Workbooks

        oBooks.Open(Server.MapPath(Request.ApplicationPath ) & "\AltadisBook.xls") 'Load colorful template with chart
        oExcel.Visible = True
        oBook = oBooks.Item(1)
        oSheets = oBook.Worksheets
        oSheet = CType(oSheets.Item(1), Excel.Worksheet)
        oSheet.Name = "First Sheet"
        oCells = oSheet.Cells


        'DumpData(dt, oCells) 'Fill in the data

        oSheet.SaveAs(sTemplate)
        oBook.Close()
        'Quit Excel and thoroughly deallocate everything
        oExcel.Quit()
        ReleaseComObject(oCells) : ReleaseComObject(oSheet)
        ReleaseComObject(oSheets) : ReleaseComObject(oBook)
        ReleaseComObject(oBooks) : ReleaseComObject(oExcel)
        oExcel = Nothing : oBooks = Nothing : oBook = Nothing
        oSheets = Nothing : oSheet = Nothing : oCells = Nothing
        GC.Collect()
        'Response.Redirect(sFile) 'Send the user to the file
        Response.Redirect(sTemplate)

which generates an error on "oSheet.SaveAs(sTemplate)"-->Cannot access read-only document 'AltadisBook.xls'.
and this function used to fill a the cells in the excel
Dim dr As DataRow, ary() As Object

        Dim iRow As Integer, iCol As Integer
        'Output Column Headers
        For iCol = 0 To dt.Columns.Count - 1
            oCells(2, iCol + 1) = dt.Columns(iCol).ToString
        Next

        'Output Data
        For iRow = 0 To dt.Rows.Count - 1
            dr = dt.Rows.Item(iRow)
            ary = dr.ItemArray
            For iCol = 0 To UBound(ary)
                oCells(iRow + 3, iCol + 1) = ary(iCol).ToString
                Response.Write(ary(iCol).ToString & vbTab)
            Next
        Next
 which i dont seem to be able to use since
'Dim dt As DataTable = CType(Application.Item("MyDataTable"), DataTable)
is nothing..

i am really lost...
10x in advance




 
Old February 12th, 2007, 11:45 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Judging by your error, the file you are opening is 'Read-Only' so you will not be able to write changes to the document if that is the case.

In so far as the Datagrid vs filling the cells individually that is up to you. If you can write your procedures so that it returns all of the data elements you would need in the excel file, I would bind that data to a datagrid and then export the datagrid. (This requires less effort and seems to be the simplest solution.) If, for some reason you can't get all of the data elements back via SQL then you will have no other choice. (Of course, you could build a dataset pragmatically and bind that to a datagrid and then export from there but, again, it is entirely up to you.)

================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========





Similar Threads
Thread Thread Starter Forum Replies Last Post
Export to excel from ASP jayanp Classic ASP Basics 4 July 31st, 2007 03:01 PM
Export ASP reports to Excel NeedASP Classic ASP Professional 2 November 30th, 2005 07:07 PM
Export from ASP to Excel with pagebreak's Fernandito Classic ASP Basics 1 May 19th, 2004 04:46 PM
How to export XML to Excel using ASP? porsrari Classic ASP Basics 2 September 4th, 2003 09:44 AM
Use ASP/SQL to export to Excel sankar Classic ASP Basics 10 July 18th, 2003 04:04 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.