exists a function in GemBox which retrieves the rows of a worksheet on a specific page? In my opinion fhe function GetPaginator() does not work for this issue.
Or is a possiblity to get the first and / or last row of a specific page?
var workbook = ExcelFile.Load("input.xlsx");
var worksheet = workbook.Worksheets.ActiveWorksheet;
var range = worksheet.NamedRanges["_xlnm.Print_Area"]?.Range ?? worksheet.GetUsedCellRange(true);
var paginator = workbook.GetPaginator(new PaginatorOptions() { UpdatePageBreaks = true });
List<int> rowIndexes =
[
range.FirstRowIndex,
..worksheet.HorizontalPageBreaks.Select(hpb => hpb.Row),
range.LastRowIndex + 1
];
rowIndexes.Sort();
var pageRanges = new List<(int StartRowIndex, int EndRowIndex)>();
for (int index = 0; index < rowIndexes.Count - 1; index++)
pageRanges.Add((rowIndexes[index], rowIndexes[index + 1] - 1));
for (int pageIndex = 0; pageIndex < paginator.Pages.Count; pageIndex++)
{
var pageRange = pageRanges[pageIndex];
Console.WriteLine($"Page {pageIndex + 1} rows from {pageRange.StartRowIndex + 1} to {pageRange.EndRowIndex + 1}");
}
The above works if all columns fit on a single page.
If they don’t, then page ranges are repeated, so for that you could try using something like this:
// ...
var pageRanges = new List<(int StartRowIndex, int EndRowIndex)>();
int rowPageCount = rowIndexes.Count - 1;
int columnPageCount = paginator.Pages.Count / rowPageCount;
for (int pageIndex = 0; pageIndex < paginator.Pages.Count; pageIndex++)
{
int rowPageIndex = worksheet.PrintOptions.PrintPagesInRows
? pageIndex / columnPageCount
: pageIndex % rowPageCount;
pageRanges.Add((rowIndexes[rowPageIndex], rowIndexes[rowPageIndex + 1] - 1));
}
for (int pageIndex = 0; pageIndex < paginator.Pages.Count; pageIndex++)
{
var pageRange = pageRanges[pageIndex];
Console.WriteLine($"Page {pageIndex + 1} rows from {pageRange.StartRowIndex + 1} to {pageRange.EndRowIndex + 1}");
}
My goal is to insert carry forward rows in an invoice. To do this, I insert carry forward rows on the end of a page and on the beginning of the next page. After inserting the carry forward rows for the first page I have to call the paginator again, to do the same on the following pages. For this I have a VB.NET function based on your idea.
Private Function GetPageRows(worksheet As ExcelWorksheet) As List(Of Integer)
Dim range = If(worksheet.NamedRanges("_xlnm.Print_Area")?.Range, worksheet.GetUsedCellRange(True))
Dim paginator = workbook.GetPaginator(New GemBox.Spreadsheet.PaginatorOptions() With {
.UpdatePageBreaks = True
})
Dim value = New List(Of Integer)()
value.AddRange(worksheet.HorizontalPageBreaks.[Select](Function(hpb) hpb.Row))
Dim rowIndexes = value
rowIndexes.Sort()
Return rowIndexes
End Function
But calling this function the second time, I get the previous page breaks additinally to the new ones.
Therefore my question, what is to do to get the page breaks again correctly? Is there something to reset? Works it for memory manipulation of the worksheet or has the workbook first be saved?