Add multiple worksheet to a stream workbook with ExcelJS

user_7364588 0
Viewed 11934

Context

  • NodeJS 10
  • ExcelJS 0.8.5
  • LibreOffice 5.1.6.2 on Ubuntu

Issue

I am trying to create a multi-sheet Excel file with ExcelJS. I am following the official documentation from the ExcelJS github page.

The first step is the creation of the workbook. In my case, i want a stream because i will append lot of datas.

// Create Excel Workbook Stream
const workbookStream = new Excel.stream.xlsx.WorkbookWriter({
  filename: path,
  useStyles: true,
  useSharedStrings: true,
});

Then i add sheet to the created workbook's stream as said into the documentation Worksheet Properties.

const sheet = workbookStream.addSheet('sheet1'); // Throw here

But in this way, i got the following error :

'Type error: workbookStream.addSheet is not a function

I have also found a code that do not throw but do not work and do not create many sheets.

const header = ['A', 'B', 'C'];

const sheet1 = Excel.addSheetOnWorkbook({
  workbook: workbookStream,
  name: 'sheet1',
});

const sheet2 = Excel.addSheetOnWorkbook({
  workbook: workbookStream,
  name: 'sheet2',
});

sheet.addRow(header).commit();

sheet.addRow(header).commit();

await workbookStream.commit();

In this case, only the sheet1 is created (opening with LibreOffice 5.1.6.2).

Any way to resolve this case with ExcelJS ?

exceljs node.js
1 Answers

I don't know how much help this is to you, but on my environment:

  • Windows 10 (10.0.17134.0)
  • Node 10.15.0
  • exceljs 1.6.0

Once I set the worksheet.state to visible, I can see it in LibreOffice:

const Excel = require('exceljs');
const workbook = new Excel.Workbook();
const fs = require('fs');
const filename = "test.xlsx";
const sheetNames = ["A", "B", "C", "D"];

sheetNames.forEach(sheetName => {
    let worksheet = workbook.addWorksheet(sheetName);
    // I believe this needs to be set to show in LibreOffice.
    worksheet.state = 'visible';
});

const stream = fs.createWriteStream(filename);
workbook.xlsx.write(stream)
.then(function() {
    console.log(`File: ${filename} saved!`);
    stream.end();
}).catch(error => {
    console.err(`File: ${filename} save failed: `, error);
});

Using the Streaming XLSX WorkbookWriter:

const Excel = require('exceljs');
const sheetNames = ["A", "B", "C", "D"];

const workbook = new Excel.stream.xlsx.WorkbookWriter( { filename: './streamed-workbook.xlsx' } );

sheetNames.forEach(sheetName => {
    let worksheet = workbook.addWorksheet(sheetName);
    worksheet.state = 'visible';
    worksheet.commit();
});

// Finished the workbook.
workbook.commit()
.then(function() {
    console.log(`Worksheet committed!`);
});

I'll test on an Ubuntu machine as well.

XSLX files are simply .zip files containing multiple .xml files, so you can inspect the XML data yourself.

To show the raw Xml (worksheet.xml) produced by LibreOffice versus exceljs:

LibreOffice

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
    <fileVersion appName="Calc"/>
    <workbookPr backupFile="false" showObjects="all" date1904="false"/>
    <workbookProtection/>
    <bookViews>
        <workbookView showHorizontalScroll="true" showVerticalScroll="true" showSheetTabs="true" xWindow="0" yWindow="0" windowWidth="16384" windowHeight="8192" tabRatio="500" firstSheet="0" activeTab="1"/>
    </bookViews>
    <sheets>
        <sheet name="A" sheetId="1" state="visible" r:id="rId2"/>
        <sheet name="B" sheetId="2" state="visible" r:id="rId3"/>
    </sheets>
    <calcPr iterateCount="100" refMode="A1" iterate="false" iterateDelta="0.001"/>
    <extLst>
        <ext xmlns:loext="http://schemas.libreoffice.org/" uri="{7626C862-2A13-11E5-B345-FEFF819CDC9F}">
            <loext:extCalcPr stringRefSyntax="CalcA1"/>
        </ext>
    </extLst>
</workbook>

exceljs

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x15" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main">
    <fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9303"/>
    <workbookPr defaultThemeVersion="164011" filterPrivacy="1"/>
    <sheets>
        <sheet sheetId="1" name="A" state="show" r:id="rId3"/>
        <sheet sheetId="2" name="B" state="show" r:id="rId4"/>
        <sheet sheetId="3" name="C" state="show" r:id="rId5"/>
        <sheet sheetId="4" name="D" state="show" r:id="rId6"/>
    </sheets>
    <calcPr calcId="171027"/>
</workbook>

One more thing

The sheet title can corrupt the XML file structure when some non alphanum characters are set.

Be careful about sheet title !

user_72372240
Related