Load Specific Sheets in Workbook


#1

Hi, I am facing memory issues in my application because currently it is loading entire workbook into memory, so I searched and found this example Load Specific Sheets in a Workbook

I have around 10 worksheets out of which I need to load only 3 worksheets in memory, Could you let me know how it can be done? With given example it seems it can load only 1 worksheet not multiple.

TIA


#2

@rahulsingii,
You can select multiple sheets by modifying the CustomLoad class as follows:

class CustomLoad : LoadFilter
{
    public override void StartSheet(Worksheet sheet)
    {
        if (sheet.Name == "Sheet2" || sheet.Name == "Sheet4" || sheet.Name == "Sheet5")
        {
            this.LoadDataFilterOptions = LoadDataFilterOptions.All;
        }
        else
        {
            // Load nothing
            this.LoadDataFilterOptions = LoadDataFilterOptions.Structure;
        }
    }
}

P.A. Please use LoadDataFilterOptions.Structure instead of LoadDataFilterOptions.None as it is obsolete now.


#3

@ahsaniqbalsidiqui,

Thank you for your response, but won’t it load the entire workbook if any of those sheets are present? Let’s say I have worksheets - Sheet1 to Sheet10 and I want to load Sheet3, Sheet5 & Sheet7, I will add it in the condition but setting “LoadDataFilterOptions.All” will load the entire workbook right meaning Sheet1 to Sheet10?

I tested it, it’s loading entire workbook if this option is set to “All” and my app is crashing due to memory issue.


#4

@rahulsingii,
LoadDataFilterOptions.All means load entire data of that particular sheet and LoadDataFilterOptions.Structure means nothing is copied except the empty sheet. You may go through the enumerator LoadDataFilterOptions and instead of using All for the worksheet, select an appropriate option like CellData to avoid unnecessary memory consumption. Hence All does not mean the entire workbook but entire worksheet and using Structure option, the empty sheet is loaded.

You may have a look at the following article as well to avoid extra memory and CPU consumption while loading data from a workbook.

Filter objects while loading Workbook or Worksheet