[F] Checkbox

[wpyoga]

🚀 Feature Proposal

Checkbox feature as available in MS Excel.

Motivation

I need the checkbox to take user input and record it in the cell. This is similar to data validation with TRUE/FALSE values, but much more convenient and intuitive for the user.

Example

ws.getCell("J1".value = true;
ws.getCell("K1".value = false;
ws.getCell("J2").value = { checkbox: true };
ws.getCell("K2").value = { checkbox: false };

Notes

Full disclosure: I have also requested this same feature on ExcelTS cjnoname/excelts#35

This is the minimum set of changes necessary for the functionality to work correctly on MS Excel Online. Please note that the files have been formatted with xmllint --format before diffing.

• [Content_Types].xml

  @@ -11,4 +11,5 @@
     <Default Extension="vml" ContentType="application/vnd.openxmlformats-officedocument.vmlDrawing"/>
     <Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
     <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>
  +  <Override PartName="/xl/featurePropertyBag/featurePropertyBag.xml" ContentType="application/vnd.ms-excel.featurepropertybag+xml"/>
   </Types>
  

• xl/featurePropertyBag/featurePropertyBag.xml (a whole new file)

  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <FeaturePropertyBags xmlns="http://schemas.microsoft.com/office/spreadsheetml/2022/featurepropertybag">
    <bag type="Checkbox"/>
    <bag type="XFControls">
      <bagId k="CellControl">0</bagId>
    </bag>
    <bag type="XFComplement">
      <bagId k="XFControls">1</bagId>
    </bag>
    <bag type="XFComplements" extRef="XFComplementsMapperExtRef">
      <a k="MappedFeaturePropertyBags">
        <bagId>2</bagId>
      </a>
    </bag>
  </FeaturePropertyBags>
  

• xl/_rels/workbook.xml.rels

  @@ -5,4 +5,5 @@
     <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>
     <Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
     <Relationship Id="rId5" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet2.xml"/>
  +  <Relationship Id="rId6" Type="http://schemas.microsoft.com/office/2022/11/relationships/FeaturePropertyBag" Target="featurePropertyBag/featurePropertyBag.xml"/>
   </Relationships>
  

• xl/styles.xml

  @@ -50,7 +50,7 @@
     <cellStyleXfs count="1">
       <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
     </cellStyleXfs>
  -  <cellXfs count="8">
  +  <cellXfs count="9">
       <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
       <xf numFmtId="49" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1" applyAlignment="1">
         <alignment horizontal="left"/>
  @@ -69,6 +69,13 @@
       </xf>
       <xf numFmtId="0" fontId="0" fillId="2" borderId="0" xfId="0" applyFill="1"/>
       <xf numFmtId="0" fontId="0" fillId="3" borderId="0" xfId="0" applyFill="1"/>
  +    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1">
  +      <extLst>
  +        <ext xmlns:xfpb="http://schemas.microsoft.com/office/spreadsheetml/2022/featurepropertybag" uri="{C7286773-470A-42A8-94C5-96B5CB345126}">
  +          <xfpb:xfComplement i="0"/>
  +        </ext>
  +      </extLst>
  +    </xf>
     </cellXfs>
     <cellStyles count="1">
       <cellStyle name="Normal" xfId="0" builtinId="0"/>
  

• xl/worksheets/sheet1.xml

  @@ -35,10 +35,10 @@
         </c>
       </row>
       <row r="2" spans="4:5">
  -      <c r="D2" s="3" t="b">
  +      <c r="D2" s="8" t="b">
           <v>1</v>
         </c>
  -      <c r="E2" s="3" t="b">
  +      <c r="E2" s="8" t="b">
           <v>0</v>
         </c>
       </row>
  

[protobi-pieter]

@wpyoga I'd be interested in implementing this for you. This looks nontrivial, so could be a few days of billable time. If interested please contact me directly for a quote, info@protobi.com. Or we could schedule smaller time to review/discuss what a successful PR would include.

[protobi-pieter]

@wpyoga (cc @cjnoname)

Below is a Claude analysis to implement this. We'd want to make sure we have examples of workbooks with existing checkboxes to ensure round trip from workbooks written in Windows and Mac.

Overview

User requests checkbox functionality for ExcelJS, mirroring Microsoft Excel's native checkbox feature, to capture user input through checkboxes and record boolean values in cells.

Proposed API

ws.getCell("J1").value = true;
ws.getCell("K1").value = false;
ws.getCell("J2").value = { checkbox: true };
ws.getCell("K2").value = { checkbox: false };

Complexity Assessment

Overall Complexity: Medium-High

This is not a quick/easy add - it requires significant architectural changes across multiple layers of the codebase.

Time Estimate:

• Development: 2-4 days for experienced developer
• Testing: 1-2 days
• Total: 3-6 days

Required Changes

1. New Value Type (Easy)

File: /lib/doc/enums.js:4-16

Add new value type:

ValueType: {
  Null: 0,
  Merge: 1,
  Number: 2,
  String: 3,
  Date: 4,
  Hyperlink: 5,
  Formula: 6,
  SharedString: 7,
  RichText: 8,
  Boolean: 9,
  Error: 10,
  Checkbox: 11,  // NEW
}

2. Cell Value Handling (Medium)

Files:

• /lib/doc/cell.js - Handle { checkbox: true/false } value format
• /lib/xlsx/xform/sheet/cell-xform.js

Changes needed:

• Update value detection logic in getValueType() function (lines 8-34)
• Add checkbox rendering logic in render() method (lines 199-275)
• Add checkbox parsing logic in parseClose() method (lines 343-426)

3. New File & Xform: Feature Property Bag (Medium-Hard)

This is the biggest addition - completely new infrastructure.

New files to create:

• /lib/xlsx/xform/feature-property-bag-xform.js
• /spec/unit/xlsx/xform/feature-property-bag-xform.spec.js

XML Structure to generate:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<FeaturePropertyBags xmlns="http://schemas.microsoft.com/office/spreadsheetml/2022/featurepropertybag">
  <bag type="Checkbox"/>
  <bag type="XFControls">
    <bagId k="CellControl">0</bagId>
  </bag>
  <bag type="XFComplement">
    <bagId k="XFControls">1</bagId>
  </bag>
  <bag type="XFComplements" extRef="XFComplementsMapperExtRef">
    <a k="MappedFeaturePropertyBags">
      <bagId>2</bagId>
    </a>
  </bag>
</FeaturePropertyBags>

Implementation notes:

• Inherit from BaseXform
• Handle nested <bag> and <bagId> elements
• Follow pattern similar to other xforms (e.g., data-validations-xform.js)

4. Content Types (Easy)

File: /lib/xlsx/xform/core/content-types-xform.js:108-115

Add override in render() method:

xmlStream.leafNode('Override', {
  PartName: '/xl/featurePropertyBag/featurePropertyBag.xml',
  ContentType: 'application/vnd.ms-excel.featurepropertybag+xml',
});

5. Workbook Relationships (Medium)

File: /lib/xlsx/xform/book/workbook-xform.js (likely location)

Add relationship:

<Relationship Id="rId6"
  Type="http://schemas.microsoft.com/office/2022/11/relationships/FeaturePropertyBag"
  Target="featurePropertyBag/featurePropertyBag.xml"/>

6. Styles System Changes (Hard)

File: /lib/xlsx/xform/style/styles-xform.js

Changes needed:

• Add xfComplement extension handling
• Track which cells use checkboxes
• Assign appropriate style index
• Add extLst extension mechanism to cellXfs

XML to add to cellXfs:

<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1">
  <extLst>
    <ext xmlns:xfpb="http://schemas.microsoft.com/office/spreadsheetml/2022/featurepropertybag"
      uri="{C7286773-470A-42A8-94C5-96B5CB345126}">
      <xfpb:xfComplement i="0"/>
    </ext>
  </extLst>
</xf>

Note: When checkboxes are present, cellXfs count increments and the checkbox style is inserted at a specific index (typically index 8 in the example).

7. Workbook Orchestration (Medium)

Files:

• /lib/xlsx/xlsx.js or /lib/stream/xlsx/workbook-writer.js

Changes needed:

• Ensure feature property bag file is created and written to zip
• Coordinate style ID updates when checkboxes are present
• Add feature property bag to workbook preparation phase

8. Worksheet XML Updates (Easy)

File: /lib/xlsx/xform/sheet/cell-xform.js

Checkbox cells need:

• Style reference: s="8" (or appropriate index)
• Type attribute: t="b" (boolean)
• Value: <v>1</v> for checked, <v>0</v> for unchecked

Example:

<c r="D2" s="8" t="b">
  <v>1</v>
</c>

Challenges & Risks

1. Style ID Management

The checkbox feature affects cell style IDs globally. When checkboxes exist, ALL cell styles need their IDs adjusted to make room for the checkbox style at the appropriate index. This requires careful coordination during the workbook preparation phase.

2. Feature Detection

Need to scan entire workbook during preparation phase to determine if any cells use checkboxes before generating styles. This adds overhead to the write process.

3. Backwards Compatibility

Need to ensure non-checkbox workbooks don't get unnecessary feature property bag files. The feature should only activate when checkboxes are actually used.

4. Excel Version Compatibility

This uses 2022 schemas (http://schemas.microsoft.com/office/spreadsheetml/2022/featurepropertybag). May not work with older Excel versions. Requires testing with:

• Excel 2019
• Excel 2021
• Excel 2022/365
• Excel Online
• LibreOffice Calc
• Google Sheets

5. Reading Existing Checkboxes

The parsing side (reading Excel files with checkboxes) is as complex as the writing side. Need to:

• Parse feature property bag XML
• Detect checkbox styles in styles.xml
• Convert cells with checkbox styles to { checkbox: true/false } format
• Handle workbooks with mixed checkbox and non-checkbox cells

6. Checkbox State vs Cell Value

Need to decide how to handle:

• Cell with checkbox but no value
• Cell with checkbox and formula result
• Cell value changes when checkbox is toggled by user
• Data validation on checkbox cells

XML File Structure Reference

The user provided complete diffs showing the minimal XML changes needed for Excel Online compatibility:

New File: xl/featurePropertyBag/featurePropertyBag.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<FeaturePropertyBags xmlns="http://schemas.microsoft.com/office/spreadsheetml/2022/featurepropertybag">
  <bag type="Checkbox"/>
  <bag type="XFControls">
    <bagId k="CellControl">0</bagId>
  </bag>
  <bag type="XFComplement">
    <bagId k="XFControls">1</bagId>
  </bag>
  <bag type="XFComplements" extRef="XFComplementsMapperExtRef">
    <a k="MappedFeaturePropertyBags">
      <bagId>2</bagId>
    </a>
  </bag>
</FeaturePropertyBags>

[Content_Types].xml - Addition

<Override PartName="/xl/featurePropertyBag/featurePropertyBag.xml"
  ContentType="application/vnd.ms-excel.featurepropertybag+xml"/>

xl/_rels/workbook.xml.rels - Addition

<Relationship Id="rId6"
  Type="http://schemas.microsoft.com/office/2022/11/relationships/FeaturePropertyBag"
  Target="featurePropertyBag/featurePropertyBag.xml"/>

xl/styles.xml - Changes

• Increment cellXfs count from "8" to "9"
• Add new xf element with extLst containing xfComplement

xl/worksheets/sheet1.xml - Changes

• Update cell style references from s="3" to s="8" for checkbox cells
• Cells have t="b" attribute and <v>1</v> or <v>0</v> values

Recommendation

Budget for this - it's a moderate-sized feature requiring 3-6 days of development and testing.

The user provided excellent XML examples which helps significantly, but integrating it properly into ExcelJS's architecture requires touching 7+ files across multiple subsystems:

• Cell values
• Xforms (transforms)
• Styles
• Content types
• Relationships
• Workbook orchestration

Alternatives

1. Write-only support (2-3 days): Only support creating checkboxes, don't parse existing ones
2. Read-only support (2-3 days): Parse checkboxes from existing files but don't create them
3. Full round-trip (3-6 days): Complete implementation - read Excel with checkboxes, modify, write back

Next Steps if Implementing

1. Start with unit tests for feature property bag xform
2. Implement basic checkbox value type and cell handling
3. Add feature property bag generation (conditional on checkbox presence)
4. Integrate with styles system
5. Add workbook-level orchestration
6. Test with real Excel files
7. Add integration tests
8. Document the feature