Applying number format on a column from a specific row number using SpreadsheetGear

Question

I am using SpreadsheetGear to generate a excel sheet. I want to apply number formatting on a column starting from row number 10.

But when I use below code, it apply formatting for the entire column.

worksheet.Cells[10, 2].EntireColumn.NumberFormat = "@";

How can I ensure formatting is applied for the entire column starting from row number 10.

score 2 · Answer 1 · answered May 09 '13 at 15:22

This could be accomplished any number of ways. One might be to "subtract" the top 10 rows from the entire column, which could be done using IRange.Subtract(). Example:

// First obtain a reference to all of Column C, then "subtract" the first 9 rows from it
IRange range = worksheet.Cells["C:C"].Subtract(worksheet.Cells["C1:C9"]);
// Apply your formatting...
range.NumberFormat = "@";

Another would be to use zero-based indexes to select the desired range, using the IWorkbookSet.MaxRows property to determine the maximum row (currently 1,048,576, as per Excel’s file format limitations). Example:

IRange range = worksheet.Cells[9, 2, worksheet.WorkbookSet.MaxRows - 1, 2];

Lastly, you could hard-code the range using an A1-style reference, knowing that the current file format limitation is 1,048,576 rows. Example:

IRange range = worksheet.Cells["C10:C1048576"];

Applying number format on a column from a specific row number using SpreadsheetGear

1 Answers1