Export to Excel feature - Need advice please

[Yazster]

Hi all,

I'm looking for some advice on the best way to proceed with an Export to Excel feature in a reporting application.

I have an application that allows users to run ad-hoc reporting against many data sources. We have a couple hundred people using the application at any given time.

One of the features on the application is to allow users to export the entire results of their queries to an Excel spreadsheet (when viewings results on-screen, we use paging allowing only 15 records to be displayed at a time). We have this working, however we have noticed high memory-usage on our web servers (web-farm 5 machines). We're concerned it's the Excel feature causing problems, so I'm wondering if our method is sound, and if not, what better ways are there.

Basically when a users selects to export to excel, a stored proc is executed using the selected parameters, and the results populated into a SQLDATAREADER. We use the following call to call a function:

DataSetToExcel.ConvertToExcel(dr, Response)

This executes the following function residing in one of our classes:

Public Shared Sub ConvertToExcel(ByVal dr As SqlDataReader, ByVal response As HttpResponse)

        response.Clear()
        response.Charset = ""
        response.ContentType = "application/vnd.ms-excel"
        response.AddHeader("Content-Disposition", "attachment;filename=AIC_Export.xls")

        Dim objSB As StringBuilder = New StringBuilder("")
        Dim intFieldCnt As Integer

        objSB.Append("<style>.text { mso-number-format:\@; } </style>")
        objSB.Append("<Table cellspacing=""0"" cellpadding=""0"" border=""1"" bordercolor=""#000000"" width=""100%"" style=""font-size:8pt;"">")

        'Column formatting
        'Header captions
        objSB.Append("<TR bgcolor=""#D0D0D0"">")
        For intFieldCnt = 0 To dr.FieldCount - 1
            objSB.Append("<TH>" & dr.GetName(intFieldCnt) & "</TH>")
        Next
        objSB.Append("</TR>")

        'Data fields
        Dim strTextFields As String = AvalancheFunctions.GetExcelFormatFields("TEXT")
        While dr.Read()
            objSB.Append("<TR>")
            For intFieldCnt = 0 To dr.FieldCount - 1
                'objSB.Append(IIf(InStr("CLASS;DIST CAT;CPN;FPN;MPN;BRANCH CODE;SHIPTO #;CUST NO;ORDER NO;AUTHORIZATION CODE;AUTH CODE", UCase(dr.GetName(intFieldCnt))) > 0, "<TD class='text'>" & dr.GetValue(intFieldCnt) & "</TD>", "<TD>" & dr.GetValue(intFieldCnt) & "</TD>"))
                '(see line below) STYLE=""vnd.ms-excel.numberformat:@"" was added for Excel 97 users
                objSB.Append(IIf(InStr(strTextFields, UCase(dr.GetName(intFieldCnt))) > 0, "<TD class='text' STYLE=""vnd.ms-excel.numberformat:@"">" & dr.GetValue(intFieldCnt) & "</TD>", "<TD>" & dr.GetValue(intFieldCnt) & "</TD>"))
            Next
            objSB.Append("</TR>")
        End While
        objSB.Append("</Table>")

        response.Write(objSB.ToString())
        response.End()

    End Sub

The recordsets can often be 30,000+ records, 30-40 columns.
Users can often wait for 4 or 5 minutes for their workbooks, which for some odd reason are often much bigger than they should be. The user will be prompted to open/save the workbook, which display excessively large workbooks. After opening the workbook, if the user chooses to save it, the SAVE AS TYPE shows Web Page (*htm,*html), not Microsoft Excel Workbook, which is significantly smaller in size.

So I'm wondering why the file type is not a workbook when being sent to the user, and is this causing my slow-downs/memory usage?

And does anyone have a better technique that would perform faster and perhaps be more memory-friendly?

By the way, small recordsets work fine, speed is good, file size is small. Large recordsets are hurting.

Any help/advice would be greatly appreciated.
Thank-you in advance,
Yazster

 

[BoulderBum]

30,000+ records are always going to be slow to deliver.

Can you page the data?

Can you have a low-priority thread creating Excel spreadsheets then e-mailing them when it's done (perhaps only if the resultset is larger than X records)?

Also, can you use a reporting tool?

 

[Yazster]

Thanks for you reply.

I am paging the data, I only display 15 records at a time on-screen, showing page x of y. When the user chooses to go to the next page, the stored proc is executed again returning the next 15 records. The paging is being handled at the stored proc level.

This is often ok when the users find what they want in the first few pages, which we try to accommodate, but sometimes users want to take all the data and play with the numbers or work with the information off-line.

We use a combination of on-screen reporting and Crystal reporting. We tend to prefer using datagrids/repeater controls as we have more control over the end result we're looking for.

We use to use the e-mail technique, but we started to have congestion and long waiting periods, as more and more requests came in, we had 4 physical machines processing the reports. At the time, we were using VB6, we've now upgraded to .Net

Our initial strategy was to try to reduce requests for longer datasets, by providing more search filters. Oddly enough, users simply prefer to have all their data in Excel and play with the numbers themselves.

So if we were to use the e-mail system, I suppose we'd have to have dedicated reporting PCs processing the requests in a VB application for example rather then ASP to not use resources on the web servers?

I guess this method is better then what we're using, since it guarantees we won't be hitting the servers with 10 simultaneous requests for 10,000+ records.

 

[BoulderBum]

So if we were to use the e-mail system, I suppose we'd have to have dedicated reporting PCs processing the requests in a VB application for example rather then ASP to not use resources on the web servers?

You can still keep it in the .NET arena, you'd just have a physically seperate tier (load balanced cluster that can scale out) and initiate the processing through, for example, a Web service.

If you want to add extra spice, you might be able add a second Web service that delivers progress percentage so you can have an AJAX progress bar or something and never have the users leave your site (while still keeping the load on the server cluster hosting the reporting service).

 

[dvannoy]

30k+ is as you know alot of records.. I have some reports that have around 5k records. what I do is use a SP to a dg and then export that dg to excel..works very well.

 

[ca8msm]

You can still keep it in the .NET arena, you'd just have a physically seperate tier (load balanced cluster that can scale out) and initiate the processing through, for example, a Web service.

That's the route I'd go with. Have a web service that is responsible for accepting report requests, running the report and saving a resulting excel file.

From the users point of view, they can click an "Export To Excel" button and then told there report will be created shortly and to check a downloads page (which will basically be a file system view of where the web service saves the excel files).


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

one suggestion in the code that you have used, dont save the data in memory (like in string builder). save the data directly to a file (name can be random). This saves memory in ASP.NET side. After the export is over allow the users to download that file.

you can probably have a routine that will clean up the folder containing such files based on some time like 1 day old etc...

Known is handfull, Unknown is worldfull

 

[Yazster]

If I were to create a web service to process the Excel requests, am I better off processing it pretty much the same way I am right now? Basically user clicks the export to excel button, a web service grabs this requests, processes the query into a SQLDatareader, and uses a function similar to the one I listed previously, except I would modify it to save to a particular location on my web server, and notify the user to go pick it up. I can see all the pieces coming together, I'm just not sure about how to physically have the web service create the Excel workbook and drop it to the drive.

VBKris, you mentioned that I should save the data directly to a file rather than in memory using the string builder, how would I go about this? Would using the response.write command for every row instead of only at the end solve this?

 

[vbkris]

>>VBKris, you mentioned that I should save the data directly to a file rather than in memory using the string builder, how would I go about this? Would using the response.write command for every row instead of only at the end solve this?

nope, open a filestream and rather than appending to the StringBuilder use the filestream's write method to write it to a file. therefore .NET will max hold memory worth 1 row (which is there in the reader).

Data now simply passes from the reader to the file.

dont have a sample now. but a search on google ought to get you all the info...

Known is handfull, Unknown is worldfull

 

[BoulderBum]

If I were to create a web service to process the Excel requests, am I better off processing it pretty much the same way I am right now?

You could. Like I was saying, you can even potentially have your website behave similarly to how it does now, but transparently deliver the spreadsheet from the reporting cluster via a Web service call initiated with AJAX (or just a page request using an iframe).

The user would be visiting your site, fetch the spreadsheet as usual, but you'd alleviate the load on your Web server and gain the potential for something like a progress bar.

If you're going the attachment route, however, vbkris is absolutely right whether or not you use a separate reporting cluster.