Repeat Rows & Columns in Excel via C#

VBC#

using IronXL;

WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Set repeating rows for row(2-4)
workSheet.SetRepeatingRows(1, 3);

// Set repeating columns for column(C-D)
workSheet.SetRepeatingColumns(2, 3);

// Set column break after column(H). Hence, the first page will only contain column(A-G)
workSheet.SetColumnBreak(7);

workBook.SaveAs("repeatingRows.xlsx");

Imports IronXL

Dim workBook As WorkBook = WorkBook.Load("sample.xlsx")
Dim workSheet As WorkSheet = workBook.DefaultWorkSheet

' Set repeating rows for row(2-4)
workSheet.SetRepeatingRows(1, 3)

' Set repeating columns for column(C-D)
workSheet.SetRepeatingColumns(2, 3)

' Set column break after column(H). Hence, the first page will only contain column(A-G)
workSheet.SetColumnBreak(7)

workBook.SaveAs("repeatingRows.xlsx")

PM > Install-Package IronXL.Excel

Install-Package IronXL.Excel

Repeat Rows & Columns in Excel via C#

When Excel spreadsheets span several pages, the data is easier and quicker to read when we print the column or row titles at the right or top of every page (repeating). These are called Repeating Rows and Columns or Header Rows and Columns in Excel. IronXL can utilize this useful aspect of spreadsheets with just a few lines of code.

`SetRepeatingRows(startRow, endRow)`

This method is used to set repeating rows. For example, calling workSheet.SetRepeatingRows(3, 4) will set repeating on rows 4 and 5.

`SetRepeatingColumns(startColumn, endColumn)`

This method is used to set repeating columns. For example, calling workSheet.SetRepeatingColumns(0, 2) will set repeating on columns A to C.

These two methods use zero-based indexing as parameters, such that column(0) is "A" and row(1) is 2. It's important to note that if these methods SetRepeatingRows() and SetRepeatingColumns() are used in combination, each page may have different repeating rules based on their position relative to the first page.

The content that spans multiple pages along the right side of the first page will have the repeating column rule applied only. Figure 1 shows this phenomenon:

Figure 1

Multiple pages spanning along the bottom side of the first page will contain repeating rows.

Lastly, pages that are located on the inner side will have both repeating columns and row rules applied to them. Figure 2 shows the last two phenomena: