"Stacking" cell styles with F# and GemBox.Spreadsheet

Question

I'm writing data from F# into Excel with GemBox.Spreadsheet. Getting the data from F# to Excel is fairly simple. Adding single cell styles to a specific or range of cells is rather simple as well. However, I'm trying to "stack" styles (i.e., add multiple styles to the same cell) but am finding it difficult to determine if 1) this is possible and 2) if possible, how can it be done?

Below is some simple F# code:

open System
open System.Data
open System.Xml
open System.Linq
open System.Text
open System.Drawing
open GemBox.Spreadsheet

SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

//initialize a new ExcelFile
let excelFile = new ExcelFile()

//Create a new worksheet in the excel object
let ws = excelFile.Worksheets.Add("data")

//Create a new DataTable to fill with data
let dt = new DataTable("dataTable")
dt.MinimumCapacity <- 1

let dcStatus = new DataColumn("Status")
let dcNumber = new DataColumn("Number")

//Add the columns to the DataTable, dt
dt.Columns.Add(dcStatus)
dt.Columns.Add(dcNumber)

dt.Rows.Add("Status", "Number")
dt.Rows.Add("Not Started - behind schedule", 3)
dt.Rows.Add("In Progress - behind schedule", 5)
dt.Rows.Add("Withdrawn", 3)
dt.Rows.Add("Total", 11)

//Define red style
let redStyle = new CellStyle()

redStyle.Font.Color <- SpreadsheetColor.FromName(ColorName.Red)

//Define bold style
let boldStyle = new CellStyle()

boldStyle.Font.Weight <- ExcelFont.BoldWeight

//Apply redStyle to row 1:3 and cols 0:1
ws.Cells.GetSubrangeAbsolute(1,0, 3, 1).Style <- redStyle

//Apply boldStyle to row 1:3, col 0
ws.Cells.GetSubrangeAbsolute(1, 0, 4, 0).Style <- boldStyle

//Insert datatable with GemBoxSpreadsheet options; GemBox starts with cell A1 as [0,0]
//StartRow == 0, StartColumn == 0
ws.InsertDataTable(dt, InsertDataTableOptions(0, 0))

//Set Col with of first column to autofit
ws.Columns.[0].AutoFit()

//Write excelFile to filePath
excelFile.Save("[YOUR_LOCAL_FILEPATH/]filename.xlsx")'

When I apply one style to a range of cells then apply another style to that range, the first style is overwritten.

Can styles be "stacked" with GemBox.Spreadsheet or do I need to create/apply a style for every bold/red and bold cell I want to apply? The data presented here is a fairly simplified data set; in most cases, I would have many different styles that, ideally, could be stacked.

Thanks for your comments and help.

FoggyFinder FoggyFinder · Accepted Answer · 2015-08-31T23:02:53

So you defined a new style. Set only the necessary parts:

//Apply redStyle to row 1:3 and cols 0:1
ws.Cells.GetSubrangeAbsolute(1, 0, 3, 1).Style <- redStyle

//Apply boldStyle to row 1:3, col 0
ws.Cells.GetSubrangeAbsolute(1, 0, 4, 0).Style.Font.Weight <- ExcelFont.BoldWeight

Before:

Now:

"Stacking" cell styles with F# and GemBox.Spreadsheet

1 Answers