en | ru
BookFx provides an extremely effective method for creating Excel workbooks of any complexity.
And we already have the xlsx with one empty spreadsheet!
The more friendly version:
Composition instead of address ciphering, component approach for tackle complexity, functional style instead of VBA-like imperativeness, components prototyping with slots made of parts of beforehand prepared xlsx-files, formulas, fonts, colors, alignments, formats. About all this below.
Table of Contents
- Getting Started
- Examples of Use
- API Reference
PM> Install-Package BookFx
Make class is the entry point of BookFx.
It exposes methods to create books, sheets, boxes, styles and borders.
The exit point is
The main properties of BookFx classes can be defined using overloads of
Another way is chaining:
Make .Book() .Add(Make.Sheet().Name("First")) .Add(Make.Sheet().Name("Second")) .ToBytes()
Both examples gives the same result.
Box is a building block of a spreadsheet.
It can be composite and always describes a range — cell, row, column or rectangle of cells.
Here is the complete list of boxes.
||Values, formulas and empty ranges.|
||From left to right box placement.|
||From top to bottom box placement.|
||Layer by layer box placement.|
||Composing from templates.|
What if we put two
ValueBoxes into the
Make.Row(Make.Value("Box A1"), Make.Value("Box B1")).ToSheet().ToBook().ToBytes()
Logical. Two values have been placed in row!
ValueBox have been implemented implicit convertions from all necessary value types.
What does this means?
This means that we don't have to repeat
Make.Value every time,
ValueBox will be created automatically.
Make.Row("Box A1", "Box B1").ToSheet().ToBook().ToBytes()
The result is the same!
Let's describe this table header.
In terms of BookFx it can be thought of as composition of elements, like this:
Is is easy to see the common pattern.
We can extract this pattern in a function:
Box PlanFact(string title) => Make.Col(title, Make.Row("Plan", "Fact"));
Essentially it is a simple component. Test it:
PlanFact("Beginning of year").ToSheet().ToBook().ToBytes()
Now let's use
PlanFact as component and add the style:
Box Head() => Make .Row() .Add("Code", "Name", PlanFact("Beginning of year"), PlanFact("End of year")) .Style(Make.Style().Center().Middle().Bold().DefaultBorder());
Wait a second, that is another component! Let me get this straight. A component is a function. A function is a component... It looks like infinite possibilities are in our hands!
Now it is easy:
AutoSpan you can read in the Spanning and Merging section.
The full version is in examples of use, see below.
Examples of Use
BookFx.Usage project contains a few examples of use. Run it and get results in the
This is a full version of Getting Started example. It makes a table with totals.
This demonstrates some style features of BookFx.
This demonstrates an adding worksheets from preexisting workbooks. See also Prototyping.
This is an example of prototyping.
This demonstrates the creation of a Balance Sheet report with headers and a variable number of columns and rows of data.
BookFx workbook model anything like the HTML DOM. This is a tree of nodes, which renders to a xlsx-file.
This approach opens multiple opportunities:
- nodes can be implemented as reusable components;
- placing of nodes can be driven by composition of nodes;
- hierarchy of nodes is convenient to applying styles;
- unit testing of components doesn't require to render workbook.
- helps to better structure a describing of workbook;
- takes the pain out of the calculating sizes and addresses of ranges;
- saves you the trouble of using imperative API came from the world of VBA macros;
- opens up opportunities of the functional programming.
Every sheet of book can contain one root box. It is placed in the upper left corner.
Composite boxes contain other boxes and are stretched to fit them:
- boxes are placed in row from left to right inside of
- boxes are placed in column from top to bottom inside of
- boxes are placed in stack one above the other inside of
ValueBox cannot contains other boxes, but can be placed in several cells.
More about it see in the Spanning and Merging section.
The size of a
ProtoBox is always equal to the size of its prototype, and inner boxes of
ProtoBox are placed using the mechanism of slots. Further in the Prototyping section.
Spanning and Merging
ValueBox, like any other box type, can be placed in several cells.
SpanCols and their combination
Span are used to define the number of spanned cells.
The cell spanning inside of
ValueBox is works like
colspan HTML table attributes, but in BookFx cells inside a
ValueBox is not always should be merged.
Merge method is used to merge cells, but BookFx merges ranges of a
ValueBox automatically if the box has a value or a formula. In some cases it may be require do not merge cells automatically. For that there is the
In addition to automatically merging, BookFx supports automatically spanning, which is activated by methods
AutoSpanCols and their combination
In this mode a box and its inners are stretched to sizes of their containers through the last stretchable
ValueBox is considered to be stretchable when its
Span is not specified and its
AutoSpan is not deactivated. We've used
AutoSpan in the Getting Started section.
Values and Formulas
ValueBox is intended for values and formulas.
It can be created either by
Make.Value or using implicit convertion from all necessary value types:
Formulas should begin with
' is used for escaping. Only
R1C1 reference style is supported.
BookFx supports using parts of other workbooks as prototypes:
Make .Proto(protoBook, "Prototype1") .Add("Slot1", "Value1") .Add("Slot2", Make.Row("Value2", "Value3"));
byteof a xlsx-file content;
"Prototype1"– name of the range in
"Slot2"– names of ranges in
Prototype1, in which other boxes can be placed.
See the example S6ProtoBox.cs.
Also BookFx supports adding whole spreadsheets from other workbooks:
Make.Sheet("New Sheet Name", protoBook, "Prototype Sheet Name");
"Prototype Sheet Name" spreadsheet will be copied from
protoBook xlsx-file and then it will be renamed to
"New Sheet Name". See also other overloads of
See also the example S5ProtoSheet.cs.
Make- the model elements factory
Make.Book- make a
Make.Sheet- make a
Make.Row- make a
Make.Col- make a
Make.Stack- make a
Make.Value- make a
Make.Proto- make a
Make.Style- make a
Make.Border- make a
Book- an Excel workbook
Book.Add- add sheet(s)
Book.ToBytes- render to xlsx
Sheet- an Excel spreadsheet
Sheet.Name- define a sheet name
Sheet.TabColor- define a tab color
Sheet.SetPageView- define page view
Sheet.Portrait- define portrait page orientation
Sheet.Landscape- define landscape page orientation
Sheet.Margin- define page margins
Sheet.Fit- fit the height and the width of printout to pages
Sheet.FitToHeight- fit the height of printout to pages
Sheet.FitToWidth- fit the width of printout to pages
Sheet.Scale- define a scale
Sheet.ToBook- make a
Bookwith one sheet
Box- a box of any type
Box.Name- define a name of the range
AutoSpanmode for rows and columns
AutoSpanmode for rows
AutoSpanmode for columns
Box.Style- define a style
Box.SizeRows- define heights of rows
Box.SizeCols- define widths of columns
Box.SetPrintArea- define print area by the box
Box.HideRows- hide rows
Box.HideCols- hide columns
Box.Freeze- freeze the box range
Box.FreezeRows- freeze rows of the box
Box.FreezeCols- freeze columns of the box
Box.AutoFilter- add auto filter to the lower row of the box
Box.ToSheet- make a
Sheetwith the root box
RowBox- a row of boxes
RowBox.Add- add box(es) in row
ColBox- a column of boxes
ColBox.Add- add box(es) in column
StackBox- a stack of boxes
StackBox.Add- add box(es) in stack
ValueBox- a box with a value, with a formula or an empty box
ValueBox.Span- span rows and columns
ValueBox.SpanRows- span rows
ValueBox.SpanCols- span columns
ValueBox.Merge- merge cells
ProtoBox- a prototype
ProtoBox.Add- add a box into a slot
BoxStyle- a style
BoxStyle.Borders- define borders
BoxStyle.DefaultBorder- define regular borders
BoxStyle.Font- define a font, its size and color
BoxStyle.Back- define a background color
BoxStyle.Color- define a font color and/or a background color
BoxStyle.Bold- in bold
BoxStyle.Italic- in italic
BoxStyle.Wrap- define a text wrap
BoxStyle.Shrink- define a text shrinking to fit
BoxStyle.Align- define an alignment
BoxStyle.Left- align to the left
BoxStyle.Center- align at the center horizontally
BoxStyle.CenterContinuous- align horizontally at the center of adjacent cells
BoxStyle.Right- align to the right
BoxStyle.Top- align to the top
BoxStyle.Middle- align at the middle vertically
BoxStyle.Bottom- align to the bottom
BoxStyle.Rotate- rotate the text
BoxStyle.Indent- define an indent
BoxStyle.Format- define a custom format
BoxStyle.DefaultFormat- define the
BoxStyle.Text- define define the
BoxStyle.Integer- define the
BoxStyle.Money- define the
#,##0.00format (Number with a thousands separator)
BoxStyle.Percent- define the
0%format (Percentage, integer)
BoxStyle.DateShort- define the
dd.mm.yyyyformat (Short date)
BoxBorder- a border
BoxBorder.Restrict- restrict a part of a box to which the border applied
BoxBorder.Style- define a border style
BoxBorder.Color- define a border color
EnumerableExt- IEnumerable extensions for BookFx types
IEnumerable<Box>.ToBook- make a
IEnumerable<Box>.ToRow- make a
RowBoxfrom other boxes
IEnumerable<Box>.ToCol- make a
ColBoxfrom other boxes
IEnumerable<Box>.ToStack- make a
StackBoxfrom other boxes
IEnumerable<BoxStyle>.Mix- mix styles into a new style
The project is licensed under the LGPL-3.0-or-later.
The copyright notice
BookFx. Composing Excel spreadsheets based on a tree of nested components like the HTML DOM. Copyright (c) 2019 Zhenya Gusev
The license notice
This library is free software: you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation, either version 3 of the License, or any later version. This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details. You should have received a copy of the GNU Lesser General Public License along with this library. If not, see <https://www.gnu.org/licenses/>.