使用 ASP.NET 网页,我将一些数据导出到 Excel 电子表格 (XLSX)。当我使用 Visual Studio 运行代码时,代码运行良好(它正在导出具有正确数据的 XLSX 文件),但在部署到测试服务器时,相同的代码会失败。
它不会抛出任何错误,它只是导出一个空白的 XLSX 文件。
Note:在测试服务器中调试时,我发现数据正在获取,临时文件也已正确创建,但数据没有写入临时文件(奇怪的是它没有出现任何错误)。
稍后添加
经过更多研究,我发现小记录集(比如 1000、2000)没有问题。但是当尝试使用约 20K 记录时,我得到一个空白文件。
过去两天我一直在烧自己,有人来救我:) ...
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();