downloadExcelFromSchema

This utility provides functions to export data to Excel files using a flexible schema object. The schema allows you to define sheet structure, styling, data mapping, and more.

Schema Structure

A schema is a JavaScript object with the following properties:

• sheetName: Name of the Excel sheet.
• columnWidths: Array of column widths (Excel units).
• mergeCells: Array of merged cell definitions.
• headerRows: Array of header row definitions.
• dataPropsToMap: Array of property names to map from data objects.
• data: Array of data objects (rows).
• footerRows: Array of footer row definitions.
• rowHeights: Object mapping row numbers to heights.
• cellStyles: Array of cell style definitions.
• rowStyles: Array of row/column-based style objects.
• globalCellStyle: Style object for all cells.
• logo: Logo definition.

Key Properties

Column Widths

Set column widths using an array of numbers:

columnWidths: [20, 30, 15]

Merging Cells

Define merged cells with objects:

mergeCells: [
  {
    row: 1, col: 1, rowspan: 2, colspan: 3,
    value: "Merged Title",
    font: { bold: true },
    alignment: { horizontal: "center" }
  }
]

Use 1-based indices for row and col.

Header Rows

Define header rows:

headerRows: [
  { values: ["Header1", "Header2"], height: 25, style: { font: { bold: true } } }
]

Data Mapping

Specify which properties to map from data objects:

dataPropsToMap: ["name", "age", "city"]
data: [
  { name: "Alice", age: 30, city: "London" },
  { name: "Bob", age: 25, city: "Paris" }
]

Footer Rows

Define footer rows:

footerRows: [
  { values: ["Footer1", "Footer2"], height: 20, style: { font: { bold: true } } }
]

Row Heights

Set row heights:

rowHeights: { 1: 30, 2: 25 }

Cell Styles

Apply styles to specific cells:

cellStyles: [
  {
    row: 3,
    col: 1,
    cellStyle: {
      font: { bold: true },
      fill: { type: "pattern", pattern: "solid", fgColor: { argb: "FFFF00" } }
    }
  }
]

You may also use an object mapping cell addresses (e.g., "A1") to style objects.

Row/Column Styles

Apply styles to specific cells:

rowStyles: [
  { row: 2, col: 3, style: { font: { italic: true } } }
]

Global Cell Style

Apply a style to all cells:

globalCellStyle: { font: { name: "Arial", size: 12 } }

Logo

Add a logo to the sheet:

logo: {
  url: "https://example.com/logo.png",
  position: { col: 0, row: 0 }, // Top-left corner (zero-based)
  size: { width: 100, height: 40 }
}

Best Practices

• Use 1-based indices for row and col.
• Only merge rows that exist (header, data, or footer).
• Add header rows first, then data, then footer.
• Place all styling and merging info in the schema for clarity.

Example Minimal Schema

{
  sheetName: "Employees",
  columnWidths: [20, 15, 25],
  mergeCells: [
    { row: 1, col: 1, rowspan: 1, colspan: 3, value: "Employee List", font: { bold: true, size: 16 }, alignment: { horizontal: "center" } }
  ],
  headerRows: [
    { values: ["Name", "Age", "City"], height: 25, style: { font: { bold: true } } }
  ],
  dataPropsToMap: ["name", "age", "city"],
  data: [
    { name: "Alice", age: 30, city: "London" },
    { name: "Bob", age: 25, city: "Paris" }
  ]
}

Usage in a Custom Widget

Below is a snippet showing how to use the export utility in a custom widget. You can configure the schema as shown and call downloadExcelFromSchema to export your data:

// Example usage in a custom widget (React)
const exportPDFReport = async () => {
  await downloadExcelFromSchema({
    fileName: "PDFReport.xlsx",
    schema: {
      sheetName: "PDF Register",
      columnWidths: [20, 20, 20, 20, 15, 15, 15, 15, 15],
      mergeCells: [
        { row: 1, col: 1, rowspan: 2, colspan: 2 },
        {
          row: 1,
          col: 3,
          rowspan: 2,
          colspan: 6,
          value: "PDF REGISTER",
          font: { bold: true, size: 14, name: "Arial" },
          alignment: { vertical: "middle", horizontal: "center" },
        },
        {
          row: 1,
          col: 9,
          rowspan: 2,
          colspan: 1,
          value: "Doc. No.: MKF-I/MKT/F/09",
          font: { bold: true, size: 10, name: "Arial" },
          alignment: { vertical: "top", horizontal: "left", wrapText: true },
        },
        // ...additional mergeCells for headers...
        {
          row: 3,
          col: 1,
          rowspan: 1,
          colspan: 9,
          value: `Datewise Range :${startDate.split("T")[0]} to ${endDate.split("T")[0]}`,
          font: { bold: true, name: "Arial", size: 10 },
          alignment: { horizontal: "left" },
        },
      ],
      logo: {
        url: pageContext?.settings?.logoUrl || "",
        position: { col: 0, row: 0 },
        size: { width: 100, height: 20 },
      },
      dataPropsToMap: [
        "srNo",
        "rfqDate",
        "rfqNo",
        "customerName",
        "partName",
        "partNumber",
        "poDate",
        "poNumber",
        "remark",
      ],
      data: response?.data || [],
      dataRowStyle: {
        font: { name: "Arial", size: 10 },
      },
      rowHeights: {
        1: 30,
        2: 30,
        3: 20,
        4: 30,
        5: 30,
      },
    },
  });
};

Tip:

• Adjust the schema properties as needed for your report.
• Call the export function after fetching your data.
• See above for details on each schema property.