import { CellIsRule, DEFAULT_LOCALE, IconSetRule } from "../../src"; import { ICON_SETS } from "../../src/components/icons/icons"; import { lettersToNumber } from "../../src/helpers/coordinates"; import { formatValue } from "../../src/helpers/format/format"; import { buildSheetLink, markdownLink } from "../../src/helpers/misc"; import { DEFAULT_TABLE_CONFIG } from "../../src/helpers/table_presets"; import { toZone } from "../../src/helpers/zones"; import { BarChartDefinition } from "../../src/types/chart/bar_chart"; import { ComboChartDefinition } from "../../src/types/chart/combo_chart"; import { LineChartDefinition } from "../../src/types/chart/line_chart"; import { PieChartDefinition } from "../../src/types/chart/pie_chart"; import { ScatterChartDefinition } from "../../src/types/chart/scatter_chart"; import { Image } from "../../src/types/image"; import { SheetData, WorkbookData } from "../../src/types/workbook_data"; import { XLSXImportData, XLSXSharedFormula, XLSXStyle, XLSXWorksheet } from "../../src/types/xlsx"; import { applyLumModOff, hexaToInt } from "../../src/xlsx/conversion/color_conversion"; import { BORDER_STYLE_CONVERSION_MAP, CF_OPERATOR_TYPE_CONVERSION_MAP, CF_THRESHOLD_CONVERSION_MAP, CF_TYPE_CONVERSION_MAP, H_ALIGNMENT_CONVERSION_MAP, ICON_SET_CONVERSION_MAP, V_ALIGNMENT_CONVERSION_MAP, } from "../../src/xlsx/conversion/conversion_maps"; import { isCompositeRange } from "../../src/xlsx/conversion/figure_conversion"; import { convertXlsxFormat } from "../../src/xlsx/conversion/format_conversion"; import { adaptFormula } from "../../src/xlsx/conversion/formula_conversion"; import { convertSheets } from "../../src/xlsx/conversion/sheet_conversion"; import { getRelativePath } from "../../src/xlsx/helpers/misc"; import { XLSXImportWarningManager } from "../../src/xlsx/helpers/xlsx_parser_error_manager"; import { XlsxReader } from "../../src/xlsx/xlsx_reader"; import { EXCEL_TEST_FILES_PATH, getTextXlsxFiles } from "../__xlsx__/read_demo_xlsx"; import { toChartDataSource } from "../test_helpers/chart_helpers"; import { deepFreeze } from "../test_helpers/helpers"; import { getCFBeginningAt, getColPosition, getDataValidationBeginningAt, getRowPosition, getWorkbookCell, getWorkbookCellBorder, getWorkbookCellFormat, getWorkbookCellStyle, getWorkbookSheet, standardizeColor, } from "../test_helpers/xlsx"; describe("Import xlsx data", () => { let convertedData: WorkbookData; beforeAll(async () => { const demo_xlsx = await getTextXlsxFiles(); const reader = new XlsxReader(demo_xlsx); convertedData = deepFreeze(reader.convertXlsx()); }); test("Can import cell content", () => { const testSheet = getWorkbookSheet("jestSheet", convertedData)!; expect(getWorkbookCell(0, 0, testSheet)).toEqual("string"); }); test("Can import formula", () => { const testSheet = getWorkbookSheet("jestSheet", convertedData)!; expect(getWorkbookCell(0, 1, testSheet)).toEqual("=SUM(A1)"); }); test("Can import array formula correctly and spill ranges values skipped", () => { const sheet = getWorkbookSheet("jestSheet", convertedData)!; expect(getWorkbookCell(0, 29, sheet)).toEqual("=RANDARRAY(2,2)"); expect(getWorkbookCell(0, 30, sheet)).toEqual(undefined); expect(getWorkbookCell(1, 29, sheet)).toEqual(undefined); expect(getWorkbookCell(1, 30, sheet)).toEqual(undefined); }); test("Can import merge", () => { const testSheet = getWorkbookSheet("jestSheet", convertedData)!; expect(testSheet.merges).toEqual(["D1:E2"]); }); test("Can import hyperlinks", () => { const testSheet = getWorkbookSheet("jestSheet", convertedData)!; expect(testSheet.cells["D3"]).toEqual(markdownLink("hyperlink", "https://www.odoo.com/")); expect(testSheet.cells["F3"]).toEqual(markdownLink("sheetLink", buildSheetLink("jestSheet"))); }); test("Can import row size", () => { const testSheet = getWorkbookSheet("jestSheet", convertedData)!; expect(testSheet.rows[4].size).toEqual(100); }); test("Can import col size", () => { const testSheet = getWorkbookSheet("jestSheet", convertedData)!; // Columns size in excel are dumb. // In the Excel UI it says "size 13.57, 100 px", then it saves size = 14.28 in the xml... // And if I dare to open and save the xlsx with Excel in another language the size changes... // I'll just test if the size is approximately right expect(testSheet.cols[lettersToNumber("F")].size).toBeBetween(80, 120); }); test("Can import hidden rows", () => { const testSheet = getWorkbookSheet("jestSheet", convertedData)!; expect(testSheet.rows[3].isHidden).toBeTruthy(); }); test("Can import hidden cols", () => { const testSheet = getWorkbookSheet("jestSheet", convertedData)!; expect(testSheet.cols[lettersToNumber("C")].isHidden).toBeTruthy(); }); test("Can import external reference", () => { // External references are cells that have references to another xlsx file const testSheet = getWorkbookSheet("jestSheet", convertedData)!; expect(testSheet.cells["H2"]).toEqual('="referenced string"'); }); test.each([ ["darkGray", "A2"], ["mediumGray", "A3"], ["lightGray", "A4"], ["gray0625", "A5"], ["darkHorizontal", "A6"], ["darkVertical", "A7"], ["darkDown", "A8"], ["darkUp", "A9"], ["darkGrid", "A10"], ["darkTrellis", "A11"], ["lightHorizontal", "A12"], ["lightVertical", "A13"], ["lightDown", "A14"], ["lightUp", "A15"], ["lightGrid", "A16"], ["lightTrellis", "A17"], ])("Can import fills", (fillType, cellXc) => { const testSheet = getWorkbookSheet("jestStyles", convertedData)!; const cellStyle = getWorkbookCellStyle(testSheet.styles[cellXc], convertedData); expect(standardizeColor(cellStyle!.fillColor!)).toEqual("#FFC000FF"); }); test.each([ ["thin", "C2"], ["hair", "C4"], ["dotted", "C6"], ["dashDotDot", "C8"], ["dashDot", "C10"], ["dashed", "C12"], ["mediumDashDotDot", "C14"], ["slantDashDot", "C16"], ["mediumDashDot", "C18"], ["mediumDashed", "C20"], ["medium", "C22"], ["thick", "C24"], ["double", "C26"], ["thick #ff0000", "C28"], ])("Can import borders", (borderType, cellXc) => { const testSheet = getWorkbookSheet("jestStyles", convertedData)!; const cellBorders = getWorkbookCellBorder(testSheet.borders[cellXc], convertedData)!; const cellContentSplit = borderType.split(" "); const expectedBorderStyle = BORDER_STYLE_CONVERSION_MAP[cellContentSplit[0]]; const expectedBorderColor = cellContentSplit.length === 2 ? standardizeColor(cellContentSplit[1]) : "#000000FF"; for (const side of ["top", "bottom", "left", "right"]) { expect(cellBorders[side].style).toEqual(expectedBorderStyle); expect(standardizeColor(cellBorders[side].color)).toEqual(expectedBorderColor); } }); test.each([ ["general", "F2"], ["left", "F3"], ["center", "F4"], ["right", "F5"], ["fill", "F6"], ["justify", "F7"], ["centerContinuous", "F8"], ["distributed", "F9"], ])("Can import Horizontal Alignment %s", (alignType, cellXc) => { const testSheet = getWorkbookSheet("jestStyles", convertedData)!; const cellStyle = getWorkbookCellStyle(testSheet.styles[cellXc], convertedData); expect(cellStyle?.align).toEqual(H_ALIGNMENT_CONVERSION_MAP[alignType]); }); test.each([ [undefined, "F12"], ["top", "F13"], ["center", "F14"], ["justify", "F15"], ["distributed", "F16"], ])("Can import Vertical Alignment %s", (alignType, cellXc) => { const testSheet = getWorkbookSheet("jestStyles", convertedData)!; const cellStyle = getWorkbookCellStyle(testSheet.styles[cellXc], convertedData); expect(cellStyle?.verticalAlign).toEqual( alignType === undefined ? undefined : V_ALIGNMENT_CONVERSION_MAP[alignType] ); }); test.each([ ["overflow", "F19"], ["wrap", "F20"], ])("Can import wrapping mode %s", (wrapType, cellXc) => { const testSheet = getWorkbookSheet("jestStyles", convertedData)!; const cellStyle = getWorkbookCellStyle(testSheet.styles[cellXc], convertedData); // "overflow" is the default wrapping style, but depending on the Excel version used to create the xlsx, // it can be explicitly set or not, so we consider both cases as correct expect(cellStyle?.wrapping ?? "overflow").toEqual(wrapType); }); test.each([ ["0.00", "M2"], ["0.00%", "M3"], ["m/d/yyyy", "M4"], ['#,##0.00\\ "€"', "M5"], ["[$$]#,##0.000", "M6"], ["[$₪]\\ #,##0", "M7"], ['#,##0" EUR €"', "M8"], ['"€"#,##0.00\\ "€"', "M9"], ])("Can import format %s", (format, cellXc) => { const testSheet = getWorkbookSheet("jestStyles", convertedData)!; const cellFormat = getWorkbookCellFormat(testSheet.formats[cellXc], convertedData); const expectedFormat: string | undefined = format; expect(cellFormat).toEqual(expectedFormat); }); test.each([ ["Red", "H2"], ["Italic", "H3"], ["Bold", "H4"], ["Striked", "H5"], ["Underlined", "H6"], ["size12", "H7"], ["size16", "H8"], ])("Can import font styles", (style, cellXc) => { const testSheet = getWorkbookSheet("jestStyles", convertedData)!; const cellStyle = getWorkbookCellStyle(testSheet.styles[cellXc], convertedData); switch (style) { case "Red": expect(standardizeColor(cellStyle!.textColor!)).toEqual("#FF0000FF"); break; case "Italic": expect(cellStyle!.italic).toBeTruthy(); break; case "Bold": expect(cellStyle!.bold).toBeTruthy(); break; case "Striked": expect(cellStyle!.strikethrough).toBeTruthy(); break; case "Underlined": expect(cellStyle!.underline).toBeTruthy(); break; case "size12": expect(cellStyle!.fontSize).toEqual(12); break; case "size16": expect(cellStyle!.fontSize).toEqual(16); break; } }); test.each([ ["aboveAverage", "B2"], ["beginsWith", "B3"], ["containsBlanks", "B4"], ["containsErrors", "B5"], ["containsText", "B6"], ["duplicateValues", "B7"], ["endsWith", "B8"], ["notContainsBlanks", "B9"], ["notContainsErrors", "B10"], ["notContainsText", "B11"], ["timePeriod", "B12"], ["top10", "B13"], ["uniqueValues", "B14"], ["expression", "B15"], ["cellIs equal", "B16"], ["cellIs notEqual", "B17"], ["cellIs greaterThan", "B18"], ["cellIs greaterThanOrEqual", "B19"], ["cellIs lessThan", "B20"], ["cellIs lessThanOrEqual", "B21"], ["cellIs between", "B22"], ["cellIs notBetween", "B23"], ])("Can import conditional format %s", (cfDescription, cfStartRange) => { const testSheet = getWorkbookSheet("jestCfs", convertedData)!; const split = cfDescription.split(" "); const cfType = split[0]; const cellIsOperator = split[1]; const cf = getCFBeginningAt(cfStartRange, testSheet)!; let operator = ""; const values: string[] = []; switch (cfType) { case "containsErrors": case "notContainsErrors": case "timePeriod": case "aboveAverage": case "dataBar": // Unsupported CF types expect(cf).toBeUndefined(); return; case "containsText": case "notContainsText": case "beginsWith": case "endsWith": operator = CF_TYPE_CONVERSION_MAP[cfType]!; values.push("rule"); break; case "expression": operator = CF_TYPE_CONVERSION_MAP[cfType]!; values.push("=ODD(B15)"); break; case "containsBlanks": case "notContainsBlanks": case "uniqueValues": case "duplicateValues": operator = CF_TYPE_CONVERSION_MAP[cfType]!; break; case "cellIs": operator = CF_OPERATOR_TYPE_CONVERSION_MAP[cellIsOperator]; values.push("2"); if (["between", "notBetween"].includes(cellIsOperator)) { values.push("4"); } break; case "top10": operator = CF_TYPE_CONVERSION_MAP[cfType]!; values.push("50"); expect(cf.rule["isPercent"]).toBe(true); expect(cf.rule["isBottom"]).toBe(true); break; } expect(cf.rule.type).toEqual("CellIsRule"); expect((cf.rule as CellIsRule).operator).toEqual(operator); expect((cf.rule as CellIsRule).values).toEqual(values); }); test("Can import CF with formulas", () => { const testSheet = getWorkbookSheet("jestCfs", convertedData)!; const cf = getCFBeginningAt("B29", testSheet)!; expect(cf.rule.type).toEqual("CellIsRule"); expect((cf.rule as CellIsRule).operator).toEqual("isBetween"); expect((cf.rule as CellIsRule).values).toEqual(["=$B$23", "=2+2"]); }); test.each([ ["A2"], // number ["F2"], // time ["G2"], // textLength ])("Cannot import unsupported data validation rule types %s", (cellXc) => { const testSheet = getWorkbookSheet("jestDataValidations", convertedData)!; const dvRule = getDataValidationBeginningAt(cellXc, testSheet); // Unsupported data validation rule expect(dvRule).toBeUndefined(); }); test("Can import decimal data validation rule %s", () => { const testSheet = getWorkbookSheet("jestDataValidations", convertedData)!; const dvRule = getDataValidationBeginningAt("B2", testSheet); expect(dvRule).toMatchObject({ criterion: { type: "isBetween", values: ["1", "100"], }, ranges: ["B2:B6"], isBlocking: true, }); }); test("Can import list data validation rule %s", () => { const testSheet = getWorkbookSheet("jestDataValidations", convertedData)!; const rangeDVRule = getDataValidationBeginningAt("C2", testSheet); const valuesDVRule = getDataValidationBeginningAt("D2", testSheet); expect(rangeDVRule).toMatchObject({ criterion: { type: "isValueInRange", values: ["$C$2:$C$6"], }, ranges: ["C2:C6"], isBlocking: false, }); expect(valuesDVRule).toMatchObject({ criterion: { type: "isValueInList", values: ["option1", "option2", "option3"], }, ranges: ["D2:D6"], isBlocking: false, }); }); test("Can import date data validation rule %s", () => { const testSheet = getWorkbookSheet("jestDataValidations", convertedData)!; const notEqualDVRule = getDataValidationBeginningAt("E2", testSheet); const dateIsAfterDVRule = getDataValidationBeginningAt("H2", testSheet); // Unsupported 'notEqual' for date data validation expect(notEqualDVRule).toBeUndefined(); expect(dateIsAfterDVRule).toMatchObject({ criterion: { type: "dateIsAfter", dateValue: "exactDate", values: ["12/1/2023"], }, ranges: ["H2:H6"], isBlocking: false, }); }); test("Can import custom data validation rule %s", () => { const testSheet = getWorkbookSheet("jestDataValidations", convertedData)!; const dvRule = getDataValidationBeginningAt("I2", testSheet); expect(dvRule).toMatchObject({ criterion: { type: "customFormula", values: ["=ISNUMBER(I2)"], }, ranges: ["I2:I6"], isBlocking: false, }); }); test("Can import a data validation rule with a formula", () => { const testSheet = getWorkbookSheet("jestDataValidations", convertedData)!; const dvRule = getDataValidationBeginningAt("J2", testSheet); expect(dvRule).toMatchObject({ criterion: { type: "isBetween", values: ["=$A$2", "=2+10"], }, ranges: ["J2:J6"], }); }); test.each([ ["2 colors max", "H2"], ["3 colors max", "H3"], ["2 colors num", "H4"], ["2 colors percent", "H5"], ["2 colors percentile", "H6"], ["2 colors formula", "H7"], ])("Can import Color Scales", (colorScaleDescr, cfStartRange) => { const testSheet = getWorkbookSheet("jestCfs", convertedData)!; const cf = getCFBeginningAt(cfStartRange, testSheet)!; const split = colorScaleDescr.split(" "); const numberOfThresholds = Number(split[0]); const thresholdType = split[2]; let values: (string | undefined)[] = []; switch (thresholdType) { case "max": values = [undefined, undefined]; break; case "num": values = ["1", "5"]; break; case "percent": case "percentile": values = ["10", "90"]; break; case "formula": values = ["$J$6", "$H$6"]; break; } const minThreshold = { color: hexaToInt("#ed7d31"), type: CF_THRESHOLD_CONVERSION_MAP[thresholdType], value: values[0], }; const maxThreshold = { color: hexaToInt("#ffc000"), type: CF_THRESHOLD_CONVERSION_MAP[thresholdType], value: values[1], }; const middleThreshold = numberOfThresholds === 2 ? undefined : { color: hexaToInt("#f69e19"), type: "percentile", value: "50", }; expect(cf.rule).toMatchObject({ type: "ColorScaleRule", minimum: minThreshold, maximum: maxThreshold, midpoint: middleThreshold, }); }); test("Can import Data Bars", () => { const testSheet = getWorkbookSheet("jestCfs", convertedData)!; const cf = getCFBeginningAt("B25", testSheet)!; expect(cf.rule).toMatchObject({ type: "DataBarRule", color: hexaToInt("#63C384"), }); }); test("Can import CF with custom formula", () => { const testSheet = getWorkbookSheet("jestCfs", convertedData)!; const cf = getCFBeginningAt("B15", testSheet)!; expect(cf.rule).toMatchObject({ type: "CellIsRule", operator: "customFormula", values: ["=ODD(B15)"], }); }); test.each([ ["3Arrows percent", "H11"], ["3ArrowsGray num", "H12"], ["3Flags percentile", "H13"], ["3TrafficLights1 formula", "H14"], ["3TrafficLights2", "H15"], ["3Signs", "H16"], ["3Symbols", "H17"], ["3Symbols2", "H18"], ["3Stars", "H19"], ["3Triangles", "H20"], ["4Arrows", "H21"], ["4ArrowsGray", "H22"], ["4RedToBlack", "H23"], ["4Rating", "H24"], ["4TrafficLights", "H25"], ["5Arrows", "H26"], ["5ArrowsGray", "H27"], ["5Rating", "H28"], ["5Quarters", "H29"], ["5Boxes", "H30"], ])("Can import icon sets", (iconSetDescr, cfStartRange) => { const testSheet = getWorkbookSheet("jestCfs", convertedData)!; const split = iconSetDescr.split(" "); const iconSetName = split[0]; const thresholdType = split.length > 1 ? split[1] : "percent"; const iconSet = { upper: ICON_SETS[ICON_SET_CONVERSION_MAP[iconSetName]]["good"], middle: ICON_SETS[ICON_SET_CONVERSION_MAP[iconSetName]]["neutral"], lower: ICON_SETS[ICON_SET_CONVERSION_MAP[iconSetName]]["bad"], }; const cf = getCFBeginningAt(cfStartRange, testSheet)!; expect(cf.rule).toMatchObject({ type: "IconSetRule", icons: iconSet, }); expect((cf.rule as IconSetRule).lowerInflectionPoint.type).toEqual( CF_THRESHOLD_CONVERSION_MAP[thresholdType] ); expect((cf.rule as IconSetRule).upperInflectionPoint.type).toEqual( CF_THRESHOLD_CONVERSION_MAP[thresholdType] ); }); test.each([ ["NoIcons", "H31"], ["Reverse", "H32"], ["ShowOnlyIcon", "H33"], ["GreaterNotEqual", "H34"], ["MixIcons", "H35"], ["2Icons", "H36"], ["1Icon", "H37"], ])("special icon sets '%s'", (iconSetDescr, cfStartRange) => { const testSheet = getWorkbookSheet("jestCfs", convertedData)!; const cf = getCFBeginningAt(cfStartRange, testSheet)!; switch (iconSetDescr) { case "Reverse": expect(cf.rule).toMatchObject({ icons: { upper: ICON_SETS.arrows.bad, middle: ICON_SETS.arrows.neutral, lower: ICON_SETS.arrows.good, }, }); break; case "MixIcons": expect(cf.rule).toMatchObject({ icons: { upper: ICON_SETS.dots.good, middle: ICON_SETS.dots.neutral, lower: ICON_SETS.arrows.bad, }, }); break; case "GreaterNotEqual": expect(cf.rule).toMatchObject({ upperInflectionPoint: { operator: "gt" }, lowerInflectionPoint: { operator: "gt" }, }); break; case "NoIcons": case "2Icons": case "1Icons": // Empty icons should have been replaced by dots icons expect(cf.rule).toMatchObject({ icons: { upper: ICON_SETS.dots.good, middle: ICON_SETS.dots.neutral, lower: ICON_SETS.dots.bad, }, }); break; case "ShowOnlyIcon": // ShowOnlyIcon boolean is ignored and it give a standard CF rule expect(cf.rule).toMatchObject({ icons: { upper: ICON_SETS.arrows.good, middle: ICON_SETS.arrows.neutral, lower: ICON_SETS.arrows.bad, }, }); break; } }); test("tables are imported", () => { const sheet = getWorkbookSheet("jestTable", convertedData)!; expect(sheet.tables).toHaveLength(10); }); test("rows filtered by a table filter are hidden", () => { const sheet = getWorkbookSheet("jestTable", convertedData)!; expect(sheet.cells["C31"]).toEqual("Hidden"); expect(sheet.rows[30].isHidden).toBeTruthy(); }); describe("table styles", () => { /** Test tables for styles are 2x2 tables located at the right of the cell describing them */ let tableTestSheet: SheetData; beforeAll(() => { tableTestSheet = getWorkbookSheet("jestTable", convertedData)!; }); test("Can import basic table style", () => { const table = tableTestSheet.tables.find((table) => table.range === "C8:D9")!; expect(table?.config).toMatchObject({ numberOfHeaders: 0, totalRow: false, bandedRows: false, bandedColumns: false, firstColumn: false, lastColumn: false, hasFilters: false, styleId: "TableStyleLight8", }); }); test("Can import table style id", () => { const table = tableTestSheet.tables.find((table) => table.range === "C3:J6"); expect(table?.config).toMatchObject({ styleId: "TableStyleLight10" }); }); test("Can import table with headers", () => { const table = tableTestSheet.tables.find((table) => table.range === "C11:D12"); expect(table?.config).toMatchObject({ numberOfHeaders: 1 }); }); test("Can import table with first column style", () => { const table = tableTestSheet.tables.find((table) => table.range === "C14:D15"); expect(table?.config).toMatchObject({ firstColumn: true }); }); test("Can highlight last table column", () => { const table = tableTestSheet.tables.find((table) => table.range === "C17:D18"); expect(table?.config).toMatchObject({ lastColumn: true }); }); test("Can import table with banded rows", () => { const table = tableTestSheet.tables.find((table) => table.range === "C20:D21"); expect(table?.config).toMatchObject({ bandedRows: true }); }); test("Can import table with banded columns", () => { const table = tableTestSheet.tables.find((table) => table.range === "C23:D24"); expect(table?.config).toMatchObject({ bandedColumns: true }); }); test("Can import table with total rows", () => { const table = tableTestSheet.tables.find((table) => table.range === "C26:D28"); expect(table?.config).toMatchObject({ totalRow: true }); }); test("Can import table with filters", () => { const table = tableTestSheet.tables.find((table) => table.range === "C30:D32"); expect(table?.config).toMatchObject({ hasFilters: true }); }); test("Table with custom style will be converted to default table style", () => { const table = tableTestSheet.tables.find((table) => table.range === "C34:D35"); expect(table?.config).toMatchObject({ styleId: DEFAULT_TABLE_CONFIG.styleId }); }); }); test("Can convert table formula inside a table", () => { // Test table coordinates are in A1 const testSheet = getWorkbookSheet("jestTable", convertedData)!; // Formula =[@Rank]+[@Age] => transformed to Col E + Col D expect(testSheet.cells["F4"]).toEqual("=E4+D4"); expect(testSheet.cells["F5"]).toEqual("=E5+D5"); // Formula =Sum([Rank]) => transformed to Sum(Col E) expect(testSheet.cells["G4"]).toEqual("=SUM(E4:E5)"); expect(testSheet.cells["G5"]).toEqual("=SUM(E4:E5)"); // Formula =Sum(TableName[[#All];[Rank]]) => transformed to Sum(Col E) (including totals & headers) expect(testSheet.cells["H4"]).toEqual("=SUM(E3:E6)"); expect(testSheet.cells["H5"]).toEqual("=SUM(E3:E6)"); // Formula =TableName[[#Total];[Rank]] => transformed to bottom of Col E expect(testSheet.cells["I4"]).toEqual("=E6"); expect(testSheet.cells["I5"]).toEqual("=E6"); // Formula =TableName[[#Headers];[Rank]] => transformed to header of Col E expect(testSheet.cells["J4"]).toEqual("=E3"); expect(testSheet.cells["J5"]).toEqual("=E3"); }); test("Can convert table formula outside of a table", () => { const testSheet = getWorkbookSheet("jestTable", convertedData)!; // Formula = SUM(Table3[#All]) => transformed to SUM of whole table expect(testSheet.cells["F9"]).toEqual("=SUM(C3:J6)"); // Formula =SUM(Table2[Col2]) => Table2 is on another sheet (jestMiscTest) expect(testSheet.cells["G9"]).toEqual("=SUM(jestMiscTest!B8:B12)"); // Formula =SUM(Table3[[#Totals],[Age]:[Rank]]) => Total row, from column Age to Rank expect(testSheet.cells["H9"]).toEqual("=SUM(D6:E6)"); // Formula =SUM(Table3[[Age]:[Rank]]) => All row data, from column Age to Rank expect(testSheet.cells["I9"]).toEqual("=SUM(D4:E5)"); // Formula =SUM(Table3[[#Data];[Rank];[#Totals]]) => Data & Total rows, column Rank expect(testSheet.cells["J9"]).toEqual("=SUM(E4:E6)"); }); // We just import pivots as a Table test("can import pivots", () => { const testSheet = getWorkbookSheet("jestPivot", convertedData)!; const table = testSheet.tables[0]; expect(table.range).toEqual("C3:L21"); expect(table.config).toMatchObject({ numberOfHeaders: 2, totalRow: true, firstColumn: true, lastColumn: true, bandedRows: false, bandedColumns: false, hasFilters: false, }); }); test.each([ ["line chart", "C5:G18"], ["bar chart", "H5:L18"], ["scatter chart", "M5:Q18"], ["pie chart", "C38:L56"], ["combo chart", "H58:L71"], ["doughnut chart", "C19:L37"], ["radar chart", "C58:G71"], ])("Can import figures ", (chartTitle, figureZone) => { const testSheet = getWorkbookSheet("jestCharts", convertedData)!; // Cells in the 1st column of the sheet contains jsons with expected figure data const figZone = toZone(figureZone); const figure = testSheet.figures.find((figure) => figure.data.title.text === chartTitle)!; // Don't test exact positions, because excel does some esoteric magic for units and sizes (+our conversion is wonky, hello hardcoded DPI) // We'll only test that the figure corners are located in the correct cells expect(figure).toMatchObject({ col: figZone.left, row: figZone.top, }); expect(figure.width).toBeBetween( getColPosition(figZone.right, testSheet) - getColPosition(figZone.left, testSheet), getColPosition(figZone.right + 1, testSheet) - getColPosition(figZone.left, testSheet) ); expect(figure.height).toBeBetween( getRowPosition(figZone.bottom, testSheet) - getRowPosition(figZone.top, testSheet), getRowPosition(figZone.bottom + 1, testSheet) - getRowPosition(figZone.top, testSheet) ); expect(figure.tag).toEqual("chart"); }); test("Can import charts with or without showValues", () => { const testSheet = getWorkbookSheet("jestCharts", convertedData)!; const figureLine = testSheet.figures.find((figure) => figure.data.type === "line"); const figuresBar = testSheet.figures.filter((figure) => figure.data.type === "bar"); expect(figureLine?.data.showValues).toBe(false); expect(figuresBar[0].data.showValues).toBe(true); expect(figuresBar[1].data.showValues).toBe(true); }); test.each([ [ "line", toChartDataSource({ dataSets: [ { dataRange: "Sheet1!B26:B35", backgroundColor: "#7030A0" }, { dataRange: "Sheet1!C26:C35", backgroundColor: "#C65911" }, ], dataSetsHaveTitle: true, }), ], [ "bar", toChartDataSource({ dataSets: [ { dataRange: "Sheet1!B27:B35", backgroundColor: "#7030A0" }, { dataRange: "Sheet1!C27:C35", backgroundColor: "#C65911" }, ], dataSetsHaveTitle: false, }), ], ])("Can import charts %s with dataset colors", (chartType, dataSource) => { const testSheet = getWorkbookSheet("jestCharts", convertedData)!; const figure = testSheet.figures.find((figure) => figure.data.type === chartType); const chartData = figure!.data as LineChartDefinition | BarChartDefinition; expect(chartData).toMatchObject(dataSource); }); test.each([ [ "line chart", [{ trend: { type: "polynomial", order: 2, display: true, color: "#4472C4" } }, {}], ], ["bar chart", [{ trend: { type: "exponential", display: true, color: "#ED7D31" } }, {}]], ["scatter chart", [{ trend: { type: "logarithmic", display: true, color: "#A5A5A5" } }]], [ "scatter chart with textual labels", [{ trend: { type: "polynomial", order: 1, display: true, color: "#FFC000" } }], ], [ "combo chart", [ { trend: { type: "trailingMovingAverage", window: 3, display: true, color: "#70AD47" } }, {}, ], ], ])("Can import %s charts with dataset trendlines", (chartTitle, chartDatasets) => { const testSheet = getWorkbookSheet("jestCharts", convertedData)!; const figure = testSheet.figures.find((figure) => figure.data.title.text === chartTitle); const chartData = figure!.data as LineChartDefinition | BarChartDefinition; expect(Object.values(chartData.dataSetStyles)).toMatchObject(chartDatasets); }); test.each([ [ "bar chart", "bar", "#fff", [ { dataRange: "Sheet1!B27:B35", backgroundColor: "#7030A0" }, { dataRange: "Sheet1!C27:C35", backgroundColor: "#C65911" }, ], ], [ "combo chart", "combo", "#fff", [ { dataRange: "Sheet1!B27:B35", backgroundColor: "#1F77B4", type: "bar" as const }, { dataRange: "Sheet1!C27:C35", backgroundColor: "#FF7F0E", type: "line" as const }, ], ], [ "radar chart", "radar", "#fff", [ { dataRange: "Sheet1!B27:B35", backgroundColor: "#1F77B4" }, { dataRange: "Sheet1!C27:C35", backgroundColor: "#FF7F0E" }, ], ], ])( "Can import charts %s without dataset titles", (chartTitle, chartType, chartColor, chartDatasets) => { const testSheet = getWorkbookSheet("jestCharts", convertedData)!; const figure = testSheet.figures.find((figure) => figure.data.title.text === chartTitle)!; const chartData = figure.data as BarChartDefinition | ComboChartDefinition; expect(chartData.title.text).toEqual(chartTitle); expect(chartData.type).toEqual(chartType); expect(standardizeColor(chartData.background!)).toEqual(standardizeColor(chartColor)); expect(chartData).toMatchObject( toChartDataSource({ dataSets: chartDatasets, labelRange: "Sheet1!A27:A35", dataSetsHaveTitle: false, }) ); } ); test("Can import horizontal bar charts", () => { const testSheet = getWorkbookSheet("jestCharts", convertedData)!; const figure = testSheet.figures.find( (figure) => figure.data.title.text === "horizontal bar chart" )!; const chartData = figure.data as BarChartDefinition; expect(chartData.title.text).toEqual("horizontal bar chart"); expect(chartData.type).toEqual("bar"); expect(chartData.horizontal).toEqual(true); }); test("Can import doughnut charts", () => { const testSheet = getWorkbookSheet("jestCharts", convertedData)!; const figure = testSheet.figures.find( (figure) => figure.data.title.text === "doughnut chart with hole" )!; const chartData = figure.data as PieChartDefinition; expect(chartData.title.text).toEqual("doughnut chart with hole"); expect(chartData.type).toEqual("pie"); expect(chartData.isDoughnut).toEqual(true); }); test.each([ ["line chart", "top"], ["bar chart", "right"], ["scatter chart", "bottom"], ["scatter chart with textual labels", "left"], ["combo chart", "none"], ])("Can import %s charts with correct legend position", (chartTitle, chartLegendPosition) => { const testSheet = getWorkbookSheet("jestCharts", convertedData)!; const figure = testSheet.figures.find((figure) => figure.data.title.text === chartTitle)!; const chartData = figure.data as BarChartDefinition | ComboChartDefinition; expect(chartData.title.text).toEqual(chartTitle); expect(chartData.legendPosition).toEqual(chartLegendPosition); }); test.each([ [ "line chart", "line", "#CECECE", [ { dataRange: "Sheet1!B26:B35", backgroundColor: "#7030A0" }, { dataRange: "Sheet1!C26:C35", backgroundColor: "#C65911" }, ], ], ["pie chart", "pie", "#fff", [{ dataRange: "Sheet1!B26:B35", backgroundColor: "#FF0000" }]], [ "doughnut chart", "pie", "#fff", [ { dataRange: "Sheet1!B26:B35", backgroundColor: "#FF0000", dataSetId: "1" }, { dataRange: "Sheet1!C26:C35", backgroundColor: "#FF0000", dataSetId: "0" }, ], ], ])( "Can import charts %s with dataset titles", (chartTitle, chartType, chartColor, chartDatasets) => { const testSheet = getWorkbookSheet("jestCharts", convertedData)!; const figure = testSheet.figures.find((figure) => figure.data.title.text === chartTitle)!; const chartData = figure.data as LineChartDefinition | PieChartDefinition; expect(chartData.title.text).toEqual(chartTitle); expect(chartData.type).toEqual(chartType); expect(standardizeColor(chartData.background!)).toEqual(standardizeColor(chartColor)); expect(chartData).toMatchObject( toChartDataSource({ dataSets: chartDatasets, labelRange: "Sheet1!A26:A35", dataSetsHaveTitle: true, }) ); } ); test.each(["pie", "doughnut"])("Can import %s chart custom slice colors", (chartType) => { const testSheet = getWorkbookSheet("jestCharts", convertedData)!; const figure = testSheet.figures.find( (figure) => figure.data.title.text === `${chartType} chart` )!; const chartData = figure.data as PieChartDefinition; expect(standardizeColor(chartData.background!)).toEqual(standardizeColor("#fff")); expect(chartData.slicesColors?.[0]).toBe("#FF0000"); expect(chartData.slicesColors?.[1]).toBe("#FFC000"); }); test("Can import scatter plot", () => { const testSheet = getWorkbookSheet("jestCharts", convertedData)!; const figure = testSheet.figures.find((figure) => figure.data.title.text === "scatter chart")!; const chartData = figure.data as ScatterChartDefinition; expect(chartData.title.text).toEqual("scatter chart"); expect(chartData.type).toEqual("scatter"); expect(standardizeColor(chartData.background!)).toEqual(standardizeColor("#fff")); expect(chartData).toMatchObject( toChartDataSource({ dataSets: [{ dataRange: "Sheet1!C27:C35" }], labelRange: "Sheet1!B27:B35", dataSetsHaveTitle: false, }) ); }); test("Can import scatter plot with textual labels", () => { const testSheet = getWorkbookSheet("jestCharts", convertedData)!; const figure = testSheet.figures.find( (figure) => figure.data.title.text === "scatter chart with textual labels" )!; const chartData = figure.data as ScatterChartDefinition; expect(chartData.title.text).toEqual("scatter chart with textual labels"); expect(chartData.type).toEqual("scatter"); expect(standardizeColor(chartData.background!)).toEqual(standardizeColor("#fff")); expect(chartData).toMatchObject( toChartDataSource({ dataSets: [{ dataRange: "Sheet1!C27:C35" }], labelRange: "Sheet1!A27:A35", dataSetsHaveTitle: false, }) ); }); test("Composite ranges are dropped from chart definition", () => { const testSheet = getWorkbookSheet("jestCharts", convertedData)!; const figure = testSheet.figures.find( (figure) => figure.data.title.text === "chart with composite ranges" )!; expect(figure.data.labelRange).toEqual(undefined); }); test.each([ ["chart", "A1:F19"], ["image", "H1:K20"], ])("Can import figure %s which uses oneCellAnchor", (figureType, figureZone) => { const testSheet = getWorkbookSheet("jestOneCellAnchor", convertedData)!; const figZone = toZone(figureZone); const figure = testSheet.figures.find((figure) => figure.tag === figureType)!; expect(figure).toMatchObject({ col: figZone.left, row: figZone.top, }); expect(figure.width).toBeBetween( getColPosition(figZone.right, testSheet) - getColPosition(figZone.left, testSheet), getColPosition(figZone.right + 1, testSheet) - getColPosition(figZone.left, testSheet) ); expect(figure.height).toBeBetween( getRowPosition(figZone.bottom, testSheet) - getRowPosition(figZone.top, testSheet), getRowPosition(figZone.bottom + 1, testSheet) - getRowPosition(figZone.top, testSheet) ); }); test("Can import images", () => { const testSheet = getWorkbookSheet("jestImages", convertedData)!; const figure = testSheet.figures.find((figure) => figure.tag === "image")!; const imageData = figure.data as Image; expect(imageData.path).toEqual("relative path"); expect(figure.width).toEqual(figure.height); }); describe("Misc tests", () => { test("Newlines characters in strings are preserved", () => { const testSheet = getWorkbookSheet("jestMiscTest", convertedData)!; const textWithNewLineInXLSX = testSheet.cells["A1"]; expect(textWithNewLineInXLSX).toEqual("This text have\n a newLine"); }); test("White spaces are preserved", () => { const testSheet = getWorkbookSheet("jestMiscTest", convertedData)!; const textWithNewLineInXLSX = testSheet.cells["B1"]; expect(textWithNewLineInXLSX).toEqual(" with whitespace before"); }); test("Can hide gridLines", () => { const testSheet = getWorkbookSheet("jestMiscTest", convertedData)!; expect(testSheet.areGridLinesVisible).toEqual(false); }); test("Can import hidden sheet", () => { const testSheet = getWorkbookSheet("jestHiddenSheet", convertedData)!; expect(testSheet.isVisible).toEqual(false); }); test("Can import sheet colors", () => { expect(getWorkbookSheet("Sheet1", convertedData)!.color).toEqual("#00FF00"); expect(getWorkbookSheet("Formulas", convertedData)!.color).toEqual("#70AD47"); expect(getWorkbookSheet("jestSheet", convertedData)!.color).toEqual("#FF0000"); }); describe("Protected sheet", () => { test("can import a fully protected sheet as locked", () => { const testSheet = getWorkbookSheet("jestLocked", convertedData)!; expect(testSheet.isLocked).toEqual(true); }); test("can import a partial protected sheet as locked", () => { const testSheet = getWorkbookSheet("jestPartiallyLocked", convertedData)!; expect(testSheet.isLocked).toEqual(true); }); }); }); test("Import header groups", () => { const testSheet = getWorkbookSheet("jestSheet", convertedData)!; expect(testSheet.headerGroups?.ROW).toMatchObject([ { start: 9, end: 17, isFolded: false }, { start: 11, end: 13, isFolded: true }, ]); expect(testSheet.headerGroups?.COL).toMatchObject([ { start: 9, end: 17, isFolded: false }, { start: 11, end: 13, isFolded: true }, ]); }); test("Can import named ranges", () => { expect(convertedData.namedRanges).toEqual({ My_Named_Range: "jestMiscTest!$A$8:$B$10", Table_Col_Named_Range: "jestMiscTest!A8:A12", // Converted from table formula `Table10[Col1]` Table_Named_Range: "jestMiscTest!A8:B12", // Converted from table formula `Table10` }); }); }); test.each([ ["xl/workbook.xml", "xl/worksheets/sheet0.xml", "worksheets/sheet0.xml"], ["xl/worksheets/sheet0.xml", "xl/workbook.xml", "../workbook.xml"], ["test/path/long/file.xml", "test/my/path/file2.xml", "../../my/path/file2.xml"], ])("get relative path", async (from: string, to: string, expected: string) => { expect(getRelativePath(from, to)).toEqual(expected); }); test.each([ ["A1", "=A1", "C3", "=C3"], ["A1", "=$A1", "C3", "=$A3"], ["A1", "=A$1", "C3", "=C$1"], ["A1", "=$A$1", "C3", "=$A$1"], ["A1", "=SUM(A1:B2, 3, C2)", "C3", "=SUM(C3:D4, 3, E4)"], ["A1", "=SUM($A$1:$B$2, 3, $C$2)", "C3", "=SUM($A$1:$B$2, 3, $C$2)"], ])("adapt formula", async (from: string, formula: string, target: string, expected: string) => { const sf = { refCellXc: from, formula: formula } as XLSXSharedFormula; expect(adaptFormula(target, sf)).toEqual(expected); }); test.each([ ["0.00", "0.00", "0.00"], ["0.00;0.00%", "0.00;0.00%", "0.00"], ["0.000%", "0.000%", "0.000%"], ["#,##0.00", "#,##0.00", "0.00"], ["m/d/yyyy", "m/d/yyyy", "12/30/1899"], ["M/D/YYYY", "m/d/yyyy", "12/30/1899"], ["mmmmm/dddd/yy", "mmm/dddd/yy", "Dec/Saturday/99"], ["mmmm-ddd-yy", "mmmm-ddd-yy", "December-Sat-99"], ["mmm dd yy", "mmm dd yy", "Dec 30 99"], ["h AM/PM", "hh a", "12 AM"], ["HHHH:MM a/m", "hh:mm a", "12:00 AM"], ["m/d/yyyy\\ hh:mm:ss", "m/d/yyyy\\ hh:mm:ss", "12/30/1899 00:00:00"], ["hh:mm:ss a", "hh:mm:ss a", "12:00:00 AM"], ['#,##0.00 "€"', '#,##0.00 "€"', "0.00 €"], ["[$$-409]#,##0.000", "[$$]#,##0.000", "$0.000"], ["[$-409]0.00", "0.00", "0.00"], ["[$MM/DD/YYYY]0", "[$MM/DD/YYYY]0", "MM/DD/YYYY0"], ["#,##0.00[$MM/DD/YYYY]", "#,##0.00[$MM/DD/YYYY]", "0.00MM/DD/YYYY"], ["[$₪-40D] #,##0.00", "[$₪] #,##0.00", "₪ 0.00"], ['"€"#,##0.00 "€"', '"€"#,##0.00 "€"', "€0.00 €"], ["@", "@", "0"], ])("convert format %s", async (excelFormat, convertedFormat, expectedValue) => { expect( convertXlsxFormat(80, [{ id: 80, format: excelFormat }], new XLSXImportWarningManager()) ).toEqual(convertedFormat); expect(formatValue(0, { format: convertedFormat, locale: DEFAULT_LOCALE })).toEqual( expectedValue ); }); test.each([ EXCEL_TEST_FILES_PATH.XLSM, EXCEL_TEST_FILES_PATH.XLTM, EXCEL_TEST_FILES_PATH.XLTX, EXCEL_TEST_FILES_PATH.XLAM, ])("Can import other excel file types %s", async (path: EXCEL_TEST_FILES_PATH) => { const demo_xlsx = await getTextXlsxFiles(path); const reader = new XlsxReader(demo_xlsx); const convertedData = reader.convertXlsx(); expect(convertedData.sheets[0].cells["A1"]).toEqual("Hello World !"); }); describe("Import col/row default styles", () => { const warningManager = new XLSXImportWarningManager(); // Styles at index 0 (default) and index 1 (custom: bold font with a custom numFmt). // Indices are 0-based in this array; SheetData IDs are stored as styleIndex+1. const TEST_STYLES: XLSXStyle[] = [ { fontId: 0, fillId: 0, borderId: 0, numFmtId: 0 }, // index 0: default { fontId: 1, fillId: 0, borderId: 0, numFmtId: 164 }, // index 1: custom, numFmt 164 ]; function buildImportData( sheetOverrides: Partial, styles: XLSXStyle[] = TEST_STYLES ): XLSXImportData { return { sheets: [ { sheetName: "Sheet1", isVisible: true, sheetViews: [], cols: [], rows: [], cfs: [], dataValidations: [], sharedFormulas: [], merges: [], figures: [], hyperlinks: [], tables: [], pivotTables: [], isLocked: false, ...sheetOverrides, }, ], sharedStrings: [], fonts: [], fills: [], borders: [], numFmts: [], styles, dxfs: [], chartIds: [], imageIds: [], externalBooks: [], namedRanges: [], }; } describe("Row default style", () => { test("row styleIndex is stored in defaultStyle.rowDefault, not expanded into cells", () => { // Excel row index is 1-based; row.index=3 maps to SheetData row 2 (0-based) const data = buildImportData({ rows: [{ index: 3, cells: [], styleIndex: 1 }] }); const [sheetData] = convertSheets(data, warningManager); expect(sheetData.defaultStyle?.rowDefault?.[2]).toBe(2); // styleIndex + 1 // Cells in that row must NOT receive an expanded style entry expect(sheetData.styles["A3"]).toBeUndefined(); }); test("row numFmtId is stored in defaultFormat.rowDefault, not expanded into cells", () => { const data = buildImportData({ rows: [{ index: 3, cells: [], styleIndex: 1 }] }); const [sheetData] = convertSheets(data, warningManager); expect(sheetData.defaultFormat?.rowDefault?.[2]).toBe(165); // numFmtId + 1 expect(sheetData.formats?.["A3"]).toBeUndefined(); }); test("multiple rows each get their own rowDefault entry", () => { const data = buildImportData({ rows: [ { index: 1, cells: [], styleIndex: 1 }, { index: 5, cells: [], styleIndex: 1 }, ], }); const [sheetData] = convertSheets(data, warningManager); expect(sheetData.defaultStyle?.rowDefault?.[0]).toBe(2); expect(sheetData.defaultStyle?.rowDefault?.[4]).toBe(2); expect(sheetData.defaultStyle?.rowDefault?.[1]).toBeUndefined(); }); }); describe("Col default style", () => { test("col styleIndex is stored in defaultStyle.colDefault, not expanded into cells", () => { // Excel col indices are 1-based; min=2,max=3 maps to colDefault[1] and colDefault[2] const data = buildImportData({ cols: [{ min: 2, max: 3, styleIndex: 1 }] }); const [sheetData] = convertSheets(data, warningManager); expect(sheetData.defaultStyle?.colDefault?.[1]).toBe(2); // styleIndex + 1 expect(sheetData.defaultStyle?.colDefault?.[2]).toBe(2); // Cells in those cols must NOT receive an expanded style entry expect(sheetData.styles["B1"]).toBeUndefined(); expect(sheetData.styles["C1"]).toBeUndefined(); }); test("col numFmtId is stored in defaultFormat.colDefault, not expanded into cells", () => { const data = buildImportData({ cols: [{ min: 2, max: 2, styleIndex: 1 }] }); const [sheetData] = convertSheets(data, warningManager); expect(sheetData.defaultFormat?.colDefault?.[1]).toBe(165); // numFmtId + 1 expect(sheetData.formats?.["B1"]).toBeUndefined(); }); test("col spanning the full sheet width is stored as sheetDefault", () => { // sheetDims[0] is at least EXCEL_IMPORT_DEFAULT_NUMBER_OF_COLS (30), so max=50 covers it const data = buildImportData({ cols: [{ min: 1, max: 50, styleIndex: 1 }] }); const [sheetData] = convertSheets(data, warningManager); expect(sheetData.defaultStyle?.sheetDefault).toBe(2); expect(sheetData.defaultFormat?.sheetDefault).toBe(165); // Should not also populate colDefault entries expect(sheetData.defaultStyle?.colDefault).toEqual([]); }); test("col not spanning full width does not become sheetDefault", () => { const data = buildImportData({ cols: [{ min: 1, max: 10, styleIndex: 1 }] }); const [sheetData] = convertSheets(data, warningManager); expect(sheetData.defaultStyle?.sheetDefault).toBeUndefined(); expect(sheetData.defaultStyle?.colDefault?.[0]).toBe(2); }); }); test("individual cell styleIndex is preserved even when its row has a default style", () => { const styles: XLSXStyle[] = [ { fontId: 0, fillId: 0, borderId: 0, numFmtId: 0 }, // index 0: default { fontId: 1, fillId: 0, borderId: 0, numFmtId: 0 }, // index 1: row default { fontId: 2, fillId: 0, borderId: 0, numFmtId: 0 }, // index 2: cell override ]; const data = buildImportData( { rows: [ { index: 3, styleIndex: 1, cells: [{ xc: "A3", styleIndex: 2, type: "number", value: "42" }], }, ], }, styles ); const [sheetData] = convertSheets(data, warningManager); // Row default is stored correctly expect(sheetData.defaultStyle?.rowDefault?.[2]).toBe(2); // Cell's own style override is preserved (styleIndex 2 → id 3) expect(sheetData.styles["A3"]).toBe(3); }); }); describe("applyLumModOff", () => { test("lumOff lifts black to 50% grey", () => { expect(applyLumModOff("#000000", 1, 0.5)).toBe("#808080"); }); test("lumMod darkens white to 50% grey", () => { expect(applyLumModOff("#FFFFFF", 0.5, 0)).toBe("#808080"); }); test("luminance is clamped to 100%", () => { expect(applyLumModOff("#FFFFFF", 2, 0)).toBe("#FFFFFF"); }); test("luminance is clamped to 0%", () => { expect(applyLumModOff("#000000", 0, 0)).toBe("#000000"); }); }); test.each([ ["A1", false], ["Sheet1!A1", false], ["A1:B2", false], ["A1,B2", true], ["(A1, B2)", true], ["A1 B2", true], ["(A1 B2)", true], ["'Sheet with, comma'!A1", false], ["('Sheet 1'!B11:C11,'Sheet 1'!A12:C12,'Sheet 1'!A13:C13)", true], ])("isCompositeRange helper with string %s", (a: string, expected: boolean) => { expect(isCompositeRange(a)).toBe(expected); });