misterspeedy / fsexcel Goto Github PK
View Code? Open in Web Editor NEWAn F# Excel spreadsheet generator
License: MIT License
An F# Excel spreadsheet generator
License: MIT License
Need to test what happens when something which is valid as some other format - e.g. date, number - is added as a string.
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:
The tutorial notebook is getting unwieldy and is not in a completely logical order.
Current ordering:
Proposed ordering:
Separate notebooks:
Other tidy up work:
Need a better workflow for new features - how to smoothly allow the tutorial to reference the local build temporarily.
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
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
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:
[
[
for i in 1..10 ->
Cell [ Integer i ]
]
]
|> Render.AsFile' (Path.Combine(savePath, "MultipleCells.xlsx"))
[
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"))
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.
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.
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.
Consider adding a feature where we serialize to our own format (JSON?) independently of xlsx.
An unhandled exception has occurred while executing the request.
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")'. Additional information is available at https://closedxml.readthedocs.io/en/latest/tips/missing-font.html page.
at ClosedXML.Graphics.DefaultGraphicEngine.LoadFont(MetricId metricId)
at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
at ClosedXML.Graphics.DefaultGraphicEngine.GetFont(MetricId metricId)
at ClosedXML.Graphics.DefaultGraphicEngine.GetFont(IXLFontBase fontBase)
at ClosedXML.Graphics.DefaultGraphicEngine.GetTextWidth(String text, IXLFontBase fontBase, Double dpiX)
at ClosedXML.Excel.XLColumn.GetWidthInCharacters(String text, IXLFontBase font)
at ClosedXML.Excel.XLColumn.AdjustToContents(Int32 startRow, Int32 endRow, Double minWidth, Double maxWidth)
at ClosedXML.Excel.XLColumn.AdjustToContents(Int32 startRow, Int32 endRow)
at ClosedXML.Excel.XLColumn.AdjustToContents(Int32 startRow)
at ClosedXML.Excel.XLColumn.AdjustToContents()
at ClosedXML.Excel.XLColumns.<>c.<AdjustToContents>b__13_0(XLColumn c)
at ClosedXML.Excel.EnumerableExtensions.ForEach[T](IEnumerable`1 source, Action`1 action)
at ClosedXML.Excel.XLColumns.AdjustToContents()
at FsExcel.Render.AsWorkBook(FSharpList`1 items)
at FsExcel.Render.AsFile(String path, FSharpList`1 items)
at server.Planilha.gerarPlanilha(DateTime dataInicial, DateTime dataFinal, FSharpList`1 list) in /app/lib/Planilha.fs:line 125
at server.Rules.gerarPlanilha(DateTime dataInicial, DateTime dataFinal) in /app/lib/Rules.fs:line 229
at server.Handlers.RelatorioHandlers.consultar(HttpContext ctx) in /app/lib/Handlers.fs:line 47
at [email protected](HttpContext ctx)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.Invoke(HttpContext httpContext)
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
Need to globalize all references to "Sheet1" - notebook and code.
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
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.
Need contributor guidelines including VS Code setup, other IDE setup, workflow...
Expected: Sept
Actual: Sep
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???
After any table is built, the current cell state should point to just below the bottom left of the table.
These appear to be supported by ClosedXML, so can we add features which allow you to:
=[@ActualHours]/[@ExpectedHours]
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")'.
savePath
is defined more than once in Tutorial.md/dib
.
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.
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, Func
2 valueFactory)
at FsExcel.Table.Fields.serializable(Type t)
at FsExcel.Table.Cells.header[T](FSharpFunc2 getCellStyle) at FsExcel.Table.fromSeq[T](Direction direction, FSharpFunc
2 getCellStyle, IEnumerable`1 xs)
at <StartupCode$FSI_0025>.$FSI_0025.main@()
Builds are reported as failing even though the build and push to nuget are successful.
Need to re-screenshot these.
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)
Query from Natalie Perret:
is there a support for PushStream or Seq?
(i.e. low memory footprint)
https://twitter.com/natalie_perret/status/1500264966024224768
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
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?
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.