Run out of memory, Run out Rensponse time

Nepethya Rana

8y1.5k.NET

How do I download Excel file without creating file in server ? size of file is 1000 kb. if multiple users tries to download , the file current process ran out of memory while creating files before downloading. What is the best way to handle the situation? Please help me.

My current Static Web method get called via JavaScript which then query database and creates excel files ( used open xml) with timestamp for every new call and saves in file server , and then it get invoked to download and later files gets deleted from the server.

issues I ran into : i) out of memory exception if many users tries to download the file. ii) sometimes error message "Someone else is working in \Filepath\file.xlsx right now . Please try again later." later pops up.

Could you please guide me?

my code :

java script (angular)
$http({
method: "POST",
url: "MyWebForm1.aspx/ExportExcelFile",
data: {
fileName: xlFile, dateString: dateString, otherParameter : parameters
},
contentType: "application/json",
dataType: "json"
}).then(function (response) {

var responsedata = JSON.parse(response.data.d);
if (responsedata.stat == 4) {
window.location.href = responsedata.redirectUrl;
}
else if (responsedata.stat == 2) {

$scope.onSuccessReturn = false;
$scope.onErrorReturn = true;
$scope.errorMessage = responsedata.message;
}
else {
window.location.href = responsedata.redirectUrl;
}
});
};

Code behind File (Asp.Net web form)
[WebMethod]
public static string ExportExcelFile(string fileName, string dateString, string otherParameter)
{
// Query Db and gets datatable
DataSet ds = new DataSet();
ds.Tables.Add(dt);
string xlfileName = fileName + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
string filePath = fileLocation + xlfileName;

// This method created excel file using OPEN XML
DataSetToExcel dxl = new DataSetToExcel();
dxl.ExportDataSet(filePath, ds);

string urlToInvoke = "~/DownloadForm.aspx?fn=" + xlfileName;

return JsonConvert.SerializeObject(new { Success = true, redirectUrl = VirtualPathUtility.ToAbsolute(urlToInvoke) });
}

Code in download form:
protected void Page_Load(object sender, EventArgs e)
{
string fileName = Request.QueryString["fn"];
string filePath = ConfigurationManager.AppSettings["ExcelFilePath"] + fileName;
Response.ContentType = ContentType;
Response.AppendHeader("content-disposition", "attachment; filename=" + Path.GetFileName(filePath));
Response.TransmitFile(filePath);
Response.Flush();
System.Threading.Thread.Sleep(1000);
if (File.Exists(filePath))
{
File.Delete(filePath);
}
}
Response.End();
}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nepethya Rana8y ago
1. I have given timeStamps on filename. what i am doing here is my program sleeps 1 sec after after gets transmitted and then i delete the file from server
2. I could see if i request for 2 files. It does not take time to create files and to get download. But if i request 4 files. File creation never completes.
How do i manage GC could you please help me by looking at code?

My code that creates excel file. I had it done as static method. but later i changed it to instance. so that each request will have different instance to create excel file. I had the same issue before too.

Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
oUtility.cnConnectionStringSQL = cs;

try
{
oUtility.WriteAudit(oUser, request, "", "Excel Creation Begin", fileName , true);

using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
workbookPart.Workbook.Sheets = workbookPart.Workbook.AppendChild(new Sheets());
WorkbookStylesPart stylePart = workbookPart.AddNewPart();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
foreach (DataTable table in dataset.Tables)
{
WorksheetPart worksheetPart = workbookPart.AddNewPart();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
Sheets sheets = workbookPart.Workbook.GetFirstChild();
string relationshipId = workbookPart.GetIdOfPart(worksheetPart);
uint sheetId = 1;
if (sheets.Elements().Count() > 0)
{
sheetId =
sheets.Elements().Select(s => s.SheetId.Value).Max() + 1;
}
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
// header row
Row headerRow = new Row();
Dictionary columns = new Dictionary();
if (table.Rows.Count == 0)
{
headerRow.Append(ConstructCell("** Nothing for today **", CellValues.String, 2));
sheetData.AppendChild(headerRow);
}
else
{
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName, column.IsNumeric());
headerRow.Append(ConstructCell(column.ColumnName, CellValues.String, 2));
}
sheetData.AppendChild(headerRow);
NumberFormatInfo valueNumberFormatInfo = new NumberFormatInfo() { NumberDecimalSeparator = ".", NumberGroupSeparator = String.Empty };
foreach (DataRow dsrow in table.Rows)
{
Row row = new Row();
foreach (KeyValuePair col in columns)
{
if (col.Value)
{
row.Append(ConstructCell(string.Format(valueNumberFormatInfo, dsrow[col.Key].ToString()), CellValues.Number, 1));
}
else
{
row.Append(ConstructCell(dsrow[col.Key].ToString(), CellValues.String, 1));
}
}
sheetData.AppendChild(row);
}
}
worksheetPart.Worksheet.Save();
stopwatch.Stop();
oUtility.WriteAudit(oUser, request, "", "Excel Creation End", fileName + "is created successfully and saved. Time Taken:" + stopwatch.Elapsed, true);
}
Nilesh Shah8y ago
Hi Gaurav,

the solution is not in code but in implementation.

first about file being locked by another user - to avoid this don't use the plain file names, append some time stamp or user id to it so unique file name is generated for each user for each request, hence there will not be error of file being locked by another user. there is also possibility that your code generating excel file is not releasing all resources, hence the file object seems to be referenced in code. to avoid this use using statements and use GC

second, about run out of memory - avoid generating and responding with files on the spot. this will always create bottle necks. instead of it, take user request, show response that your request is being processed, outsource file generation task to a separate job/service, than later when file is ready, either send user email or show status in some screen. that's how some banks give you pdf statements, etc.