style-xlsx-m

ℹ️ About

Style-xlsx-m is a custom XLSX (SheetJs) version library with styling from xlsx-js-style

🔌 Installation

Install npm:

1npm install style-xlsx-m

Install yarn:

1yarn add style-xlsx-m

Install browser:

1<script src="dist/xlsx.js"></script>

🗒 Core API

• Refer to the SheetJS documentation for core API reference
• Current version of sheetjs used: 0.18.5
• Over time, will be added new and customized features.

🗒 What I added?

1. read or readFile

-- When you use read (second property put {type:'file'}) or readFile your file get cells style.

1// STEP 1 
2// './excample.xlsx' - is a path to your excel file.
3// Second property is a option { cellStyle: true } for get cells style (but now can't get in xls format).
4const workbook = XLSX.readFile('./example.xlsx',{ cellStyles: true });
5
6// STEP 2 Create data rows worksheet 
7let worksheet = [
8  "!col":[ { wpx: 10 }, { wpx: 15 }, { wpx:30 } ],
9  "!row":[ { hpx: 10 }, { hpx: 15 }, { hpx:30 } ],
10  { v: "line\nbreak", t: "s" },
11  { v: "Courier: 24", t: "s" },
12  { v: 45, t: "n" },
13  { v: 2023, t: "n" },
14];
15
16// STEP 3 Get workbook data with style and set workbook.Sheets.sheetname to worksheet
17XLSX.utils.book_append_sheet(workbook, worksheet, false, false, sheetname);
18
19// STEP 4 Save your file with style
20XLSX.writeFile(wb, "style-xlsx-m-example.xlsm");

This example shows how you can change the contents of a file without removing its old style. (But you can't get style in xls format becouse this format don't use xml)

2. PageSetup

1 const worksheet = [...]
2 
3 // default null
4 worksheet['!pageSetup'] = { 
5   paperSize: "9", // "9" for format A4
6   scale: "91", // 1 - 100%
7   fitToWidth: "0", // "0" || "1"
8   orientation: "landscape" // "landscape" || "portrait"
9 }

it can be used to print in desired format

🗒 Style API

-- Method to export file in excel with style get from xlsx-js-style (only function writeFile)

Cell Style JSON structure

• Cell styles are specified by a style object that roughly parallels the OpenXML structure.
• Style properties currently supported are: alignment, border, fill, font.

1
2 const A1 = { v: "line\nbreak", t: "s", s:{} } // key s is a style key for your cell
3
4 const BORDER_STYLE = 'think'
5
6 const COLOR_STYLE = {
7    rgb: 'FF0000'
8 }
9
10 // Aligment
11 A1.s = {
12       aligment: {
13       vertical: 'bottom', // "top" or "center" or "bottom"
14       horizontal : 'left', // "left" or "center" or "right"
15       wrapText: false, // true or false
16       textRotation: 0 // 0 to 180, or 255 // 180 is rotated down 180 degrees, 255 is special, aligned vertically
17    }
18 }
19
20 const border = { 
21    style: BORDER_STYLE, 
22    color: COLOR_STYLE,
23 }
24
25 // Border
26 A1.s = {
27    border: {
28        top: border, 
29        bottom: border,
30        left: border,
31        right: border,
32        diagonal: { 
33            style: BORDER_STYLE, 
34            color: COLOR_STYLE, 
35            diagonalUp: false, // true or false
36            diagonalDown: true/false // true or false
37        }
38    }
39 }
40
41 // Font
42 A1.s = {
43    font: {
44        color: COLOR_STYLE,
45        name: 'Calibri', // font name
46        sz: '11', // font size (points)
47        vertAlign: '', //  	"superscript" or "subscript"
48        bold: false, // true or false
49        italic: true, // true or false
50        strike: false, // true or false
51        underline: false, // true or false
52    }
53 }
54
55 // Fill 
56 A1.s = {
57    fill: {
58        patternType: 'none', // 'none' or 'solid'
59        fgColor: COLOR_STYLE,
60    }
61 }
62

Cell Style Example

1// STEP 1: Create a new workbook
2const wb = XLSX.utils.book_new();
3
4// STEP 2: Create data rows and styles
5let row = [
6  { v: "line\nbreak", t: "s", s: { alignment: { wrapText: true } } },
7  { v: "Courier: 24", t: "s", s: { font: { name: "Courier", sz: 24 } } },
8  { v: "fill: color", t: "s", s: { fill: { fgColor: { rgb: "E9E9E9" } } } },
9  { v: "bold & color", t: "s", s: { font: { bold: true, color: { rgb: "FF0000" } } } },
10];
11
12// STEP 3: Create worksheet with rows; Add worksheet to workbook
13const ws = XLSX.utils.aoa_to_sheet([row]);
14XLSX.utils.book_append_sheet(wb, ws, "readme demo");
15
16// STEP 4: Write Excel file to browser
17XLSX.writeFile(wb, "xlsx-js-style-demo.xlsx");

COLOR_STYLE {object} Properties

Colors for border, fill, font are specified as an name/value object - use one of the following: rgb key doesn't mean it's rgb format it's a hek format.

1 // JSON structure
2 const COLOR_STYLE = { // hex RGB value 
3    rgb: 'FFCC00'
4 }
5 const COLOR_STYLE = { // theme color index
6    theme: 4 // (0-n) // Theme color index 4 ("Blue, Accent 1")
7 }
8 const COLOR_STYLE = { // tint by percent
9    theme: 1, 
10    tint: 0.4 // ("Blue, Accent 1, Lighter 40%")
11 } 

BORDER_STYLE {string} Properties

Border style property is one of the following values:

dashDotDot, dashDot, dashed, dotted, hair, mediumDashDotDot, mediumDashDot, mediumDashed, medium, slantDashDot, thick, thin

🗒 TODO

• optimize the project
• add new features for tasks and libraries (for example: spreadsheet)
• If you have any ideas and/or suggestions write in my email (hp.sh.shahrom@gmail.com) or github

🙏 Thanks

This project is a fork of SheetJS/sheetjs combined with code and documentation from xlsx-js-style (by brentely).

🔖 License

Please consult the attached LICENSE file for details. All rights not explicitly granted by the Apache 2.0 License are reserved by the Original Author.