How can I set the row height for a defined name (range) in Spreadsheet Light?

Question

I am trying to convert Excel Interop code to Spreadsheet Light. The legacy Interop code I want to emulate is:

var columnHeaderRowRange = _xlSheetFillRateByDistributorByCustomer.Range[
    _xlSheetFillRateByDistributorByCustomer.Cells[1, 1],
    _xlSheetFillRateByDistributorByCustomer.Cells[1, 16]];
columnHeaderRowRange.Interior.Color = Color.LightBlue;
columnHeaderRowRange.RowHeight = 12;
columnHeaderRowRange.Font.Bold = true;
columnHeaderRowRange.Font.Size = 11;

I'm creating a defined name (new designation, or Spreadsheet Light's description, of a range) like so, in Spreadsheet Light-ese:

SLDocument sl;
. . .
sl.SetDefinedName("columnHeaderRowRange", "Sheet1!$A$1:$P$4");

I would think I could set the row height for the range something like this:

sl.SetRowHeight("columnHeaderRowRange", 12);

...but that doesn't work; the first arg must be an int (row index).

So I thought maybe I could add it to a style that I could then apply to the defined name:

SLStyle headerStyle = sl.CreateStyle();

..and then apply that style to the defined name like so:

sl.SetCellStyle("columnHeaderRowRange", headerStyle); // Remember Merle Haggard!

...but I see no properties in SLStyle that will allow me to do that, either.

How can I control the height of all the rows within a defined name?

I think I have everything from the Excel Interop range converted over besides that in my style:

headerStyle.Fill.SetPattern(DocumentFormat.OpenXml.
    Spreadsheet.PatternValues.Solid, Color.Black, Color.LightBlue);
headerStyle.Font.Bold = true;
headerStyle.Font.FontSize = 12;

B. Clay Shannon-B. Crow Raven · Accepted Answer · 2016-04-07T23:17:03.460

The same thing could effectively be accomplished via:

sl.SetRowHeight(1, 4, 12);

...or if you want the range to run through the last row added:

var stats = sl.GetWorksheetStatistics();
var rowcount = stats.NumberOfRows;
sl.SetRowHeight(1, rowcount, 12);

Although not being able to use a Range for some things with Spreadsheet Light, it seems pretty easy using values available via the GetWorksheetStatistics() method to accomplish handy tasks such as fit every row and column:

SLWorksheetStatistics wsstats = sl.GetWorksheetStatistics();
int rowCount = wsstats.NumberOfRows;
int colCount = wsstats.NumberOfColumns;

sl.AutoFitRow(1, rowCount);
sl.AutoFitColumn(1, colCount);

Of course, the values provided a defined name could be used, too, in suchlike instances:

sl.SetDefinedName("grandTotalRowRange",
        string.Format("Sheet1!${0}${1}:${2}${3}", 
            GetExcelTextColumnName(SHORTNAME_BYDCBYLOC_COL),
            rowToPopulate,
            GetExcelTextColumnName(QTYSHIPPED_BYDCBYLOC_COL),
            rowToPopulate)
        );

sl.AutoFitRow(rowToPopulate, rowToPopulate); // autofit the one row
sl.AutoFitColumn(SHORTNAME_BYDCBYLOC_COL, QTYSHIPPED_BYDCBYLOC_COL); // autofit the subset of columns

How can I set the row height for a defined name (range) in Spreadsheet Light?

1 Answers1