Export large volumes of data to excel files with ado.net

[josecarlo]

Hi,

I'm using asp.net to generate excel files with data read from another source (Oracle for example). I'm using Oledb data provider for ado.net (.net framework 1.1). When I generate a small excel file everything goes fine, but when I generate large excel files (i.e. 500K), I get the following error:

"The process cannot access the file ... because it is being used by another process".

I have found that in the case of large files, the connection object maintains the file open even after I close the connection object.

The relevant code is this:

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "12345.xls" & _
";Extended Properties='Excel 8.0;HDR=No;'")
conn.open()
... send data to the file...
conn.close()
//with small files, the .xls file shows its real size (with the new data) and the connection object doesn't keep the file opened.
//but with large files, the .xls file maintains its original size, in my case, 25 K, and all the data that I've sent is not showed, and I get the previous error. I think the data is stored in a cache in memory before being sent to the file.

When I shut down the aspnet_wp.exe, the file is freed and the .xls file now shows its real size, it means, 500K. So, I don't lose the data.

Is there any way to control the content of this buffer? I have found that if I close and open the connection in regular intervals, the data is sent to the file in small blocks, it fixes the problem, but I'm looking for another solution.

Thanks in advance

José Carlos



JOSE CARLOS ARAMBURU
josecarloaa@hotmail.com

 

[dvannoy]

try adding conn.dispose and set conn = nothing to make sure you are actually closing your connection.

 

[josecarlo]

well, I already did that, but it didn't work.
To complete the code, this is what I did:

Try
conn.Open()
...
Catch ...
....
Finally
conn.Close()
conn.Dispose()
conn = Nothing
Dim fi As New FileInfo(Request.PhysicalApplicationPath & _
Me._excelFile)
Response.Clear()
Response.ClearHeaders()
Response.ClearContent()
Response.ContentType = "application/vnd.ms-excel"
Response.AppendHeader("Content-disposition", "inline; filename=" & fi.Name)
Response.AppendHeader("Content-Length", fi.Length.ToString())
Response.WriteFile(Request.PhysicalApplicationPath & Me._excelFile)
Response.End()
End Try

The exception occurs in the WriteFile() method, because it tries to open the file to send the content in the response. I know that aspnet_wp.exe is keeping the file opened because I checked it with the ProcessExplorer tool from SysInternals. As I said, it works fine with small files. And it even works with large files, but rarely ever.

José Carlos





JOSE CARLOS ARAMBURU
josecarloaa@hotmail.com

 

[dvannoy]

this is how I do it with a pdf

Response.Clear()
Response.Buffer = True
Response.AddHeader("Content-Type", "application/pdf")
fs = New IO.FileStream(ExportFileName, IO.FileMode.Open)
FileSize = fs.Length
Dim bBuffer(CInt(FileSize)) As Byte
fs.Read(bBuffer, 0, CInt(FileSize))
fs.Close()
Response.BinaryWrite(bBuffer)
Response.Flush()
Response.Close()

you can change that to excel format and it should work.

 

[dvannoy]

sorry also

Dim fs As IO.FileStream
Dim FileSize As Long

 

[josecarlo]

Ok, thanks, but I think that Response.WriteFile() tries also to create a FileStream object. And there is where it fails.

This is the stack trace:
[IOException: The process cannot access the file "c:\inetpub\

http://wwwroot\bingos\pp007\cache_excel\47522268.xls"

because it is being used by another process.]

System.IO.__Error.WinIOError(Int32 errorCode, String str) +614

System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, Boolean useAsync, String msgPath, Boolean bFromProxy) +888

System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share) +45

System.Web.HttpResponse.WriteFile(String filename, Boolean readIntoMemory) +75

System.Web.HttpResponse.WriteFile(String filename) +7
....







JOSE CARLOS ARAMBURU
josecarloaa@hotmail.com

 

[dvannoy]

OK, it sounds like it could a permissions issue or maybe something like a virus program is locking that file?

also, I don't see a Server.MapPath in your code. that could have something to do with it as well..

Dim File As String = Server.MapPath(".") & "/FileName.xls"

 

[dvannoy]

also check this out

http://www.aspnetpro.com/NewsletterArticle/2003/09/asp200309so_l/asp200309so_l.asp