You want to apply particular styles or formats, but only to rows or columns that actually contain values. Here are some examples of styles and formats that you might want to apply to specific rows or columns:
- Background/Foreground colors
- Text orientation, justification, indentation, orientation, number format
- Horizontal/vertical alignment
- Cell locked/unlocked when worksheet is protected
Conditional formatting is useful if you wish to apply particular formatting that changes depending on the actual values in the cells. Go to Conditional formatting resources.
A similar technique can be used to format just an area. For specifics, see Format an area instead of rows or columns.
This solution has several steps:
- Define an area that contains the populated cells in order to determine which rows and columns to format.
- Create the style to apply.
- Loop through the rows or columns in the area and apply the style to each row or column.
Get the populate cells in the worksheet. There are two ways to do this:
A. Worksheet.Populated Cells –
Use Worksheet.PopulatedCells to return an area of all the cells that are populated starting with the top left cell that contains a value or formatting, down to the bottom right cells. It includes both cells with values/formulas as well as cells that contain formatting (e.g. background color, conditional format etc.)
B. Use a named range
Add a named range in the template file around the area that will contain the populated cells and then use Worksheet.GetNamedRange(String) or Workbook.GetNamedRange(String) to retrieve the named range. Use Range.Areas to get a handle on the desired area.
This is less expensive than Worksheet.PopulatedCells since ExcelWriter does not need to go through the worksheet to figure out which cells contain values. There are some limitations to this approach:
- The number of columns in the named range will not change after data is imported. If there are columns in the populated named range that do not contain data, the named range won’t reflect that.
- The number of rows in the named range will only change after data is imported if the named range is wrapped around ExcelTemplate data markers and new rows are inserted.
- If you are using Worksheet.ImportData, the named range will not update to reflect where the live data is.
For this example, we want to leave the populated cells unlocked, but leave the rest of the cells locked when the worksheet is protected. This is done by setting Style.CellLocked.
Iterate through the rows/columns with Worksheet.GetColumnProperties(int) or Worksheet.GetRowProperties(int). Call ApplyStyle on the appropriate RowProperties or ColumnProperties with ColumnProperties.ApplyStyle(Style) or RowProperties.ApplyStyle(Style). Applying the style on an entire row or column will consume less memory than applying the styles to individual cells.
Applying Formatting to just an Area instead of Rows or Columns
IMPORTANT: This is only recommended for small areas. Area.ApplyStyle and Area.SetStyle apply the style to each cell within the area, which increases the time and memory needed to process the file. Applying styles on a per-row or per-column basis is the recommended best practice since the style is applied to the RowProperties or ColumnProperties object, instead to each individual cell.
- Getting Started with Conditional Formatting in ExcelApplication
- How to format alternating color rows using ExcelTemplate
- How to format alternating color rows with ExcelApplication