An Exception of type 'System.Outofmemoryexception' occurred in mscorlib.dll but was not handled in user code Asp.net

Asked

Viewed 670 times

6

I’m having a hard time solving this mistake.

My goal is to collect data from an Excel file and store it in c#variables, which in turn will be inserted into SQL Server 2012.

Reading is done line by line.

Here’s the code where the error happens: 

Excel.Application excelApp = new Excel.Application();
Excel.Workbook WB = null;
Excel.Worksheet WS = null;
excelApp.Visible = false;


Convert.ToString(FileUpload1.PostedFile.FileName);
Response.Write(FileUpload1.PostedFile.FileName);



string excelpath = FileUpload1.PostedFile.FileName;
Convert.ToString(excelpath);

string workbookPath = excelpath;

WB = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
WS = excelApp.Worksheets.get_Item("" + TextBox2.Text); 


String connectionString =
   "Data Source=localhost;" +
   "Initial Catalog=Teste;" +
   "User id=sa;" +
   "Password=123;";

Excel.Range last = WS.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
Excel.Range range = WS.get_Range("A1", last);
object ranger = range.Value;

Error:

An Exception of type 'System.Outofmemoryexception' occurred in mscorlib.dll but was not handled in user code Asp.net

c# sql-server asp.net excel range

  • Do you really have to store everything in variables? https://msdn.microsoft.com/pt-br/library/system.outofmemoryexception(v=vs.110). aspx

  • yes to then make a query to enter them correctly in the database. but the problem is not there

  • please I really need help

  • How many lines are being read?

  • Close to 400 rows and 142 columns. I know it is possible using these methods but something is going wrong .

  • Creates a table similar to the spreadsheet in the DB, then inserts row by row not to load everything at once in the variable, applies its business rule in this table, transports the data to the respective tables and clears the manipulation table at the end of the process. Or request a memory UP on the server.

Show 1 more comment

2 answers

0

Outofmemoryexception has no way to recover in the traditional way, it is launched when your application runs out of enough memory, this gives the fata to read 400*142 entire strings to memory, you could have fun, read the first line and send the data, then read the second and send the data, do not read everything at once if you will not run out of memory even. this ranger is getting the value of the WHOLE spreadsheet, tries to make two of them each for a section of the spreadsheet,or maybe three.

-1

As the error message indicates, your system has run out of memory. Your excel file has 400 rows and 142 columns, which, by the size, makes it impossible to put everything in memory. So what I suggest is that you stick to the file, line by line, and write directly into the database. This way your variables end up being rewritten not consuming memory.

Browser other questions tagged c# sql-server asp.net excel range

You are not signed in. Login or sign up in order to post.