Accessing cells eats up process memory, lots

[Chuck Strohm]

I have an ASP website application in C# that reads out of an Oracle DB and writes to cells of an Excel sheet. I'm writing to over 550,000 cells on the particular sheet. The writing is done methodically, all columns in one row, then move to the next row. There is no random jumping from cell to distant cell. Each cell that is written is written as a DateTime, Number or Text. A cell with no data to be written is not accessed.

My problem is that the ASP process on my XP Pro PC that's running this application balloons up to about 375MB of memory used by the time the writing process is done. If I run this from the actual Win 2K server, the process on there will go up to more than 750MB used before the application finishes.

I've narrowed the memory usage down to the lines of code that access the spreadsheet cells. And surprisingly, it doesn't seem to matter if there is an actual write operation performed - it appears to be all I have to do is access a cell for read or write, and the memory usage goes ballistic.

I've tried to force C# to do garbage collection, optimized everything I can see, but nothing helps. The memory usage doesn't go down on it own either. It just stays up at the huge usage until the process is restarted. I do all cell access thru an IWorksheet variable opened on an IWorkbook.

Any thoughts? TIA

[Melba Winshia]

Hi Chuck,

Could you please provide me a sample in which you are able to reproduce the issue? It would help me in investigating further on this issue.

Thanks,
Melba

[Chuck Strohm]

I wasn't sure how much code to show. Let's try this:

1 IWorksheet sRaw_Data;
2 sRaw_Data = xl.Worksheets[sheetName];
3
4 switch (DBcol_type)
5 {
6 case SSCOLTYPE.COLTYPE_DATE:
7 sRaw_Data.Range[rowsWritten, (i + 1)].DateTime = DateTime.Parse(qValues[i].ToString());
8 break;
9
10 case SSCOLTYPE.COLTYPE_NUMBER:
11 sRaw_Data.Range[rowsWritten, (i + 1)].Number = Convert.ToDouble(qValues[i].ToString());
12 break;
13
14 case SSCOLTYPE.COLTYPE_TEXT:
15 sRaw_Data.Range[rowsWritten, (i + 1)].Text = qValues[i].ToString();
16 break;
17 }

[Melba Winshia]

Hi chuck,

Thank you for the code snippets. The performance issue (speed and memory consumption) is something we are working on currently. However, since there are major changes being made to the code it can also take a little bit more time to get a stable version out, but its something we are looking into very seriously. We regret for the inconvenience caused. I assure you that the next release will be more optimized with respect to time taken and memory usage. Please let me know if you have any concerns regarding this issue.

Thanks,
Melba