Asp.net using oledb to export excel file returns empty excel file

Question

Hello,

I am using asp.net oledb to export information to excel file. I encounter problems when the information to export becomes too big, in this case the code I have given below, the excel file generated becomes an empty spreadsheet.

If I changed the loop to 1123 for insertion of the rows. The generated excel file is fine, 1125 rows, and 4 columns shown.

A test program in windows form is also working fine regardless of how many rows.

Code has been simplified, "information ..." in the sql insertion command represents 1803 characters.

ExcelObjConn = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
                 "Data Source=" & fileName & ";Extended Properties=Excel 12.0 XML"
    ExcelConnection = New System.Data.OleDb.OleDbConnection(ExcelObjConn)
    ExcelConnection.Open()
Try
        SqlCommand = "CREATE TABLE ABC ([row1] text, [row2] text, [row3] text, [row4] text)"

        ExcelCommand = New OleDb.OleDbCommand(SqlCommand, ExcelConnection)
        ExcelCommand.ExecuteNonQuery()
        ExcelCommand.Dispose()

        For i As Integer = 0 To 1124
            SqlCommand = "Insert into ABC ([row1], [row2], [row3], [row4]) Values ('information...', 'information ...', 'information ...', 'information ...')"
 ExcelCommand = New OleDb.OleDbCommand(SqlCommand, ExcelConnection)
            ExcelCommand.ExecuteNonQuery()
            ExcelCommand.Dispose()
        Next
Catch ex As Exception
Finally
If ExcelConnection IsNot Nothing Then
      ExcelConnection.Close()
      ExcelConnection.Dispose()
End If
End Try

What should I do? Thanks.

Workaround:

Hello, I couldn't find a solution to my problem as well. What I did eventually was to run the process using another separate windows service. The code works perfectly fine running from a windows form or service program, but not asp.net, not sure why. Hope this workaround helps.

Answer 1

I'm having a similar problem. I can export upwards of 42,000 rows on my development machine. But when I run it on my Test server, it inserts the rows, reads the rows back out of the file, and then when I look at the file, it is blank. If I export less than ~9000 rows then it works fine and all the data is in the file.

Did you ever find a solution?

I have tried doing the inserts in smaller batches, but it didn't help. No exceptions are thrown. I installed the 2007 Office System Driver onto my Test server. It's described here - http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

Thanks!

Answer 2

Hi,

I have the same problem. If I try to export more than 9542 the excel file is blank. I have installed the same component but I didn't solve my problem.

Did you find a solution?

Thanks