A feature of a legacy website that I support is the ability to download a data extract. After the application is deployed, the extract appears to complete successfully but the file downloaded only doesn't contain any data in any of the tabs. The logic is that a blank Excel template of 10 sheets (containing only the headings) is populated from data read from a database and inserted into the tabs.
// Copy the template to the relevant folder
const string templateFilename = "DataExtractTemplate.xlsx";
var templateFullPath = Server.MapPath($"~/Administration/Templates/{templateFilename}");
var templateFileInfo = new FileInfo(templateFullPath);
var temporaryFile = Server.MapPath($"~/Administration/ExtractOutput/{templateFilename}");
templateFileInfo.CopyTo(temporaryFile, true);
templateFileInfo = new FileInfo(temporaryFile) { IsReadOnly = false };
// Download the file to the user's downloads folder
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=DataExtract.xlsx");
Response.AddHeader("Content-Length", templateFileInfo.Length.ToString());
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.TransmitFile(templateFileInfo.FullName);
Response.Flush();
Response.Close();
Response.End();
templateFileInfo.Delete();
I have confirmed that the data is being loaded into the datatable, and as far as it appears, each row is inserted into each worksheet of the copied template, but the file that I end up with is identical to the file I started with, i.e. an empty template. I have confirmed that the file is being copied correctly on the server and that the parameters are being populated from the data using the code below.
// Get the relevant OleDbType based on the column type
internal static OleDbType GetOleDbType(string systemType)
{
OleDbType returnType;
switch (systemType)
{
case "Double":
case "Int32":
returnType = OleDbType.Numeric;
break;
default:
returnType = OleDbType.VarChar;
break;
}
return returnType;
}
// Create delimited list of column names
var columnParams = dataTable.Columns.Cast<DataColumn>()
.Aggregate(string.Empty,
(current,
column) => current + $"@{column.ColumnName},");
columnParams.TrimEnd(',');
// Create parameters for each row and insert into the worksheet
int insertedRows = 0;
foreach (DataRow dataRow in dataTable.Rows)
{
oleCmd.Parameters.Clear();
oleCmd.CommandText = $"INSERT INTO [{tabName}$] VALUES ({parameterValues})";
var columnCount = 0;
var parameters = new List<OleDbParameter>();
foreach (DataColumn column in dataTable.Columns)
{
var dataColumnName = column.ColumnName;
var dataTypeName = column.DataType.Name;
var dataRowItemValue = dataRow[columnCount];
var isItemNull = string.IsNullOrEmpty(dataRowItemValue.ToString());
var parameter = new OleDbParameter { ParameterName = $"${dataColumnName}", SourceColumn = dataColumnName, OleDbType = GetOleDbType(dataTypeName) };
if (!isItemNull)
{
switch (dataTypeName)
{
case "Double":
case "Int32":
case "DateTime":
parameter.Value = Convert.DBNull;
break;
default:
parameter.Value = string.Empty;
break;
}
}
else
{
var cellValue = dataRow.ToString();
switch (dataTypeName)
{
case "Double":
parameter.Value = double.Parse(cellValue);
break;
case "Int32":
parameter.Value = int.Parse(cellValue);
break;
default:
parameter.Value = cellValue;
break;
}
}
parameters.Add(parameter);
}
oleCmd.Parameters.AddRange(parameters.ToArray());
insertCount += oleCmd.ExecuteNonQuery();
rowCount++;
}
Each ExecuteNonQuery succeeds, i.e. returns 1, yet no data is written to the template. Oddly, it seems to work for a similar extract that uses nearly all the same code, but the output is corrupted. This extract has 21 columns of data for 4-500 rows and this is the error I get when opening it:
Best guess is data not being pulled on the server. The code looks ok, and as noted works during testing local. Did any of the data pull + downloads EVER work on the server?
I mean, obviously the connection path used on production will be VAST different. You might want to add some test page to the application with a few buttons such as "database connect test". Or a button to pull rows, and shove the row count into a text box on the same page.
Remember, the production IIS server version running HAS TO match the bit size of the installed version of Access data engine. So, enable x32 bit application pool if you using Access x32. I
I would also NEVER use ANY cpu, but force the project to run as x32 bits, or force the project to run as x64 bits. As noted, if you force project to run as x64 bits, then the x64 bit version of the Access data engine has to be installed on the server.
So, that path to the temp file? Gee, did some previous code place that file in the temp folder? I'm not all that convinced that placing a Excel file in the windows temp folder is a good idea. I would create a folder in the project, and use sever.mappath() to get that file location, and that Excel file thus should be placed in a better folder location, since on a hosted server, you may well have some rights and restrictions for the windows folder.