Out of Memory when reading an excel file with ClosedXML

0

I have an interface on the web, with MVC, c # and js, when I attach the file to the input, it keeps loading and after a while it gives me the message outofmemory.

The file is read, saved in a datatable and then inserted in bd. There is some method or how I can do it to avoid this saturation. I attach the example of the code I'm using ClosedXML .

 using ( XLWorkbook workBook = new XLWorkbook(path))
            {
                //var workBook = new XLWorkbook(path);
                //Read the first Sheet from Excel file.
                IXLWorksheet workSheet = workBook.Worksheet("Customer View");


                //Loop through the Worksheet rows.
                bool firstRow = true;
                foreach (IXLRow row in workSheet.Rows())
                {
                    //Use the first row to add columns to DataTable.
                    if (firstRow)
                    {
                        foreach (IXLCell cell in row.Cells())
                        {
                            dt.Columns.Add(cell.Value.ToString());
                        }
                        firstRow = false;
                    }
                    else
                    {
                        //Add rows to DataTable.
                        dt.Rows.Add();
                        int i = 0;

                        foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
                        {
                            dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                            i++;
                        }
                    }
                }
                workSheet = null;
            }
// INSERTO A SQL
 try
            {
                using (var cmd = new SqlCommand("InsertSQLby") { CommandType = CommandType.StoredProcedure })
                {

                    cmd.Parameters.Add(new SqlParameter("@myTableType", dt));
                    cmd.Connection = conexion;
                    conexion.Open();
                    cmd.ExecuteNonQuery();
                    conexion.Close();

                }
            }

The Excel has 114 records and around 30 columns, anyway I would like to read only some columns and not all, someone has the idea of how I can do it?

    
asked by Richard 24.09.2018 в 22:11
source

0 answers