Filter Objects while loading Workbook or Worksheet
Possible Usage Scenarios
Please use LoadOptions.LoadFilter property while filtering data from the workbook. But if you want to filter data from individual worksheets, then you will have to override the LoadFilter.StartSheet method. Please provide appropriate value from the LoadDataFilterOptions enumeration while creating or working with LoadFilter.
The LoadDataFilterOptions enumeration has the following possible values.
- All
- BookSettings
- CellBlank
- CellBool
- CellData
- CellError
- CellNumeric
- CellString
- CellValue
- Chart
- ConditionalFormatting
- DataValidation
- DefinedNames
- DocumentProperties
- Formula
- Hyperlinks
- MergedArea
- PivotTable
- Settings
- Shape
- SheetData
- SheetSettings
- Structure
- Style
- Table
- VBA
- XmlMap
Filter Objects while loading Workbook
The following sample code illustrates how to filter charts from the workbook. Please check the sample excel file used in this code and the output PDF generated by it. As you can see in the output PDF, all charts have been filtered out of the workbook.
Filter Objects while loading Worksheet
The following sample code loads the source excel file and filters the following data from its worksheets using a custom filter.
- It filters Charts from worksheet named NoCharts.
- It filters Shapes from worksheet named NoShapes.
- It filters Conditional Formatting from worksheet named NoConditionalFormatting.
Once, it loads the source excel file with a custom filter, it takes the images of all worksheets one by one. Here are the output images for your reference. As you can see, the first image does not have charts, the second image does not have shapes and the third image does not have conditional formatting.
This is how to use the CustomLoadFilter class as per worksheet names.