Welcome to FsExcel, a library for generating Excel spreadsheets using very simple code.
FsExcel is based on ClosedXML but abstracts away many of the complications of building spreadsheets cell by cell.
This tutorial is also available as an interactive notebook. Download it, open in Visual Studio Code, and start generating spreadsheets for real!
-
Contributors - please see Contributing.md for getting-started information.
-
Usage example - for an example of FsExcel in action, see http://www.pushbuttonreceivetables.com. Source code on GitHub.
Here's the complete code to generate a spreadsheet with a single cell containing a string.
Run this and you should find a spreadsheet called HelloWorld.xlsx in your /temp folder. (Change the path to suit.)
// For scripts only; for programs, use NuGet to install FsExcel:
#r "nuget: FsExcel"
let savePath = "/temp"
open System.IO
open FsExcel
[
Cell [ String "Hello world!" ]
]
|> Render.AsFile (Path.Combine(savePath, "HelloWorld.xlsx"))
This example embodies the main stages of building a spreadsheet using FsExcel:
- Build a list using a list comprehension:
[ ... ] - In the list make cells using
Cell - Each cell gets a list of properties, in this case just the cell content, which here is a string:
String "Hello world!"
If you've used Fable.React, or a similar library, you'll already be familiar with the concepts so far.
- Send the resulting list to
FsExcel.Render.AsFile, providing a path.
open System.IO
open FsExcel
[
for i in 1..10 do
Cell [ Integer i ]
]
|> Render.AsFile (Path.Combine(savePath, "MultipleCells.xlsx"))
Here we use a for... comprehension to build multiple cells. (Don't panic: we could have used List.map instead!)
By default each new cell is put on the right of its predecessor.
If you want the next cell to be rendered below instead of to the right, you can add a Next(DownBy 1) property to the cell:
open System.IO
open System.Globalization
open FsExcel
[
for m in 1..12 do
let monthName = CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB").DateTimeFormat.GetMonthName(m)
Cell [
String monthName
Next(DownBy 1)
]
]
|> Render.AsFile (Path.Combine(savePath, "VerticalMovement.xlsx"))
The Next property overrides the default behaviour of rendering each successive cell one to the right. In this case we override it with a 'go down by 1' behaviour.
But what if you want a table of cells? Use the default behaviour for each cell in a row except the last. In the last cell use Next NewRow. This causes the next cell to be rendered in column 1 of the next row.
open System.IO
open System.Globalization
open FsExcel
[
for m in 1..12 do
let monthName = CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB").DateTimeFormat.GetMonthName(m)
Cell [
String monthName
]
Cell [
Integer monthName.Length
Next NewRow
]
]
|> Render.AsFile (Path.Combine(savePath, "Rows.xlsx"))
Maybe you don't like the idea of saying where to go next in the properties of a cell. No problem, you can have standalone position-control with the Go instruction:
open System.IO
open System.Globalization
open FsExcel
[
for m in 1..12 do
let monthName = CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB").DateTimeFormat.GetMonthName(m)
Cell [ String monthName ]
Cell [ Integer monthName.Length ]
Go NewRow
]
|> Render.AsFile (Path.Combine(savePath, "RowsGo.xlsx"))
Maybe you want a series of rows that don't start in column 1. Use Indent:
open System.IO
open System.Globalization
open FsExcel
[
Go(Indent 2)
for m in 1..12 do
let monthName = CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB").DateTimeFormat.GetMonthName(m)
Cell [ String monthName ]
Cell [ Integer monthName.Length ]
Go NewRow
]
|> Render.AsFile (Path.Combine(savePath, "Indentation.xlsx"))
Now each row begins at column 2.
Indents apply to all NewRow operations until some other indent value is set using Go(Indent n). Specify no indenting with Go(Indent 1).
You can specify indents relative to the current indent level using Go(IndentBy n) where n can be a positive or negative integer.
You can add border styling and font emphasis (bold, italic, underline or strikethrough) styling using Border (...) and FontEmphasis ... cell properties.
The border style values are in ClosedXML.Excel.XLBorderStyleValues and the underline values are in ClosedXML.Excel.XLFontUnderlineValues.
open System.IO
open System.Globalization
open FsExcel
open ClosedXML.Excel
[
for heading in ["Month"; "Letter Count"] do
Cell [
String heading
Border (Border.Bottom XLBorderStyleValues.Medium)
FontEmphasis Bold
FontEmphasis Italic
]
Go NewRow
for m in 1..12 do
let monthName = CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB").DateTimeFormat.GetMonthName(m)
Cell [
String monthName
FontEmphasis (Underline XLFontUnderlineValues.DoubleAccounting)
if monthName = "May" then
FontEmphasis StrikeThrough
]
Cell [ Integer monthName.Length ]
Go NewRow
]
|> Render.AsFile (Path.Combine(savePath, "Styling.xlsx"))
As they are just list items, styles can be composed and applied together as a list. You'll need a yield! to include these multiple elements in your cell property list.
open System.IO
open System.Globalization
open FsExcel
open ClosedXML.Excel
let headingStyle =
[
Border(Border.Bottom XLBorderStyleValues.Medium)
FontEmphasis Bold
FontEmphasis Italic
]
[
for heading in ["Month"; "Letter Count"] do
Cell [
String heading
yield! headingStyle
]
Go NewRow
for m in 1..12 do
let monthName = CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB").DateTimeFormat.GetMonthName(m)
Cell [ String monthName ]
Cell [ Integer monthName.Length ]
Go NewRow
]
|> Render.AsFile (Path.Combine(savePath, "ComposedStyling.xlsx"))
You can set the font name using FontName and the size using FontSize:
open System.IO
open System.Globalization
open FsExcel
open ClosedXML.Excel
// ClosedXml currently depends on SixLabors.Fonts -
// we use that to enumerate fonts so this code works cross-platform:
let fontNames =
SixLabors.Fonts.SystemFonts.Collection.Families
|> Seq.map (fun font -> font.Name)
|> Seq.sort
|> Seq.truncate 20
[
for i, fontName in fontNames |> Seq.indexed do
Cell [
String fontName
FontName fontName
FontSize (10 + (i * 2) |> float)
]
Go NewRow
]
|> Render.AsFile (Path.Combine(savePath, "FontNameSize.xlsx"))
You can wrap text in cells with long sentences/many words using WrapText true:
open System.IO
open FsExcel
open ClosedXML.Excel
[
Cell [ String "Without wrap text:"
HorizontalAlignment Center
VerticalAlignment Middle
CellSize (ColWidth 16) ]
Cell [ String "The quick brown fox jumps over the lazy dog."
HorizontalAlignment Center
VerticalAlignment Middle ]
Go NewRow
Cell [ String "With wrap text:"
HorizontalAlignment Center
VerticalAlignment Middle
CellSize (ColWidth 16) ]
Cell [ String "The quick brown fox jumps over the lazy dog."
HorizontalAlignment Center
VerticalAlignment Middle
WrapText true ]
]
|> Render.AsFile (Path.Combine(savePath, "WrapText.xlsx"))
You can rotate text between -90° and +90° with TextRotation n.
open System
open FsExcel
let p, m, g = "⏺", "◑", "⭘"
let performances =
[|
[| p; m; g; g; p; p; g; p; p; g |]
[| g; m; g; m; g; p; g; p; p; g |]
[| g; m; m; g; g; p; g; g; p; g |]
[| m; m; m; p; p; p; g; m; p; g |]
[| p; p; p; p; g; g; m; m; p; g |]
[| p; g; p; g; g; g; p; g; m; m |]
[| g; p; g; p; m; p; m; p; p; g |]
[| p; p; m; g; p; p; p; m; p; m |]
|]
let getPerformance (categoryIndex : int) (supplierIndex : int) =
performances[supplierIndex-1][categoryIndex-1]
[
Go (RC(1, 2))
for category in 1..10 do
Cell [String $"Category {category}"; TextRotation 45; CellSize (RowHeight 45)]
Go NewRow
for supplier in 1..8 do
Cell [String $"Supplier {supplier}"; CellSize (ColWidth 10)]
Go NewRow
Go (RC(2, 2))
Go (Indent 2)
for supplier in 1..8 do
for category in 1..10 do
Cell [ String (getPerformance category supplier); HorizontalAlignment Center]
Go NewRow
]
|> Render.AsFile (System.IO.Path.Combine(savePath, "TextRotation.xlsx"))
Number styling can be applied using standard Excel format strings. You can also apply horizontal alignment.
open System
open System.IO
open FsExcel
open ClosedXML.Excel
module PseudoRandom =
let mutable state = 1u
let mangle (n : UInt64) = (n &&& (0x7fffffff |> uint64)) + (n >>> 31)
let nextDouble() =
state <- (state |> uint64) * 48271UL |> mangle |> mangle |> uint32
(float state) / (float Int32.MaxValue)
let headingStyle =
[
Border(Border.Bottom XLBorderStyleValues.Medium)
FontEmphasis Bold
FontEmphasis Italic
]
[
for heading, alignment in ["Stock Item", Left; "Price", Right ; "Count", Right] do
Cell [
String heading
yield! headingStyle
HorizontalAlignment alignment
]
Go NewRow
for item in ["Apples"; "Oranges"; "Pears"] do
Cell [
String item
]
Cell [
Float ((PseudoRandom.nextDouble()*1000.))
FormatCode "$0.00"
]
Cell [
Integer (int (PseudoRandom.nextDouble()*100.))
FormatCode "#,##0"
]
Go NewRow
]
|> Render.AsFile (Path.Combine(savePath, "NumberFormatAndAlignment.xlsx"))
You can add a formula to a cell using FormulaA1(...).
Currently only the A1 style of cell referencing is supported, meaning that you will need to keep track of the column letter and row number you want to refer to:
open System
open System.IO
open FsExcel
open ClosedXML.Excel
module PseudoRandom =
let mutable state = 1u
let mangle (n : UInt64) = (n &&& (0x7fffffff |> uint64)) + (n >>> 31)
let nextDouble() =
state <- (state |> uint64) * 48271UL |> mangle |> mangle |> uint32
(float state) / (float Int32.MaxValue)
let headingStyle =
[
Border(Border.Bottom XLBorderStyleValues.Medium)
FontEmphasis Bold
FontEmphasis Italic
]
[
for heading, alignment in ["Stock Item", Left; "Price", Right ; "Count", Right; "Total", Right] do
Cell [
String heading
yield! headingStyle
HorizontalAlignment alignment
]
Go NewRow
for index, item in ["Apples"; "Oranges"; "Pears"] |> List.indexed do
Cell [
String item
]
Cell [
Float (PseudoRandom.nextDouble()*1000.)
FormatCode "$0.00"
]
Cell [
Integer (int (PseudoRandom.nextDouble()*1000.))
FormatCode "#,##0"
]
Cell [
FormulaA1 $"=B{index+2}*C{index+2}"
FormatCode "$#,##0.00"
]
Go NewRow
]
|> Render.AsFile (Path.Combine(savePath, "Formulae.xlsx"))
Set the font color with FontColor and the background color with the BackgroundColor property. Set the border color with BorderColor.
The color values and color creation functions are in ClosedXml.Excel.XLColor.
open System.IO
open FsExcel
open ClosedXML.Excel
[
let values = [0..32..224] @ [255]
for r in values do
for g in values do
for b in values do
// N.B. the API refuses to fill a cell with black if its font is black
// so the very first cell won't be colored.
let backgroundColor = ClosedXML.Excel.XLColor.FromArgb(0, r, g, b)
let fontColor = ClosedXML.Excel.XLColor.FromArgb(0, b, r, g)
let borderColor = ClosedXML.Excel.XLColor.FromArgb(0, g, b, r)
Cell [
String $"R={r};G={g};B={b}"
FontColor fontColor
BackgroundColor backgroundColor
Border (Border.Top XLBorderStyleValues.Thick)
Border (Border.Right XLBorderStyleValues.Thick)
Border (Border.Bottom XLBorderStyleValues.Thick)
Border (Border.Left XLBorderStyleValues.Thick)
// Could also have used Border.All:
// Border (Border.All XLBorderStyleValues.Thick)
BorderColor (BorderColor.Top borderColor)
BorderColor (BorderColor.Right borderColor)
BorderColor (BorderColor.Bottom borderColor)
BorderColor (BorderColor.Left borderColor)
// Could also have used BorderColor.All:
// BorderColor (BorderColor.All borderColor)
]
Go NewRow
Go NewRow
]
|> Render.AsFile (Path.Combine(savePath, "Color.xlsx"))
You can apply any properties to all cells from a point in your code using Style [ prop; prop...]. Don't forget to reset style with Style [] afterwards.
open System
open System.IO
open FsExcel
open ClosedXML.Excel
module PseudoRandom =
let mutable state = 1u
let mangle (n : UInt64) = (n &&& (0x7fffffff |> uint64)) + (n >>> 31)
let nextDouble() =
state <- (state |> uint64) * 48271UL |> mangle |> mangle |> uint32
(float state) / (float Int32.MaxValue)
[
Style [
Border(Border.Bottom XLBorderStyleValues.Medium)
FontEmphasis Bold
FontEmphasis Italic
]
for heading in ["Stock Item"; "Price"; "Count"] do
Cell [ String heading ]
Style []
Go NewRow
for item in ["Apples"; "Oranges"; "Pears"] do
Cell [
String item
]
Style [ FontEmphasis Italic ]
Cell [
Float ((PseudoRandom.nextDouble()*1000.))
FormatCode "$0.00"
]
Cell [
Integer (int (PseudoRandom.nextDouble()*100.))
FormatCode "#,##0"
]
Style []
Go NewRow
]
|> Render.AsFile (Path.Combine(savePath, "RangeStyle.xlsx"))
To add a border to all cells in an Item list that includes merged cells, use:
BorderMergedCell [ BorderType (Border.x XLBorderStyleValues.[......]); ColorBorder (BorderColor.x (XLColor.FromArgb(0, 68, 114, 196)))]
where x = [All, Top, Right, Bottom, Left]. Include this border styling after any merged cells.
Other styling such as background color, font emphasis, font style etc. can be applied to all cells (including merged cells) using Style [prop; prop...] at the start of the Item list as outlined above. These styling propeties are retained when a cell is merged, unlike with cell borders.
open System.IO
open System
open ClosedXML.Excel
open FsExcel
[ Go NewRow
for heading, colWidth in ["ID", 3.22; "Car Name", 10.33; "Car Description", 49.33; "Car Regestration", 16.89 ] do
Cell [
String heading
FontEmphasis Bold
FontName "Calibri"
FontSize 11
HorizontalAlignment Center
FontColor (XLColor.FromArgb(0, 255, 255, 255))
BackgroundColor (XLColor.FromArgb(0, 68, 114, 196))
Border (Border.All XLBorderStyleValues.Thin)
CellSize (ColWidth colWidth)
]
Go NewRow
Style [ HorizontalAlignment Center
VerticalAlignment Middle
BackgroundColor (XLColor.FromArgb(0, 240, 240, 210))]
Cell [ Integer 1
//HorizontalAlignment Left
//VerticalAlignment TopMost
Name "ID" ]
Cell [ String "Ford Fiesta" ]
//HorizontalAlignment Center
//VerticalAlignment Middle ]
Cell [ String "Car Technical Details:"
Next (DownBy 1) ]
Cell [ String "Technical Detail 1"
Next (DownBy 1) ]
Cell [ String "Technical Detail 2"
Next (DownBy 1)]
Cell [ String "Technical Detail 3"
Name "LastL" ]
Go (RC (3, 4))
Cell [ String "AB12 CDE"
//HorizontalAlignment Right
//VerticalAlignment Base
Name "Reg" ]
Go (RC (6, 4))
Cell [Name "RegEnd"]
Go (RC (7, 3))
Cell [ String "Another Technical Detail"
FontEmphasis Italic
//VerticalAlignment Middle
Name "TD"
Next Stay]
Go (DownBy 1)
Cell [ Name "info"]
// Merging between named and specific cells
MergeCells (ColRowLabel ("B", 3), ColRowLabel ("B", 6))
MergeCells (NamedCell "ID", ColRowLabel ("A", 6))
MergeCells (ColRowLabel ("C", 7), NamedCell "info")
MergeCells (NamedCell "Reg", NamedCell "RegEnd")
// Adding a border to merged cells - any original border around a single cell is lost post merging cells
BorderMergedCell [ BorderType (Border.All XLBorderStyleValues.Thin)
ColorBorder (BorderColor.All (XLColor.FromArgb(0, 68, 114, 196)))]
]
|> Render.AsFile (Path.Combine(savePath, "BorderMergedCells.xlsx"))
FsExcel is designed to save you from having to keep track of absolute row- and column-numbers. However sometimes you might want to position a cell at an absolute row or column position - or both.
After the explicitly-positioned cell, subsequent cells are by default rendered to the right again.
open System.IO
open FsExcel
open ClosedXML.Excel
[
Go (Col 3)
Cell [ String "Col 3"]
Go (Row 4)
Cell [ String "Row 4"]
Go (RC(6, 5))
Cell [ String "R6C5"]
Cell [ String "R6C6"]
]
|> Render.AsFile (Path.Combine(savePath, "AbsolutePositioning.xlsx"))
Remember that, by default, successive cells are placed to the right of their predecessors? Sometimes (rarely) you might want to suppress that behaviour completely. To do that use Next Stay.
open System.IO
open FsExcel
[
for i in 1..5 do
Cell [
Integer i
Next Stay
]
Go(DownBy i)
]
|> Render.AsFile (Path.Combine(savePath, "Stay.xlsx"))
To create worksheet scoped name use
Name "Username"
or
ScopedName ("Email", NameScope.Worksheet)
To create workbook scoped name use
ScopedName ("Email", NameScope.Workbook)
open System.IO
open FsExcel
[
Cell [
String "JohnDoe"
Name "Username" ]
Cell [
String "[email protected]"
ScopedName ("Email", NameScope.Workbook) ]
]
|> Render.AsFile (Path.Combine(savePath, "NamedCells.xlsx"))
By default, all cells are placed into a worksheet (tab) called "Sheet1". You can override this, and create additional worksheets, using Worksheet ....
If you do not want a "Sheet1" tab you'll need to use Worksheet to create an explicitly named sheet - before creating any cells.
Each new worksheet starts at the top-left cell, has an indent setting of 1 (no indent), and has an empty list as its current Style [...] value.
If you use Worksheet with the name of a worksheet that already exists, that worksheet becomes active with a current position of RC(1, 1), no indent and an empty Style [...] value.
open System.IO
open FsExcel
let britishCultureNativeName = "English (United Kingdom)"
let ukrainianCultureNativeName = "українська"
let britishCultureDateTimeFormatGetMonthName =
[ "January"; "February"; "March"; "April"; "May"; "June"; "July";
"August"; "September"; "October"; "November"; "December" ]
let britishCultureDateTimeFormatAbbreviatedMonthNames =
[ "Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct";
"Nov"; "Dec" ]
let ukrainianCultureDateTimeFormatGetMonthName =
[ "січень"; "лютий"; "березень"; "квітень"; "травень"; "червень";
"липень"; "серпень"; "вересень"; "жовтень"; "листопад"; "грудень" ]
let ukrainianCultureDateTimeFormatAbbreviatedMonthNames =
[ "січ"; "лют"; "бер"; "кві"; "тра"; "чер"; "лип"; "сер"; "вер"; "жов";
"лис"; "гру" ]
[
Worksheet britishCultureNativeName
for m in 0..11 do
let monthName = britishCultureDateTimeFormatGetMonthName.[m]
Cell [ String monthName ]
Cell [ Integer monthName.Length ]
Go NewRow
Worksheet ukrainianCultureNativeName
for m in 0..11 do
let monthName = ukrainianCultureDateTimeFormatGetMonthName.[m]
Cell [ String monthName ]
Cell [ Integer monthName.Length ]
Go NewRow
Worksheet britishCultureNativeName // Switch back to the first worksheet
Go (RC(13, 1))
for m in 0..11 do
let monthAbbreviation = britishCultureDateTimeFormatAbbreviatedMonthNames.[m]
Cell [ String monthAbbreviation ]
Cell [ Integer monthAbbreviation.Length ]
Go NewRow
Worksheet ukrainianCultureNativeName // Switch back to the second worksheet
Go (RC(13, 1))
for m in 0..11 do
let monthAbbreviation = ukrainianCultureDateTimeFormatAbbreviatedMonthNames.[m]
Cell [ String monthAbbreviation ]
Cell [ Integer monthAbbreviation.Length ]
Go NewRow
]
|> Render.AsFile (Path.Combine(savePath, "Worksheets.xlsx"))
You can update an existing workbook by using Workbook ... as the first item in the list. By default, this will set the first worksheet in the workbook as the active sheet.
Typically, you will want to immediately follow with a Worksheet ... to either move to the worksheet you want to update or create a new worksheet.
Workbook requires you to pass in a reference to a valid ClosedXML.Excel.XLWorkbook object. See Inserting blank rows below for an example.
One common task when working with existing workbooks is inserting rows of data above existing rows. You can use InsertRowsAbove n which will insert n blank rows above the current row.
InsertRowsAbove does not change the current position. However, the row at that position is now the first inserted (blank) row. Note that if a formula refers to a cell that is moved, the formula is automatically updated.
open System.IO
open ClosedXML.Excel
open FsExcel
// Open Worksheets.xlsx created in the previous snippet:
let workbook = new XLWorkbook(Path.Combine(savePath, "Worksheets.xlsx"))
let britishCultureNativeName = "English (United Kingdom)"
let ukrainianCultureNativeName = "українська"
let altMonthNames = [| "Vintagearious"; "Fogarious"; "Frostarious"; "Snowous"; "Rainous"; "Windous"; "Buddal"; "Floweral"; "Meadowal"; "Reapidor"; "Heatidor"; "Fruitidor" |]
[
Workbook workbook
Worksheet ukrainianCultureNativeName
Go(RC(1,3))
Cell [FormulaA1 $"='{britishCultureNativeName}'!B1*2" ]
Worksheet britishCultureNativeName
InsertRowsAbove 12 // The cell reference in the formula above will be updated to B13
for m in 0..11 do
Cell [ String altMonthNames[m] ]
Cell [ Integer altMonthNames[m].Length ]
Go NewRow
]
|> Render.AsFile (Path.Combine(savePath, "WorksheetsRevised.xlsx"))
You can set a specific width for all columns and a specific height for all rows with SizeAll (ColWidth x) and SizeAll (RowHeight x).
Excel and ClosedXml documentation is not clear on what units are used. The width unit correlates with ~10% of the width given number of pixels. For example, if you need a cell to be ~50 pixels wide, then you would set the ColWidth to ~5. The width unit appears to be, roughly, average character width. The height unit is 60% of the pixel height. For example, if you want the height to be 45 pixels, then you would set the RowHeight to 27.
open System.IO
open System.Globalization
open FsExcel
[
for x in 1..12 do
for y in 0..12 do
Cell [ Integer (x * y) ]
Go NewRow
SizeAll (ColWidth 5)
SizeAll (RowHeight 20)
]
|> Render.AsFile (Path.Combine(savePath, "ColumnWidthRowHeight.xlsx"))
To size individual cells within an Item list (e.g. [ Cell [....]; Go NewRow; Cell [...]; Go NewRow etc.]), use CellSize (ColWidth 10) and CellSize (RowHeight 10) as part of a Cell's list of properties.
open System.IO
open System
open ClosedXML.Excel
open FsExcel
[ Go NewRow
for heading, colWidth in ["ID", 3.22; "Car Name", 10.33; "Car Description", 49.33; "Car Registration", 16.89 ] do
Cell [
String heading
FontEmphasis Bold
FontName "Calibri"
FontSize 11
HorizontalAlignment Center
FontColor (XLColor.FromArgb(0, 255, 255, 255))
BackgroundColor (XLColor.FromArgb(0, 68, 114, 196))
Border(Border.All XLBorderStyleValues.Thin)
CellSize (ColWidth colWidth)
]
Go NewRow
Cell [ Integer 1
HorizontalAlignment Center ]
Cell [ String "Ford Fiesta" ]
Cell [ String "Car Technical Details..."]
Cell [ String "AB12 CDE"
HorizontalAlignment Center]
]
|> Render.AsFile (Path.Combine(savePath, "IndividualCellSize.xlsx"))
You can set the widths of columns to fit their contents using AutoFit AllCols. You can auto fit a range of columns with AutoFit (ColRange(<c1>, <c2>)).
You can autofit heights of rows with AutoFit AllRows and AutoFit (RowRange(<r1>,<r2>)).
You can autofit all columns and all rows with AutoFit All.
Perform AutoFit operations after the cells have been populated!
open System.IO
open System.Globalization
open FsExcel
open ClosedXML.Excel
// For non-Windows runtime environments you will have to add these lines to use AutoFit.
// This is because ClosedXML needs a font to work with when computing sizes. You may have
// to use a different font name if Liberation Sans is not installed on the target system.
open System.Runtime.InteropServices
if not (RuntimeInformation.IsOSPlatform(OSPlatform.Windows)) then
LoadOptions.DefaultGraphicEngine <- new ClosedXML.Graphics.DefaultGraphicEngine("Liberation Sans")
//
let headingStyle =
[
Border(Border.Bottom XLBorderStyleValues.Medium)
FontEmphasis Bold
FontEmphasis Italic
]
[
for heading in ["Month"; "Letter Count"] do
Cell [
String heading
yield! headingStyle
]
Go NewRow
for m in 1..12 do
let monthName = CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB").DateTimeFormat.GetMonthName(m)
Cell [ String monthName ]
Cell [ Integer monthName.Length ]
Go NewRow
AutoFit AllCols
]
|> Render.AsFile (Path.Combine(savePath, "AutosizeColumns.xlsx"))
You can merge cells by using MergeCells (CellLabel , CellLabel) where a CellLabel can be a:
- specific cell -
ColRowLabel ("<column letter>", <row number>) - named cell -
NamedCell "CellName" - specified depth and span -
SpanDepth (<column span>, <row depth>)
Cells can be merged before or after placing text in them, however, as per Excel cell naming convention with merged cells, the following rules must be observed:
- Merging cells vertically - text must be assigned to the top-most cell label of the merged cell. For example, if there is a vertically merged cell spanning from cell A3 to A6, any text within the range A3 to A6 must be assigned to cell A3.
- Merging cells horizontally - text must be assigned to the left-most cell label of the merged cell. For example, if there is a horizontally merged cell spanning from cell A2 to E2, any text within the range A2 to E2 must be assigned to cell A2.
- Merging cells horizontally & vertically - text must be assigned to the top-left cell label of the merged cell. For example, if there is a merged cell spanning from cell A2 to E10, any text within the range A2 to E2 must be assigned to cell A2.
A SpanDepth of e.g. (1, 3) creates a merged cell spanning one column and a depth of three rows. The column span and row depth of a merged cell from a starting cell can be specified in two ways:
- Forward Merging i.e. merging from top left to bottom right with the starting cell being in the top left hand corner of the merged cell. This method retains cell name, cell contents, cell shading & the top left hand corner of the original starting cell border. This is achieved by having SpanDepth as the second item in the Merge tuple:
MergeCells ((NamedCell "CellName", SpanDepth (3, 3)))MergeCells ((ColRowLabel ("B", 15), SpanDepth (1, 2)))
- Reverse Merging i.e. merging from bottom right to top left with the starting cell being in the bottom right hand corner of the merged cell. This method loses the cell name, cell contents, cell shading of the original starting cell. However, the bottom right hand corner of the original starting cell border is retained. In the case of starting with e.g cell B2 and requesting a reverse merge SpanDepth of (5, 5), i.e. merging to beyond the excel sheet boundaries, the cell to merge to will be defaulted to cell A1. The Reverse Merging is achieved by having SpanDepth as the first item in the Merge tuple:
MergeCells ((SpanDepth (3, 3)), NamedCell "CellName")MergeCells ((SpanDepth (1, 2)), (ColRowLabel ("B", 15))
Vertical Alignment for a given cell can be achieved by using Vertical Alignment [Base, Middle, TopMost].
Note that when using two NamedCell references in a MergeCells () call, the cell names must differ between the first and second cell. For example the following construct will not result in merged cells (though it does not cause an error):
[
Cell [String "Hello"; Name "name"]
Cell [String "Hello2"; Name "name"]
MergeCells ((NamedCell "name", NamedCell "name"))
] Merges which lead to a column reference beyond the maximum supported by Excel ("XFD") will result in an ArgumentException.
open System.IO
open System
open ClosedXML.Excel
open FsExcel
[ Go NewRow
for heading, colWidth in ["ID", 3.22; "Car Name", 10.33; "Car Description", 49.33; "Car Registration", 16.89 ] do
Cell [
String heading
FontEmphasis Bold
FontName "Calibri"
FontSize 11
HorizontalAlignment Center
FontColor (XLColor.FromArgb(0, 255, 255, 255))
BackgroundColor (XLColor.FromArgb(0, 68, 114, 196))
Border(Border.All XLBorderStyleValues.Thin)
CellSize (ColWidth colWidth)
]
Go NewRow
Cell [ Integer 1
HorizontalAlignment Left
VerticalAlignment TopMost
Name "ID" ]
Cell [ String "Ford Fiesta"
HorizontalAlignment Center
VerticalAlignment Middle ]
Cell [ String "Car Technical Details:"
Next (DownBy 1) ]
Cell [ String "Technical Detail 1"
Next (DownBy 1) ]
Cell [ String "Technical Detail 2"
Next (DownBy 1)]
Cell [ String "Technical Detail 3"
Name "LastL" ]
Go (RC (3, 4))
Cell [ String "AB12 CDE"
HorizontalAlignment Right
VerticalAlignment Base
Name "Reg" ]
Go (RC (6, 4))
Cell [Name "RegEnd"]
Go (RC (7, 3))
Cell [ String "Another Technical Detail"
FontEmphasis Italic
VerticalAlignment Middle
Name "TD"
Next Stay]
Go (DownBy 1)
Cell [ Name "info"]
// Merging between named and specific cells
MergeCells ((ColRowLabel ("B", 3), ColRowLabel ("B", 6)))
MergeCells ((NamedCell "ID", ColRowLabel ("A", 6)))
MergeCells ((ColRowLabel ("C", 7), NamedCell "info"))
MergeCells ((NamedCell "Reg", NamedCell "RegEnd"))
Go (RC (10, 1))
Cell [ String "Merging from a starting cell given a depth and span"
BackgroundColor (XLColor.FromArgb(0, 80, 180, 220))
FontEmphasis Bold
HorizontalAlignment Center ]
MergeCells ((ColRowLabel ("A", 10), ColRowLabel ("D", 10)))
Go (RC (12, 2))
Cell [ String "The components that make up a car are: "
Name "components"
HorizontalAlignment Left
VerticalAlignment TopMost
Border(Border.All XLBorderStyleValues.MediumDashDot)]
Go (RC (12, 4))
Cell [ Border(Border.All XLBorderStyleValues.MediumDashDot)]
Go (RC (14, 4))
Cell [ Border(Border.All XLBorderStyleValues.MediumDashDot)]
Go (RC (15, 2))
Cell [ String "Road Tax"
HorizontalAlignment Center
VerticalAlignment Middle
Border(Border.All XLBorderStyleValues.SlantDashDot)]
Go (RC (16, 2))
Cell [ Border(Border.All XLBorderStyleValues.SlantDashDot)]
// Forward merging - cell name, cell contents, shading & top LH corner of border are retained
MergeCells ((NamedCell "components", SpanDepth (3, 3)))
MergeCells ((ColRowLabel ("B", 15), SpanDepth (1, 2)))
Go (RC (17, 4))
Cell [ String "Insurance"
Name "insurance" // NamedCells cannot begin with a number
Border(Border.All XLBorderStyleValues.Dashed) ]
Go (RC (17, 3))
Cell [ Border(Border.All XLBorderStyleValues.Dashed)]
Go (RC (17, 2))
Cell [ Border(Border.All XLBorderStyleValues.Dashed)]
Go (RC (16, 4))
Cell [ String "Signature"]
// Reverse Merging - original cell contents, cell name and cell shading are lost
// Only bottom RH corner of the border is retained
MergeCells ((SpanDepth (3, 1), NamedCell "insurance"))
MergeCells ((SpanDepth (2, 2), ColRowLabel ("D", 16)))
]
|> Render.AsFile (Path.Combine(savePath, "MergeCellsWithVerticalAlignment.xlsx"))
You can create a table of cells from an F# record or a sequence of F# records.
The tables created using this approach are not proper Excel tables, simply grids of cells with potentially a little formatting. To create true Excel tables, see Excel Table Tutorial.
Use Table.fromInstance or Table.fromSeq and provide
- an orientation (
Table.Direction.HorizontalorTable.Direction.Vertical) - a function which, given an index and a field name, returns a list of properties for styling. (This style can be an empty list.)
- the instance or sequence.
In horizontal tables, the values for each record appear beside one another. In vertical tables the values for a record appear below one another.
Calls to the cell style function are given 0 for the header, 1 for the first (or only) data row, 2 for the next and so on.
Tables don't automatically autofit - you'll have to do that (if you want) after the table is built.
Regardless of table orientation, the 'current cell' (i.e. the address at which any further new cell is rendered) is always just below the bottom-left corner of the table that was just created.
open System
open System.IO
open ClosedXML.Excel
open FsExcel
type JoiningInfo = {
Name : string
Age : int
Fees : decimal
DateJoined : string
}
// This works just as well if these are anonymous record instances,
// eg. {| Name = "..."; ... |}
let records = [
{ Name = "Jane Smith"; Age = 32; Fees = 59.25m; DateJoined = "2022-03-12" } // Excel will treat these strings as dates
{ Name = "Michael Nguyễn"; Age = 23; Fees = 61.2m; DateJoined = "2022-03-13" }
{ Name = "Sofia Hernández"; Age = 58; Fees = 59.25m; DateJoined = "2022-03-15" }
]
let cellStyleVertical index name =
if index = 0 then
[ FontEmphasis Bold ]
elif name = "Fees" then
[ FormatCode "$0.00" ]
else
[]
let cellStyleHorizontal index name =
if index = 0 then
[
Border(Border.Bottom XLBorderStyleValues.Medium)
FontEmphasis Bold
]
elif name = "Fees" then
[ FormatCode "$0.00" ]
else
[]
records
|> Table.fromSeq Table.Direction.Vertical cellStyleVertical
|> fun cells -> cells @ [ AutoFit All ]
|> Render.AsFile (Path.Combine(savePath, "RecordSequenceVertical.xlsx"))
records
|> Table.fromSeq Table.Direction.Horizontal cellStyleHorizontal
|> fun cells -> cells @ [ AutoFit All ]
|> Render.AsFile (Path.Combine(savePath, "RecordSequenceHorizontal.xlsx"))
records
|> Seq.tryHead
|> Option.iter (fun r ->
r
|> Table.fromInstance Table.Direction.Vertical cellStyleVertical
|> fun cells -> cells @ [ AutoFit All ]
|> Render.AsFile (Path.Combine(savePath, "RecordInstanceVertical.xlsx"))
r
|> Table.fromInstance Table.Direction.Horizontal cellStyleHorizontal
|> fun cells -> cells @ [ AutoFit All ]
|> Render.AsFile (Path.Combine(savePath, "RecordInstanceHorizontal.xlsx")))
You can use Render.AsStream <stream> <items> to render to a pre-existing stream, or Render.AsStreamBytes <items> to render as a byte array.
Render.AsStreamBytes is useful for Fable-based and other web app scenarios. Render to a byte array on the server, and transfer the bytes to the client using Fable Remoting. On the client use the SaveFileAs extension function to start a browser download. Make sure you have opened the Fable.Remoting.Client to get the SaveFileAs method of a byte array.
For a working example, see http://www.pushbuttonreceivetables.com/, in particular https://github.com/misterspeedy/HtmlExcel/blob/main/src/Server/Html.fs#L105.
open FsExcel
[
Cell [ String "Hello world!" ]
]
|> Render.AsStreamBytes
|> fun bytes ->
$"Bytes length: {bytes.Length}"
FsExcel supports the following data types for cell content:
- String
- Integer
- Float
- Boolean
- DateTime
- TimeSpan
open System
open System.IO
open FsExcel
[
Cell [ String "String"]; Cell [ String "string" ]
Go NewRow
Cell [ String "Integer" ]; Cell [ Integer 42 ]
Go NewRow
Cell [ String "Number" ]; Cell [ Float Math.PI ]
Go NewRow
Cell [ String "Boolean" ]; Cell [ Boolean false ]
Go NewRow
Cell [ String "DateTime" ]; Cell [ DateTime (System.DateTime(1903, 12, 17)) ]
Go NewRow
Cell [ String "TimeSpan" ]
Cell [
TimeSpan (System.TimeSpan(hours=1, minutes=2, seconds=3))
FormatCode "hh:mm:ss"
]
]
|> Render.AsFile (Path.Combine(savePath, "DataTypes.xlsx"))
You can render a workbook as a set of HTML tables. You will get one table per worksheet.
This feature is primarily for use in Dotnet Interactive Notebooks, where you can use the HTML() helper method to display the resulting HTML. This can be useful when experimenting with cell layouts, to avoid having to view an Excel file on every iteration.
The styling representation is approximate:
- Bold and italic font emphasis should show correctly. (Note that VS Code does not default to representing
<th>items in bold) - Underlining, where present, will always be be shown as a single underline.
- Cell borders, where present, will always be a single line. (Note that VS Code does not yet show borders on tables.)
- Font names, sizes, cell alignment and any kind of color are not currently supported.
The AsHtml function takes a function parameter which is called for every cell rendered, with a row and column index (both zero based, originating from the top-left-most occupied cell). When this function returns true, the cell is rendered as <th>, otherwise it is rendered as <td>.
open System
open System.IO
open FsExcel
open ClosedXML.Excel
let isHeader r c =
r = 0 || c = 0
[
Worksheet "Worksheet 1"
Style [ FontEmphasis Bold ]
Cell [ String "Item" ]
Cell [ String "Example" ]
Style []
Go NewRow
Cell [ String "String"]
Cell [ String "string" ]
Go NewRow
Cell [ String "Integer" ]
Cell [ Integer 42 ]
Go NewRow
Cell [ String "Number" ]
Cell [ Float Math.PI ]
Go NewRow
Cell [ String "Boolean" ]
Cell [ Boolean false ]
Go NewRow
Cell [ String "DateTime" ]
Cell [ DateTime (System.DateTime(1903, 12, 17)) ]
Go NewRow
Cell [ String "TimeSpan" ]
Cell [
TimeSpan (System.TimeSpan(hours=1, minutes=2, seconds=3))
FormatCode "hh:mm:ss"
]
Go NewRow
Cell [ String "Bold" ]
Cell [
String "I am bold"
FontEmphasis Bold
]
Go NewRow
Cell [ String "Italic" ]
Cell [
String "I am Italic"
FontEmphasis Italic
]
Go NewRow
Cell [ String "Underlined" ]
Cell [
String "I am underlined"
FontEmphasis (Underline XLFontUnderlineValues.Single)
]
Go NewRow
Worksheet "Worksheet 2"
Cell [String "I am another table"]
]
|> Render.AsHtml isHeader
|> HTML
You can add filters to a WorkSheet.
- Enable Only: Enables but does not apply an AutoFilter.
- Apply filter: Enables and applies an AutoFilter.
- Clear filter: Clears an AutoFilter.
There can be multiple AutoFilters on a given worksheet. This means that the area to be filtered has to be specified when defining the filter. This is done with AutoFilterRange.
RangeUsed: The entire range used in the worksheet.CurrentRegionof string: The current region around a spcified cell.Rangeof string: A specified range.
Examples:
AutoFilter [ EnableOnly RangeUsed ]
AutoFilter [ EnableOnly CurrentRegion ]
AutoFilter [ GreaterThanInt ("A1:E6", 2, 3) ]EnableOnly of AutoFilterRange
Clear of AutoFilterRange
EqualToString of range : AutoFilterRange * column : int * value : string
EqualToInt of range : AutoFilterRange * column : int * value : int
EqualToFloat of range : AutoFilterRange * column : int * value : float
EqualToDateTime of range : AutoFilterRange * column : int * value : DateTime
EqualToBool of range : AutoFilterRange * column : int * value : bool
NotEqualToString of range : AutoFilterRange * column : int * value : string
NotEqualToInt of range : AutoFilterRange * column : int * value : int
NotEqualToFloat of range : AutoFilterRange * column : int * value : float
NotEqualToDateTime of range : AutoFilterRange * column : int * value : DateTime
NotEqualToBool of range : AutoFilterRange * column : int * value : bool
BetweenInt of range : AutoFilterRange * column : int * min : int * max : int
BetweenFloat of range : AutoFilterRange * column : int * min : float * max : float
BetweenDateTime of range : AutoFilterRange * column : int * min : DateTime * max : DateTime
NotBetweenInt of range : AutoFilterRange * column : int * min : int * max : int
NotBetweenFloat of range : AutoFilterRange * column : int * min : float * max : float
NotBetweenDateTime of range : AutoFilterRange * column : int * min : DateTime * max : DateTime
ContainsString of range : AutoFilterRange * column : int * value : string
NotContainsString of range : AutoFilterRange * column : int * value : string
BeginsWithString of range : AutoFilterRange * column : int * value : string
NotBeginsWithString of range : AutoFilterRange * column : int * value : string
EndsWithString of range : AutoFilterRange * column : int * value : string
NotEndsWithString of range : AutoFilterRange * column : int * value : string
Top of range : AutoFilterRange * column : int * value : int * topType : XLTopBottomType
Bottom of range : AutoFilterRange * column : int * value : int * bottomType : XLTopBottomType
GreaterThanInt of range : AutoFilterRange * column : int * value : int
GreaterThanFloat of range : AutoFilterRange * column : int * value : float
GreaterThanDateTime of range : AutoFilterRange * column : int * value : DateTime
LessThanInt of range : AutoFilterRange * column : int * value : int
LessThanFloat of range : AutoFilterRange * column : int * value : float
LessThanDateTime of range : AutoFilterRange * column : int * value : DateTime
EqualOrGreaterThanInt of range : AutoFilterRange * column : int * value : int
EqualOrGreaterThanFloat of range : AutoFilterRange * column : int * value : float
EqualOrGreaterThanDateTime of range : AutoFilterRange * column : int * value : DateTime
EqualOrLessThanInt of range : AutoFilterRange * column : int * value : int
EqualOrLessThanFloat of range : AutoFilterRange * column : int * value : float
EqualOrLessThanDateTime of range : AutoFilterRange * column : int * value : DateTime
AboveAverage of range : AutoFilterRange * column : int
BelowAverage of range : AutoFilterRange * column : intEqualToDateTime:
Works but, both Equals and Custom Filter are blank.
NotEqualToDateTime:
Does not work. Does contains. Should be not contains.
BetweenDateTime
Does not work. Excel filter shows 07/01/1900. Reapply hides all rows.
NotBetweenDateTime
Works but, shows as a Custom filter with 07/01/1900 in Excel.
NotContains
Works but, shows as a Contains filter in Excel. Reapply does Contains.
GreaterThanDateTime
Works but, filter name is After and shows 07/01/1900.
LessThanDateTime
Works but, filter name is Before and shows 07/01/1900.
EqualOrGreaterThanDateTime
Works but, filter name is Custom Filter and shows 07/01/1900.
EqualOrLessThanDateTime
Works but, filter name is Custom Filter and shows 07/01/1900.
Some of the above issues may be related to one of these:
In the example below and AutoFilter is enabled for the RangeUsed, but no filter is applied.
open System
open System.IO
open FsExcel
let headings =
[ Cell [ String "StringCol"; HorizontalAlignment Center ]
Cell [ String "IntCol"; HorizontalAlignment Center ]
Cell [ String "FloatCol"; HorizontalAlignment Center ]
Cell [ String "DateTimeCol"; HorizontalAlignment Center ]
Cell [ String "BooleanCol"; HorizontalAlignment Center ]
Go NewRow ]
let rows =
[ 1 .. 5 ]
|> Seq.map(fun i ->
[ Cell [ String $"String{i}" ]
Cell [ Integer i ]
Cell [ Float ((i |> float) + 0.1) ]
Cell [ DateTime (DateTime.Parse("15-July-2017 05:33:00").AddMinutes(i)) ]
Cell [ Boolean (i % 2 |> Convert.ToBoolean) ]
Go NewRow ])
|> Seq.collect id
|> List.ofSeq
headings @ rows @ [ AutoFit All; AutoFilter [ EnableOnly RangeUsed ] ]
|> Render.AsFile (Path.Combine(savePath, "AutoFilterEnableOnly.xlsx"))
In the example below AutoFilter is enabled (this is automatic if you create a filter).
The following compound filter is created:
RangeUsed, column 2 is filtered for greater than 3- and
RangeUsed, column 5 is filtered to equaltrue
open System
open System.IO
open FsExcel
let headings =
[ Cell [ String "StringCol"; HorizontalAlignment Center ]
Cell [ String "IntCol"; HorizontalAlignment Center ]
Cell [ String "FloatCol"; HorizontalAlignment Center ]
Cell [ String "DateTimeCol"; HorizontalAlignment Center ]
Cell [ String "BooleanCol"; HorizontalAlignment Center ]
Go NewRow ]
let rows =
[ 1 .. 5 ]
|> Seq.map(fun i ->
[ Cell [ String $"String{i}" ]
Cell [ Integer i ]
Cell [ Float ((i |> float) + 0.1) ]
Cell [ DateTime (DateTime.Parse("15-July-2017 05:33:00").AddMinutes(i)) ]
Cell [ Boolean (i % 2 |> Convert.ToBoolean) ]
Go NewRow ])
|> Seq.collect id
|> List.ofSeq
headings @ rows @ [ AutoFit All; AutoFilter [ GreaterThanInt (RangeUsed, 2, 3); EqualToBool (RangeUsed, 5, true) ] ]
|> Render.AsFile (Path.Combine(savePath, "AutoFilterCompound.xlsx"))
No AutoFilter:
AutoFilter applied:
You can Freeze Panes for a WorkSheet.
Note: There is an issue with
Freeze Panes. If it is disabled in ExcelSplitis left enabled. If you disableFreeze Panesin Excel, you will also have to disableSplit.
ClosedXmlissue: Distorted Header when Scrolling
In the example below cell B2 is selected. Freeze Panes is set to Freeze Panes.
open System
open System.IO
open FsExcel
let headings =
[ Cell [ String "StringCol"; HorizontalAlignment Center ]
Cell [ String "IntCol"; HorizontalAlignment Center ]
Cell [ String "FloatCol"; HorizontalAlignment Center ]
Cell [ String "DateTimeCol"; HorizontalAlignment Center ]
Cell [ String "BooleanCol"; HorizontalAlignment Center ]
Go NewRow ]
let rows =
[ 1 .. 5 ]
|> Seq.map(fun i ->
[ Cell [ String $"String{i}" ]
Cell [ Integer i ]
Cell [ Float ((i |> float) + 0.1) ]
Cell [ DateTime (DateTime.Parse("15-July-2017 05:33:00").AddMinutes(i)) ]
Cell [ Boolean (i % 2 |> Convert.ToBoolean) ]
Go NewRow ])
|> Seq.collect id
|> List.ofSeq
headings @ rows @ [ AutoFit All; FreezePanes (Panes (1, 1)) ]
|> Render.AsFile (Path.Combine(savePath, "FreezePanes.xlsx"))
Cursor at B2 > Freeze Panes:
In the example below Freeze Panes is set to Freeze Top Row.
open System
open System.IO
open FsExcel
let headings =
[ Cell [ String "StringCol"; HorizontalAlignment Center ]
Cell [ String "IntCol"; HorizontalAlignment Center ]
Cell [ String "FloatCol"; HorizontalAlignment Center ]
Cell [ String "DateTimeCol"; HorizontalAlignment Center ]
Cell [ String "BooleanCol"; HorizontalAlignment Center ]
Go NewRow ]
let rows =
[ 1 .. 5 ]
|> Seq.map(fun i ->
[ Cell [ String $"String{i}" ]
Cell [ Integer i ]
Cell [ Float ((i |> float) + 0.1) ]
Cell [ DateTime (DateTime.Parse("15-July-2017 05:33:00").AddMinutes(i)) ]
Cell [ Boolean (i % 2 |> Convert.ToBoolean) ]
Go NewRow ])
|> Seq.collect id
|> List.ofSeq
headings @ rows @ [ AutoFit All; FreezePanes TopRow ]
|> Render.AsFile (Path.Combine(savePath, "FreezePanesTopRow.xlsx"))
Freeze Panes > Freeze Top Row:
In the example below Freeze Panes is set to Freeze First Column.
open System
open System.IO
open FsExcel
let headings =
[ Cell [ String "StringCol"; HorizontalAlignment Center ]
Cell [ String "IntCol"; HorizontalAlignment Center ]
Cell [ String "FloatCol"; HorizontalAlignment Center ]
Cell [ String "DateTimeCol"; HorizontalAlignment Center ]
Cell [ String "BooleanCol"; HorizontalAlignment Center ]
Go NewRow ]
let rows =
[ 1 .. 5 ]
|> Seq.map(fun i ->
[ Cell [ String $"String{i}" ]
Cell [ Integer i ]
Cell [ Float ((i |> float) + 0.1) ]
Cell [ DateTime (DateTime.Parse("15-July-2017 05:33:00").AddMinutes(i)) ]
Cell [ Boolean (i % 2 |> Convert.ToBoolean) ]
Go NewRow ])
|> Seq.collect id
|> List.ofSeq
headings @ rows @ [ AutoFit All; FreezePanes FirstColumn ]
|> Render.AsFile (Path.Combine(savePath, "FreezePanesFirstColumn.xlsx"))
Freeze Panes > Freeze First Column:
In the example below Freeze Panes is set to Unfreeze Panes.
Not that in the code below I first
Freeze Panesprior to unfreezing them. This is for demonstration purposes only.
open System
open System.IO
open FsExcel
let headings =
[ Cell [ String "StringCol"; HorizontalAlignment Center ]
Cell [ String "IntCol"; HorizontalAlignment Center ]
Cell [ String "FloatCol"; HorizontalAlignment Center ]
Cell [ String "DateTimeCol"; HorizontalAlignment Center ]
Cell [ String "BooleanCol"; HorizontalAlignment Center ]
Go NewRow ]
let rows =
[ 1 .. 5 ]
|> Seq.map(fun i ->
[ Cell [ String $"String{i}" ]
Cell [ Integer i ]
Cell [ Float ((i |> float) + 0.1) ]
Cell [ DateTime (DateTime.Parse("15-July-2017 05:33:00").AddMinutes(i)) ]
Cell [ Boolean (i % 2 |> Convert.ToBoolean) ]
Go NewRow ])
|> Seq.collect id
|> List.ofSeq
headings @ rows @ [ AutoFit All; FreezePanes TopRow; FreezePanes UnfreezePanes ]
|> Render.AsFile (Path.Combine(savePath, "FreezePanesUnfreezePanes.xlsx"))
Freeze Panes > Unfreeze Panes:
To create Excel Tables, see the separate Excel Table Tutorial.