Writing Excel Files for Download

[mavalon]

I've been struggling with this code for days, and hope someone can help. I have an asp.net page having a datagrid that displays results from a stored procedure. When the user clicks a button, an Excel file is created and the user downloads that file. Really, I would prefer if i didn't have to create a file on the server every time, since it's only needed for the user to download. I'd prefer if it was stored in memory until it was downloaded and then release it from memory. Is something like that even possible? Also, I CAN'T FIGURE OUT HOW TO KILL THE OBJECT. no matter what I do, it seems that the Excel application is still running. How can i kill the processes???? I found this article, which didn't help at all:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q317109

Any suggestions would be greatly appreciated. Here is my code:


Private Sub CreateExcel(ByVal strFile As String, ByVal strQry As String)

' get datatable
Dim dt As System.Data.DataTable = New System.Data.DataTable()
dt = GetData(strQry)

Dim EXL As New Microsoft.Office.Interop.Excel.Application()
Dim xlBooks As Microsoft.Office.Interop.Excel.Workbooks = EXL.Workbooks
Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = xlBooks.Add
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet = EXL.ActiveSheet

With wSheet
Dim i As Int32 = 0
Dim c As Int32 = 1
For i = 0 To dt.Columns.Count - 1
.Cells(1, c).Value = dt.Columns(i).ColumnName.ToString
c += 1
Next

.Range("A1:Z1&quot.Select()
With EXL.Selection.font
.Name = "Verdana"
.FontStyle = "Bold"
.Size = 12
End With

For i = 0 To dt.Rows.Count - 1

For c = 0 To dt.Columns.Count - 1
.Cells((i + 2), (c + 1)).Value = dt.Rows(i).Item(c).ToString
Next

Next

End With

wSheet.Range("A1:Z" & (dt.Rows.Count + 1)).Select()
EXL.Selection.Columns.AutoFit()
wSheet.Range("A1:Z1&quot.Select()
With EXL.Selection
.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
End With

' Format numbers
wSheet.Range("A2:Z" & (dt.Rows.Count + 1)).Select()
With EXL.Selection.Font
.Name = "Arial"
.FontStyle = "regular"
.Size = 11
End With

wSheet.Range("A1:Z" & (dt.Rows.Count + 1)).Select()
EXL.Selection.Columns.AutoFit()

Try
wSheet.SaveAs(Server.MapPath("1" & strFile))
Catch ex As Exception
End Try

' Clean up
NAR(wSheet)
xlBook.Close(False)
NAR(xlBook)
xlBooks.Close()
NAR(xlBooks)
EXL.Quit()
NAR(EXL)
EXL = Nothing
GC.Collect()

End Sub

Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Catch
Finally
o = Nothing
End Try
End Sub

 

[Custom24]

Yep - you've run into 2 problems.

First, there is a problem with releasing excel, though it can be solved. Check the vb.net forum.

But secondly, there is no point in using the excel application on the server for the reasons you've specified and also because Excel was not designed to be used in such a way - it is a desktop application, not a server one.

So, try this

thread855-668886

Mark

 

[mavalon]

Thanks for your suggestion. However, one of the complaints we are currently getting from users, is that the current codes (using the stringwriter) is that the excel files they download are not formatted. They want the columns resized and the header bold, etc.

Is there any way to accomplish this while avoiding the problems i noted?

Also, I HAVE checked out all the forums I could. And the code I posted uses the techniques suggested in those forums/articles. (I only start my own threads as a last resort.) If you know of any useful links, please advise.

Thanks

 

[Custom24]

If you need the formatting, then you must look at a third way of doing it.

My suggestion is to keep an excel file formatted the way you like as a template, copy this file, use Jet OleDb to insert contents into it, and then make the finished file available for download.

http://support.microsoft.com/default.aspx?scid=KB;en-us;q278973

This brings disadvantages in that the user must click on a link to download the file once it is prepared, and you must have cleanup code to avoid filling your server with files, but you might be able to circumvent that by writing the bytes of the file directly to the output stream.

The other disadvantage is that jet must touch the disk to read and write files. For a really high performance optimization of this, you might consider making a RAM disk so that this is done in memory rather than on disk.

I have not tried all of this, so good luck!

Mark

Mark