Would you be interested in adding a CE builder for this library? I'd be happy to work implement it and provide a pull request for it if that works into you vision of the project? This the reason for the prior issue I filed regarding #seq.

-Patrick

Need to test what happens when something which is valid as some other format - e.g. date, number - is added as a string.

Query from Natalie Perret:

is there a support for PushStream or Seq?
(i.e. low memory footprint)

https://twitter.com/natalie_perret/status/1500264966024224768

These appear to be supported by ClosedXML, so can we add features which allow you to:

• Define tables
• Add formulae using structure references, e.g. =[@ActualHours]/[@ExpectedHours]

Builds are reported as failing even though the build and push to nuget are successful.

On Ubuntu, the list of fonts shown in the fonts list example of the tutorial is rather boring. Try distincting by the first few characters.

Allow some kind of Auto feature as well as String, Float, Integer etc. Sets the value based on content.

Possibly even infer from all the items in a column???

Currently the package only (seems) to support .NET 5. Supporting .NET Standard 2.0 would allow other .NET runtimes to use this package e.g. .NET Core 3.1, Net Framework 4.8 etc. I think .NET 6 should be ok though.

Need contributor guidelines including VS Code setup, other IDE setup, workflow...

The tutorial notebook is getting unwieldy and is not in a completely logical order.

Current ordering:

• Hello world
• Multiple cells
• Vertical Movement
• Indentation
• Border and Font Styling
• Font Name and Size
• Wrap Text
• Text Rotation
• Number Formatting and Alignment
• Formulae
• Color
• Range Styles
• Adding a Border to Merged Cells <- have we mentioned merged cells previously?
• Absolute Positioning <- could be moved near Vertical Movement and Indentation
• Named Cells
• Worksheets (Tabs) <- separate notebook?
• Working with an Existing Workbook
• Inserting blank rows
• Column Widths and Row Heights for All Cells <- simplify title? <- separate notebook?
• Individual Cell Sizing
• Autofitting
• Merging Cells and Vertical Alignment <- separate notebook?
• Tables from Records <- consider deprecating
• Rendering in Fable Elmish... <- separate notebook?
• Data Types <- move much earlier
• Rendering in HTML <- with Elmish in separate notebook?
• Autofilter <- separate notebook
• Excel Tables <- already a separate notebook

Proposed ordering:

• Hello world
• Multiple cells
• Vertical Movement
• Indentation
• Absolute Positioning
• Data Types
• Number Formatting and Alignment
• Border and Font Styling
• Font Name and Size
• Wrap Text
• Text Rotation
• Formulae
• Color
• Range Style
• Named Cells

Separate notebooks:

Row and Column Sizing

• Individual Cell Sizing
• Autofitting
• Column Widths and Row Heights for All Cells <- simplify title?

Worksheets (Tabs)

• Worksheets (Tabs)
• Working with an Existing Workbook
• Inserting blank rows

Cell merging

• Merging Cells and Vertical Alignment

Tables

• Excel tables
• Simple tables

Filtering

• Autofilter <- separate notebook

Rendering

• Rendering in HTML
• Rendering to Stream
• Rendering in Fable Elmish

Other tidy up work:

• Centralize the list of notebooks that contribute to tests - or just use all notebooks in a particular folder
• Consistent titlecase for sections
• Consistent heading level for sections
• Move known issues to github issues

Consider adding a feature where we serialize to our own format (JSON?) independently of xlsx.

On Ubuntu, get this error in the Autofitting section of the tutorial:

Error: System.ArgumentException: Unable to find font Calibri or fallback font Microsoft Sans Serif. Install missing fonts or specify a different fallback font through 'LoadOptions.DefaultGraphicEngine = new DefaultGraphicEngine("Fallback font name")'.

The worksheet test failed because it was looking for a tab named "English (United Kingdom)" and the actual worksheet created on my machine was named according to my local culture "English (United States)".

The tables from types test failed when comparing the DateJoined values with this error:
Expected: 2022-03-12T00:00:00.0000000
Actual: 2022-03-11T18:00:00.0000000

(My offset from UTC is -6).

I will create a PR to fix these issues.

When working with an existing worksheet, I've found that very often the safest way to add data is to insert rows above the existing data in the worksheet. I'd like to add a case `| InsertRowsAbove of int' to the Item DU. This would insert some number of rows above the current row index.

(I know ClosedXML also has an InsertRowsBelow method. I'd prefer to keep the API as simple possible and simply suggest to users that adding one to the row index and then calling InsertRowsAbove is the same thing as InsertRowsBelow)

As I was working on a PR for Issue #9, I realized that there a problem with all of the tests that use CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName.

Those tests will obviously fail on any system that does use the English names for the months. The quickest and most obvious solution is to change all those calls to CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB").DateTimeFormat.GetMonthName. I'd be willing to do a PR that's acceptable. However, I thought I should ask if you want to take a different approach.

After any table is built, the current cell state should point to just below the bottom left of the table.

Since F# London, I've been giving FsExcel a bit of thought and came to the conclusion that it might be nicer to work with if it supported using a list of lists.

Thus:

Render.AsFile': path: string -> items: Item list list -> unit

To be consistent with the current API, an Item list would be a row of cells, and an Item list list would be the grid of cells (collection of rows).

So taking examples from the README:

Multiple Cells

[
    [
        for i in 1..10 ->
            Cell [ Integer i ]
    ]
]
|> Render.AsFile' (Path.Combine(savePath, "MultipleCells.xlsx"))

Vertical Movement

[
    for m in 1..12 do
        let monthName = CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB").DateTimeFormat.GetMonthName(m)
        [ Cell [ String monthName ] ]
]
|> Render.AsFile' (Path.Combine(savePath, "VerticalMovement.xlsx"))

[
    for m in 1..12 do
        [
            let monthName = CultureInfo.GetCultureInfoByIetfLanguageTag("en-GB").DateTimeFormat.GetMonthName(m)
            Cell [ String monthName ]
            Cell [ Integer monthName.Length ]
        ]
]
|> Render.AsFile' (Path.Combine(savePath, "Rows.xlsx"))

Absolute Positioning

For this example, I'm going to make another proposal for EmptyCell as a type of Item, by Go should still work in this system, it's probably need to be separated into distinct GoRow and GoCol. Go (RC(_, _)) wouldn't easily translate:

[
    [
        for i in 1..6 do
            if i = 3
            then Cell [ String "Col 3"]
            else EmptyCell
    ]
    []
    []
    [
        for i in 1..6 do
            if i = 4
            then Cell [ String "Row 4"]
            else EmptyCell
    ]
    []
    [
        for i in 1..6 do
            if i = 5
            then Cell [ String "R6C5"]
            elif i = 6
            then Cell [ String "R6C6"]
            else EmptyCell
    ]
]
|> Render.AsFile' (Path.Combine(savePath, "AbsolutePositioning.xlsx"))

For better type safety, AsFile' could be Render.AsFile': path: string -> items: Item [,] -> unit, but Array2D is definitely not as nice to work with compared to list comprehension.

This is pretty big change, so thought best to create an issue first. I would be happy to create a PR for this, just for the sake of playing around with the library more than anything.

How can I create a named cell

Let's say I have a value 0.04 and I put it in B1 but that 0.04 is called "rate"
How do I pass in that information to Cell [] ? if at all

Is there a specific reason why you opted for Item list instead of #seq<Item> as the type for the items argument? Utilizing #seq<Item> would have greatly simplified creating composable functions because it defers the computation of Item references until rendering is required.

Did you have another engineering consideration behind this decision? As far as I can see, the code works just as well when changing the argument type to items: #seq<Item>, without having to pre-allocate the entire list before rendering.

It seems like a list type would be beneficial if there were back references to access. However, in such cases, an array or ResizeArray would be the ideal choice. Currently, I don't see any such usage in the code that would imply the need for this.

So I would suggest changing the type to items:#seq which would not be a breaking change for current users since Item list is an compatible with #seq.

Thanks,
-Patrick

For example, this causes an error:

open System.Data
open FsExcel
open ClosedXML.Excel

let table = new DataTable("Fruits")

let id = new DataColumn()
id.ColumnName <- "Id"
table.Columns.Add(id)

let name = new DataColumn()
name.ColumnName <- "Name"
table.Columns.Add(name)

let averageWeightG = new DataColumn()
averageWeightG.ColumnName <- "AverageWeightG"
table.Columns.Add(averageWeightG)

for id, name, averageWeightG in [1, "Apple", 100; 2, "Pear", 110; 3, "Banana", 120] do
    let row = table.NewRow()
    row["Id"] <- id
    row["Name"] <- name
    row["AverageWeightG"] <- averageWeightG
    table.Rows.Add(row)

let cellStyleHorizontal index name =
    if index = 0 then
        [
            Border(Border.Bottom XLBorderStyleValues.Medium)
            FontEmphasis Bold
        ]
    elif name = "Fees" then
        [ FormatCode "$0.00" ]
    else
        []    

seq {for row in table.Rows -> row}
|> Table.fromSeq Table.Direction.Horizontal cellStyleHorizontal
|> fun cells -> cells @ [ AutoFit All ]
|> Render.AsFile (Path.Combine(savePath, "RecordSequenceVertical.xlsx"))

Error: System.ArgumentException: Type 'System.Data.DataRow' is not an F# record type. (Parameter 'recordType')
at Microsoft.FSharp.Reflection.Impl.checkRecordType(String argName, Type recordType, BindingFlags bindingFlags) in D:\a_work\1\s\src\fsharp\FSharp.Core\reflect.fs:line 849
at [email protected](Type _arg1)
at System.Collections.Concurrent.ConcurrentDictionary2.GetOrAdd(TKey key, Func2 valueFactory)
at FsExcel.Table.Fields.serializable(Type t)
at FsExcel.Table.Cells.header[T](FSharpFunc2 getCellStyle) at FsExcel.Table.fromSeq[T](Direction direction, FSharpFunc2 getCellStyle, IEnumerable`1 xs)
at <StartupCode$FSI_0025>.$FSI_0025.main@()

Need a better workflow for new features - how to smoothly allow the tutorial to reference the local build temporarily.

Expected: Sept
Actual: Sep

Need to re-screenshot these.

savePath is defined more than once in Tutorial.md/dib.

With .NET 7 which is openly gonna drop System.Drawing.Common stuff for non-Windows platforms, I'm wondering if FsExcel should change the ClosedXML implementation on which it relies on to something that doesn't need System.Drawing.Common like that one?

I'm trying to run the first example in the tables tutorial (https://github.com/misterspeedy/FsExcel/blob/main/ExcelTableTutorial.md)

When I look at the output I get one table but where the headers and data are duplicated.
The first set of headers are appended with the '@' character.

I'm using FsExcel package version 0.0.46

Need to globalize all references to "Sheet1" - notebook and code.

I would like to use this library to make changes to an existing workbook. Ideally, I would like to be able to specify an existing workbook, and then identify one or more existing worksheets to update and/or create one or more new worksheets.

Externally, I would expect the API to look like this:

// assume path is a string pointing to a valid existing Excel file
let myWorkbook = new XLWorksheet(path)

[ 
  Workbook myWorkbook //only works as the first item, otherwise it's a no-op
  Worksheet "wsName" //if a sheet named wsName exists, it becomes the current sheet, if not, it is created
  Cell [ String "Hello, World!" ]
]

I'd be happy to submit a PR for this.

Considering some changes which would "demote" cell merging behaviour to a CellProperty, e.g.

#r "nuget: ClosedXML"
#r "../FsExcel/bin/Debug/netstandard2.1/FsExcel.dll"
let savePath = "/temp"

open System
open FsExcel 
open ClosedXML.Excel

[
    Cell [ 
        String "Hello" 
        Merge(3, 4)
        VerticalAlignment Middle
    ]
]
|> Render.AsFile (System.IO.Path.Combine(savePath, "LocalDemo.xlsx"))

This has a few advantages:

• simplifies the expression of ranges, which has got quite complicated
• may mean that we don't have to have a separate concept of merged cell borders. (Current implementation puts borders around all populated cells, whether merged or not.)
• might simplify the code and documentation generally.