/
pjs.exportToExcel()

pjs.exportToExcel()

Owned by Megan Bond
Mar 03, 2025
4 min read

API to export the column text, types, and data of a given database table or view name to an Excel spreadsheet.

This API uses the write-excel-file npm package. Additional information on formats, styles, and other customizations can be found in the npm: write-excel-file documentation.

Parameters

Parameter Name

Valid Values

Description

Parameter Name

Valid Values

Description

dbObject

<string>

Required.
Name of the table or view to be exported.

dbSchema

<string>

Optional.
Name of the database schema/library where the table or view should be found.
Default Value: If not specified, this will be looked up based on users pathlist or library list

dbConnection

<string>

Optional.
Name of the database connection to use, allowing the use of multiple databases.
Default Value: default
Notes: Database connections are listed in the config.js of the Profound.js instance. The default connection has the parameter default: true specified.

filePath

<string>

Optional.
Path of where to save the generated file. Paths relative to the instance folder can be used (i.e. ./exports/myTable.xlsx) except when saveToIFS is set to true.
Default Value: {instance folder}/exports/{tablename}.xlsx
Notes: The file extension .xlsx is not required as part of the file name. It will be added if missing.

saveToIFS

<boolean>

Optional.
Whether or not to save to an IBM i IFS.
Notes: When set to true, the filePath is required to be fully qualified.

select

<string>

Optional.
The Select clause of an SQL statement. A comma delimited list of column names to select from a table or view.
Default Value: *

where

<string>

Optional.
The Where clause of an SQL statement. A conditional statement used to select for specific data from a table or view.

orderBy

<string>

Optional.
The Order By clause of an SQL statement. A name of a column in on which to sort in ASC | DSC order the selected rows of a table or view.

maxRows

<number>

Optional.
The total number of rows to retrieve.

batchSize

<number>

Optional. IBM i only.
The number of rows to return in each batch of rows returned. This is used to manage performance when large amounts of data are involved.
To calculate the value for this property: {max batch size in Mb} / {max size of each row in Mb} = {max rows per batch}
Notes: It is suggested that IBM i batch sizes be limited to 64Mb.

applyColumnSchemaOverrides

<function>

Optional.
Function to modify any columns.
Parameters:
tableInfo - <object> - information and data related to the table/view
dbObject - <string> - table/view name
dbSchema - <string> - schema/library name
text - <string> - description
fields - <array> - array of field definitions
data - <array> - array of table/view data
columnSchema - <object> - export definition for a column (see npm: write-excel-file for properties)

applyFinalChanges

<function>

Optional.
Function to customize anything to be exported, including the data.
Parameters:
tableInfo - <object> - information and data related to the table/view
dbObject - <string> - table/view name
dbSchema - <string> - schema/library name
text - <string> - description
fields - <array> - array of field definitions
data - <array> - array of table/view data
schema - <object> - full export definition (see npm: write-excel-file for properties)

exportOptions

<object>

Optional.
Export options for the excel file (see npm: write-excel-file for properties).

applyColumnSchemaOverrides <Function>

Example

const dbObject = "myTable"; const dbSchema = "myDatabase"; const filePath = `./exports/${dbObject}-out`; const args = { dbObject, dbSchema, filePath, exportOptions: { fontFamily: "Arial", fontSize: 10, sheet: `Sheet ${dbObject}`, getHeaderStyle: (columnSchema) => ({ color: "#ff0000", align: columnSchema.align !== "left" ? "center" : "left", }), }, applyColumnSchemaOverrides: async (tableInfo, columnSchema) => { columnSchema.column += ` [${columnSchema.name}]`; // All Number type Columns if (columnSchema.type === Number) { columnSchema.align = "center"; } // All Date, Time, and DateTime/TimeStamp type Columns if (columnSchema.type === Date) { columnSchema.align = "center"; if (columnSchema.isDate) columnSchema.format = "dd/mm/yyyy"; if (columnSchema.isDateTime) columnSchema.format = "dd/mm/yyyy hh:mm:ss.fff"; } // Customizations based on the table/field if (columnSchema.name === "bildcogee") columnSchema.width = 10; if (columnSchema.name === "bilddarif") columnSchema.format = "mm/dd/yyyy"; // Can set columns as hidden if (columnSchema.name === "primage") { columnSchema.hidden = true; } // Can even change the value that is exported if (columnSchema.name === "prcatid") { columnSchema.column = "prcatid * 10"; columnSchema.value = (row) => { return row.prcatid * 10; }; } }, }; pjs.exportToExcel(args);

applyFinalChanges <Function>

Example

const dbObject = "myTable"; const dbSchema = "myDatabase"; const filePath = `./exports/${dbObject}-out`; const args = { dbObject, dbSchema, filePath, exportOptions: { fontFamily: "Arial", fontSize: 10, sheet: `Sheet ${dbObject}`, getHeaderStyle: (columnSchema) => ({ color: "#ff0000", align: columnSchema.align !== "left" ? "center" : "left", }), }, applyFinalChanges: async (tableInfo, exportOptions) => { // This is where you can make final changes to the tableInfo.data // This is where you can add or remove columns from the exportOptions.schema // If you add/remove/change data property names -- you must also update the schema // Move the "prcatid" column to be after prid const fromCol = exportOptions.schema.findIndex((s) => s.name === "prcatid"); const afterCol = exportOptions.schema.findIndex((s) => s.name === "prid"); if (fromCol >= 0 && afterCol >= 0) { const toMove = exportOptions.schema[fromCol]; exportOptions.schema.splice(fromCol, 1); //Remove old exportOptions.schema.splice(afterCol + 1, 0, toMove); //Add new } tableInfo.data[3].prname = "SCOT DID THIS"; // Add a excel formula column exportOptions.schema.push({ column: "Excel formula", type: "Formula", width: 20, align: "right", format: "0.00", value: (row) => { return `=${row.prprice}*${row.prqty}`; }, }); // Add a new js match column exportOptions.schema.push({ column: "Node doing math", type: Number, width: 20, format: "0.00", align: "right", value: (row) => { return row.prprice * row.prqty; }, }); }, }; pjs.exportToExcel(args);

exportOptions <Object>

Example

const dbObject = "myTable"; const dbSchema = "myDatabase"; const filePath = `./exports/${dbObject}-out`; const args = { dbObject, dbSchema, filePath, exportOptions: { fontFamily: "Arial", fontSize: 10, sheet: `Sheet ${dbObject}`, getHeaderStyle: (columnSchema) => ({ color: "#ff0000", align: columnSchema.align !== "left" ? "center" : "left", }), }, }; pjs.exportToExcel(args);

Examples

Simple Call

pjs.exportToExcel({ dbObject: "MYTABLE" });

Call with dbSchema and filePath

pjs.exportToExcel({ dbObject: "MYTABLE", dbSchema: "MYSCHEMA", filePath: "./exports/mytable.xlsx" });

Call with exportOptions

pjs.exportToExcel({ dbObject: "MYTABLE", exportOptions: { fontFamily: "Arial", fontSize: 10, getHeaderStyle: (columnSchema) => ({ fontWeight: true, color: "#ff0000" }) } });