Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

Exporting a ASP.Net page to Excel - Can I do page breaks?

Hi,

My client needs to be able to print a report (17 actuall) being generated in ASP.Net.  All of the already have a Export to Excel function.  Is there any way to set up some code with default 'safe' page breaks and headers?

Thanks,
DP
0
porter416
Asked:
porter416
  • 8
  • 7
1 Solution
 
smolamCommented:
How are you exporting to Excel?  Are you just converting HTML and sending it as content-type:excel, or are you using the Excel COM object to create the reports?
0
 
smolamCommented:
Or are you talking about printing directly from a webpage?
0
 
porter416Author Commented:
Hi,

I do it as follows from the ASP.Net page that opens Excel in the browser.  This is being used on a corporate intranet. The HTML tables are being run on the server to make this happen.

    Private Sub btnExportToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
        Response.ContentType = "application/vnd.ms-excel"
        Response.Charset = ""
        Me.EnableViewState = False
        Dim tw As New System.IO.StringWriter()
        Dim hw As New System.Web.UI.HtmlTextWriter(tw)


        lblTitle.ForeColor = Color.Transparent
        Label4.ForeColor = Color.Transparent
        Label6.ForeColor = Color.Transparent
        Label7.ForeColor = Color.Transparent

        lblTitle.RenderControl(hw)
        tblCriteria.RenderControl(hw)
        lblBlindedNotifier.RenderControl(hw)
        tblAdditionalCriteria.ForeColor = Color.Transparent
        tblAdditionalCriteria.RenderControl(hw)
        tblReport.ForeColor = Color.Transparent
        tblReport.RenderControl(hw)
        Response.Write(tw.ToString())
        Response.End()
End Sub
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
smolamCommented:
If you are doing it that way then unfortunately you won't be able to create those default headers and breaks, this is because it doesn't actually create an excel file persay, it fools the browser into interpreting the HTML table as an Excel file which Excel "inteligently" breaks the HTML Cells into excel cells.  If you want to create the desired effect there are 2 ways I would suggest, not easy though:

1) Use the Excel COM object (OLEAutomation) to create the Excel reports first before sending that file to the browser, with that you have full control over Excel as if you were running VBA, there fore you can format the data however you want it to be displayed.  It is a lot of work to get this working perfectly but it does have its advantages. http://www.eggheadcafe.com/articles/20021012.asp

2) This way is possibly easier than the first but still requires work, and that would be to creat a "Printer Friendly" page of the reports.  What you will need to do is actually break the tblReport into smaller chunks, so that they fit onto an A4 sheet when you print, then you can use the javascript print() function to tell the browser to print.  This again isn't perfect because you need to force the data to fit into an area that sometimes it just can't fit!

I have been trying to find another easier way to do this for ages and have given up, I am hoping that with the next version of Internet Explorer that they make this easier for programmers.

HTH
0
 
porter416Author Commented:
Hmm.

With option 1, it seems as though I have to specify exactly which label and html tables goes to which cell, ifi I understand it correctly.  I tried doing something similar with option 2 and just a web page but again, that is a lot of work and if they change printers later on, I am hosed (right?).  

Your way looks like it will work but I have 17 reports to do all with lots of headers and the like.  :(
0
 
porter416Author Commented:
Is there another Response.ContentType I can use?  I only chose Excel as it looked like a good comprimise back when I discovered printing from a web server is a security risk.

Thanks,
DP
0
 
smolamCommented:
Actually getting the data into Excel is actually fairly simple.  The hard part is understanding how Excel Automation works.  All you would do is open up the same DataReader/DataSet you use to create your reports and then loop through and write the Excel File.

I think it is a better way for printing too because as you rightly point out that for option 2 that each printer your user uses will have different settings for each printer which could potentially throw it out any sizing that you have done, at least Excel deals with all that itself!
0
 
smolamCommented:
You could try and use Crystal Reports and Save it to PDF?
0
 
porter416Author Commented:
I really appreciate your patience.  :-)

Would be willing to try CR (I have VS2003 and CRv10) or SQL RS if I could take ASP.Net reports that are already formatted and validated by the user (they had a 1500 item check list, page breaks was 1 of 30 'oops').  I am just worried about making a change to something that already works.  I don't suppose it's possible to 'throw' a web page at a Crystal Report or RS and get it to try and format it for me?  From what I know, I'd have to re-run all the SQL a second time when it goes to CR or RS.

Some of the pages have calls to 3 or 4 stored procedures to get them to come up right.
0
 
porter416Author Commented:
PS I just boosted the points.  I really appreciate your help.
0
 
porter416Author Commented:
FWIW - When I started this project they had 1 P4 desktop running ASP.Net, ColdFusion, SQL2000 + acting as a file server.  I figured using CR would kill the box.  They have since moved the DB to a XEON CPU but the legacy still haunts us.
0
 
smolamCommented:
I am no pro at Crystal Reports, so will help as much as I can but it will probably be a case of the blind leading the blind:  Here is one Useful article I know some of the other experts have recommended: http://aspalliance.com/articleViewer.aspx?aId=265&pId=

If I were you I would leave the old form and then copy it to a new form and play around with CR until you can hopefully get it working before dumping the old way.  As for converting existing HTML to Crystal I am not sure will see what I can find for you though.

Well CR.Net I know is more robust that perviously so hopefully it all works smooth for you!
0
 
smolamCommented:
This is something else I found, might be useful: http://www.codeproject.com/aspnet/HTML2PDF.asp
0
 
smolamCommented:
I don't know what sort of budget that you have but there are also lots of Third Party ASP.Net Components that you can purchase to do what you want to, considering you will have to pay for CR anyway as the one that comes with VS.Net is only a trial?
0
 
porter416Author Commented:
Actually, the CR that comes with VS can be used for web applicaitons if you get permission from CR to do so.  Then you have to figure out which of 4 modules you need to download and add to your setup.exe but that's another story. :-)

I will take some of these to my client and thanks for your help in giving me some alternatives.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now