3

I am currently working with a client that generates their invoices manually in Excel. I have used epplus to generate this file automatically for them.

One of the requirements I have is that each page should have a border around it and start with a header that is about 7 rows long (and each row has different heights based on what I have to put in them). In order to do this, I need to detect when I am at the end of a page so that I can pause my data printing and print out the header, then print out all the data for that page and print the border on the page based on how many rows are on the page.

This was a bit easier when the row heights were static because I could just assume that based on the page settings, that there were a specific amount of rows per page. However, some of the rows on the invoice can have multiple lines which means that WrapText = true. This obviously alters the numbers of rows on the page which throws all my formatting off.

Is there a way to calculate how many rows are on a page and detect when I am on a row where the page ends?

Thanks in advance for any help

JakeHova
  • 1,189
  • 2
  • 15
  • 36

1 Answers1

0

To get the row count, and column count, I used the following code:

// get the first worksheet in the workbook
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
//Gets the row count
var rowCnt = worksheet.Dimension.End.Row;
//Gets the column count
var colCnt = worksheet.Dimension.End.Column;
TGills
  • 126
  • 1
  • 2
  • 14
  • The dimension property on my worksheet is null. Any thoughts on why? – JakeHova Nov 03 '17 at 16:11
  • I might have misunderstood the question looking back at it. You are creating an excel sheet from some sort of program? Does the excel sheet you are trying to get the row count of have data already in it? – TGills Nov 03 '17 at 16:19
  • Sorry, I'm creating a spreadsheet programatically from scracth(starting with new ExcelPackage().Workbook.Worksheets.Add("Invoice");). So there is nothing that the program is reading and no data in it. – JakeHova Nov 03 '17 at 16:22
  • No worries. The code above works for excel sheets with data already in them. Are you using something like a DataTable to store the data before exporting it to the excel sheet? – TGills Nov 03 '17 at 16:27
  • Nah. I grab the data from SQL and go row by row and print the invoice out. Some rows are the standard height but other rows can be taller based on multiline of data being in them. My issue is that my calculations for the last row on the page seem to be off for some reason so I was hoping there was a way to access the last row on a printable page through EPPlus. – JakeHova Nov 03 '17 at 16:45
  • If you post the calculations for the last row, might be able to come to a different solution – TGills Nov 03 '17 at 18:03