Overview

The library is aimed to generate either PDF or XLSX documents based on HTML-template and JSON-data. The HTML-template processing is powered by Nunjunks engine.

Key features and advantages

• Allows to use the same template file to generate both PDF and XLSX documents. In case of XLSX, all tags but <table> tag are ignored.
• Supports Excel formulas.
• Formats Excel Sheets based on data types, i.e. numbers will be numbers, not a plain text.

Expected Behavior

• Every <table> will be converted into a separate Excel Sheet.
• A name of a sheet can be set by specifying a title attribute of a <table> tag.

  <table title="Details">...</table>
  <table title="Summary">...</table>

• To manually control a data format in Excel, use classes date, string:

  <td class="date">2012-01-01</td>
  <td class="string">1</td>

• When XLSX generation, every value which starts from = will be converted to a formula. To avoid conversion, use string class.
• When PDF generation, a page style can be set by providing the @page CSS attribute in the HTML-template (See more about @page on MDN's pages):

  @page {
    size: A4 landscape;
    margin: 10mm;
  }

• External fonts and CSS-files are supported:

  <link href="https://fonts.googleapis.com/css2?family=Fira+Mono&display=swap" rel="stylesheet">

• Supports paged.js library.

Tips

• How to display a page number:

  @page {
    @bottom-center {
      content: "Page " counter(page) " of " counter(pages);
    }
  }

• In case, complex header and footer are required, use paged.js library.
• How to display generation time:

  <h1>{{ (now() | date('YYYY-MM-DD HH:mm:ss')) }}</h1>

Build

Prepare Windows

1. Open PowerShell and execute wsl -d Ubuntu or wsl --install.
2. Run the following commands:

   sudo apt update
   sudo apt install -y nodejs npm
   sudo apt install -y libnss3 libatk1.0-0 libatk-bridge2.0-0 libcups2 libxkbcommon-x11-0 libgbm1 \
     build-essential libcairo2-dev libpango1.0-dev libjpeg-dev libgif-dev librsvg2-dev

3. Navigate to the bnc-report directory (project root) and execute:

   npm install

Running Tests

Note: For Windows OS, all runs must be performed in the wsl terminal, not in the PowerShell.

To run a certain test set, execute

node tests/html-to-pdf-converter.test.js

To enable debugging, add the flag --inspect-brk:

node --inspect-brk tests/html-to-pdf-converter.test.js

To manually build a report, use .report.js

node tests/.report.js PDF <njk-file-path> <json-file-path>

Paged.JS Specifics

• Does not support {size: landscape}. Requires an explicit format: {size: A4 landscape}
• Does not support {size: 100px}. Requires both dimensions: {size: 100px 100px}
• It is recommended to add body { margin: 0 } to avoid extra 8px of margin
• A default margin is 96px 96px

HtmlToXlsxConverter

Converts HTML tables (and optional text elements) into .xlsx Excel files. Uses Puppeteer to render HTML and extract computed styles, then generates Excel output via xlsx-js-style.

Usage

const HtmlToXlsxConverter = require("./html-to-xlsx-converter");

const converter = new HtmlToXlsxConverter();
const buffer = await converter.convert(html);
fs.writeFileSync("report.xlsx", buffer);

convert(html) accepts an HTML string and returns a Buffer with the .xlsx file content.

Tables

Each <table> becomes a separate Excel sheet. The sheet name is taken from the title attribute, falling back to Sheet1, Sheet2, etc.

<table title="Sales Report">
  <thead>
    <tr><th>Product</th><th>Revenue</th></tr>
  </thead>
  <tbody>
    <tr><td>Widget</td><td>1000</td></tr>
  </tbody>
</table>

<table title="Inventory">
  <tr><td>Item</td><td>Count</td></tr>
</table>

Supported cell features

• Numbers - pure numeric text is stored as Excel numbers
• Dates - cells with class date or text matching YYYY-MM-DD / DD.MM.YYYY are stored as Excel dates
• Currency - text starting with $, , , `` is parsed as numeric with currency format
• Formulas - text starting with = is stored as an Excel formula
• Merges - rowspan and colspan attributes are preserved

Supported styles

Styles are extracted via getComputedStyle(), so CSS classes, inline styles, and inherited styles all work.

| CSS Property | Excel Effect | |---|---| | font-weight: bold | Bold text | | font-style: italic | Italic text | | font-size | Font size (converted px to pt) | | color | Text color | | background-color | Cell fill color | | text-align | Horizontal alignment | | vertical-align | Vertical alignment | | border, border-* | Cell borders (thin/medium/thick) |

Colors can be specified as named colors, hex (#FF0000), rgb(), or rgba().

Text elements (data-xlsx-text)

Non-table content (report headers, footers, titles) can be included in Excel output using the data-xlsx-text attribute.

<div data-xlsx-text data-xlsx-sheet="Report"
     style="font-size: 18px; font-weight: bold;">
  Quarterly Sales Report
</div>

<table title="Report">
  <tr><td>Q1</td><td>Q2</td><td>Q3</td></tr>
  <tr><td>100</td><td>200</td><td>300</td></tr>
</table>

<div data-xlsx-text data-xlsx-sheet="Report"
     style="font-style: italic;">
  Generated: 2024-01-15
</div>

This produces:

| | A | B | C | |---|---|---|---| | 1 | Quarterly Sales Report (bold, 18px, merged across A-C) ||| | 2 | Q1 | Q2 | Q3 | | 3 | 100 | 200 | 300 | | 4 | Generated: 2024-01-15 (italic, merged across A-C) |||

Attributes

| Attribute | Required | Description | |---|---|---| | data-xlsx-text | Yes | Marks the element for Excel export | | data-xlsx-sheet | No | Associates with a sheet (matches <table title="...">) |

Placement rules

• DOM order determines whether text appears before or after the table
• Elements before their matching table in the DOM become rows above the table data
• Elements after their matching table become rows below the table data
• Multiple text elements maintain their DOM order
• Text rows are merged across all table columns

Standalone text

Text elements with no matching table (or without data-xlsx-sheet) create their own sheet:

<div data-xlsx-text data-xlsx-sheet="Notes"
     style="font-weight: bold;">Important Note</div>
<div data-xlsx-text data-xlsx-sheet="Notes">Details here</div>

This creates a "Notes" sheet with two rows. Elements without data-xlsx-sheet are grouped into a default "Sheet1".

Styling

Text elements support the same CSS styling as table cells. Styles are extracted via getComputedStyle(), so class-based styling works:

<style>
  .report-title { font-size: 20px; font-weight: bold; color: navy; }
</style>
<div class="report-title" data-xlsx-text data-xlsx-sheet="Report">
  Styled Title
</div>

Borders

Text row borders automatically match the border color of the associated table's cells.