mr-excel
v5.2.0
Published
A versatile JavaScript library for effortlessly generating .xlsx files from input objects. Seamlessly create Excel spreadsheets with data, formatting, formulas, and more.
Downloads
21,535
Maintainers
mohammadrezaeicodeKeywords
Readme
MR Excel
MR-Excel is a JavaScript library designed for reading and writing Excel files. This library allows you to extract data from Excel files, and when it comes to writing, it offers a range of features such as commenting, styling, Formulas, merging cells,Grouping rows, conditional formatting (Excel), multi-style values, and functions that can be used for cell merging, adding styles and commenting functionalities. Since version 5.0.0, we exclusively build via Vite. The path of the TypeScript interface has been updated (link). For further details, refer to the TypeScript example. Example: Express | CDN | Typescript | Angular
Table of Contents
[!NOTE] You can return to the table of contents by clicking on ⬆️
InstallationImportGetting StartedgenerateExcelconvertTableToExcelsideBySideLineByLinethemeBaseGenerateextractExcelData🆕 generateCSV && generateTextinterfaceMigrate Version
Introduction ⬆️
Our library offers comprehensive support for a wide range of features, including data formatting, formulas, styles, merged cells, and grouped rows. Express/backend example, TypeScript example
In version 2.1.0, we introduced a new feature called "convertTableToExcel," which enables the generation of an Excel file from a specified table or DOM element (table). The provided query is expected to be a valid input for the querySelector method. This enhancement allows for greater flexibility and convenience when creating Excel files directly from HTML tables.
We have four functions that are defined with specific use cases as follows:
generateExcel: This is the primary and most important function that serves as the entry point for all other functions. Its responsibility is to generate an Excel file based on the received input data. We will provide examples of the various options that can be utilized.
convertTableToExcel: This function is designed exclusively for client-side use. It requires passing a DOM element (a table element) as a parameter. The output of this function is an Excel file generated from the provided table.
sideBySideLineByLine: This function offers the capability to generate a single-sheet Excel file containing multiple tables side by side and line by line.
themeBaseGenerate: Within this function, we utilize color palettes from https://colorhunt.co/. It accepts data and a theme index as inputs, then generates an Excel file with the selected theme applied.Themes
extractExcelData: We provide 'extract-excel-data-usage,' which takes the URL of an Excel file that needs to be read, retrieves and reads the data, and returns an object containing the sheets as the result.
🆕 generateCSV && generateText:The generateCSV function produces a .csv file based on the excelTable output, while the generateText function generates a .txt file. This function includes a boolean property; if set to true, the generated files will be compressed into a zip file. It is important to note that .csv and .txt files do not support styles, formulas, and other similar features.
Installation ⬆️
Via CDN
You can utilize our library, which comes bundled with Vite, by including the following link:
<script src="https://unpkg.com/mr-excel@latest/dist/excel-table.umd.js"></script>Using a Package Manager To seamlessly integrate our library, you can install it using your preferred package manager:
Via npm:
npm install mr-excelUsing yarn:
yarn add mr-excelAlternatively, you have the option to use pnpm:
pnpm install mr-excelChoose the package manager that suits your workflow, and effortlessly bring the power of our library into your project, enabling smooth generation of Excel tables with ease and efficiency.
Import ⬆️
Depending on the installation method, use the appropriate approach:
CDN:
If you opt for a CDN, after adding the script, you only need the ExcelTable keyword for access to functions.
Javascript(type: module) OR TypeScript:
For JavaScript or TypeScript files that use the module type (indicated by adding type: module to the package.json file), employ the following code:
import { generateExcel } from "mr-excel";
// or
import * as ExcelTable from "mr-excel";
ExcelTable.generateExcel();Lazy Import:
We recommend using this approach on the client side for import:
import("mr-excel").then(m => {
m.generateExcel();
});Ensure you choose the appropriate method based on your installation preferences and project requirements.
Getting Started ⬆️
After adding the library to your project, generating XLSX files becomes straightforward. You can achieve this by creating a data object similar to the code snippet below:
How to use themeBaseGenerate
let data = {
sheet: [
{
headers: [
{
label: "ID",
text: "ID",
},
{
label: "FirstName",
text: "First Name",
},
{
label: "LastName",
text: "Last Name",
},
{
label: "Age",
text: "Age",
},
{
label: "Email",
text: "Email",
},
{
label: "PhoneNumber",
text: "PhoneNumber",
},
{
label: "Address",
text: "Address",
},
{
label: "Occupation",
text: "Occupation",
},
],
data: [
{
ID: 1,
FirstName: "John",
LastName: "Smith",
Age: 32,
Email: "[email protected]",
PhoneNumber: "555-123-4567",
Address: "123 Main St, City",
Occupation: "Engineer",
},
{
ID: 2,
FirstName: "Jane",
LastName: "Doe",
Age: 28,
Email: "[email protected]",
PhoneNumber: "555-987-6543",
Address: "456 Elm St, Town",
Occupation: "Teacher",
},
{
ID: 3,
FirstName: "David",
LastName: "Johnson",
Age: 45,
Email: "[email protected]",
PhoneNumber: "555-555-5555",
Address: "789 Oak St, City",
Occupation: "Doctor",
},
{
ID: 4,
FirstName: "Sarah",
LastName: "Brown",
Age: 22,
Email: "[email protected]",
PhoneNumber: "555-321-6549",
Address: "101 Pine St, Town",
Occupation: "Student",
},
{
ID: 5,
FirstName: "Michael",
LastName: "Wilson",
Age: 38,
Email: "[email protected]",
PhoneNumber: "555-777-8888",
Address: "246 Maple St, City",
Occupation: "Lawyer",
},
{
ID: 6,
FirstName: "Emily",
LastName: "Davis",
Age: 29,
Email: "[email protected]",
PhoneNumber: "555-444-3333",
Address: "555 Birch St, Town",
Occupation: "Nurse",
},
{
ID: 7,
FirstName: "Daniel",
LastName: "Lee",
Age: 31,
Email: "[email protected]",
PhoneNumber: "555-666-9999",
Address: "777 Cedar St, City",
Occupation: "Software Dev",
},
{
ID: 8,
FirstName: "Olivia",
LastName: "White",
Age: 27,
Email: "[email protected]",
PhoneNumber: "555-222-1111",
Address: "888 Redwood St, Town",
Occupation: "Artist",
},
{
ID: 9,
FirstName: "James",
LastName: "Anderson",
Age: 40,
Email: "[email protected]",
PhoneNumber: "555-888-3333",
Address: "333 Oak St, City",
Occupation: "Accountant",
},
{
ID: 10,
FirstName: "Sophia",
LastName: "Martinez",
Age: 24,
Email: "[email protected]",
PhoneNumber: "555-999-7777",
Address: "666 Pine St, Town",
Occupation: "Engineer",
},
],
},
],
};
ExcelTable.themeBaseGenerate(data, 0);Or:
let data = [
{
ID: 1,
FirstName: "John",
LastName: "Smith",
Age: 32,
Email: "[email protected]",
PhoneNumber: "555-123-4567",
Address: "123 Main St, City",
Occupation: "Engineer",
},
{
ID: 2,
FirstName: "Jane",
LastName: "Doe",
Age: 28,
Email: "[email protected]",
PhoneNumber: "555-987-6543",
Address: "456 Elm St, Town",
Occupation: "Teacher",
},
{
ID: 3,
FirstName: "David",
LastName: "Johnson",
Age: 45,
Email: "[email protected]",
PhoneNumber: "555-555-5555",
Address: "789 Oak St, City",
Occupation: "Doctor",
},
{
ID: 4,
FirstName: "Sarah",
LastName: "Brown",
Age: 22,
Email: "[email protected]",
PhoneNumber: "555-321-6549",
Address: "101 Pine St, Town",
Occupation: "Student",
},
{
ID: 5,
FirstName: "Michael",
LastName: "Wilson",
Age: 38,
Email: "[email protected]",
PhoneNumber: "555-777-8888",
Address: "246 Maple St, City",
Occupation: "Lawyer",
},
{
ID: 6,
FirstName: "Emily",
LastName: "Davis",
Age: 29,
Email: "[email protected]",
PhoneNumber: "555-444-3333",
Address: "555 Birch St, Town",
Occupation: "Nurse",
},
{
ID: 7,
FirstName: "Daniel",
LastName: "Lee",
Age: 31,
Email: "[email protected]",
PhoneNumber: "555-666-9999",
Address: "777 Cedar St, City",
Occupation: "Software Dev",
},
{
ID: 8,
FirstName: "Olivia",
LastName: "White",
Age: 27,
Email: "[email protected]",
PhoneNumber: "555-222-1111",
Address: "888 Redwood St, Town",
Occupation: "Artist",
},
{
ID: 9,
FirstName: "James",
LastName: "Anderson",
Age: 40,
Email: "[email protected]",
PhoneNumber: "555-888-3333",
Address: "333 Oak St, City",
Occupation: "Accountant",
},
{
ID: 10,
FirstName: "Sophia",
LastName: "Martinez",
Age: 24,
Email: "[email protected]",
PhoneNumber: "555-999-7777",
Address: "666 Pine St, Town",
Occupation: "Engineer",
},
];
ExcelTable.themeBaseGenerate(data, 12);How to use convertTableToExcel
ExcelTable.convertTableToExcel("#table");
-------------------------------------------------------------------
let element = document.querySelector("#table");
ExcelTable.convertTableToExcel(null, element, true);
-------------------------------------------------------------------
const rowF = (value, index, from) => {
return 50
}
const colF = (value, index) => {
return value * 0.19
}
ExcelTable.convertTableToExcel("#table", null, true, rowF, colF)
How to use extractExcelData
ExcelTable.extractExcelData(your excel url);🆕 How to use generateCSV && generateText
const data = {
sheet: [
{
headers: [
{
label: "test",
text: "Test",
},
{
label: "_id",
text: "ID",
formula: {
type: "MAX",
styleId: "formulaStyle",
},
},
],
data: [
{
_id: 0.3,
test: "test1",
},
{
_id: 2,
test: "test2",
},
{
_id: 3,
test: "test3",
},
{
_id: 4,
test: "test4",
},
{
_id: 5,
test: "test5",
},
{
_id: 6,
test: "test6",
},
{
_id: 7,
test: "test7",
},
{
_id: 8,
test: "test8",
},
{
_id: 9,
test: "test9",
},
{
_id: 10,
test: "test10",
},
{
_id: 11,
test: "test11",
},
],
},
{
headers: [
{
label: "test",
text: "Test",
},
{
label: "_id",
text: "ID",
},
],
data: [
{
_id: 1,
test: "test1",
},
{
_id: 2,
test: "test2",
},
{
_id: 3,
test: "test3",
},
{
_id: 4,
test: "test4",
},
{
_id: 5,
test: "test5",
},
{
_id: 6,
test: "test6",
},
{
_id: 7,
test: "test7",
},
{
_id: 8,
test: "test8",
},
{
_id: 9,
test: "test9",
},
{
_id: 10,
test: "test10",
},
{
_id: 11,
test: "test11",
},
],
},
],
};
ExcelTable.generateCSV(data, true);
ExcelTable.generateText(data, true); How to use sideBySideLineByLine
sideBySideLineByLine is a new feature that enables the generation of multiple tables within a single Excel sheet.
const sideData = [
[
{
sheetName: "sheetName",
spaceX: 1,
spaceY: 1,
headers: [
{
label: "id",
text: "id",
},
],
data: [
{ id: 11 },
{ id: 10 },
{ id: 9 },
{ id: 8 },
{ id: 7 },
{ id: 6 },
{ id: 5 },
{ id: 4 },
{ id: 3 },
{ id: 2 },
{ id: 1 },
],
},
{
sheetName: "sheetName",
spaceX: 1,
spaceY: 1,
headers: [
{
label: "el",
text: "el",
},
],
data: [
{ el: 11 },
{ el: 10 },
{ el: 9 },
{ el: 8 },
{ el: 7 },
{ el: 4 },
{ el: 3 },
{ el: 2 },
{ el: 1 },
],
},
],
[
{
sheetName: "sheetName",
spaceX: 1,
spaceY: 1,
headers: [
{
label: "id",
text: "id",
},
{ label: "test", text: "test" },
],
data: [
{ id: 1 },
{ id: 2 },
{ id: 3 },
{ id: 4 },
{ id: 5 },
{ id: 6 },
{ id: 7 },
{ id: 8 },
{ id: 9 },
{ id: 10 },
{ id: 11 },
],
},
{
sheetName: "sheetName1",
spaceX: 1,
spaceY: 1,
headers: [
{
label: "id",
text: "id",
},
{ label: "test", text: "test" },
],
data: [
{ id: 1 },
{ id: 2 },
{ id: 3 },
{ id: 4 },
{ id: 5 },
{ id: 6 },
{ id: 7 },
{ id: 8 },
{ id: 9 },
{ id: 10 },
{ id: 11 },
],
},
{
sheetName: "sheetName",
spaceX: 1,
spaceY: 1,
headers: [
{
label: "id",
text: "id",
},
{ label: "test", text: "test" },
],
data: [
{ test: "test14", id: "u1i1r23" },
{ test: "test13", id: "u2i2r24" },
{ test: "test12", id: "u3i3r25" },
{ test: "test11", id: "u4i4r26" },
{ test: "test10", id: "u5i5r27" },
{ test: "test9", id: "u6i6r28" },
{ test: "test8", id: "u7i7r29" },
{ test: "test7", id: "u8i8r30" },
{ test: "test6", id: "u9i9r31" },
{ test: "test5", id: "ui1010r32" },
{ test: "test4", id: "ui1111r33" },
{ test: "test3" },
{ test: "test2" },
{ test: "test1" },
],
},
],
];
ExcelTable.sideBySideLineByLine(sideData);generateExcel
//<https://colorhunt.co/palette/f9ed69f08a5db83b5e6a2c70>
const colorPalette = {
c1: "F9ED69",
c2: "#F08A5D",
c3: "B83B5E",
c4: "6A2C70",
};
const data = {
creator: "mr",
created: "2023-08-06T07:22:40Z",
modified: "2023-08-06T07:22:40Z",
styles: {
formulaStyle: {
backgroundColor: "B83B5E",
border: {
full: {
style: "medium",
color: "F9ED69",
},
},
},
},
sheet: [
{
name: "Test",
formula: {
B16: {
type: "SUM",
start: "B2",
end: "B8",
styleId: "formulaStyle",
},
},
tabColor: "B83B5E",
headers: [
{
label: "test",
text: "Test",
},
{
label: "_id",
text: "ID",
formula: {
type: "MAX",
styleId: "formulaStyle",
},
},
],
data: [
{
_id: 0.3,
test: "test1",
},
{
_id: 2,
test: "test2",
},
{
_id: 3,
test: "test3",
},
{
_id: 4,
test: "test4",
},
{
_id: 5,
test: "test5",
},
{
_id: 6,
test: "test6",
},
{
_id: 7,
test: "test7",
},
{
_id: 8,
test: "test8",
},
{
_id: 9,
test: "test9",
},
{
_id: 10,
test: "test10",
},
{
_id: 11,
test: "test11",
},
],
},
{
headers: [
{
label: "test",
text: "Test",
},
{
label: "_id",
text: "ID",
},
],
data: [
{
_id: 1,
test: "test1",
},
{
_id: 2,
test: "test2",
},
{
_id: 3,
test: "test3",
},
{
_id: 4,
test: "test4",
},
{
_id: 5,
test: "test5",
},
{
_id: 6,
test: "test6",
},
{
_id: 7,
test: "test7",
},
{
_id: 8,
test: "test8",
},
{
_id: 9,
test: "test9",
},
{
_id: 10,
test: "test10",
},
{
_id: 11,
test: "test11",
},
],
},
],
};
ExcelTable.generateExcel(data);General option ⬆️
Each sheet has options for customization. You can change the sheet name using name, adjust the tab name color with tabColor, control visibility with state, add protection to a sheet via protectionOption, and implement sorting and filtering using sortAndFilter. In the example below, we will demonstrate how to utilize these properties. Additionally, for Excel file information, we offer options such as creator, created, notSave, and modified.
const data = {
notSave: true,
creator: "mr",
created: "2023-08-12T02:08:04.469Z",
modified: "2023-08-12T02:08:04.469Z",
sheet: [
{
name: "family record",
tabColor: "#a1b4c6",
sortAndFilter: {
mode: "all",
},
protectionOption: {
sheet: 1,
formatCells: 0,
formatColumns: 0,
formatRows: 0,
insertColumns: 0,
insertRows: 0,
insertHyperlinks: 0,
deleteColumns: 0,
deleteRows: 0,
sort: 0,
autoFilter: 0,
pivotTables: 0,
},
headers: [
{
label: "id",
text: "ID",
},
{
label: "name",
text: "Name",
},
{
label: "surname",
text: "Surname",
},
{
label: "parentId",
text: "Parent Id",
},
{
label: "work",
text: "Work",
},
{
label: "birthDate",
text: "Birth Date",
},
],
data: [
{
id: 7209449538085,
name: "Tabitha",
surname: "Terry",
parentId: 6998520522169,
work: "Computer repair technician",
birthDate: "1853-04-10T01:23:16.181Z",
},
{
id: 4132538644996,
name: "Grace",
surname: "MacTavish",
parentId: 6840142476821,
work: "Retired",
birthDate: "1854-04-03T08:51:19.825Z",
},
{
id: 778493423064,
name: "Bailey",
surname: "Byram",
parentId: 7137102781494,
work: "Occupational Therapist- Neonatal/ Pediatric",
birthDate: "1852-08-13T18:07:57.408Z",
},
{
id: 510141747289,
name: "Sherman",
surname: "Joseph",
parentId: 602149579197,
work: "work from home",
birthDate: "1854-12-12T05:48:31.806Z",
},
{
id: 5513277402976,
name: "Ryder",
surname: "Watrous",
parentId: 7435302183884,
work: "Welder",
birthDate: "1854-08-18T04:11:04.736Z",
},
{
id: 1032906540606,
name: "Phoenix",
surname: "Netter",
parentId: 3204642808212,
work: "Unemployed",
birthDate: "1854-07-19T07:53:58.843Z",
},
{
id: 343574032284,
name: "Tonya",
surname: "Carpenter",
parentId: 3709985684199,
work: "Pulmonologist",
birthDate: "1852-04-20T12:44:08.362Z",
},
{
id: 9497014533965,
name: "Coral",
surname: "Hoskins",
parentId: 3497153671269,
work: "Unemployed",
birthDate: "1854-12-01T22:08:59.891Z",
},
{
id: 4998374693826,
name: "Billie",
surname: "Guthrie",
parentId: 1555796128163,
work: "Skomorokh",
birthDate: "1853-11-10T14:06:54.037Z",
},
{
id: 95132218987,
name: "Gertrude",
surname: "Clark",
parentId: 2324519652998,
work: "Unemployed",
birthDate: "1852-12-22T20:12:13.237Z",
},
],
},
{
state: "hidden",
headers: [
{
label: "id",
text: "ID",
},
{
label: "name",
text: "Name",
},
{
label: "surname",
text: "Surname",
},
{
label: "parentId",
text: "Parent Id",
},
{
label: "work",
text: "Work",
},
{
label: "birthDate",
text: "Birth Date",
},
],
data: [
{
id: 7209449538085,
name: "Tabitha",
surname: "Terry",
parentId: 6998520522169,
work: "Computer repair technician",
birthDate: "1853-04-10T01:23:16.181Z",
},
{
id: 4132538644996,
name: "Grace",
surname: "MacTavish",
parentId: 6840142476821,
work: "Retired",
birthDate: "1854-04-03T08:51:19.825Z",
},
{
id: 778493423064,
name: "Bailey",
surname: "Byram",
parentId: 7137102781494,
work: "Occupational Therapist- Neonatal/ Pediatric",
birthDate: "1852-08-13T18:07:57.408Z",
},
{
id: 510141747289,
name: "Sherman",
surname: "Joseph",
parentId: 602149579197,
work: "work from home",
birthDate: "1854-12-12T05:48:31.806Z",
},
{
id: 5513277402976,
name: "Ryder",
surname: "Watrous",
parentId: 7435302183884,
work: "Welder",
birthDate: "1854-08-18T04:11:04.736Z",
},
{
id: 1032906540606,
name: "Phoenix",
surname: "Netter",
parentId: 3204642808212,
work: "Unemployed",
birthDate: "1854-07-19T07:53:58.843Z",
},
{
id: 343574032284,
name: "Tonya",
surname: "Carpenter",
parentId: 3709985684199,
work: "Pulmonologist",
birthDate: "1852-04-20T12:44:08.362Z",
},
{
id: 9497014533965,
name: "Coral",
surname: "Hoskins",
parentId: 3497153671269,
work: "Unemployed",
birthDate: "1854-12-01T22:08:59.891Z",
},
{
id: 4998374693826,
name: "Billie",
surname: "Guthrie",
parentId: 1555796128163,
work: "Skomorokh",
birthDate: "1853-11-10T14:06:54.037Z",
},
{
id: 95132218987,
name: "Gertrude",
surname: "Clark",
parentId: 2324519652998,
work: "Unemployed",
birthDate: "1852-12-22T20:12:13.237Z",
},
{
id: 4353606351491,
name: "Corinna",
surname: "Powell",
parentId: 6303270085856,
work: "Business analyst",
birthDate: "1853-05-13T14:00:27.525Z",
},
{
id: 9821977853501,
name: "Alexander",
surname: "Rowlands",
parentId: 5281151854805,
work: "work from home",
birthDate: "1852-07-29T02:34:26.818Z",
},
{
id: 1789259068865,
name: "Khloe",
surname: "Allsebrook",
parentId: 9085712911231,
work: "Internet celebrity",
birthDate: "1853-10-10T01:28:17.141Z",
},
{
id: 1547071460137,
name: "Sky",
surname: "Hyde",
parentId: 3932330547777,
work: "Retired",
birthDate: "1854-11-03T05:06:37.477Z",
},
{
id: 8054019194308,
name: "Niles",
surname: "Dudley",
parentId: 9487606369042,
work: "Porter",
birthDate: "1852-09-01T05:46:03.040Z",
},
{
id: 6759490924297,
name: "Valda",
surname: "Bye",
parentId: 5146431233541,
work: "Management",
birthDate: "1853-09-29T00:33:43.968Z",
},
{
id: 6625148457816,
name: "Keaton",
surname: "Boothe",
parentId: 4935663844945,
work: "Mental Health Counselor",
birthDate: "1854-10-25T16:56:16.488Z",
},
{
id: 5968154253225,
name: "Gill",
surname: "Voyles",
parentId: 1358001926693,
work: "Ophthalmologist",
birthDate: "1853-09-13T06:48:43.632Z",
},
{
id: 7963337332727,
name: "Josiah",
surname: "Engleman",
parentId: 4661422144081,
work: "Unemployed",
birthDate: "1854-05-31T14:12:01.180Z",
},
{
id: 7276643575609,
name: "Luca",
surname: "Alderman",
parentId: 1257836499862,
work: "Unemployed",
birthDate: "1852-07-04T12:18:59.130Z",
},
{
id: 4670743239553,
name: "Kathleen",
surname: "Walle",
parentId: 4441682230999,
work: "Computer scientist",
birthDate: "1854-08-22T11:02:53.945Z",
},
{
id: 7400518637533,
name: "Deacon",
surname: "Humpherys",
parentId: 1446747090804,
work: "Conservation officer",
birthDate: "1854-06-07T15:31:11.006Z",
},
{
id: 6500156651299,
name: "Carina",
parentId: 5968154253225,
surname: "Voyles",
work: "Chiropodist",
birthDate: "1867-02-15T16:59:13.152Z",
},
{
id: 1706294044283,
name: "Tucker",
parentId: 7963337332727,
surname: "Engleman",
work: "Podiatrist",
birthDate: "1866-12-09T07:32:17.900Z",
},
{
id: 3173442171339,
name: "Clarissa",
surname: "Harvie",
parentId: 8355293899833,
work: "Botanist",
birthDate: "1853-03-29T17:49:36.408Z",
},
{
id: 5788750055334,
name: "Melina",
parentId: 6500156651299,
surname: "Voyles",
work: "Welder",
birthDate: "1882-08-16T00:50:47.934Z",
},
{
id: 8843316769405,
name: "Keith",
surname: "Ludington",
parentId: 6356556019596,
work: "Usher",
birthDate: "1852-06-15T06:27:32.233Z",
},
{
id: 4207038883417,
name: "Aaliyah",
surname: "Woodville",
parentId: 5819329552655,
work: "Scop",
birthDate: "1854-10-08T02:36:29.656Z",
},
{
id: 8313555968957,
name: "Harper",
surname: "Allcock",
parentId: 7731940327551,
work: "Songwriter",
birthDate: "1853-05-09T23:05:40.195Z",
},
{
id: 8321384691545,
name: "Johnny",
parentId: 778493423064,
surname: "Byram",
work: "Copy editor",
birthDate: "1873-12-17T06:16:58.798Z",
},
{
id: 8774024011461,
name: "Jody",
parentId: 4132538644996,
surname: "MacTavish",
work: "Graphic designer",
birthDate: "1872-08-31T01:04:39.456Z",
},
{
id: 3306066401344,
name: "Mercedes",
surname: "Roberts",
parentId: 6748854838600,
work: "Feller",
birthDate: "1852-05-10T11:58:05.415Z",
},
{
id: 6496588012690,
name: "Renee",
parentId: 8054019194308,
surname: "Dudley",
work: "Plumber",
birthDate: "1873-12-17T21:54:18.411Z",
},
{
id: 2454674611066,
name: "Jill",
parentId: 6759490924297,
surname: "Bye",
work: "Dermatology Physician Assistant",
birthDate: "1865-03-20T17:31:14.827Z",
},
{
id: 8810365866300,
name: "Townsend",
parentId: 6496588012690,
surname: "Dudley",
work: "Psychiatric Nurse Practitioner",
birthDate: "1885-09-06T14:23:00.439Z",
},
{
id: 1971028749432,
name: "George",
surname: "Georgeson",
parentId: 3375820364841,
work: "Director",
birthDate: "1854-10-26T04:21:32.595Z",
},
{
id: 1113599419684,
name: "Jess",
surname: "Summers",
parentId: 8197445224794,
work: "Showrunner",
birthDate: "1853-06-20T11:49:42.822Z",
},
{
id: 260692577513,
name: "Salma",
surname: "Bagshaw",
parentId: 8646582409037,
work: "Technical director",
birthDate: "1854-08-02T11:08:13.512Z",
},
{
id: 4717646566698,
name: "Jakob",
parentId: 7400518637533,
surname: "Humpherys",
work: "Draper",
birthDate: "1875-09-29T02:59:25.272Z",
},
{
id: 2812851588162,
name: "Ezekiel",
surname: "Glanton",
parentId: 8694260373371,
work: "Retired",
birthDate: "1853-07-25T22:30:11.069Z",
},
{
id: 546554428930,
name: "Darrin",
parentId: 7963337332727,
surname: "Engleman",
work: "Healthcare science",
birthDate: "1867-11-05T12:21:08.349Z",
},
{
id: 5568159710759,
name: "Kirsten",
parentId: 6496588012690,
surname: "Dudley",
work: "work from home",
birthDate: "1890-06-13T12:43:09.597Z",
},
{
id: 3385269771452,
name: "Emerald",
parentId: 6496588012690,
surname: "Dudley",
work: "Retired",
birthDate: "1892-05-01T07:40:24.011Z",
},
{
id: 8533172021814,
name: "Andre",
parentId: 8321384691545,
surname: "Byram",
work: "Retired",
birthDate: "1892-02-25T01:12:38.146Z",
},
{
id: 4710524419131,
name: "Patty",
surname: "Dick",
parentId: 1760988033690,
work: "Technical writer",
birthDate: "1853-01-11T14:35:24.298Z",
},
{
id: 1805461552938,
name: "Elisha",
surname: "Bradford",
parentId: 8622160581630,
work: "Theatre practitioner",
birthDate: "1853-06-30T19:11:12.105Z",
},
{
id: 6542853113042,
name: "Zoe",
parentId: 3173442171339,
surname: "Harvie",
work: "Showgirl",
birthDate: "1870-12-08T18:18:09.844Z",
},
{
id: 1718991242108,
name: "Drew",
surname: "Ashby",
parentId: 3010550761310,
work: "Retired",
birthDate: "1854-10-23T05:29:23.476Z",
},
{
id: 1334633156399,
name: "Marie",
surname: "Tibbets",
parentId: 2575362966990,
work: "Geotechnical Engineer",
birthDate: "1854-09-01T14:29:47.329Z",
},
{
id: 5668998711075,
name: "Dulcie",
surname: "Derwin",
parentId: 7518120488089,
work: "Emcee",
birthDate: "1854-03-09T04:56:18.697Z",
},
{
id: 248786146102,
name: "Shania",
surname: "Fleetwood",
parentId: 7804160535710,
work: "Station",
birthDate: "1854-10-06T02:01:08.206Z",
},
{
id: 9525762609317,
name: "Ana",
parentId: 7209449538085,
surname: "Terry",
work: "Manager (Guard)",
birthDate: "1867-07-30T11:59:23.739Z",
},
{
id: 934688923249,
name: "Alicia",
parentId: 6542853113042,
surname: "Harvie",
work: "Blogger",
birthDate: "1890-10-06T17:26:42.673Z",
},
{
id: 7306715236916,
name: "Petula",
surname: "Newey",
parentId: 1042642003017,
work: "Geoff",
birthDate: "1852-10-04T08:37:28.785Z",
},
{
id: 8234536999381,
name: "Mary",
surname: "Rose",
parentId: 5386302345101,
work: "Signal maintainer",
birthDate: "1854-02-15T23:14:32.174Z",
},
{
id: 7494359933717,
name: "Brittany",
parentId: 1805461552938,
surname: "Bradford",
work: "Unemployed",
birthDate: "1875-01-06T09:30:49.999Z",
},
{
id: 7049262735491,
name: "Juniper",
surname: "Marks",
parentId: 9127368958939,
work: "work from home",
birthDate: "1854-11-26T11:05:44.683Z",
},
{
id: 7781822521224,
name: "Edris",
surname: "Godwin",
parentId: 6016143008277,
work: "work from home",
birthDate: "1854-09-13T08:22:46.191Z",
},
{
id: 9078112227633,
name: "Kaitlin",
surname: "Minogue",
parentId: 95176825076,
work: "Clinical pharmaceutical scientist",
birthDate: "1852-05-08T21:59:27.773Z",
},
{
id: 3055221397713,
name: "Lauretta",
parentId: 6759490924297,
surname: "Bye",
work: "Track inspector",
birthDate: "1867-05-21T06:07:21.273Z",
},
{
id: 3794107972026,
name: "Dorothy",
surname: "Bostick",
parentId: 6850128149597,
work: "Go-go dancer",
birthDate: "1853-03-06T19:01:31.143Z",
},
{
id: 1698494645377,
name: "Cameron",
surname: "Torney",
parentId: 770004791023,
work: "Advertising designer",
birthDate: "1854-07-17T07:16:12.429Z",
},
{
id: 841895938898,
name: "Shepherd",
parentId: 4717646566698,
surname: "Humpherys",
work: "Quilter",
birthDate: "1897-02-23T22:01:59.520Z",
},
{
id: 7914083691988,
name: "Alisha",
surname: "Wind",
parentId: 1656884957449,
work: "Veterinarian",
birthDate: "1853-04-04T07:49:55.453Z",
},
{
id: 5015337941343,
name: "Agnes",
parentId: 9821977853501,
surname: "Rowlands",
work: "Rhapsode",
birthDate: "1865-09-14T16:07:21.238Z",
},
{
id: 6498601438355,
name: "Harvard",
parentId: 1971028749432,
surname: "Georgeson",
work: "Retired",
birthDate: "1874-02-03T05:44:32.483Z",
},
{
id: 6162073883494,
name: "Lizzie",
surname: "Allitt",
parentId: 7443156411879,
work: "work from home",
birthDate: "1854-02-09T14:41:55.210Z",
},
{
id: 4027876312592,
name: "Samuel",
parentId: 8843316769405,
surname: "Ludington",
work: "Neuroradiographer",
birthDate: "1864-11-13T01:39:02.494Z",
},
{
id: 6910084039563,
name: "Layla",
parentId: 95132218987,
surname: "Clark",
work: "CT Radiographer",
birthDate: "1868-09-02T07:10:46.829Z",
},
{
id: 2583927007530,
name: "Darby",
surname: "Yeakel",
parentId: 1323435182904,
work: "Data designer",
birthDate: "1853-05-12T16:14:07.161Z",
},
{
id: 7448530790803,
name: "Winnifred",
surname: "Rykener",
parentId: 9181202451521,
work: "Unemployed",
birthDate: "1852-03-20T06:48:07.162Z",
},
{
id: 4842667119782,
name: "Lacey",
parentId: 7400518637533,
surname: "Humpherys",
work: "Unemployed",
birthDate: "1875-03-04T17:56:15.949Z",
},
{
id: 3926090190617,
name: "Beverley",
surname: "McHatton",
parentId: 8769830982857,
work: "Field warden",
birthDate: "1853-06-02T04:00:57.288Z",
},
{
id: 5107646185720,
name: "Cassidy",
parentId: 7276643575609,
surname: "Alderman",
work: "Retired",
birthDate: "1863-12-22T05:38:41.764Z",
},
{
id: 6617379016460,
name: "Jeannie",
surname: "Artley",
parentId: 5730519222203,
work: "Psychologist",
birthDate: "1854-11-06T11:20:41.234Z",
},
{
id: 5415904134291,
name: "Shannon",
parentId: 5513277402976,
surname: "Watrous",
work: "Artistic director",
birthDate: "1872-02-22T13:25:42.485Z",
},
{
id: 8625554360699,
name: "Margot",
surname: "Glidewell",
parentId: 5710615384493,
work: "Rhapsode",
birthDate: "1854-10-31T13:03:27.754Z",
},
{
id: 2167634099186,
name: "Annie",
parentId: 9497014533965,
surname: "Hoskins",
work: "Spotlight Operator",
birthDate: "1866-11-19T12:52:54.706Z",
},
{
id: 2969518168386,
name: "Velma",
parentId: 7209449538085,
surname: "Terry",
work: "Web developer",
birthDate: "1871-10-28T11:08:07.215Z",
},
{
id: 9376340620106,
name: "Cheryl",
parentId: 1706294044283,
surname: "Engleman",
work: "Stunt performer",
birthDate: "1877-10-11T10:56:39.191Z",
},
{
id: 4611179134961,
name: "Mayola",
surname: "Whyte",
parentId: 2106785096820,
work: "Painters",
birthDate: "1854-12-18T21:34:21.340Z",
},
{
id: 3330893717600,
name: "Imogen",
surname: "Brinkley",
parentId: 4855829489899,
work: "Nephrology Physician Assistant",
birthDate: "1853-02-02T11:36:44.244Z",
},
{
id: 2322780495725,
name: "Gillian",
parentId: 8313555968957,
surname: "Allcock",
work: "Rhapsode",
birthDate: "1871-08-02T14:41:44.723Z",
},
{
id: 9096773496327,
name: "Crossley",
surname: "Oatway",
parentId: 4983996020215,
work: "Flagman",
birthDate: "1854-09-04T11:14:16.967Z",
},
{
id: 7912768369102,
name: "Jack",
surname: "Cortright",
parentId: 1954893391139,
work: "Racing driver",
birthDate: "1852-08-02T03:15:25.294Z",
},
{
id: 4936706920188,
name: "Marilyn",
parentId: 1805461552938,
surname: "Bradford",
work: "Park ranger",
birthDate: "1868-03-15T10:44:36.661Z",
},
{
id: 4507151706136,
name: "Malcolm",
surname: "Fish",
parentId: 10892055752,
work: "Otorhinolaryngology Physician assistant",
birthDate: "1852-09-21T09:57:38.875Z",
},
{
id: 1002471008138,
name: "Sapphire",
surname: "Plumb",
parentId: 7581067886471,
work: "Physical Therapist",
birthDate: "1853-10-01T07:37:10.197Z",
},
{
id: 935177490762,
name: "Cracroft",
parentId: 4710524419131,
surname: "Dick",
work: "Emergency medical technician",
birthDate: "1874-06-28T07:03:04.205Z",
},
{
id: 3214614421553,
name: "Sparrow",
surname: "Ashbridge",
parentId: 957660638347,
work: "Deputy Station Master",
birthDate: "1852-04-10T04:05:16.806Z",
},
{
id: 7486640594543,
name: "Krista",
surname: "Pancake",
parentId: 7378652821449,
work: "Plumber",
birthDate: "1854-08-23T21:50:03.378Z",
},
{
id: 4208092032684,
name: "Jolie",
surname: "Williamson",
parentId: 542240779474,
work: "Stage crew",
birthDate: "1853-10-29T00:38:32.373Z",
},
{
id: 265753551687,
name: "Holbrook",
parentId: 4936706920188,
surname: "Bradford",
work: "Hack writer",
birthDate: "1884-05-07T02:08:54.196Z",
},
{
id: 8299001953232,
name: "Robert",
parentId: 3173442171339,
surname: "Harvie",
work: "Ticketing agent",
birthDate: "1873-06-05T02:00:54.119Z",
},
{
id: 4569395503128,
name: "Heather",
surname: "Exton",
parentId: 9258857749445,
work: "Millwright",
birthDate: "1854-07-30T13:43:50.734Z",
},
{
id: 3805806644419,
name: "Avis",
surname: "Waddington",
parentId: 7607565215691,
work: "Soloist",
birthDate: "1853-05-16T10:13:10.174Z",
},
{
id: 215914970248,
name: "Lanna",
parentId: 3926090190617,
surname: "McHatton",
work: "Poet",
birthDate: "1871-10-23T19:08:06.184Z",
},
{
id: 405274833240,
name: "Demetria",
parentId: 4670743239553,
surname: "Walle",
work: "Signalman",
birthDate: "1874-07-17T12:36:59.100Z",
},
{
id: 2703780723460,
name: "Grayson",
parentId: 7914083691988,
surname: "Wind",
work: "Bylaw enforcement officer",
birthDate: "1866-08-17T16:34:36.854Z",
},
{
id: 2649091306594,
name: "Kathryn",
parentId: 8313555968957,
surname: "Allcock",
work: "Station",
birthDate: "1874-09-20T04:49:58.207Z",
},
],
},
],
};
ExcelTable.generateExcel(data).then((res) => {
var url = window.URL.createObjectURL(res);
window.location.assign(url);
return res;
});
🆕 fetch Option ⬆️
mr-Excel uses fetch (if the images option is used). If it is used with Node lower than 18.0.0, you may encounter problems. To fix this problem, you can add the fetch option. Here’s an example of how the function should look like:
import fetch from "cross-fetch";
export async function callApi(url) {
return await fetch(url).then((res) => {
return res.arrayBuffer();
});
}
const data = {
fetch: callApi,
...
sheet: [
{
images: [
{
url: "https://mohammadrezaeicode.github.io/mr-excel-page/img/ezgif.com-gif-maker.gif",
from: "H1",
type: "one",
},
],
...
},
],
};Header Option ⬆️
We offer specific header options for Excel headers. The header is a mandatory component, so the withoutHeader option cannot be used to omit it. The headerHeight option is employed to determine the height of the header row. Additionally, we provide the headerStyleKey property, which specifies the most commonly used style for each cell (its value corresponds to the style ID; detailed functionality is explained in the Styles section).
Each header cell is endowed with properties beyond the label and text. The size property defines the width of the column, while the formula property applies a formula to all rows (excluding the header) within the column, ultimately affecting the final cell in that column.
const color = { c1: "08D9D6", c2: "252A34", c3: "FF2E63", c4: "EAEAEA" };
const data = {
creator: "mr",
styles: {
headerStyle: {
backgroundColor: color.c2,
fontFamily: "Times New Roman",
color: color.c4,
size: 20,
},
formulaStyle: {
backgroundColor: color.c1,
fontFamily: "Times New Roman",
color: color.c3,
size: 15,
},
},
sheet: [
{
headerStyleKey: "headerStyle",
headerHeight: 30,
headers: [
{
label: "id",
text: "ID",
size: 20,
formula: { type: "COUNT", styleId: "formulaStyle" },
},
{ label: "name", text: "Name", size: 20 },
{ label: "surname", text: "Surname", size: 20 },
{ label: "parentId", text: "Parent Id", size: 20 },
{ label: "work", text: "Work", size: 20 },
{ label: "birthDate", text: "Birth Date", size: 30 },
],
data: [
{
id: 7209449538085,
name: "Tabitha",
surname: "Terry",
parentId: 6998520522169,
work: "Computer repair technician",
birthDate: "1853-04-10T01:23:16.181Z",
},
{
id: 4132538644996,
name: "Grace",
surname: "MacTavish",
parentId: 6840142476821,
work: "Retired",
birthDate: "1854-04-03T08:51:19.825Z",
},
{
id: 778493423064,
name: "Bailey",
surname: "Byram",
parentId: 7137102781494,
work: "Occupational Therapist- Neonatal/ Pediatric",
birthDate: "1852-08-13T18:07:57.408Z",
},
{
id: 510141747289,
name: "Sherman",
surname: "Joseph",
parentId: 602149579197,
work: "work from home",
birthDate: "1854-12-12T05:48:31.806Z",
},
{
id: 5513277402976,
name: "Ryder",
surname: "Watrous",
parentId: 7435302183884,
work: "Welder",
birthDate: "1854-08-18T04:11:04.736Z",
},
{
id: 1032906540606,
name: "Phoenix",
surname: "Netter",
parentId: 3204642808212,
work: "Unemployed",
birthDate: "1854-07-19T07:53:58.843Z",
},
{
id: 343574032284,
name: "Tonya",
surname: "Carpenter",
parentId: 3709985684199,
work: "Pulmonologist",
birthDate: "1852-04-20T12:44:08.362Z",
},
{
id: 9497014533965,
name: "Coral",
surname: "Hoskins",
parentId: 3497153671269,
work: "Unemployed",
birthDate: "1854-12-01T22:08:59.891Z",
},
{
id: 4998374693826,
name: "Billie",
surname: "Guthrie",
parentId: 1555796128163,
work: "Skomorokh",
birthDate: "1853-11-10T14:06:54.037Z",
},
{
id: 95132218987,
name: "Gertrude",
surname: "Clark",
parentId: 2324519652998,
work: "Unemployed",
birthDate: "1852-12-22T20:12:13.237Z",
},
],
},
{
withoutHeader: true,
headerStyleKey: "headerStyle",
headers: [
{
label: "id",
text: "ID",
size: 70,
formula: { type: "COUNT", styleId: "formulaStyle" },
},
{ label: "name", text: "Name", size: 12 },
{ label: "surname", text: "Surname", size: 70 },
{ label: "parentId", text: "Parent Id", size: 100 },
{ label: "work", text: "Work", size: 100 },
{ label: "birthDate", text: "Birth Date", size: 100 },
],
data: [
{
id: 7209449538085,
name: "Tabitha",
surname: "Terry",
parentId: 6998520522169,
work: "Computer repair technician",
birthDate: "1853-04-10T01:23:16.181Z",
},
{
id: 4132538644996,
name: "Grace",
surname: "MacTavish",
parentId: 6840142476821,
work: "Retired",
birthDate: "1854-04-03T08:51:19.825Z",
},
{
id: 778493423064,
name: "Bailey",
surname: "Byram",
parentId: 7137102781494,
work: "Occupational Therapist- Neonatal/ Pediatric",
birthDate: "1852-08-13T18:07:57.408Z",
},
{
id: 510141747289,
name: "Sherman",
surname: "Joseph",
parentId: 602149579197,
work: "work from home",
birthDate: "1854-12-12T05:48:31.806Z",
},
{
id: 5513277402976,
name: "Ryder",
surname: "Watrous",
parentId: 7435302183884,
work: "Welder",
birthDate: "1854-08-18T04:11:04.736Z",
},
{
id: 1032906540606,
name: "Phoenix",
surname: "Netter",
parentId: 3204642808212,
work: "Unemployed",
birthDate: "1854-07-19T07:53:58.843Z",
},
{
id: 343574032284,
name: "Tonya",
surname: "Carpenter",
parentId: 3709985684199,
work: "Pulmonologist",
birthDate: "1852-04-20T12:44:08.362Z",
},
{
id: 9497014533965,
name: "Coral",
surname: "Hoskins",
parentId: 3497153671269,
work: "Unemployed",
birthDate: "1854-12-01T22:08:59.891Z",
},
{
id: 4998374693826,
name: "Billie",
surname: "Guthrie",
parentId: 1555796128163,
work: "Skomorokh",
birthDate: "1853-11-10T14:06:54.037Z",
},
{
id: 95132218987,
name: "Gertrude",
surname: "Clark",
parentId: 2324519652998,
work: "Unemployed",
birthDate: "1852-12-22T20:12:13.237Z",
},
],
},
],
};
ExcelTable.generateExcel(data);
Formula Option ⬆️
We provide two distinct methods for defining formulas: customization and column type. In the customization approach, if you employ a cell containing data that is used within the formula, the formula will display an instance of the formula. When using the customization type, it's important to specify the formula type, which can be any of the following: AVERAGE, SUM, COUNT, MAX, or MIN.
const colorPalette = {
c1: "2B2E4A",
c2: "E84545",
c3: "903749",
c4: "53354A",
};
const data = {
creator: "mr",
styles: {
headerStyle: {
backgroundColor: "2B2E4A",
fontFamily: "Times New Roman",
color: "E84545",
},
customFormulaStyle: {
backgroundColor: "E84545",
fontFamily: "Times New Roman",
color: "2B2E4A",
size: 15,
border: {
full: {
color: "53354A",
style: "dashDot",
},
},
},
formulaStyle: {
backgroundColor: "2B2E4A",
fontFamily: "Times New Roman",
color: "E84545",
size: 15,
border: {
full: {
color: "903749",
style: "medium",
},
},
},
},
sheet: [
{
formula: {
A8: {
type: "SUM",
start: "B2",
end: "D3",
styleId: "customFormulaStyle",
},
B8: {
type: "AVERAGE",
start: "A2",
end: "F6",
styleId: "customFormulaStyle",
},
C8: {
type: "SUM",
start: "A2",
end: "F6",
styleId: "customFormulaStyle",
},
D8: {
type: "MAX",
start: "A2",
end: "F6",
styleId: "customFormulaStyle",
},
E8: {
type: "MIN",
start: "A2",
end: "F6",
styleId: "customFormulaStyle",
},
F8: {
type: "COUNT",
start: "A2",
end: "F6",
styleId: "customFormulaStyle",
},
},
headerStyleKey: "headerStyle",
headers: [
{
label: "Date",
text: "Date",
formula: {
styleId: "formulaStyle",
type: "COUNT",
},
},
{
label: "Column 1",
text: "Column 1",
formula: {
styleId: "formulaStyle",
type: "AVERAGE",
},
},
{
label: "Column 2",
text: "Column 2",
formula: {
styleId: "formulaStyle",
type: "SUM",
},
},
{
label: "Column 3",
text: "Column 3",
formula: {
styleId: "formulaStyle",
type: "MAX",
},
},
{
label: "Column 4",
text: "Column 4",
formula: {
styleId: "formulaStyle",
type: "MIN",
},
},
{
label: "Column 5",
text: "Column 5",
formula: {
styleId: "formulaStyle",
type: "COUNT",
},
},
],
data: [
{
Date: "2023-08-01",
"Column 1": 5,
"Column 2": 10,
"Column 3": 15,
"Column 4": 20,
"Column 5": 25,
},
{
Date: "2023-08-02",
"Column 1": 7,
"Column 2": 14,
"Column 3": 21,
"Column 4": 28,
"Column 5": 35,
},
{
Date: "2023-08-03",
"Column 1": 3,
"Column 2": 6,
"Column 3": 9,
"Column 4": 12,
"Column 5": 15,
},
{
Date: "2023-08-04",
"Column 1": 12,
"Column 2": 24,
"Column 3": 36,
"Column 4": 48,
"Column 5": 60,
},
{
Date: "2023-08-05",
"Column 1": 8,
"Column 2": 16,
"Column 3": 24,
"Column 4": 32,
"Column 5": 40,
},
],
},
],
};
ExcelTable.generateExcel(data);
Time, Math, Custom Formula & etc ⬆️
We offer new formulas for mathematics, time, and more. Additionally, you can create complex formulas using the Custom Formula feature. One notable aspect of the Custom Formula feature is the ability to generate an array result formula. To achieve this, you must specify a range cell where the results are to be inserted and define the formula in the formula property. Moreover, you can use this feature to define a single result formula.
const colorPalette = {
c1: "2B2E4A",
c2: "E84545",
c3: "903749",
c4: "53354A",
};
const t = { c1: "2C3639", c2: "3F4E4F", c3: "A27B5C", c4: "DCD7C9" },
n = { backgroundColor: t.c2, fontFamily: "Times New Roman", color: t.c4 },
a = { backgroundColor: t.c4, fontFamily: "Times New Roman", color: t.c2 };
const data = {
creator: "mr",
styles: {
headerStyle: {
backgroundColor: "2B2E4A",
fontFamily: "Times New Roman",
color: "E84545",
},
Date: { ...n, format: "short_date" },
customFormulaStyle: {
backgroundColor: "E84545",
fontFamily: "Times New Roman",
color: "2B2E4A",
size: 15,
border: {
full: {
color: "53354A",
style: "dashDot",
},
},
},
formulaStyle: {
backgroundColor: "2B2E4A",
fontFamily: "Times New Roman",
color: "E84545",
size: 15,
border: {
full: {
color: "903749",
style: "medium",
},
},
},
},
sheet: [
{
formula: {
J7: {
formula: 'REPLACE(D3,1,1,"replced")',
styleId: "customFormulaStyle",
},
H8: {
formula: 'CONCATENATE(D2, " ", D5)',
styleId: "customFormulaStyle",
},
"J2:J6": {
formula: "YEAR(NOW()-A2:A6)",
referenceCells: "J2:J6",
},
"K2:K6": {
formula: "LOWER(D2:D6)",
},
I2: {
formula: "COUNT(A1:B8)",
styleId: "customFormulaStyle",
},
H6: {
noArgType: "NOW_HOUR",
styleId: "customFormulaStyle",
},
H5: {
noArgType: "NOW",
styleId: "customFormulaStyle",
},
H4: {
type: "TRIM",
referenceCell: "D3",
styleId: "customFormulaStyle",
},
H7: {
type: "SUMIF",
referenceCell: "B1:B5",
value: '">=5"',
styleId: "customFormulaStyle",
},
H3: {
type: "COUNTIF",
referenceCell: "B1:B5",
value: '">=5"',
styleId: "customFormulaStyle",
},
H2: {
type: "ABS",
referenceCell: "B5",
styleId: "customFormulaStyle",
},
I1: {
type: "PROPER",
referenceCell: "D1",
styleId: "customFormulaStyle",
},
G11: {
type: "UPPER",
referenceCell: "D1",
styleId: "customFormulaStyle",
},
G10: {
type: "TAN",
referenceCel