A simple Excel export utility using ExcelJS
npm install exceljs-wrpper-to-excel-exportInstallation
Install the package via npm:
``bash
npm install exceljs-wrpper-to-excel-export
`
`
Usage
Here’s an example of how to use the wrapper in your project:
Steps
1. Import the Package
import { excelJsWrapperToExcelExport } from "exceljs-wrpper-to-excel-export";
2. Define Configuration
3. Call The wrapper function excelJsWrapperToExcelExport with file name and config parameters
`
excelJsWrapperToExcelExport("Example.xlsx", sheetConfigs);
`
Basic Configuration
The wrapper function excelJsWrapperToExcelExport accepts two main
parameters:
1. fileName,
1. sheetConfigs
Here’s a breakdown of the configuration for each sheet.
Example Configuration:
`
interface ColumnConfig {
header: string;
headerStyle?: ExcelJS.Style;
key: string;
type?: string; //like 'date','string' default is 'string'
width?: number;
style?: ExcelJS.Style;
formula?: string;
sum?: boolean;
sumColumnCellStyle?: ExcelJS.Style;
dateFormat?: string; // Default "mm/dd/yyyy"
}
`
`
Config {
columns: ColumnConfig[];
autoAdjustColumnsWidth?: boolean;
maxColumnWidth?: number;
groupBy?: string;
groupHeaderRow?: boolean;
groupTotalLable?: string; // Default value is "Total"
groupTotalLableCell?: string; // Default cell is "A"
groupSumRowStyle?: ExcelJS.Style;
}
`
`
interface Data {
[key: string]: any;
}
`
`
interface SheetConfig {
name: string; // sheet name
config: Config; // sheet config
data: Data[]; // data array
extraHeaders?: ExtraHeader[];
}
`
Extra Headers
Extra headers are rows above the column headers, allowing for merged cells and custom styles.
`
interface ExtraHeader {
values: string[];
mergeAcross: number[];
style: ExcelJS.Style;
}
`
Extra Headers Example:
`
extraHeaders: [
{
values: ["Dashboard"],
mergeAcross: [24],
style: {
font: { bold: true, size: 16 },
alignment: { horizontal: "center" },
},
},
{
values: [
"Contract Information",
"Financial Information",
"Schedule Information"
],
mergeAcross: [5, 4, 5],
style: {
font: { bold: true, size: 12 },
alignment: { horizontal: "center" },
},
},
],
`
This will merge the specified columns to accommodate the header text across multiple columns.
Grouping and Summing
To group data by a column and calculate sums:
`
groupBy?: "groupColumn",
groupHeaderRow?: true,
groupTotalLable?: 'Total',
groupTotalLableCell?: 'A',
groupSumRowStyle?: ExcelJS.Style;
`
This configuration will automatically group data and generate a sum row for columns where sum: true is set. This configuration applied at column level.
Applying Formulas
You can define formulas in the ColumnConfig:
`
formula: '{column}{row} * 2'
`
The placeholders {column} and {row} are automatically replaced by the actual cell references.
Date Formats
Excel accepts various date format strings, including:
"mm/dd/yyyy": U.S. date format.
"dd/mm/yyyy": European date format.
"yyyy-mm-dd": ISO format.
"mmmm dd, yyyy": Full month name (e.g., "September 12, 2024").
"d-mmm-yy": Short month name with year (e.g., "12-Sep-24").
"hh:mm:ss": Time format.
"dd/mm/yyyy hh:mm:ss": Date and time combined.
Full Example
Here’s an example usage of the wrapper:
``
const sheetConfigs = [
{
name: "ExampleSheet",
config: {
columns: [
{ key: "id", header: "ID", width: 10 },
{ key: "name", header: "Name", width: 20, style: { font: { bold: true } } },
{ key: "date", header: "Date", type: "date", dateFormat: "dd/mm/yyyy" },
],
groupBy: "category",
groupHeaderRow: true,
autoAdjustColumnsWidth: true,
},
data: [
{ id: 1, name: "John", date: "/Date(1635791400000)/", category: "A" },
{ id: 2, name: "Jane", date: "10/12/2024", category: "B" },
],
extraHeaders: [
{
values: ["Extra Header 1", "Extra Header 2"],
mergeAcross: [2, 1],
style: {
font: { bold: true, size: 14 },
alignment: { horizontal: "center" },
},
},
],
},
];
excelJsWrapperToExcelExport("Example.xlsx", sheetConfigs);
Error Handling
The wrapper logs errors to the console and alerts users when something goes wrong.
Ensure you have appropriate data validation to avoid issues like invalid configurations or missing columns.
Feel free to add any additional features or suggestions in the documentation as required.