GetUsedCells doesn't properly limit candidate cells (OutOfMemoryException)

[anilreddy128]

Read and complete the full issue template

Do not randomly delete sections. They are here for a reason.

Do you want to request a feature or report a bug?

• Bug
• Feature
• Question

Did you test against the latest CI build?

• Yes
• No

If you answered No, please test with the latest development build first.

Version of ClosedXML

0.95.4

What is the current behavior?
When reading excel file, getting below Out of memory exception. This only happens on IIS. It works well on develeopment machine while debugging using Visual Studio. Excel file size is just 40KB.

at TestCasesImport.ReadExcelFile(FileUpload fu, String SheetName) in E:\agent_work\1\s\TDMS Web App\TestCasesImport.aspx.cs:line 364 at line 384 In the method ReadExcelFileSystem.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
at System.Linq.Set1.Resize() at System.Linq.Set1.Find(TElement value, Boolean add)
at System.Linq.Enumerable.d__671.MoveNext() at System.Linq.Enumerable.<DistinctIterator>d__641.MoveNext()
at ClosedXML.Excel.XLCells.d__9.MoveNext()
at System.Linq.Enumerable.d__172.MoveNext() at System.Linq.Buffer1..ctor(IEnumerable1 source) at System.Linq.OrderedEnumerable1.d__1.MoveNext()
at ClosedXML.Excel.XLCells.d__8.MoveNext()
at ClosedXML.Excel.XLCells.d__11.MoveNext()
at ClosedXML.Excel.XLCells.<System-Collections-Generic-IEnumerable-GetEnumerator>d__12.MoveNext()
What is the expected behavior or new feature?

Complete this.

No

Regressions get higher priority. Test against the latest build of the previous minor version. For example, if you experience a problem on v0.95.3, check whether it the problem occurred in v0.94.2 too.

Reproducibility

This is an important section. Read it carefully. Failure to do so will cause a 'RTFM' comment.

Without a code sample, it is unlikely that your issue will get attention. Don't be lazy. Do the effort and assist the developers to reproduce your problem. Code samples should be minimal complete and verifiable. Sample spreadsheets should be attached whenever applicable. Remove sensitive information.

Code to reproduce problem:

 string strNewPath = Server.MapPath("~/Uploads/" + strFileName + strFileType);

            using (XLWorkbook workBook = new XLWorkbook(strNewPath))
            {
                try
                {
                    //Read the Sheet1 from Excel file.
                    IXLWorksheet workSheet = workBook.Worksheet("Sheet1");

                    //Create a new DataTable.
                    DataTable dt = new DataTable();

                    //Loop through the Worksheet rows.
                    bool firstRow = true;
                    foreach (IXLRow row in workSheet.Rows())
                    {
                        try
                        {
                            //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(1, dt.Columns.Count))
                                {
                                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                                    i++;
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            Logger.TraceMessage_TDMS(ex.ToString());
                        }
                    }
                    if (dt.Rows.Count > 0)
                    {
                        try
                        {
                            dt = dt.Rows.Cast<DataRow>().Where(row => !row.ItemArray.All(field => field is System.DBNull || string.Compare((field as string).Trim(), string.Empty) == 0)).CopyToDataTable();
                        }
                        catch (Exception ex)
                        {
                            Logger.TraceMessage_TDMS(ex.ToString());
                        }
                    }
                    int groupID = Convert.ToInt16(Session["GroupID"].ToString());

                    ULTestCases.DataSource = dt;
                    ULTestCases.DataBind();
                    lblMessage.Visible = true;
                    lblMessage.Text = "Please verify data displayed in grid and click on Upload Icon on top right corner of the page.";
                }
                catch (Exception ex)
                {
                    Logger.TraceMessage_TDMS(ex.ToString());
                    throw ex;
                }
            }

[Copy of Geiger A HW DVT For TDMS Import_Small.xlsx](https://github.com/ClosedXML/ClosedXML/files/14210967/Copy.of.Geiger.A.HW.DVT.For.TDMS.Import_Small.xlsx)

[jahav]

When trying to determine candidate cells for used cells (in this case the line foreach (IXLCell cell in row.Cells()) below if (firstRow)), it doesn't filter out cells that aren't part of the potential range (in this case 1 row). Instead, it takes all cells in whole worksheet (in this case all cells of columns B, C, F and J, because they contain data validations).

As a consequence, it allocates hundreds of MBs unnecessarily. That likely runs into IIS memory limit.
https://github.com/ClosedXML/ClosedXML/blob/develop/ClosedXML/Excel/Cells/XLCells.cs#L80

As a workaround, use more specific filtering for cells that searches only for content: foreach (IXLCell cell in row.CellsUsed(XLCellsUsedOptions.Contents))

If you are really using old version 0.95.4, it's roughly double the memory. for current develop branch, its about 230MB.

[anilreddy128]

That worked. Thank you.