Export to excel spreadsheet (XLSX) failing

Question

Using ASP.NET web page, I am exporting some data to excel spreadsheet (XLSX). The code is running fine when I run it using Visual Studio (it is exporting a XLSX file with correct data), but the same code fails when deployed to Testing Server.

It is not throwing any error, it simply exports a blank XLSX file.

Note: While debugging in the test server, I found that the data is getting fetched and temp file is also getting created properly, but the data is not getting written to the temp file (the weird thing is it doesn't through any error).

Added later

After doing some more research, I have found that there is no issue with small record set (say 1000, 2000). But when tried with ~20K records, I get a blank file.

I have been burning myself for last 2 days, someone rescue me :) ...

Code

string templateFile = @"C:\Templates\ExportFile.xlsx";
string tempFileName = Path.Combine(@"C:\Temp\", Path.GetRandomFileName());
tempFileName = Path.ChangeExtension(tempFileName, ".xlsx");
File.Copy(templateFile, tempFileName);

List<Customer> customerList = FetchCustomers();

DataTable dataTableObj = new DataTable("Customers$");
dataTableObj.Columns.Add(new DataColumn("CustomerID"));
dataTableObj.Columns.Add(new DataColumn("FirstName"));
dataTableObj.Columns.Add(new DataColumn("LastName"));
dataTableObj.Columns.Add(new DataColumn("CreatedDate"));

foreach (Customer customerObj in customerList)
{
    DataRow dataRowObj = dataTableObj.NewRow();
    dataRowObj["CustomerID"] = customerObj.CustomerID;
    dataRowObj["FirstName"] = customerObj.FirstName;
    dataRowObj["LastName"] = customerObj.LastName;
    dataRowObj["CreatedDate"] = customerObj.CreatedDate;
    dataTableObj.Rows.Add(dataRowObj);
}

using (OleDbConnection oleDbConnectionObj = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tempFileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\""))
{
    OleDbCommand insertCommand = new OleDbCommand();
    insertCommand.Connection = oleDbConnectionObj;
    insertCommand.CommandText = @"INSERT INTO [Customers$] ([CustomerID], [FirstName], [LastName], [CreatedDate]) VALUES (?, ?, ?, ?)";
    insertCommand.Parameters.Add("CustomerID", OleDbType.Numeric, 0, "CustomerID");
    insertCommand.Parameters.Add("FirstName", OleDbType.VarChar, 0, "FirstName");
    insertCommand.Parameters.Add("LastName", OleDbType.VarChar, 0, "LastName");
    insertCommand.Parameters.Add("CreatedDate", OleDbType.Date, 0, "CreatedDate");

    DataSet dataSetObj = new DataSet();
    dataSetObj.Tables.Add(dataTableObj);

    OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
    dataAdapter.InsertCommand = insertCommand;
    dataAdapter.Update(dataSetObj, "Customers$");
}

Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
Response.AddHeader("Content-Disposition", "attachment; filename=Customers.xlsx");
Response.WriteFile(tempFileName);
Response.Flush();
Response.End();

When you say "the data is getting fetched" you are certain that it isn't an empty data set that is fetched? — abiessu, May 21 '14 at 16:31
Have you checked the MIME types configuration in IIS on the server? — Donal, May 21 '14 at 16:32
I would further @abiessu 's comment and do a simple operation on the file, like add some text to make sure you are actually in the right output file. — Bmo, May 21 '14 at 16:33
What process are you using to verify that the testing server is retrieving ~20K records? I don't see a print or debug statement anywhere to show that information... — abiessu, May 21 '14 at 17:20
For debug purposes I am writing directly to a TXT file and there I can see the total record count for **`dataTableObj`** (just before doing **`dataAdapter.Update(dataSetObj, "Customers$");`**) is ~20K. `I haven't mentioned the debug code here.` As suggested by @Bmo, I will try to write lesser records and see what happens. — addy2601, May 21 '14 at 17:26
With lower number of records (tried for 1000 & 2000) the export is working fine, when I again try to export ~20K records it is exporting blank. — addy2601, May 21 '14 at 18:14
Have you verified the file is being written to your temp location before trying to write to the stream? — Maurice Reeves, May 21 '14 at 18:41

score 5 · Accepted Answer · edited May 23 '17 at 12:16

Finally it got resolved!!!

While creating large XLSX file (greater then ~1MB), OLEDB Provider tries to create temp file at Local Settings\Temporary Internet Files\Content.MSO\ for the APP POOL account. If the folder doesn't exists or the APP POOL account doesn't have proper permission, then it fails without throwing errors (no idea why it doesn't throw error).

In my case Content.MSO folder was missing at C:\Documents and Settings\Default User\Local Settings\Temporary Internet Files.

I created the folder and gave the modify permission to NETWORK SERVICES..... and "Voila!" - everything started working.

Thanks to the below 2 links, they saved me days...:)

Microsoft ACE OLEDB connection creating empty Excel when there are 166,110 rows

http://www.rapidsnail.com/developer/topic/2011/109/2/65194/excel-writes-secret-use-oledb-write-data-to-excel-more-than-13571-line-file-for-blank.aspx

Export to excel spreadsheet (XLSX) failing

1 Answers1

Linked