17136790327

Committed 21 Aug 2025 07:19PM UTC coverage: 77.635% (+1.7%) from 75.941%

Build # 17136790327

Build Type

push

github

Committed by

web-flow

Commit Message

Merge pull request #806 from CBIIT/feat/Submission-Request-Export

Submission Request Excel Import & Export CRDCDH-3033, CRDCDH-3045, CRDCDH-3063

Run Details

4854 of 5337 branches covered (90.95%)

Branch coverage included in aggregate %.

3214 of 3490 new or added lines in 33 files covered. (92.09%)

7 existing lines in 3 files now uncovered.

29088 of 38383 relevant lines covered (75.78%)

175.29 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

92.03

/src/classes/Excel/SectionBase.ts

import type ExcelJS from "exceljs";
import { CellValue } from "exceljs";

import { Logger } from "@/utils";

import { ErrorCatalog } from "./ErrorCatalog";

/**
 * Represents the context for a section in the Excel worksheet.
 */
export type SectionCtxBase = {
  /**
   * The Excel workbook.
   */
  workbook: ExcelJS.Workbook;
  /**
   * The utility functions for the section.
   */
  u: {
    /**
     * Set the header for the worksheet.
     *
     * @param ws The worksheet to modify.
     * @param color The color to use for the header.
     * @returns void
     */
    header: (ws: ExcelJS.Worksheet, color?: string) => void;
  };
};

/**
 * Represents the definition of a column in the section.
 */
export type ColumnDef<K extends string> = Omit<Partial<ExcelJS.Column>, "key"> & {
  key: K;
  /**
   * An optional annotation for the column header.
   */
  annotation?: string;
};

/**
 * Represents the character limits for each column in the section.
 */
export type CharacterLimitsMap<K extends string> = Readonly<Partial<{ [key in K]: number }>>;

/**
 * Represents the configuration for a section in the Excel worksheet.
 */
export type SectionConfig<K extends string, D> = {
  id: string;
  deps: D;
  sheetName: string;
  columns: ColumnDef<K>[];
  headerColor?: string;
  characterLimits?: CharacterLimitsMap<K>;
};

/**
 * Represents a section in the Excel worksheet.
 */
export type Section = {
  /**
   * The unique identifier for the section.
   *
   * @example "B"
   */
  id: string;
  /**
   * Serialize the section data to the worksheet.
   *
   * @param ctx The section context.
   * @returns The created worksheet.
   */
  serialize: (ctx: SectionCtxBase) => Promise<ExcelJS.Worksheet>;
};

/**
 * Represents a section in the Excel worksheet.
 */
export abstract class SectionBase<K extends string, D> implements Section {
  /**
   * The unique identifier for the section.
   * @example "B"
   */
  public readonly id: string;

  /**
   * The section dependencies.
   */
  protected readonly deps: D;

  /**
   * The name of the worksheet.
   * @example "Program and Study"
   */
  private readonly _sheetName: string;

  /**
   * The columns in the section.
   */
  private readonly _columns: ColumnDef<K>[];

  /**
   * The color of the header row.
   * @example "#D9EAD3"
   */
  private readonly _headerColor: string;

  /**
   * The character limits for each column in the section.
   */
  protected readonly CHARACTER_LIMITS: CharacterLimitsMap<K>;

  /**
   * Create a new section.
   *
   * @param cfg The section configuration.
   */
  constructor(cfg: SectionConfig<K, D>) {
    this.id = cfg.id;
    this.deps = cfg.deps;
    this._sheetName = cfg.sheetName;
    this._columns = cfg.columns;
    this._headerColor = cfg.headerColor || "#D9EAD3";
    this.CHARACTER_LIMITS = Object.freeze({ ...(cfg.characterLimits ?? {}) });
  }

  /**
   * Serialize the section data to the worksheet.
   *
   * @param ctx The section context.
   * @returns The created worksheet.
   */
  public async serialize(ctx: SectionCtxBase): Promise<ExcelJS.Worksheet> {
    const ws = this.create(ctx);
    this.write(ctx, ws);
    this.applyValidation(ctx, ws);
    this.annotate(
      ws,
      this._columns
        .filter((c) => c.annotation)
        .reduce(
          (acc, col) => {
            acc[col.key] = col.annotation;
            return acc;
          },
          {} as Partial<Record<K, string>>
        )
    );

    return ws;
  }

  /**
   * Create worksheet, add columns, and style header.
   *
   * @param ctx The section context.
   * @returns The created worksheet.
   */
  protected create(ctx: SectionCtxBase): ExcelJS.Worksheet {
    const existing = ctx.workbook.worksheets.find((ws) => ws.name === this._sheetName);
    if (existing) {
      ctx.workbook.removeWorksheet(existing.id);
    }

    const ws = ctx.workbook.addWorksheet(this._sheetName);
    ws.columns = this._columns;

    const color = (this._headerColor || "#D9EAD3").replace(/^#/, "").toUpperCase();
    ctx.u.header(ws, color);

    ws.views = [{ state: "frozen", ySplit: 1 }];

    return ws;
  }

  /**
   * Write data to the worksheet.
   *
   * @param ctx The section context.
   * @param ws The worksheet.
   * @returns The created row.
   */
  protected abstract write(ctx: SectionCtxBase, ws: ExcelJS.Worksheet): ExcelJS.Row[];

  /**
   * Attach data validation to the data in the worksheet.
   *
   * @param ctx The section context.
   * @param ws The worksheet.
   * @param row The row to validate.
   */
  protected abstract applyValidation(
    ctx: SectionCtxBase,
    ws: ExcelJS.Worksheet
  ): void | Promise<void>;

  /**
   * Write annotations on the specified cells in the worksheet.
   *
   * @param ws The worksheet to annotate
   * @param annotations A map of Cell Key -> Note
   * @param rowNum The row to annotate. Default is the header row.
   */
  protected annotate(
    ws: ExcelJS.Worksheet,
    annotations: Partial<Record<K, string>>,
    rowNum = 1
  ): void {
    const row = ws.getRow(rowNum);
    this._columns.forEach((col, index) => {
      if (col.key in annotations) {
        const cell = row.getCell(index + 1);
        cell.note = {
          texts: [{ text: annotations[col.key] || "" }],
          protection: { locked: "True", lockText: "True" },
        };
      }
    });
  }

  /**
   * Get the cells in row 2 of the worksheet.
   *
   * @note Follows the order of the defined columns.
   * @param ws The worksheet.
   * @param row The row number to get cells from.
   * @returns The cells in the specified row.
   */
  protected getRowCells(ws: ExcelJS.Worksheet, row = 2) {
    const r = ws.getRow(row);
    const cells = this._columns.map((col) => r.getCell(col.key)) || [];

    return cells;
  }

  /**
   * Set the values for a specific row in the worksheet.
   *
   * @param ws The worksheet.
   * @param rowIndex The index of the row to update.
   * @param values The values to set.
   */
  protected setRowValues(
    ws: ExcelJS.Worksheet,
    rowIndex: number,
    values: Partial<Record<K, CellValue>>
  ) {
    const row = ws.getRow(rowIndex);
    this._columns.forEach((col, index) => {
      if (col.key in values) {
        row.getCell(index + 1).value = values[col.key] || null;
      }
    });
  }

  /**
   * Apply data validation to a specific column in the worksheet.
   *
   * @param ws The worksheet.
   * @param key The column key.
   * @param callback The callback to apply to each cell.
   * @param options The options for the cell range.
   */
  protected forEachCellInColumn(
    ws: ExcelJS.Worksheet,
    key: K,
    callback: (cell: ExcelJS.Cell, rowNumber: number) => void,
    options?: { startRow?: number; endRow?: number }
  ): void {
    const keyIsFound = this._columns?.find((c) => c.key === key);
    if (!keyIsFound) {
      Logger.error(`SectionBase.tsx: Column with key "${key}" not found.`);
      return;
    }

    const { startRow = 2, endRow = 100 } = options ?? {};
    const column = ws.getColumn(key as string);

    Array.from({ length: endRow - startRow + 1 }, (_, i) => i + startRow).forEach((rowNum) => {
      const row = ws.getRow(rowNum);
      const cell = row.getCell(column.number);
      callback(cell, rowNum);
    });
  }

  /**
   * Applies a standard character limit validation to the specified cell.
   *
   * @param cell The cell to apply the ruleset to
   * @param limit The character limit to apply
   */
  // eslint-disable-next-line class-methods-use-this
  protected applyTextLengthValidation(cell: ExcelJS.Cell, limit: number): void {
    cell.dataValidation = {
      type: "textLength",
      operator: "lessThan",
      showErrorMessage: true,
      error: ErrorCatalog.get("max", { max: limit }),
      allowBlank: false,
      formulae: [limit],
    };
  }
}

1	import type ExcelJS from "exceljs";
2	import { CellValue } from "exceljs";
3
4	import { Logger } from "@/utils";	1✔
5
6	import { ErrorCatalog } from "./ErrorCatalog";	1✔
7
8	/**
9	* Represents the context for a section in the Excel worksheet.
10	*/
11	export type SectionCtxBase = {
12	/**
13	* The Excel workbook.
14	*/
15	workbook: ExcelJS.Workbook;
16	/**
17	* The utility functions for the section.
18	*/
19	u: {
20	/**
21	* Set the header for the worksheet.
22	*
23	* @param ws The worksheet to modify.
24	* @param color The color to use for the header.
25	* @returns void
26	*/
27	header: (ws: ExcelJS.Worksheet, color?: string) => void;
28	};
29	};
30
31	/**
32	* Represents the definition of a column in the section.
33	*/
34	export type ColumnDef<K extends string> = Omit<Partial<ExcelJS.Column>, "key"> & {
35	key: K;
36	/**
37	* An optional annotation for the column header.
38	*/
39	annotation?: string;
40	};
41
42	/**
43	* Represents the character limits for each column in the section.
44	*/
45	export type CharacterLimitsMap<K extends string> = Readonly<Partial<{ [key in K]: number }>>;
46
47	/**
48	* Represents the configuration for a section in the Excel worksheet.
49	*/
50	export type SectionConfig<K extends string, D> = {
51	id: string;
52	deps: D;
53	sheetName: string;
54	columns: ColumnDef<K>[];
55	headerColor?: string;
56	characterLimits?: CharacterLimitsMap<K>;
57	};
58
59	/**
60	* Represents a section in the Excel worksheet.
61	*/
62	export type Section = {
63	/**
64	* The unique identifier for the section.
65	*
66	* @example "B"
67	*/
68	id: string;
69	/**
70	* Serialize the section data to the worksheet.
71	*
72	* @param ctx The section context.
73	* @returns The created worksheet.
74	*/
75	serialize: (ctx: SectionCtxBase) => Promise<ExcelJS.Worksheet>;
76	};
77
78	/**
79	* Represents a section in the Excel worksheet.
80	*/
81	export abstract class SectionBase<K extends string, D> implements Section {	1✔
82	/**
83	* The unique identifier for the section.
84	* @example "B"
85	*/
86	public readonly id: string;	68✔
87
88	/**
89	* The section dependencies.
90	*/
91	protected readonly deps: D;	68✔
92
93	/**
94	* The name of the worksheet.
95	* @example "Program and Study"
96	*/
97	private readonly _sheetName: string;	68✔
98
99	/**
100	* The columns in the section.
101	*/
102	private readonly _columns: ColumnDef<K>[];	68✔
103
104	/**
105	* The color of the header row.
106	* @example "#D9EAD3"
107	*/
108	private readonly _headerColor: string;	68✔
109
110	/**
111	* The character limits for each column in the section.
112	*/
113	protected readonly CHARACTER_LIMITS: CharacterLimitsMap<K>;	68✔
114
115	/**
116	* Create a new section.
117	*
118	* @param cfg The section configuration.
119	*/
120	constructor(cfg: SectionConfig<K, D>) {	68✔
121	this.id = cfg.id;	68✔
122	this.deps = cfg.deps;	68✔
123	this._sheetName = cfg.sheetName;	68✔
124	this._columns = cfg.columns;	68✔
125	this._headerColor = cfg.headerColor \|\| "#D9EAD3";	68!
126	this.CHARACTER_LIMITS = Object.freeze({ ...(cfg.characterLimits ?? {}) });	68✔
127	}	68✔
128
129	/**
130	* Serialize the section data to the worksheet.
131	*
132	* @param ctx The section context.
133	* @returns The created worksheet.
134	*/
135	public async serialize(ctx: SectionCtxBase): Promise<ExcelJS.Worksheet> {	68✔
136	const ws = this.create(ctx);	68✔
137	this.write(ctx, ws);	68✔
138	this.applyValidation(ctx, ws);	68✔
139	this.annotate(	68✔
140	ws,	68✔
141	this._columns	68✔
142	.filter((c) => c.annotation)	68✔
143	.reduce(	68✔
144	(acc, col) => {	68✔
145	acc[col.key] = col.annotation;	182✔
146	return acc;	182✔
147	},	182✔
148	{} as Partial<Record<K, string>>	68✔
149	)	68✔
150	);	68✔
151
152	return ws;	68✔
153	}	68✔
154
155	/**
156	* Create worksheet, add columns, and style header.
157	*
158	* @param ctx The section context.
159	* @returns The created worksheet.
160	*/
161	protected create(ctx: SectionCtxBase): ExcelJS.Worksheet {	68✔
162	const existing = ctx.workbook.worksheets.find((ws) => ws.name === this._sheetName);	68✔
163	if (existing) {	68!
NEW 164	ctx.workbook.removeWorksheet(existing.id);	×
NEW 165	}	×
166
167	const ws = ctx.workbook.addWorksheet(this._sheetName);	68✔
168	ws.columns = this._columns;	68✔
169
170	const color = (this._headerColor \|\| "#D9EAD3").replace(/^#/, "").toUpperCase();	68!
171	ctx.u.header(ws, color);	68✔
172
173	ws.views = [{ state: "frozen", ySplit: 1 }];	68✔
174
175	return ws;	68✔
176	}	68✔
177
178	/**
179	* Write data to the worksheet.
180	*
181	* @param ctx The section context.
182	* @param ws The worksheet.
183	* @returns The created row.
184	*/
185	protected abstract write(ctx: SectionCtxBase, ws: ExcelJS.Worksheet): ExcelJS.Row[];
186
187	/**
188	* Attach data validation to the data in the worksheet.
189	*
190	* @param ctx The section context.
191	* @param ws The worksheet.
192	* @param row The row to validate.
193	*/
194	protected abstract applyValidation(
195	ctx: SectionCtxBase,
196	ws: ExcelJS.Worksheet
197	): void \| Promise<void>;
198
199	/**
200	* Write annotations on the specified cells in the worksheet.
201	*
202	* @param ws The worksheet to annotate
203	* @param annotations A map of Cell Key -> Note
204	* @param rowNum The row to annotate. Default is the header row.
205	*/
206	protected annotate(	68✔
207	ws: ExcelJS.Worksheet,	68✔
208	annotations: Partial<Record<K, string>>,	68✔
209	rowNum = 1	68✔
210	): void {	68✔
211	const row = ws.getRow(rowNum);	68✔
212	this._columns.forEach((col, index) => {	68✔
213	if (col.key in annotations) {	1,239✔
214	const cell = row.getCell(index + 1);	182✔
215	cell.note = {	182✔
216	texts: [{ text: annotations[col.key] \|\| "" }],	182!
217	protection: { locked: "True", lockText: "True" },	182✔
218	};	182✔
219	}	182✔
220	});	68✔
221	}	68✔
222
223	/**
224	* Get the cells in row 2 of the worksheet.
225	*
226	* @note Follows the order of the defined columns.
227	* @param ws The worksheet.
228	* @param row The row number to get cells from.
229	* @returns The cells in the specified row.
230	*/
231	protected getRowCells(ws: ExcelJS.Worksheet, row = 2) {	68✔
232	const r = ws.getRow(row);	64✔
233	const cells = this._columns.map((col) => r.getCell(col.key)) \|\| [];	64!
234
235	return cells;	64✔
236	}	64✔
237
238	/**
239	* Set the values for a specific row in the worksheet.
240	*
241	* @param ws The worksheet.
242	* @param rowIndex The index of the row to update.
243	* @param values The values to set.
244	*/
245	protected setRowValues(	68✔
246	ws: ExcelJS.Worksheet,	170✔
247	rowIndex: number,	170✔
248	values: Partial<Record<K, CellValue>>	170✔
249	) {	170✔
250	const row = ws.getRow(rowIndex);	170✔
251	this._columns.forEach((col, index) => {	170✔
252	if (col.key in values) {	3,022✔
253	row.getCell(index + 1).value = values[col.key] \|\| null;	1,023✔
254	}	1,023✔
255	});	170✔
256	}	170✔
257
258	/**
259	* Apply data validation to a specific column in the worksheet.
260	*
261	* @param ws The worksheet.
262	* @param key The column key.
263	* @param callback The callback to apply to each cell.
264	* @param options The options for the cell range.
265	*/
266	protected forEachCellInColumn(	68✔
267	ws: ExcelJS.Worksheet,	410✔
268	key: K,	410✔
269	callback: (cell: ExcelJS.Cell, rowNumber: number) => void,	410✔
270	options?: { startRow?: number; endRow?: number }	410✔
271	): void {	410✔
272	const keyIsFound = this._columns?.find((c) => c.key === key);	410✔
273	if (!keyIsFound) {	410!
NEW 274	Logger.error(`SectionBase.tsx: Column with key "${key}" not found.`);	×
NEW 275	return;	×
NEW 276	}	×
277
278	const { startRow = 2, endRow = 100 } = options ?? {};	410✔
279	const column = ws.getColumn(key as string);	410✔
280
281	Array.from({ length: endRow - startRow + 1 }, (_, i) => i + startRow).forEach((rowNum) => {	410✔
282	const row = ws.getRow(rowNum);	40,590✔
283	const cell = row.getCell(column.number);	40,590✔
284	callback(cell, rowNum);	40,590✔
285	});	410✔
286	}	410✔
287
288	/**
289	* Applies a standard character limit validation to the specified cell.
290	*
291	* @param cell The cell to apply the ruleset to
292	* @param limit The character limit to apply
293	*/
294	// eslint-disable-next-line class-methods-use-this
295	protected applyTextLengthValidation(cell: ExcelJS.Cell, limit: number): void {	68✔
296	cell.dataValidation = {	7,527✔
297	type: "textLength",	7,527✔
298	operator: "lessThan",	7,527✔
299	showErrorMessage: true,	7,527✔
300	error: ErrorCatalog.get("max", { max: limit }),	7,527✔
301	allowBlank: false,	7,527✔
302	formulae: [limit],	7,527✔
303	};	7,527✔
304	}	7,527✔
305	}	68✔

CBIIT / crdc-datahub-ui / 17136790327

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous