BookFx

[![nuget-img][]][nuget-link] [![build-img][]][build-link] [![tests-img][]][tests-link] [![quality-img][]][quality-link]

en | [ru][]

BookFx provides an extremely effective method for creating Excel workbooks of any complexity.

Make.Book().ToBytes()

And we already have the xlsx with one empty spreadsheet!

![book-empty][]

The more friendly version:

Make.Value("Hi, World!").ToSheet().ToBook().ToBytes()

![box-a1][]

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.

BookFx requires [.NET Standard 2.0][.net standard 2.0] and depends on [EPPlus][epplus] which is used as a render to XLSX Office Open XML format.

Table of Contents

• Installation
• Getting Started
• Examples of Use
• Concepts
  • Model Description
  • Layout System
  • Spanning and Merging
  • Values and Formulas
  • Prototyping
• API Reference
• License

Installation

PM> Install-Package BookFx

Getting Started

Making

The Make class is the entry point of BookFx. It exposes methods to create books, sheets, boxes, styles and borders. The exit point is ToBytes(). The main properties of BookFx classes can be defined using overloads of Make methods:

Make.Book(Make.Sheet("First"), Make.Sheet("Second")).ToBytes()

Another way is chaining:

Make
    .Book()
    .Add(Make.Sheet().Name("First"))
    .Add(Make.Sheet().Name("Second"))
    .ToBytes()

Both examples gives the same result.

![sheet-name][]

Boxes

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.

| Type | Creating | Destination | | -- | -- | -- | | ValueBox | Make.Value() | Values, formulas and empty ranges. | | RowBox | Make.Row() | From left to right box placement. | | ColBox | Make.Col() | From top to bottom box placement. | | StackBox | Make.Stack() | Layer by layer box placement. | | ProtoBox | Make.Proto() | Composing from templates. |

What if we put two ValueBoxes into the RowBox?

Make.Row(Make.Value("Box A1"), Make.Value("Box B1")).ToSheet().ToBook().ToBytes()

![box-a1-b1][]

Logical. Two values have been placed in row!

Conversions

In the 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, because the ValueBox will be created automatically.

Make.Row("Box A1", "Box B1").ToSheet().ToBook().ToBytes()

![box-a1-b1][]

The result is the same!

Composition

Let's describe this table header.

![box-header][]

In terms of BookFx it can be thought of as composition of elements, like this:

![box-header-model][]

Is is easy to see the common pattern.

![box-plan-fact-model][]

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()

![box-plan-fact][]

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:

Head().AutoSpan().ToSheet().ToBook().ToBytes()

![box-header][]

Done.

About the AutoSpan you can read in the Spanning and Merging section. The full version is in examples of use, see below.

Examples of Use

The BookFx.Usage project contains a few examples of use. Run it and get results in the src\BookFx.Usage\bin\Debug\netcoreapp2.1\Results\ folder.

[S1Table.cs][s1table.cs]

This is a full version of Getting Started example. It makes a table with totals.

[S2Style.cs][s2style.cs]

This demonstrates some style features of BookFx.

[S3Calendar.cs][s3calendar.cs]

Wow! Calendar!

[![s-3-calendar][]][s3calendar.cs]

[S5ProtoSheet.cs][s5protosheet.cs]

This demonstrates an adding worksheets from preexisting workbooks. See also Prototyping.

[S6ProtoBox.cs][s6protobox.cs]

This is an example of prototyping.

[S7BalanceSheet.cs][s7balancesheet.cs]

This demonstrates the creation of a Balance Sheet report with headers and a variable number of columns and rows of data.

Concepts

Model Description

BookFx workbook model anything like the HTML [DOM][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.

BookFx model is [immutable][immutable object], and methods of the library has no [side effects][side effect], hence BookFx allows you to write [pure functions][pure function].

Thus, BookFx:

• 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.

Layout System

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 RowBox;
• boxes are placed in column from top to bottom inside of ColBox;
• boxes are placed in stack one above the other inside of StackBox.

A 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

A ValueBox, like any other box type, can be placed in several cells. A ValueBox methods SpanRows, SpanCols and their combination Span are used to define the number of spanned cells. The cell spanning inside of ValueBox is works like rowspan and colspan HTML table attributes, but in BookFx cells inside a ValueBox is not always should be merged.

The 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 Merge(false).

In addition to automatically merging, BookFx supports automatically spanning, which is activated by methods AutoSpanRows, AutoSpanCols and their combination AutoSpan. In this mode a box and its inners are stretched to sizes of their containers through the last stretchable ValueBox. A 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

The ValueBox is intended for values and formulas. It can be created either by Make.Value or using implicit convertion from all necessary value types: string, int, decimal, DateTime, etc.

Formulas should begin with =. The ' is used for escaping. Only R1C1 reference style is supported.

Make.Value("=SUM(RC[1]:RC[3])")

Prototyping

BookFx supports using parts of other workbooks as prototypes:

Make
    .Proto(protoBook, "Prototype1")
    .Add("Slot1", "Value1")
    .Add("Slot2", Make.Row("Value2", "Value3"));

Here

• protoBook – byte[] of a xlsx-file content;
• "Prototype1" – name of the range in protoBook;
• "Slot1" and "Slot2" – names of ranges in Prototype1, in which other boxes can be placed.

See the example [S6ProtoBox.cs][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 Make.Sheet.

See also the example [S5ProtoSheet.cs][s5protosheet.cs].

API Reference

• Make - the model elements factory
  • Make.Book - make a Book
  • Make.Sheet - make a Sheet
  • Make.Row - make a RowBox
  • Make.Col - make a ColBox
  • Make.Stack - make a StackBox
  • Make.Value - make a ValueBox
  • Make.Proto - make a ProtoBox
  • Make.Style - make a BoxStyle
  • Make.Border - make a BoxBorder
• 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
  • `S