Problem

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
Borders
Fonts
Text orientation, justification, indentation, orientation, number format
Horizontal/vertical alignment
Cell locked/unlocked when worksheet is protected

Notes:

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.

Solution

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.

Step 1:

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.)

ExcelApplication xla = new ExcelApplication();

Workbook wb = xla.Open("MyUnformattedWB.xlsx");

Worksheet ws = wb.Worksheets["SheetToFormat"];

Area populatedArea = ws.PopulatedCells;

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.

ExcelApplication xla = new ExcelApplication();

Workbook wb = xla.Open("MyUnformattedWB.xlsx");

Range myRange = wb.GetNamedRange("AreaWithValues");

Area populatedArea = myRange.Areas[0];

Step 2:

Create the desired Style with Workbook.CreateStyle().

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.

Style unlockStyle = wb.CreateStyle();

unlockStyle.CellLocked = false;

Step 3:

Determine the first row/column in the area with Area.FirstColumn or Area.FirstRow. Get the total number of rows/columns in the populated area with Area.RowCount or Area.ColumnCount.

int colStart = populatedArea.FirstColumn;

int totalCol = populatedArea.ColumnCount;

Step 4:

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.

for(int i = colStart; i < totalCol; i++)

{

ws.GetColumnProperties(i).ApplyStyle(unlockStyle);

}

//For worksheet protection, we also need to protect the worksheet

ws.Protect("MyPassword");

Additional References:

Applying Formatting to just an Area instead of Rows or Columns

If you want to apply the formatting just to specific cells, you can use Area.ApplyStyle() or Area.SetStyle() instead of looping through the area to apply the style to each row or column.

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.

Conditional Formatting

Using ExcelTemplate Grouping

How to remove WordWriter from the Global Assembly Cache

Updating the Table of Contents in a WordWriter generated document

SoftArtisans

How to Format Rows or Columns only if they Contain Values