import { ClipboardMIMEType, ClipboardPasteTarget, Command, CommandResult, DEFAULT_LOCALE, DEFAULT_LOCALES, UIPlugin, } from "../../src"; import { DEFAULT_BORDER_DESC, LINK_COLOR } from "../../src/constants"; import { getClipboardDataPositions, getOSheetClipboardIdFromHTML, parseOSClipboardContent, } from "../../src/helpers/clipboard/clipboard_helpers"; import { toCartesian } from "../../src/helpers/coordinates"; import { urlRepresentation } from "../../src/helpers/links"; import { markdownLink } from "../../src/helpers/misc"; import { toZone, zoneToXc } from "../../src/helpers/zones"; import { Model } from "../../src/model"; import { featurePluginRegistry } from "../../src/plugins/plugin_registries"; import { clipboardHandlersRegistries } from "../../src/registries/clipboardHandlersRegistries"; import { DependencyContainer } from "../../src/store_engine/dependency_container"; import { ClipboardStore, MAX_FILE_SIZE } from "../../src/stores/clipboard_store"; import { NotificationStore } from "../../src/stores/notification_store"; import { ViewportsStore } from "../../src/stores/viewports_store"; import { XMLString } from "../../src/types/xlsx"; import { parseXML, xmlEscape } from "../../src/xlsx/helpers/xml_helpers"; import { FileStore as MockFileStore } from "../__mocks__/mock_file_store"; import { MockClipboardData } from "../test_helpers/clipboard"; import { activateSheet, addCellToSelection, addColumns, addEqualCf, addRows, cleanClipBoardHighlight, copy, copyPasteAboveCells, copyPasteCellsOnLeft, createDynamicTable, createImage, createSheet, createSheetWithName, createTable, createTableWithFilter, cut, deleteCells, deleteColumns, deleteRows, deleteSheet, hideColumns, hideRows, insertCells, merge, paste, pasteFromOSClipboard, removeCF, selectCell, selectFigure, setAnchorCorner, setCellContent, setCellFormat, setFormat, setFormatting, setFormulaVisibility, setSelection, setZoneBorders, unMerge, undo, updateFilter, updateLocale, } from "../test_helpers/commands_helpers"; import { getBorder, getCell, getCellContent, getCellError, getCellRawContent, getCellText, getClipboardVisibleZones, getEvaluatedCell, getEvaluatedGrid, getStyle, } from "../test_helpers/getters_helpers"; import { addTestPlugin, createModelFromGrid, getGrid, target } from "../test_helpers/helpers"; import { addPivot } from "../test_helpers/pivot_helpers"; import { makeStore, makeStoreWithModel } from "../test_helpers/stores"; let model: Model; let store: ClipboardStore; let container: DependencyContainer; describe("clipboard", () => { test("can copy and paste a cell", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); expect(getCell(model, "B2")).toMatchObject({ content: "b2", }); copy(model, "B2"); paste(model, "D2"); expect(getCell(model, "B2")).toMatchObject({ content: "b2", }); expect(getCell(model, "D2")).toMatchObject({ content: "b2", }); expect(getClipboardVisibleZones(store).length).toBe(0); }); test("can cut and paste a cell", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); expect(getCell(model, "B2")).toMatchObject({ content: "b2", }); cut(model, "B2"); expect(getCell(model, "B2")).toMatchObject({ content: "b2", }); expect(store.isCutOperation()).toBe(true); paste(model, "D2"); expect(store.isCutOperation()).toBe(false); expect(getCell(model, "B2")).toBeUndefined(); expect(getCell(model, "D2")).toMatchObject({ content: "b2", }); expect(getClipboardVisibleZones(store).length).toBe(0); // select D3 and paste. it should do nothing paste(model, "D3"); expect(getCell(model, "D3")).toBeUndefined(); }); test("can clean the clipboard visible zones (copy)", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); copy(model, "B2"); expect(getClipboardVisibleZones(store).length).toBe(1); cleanClipBoardHighlight(model); expect(getClipboardVisibleZones(store).length).toBe(0); }); test("can clean the clipboard visible zones (cut)", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); cut(model, "B2"); expect(getClipboardVisibleZones(store).length).toBe(1); cleanClipBoardHighlight(model); expect(getClipboardVisibleZones(store).length).toBe(0); }); test("cut command will cut the selection if no target were given", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); setSelection(model, ["B2"]); cut(model); paste(model, "D2"); expect(getCellRawContent(model, "D2")).toBe("b2"); }); test("paste without copied value", () => { ({ model, store } = makeStore(ClipboardStore)); const result = store.isCommandValid({ type: "PASTE", target: target("D2") }); expect(result).toBeCancelledBecause(CommandResult.EmptyClipboard); }); test("can cut and paste a cell in different sheets", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "a1"); cut(model, "A1"); const to = model.getters.getActiveSheetId(); createSheet(model, { sheetId: "42", activate: true }); setCellContent(model, "A1", "a1Sheet2"); paste(model, "B2"); expect(getCell(model, "A1")).toMatchObject({ content: "a1Sheet2", }); expect(getCell(model, "B2")).toMatchObject({ content: "a1", }); activateSheet(model, to); expect(model.getters.getEvaluatedCells(to)).toEqual([]); expect(getClipboardVisibleZones(store).length).toBe(0); // select D3 and paste. it should do nothing paste(model, "D3"); expect(getCell(model, "D3")).toBeUndefined(); }); test("can cut and paste a zone inside the cut zone", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "a1"); setCellContent(model, "A2", "a2"); cut(model, "A1:A2"); expect(getGrid(model)).toEqual({ A1: "a1", A2: "a2" }); paste(model, "A2"); expect(getGrid(model)).toEqual({ A2: "a1", A3: "a2" }); }); test("can copy a cell with style", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); setFormatting(model, "B2", { bold: true }); expect(getCell(model, "B2")!.style).toEqual({ bold: true }); copy(model, "B2"); paste(model, "C2"); expect(getCell(model, "B2")!.style).toEqual({ bold: true }); expect(getCell(model, "C2")!.style).toEqual({ bold: true }); }); test("copying external content & paste-format on a cell will not paste content", () => { ({ model, store } = makeStore(ClipboardStore)); const clipboardData = new MockClipboardData(); clipboardData.setData(ClipboardMIMEType.PlainText, "Excalibur"); const content = clipboardData.getData(ClipboardMIMEType.PlainText); pasteFromOSClipboard(model, "C2", { text: content }); expect(getCellContent(model, "C2")).toBe(content); pasteFromOSClipboard(model, "C3", { text: content }, "onlyFormat"); expect(getCellContent(model, "C3")).toBe(""); }); test("cannot paste multiple times after cut", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); setFormatting(model, "B2", { bold: true }); cut(model, "B2"); paste(model, "C2"); expect(getCellContent(model, "C2")).toBe("b2"); expect(getCell(model, "C2")!.style).toEqual({ bold: true }); paste(model, "E5"); expect(getCell(model, "E5")).toBe(undefined); }); test("Cut clipboard should be invalidated when sheet is deleted", () => { ({ model, store } = makeStore(ClipboardStore)); const sheet1Id = model.getters.getActiveSheetId(); const sheet2Id = "sheet2"; createSheet(model, { sheetId: sheet2Id }); setCellContent(model, "A1", "Apple", sheet1Id); setFormatting(model, "A1", { bold: true }); cut(model, "A1"); activateSheet(model, sheet2Id); deleteSheet(model, sheet1Id); paste(model, "A2"); expect(getCell(model, "A2", sheet2Id)).toBe(undefined); }); test("can paste even if sheet containing copy zone has been deleted", () => { ({ model, store } = makeStore(ClipboardStore)); const sheet1Id = model.getters.getActiveSheetId(); const sheet2Id = "sheet2"; createSheet(model, { sheetId: sheet2Id }); setCellContent(model, "A1", "Apple", sheet1Id); setFormatting(model, "A1", { bold: true }); copy(model, "A1"); activateSheet(model, sheet2Id); deleteSheet(model, sheet1Id); paste(model, "A2"); expect(getCellContent(model, "A2", sheet2Id)).toBe("Apple"); expect(getCell(model, "A2", sheet2Id)!.style).toEqual({ bold: true }); }); test("can copy into a cell with style", () => { ({ model, store } = makeStore(ClipboardStore)); // set value and style in B2 setCellContent(model, "B2", "b2"); selectCell(model, "B2"); setFormatting(model, "B2", { bold: true }); expect(getCell(model, "B2")!.style).toEqual({ bold: true }); // set value in A1, select and copy it setCellContent(model, "A1", "a1"); selectCell(model, "A1"); copy(model, "A1"); // select B2 again and paste paste(model, "B2"); expect(getEvaluatedCell(model, "B2").value).toBe("a1"); expect(getCell(model, "B2")!.style).not.toBeDefined(); }); test("can copy from an empty cell into a cell with style", () => { ({ model, store } = makeStore(ClipboardStore)); // set value and style in B2 setCellContent(model, "B2", "b2"); selectCell(model, "B2"); setFormatting(model, "B2", { bold: true }); expect(getCell(model, "B2")!.style).toEqual({ bold: true }); // set value in A1, select and copy it selectCell(model, "A1"); copy(model, "A1"); paste(model, "B2"); expect(getCell(model, "B2")).toBeUndefined(); }); test("can copy a cell with borders", () => { ({ model, store } = makeStore(ClipboardStore)); selectCell(model, "B2"); setZoneBorders(model, { position: "bottom" }); expect(getBorder(model, "B2")).toEqual({ bottom: DEFAULT_BORDER_DESC }); copy(model, "B2"); paste(model, "C2"); expect(getBorder(model, "B2")).toEqual({ bottom: DEFAULT_BORDER_DESC }); expect(getBorder(model, "C2")).toEqual({ bottom: DEFAULT_BORDER_DESC }); }); test("paste cell does not overwrite existing borders", () => { ({ model, store } = makeStore(ClipboardStore)); const sheetId = model.getters.getActiveSheetId(); setZoneBorders(model, { position: "all" }, ["A1"]); copy(model, "B2"); paste(model, "A1"); expect(model.getters.getCellBorder({ sheetId, col: 0, row: 0 })).toEqual({ top: DEFAULT_BORDER_DESC, bottom: DEFAULT_BORDER_DESC, left: DEFAULT_BORDER_DESC, right: DEFAULT_BORDER_DESC, }); }); test("can copy a cell with a format", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "0.451"); selectCell(model, "B2"); setFormat(model, "B2", "0.00%"); expect(getCellContent(model, "B2")).toBe("45.10%"); copy(model, "B2"); paste(model, "C2"); expect(getCellContent(model, "C2")).toBe("45.10%"); }); test("can copy and paste merged content", () => { model = new Model({ sheets: [{ id: "s1", colNumber: 5, rowNumber: 5, merges: ["B1:C2"] }], }); ({ store } = makeStoreWithModel(model, ClipboardStore)); copy(model, "B1"); paste(model, "B4"); expect(model.getters.getMerges("s1")).toMatchObject([toZone("B1:C2"), toZone("B4:C5")]); }); test("can cut and paste merged content", () => { model = new Model({ sheets: [{ id: "s2", colNumber: 5, rowNumber: 5, merges: ["B1:C2"] }], }); ({ store } = makeStoreWithModel(model, ClipboardStore)); cut(model, "B1:C2"); paste(model, "B4"); expect(model.getters.getMerges("s2")).toHaveLength(1); expect(model.getters.getMerges("s2")).toMatchObject([toZone("B4:C5")]); }); test("can cut and paste merged content in another sheet", () => { model = new Model({ sheets: [{ id: "s1", colNumber: 5, rowNumber: 5, merges: ["B1:C2"] }, { id: "s2" }], }); ({ store } = makeStoreWithModel(model, ClipboardStore)); cut(model, "B1:C2"); activateSheet(model, "s2"); paste(model, "B4"); expect(model.getters.getMerges("s1")).toEqual([]); expect(model.getters.getMerges("s2")).toMatchObject([toZone("B4:C5")]); }); test("Pasting merge on content will remove the content", () => { model = new Model({ sheets: [ { id: "s1", colNumber: 5, rowNumber: 5, cells: { A1: "merge", C1: "a", D2: "a" }, merges: ["A1:B2"], }, ], }); ({ store } = makeStoreWithModel(model, ClipboardStore)); copy(model, "A1"); paste(model, "C1"); expect(model.getters.isInMerge({ sheetId: "s1", ...toCartesian("C1") })).toBe(true); expect(model.getters.isInMerge({ sheetId: "s1", ...toCartesian("D2") })).toBe(true); expect(getCellContent(model, "C1")).toBe("merge"); expect(getCellContent(model, "D2")).toBe(""); }); test("copy/paste a merge from one page to another", () => { model = new Model({ sheets: [ { id: "s1", colNumber: 5, rowNumber: 5, merges: ["B2:C3"] }, { id: "s2", colNumber: 5, rowNumber: 5 }, ], }); ({ store } = makeStoreWithModel(model, ClipboardStore)); const sheet2 = "s2"; copy(model, "B2"); activateSheet(model, sheet2); paste(model, "A1"); expect(model.getters.isInMerge({ sheetId: sheet2, ...toCartesian("A1") })).toBe(true); expect(model.getters.isInMerge({ sheetId: sheet2, ...toCartesian("A2") })).toBe(true); expect(model.getters.isInMerge({ sheetId: sheet2, ...toCartesian("B1") })).toBe(true); expect(model.getters.isInMerge({ sheetId: sheet2, ...toCartesian("B2") })).toBe(true); }); test("copy/paste a formula that has no sheet specific reference to another", () => { model = new Model({ sheets: [ { id: "s1", colNumber: 5, rowNumber: 5, cells: { A1: "=A2" } }, { id: "s2", colNumber: 5, rowNumber: 5 }, ], }); ({ store } = makeStoreWithModel(model, ClipboardStore)); expect(getCellText(model, "A1", "s1")).toBe("=A2"); copy(model, "A1"); activateSheet(model, "s2"); paste(model, "A1"); expect(getCellText(model, "A1", "s1")).toBe("=A2"); expect(getCellText(model, "A1", "s2")).toBe("=A2"); }); test("Pasting content that will destroy a merge will fail", async () => { ({ model, store } = makeStore(ClipboardStore)); const sheetId = model.getters.getActiveSheetId(); merge(model, "B2:C3"); copy(model, "B2"); const result = store.isCommandValid({ type: "PASTE", target: target("A1") }); expect(result).toBeCancelledBecause(CommandResult.WillRemoveExistingMerge); expect(model.getters.getMerges(sheetId).map(zoneToXc)).toEqual(["B2:C3"]); }); test("Can paste a single cell on a merge", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "thingies"); merge(model, "B1:B2"); copy(model, "A1"); paste(model, "B1:B2"); expect(getCellContent(model, "B1")).toEqual("thingies"); }); test("copy zones with multiple compatible merges => paste => it should paste with all merges", () => { model = new Model({ sheets: [{ id: "s1", merges: ["A1:A3", "C1:C3"] }], }); ({ store } = makeStoreWithModel(model, ClipboardStore)); copy(model, "A1:C3"); paste(model, "E1"); const sheetId = model.getters.getActiveSheetId(); expect(model.getters.getMerges(sheetId).map(zoneToXc)).toEqual([ "A1:A3", "C1:C3", "E1:E3", "G1:G3", ]); }); test("copy zones with multiple compatible merges with CTRL+CLICK => paste => it should paste with all merges", () => { model = new Model({ sheets: [{ id: "s1", merges: ["A1:A3", "C1:C3"] }], }); ({ store } = makeStoreWithModel(model, ClipboardStore)); copy(model, "A1", "C1"); paste(model, "E1"); const sheetId = model.getters.getActiveSheetId(); expect(model.getters.getMerges(sheetId).map(zoneToXc)).toEqual([ "A1:A3", "C1:C3", "E1:E3", "F1:F3", ]); }); test("copy zones with one merge => unmerge origin cell => paste => it should paste with original merge", () => { ({ model, store } = makeStore(ClipboardStore)); const sheetId = model.getters.getActiveSheetId(); merge(model, "A1:C3"); copy(model, "A1"); unMerge(model, "A1:C3"); paste(model, "E1"); expect(model.getters.getMerges(sheetId).map(zoneToXc)).toEqual(["E1:G3"]); }); test("copy zones with multiple compatible merges => unmerge origin zones => paste => it should paste with all merges", () => { ({ model, store } = makeStore(ClipboardStore)); const sheetId = model.getters.getActiveSheetId(); merge(model, "A1:A3"); merge(model, "C1:C3"); copy(model, "A1:C3"); unMerge(model, "A1:A3"); unMerge(model, "C1:C3"); paste(model, "E1"); expect(model.getters.getMerges(sheetId).map(zoneToXc)).toEqual(["E1:E3", "G1:G3"]); }); test("copy zones with multiple compatible merges => delete origin sheet => paste => it should paste with all merges", () => { ({ model, store } = makeStore(ClipboardStore)); const sheetId = model.getters.getActiveSheetId(); merge(model, "A1:A3"); merge(model, "C1:C3"); copy(model, "A1:C3"); const newSheetId = "Sheet2"; createSheet(model, { sheetId: newSheetId }); activateSheet(model, newSheetId); deleteSheet(model, sheetId); paste(model, "E1"); expect(model.getters.getMerges(newSheetId).map(zoneToXc)).toEqual(["E1:E3", "G1:G3"]); }); test("cutting a cell with style remove the cell", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); selectCell(model, "B2"); setFormatting(model, "B2", { bold: true }); cut(model, "B2"); paste(model, "C2"); expect(getCell(model, "C2")).toMatchObject({ style: { bold: true }, content: "b2", }); expect(getCell(model, "B2")).toBeUndefined(); }); test("Clipboard text content export formatted string", async () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "abc"); selectCell(model, "B2"); copy(model, "B2"); expect(store.getClipboardTextContent()).toBe("abc"); setCellContent(model, "B2", "= 1 + 2"); selectCell(model, "B2"); copy(model, "B2"); expect(store.getClipboardTextContent()).toBe("3"); }); describe("Copied cells HTML", () => { beforeEach(() => { ({ model, store } = makeStore(ClipboardStore)); }); test("Copied HTML table snapshot", async () => { setCellContent(model, "A1", "1"); setCellContent(model, "B1", "2"); setCellContent(model, "A2", "3"); copy(model, "A1:B2"); const osClipboardContent = await store.getClipboardTextAndImageContent(); const htmlContent = osClipboardContent[ClipboardMIMEType.Html]!; const clipboardId = store.getClipboardId(); const clipboardData = JSON.stringify(store["getSheetData"]()); const expectedHtmlContent = `
12
3
`; expect(htmlContent).toBe(expectedHtmlContent); }); test("Copied group of cells are represented as a valid HTML table in the clipboard", async () => { setCellContent(model, "A1", "1"); setCellContent(model, "B1", "2"); setCellContent(model, "A2", "3"); copy(model, "A1:B2"); const osClipboardContent = await store.getClipboardTextAndImageContent(); const htmlContent = osClipboardContent[ClipboardMIMEType.Html]!; const parsedHTML = parseXML(new XMLString(htmlContent), "text/html"); expect(parsedHTML.body.firstElementChild?.tagName).toBe("DIV"); const tableRows = parsedHTML.querySelectorAll("tr"); expect(tableRows).toHaveLength(2); expect(tableRows[0].querySelectorAll("td")).toHaveLength(2); expect(tableRows[0].querySelectorAll("td")[0].innerHTML).toEqual("1"); expect(tableRows[0].querySelectorAll("td")[1].innerHTML).toEqual("2"); expect(tableRows[1].querySelectorAll("td")).toHaveLength(2); expect(tableRows[1].querySelectorAll("td")[0].innerHTML).toEqual("3"); expect(tableRows[1].querySelectorAll("td")[1].innerHTML).toEqual(""); }); test("Copied HTML table style", async () => { setCellContent(model, "A1", "1"); setCellContent(model, "A2", "3"); copy(model, "A1:A2"); const osClipboardContent = await store.getClipboardTextAndImageContent(); const htmlContent = osClipboardContent[ClipboardMIMEType.Html]!; expect(htmlContent).toContain('style="border-collapse:collapse"'); expect(htmlContent).toContain('border="1"'); }); test("Copied cells have their style in the HTML", async () => { setCellContent(model, "A1", "1"); setCellContent(model, "A2", "3"); setFormatting(model, "A1", { bold: true }); addEqualCf(model, "A1", { fillColor: "#123456" }, "1"); copy(model, "A1:A2"); const osClipboardContent = await store.getClipboardTextAndImageContent(); const htmlContent = osClipboardContent[ClipboardMIMEType.Html]!; const firstCellStyle = htmlContent .replace(/\n/g, "") .match(/.*?<\/td>/)![1]; expect(firstCellStyle).toContain("font-weight:bold;"); expect(firstCellStyle).toContain("background:#123456;"); }); test("Copied cells have their content escaped", async () => { const cellContent = "
1
"; setCellContent(model, "A1", cellContent); setCellContent(model, "A2", "3"); copy(model, "A1:A2"); const osClipboardContent = await store.getClipboardTextAndImageContent(); const htmlContent = osClipboardContent[ClipboardMIMEType.Html]!; expect(htmlContent).toContain(xmlEscape(cellContent)); }); test("Copied single cells are not in a html table", async () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); copy(model, "A1"); const clipboardId = store.getClipboardId(); const clipboardData = JSON.stringify(store["getSheetData"]()); const osClipboardContent = await store.getClipboardTextAndImageContent(); expect(osClipboardContent[ClipboardMIMEType.Html]).toBe( `
1
` ); }); test("Copied cell content is html-escaped", async () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "hello"); copy(model, "A1"); const clipboardId = store.getClipboardId(); const clipboardData = JSON.stringify(store["getSheetData"]()); const osClipboardContent = await store.getClipboardTextAndImageContent(); expect(osClipboardContent[ClipboardMIMEType.Html]).toBe( `
<b>hello</b>
` ); }); }); test("can copy a rectangular selection", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); setCellContent(model, "B3", "b3"); setCellContent(model, "C2", "c2"); setCellContent(model, "C3", "c3"); copy(model, "B2:C3"); expect(getCell(model, "D1")).toBeUndefined(); expect(getCell(model, "D2")).toBeUndefined(); expect(getCell(model, "E1")).toBeUndefined(); expect(getCell(model, "E2")).toBeUndefined(); paste(model, "D1"); expect(getCellContent(model, "D1")).toBe("b2"); expect(getCellContent(model, "D2")).toBe("b3"); expect(getCellContent(model, "E1")).toBe("c2"); expect(getCellContent(model, "E2")).toBe("c3"); }); test("empty clipboard: getClipboardTextAndImageContent returns a tab", () => { ({ model, store } = makeStore(ClipboardStore)); expect(store.getClipboardTextContent()).toBe("\t"); }); test("Clipboard Text exports multiple cells", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); setCellContent(model, "B3", "b3"); setCellContent(model, "C2", "c2"); setCellContent(model, "C3", "c3"); copy(model, "B2:C3"); expect(store.getClipboardTextContent()).toBe("b2\tc2\nb3\tc3"); }); test("can paste multiple cells from os clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); pasteFromOSClipboard(model, "C1", { text: "a\t1\nb\t2" }); expect(getCellContent(model, "C1")).toBe("a"); expect(getCellContent(model, "C2")).toBe("b"); expect(getCellContent(model, "D1")).toBe("1"); expect(getCellContent(model, "D2")).toBe("2"); }); test("Pasting content from os that will destroy a merge will fail", () => { ({ model, store } = makeStore(ClipboardStore)); const sheetId = model.getters.getActiveSheetId(); merge(model, "B2:C3"); const result = store.isCommandValid({ type: "PASTE_FROM_OS_CLIPBOARD", target: target("B2"), clipboardContent: { text: "a\t1\nb\t2" }, }); expect(result).toBeCancelledBecause(CommandResult.WillRemoveExistingMerge); expect(model.getters.getMerges(sheetId).map(zoneToXc)).toEqual(["B2:C3"]); }); test("pasting from OS will not change the viewport", () => { ({ model, store, container } = makeStore(ClipboardStore)); const viewStore = container.get(ViewportsStore); const viewport = viewStore.activeMainViewport; pasteFromOSClipboard(model, "C60", { text: "a\t1\nb\t2" }); expect(viewStore.activeMainViewport).toEqual(viewport); }); test("pasting numbers from windows clipboard => interpreted as number", () => { ({ model, store } = makeStore(ClipboardStore)); pasteFromOSClipboard(model, "C1", { text: "1\r\n2\r\n3" }); expect(getCellContent(model, "C1")).toBe("1"); expect(getEvaluatedCell(model, "C1").value).toBe(1); expect(getCellContent(model, "C2")).toBe("2"); expect(getEvaluatedCell(model, "C2").value).toBe(2); expect(getCellContent(model, "C3")).toBe("3"); expect(getEvaluatedCell(model, "C3").value).toBe(3); }); test("incompatible multiple selections: only last one is actually copied", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "a1"); setCellContent(model, "A2", "a2"); setCellContent(model, "C1", "c1"); copy(model, "A1:A2", "C1"); expect(getClipboardVisibleZones(store).length).toBe(1); selectCell(model, "E1"); paste(model, "E1"); expect(getCellContent(model, "E1")).toBe("c1"); expect(getCell(model, "E2")).toBeUndefined(); }); test("compatible multiple selections: each column is copied", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "a1"); setCellContent(model, "A2", "a2"); setCellContent(model, "C1", "c1"); setCellContent(model, "C2", "c2"); copy(model, "A1:A2", " C1:C2"); expect(getClipboardVisibleZones(store).length).toBe(2); paste(model, "E1"); expect(getCellContent(model, "E1")).toBe("a1"); expect(getCellContent(model, "E2")).toBe("a2"); expect(getCellContent(model, "F1")).toBe("c1"); expect(getCellContent(model, "F2")).toBe("c2"); }); test("Viewport won't move after pasting", () => { ({ model, store, container } = makeStore(ClipboardStore)); const viewStore = container.get(ViewportsStore); copy(model, "A1:B2"); setSelection(model, ["C60:D70"]); viewStore.setViewportOffset({ offsetX: 0, offsetY: 0 }); const viewport = viewStore.activeMainViewport; paste(model, "C60:D70"); expect(viewStore.activeMainViewport).toEqual(viewport); }); describe("copy/paste a zone in a larger selection will duplicate the zone on the selection as long as it does not exceed it", () => { test("paste a value (zone with hight=1 and width=1)", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); copy(model, "A1"); paste(model, "C2:D3"); expect(getCellContent(model, "C2")).toBe("1"); expect(getCellContent(model, "C3")).toBe("1"); expect(getCellContent(model, "D2")).toBe("1"); expect(getCellContent(model, "D3")).toBe("1"); }); test("paste a zone with hight zone > 1", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "a1"); setCellContent(model, "A2", "a2"); copy(model, "A1:A2"); paste(model, "A3:A7"); expect(getCellContent(model, "A3")).toBe("a1"); expect(getCellContent(model, "A4")).toBe("a2"); expect(getCellContent(model, "A5")).toBe("a1"); expect(getCellContent(model, "A6")).toBe("a2"); expect(getCellContent(model, "A7")).toBe(""); }); test("paste a zone with width zone > 1", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "a1"); setCellContent(model, "B1", "b1"); copy(model, "A1:B1"); paste(model, "C1:G1"); expect(getCellContent(model, "C1")).toBe("a1"); expect(getCellContent(model, "D1")).toBe("b1"); expect(getCellContent(model, "E1")).toBe("a1"); expect(getCellContent(model, "F1")).toBe("b1"); expect(getCellContent(model, "G1")).toBe(""); }); test("selection is updated to contain exactly the new pasted zone", () => { ({ model, store } = makeStore(ClipboardStore)); copy(model, "A1:B2"); // select C3:G7 selectCell(model, "C3"); setAnchorCorner(model, "G7"); expect(model.getters.getSelectedZones()[0]).toEqual({ top: 2, left: 2, bottom: 6, right: 6 }); paste(model, "C3:G7"); expect(model.getters.getSelectedZones()[0]).toEqual({ top: 2, left: 2, bottom: 5, right: 5 }); }); }); describe("cut/paste a zone in a larger selection will paste the zone only once", () => { test("paste a value (zone with hight=1 and width=1)", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); cut(model, "A1"); paste(model, "C2:D3"); expect(getCellContent(model, "C2")).toBe("1"); expect(getCellContent(model, "C3")).toBe(""); expect(getCellContent(model, "D2")).toBe(""); expect(getCellContent(model, "D3")).toBe(""); }); test("with hight zone > 1", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "a1"); setCellContent(model, "A2", "a2"); cut(model, "A1:A2"); paste(model, "A3:A7"); expect(getCellContent(model, "A3")).toBe("a1"); expect(getCellContent(model, "A4")).toBe("a2"); expect(getCellContent(model, "A5")).toBe(""); expect(getCellContent(model, "A6")).toBe(""); expect(getCellContent(model, "A7")).toBe(""); }); test("with width zone > 1", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "a1"); setCellContent(model, "B1", "b1"); cut(model, "A1:B1"); paste(model, "C1:G1"); expect(getCellContent(model, "C1")).toBe("a1"); expect(getCellContent(model, "D1")).toBe("b1"); expect(getCellContent(model, "E1")).toBe(""); expect(getCellContent(model, "F1")).toBe(""); expect(getCellContent(model, "G1")).toBe(""); }); test("selection is updated to contain exactly the cut and pasted zone", () => { ({ model, store } = makeStore(ClipboardStore)); cut(model, "A1:B2"); // select C3:G7 selectCell(model, "C3"); setAnchorCorner(model, "G7"); expect(model.getters.getSelectedZones()[0]).toEqual({ top: 2, left: 2, bottom: 6, right: 6 }); paste(model, "C3:G7"); expect(model.getters.getSelectedZones()[0]).toEqual({ top: 2, left: 2, bottom: 3, right: 3 }); }); }); describe("copy/paste a zone in several selection will duplicate the zone on each selection", () => { test("paste a value (zone with hight=1 and width=1)", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "33"); copy(model, "A1"); paste(model, "C1, E1"); expect(getCellContent(model, "C1")).toBe("33"); expect(getCellContent(model, "E1")).toBe("33"); }); test("selection is updated to contain exactly the new pasted zones", () => { ({ model, store } = makeStore(ClipboardStore)); copy(model, "A1"); // select C1 and E1 selectCell(model, "C1"); addCellToSelection(model, "E1"); paste(model, "C1, E1"); expect(model.getters.getSelectedZones()[0]).toEqual({ top: 0, left: 2, bottom: 0, right: 2 }); expect(model.getters.getSelectedZones()[1]).toEqual({ top: 0, left: 4, bottom: 0, right: 4 }); }); test("paste a zone with more than one value is not allowed", () => { ({ model, store } = makeStore(ClipboardStore)); copy(model, "A1:B2"); const result = store.isCommandValid({ type: "PASTE", target: target("C1, E1") }); expect(result).toBeCancelledBecause(CommandResult.WrongPasteSelection); }); }); describe("cut/paste a zone in several selection will paste the zone only once", () => { test("paste a value (zone with hight=1 and width=1)", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "33"); cut(model, "A1"); paste(model, "E1, C1"); expect(getCellContent(model, "E1")).toBe("33"); expect(getCellContent(model, "C1")).toBe(""); }); test("selection is updated to contain exactly the new pasted zones", () => { ({ model, store } = makeStore(ClipboardStore)); cut(model, "A1"); // select C1 and E1 selectCell(model, "C1"); addCellToSelection(model, "E1"); paste(model, "C1, E1"); expect(model.getters.getSelectedZones()[0]).toEqual({ top: 0, left: 2, bottom: 0, right: 2 }); expect(model.getters.getSelectedZones().length).toBe(1); }); test("paste a zone with more than one value is not allowed", () => { ({ model, store } = makeStore(ClipboardStore)); cut(model, "A1:B2"); const result = store.isCommandValid({ type: "PASTE", target: target("C1, E1") }); expect(result).toBeCancelledBecause(CommandResult.WrongPasteSelection); }); }); describe("cut/paste several zones", () => { test("cutting is not allowed if multiple selection", () => { ({ model, store } = makeStore(ClipboardStore)); setSelection(model, ["A1", "A2"]); const result = store.isCommandValid({ type: "CUT" }); expect(result).toBeCancelledBecause(CommandResult.WrongCutSelection); }); }); describe("copy/paste several zones", () => { beforeEach(() => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "a1"); setCellContent(model, "A2", "a2"); setCellContent(model, "A3", "a3"); setCellContent(model, "B1", "b1"); setCellContent(model, "B2", "b2"); setCellContent(model, "B3", "b3"); setCellContent(model, "C1", "c1"); setCellContent(model, "C2", "c2"); setCellContent(model, "C3", "c3"); }); describe("if they have same left and same right", () => { test("copy all zones", () => { copy(model, "A1:B1", " A2:B2"); expect(getClipboardVisibleZones(store)[0]).toEqual(toZone("A1:B1")); expect(getClipboardVisibleZones(store)[1]).toEqual(toZone("A2:B2")); expect(getClipboardVisibleZones(store).length).toBe(2); paste(model, "F6"); expect(getCellContent(model, "F6")).toBe("a1"); expect(getCellContent(model, "F7")).toBe("a2"); expect(getCellContent(model, "G6")).toBe("b1"); expect(getCellContent(model, "G7")).toBe("b2"); }); test("Copy cells only once", () => { copy(model, "A1:A3", "A1:A2", "A2:A3", "A1", "A2", "A3"); expect(getClipboardVisibleZones(store)[0]).toEqual(toZone("A1:A3")); expect(getClipboardVisibleZones(store).length).toBe(1); paste(model, "F6"); expect(getCellContent(model, "F6")).toBe("a1"); expect(getCellContent(model, "F7")).toBe("a2"); expect(getCellContent(model, "F8")).toBe("a3"); expect(getCellContent(model, "F9")).toBe(""); }); test("paste zones without gap", () => { // gap between 1st selection and 2nd selection is one row copy(model, "A1:B1", "A3:B3"); expect(getClipboardVisibleZones(store)[0]).toEqual(toZone("A1:B1")); expect(getClipboardVisibleZones(store)[1]).toEqual(toZone("A3:B3")); expect(getClipboardVisibleZones(store).length).toBe(2); paste(model, "F6"); expect(getCellContent(model, "F6")).toBe("a1"); expect(getCellContent(model, "F7")).toBe("a3"); expect(getCellContent(model, "G6")).toBe("b1"); expect(getCellContent(model, "G7")).toBe("b3"); }); test("paste zones selected from different orders does not influence the final result", () => { copy(model, "A1", "A2"); paste(model, "F6"); expect(getCellContent(model, "F6")).toBe("a1"); expect(getCellContent(model, "F7")).toBe("a2"); copy(model, "A2", "A1"); paste(model, "F6"); expect(getCellContent(model, "F6")).toBe("a1"); expect(getCellContent(model, "F7")).toBe("a2"); }); }); describe("if zones have same top and same bottom", () => { test("copy all zones", () => { copy(model, "A1:A2", "B1:B2"); expect(getClipboardVisibleZones(store)[0]).toEqual(toZone("A1:A2")); expect(getClipboardVisibleZones(store)[1]).toEqual(toZone("B1:B2")); expect(getClipboardVisibleZones(store).length).toBe(2); paste(model, "F6"); expect(getCellContent(model, "F6")).toBe("a1"); expect(getCellContent(model, "F7")).toBe("a2"); expect(getCellContent(model, "G6")).toBe("b1"); expect(getCellContent(model, "G7")).toBe("b2"); }); test("Copy cells only once", () => { copy(model, "A1:C1", "A1:B1", "B1:C1", "A1", "B1", "C1"); expect(getClipboardVisibleZones(store)[0]).toEqual(toZone("A1:C1")); expect(getClipboardVisibleZones(store).length).toBe(1); paste(model, "F6"); expect(getCellContent(model, "F6")).toBe("a1"); expect(getCellContent(model, "G6")).toBe("b1"); expect(getCellContent(model, "H6")).toBe("c1"); expect(getCellContent(model, "I6")).toBe(""); }); test("paste zones without gap", () => { // gap between 1st selection and 2nd selection is one column copy(model, "A1:A2", "C1:C2"); paste(model, "F6"); expect(getCellContent(model, "F6")).toBe("a1"); expect(getCellContent(model, "F7")).toBe("a2"); expect(getCellContent(model, "G6")).toBe("c1"); expect(getCellContent(model, "G7")).toBe("c2"); }); test("paste zones selected from different orders does not influence the final result", () => { copy(model, "A1", "B1"); paste(model, "F6"); expect(getCellContent(model, "F6")).toBe("a1"); expect(getCellContent(model, "G6")).toBe("b1"); copy(model, "A1", "B1"); paste(model, "F6"); expect(getCellContent(model, "F6")).toBe("a1"); expect(getCellContent(model, "G6")).toBe("b1"); }); }); describe("copy/paste the last zone if zones don't have [same top and same bottom] or [same left and same right]", () => { test("test with dissociated zones", () => { copy(model, "A1:A2", "B2:B3"); expect(getClipboardVisibleZones(store)[0]).toEqual(toZone("B2:B3")); expect(getClipboardVisibleZones(store).length).toBe(1); paste(model, "F6"); expect(getCellContent(model, "F6")).toBe("b2"); expect(getCellContent(model, "F7")).toBe("b3"); }); test("test with overlapped zones", () => { copy(model, "A1:B2", "B2:B3"); expect(getClipboardVisibleZones(store)[0]).toEqual(toZone("B2:B3")); expect(getClipboardVisibleZones(store).length).toBe(1); paste(model, "F6"); expect(getCellContent(model, "F6")).toBe("b2"); expect(getCellContent(model, "F7")).toBe("b3"); }); }); test("can paste zones in a larger selection", () => { copy(model, "A1", "C1"); paste(model, "E1:I1"); expect(getCellContent(model, "E1")).toBe("a1"); expect(getCellContent(model, "F1")).toBe("c1"); expect(getCellContent(model, "G1")).toBe("a1"); expect(getCellContent(model, "H1")).toBe("c1"); expect(getCellContent(model, "I1")).toBe(""); }); test("is not allowed if paste in several selection", () => { copy(model, "A1", "C1"); const result = store.isCommandValid({ type: "PASTE", target: target("A2, B2") }); expect(result).toBeCancelledBecause(CommandResult.WrongPasteSelection); }); }); test("can copy and paste a cell with STRING content", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", '="test"'); expect(getCellText(model, "B2")).toEqual('="test"'); expect(getEvaluatedCell(model, "B2").value).toEqual("test"); copy(model, "B2"); paste(model, "D2"); expect(getCellText(model, "B2")).toEqual('="test"'); expect(getEvaluatedCell(model, "B2").value).toEqual("test"); expect(getCellText(model, "D2")).toEqual('="test"'); expect(getEvaluatedCell(model, "D2").value).toEqual("test"); expect(getClipboardVisibleZones(store).length).toBe(0); }); test("can undo a paste operation", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); copy(model, "B2"); paste(model, "D2"); expect(getCell(model, "D2")).toBeDefined(); undo(model); expect(getCell(model, "D2")).toBeUndefined(); }); test("can paste-format a cell with style", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); selectCell(model, "B2"); setFormatting(model, "B2", { bold: true }); expect(getCell(model, "B2")!.style).toEqual({ bold: true }); copy(model, "B2"); paste(model, "C2", "onlyFormat"); expect(getCellContent(model, "C2")).toBe(""); expect(getCell(model, "C2")!.style).toEqual({ bold: true }); }); test("can copy and paste format", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); setFormatting(model, "B2", { bold: true }); selectCell(model, "B2"); expect(getCell(model, "B2")!.style).toEqual({ bold: true }); copy(model, "B2"); paste(model, "C2", "onlyFormat"); expect(getCellContent(model, "C2")).toBe(""); expect(getCell(model, "C2")!.style).toEqual({ bold: true }); }); test("paste format does not remove content", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); setCellContent(model, "C2", "c2"); setFormatting(model, "B2", { bold: true }); selectCell(model, "B2"); expect(getCell(model, "B2")!.style).toEqual({ bold: true }); copy(model, "B2"); paste(model, "C2", "onlyFormat"); expect(getCellContent(model, "C2")).toBe("c2"); expect(getCell(model, "C2")!.style).toEqual({ bold: true }); }); test("can undo a paste format", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); setFormatting(model, "B2", { bold: true }); selectCell(model, "B2"); copy(model, "B2"); paste(model, "C2", "onlyFormat"); expect(getCellContent(model, "C2")).toBe(""); expect(getCell(model, "C2")!.style).toEqual({ bold: true }); undo(model); expect(getCell(model, "C2")).toBeUndefined(); }); test("can copy and paste as value", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); selectCell(model, "B2"); copy(model, "B2"); paste(model, "C2", "asValue"); expect(getCellContent(model, "C2")).toBe("b2"); }); test("can copy a cell with a style and paste as value", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); setFormatting(model, "B2", { bold: true }); selectCell(model, "B2"); expect(getCell(model, "B2")!.style).toEqual({ bold: true }); copy(model, "B2"); paste(model, "C2", "asValue"); expect(getEvaluatedCell(model, "C2").value).toBe("b2"); expect(getCell(model, "C2")!.style).not.toBeDefined(); }); test("can copy a cell with a border and paste as value", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); selectCell(model, "B2"); setZoneBorders(model, { position: "bottom" }); expect(getBorder(model, "B2")).toEqual({ bottom: DEFAULT_BORDER_DESC }); copy(model, "B2"); paste(model, "C2", "asValue"); expect(getEvaluatedCell(model, "C2").value).toBe("b2"); expect(getBorder(model, "C2")).toBeNull(); }); test("can copy a cell with a conditional format and paste as value", () => { model = new Model({ sheets: [{ colNumber: 5, rowNumber: 5 }] }); ({ store } = makeStoreWithModel(model, ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); setCellContent(model, "C1", "1"); setCellContent(model, "C2", "2"); const result = addEqualCf(model, "A1,A2", { fillColor: "#FF0000" }, "1"); expect(result).toBeSuccessfullyDispatched(); copy(model, "A1"); paste(model, "C1", "asValue"); copy(model, "A2"); paste(model, "C2", "asValue"); expect(getStyle(model, "A1")).toEqual({ fillColor: "#FF0000", }); expect(getStyle(model, "A2")).toEqual({}); expect(getStyle(model, "C1")).toEqual({}); expect(getStyle(model, "C2")).toEqual({}); }); test("paste as value does not remove style", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); setCellContent(model, "C3", "c3"); selectCell(model, "C3"); setFormatting(model, "C3", { bold: true }); expect(getCell(model, "C3")!.style).toEqual({ bold: true }); copy(model, "B2"); paste(model, "C3", "asValue"); expect(getCellContent(model, "C3")).toBe("b2"); expect(getCell(model, "C3")!.style).toEqual({ bold: true }); }); test("paste as value does not remove border", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); setCellContent(model, "C3", "c3"); setZoneBorders(model, { position: "bottom" }, ["C3"]); expect(getBorder(model, "C3")).toEqual({ bottom: DEFAULT_BORDER_DESC }); expect(getBorder(model, "C4")).toBeNull(); copy(model, "B2"); paste(model, "C3", "asValue"); expect(getCellContent(model, "C3")).toBe("b2"); expect(getBorder(model, "C3")).toEqual({ bottom: DEFAULT_BORDER_DESC }); expect(getBorder(model, "C4")).toBeNull(); }); test("paste as value does remove number format", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "0.451"); setFormat(model, "B2", "0.00%"); expect(getCellContent(model, "B2")).toBe("45.10%"); setCellContent(model, "C3", "42"); setFormat(model, "C3", "#,##0.00"); expect(getCellContent(model, "C3")).toBe("42.00"); copy(model, "B2"); paste(model, "C3", "asValue"); expect(getCellContent(model, "C3")).toBe("0.45"); }); test("paste as value works with both no core format and empty string core format", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "D4", "=DATE(2024,6,5)"); copy(model, "D4"); paste(model, "E4", "asValue"); expect(getCell(model, "E4")).toMatchObject({ content: "45448", format: undefined }); setFormat(model, "D4", ""); // An empty string format is equivalent to no format expect(getCellContent(model, "D4")).toBe("6/5/2024"); copy(model, "D4"); paste(model, "E5", "asValue"); expect(getCell(model, "E5")).toMatchObject({ content: "45448", format: undefined }); }); test.each([ ["1", "0.00%", "100.00%"], ["46023", "m/d/yyyy", "1/1/2026"], ])( "can copy a cell with a format and paste as value", (originalContent, format, formatedContent) => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", originalContent); setFormat(model, "B2", format); expect(getCellContent(model, "B2")).toBe(formatedContent); expect(getCell(model, "B2")!.format).toEqual(format); copy(model, "B2"); paste(model, "C2", "asValue"); expect(getCellContent(model, "C2")).toBe(originalContent); expect(getCell(model, "C2")!.format).not.toBeDefined(); } ); test("copy as value : the cell take the format of the target cell", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "46023"); setFormat(model, "B2", "0.00%"); expect(getCellContent(model, "B2")).toBe("4602300.00%"); expect(getCell(model, "B2")!.format).toEqual("0.00%"); setFormat(model, "C2", "m/d/yyyy"); copy(model, "B2"); paste(model, "C2", "asValue"); expect(getCellContent(model, "C2")).toBe("1/1/2026"); expect(getCell(model, "C2")!.format).toEqual("m/d/yyyy"); }); test("can copy a formula and paste as value", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "=SUM(1+2)"); setCellContent(model, "A2", "=EQ(42,42)"); setCellContent(model, "A3", '=CONCAT("Ki","kou")'); copy(model, "A1:A3"); paste(model, "B1", "asValue"); expect(getCellContent(model, "B1")).toBe("3"); expect(getCellContent(model, "B2")).toBe("TRUE"); expect(getCellContent(model, "B3")).toBe("Kikou"); }); test("Can paste localized content as value", () => { ({ model, store } = makeStore(ClipboardStore)); updateLocale(model, DEFAULT_LOCALES[1]); setCellContent(model, "A1", "5.4"); setCellContent(model, "A2", "=SUM(4.5)"); copy(model, "A1:A2"); paste(model, "B1", "asValue"); expect(getCellRawContent(model, "B1")).toBe("5.4"); expect(getCellRawContent(model, "B2")).toBe("4.5"); }); test("can copy a formula and paste -> apply the format defined by user, if not apply the automatic evaluated format ", () => { ({ model, store } = makeStore(ClipboardStore)); // formula without format setCellContent(model, "A1", "=SUM(1+2)"); // formula with format seted on it setCellContent(model, "A2", "=SUM(1+2)"); setCellFormat(model, "A2", "0%"); // formula that return value with format setCellContent(model, "A3", "=DATE(2042,1,1)"); // formula that return value with format and other format seted on it setCellContent(model, "A4", "=DATE(2042,1,1)"); setCellFormat(model, "A4", "0%"); // formula that return value with format infered from reference setCellContent(model, "A5", "3"); setCellFormat(model, "A5", "0%"); setCellContent(model, "A6", "=SUM(1+A5)"); // formula that return value with format infered from reference and other format seted on it setCellContent(model, "A7", "3"); setCellFormat(model, "A7", "0%"); setCellContent(model, "A8", "=SUM(1+A7)"); setCellFormat(model, "A8", "#,##0[$$]"); copy(model, "A1:A8"); paste(model, "B1"); setCellFormat(model, "B5", "#,##0[$$]"); setCellFormat(model, "B7", "0%"); expect(getCellContent(model, "B1")).toBe("3"); expect(getCellContent(model, "B2")).toBe("300%"); expect(getCellContent(model, "B3")).toBe("1/1/2042"); expect(getCellContent(model, "B4")).toBe("5186700%"); expect(getCellContent(model, "B6")).toBe("4$"); expect(getCellContent(model, "B8")).toBe("4$"); }); test("can copy a formula and paste format only --> apply the automatic evaluated format", () => { ({ model, store } = makeStore(ClipboardStore)); // formula without format setCellContent(model, "A1", "=SUM(1+2)"); // formula with format seted on it setCellContent(model, "A2", "=SUM(1+2)"); setCellFormat(model, "A2", "0%"); // formula that return value with format setCellContent(model, "A3", "=DATE(2042,1,1)"); // formula that return value with format and other format seted on it setCellContent(model, "A4", "=DATE(2042,1,1)"); setCellFormat(model, "A4", "0%"); // formula that return value with format infered from reference setCellContent(model, "A5", "3"); setCellFormat(model, "A5", "0%"); setCellContent(model, "A6", "=SUM(1+A5)"); // formula that return value with format infered from reference and other format seted on it setCellContent(model, "A7", "3"); setCellFormat(model, "A7", "0%"); setCellContent(model, "A8", "=SUM(1+A7)"); setCellFormat(model, "A8", "#,##0[$$]"); setCellContent(model, "B1", "42"); setCellContent(model, "B2", "42"); setCellContent(model, "B3", "42"); setCellContent(model, "B4", "42"); setCellContent(model, "B6", "42"); setCellContent(model, "B8", "42"); copy(model, "A1:A8"); paste(model, "B1", "onlyFormat"); expect(getCellContent(model, "B1")).toBe("42"); expect(getCellContent(model, "B2")).toBe("4200%"); expect(getCellContent(model, "B3")).toBe("2/10/1900"); expect(getCellContent(model, "B4")).toBe("4200%"); expect(getCellContent(model, "B6")).toBe("4200%"); expect(getCellContent(model, "B8")).toBe("42$"); }); test("can undo a paste as value", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); selectCell(model, "B2"); setFormatting(model, "B2", { bold: true }); copy(model, "B2"); paste(model, "C2", "asValue"); expect(getCellContent(model, "C2")).toBe("b2"); expect(getCell(model, "C2")!.style).not.toBeDefined(); undo(model); expect(getCell(model, "C2")).toBeUndefined(); }); test("cut and paste as value is not allowed", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); cut(model, "B2"); const result = store.isCommandValid({ type: "PASTE", target: target("C3"), pasteOption: "asValue", }); expect(result).toBeCancelledBecause(CommandResult.WrongPasteOption); }); test("cut and paste format only is not allowed", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); cut(model, "B2"); const result = store.isCommandValid({ type: "PASTE", target: target("C3"), pasteOption: "onlyFormat", }); expect(result).toBeCancelledBecause(CommandResult.WrongPasteOption); }); describe("copy/paste a formula with references", () => { test("update the references", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "=SUM(C1:C2)"); copy(model, "A1"); paste(model, "B2"); expect(getCellText(model, "B2")).toBe("=SUM(D2:D3)"); }); /* $C2:E$1 <=> $C$1:E2 * * a b c d e * -------------------------------------------- * 1 | | | | x * | | | | * ----------------------------|--------- * 2 | | x | | * * * */ test.each([ ["=SUM(C1:C2)", "=SUM(D2:D3)"], ["=$C1", "=$C2"], ["=SUM($C1:D$1)", "=SUM($C$1:E2)"], //excel and g-sheet compatibility ($C2:E$1 <=> $C$1:E2) ])("does not update fixed references", (value, expected) => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", value); copy(model, "A1"); paste(model, "B2"); expect(getCellText(model, "B2")).toBe(expected); }); test("update cross-sheet reference", () => { ({ model, store } = makeStore(ClipboardStore)); createSheet(model, { sheetId: "42" }); setCellContent(model, "B2", "=Sheet2!B2"); copy(model, "B2"); paste(model, "B3"); expect(getCellText(model, "B3")).toBe("=Sheet2!B3"); }); test("update cross-sheet reference with a space in the name", () => { ({ model, store } = makeStore(ClipboardStore)); createSheetWithName(model, { sheetId: "42" }, "Sheet 2"); setCellContent(model, "B2", "='Sheet 2'!B2"); copy(model, "B2"); paste(model, "B3"); expect(getCellText(model, "B3")).toBe("='Sheet 2'!B3"); }); test("update cross-sheet reference in a smaller sheet", () => { ({ model, store } = makeStore(ClipboardStore)); createSheet(model, { sheetId: "42", rows: 2, cols: 2 }); setCellContent(model, "A1", "=Sheet2!A1:A2"); copy(model, "A1"); paste(model, "A2"); expect(getCellText(model, "A2")).toBe("=Sheet2!A2:A3"); }); test("update cross-sheet reference to a range", () => { ({ model, store } = makeStore(ClipboardStore)); createSheet(model, { sheetId: "42" }); setCellContent(model, "A1", "=SUM(Sheet2!A2:A5)"); copy(model, "A1"); paste(model, "B1"); expect(getCellText(model, "B1")).toBe("=SUM(Sheet2!B2:B5)"); }); }); test("can cut and paste an invalid formula", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "=(+)"); setCellContent(model, "A2", "=C1{C2"); cut(model, "A1:A2"); paste(model, "C1"); expect(getCellText(model, "C1")).toBe("=(+)"); expect(getCellText(model, "C2")).toBe("=C1{C2"); expect(getCellText(model, "A1")).toBe(""); expect(getCellText(model, "A2")).toBe(""); }); test("cut/paste a formula with references does not update references in the formula", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "=SUM(C1:C2)"); cut(model, "A1"); paste(model, "B2"); expect(getCellText(model, "B2")).toBe("=SUM(C1:C2)"); }); test("cut/paste a formula with references in another sheet updates the sheet references in the formula", () => { ({ model, store } = makeStore(ClipboardStore)); createSheet(model, { sheetId: "sh2", name: "Sheet2" }); setCellContent(model, "A1", "=SUM(C1:C2)"); setCellContent(model, "B1", "=Sheet2!A1 + A2"); cut(model, "A1:B1"); activateSheet(model, "sh2"); paste(model, "A1"); expect(getCellText(model, "A1")).toBe("=SUM(Sheet1!C1:C2)"); expect(getCellText(model, "B1")).toBe("=A1+Sheet1!A2"); }); test("copy/paste a zone present in formulas references does not update references", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "=B2"); copy(model, "B2"); paste(model, "C3"); expect(getCellText(model, "A1")).toBe("=B2"); }); describe("cut/paste a zone present in formulas references", () => { test("update references", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "=B2"); cut(model, "B2"); paste(model, "C3"); expect(getCellText(model, "A1")).toBe("=C3"); }); test("update references to a range", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "=SUM(B2:C3)"); cut(model, "B2:C3"); paste(model, "D4"); expect(getCellText(model, "A1")).toBe("=SUM(D4:E5)"); }); test("update fixed references", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "=$B$2"); cut(model, "B2"); paste(model, "C3"); expect(getCellText(model, "A1")).toBe("=$C$3"); }); test("update cross-sheet reference", () => { ({ model, store } = makeStore(ClipboardStore)); createSheet(model, { sheetId: "Sheet2" }); setCellContent(model, "A1", "=Sheet2!$B$2"); activateSheet(model, "Sheet2"); cut(model, "B2"); createSheet(model, { activate: true, sheetId: "Sheet3" }); paste(model, "C3"); activateSheet(model, "Sheet1"); expect(getCellText(model, "A1")).toBe("=Sheet3!$C$3"); }); test("update references even if the formula is present in the cutting zone", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "=B1"); setCellContent(model, "B1", "b1"); cut(model, "A1:B1"); paste(model, "A2"); expect(getCellText(model, "A1")).toBe(""); expect(getCellText(model, "B1")).toBe(""); expect(getCellText(model, "A2")).toBe("=B2"); expect(getCellText(model, "B2")).toBe("b1"); }); test("does not update reference if it isn't fully included in the zone", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "=SUM(B1:C1)+B1"); cut(model, "B1"); paste(model, "B2"); expect(getCellText(model, "A1")).toBe("=SUM(B1:C1)+B2"); }); test("does not update reference if it isn't fully included in the zone even if the formula is present in the cutting zone", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "=SUM(B1:C1)+B1"); setCellContent(model, "B1", "b1"); cut(model, "A1:B1"); paste(model, "A2"); expect(getCellText(model, "A1")).toBe(""); expect(getCellText(model, "B1")).toBe(""); expect(getCellText(model, "A2")).toBe("=SUM(B1:C1)+B2"); expect(getCellText(model, "B2")).toBe("b1"); }); }); test.each([ ["=SUM(1:2)", "=SUM(2:3)"], ["=$C1:1", "=$C2:2"], ["=SUM($A:D$2)", "=SUM($A$2:E)"], ])("can copy and paste formula with full cols/rows", (value, expected) => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", value); copy(model, "A1"); paste(model, "B2"); expect(getCellText(model, "B2")).toBe(expected); }); test("can copy format from empty cell to another cell to clear format", () => { ({ model, store } = makeStore(ClipboardStore)); // write something in B2 and set its format setCellContent(model, "B2", "b2"); selectCell(model, "B2"); setFormatting(model, "B2", { bold: true }); expect(getCell(model, "B2")!.style).toEqual({ bold: true }); // select A1 and copy format copy(model, "A1"); // select B2 and paste format paste(model, "B2", "onlyFormat"); expect(getCellContent(model, "B2")).toBe("b2"); expect(getCell(model, "B2")!.style).not.toBeDefined(); }); test("can copy and paste a conditional formatted cell", () => { model = new Model({ sheets: [{ colNumber: 5, rowNumber: 5 }] }); ({ store } = makeStoreWithModel(model, ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); setCellContent(model, "C1", "1"); setCellContent(model, "C2", "2"); addEqualCf(model, "A1,A2", { fillColor: "#FF0000" }, "1"); copy(model, "A1"); paste(model, "C1"); copy(model, "A2"); paste(model, "C2"); expect(getStyle(model, "A1")).toEqual({ fillColor: "#FF0000", }); expect(getStyle(model, "A2")).toEqual({}); expect(getStyle(model, "C1")).toEqual({ fillColor: "#FF0000", }); expect(getStyle(model, "C2")).toEqual({}); }); test("can cut and paste a conditional formatted cell", () => { model = new Model({ sheets: [{ colNumber: 5, rowNumber: 5 }] }); ({ store } = makeStoreWithModel(model, ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); setCellContent(model, "C1", "1"); setCellContent(model, "C2", "2"); addEqualCf(model, "A1,A2", { fillColor: "#FF0000" }, "1"); cut(model, "A1"); paste(model, "C1"); cut(model, "A2"); paste(model, "C2"); expect(getStyle(model, "A1")).toEqual({}); expect(getStyle(model, "A2")).toEqual({}); expect(getStyle(model, "C1")).toEqual({ fillColor: "#FF0000", }); expect(getStyle(model, "C2")).toEqual({}); }); test("can cut and paste a conditional format in another sheet", () => { ({ model, store } = makeStore(ClipboardStore)); const sheet1Id = model.getters.getActiveSheetId(); createSheet(model, { sheetId: "sheet2Id" }); addEqualCf(model, "A1:A2", { fillColor: "#FF0000" }, "1"); cut(model, "A1:A2"); activateSheet(model, "sheet2Id"); paste(model, "C1"); expect(model.getters.getConditionalFormats(sheet1Id)).toEqual([]); expect(model.getters.getConditionalFormats("sheet2Id")).toMatchObject([ { ranges: ["C1:C2"], rule: { type: "CellIsRule", style: { fillColor: "#FF0000" } } }, ]); }); test("copy cells with CF => remove origin CF => paste => it should paste with original CF", () => { ({ model, store } = makeStore(ClipboardStore)); addEqualCf(model, "A1:A3", { fillColor: "#00FF00" }, "1", "cfId"); copy(model, "A1:A3"); removeCF(model, "cfId"); paste(model, "D1"); expect(model.getters.getConditionalFormats(model.getters.getActiveSheetId())).toMatchObject([ { ranges: ["D1:D3"], rule: { style: { fillColor: "#00FF00" } } }, ]); }); test("copy cells with multiple independent CF => remove all copied CF => paste => it should paste with all original CF in the correct positions", () => { ({ model, store } = makeStore(ClipboardStore)); const sheetId = model.getters.getActiveSheetId(); addEqualCf(model, "A1:A3", { fillColor: "#00FF00" }, "1", "cf1"); addEqualCf(model, "C1:C3", { fillColor: "#0000FF" }, "1", "cf2"); copy(model, "A1:C3"); removeCF(model, "cf1"); removeCF(model, "cf2"); paste(model, "E1"); expect(model.getters.getConditionalFormats(sheetId)).toMatchObject([ { ranges: ["E1:E3"], rule: { style: { fillColor: "#00FF00" } } }, { ranges: ["G1:G3"], rule: { style: { fillColor: "#0000FF" } } }, ]); }); test("copy cells with multiple independent CF => remove origin sheet => paste => it should paste with all original CF in the correct positions", () => { ({ model, store } = makeStore(ClipboardStore)); const sheetId = model.getters.getActiveSheetId(); addEqualCf(model, "A1:A3", { fillColor: "#00FF00" }, "1", "cf1"); addEqualCf(model, "C1:C3", { fillColor: "#0000FF" }, "1", "cf2"); copy(model, "A1:C3"); const newSheetId = "Sheet2"; createSheet(model, { sheetId: newSheetId, activate: true }); deleteSheet(model, sheetId); paste(model, "E1"); expect(model.getters.getConditionalFormats(newSheetId)).toMatchObject([ { ranges: ["E1:E3"], rule: { style: { fillColor: "#00FF00" } } }, { ranges: ["G1:G3"], rule: { style: { fillColor: "#0000FF" } } }, ]); }); test("can copy and paste a conditional formatted zone", () => { model = new Model({ sheets: [{ colNumber: 5, rowNumber: 5 }] }); ({ store } = makeStoreWithModel(model, ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); addEqualCf(model, "A1,A2", { fillColor: "#FF0000" }, "1"); copy(model, "A1:A2"); paste(model, "B1"); paste(model, "C1"); expect(getStyle(model, "A1")).toEqual({ fillColor: "#FF0000", }); expect(getStyle(model, "A2")).toEqual({}); expect(getStyle(model, "B1")).toEqual({ fillColor: "#FF0000", }); expect(getStyle(model, "B2")).toEqual({}); expect(getStyle(model, "C1")).toEqual({ fillColor: "#FF0000", }); expect(getStyle(model, "C2")).toEqual({}); setCellContent(model, "C1", "2"); setCellContent(model, "C2", "1"); expect(getStyle(model, "C1")).toEqual({}); expect(getStyle(model, "C2")).toEqual({ fillColor: "#FF0000", }); }); test("can cut and paste a conditional formatted zone", () => { model = new Model({ sheets: [{ colNumber: 5, rowNumber: 5 }] }); ({ store } = makeStoreWithModel(model, ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); addEqualCf(model, "A1,A2", { fillColor: "#FF0000" }, "1"); cut(model, "A1:A2"); paste(model, "B1"); expect(getStyle(model, "A1")).toEqual({}); expect(getStyle(model, "A2")).toEqual({}); expect(getStyle(model, "B1")).toEqual({ fillColor: "#FF0000", }); expect(getStyle(model, "B2")).toEqual({}); setCellContent(model, "B1", "2"); setCellContent(model, "B2", "1"); expect(getStyle(model, "B1")).toEqual({}); expect(getStyle(model, "B2")).toEqual({ fillColor: "#FF0000", }); }); test("can copy and paste a conditional formatted cell to another page", () => { model = new Model({ sheets: [ { id: "s1", colNumber: 5, rowNumber: 5 }, { id: "s2", colNumber: 5, rowNumber: 5 }, ], }); ({ store } = makeStoreWithModel(model, ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); addEqualCf(model, "A1,A2", { fillColor: "#FF0000" }, "1"); copy(model, "A1:A2"); activateSheet(model, "s2"); paste(model, "A1"); expect(getStyle(model, "A1", "s2")).toEqual({ fillColor: "#FF0000", }); expect(getStyle(model, "A2", "s2")).toEqual({}); setCellContent(model, "A1", "2"); setCellContent(model, "A2", "1"); expect(getStyle(model, "A1", "s2")).toEqual({}); expect(getStyle(model, "A2", "s2")).toEqual({ fillColor: "#FF0000", }); }); test("can cut and paste a conditional formatted zone to another page", () => { model = new Model({ sheets: [{ id: "sheet1" }, { id: "sheet2" }] }); ({ store } = makeStoreWithModel(model, ClipboardStore)); addEqualCf(model, "A1:A2", { fillColor: "#FF0000" }, "1"); cut(model, "A1:A2"); activateSheet(model, "sheet2"); paste(model, "A1"); expect(model.getters.getConditionalFormats("sheet2")).toMatchObject([ { ranges: ["A1:A2"], rule: { style: { fillColor: "#FF0000" } } }, ]); expect(model.getters.getConditionalFormats("sheet1")).toEqual([]); }); test("copy paste CF in another sheet => change CF => copy paste again does not overwrite the previously pasted CF", () => { ({ model, store } = makeStore(ClipboardStore)); createSheet(model, {}); const sheet1Id = model.getters.getSheetIds()[0]; const sheet2Id = model.getters.getSheetIds()[1]; addEqualCf(model, "A1", { fillColor: "#00FF00" }, "2", "cfId"); copy(model, "A1"); activateSheet(model, sheet2Id); paste(model, "A1"); expect(model.getters.getConditionalFormats(sheet2Id)).toMatchObject([ { ranges: ["A1"], rule: { style: { fillColor: "#00FF00" } } }, ]); addEqualCf(model, "A1", { fillColor: "#FF0000" }, "2", "cfId", sheet1Id); activateSheet(model, sheet1Id); copy(model, "A1"); activateSheet(model, sheet2Id); paste(model, "B2"); expect(model.getters.getConditionalFormats(sheet2Id)).toMatchObject([ { ranges: ["A1"], rule: { style: { fillColor: "#00FF00" } } }, { ranges: ["B2"], rule: { style: { fillColor: "#FF0000" } } }, ]); }); test("copy/paste a CF zone only dispatch a singled ADD_CONDITIONAL_FORMAT", () => { const commands: Command[] = []; class MyUIPlugin extends UIPlugin { handle = (cmd: Command) => commands.push(cmd); } addTestPlugin(featurePluginRegistry, MyUIPlugin); model = new Model({ sheets: [{ colNumber: 5, rowNumber: 5 }] }); ({ store } = makeStoreWithModel(model, ClipboardStore)); const sheetId = model.getters.getActiveSheetId(); addEqualCf(model, "A1,A2", { fillColor: "#FF0000" }, "1"); copy(model, "A1:A2"); paste(model, "B1"); expect(model.getters.getConditionalFormats(sheetId)).toMatchObject([{ ranges: ["A1:B2"] }]); expect(commands.filter((c) => c.type === "ADD_CONDITIONAL_FORMAT")).toHaveLength(2); }); test("can copy and paste a cell which contains a cross-sheet reference", () => { ({ model, store } = makeStore(ClipboardStore)); createSheet(model, { sheetId: "42" }); setCellContent(model, "B2", "=Sheet2!B2"); copy(model, "B2"); paste(model, "B3"); expect(getCellText(model, "B3")).toBe("=Sheet2!B3"); }); test("can copy and paste a cell which contains a cross-sheet reference with a space in the name", () => { ({ model, store } = makeStore(ClipboardStore)); createSheetWithName(model, { sheetId: "42" }, "Sheet 2"); setCellContent(model, "B2", "='Sheet 2'!B2"); copy(model, "B2"); paste(model, "B3"); expect(getCellText(model, "B3")).toBe("='Sheet 2'!B3"); }); test("can copy and paste a cell which contains a cross-sheet reference in a smaller sheet", () => { ({ model, store } = makeStore(ClipboardStore)); createSheet(model, { sheetId: "42", rows: 2, cols: 2 }); setCellContent(model, "A1", "=Sheet2!A1:A2"); copy(model, "A1"); paste(model, "A2"); expect(getCellText(model, "A2")).toBe("=Sheet2!A2:A3"); }); test("can copy and paste a cell which contains a cross-sheet reference to a range", () => { ({ model, store } = makeStore(ClipboardStore)); createSheet(model, { sheetId: "42" }); setCellContent(model, "A1", "=SUM(Sheet2!A2:A5)"); copy(model, "A1"); paste(model, "B1"); expect(getCellText(model, "B1")).toBe("=SUM(Sheet2!B2:B5)"); }); test.each([ ["=A1", "=#REF"], ["=SUM(A1:B1)", "=SUM(#REF)"], ])("Copy invalid ranges due to row deletion", (initialFormula, expectedInvalidFormula) => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A3", initialFormula); deleteRows(model, [0]); expect(getCellRawContent(model, "A2")).toBe(expectedInvalidFormula); copy(model, "A2"); paste(model, "C5"); expect(getCellRawContent(model, "C5")).toBe(expectedInvalidFormula); }); test.each([ ["=A1", "=#REF"], ["=SUM(A1:A2)", "=SUM(#REF)"], ])("Copy invalid ranges due to column deletion", (initialFormula, expectedInvalidFormula) => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "C1", initialFormula); deleteColumns(model, ["A"]); expect(getCellRawContent(model, "B1")).toBe(expectedInvalidFormula); copy(model, "B1"); paste(model, "C3"); expect(getCellRawContent(model, "C3")).toBe(expectedInvalidFormula); }); test.each([ ["=A1", "=#REF"], ["=SUM(A1:B1)", "=SUM(#REF)"], ])("Cut invalid ranges due to row deletion", (initialFormula, expectedInvalidFormula) => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A3", initialFormula); deleteRows(model, [0]); expect(getCellRawContent(model, "A2")).toBe(expectedInvalidFormula); cut(model, "A2"); paste(model, "C5"); expect(getCellRawContent(model, "C5")).toBe(expectedInvalidFormula); }); test.each([ ["=A1", "=#REF"], ["=SUM(A1:A2)", "=SUM(#REF)"], ])("Cut invalid ranges due to column deletion", (initialFormula, expectedInvalidFormula) => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "C1", initialFormula); deleteColumns(model, ["A"]); expect(getCellRawContent(model, "B1")).toBe(expectedInvalidFormula); cut(model, "B1"); paste(model, "C3"); expect(getCellRawContent(model, "C3")).toBe(expectedInvalidFormula); }); test("filtered rows are ignored when copying range", () => { //prettier-ignore model = createModelFromGrid({ B2: "b2", C2: "c2", D2: "d2", B3: "b3", C3: "c3", D3: "d3", B4: "b4", C4: "c4", D4: "d4", }); ({ store } = makeStoreWithModel(model, ClipboardStore)); createTableWithFilter(model, "B2:D4"); updateFilter(model, "C3", ["c3"]); copy(model, "B2:D4"); paste(model, "B5"); expect(getEvaluatedGrid(model, "B5:D7")).toEqual([ ["b2", "c2", "d2"], ["b4", "c4", "d4"], ["", "", ""], ]); }); test("filtered rows are ignored when cutting range", () => { //prettier-ignore model = createModelFromGrid({ B2: "b2", C2: "c2", D2: "d2", B3: "b3", C3: "c3", D3: "d3", B4: "b4", C4: "c4", D4: "d4", }); ({ store } = makeStoreWithModel(model, ClipboardStore)); createTableWithFilter(model, "B2:D4"); updateFilter(model, "C3", ["c3"]); cut(model, "B2:D4"); paste(model, "B5"); expect(getEvaluatedGrid(model, "B5:D7")).toEqual([ ["b2", "c2", "d2"], ["b3", "c3", "d3"], ["b4", "c4", "d4"], ]); }); test("hidden rows/columns are taken into account when copypasting range", () => { //prettier-ignore model = createModelFromGrid({ B2: "b2", C2: "c2", D2: "d2", B3: "b3", C3: "c3", D3: "d3", B4: "b4", C4: "c4", D4: "d4", }); ({ store } = makeStoreWithModel(model, ClipboardStore)); hideRows(model, [2]); hideColumns(model, ["C"]); copy(model, "B2:D4"); paste(model, "E1"); expect(getEvaluatedGrid(model, "E1:G3")).toEqual([ ["b2", "c2", "d2"], ["b3", "c3", "d3"], ["b4", "c4", "d4"], ]); }); test("hidden rows/columns are taken into account when cutpasting range", () => { //prettier-ignore model = createModelFromGrid({ B2: "b2", C2: "c2", D2: "d2", B3: "b3", C3: "c3", D3: "d3", B4: "b4", C4: "c4", D4: "d4", }); ({ store } = makeStoreWithModel(model, ClipboardStore)); hideRows(model, [2]); hideColumns(model, ["C"]); cut(model, "B2:D4"); paste(model, "E1"); expect(getEvaluatedGrid(model, "E1:G3")).toEqual([ ["b2", "c2", "d2"], ["b3", "c3", "d3"], ["b4", "c4", "d4"], ]); }); test("copying a spread pivot cell results in the fixed pivot formula", () => { // prettier-ignore const grid = { A1: "Customer", B1: "Price", C1: "=PIVOT(1)", A2: "Alice", B2: "10", A3: "Bob", B3: "30" }; ({ store, model } = makeStoreWithModel(createModelFromGrid(grid), ClipboardStore)); addPivot(model, "A1:B3", { columns: [], rows: [{ fieldName: "Customer" }], measures: [{ id: "Price:sum", fieldName: "Price", aggregator: "sum" }], }); // copy 1 cell copy(model, "D1"); // copy the header Total paste(model, "G4"); expect(getEvaluatedCell(model, "G4").value).toBe("Total"); expect(getCellRawContent(model, "G4")).toBe("=PIVOT.HEADER(1)"); // copy part of pivot copy(model, "C1:D4"); paste(model, "G4"); setFormulaVisibility(model, true); // prettier-ignore expect(getEvaluatedGrid(model, "G4:H7")).toEqual([ ["", "=PIVOT.HEADER(1)"], ["", '=PIVOT.HEADER(1,"measure","Price:sum")'], ['=PIVOT.HEADER(1,"Customer","Alice")', '=PIVOT.VALUE(1,"Price:sum","Customer","Alice")'], ['=PIVOT.HEADER(1,"Customer","Bob")', '=PIVOT.VALUE(1,"Price:sum","Customer","Bob")'], ]); }); test("Copying (or cutting) entire pivot does not results in fixed pivot formula", () => { // prettier-ignore const grid = { A1: "Customer", B1: "Price", C1: "=PIVOT(1)", A2: "Alice", B2: "10", A3: "Bob", B3: "30" }; ({ store, model } = makeStoreWithModel(createModelFromGrid(grid), ClipboardStore)); addPivot(model, "A1:B3", { columns: [], rows: [{ fieldName: "Customer" }], measures: [{ id: "Price:sum", fieldName: "Price", aggregator: "sum" }], }); copy(model, "C1:D5"); paste(model, "G4"); expect(getCellRawContent(model, "G4")).toBe("=PIVOT(1)"); expect(getCell(model, "G5")).toBeUndefined(); cut(model, "C1:D5"); paste(model, "G20"); expect(getCellRawContent(model, "G20")).toBe("=PIVOT(1)"); expect(getCell(model, "G21")).toBeUndefined(); }); test("copy spread pivot cells format", () => { // prettier-ignore const grid = { A1: "Customer", B1: "Price", C1: "=PIVOT(1)", A2: "Alice", B2: "10", A3: "Bob", B3: "30" }; const { model } = makeStoreWithModel(createModelFromGrid(grid), ClipboardStore); setFormat(model, "B2:B3", "#,##0[$$]"); addPivot(model, "A1:B3", { columns: [], rows: [{ fieldName: "Customer" }], measures: [{ id: "Price:sum", fieldName: "Price", aggregator: "sum" }], }); setFormat(model, "D5", "#,##0.0"); copy(model, "D4:D5"); paste(model, "G4"); // automatic format on G4 expect(getCellRawContent(model, "G4")).toBe('=PIVOT.VALUE(1,"Price:sum","Customer","Bob")'); expect(getCell(model, "G4")?.format).toBeUndefined(); expect(getEvaluatedCell(model, "G4").format).toBe("#,##0[$$]"); // forced format copied from D5 expect(getCellRawContent(model, "G5")).toBe('=PIVOT.VALUE(1,"Price:sum")'); expect(getCell(model, "G5")?.format).toBe("#,##0.0"); expect(getEvaluatedCell(model, "G5").format).toBe("#,##0.0"); }); test("copy spread pivot from a referenced id", () => { // prettier-ignore const grid = { A1: "Customer", B1: "Price", C1: "1", A2: "Alice", B2: "10", C2: "=PIVOT(C1)", A3: "Bob", B3: "30" }; ({ store, model } = makeStoreWithModel(createModelFromGrid(grid), ClipboardStore)); addPivot(model, "A1:B3", { columns: [], rows: [{ fieldName: "Customer" }], measures: [{ id: "Price:sum", fieldName: "Price", aggregator: "sum" }], }); copy(model, "D4"); paste(model, "G4"); expect(getCellRawContent(model, "G4")).toBe('=PIVOT.VALUE(1,"Price:sum","Customer","Alice")'); }); test("copying a spread pivot cell with (Undefined)", () => { // prettier-ignore const grid = { A1: "Customer", B1: "Price", C1: "=PIVOT(1)", A2: "Alice", B2: "10", A3: "", B3: "20" }; ({ store, model } = makeStoreWithModel(createModelFromGrid(grid), ClipboardStore)); addPivot(model, "A1:B3", { columns: [], rows: [{ fieldName: "Customer" }], measures: [{ fieldName: "Price", aggregator: "sum", id: "Price:sum" }], }); copy(model, "C1:D4"); paste(model, "G4"); setFormulaVisibility(model, true); // prettier-ignore expect(getEvaluatedGrid(model, "G4:H7")).toEqual([ ["", "=PIVOT.HEADER(1)"], ["", '=PIVOT.HEADER(1,"measure","Price:sum")'], ['=PIVOT.HEADER(1,"Customer","Alice")', '=PIVOT.VALUE(1,"Price:sum","Customer","Alice")'], ['=PIVOT.HEADER(1,"Customer","null")', '=PIVOT.VALUE(1,"Price:sum","Customer","null")'], ]); }); test("copying only the cell with a spread pivot formula doesn't fix the pivot", () => { // prettier-ignore const grid = { A1: "Customer", B1: "Price", C1: "=PIVOT(1)", A2: "Alice", B2: "10", A3: "Bob", B3: "30" }; ({ store, model } = makeStoreWithModel(createModelFromGrid(grid), ClipboardStore)); addPivot(model, "A1:B3", { columns: [], rows: [{ fieldName: "Customer" }], measures: [{ id: "Price:sum", fieldName: "Price", aggregator: "sum" }], }); copy(model, "C1"); paste(model, "G4"); expect(getCellRawContent(model, "G4")).toBe("=PIVOT(1)"); }); test("fixed pivot formulas are copied like standard cells", () => { // prettier-ignore const grid = { A1: "Customer", B1: "Price", C1: "1", A2: "Alice", B2: "10", C2: '=PIVOT.VALUE(C1,"Price","Customer","Bob")', A3: "Bob", B3: "30" }; ({ store, model } = makeStoreWithModel(createModelFromGrid(grid), ClipboardStore)); addPivot(model, "A1:B3", { columns: [], rows: [{ fieldName: "Customer" }], measures: [{ id: "Price:sum", fieldName: "Price", aggregator: "sum" }], }); copy(model, "C2"); paste(model, "G4"); expect(getCellRawContent(model, "G4")).toBe('=PIVOT.VALUE(G3,"Price","Customer","Bob")'); }); }); describe("clipboard: pasting outside of sheet", () => { test("can copy and paste a full column", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "txt"); const activeSheetId = model.getters.getActiveSheetId(); const currentRowNumber = model.getters.getNumberRows(activeSheetId); copy(model, zoneToXc(model.getters.getColsZone(activeSheetId, 0, 0))); paste(model, "B2"); expect(model.getters.getNumberRows(activeSheetId)).toBe(currentRowNumber + 1); expect(getCellContent(model, "B2")).toBe("txt"); expect(model.getters.getSelectedZones()).toEqual([toZone("B2:B101")]); }); test("can copy and paste a full row", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "txt"); const activeSheetId = model.getters.getActiveSheetId(); const currentColNumber = model.getters.getNumberCols(activeSheetId); copy(model, zoneToXc(model.getters.getRowsZone(activeSheetId, 0, 0))); paste(model, "B2"); expect(model.getters.getNumberCols(activeSheetId)).toBe(currentColNumber + 1); expect(getCellContent(model, "B2")).toBe("txt"); expect(model.getters.getSelectedZones()).toEqual([toZone("B2:AA2")]); }); test("fill down on cell(s) of edge row should do nothing", async () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B1", "b1"); selectCell(model, "B1"); copyPasteAboveCells(model); expect(getCellContent(model, "B1")).toBe("b1"); setCellContent(model, "C1", "c1"); setSelection(model, ["B1:C1"]); copyPasteAboveCells(model); expect(getCellContent(model, "B1")).toBe("b1"); expect(getCellContent(model, "C1")).toBe("c1"); }); test("fill right on cell(s) of edge column should do nothing", async () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A2", "a2"); selectCell(model, "A2"); copyPasteCellsOnLeft(model); expect(getCellContent(model, "A2")).toBe("a2"); setCellContent(model, "A3", "a3"); setSelection(model, ["A2:A3"]); copyPasteCellsOnLeft(model); expect(getCellContent(model, "A2")).toBe("a2"); expect(getCellContent(model, "A3")).toBe("a3"); }); test("fill down selection with single row -> for each cell, replicates the cell above it", async () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "b2"); selectCell(model, "B2"); copyPasteAboveCells(model); expect(getCell(model, "B2")).toBe(undefined); setCellContent(model, "B1", "b1"); setFormatting(model, "B1", { bold: true, fillColor: "red" }); setCellContent(model, "B2", "b2"); selectCell(model, "B2"); copyPasteAboveCells(model); expect(getCellContent(model, "B2")).toBe("b1"); expect(getStyle(model, "B2")).toEqual({ bold: true, fillColor: "red" }); setCellContent(model, "C1", "c1"); setCellContent(model, "D1", "d1"); setSelection(model, ["B2:D2"]); copyPasteAboveCells(model); expect(getCellContent(model, "B2")).toBe("b1"); expect(getStyle(model, "B2")).toEqual({ bold: true, fillColor: "red" }); expect(getCellContent(model, "C2")).toBe("c1"); expect(getCellContent(model, "D2")).toBe("d1"); }); test("do not fill down if filling down would unmerge cells", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "a1"); merge(model, "A2:A3"); setSelection(model, ["A1:A3"]); const result = store.isCommandValid({ type: "COPY_PASTE_CELLS_ABOVE" }); expect(result).toBeCancelledBecause(CommandResult.WillRemoveExistingMerge); }); test("do not fill right if filling right would unmerge cells", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "a1"); merge(model, "B1:C1"); setSelection(model, ["A1:C1"]); const result = store.isCommandValid({ type: "COPY_PASTE_CELLS_ON_LEFT" }); expect(result).toBeCancelledBecause(CommandResult.WillRemoveExistingMerge); }); test("do not fill if filling would unmerge cells", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "a1"); merge(model, "A2:A3"); setSelection(model, ["A1:A3"]); const result = store.isCommandValid({ type: "COPY_PASTE_CELLS_ON_ZONE" }); expect(result).toBeCancelledBecause(CommandResult.WillRemoveExistingMerge); }); test("fill right selection with single column -> for each cell, replicates the cell on its left", async () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B1", "b1"); selectCell(model, "B1"); copyPasteCellsOnLeft(model); expect(getCell(model, "B1")).toBe(undefined); setCellContent(model, "A1", "a1"); setFormatting(model, "A1", { bold: true, fillColor: "red" }); setCellContent(model, "B1", "b1"); selectCell(model, "B1"); copyPasteCellsOnLeft(model); expect(getCellContent(model, "B1")).toBe("a1"); expect(getStyle(model, "B1")).toEqual({ bold: true, fillColor: "red" }); setCellContent(model, "A2", "a2"); setCellContent(model, "A3", "a3"); setSelection(model, ["B1:B3"]); copyPasteCellsOnLeft(model); expect(getCellContent(model, "B1")).toBe("a1"); expect(getStyle(model, "B1")).toEqual({ bold: true, fillColor: "red" }); expect(getCellContent(model, "B2")).toBe("a2"); expect(getCellContent(model, "B3")).toBe("a3"); }); test("fill down selection with multiple rows -> copies first row and pastes in each subsequent row", async () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B3", "b3"); setSelection(model, ["B2:B3"]); copyPasteAboveCells(model); expect(getCell(model, "B2")).toBe(undefined); expect(getCell(model, "B3")).toBe(undefined); setCellContent(model, "B1", "b1"); setCellContent(model, "B2", "b2"); setFormatting(model, "B2", { bold: true, fillColor: "red" }); setCellContent(model, "C2", "c2"); setSelection(model, ["B2:C3"]); copyPasteAboveCells(model); expect(getCellContent(model, "B2")).toBe("b2"); expect(getStyle(model, "B2")).toEqual({ bold: true, fillColor: "red" }); expect(getCellContent(model, "B3")).toBe("b2"); expect(getStyle(model, "B3")).toEqual({ bold: true, fillColor: "red" }); expect(getCellContent(model, "C2")).toBe("c2"); expect(getCellContent(model, "C3")).toBe("c2"); }); test("CopyPasteAboveCell and copyPasteCellsOnLeft do not change the clipboard state", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B3", "b3"); cut(model, "B3"); setSelection(model, ["A1:B2"]); copyPasteAboveCells(model); expect(store.isCutOperation()).toBe(true); paste(model, "A2"); expect(getCellContent(model, "A2")).toBe("b3"); setCellContent(model, "B3", "b3"); cut(model, "B3"); setSelection(model, ["A1:B2"]); copyPasteCellsOnLeft(model); expect(store.isCutOperation()).toBe(true); paste(model, "A2"); expect(getCellContent(model, "A2")).toBe("b3"); }); test("Delete Cell and Insert Cell do not invalidate the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B3", "b3"); copy(model, "B3"); deleteCells(model, "A1", "up"); expect(store.isCutOperation()).toBe(false); paste(model, "A2"); expect(getCellContent(model, "A2")).toBe("b3"); insertCells(model, "A1", "down"); expect(store.isCutOperation()).toBe(false); paste(model, "A5"); expect(getCellContent(model, "A5")).toBe("b3"); }); test("Can insert and delete cells inside an array formula", () => { model = createModelFromGrid({ A1: "=MUNIT(2)" }); createDynamicTable(model, "A1"); insertCells(model, "B1", "down"); expect(getCellRawContent(model, "A1")).toBe("=MUNIT(2)"); expect(getCellContent(model, "A1")).toBe("1"); expect(getCell(model, "B2")).toBe(undefined); deleteCells(model, "A2", "left"); expect(getCellRawContent(model, "A1")).toBe("=MUNIT(2)"); expect(getCellContent(model, "A1")).toBe("1"); expect(getCell(model, "A2")).toBe(undefined); }); test("fill right selection with multiple columns -> copies first column and pastes in each subsequent column, ", async () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "C1", "c1"); setSelection(model, ["B1:C1"]); copyPasteCellsOnLeft(model); expect(getCell(model, "B1")).toBe(undefined); expect(getCell(model, "C1")).toBe(undefined); setCellContent(model, "A1", "a1"); setCellContent(model, "B2", "b2"); setCellContent(model, "B1", "b1"); setFormatting(model, "B1", { bold: true, fillColor: "red" }); setSelection(model, ["B1:C2"]); copyPasteCellsOnLeft(model); expect(getCellContent(model, "B1")).toBe("b1"); expect(getStyle(model, "B1")).toEqual({ bold: true, fillColor: "red" }); expect(getCellContent(model, "B2")).toBe("b2"); expect(getCellContent(model, "C1")).toBe("b1"); expect(getStyle(model, "C1")).toEqual({ bold: true, fillColor: "red" }); expect(getCellContent(model, "C2")).toBe("b2"); }); test("Copy a formula which lead to #REF", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B3", "=A1"); copy(model, "B3"); paste(model, "B2"); expect(getCellContent(model, "B2", "#BAD_EXPR")); expect(getCellError(model, "B2")).toEqual("Invalid reference"); }); test("Can cut & paste a formula", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "=1"); cut(model, "A1"); paste(model, "B1"); expect(getCellContent(model, "A1")).toBe(""); expect(getCellText(model, "B1")).toBe("=1"); }); test("Cut & paste a formula update offsets only if the range is in the zone", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B1", "2"); setCellContent(model, "B2", "=B1"); setCellContent(model, "B3", "=B2"); cut(model, "B2:B3"); paste(model, "C2"); expect(getCellText(model, "C2")).toBe("=B1"); expect(getCellText(model, "C3")).toBe("=C2"); }); test("can paste multiple cells from os to outside of sheet", () => { ({ model, store } = makeStore(ClipboardStore)); createSheet(model, { activate: true, sheetId: "2", rows: 2, cols: 2 }); pasteFromOSClipboard(model, "B2", { text: "A\nque\tcoucou\nBOB" }); expect(getCellContent(model, "B2")).toBe("A"); expect(getCellContent(model, "B3")).toBe("que"); expect(getCellContent(model, "C3")).toBe("coucou"); expect(getCellContent(model, "B4")).toBe("BOB"); createSheet(model, { activate: true, sheetId: "3", rows: 2, cols: 2, }); pasteFromOSClipboard(model, "B2", { text: "A\nque\tcoucou\tPatrick" }); expect(getCellContent(model, "B2")).toBe("A"); expect(getCellContent(model, "B3")).toBe("que"); expect(getCellContent(model, "C3")).toBe("coucou"); expect(getCellContent(model, "D3")).toBe("Patrick"); }); test("Can paste localized formula from the OS", () => { ({ model, store } = makeStore(ClipboardStore)); updateLocale(model, { ...DEFAULT_LOCALE, decimalSeparator: ",", formulaArgSeparator: ";", thousandsSeparator: " ", }); pasteFromOSClipboard(model, "A1", { text: "=SUM(5 ; 3,14)" }); expect(getCellRawContent(model, "A1")).toBe("=SUM(5,3.14)"); expect(getEvaluatedCell(model, "A1").value).toBe(8.14); }); test("Pasted images from OS are inserted at the paste position with a limited size", () => { ({ model, store, container } = makeStore(ClipboardStore)); const viewStore = container.get(ViewportsStore); const width = 2000; const height = 2000; pasteFromOSClipboard(model, "B2", { imageData: { path: "data:image/png;base64,", size: { width, height }, }, }); const sheetId = model.getters.getActiveSheetId(); expect(getCellContent(model, "B2")).toBe(""); const figures = model.getters.getFigures(sheetId); expect(figures).toHaveLength(1); const sheetViewDimension = viewStore.sheetViewDimension; expect(figures[0]).toMatchObject({ tag: "image", width: sheetViewDimension.width, height: sheetViewDimension.height, col: 1, row: 1, }); }); test("Copying an imge too big in the clipboard notifies the user", async () => { class FileStore extends MockFileStore { async getFile(fileUrl) { return new File(["x".repeat(MAX_FILE_SIZE + 1)], "mock", { type: "image/jpeg" }); } } model = new Model({}, { external: { fileStore: new FileStore() } }); const { store, container } = makeStoreWithModel(model, ClipboardStore); const spyNotifyUI = jest.spyOn(container.get(NotificationStore), "notifyUser"); createImage(model, { figureId: "test" }); selectFigure(model, "test"); copy(model); await store.getClipboardTextAndImageContent(); expect(spyNotifyUI).toHaveBeenCalledWith({ sticky: false, text: "The file you are trying to copy is too large (>5MB).\nIt will not be added to your OS clipboard.\nYou can download it directly instead.", type: "warning", }); }); test("Can copy parts of the spread values", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); setCellContent(model, "A3", "3"); setCellContent(model, "B1", "=TRANSPOSE(A1:A3)"); copy(model, "C1:D1"); paste(model, "C2"); expect(getEvaluatedCell(model, "C2").value).toBe(2); expect(getEvaluatedCell(model, "D2").value).toBe(3); }); test("Cutting parts of the spread values will make a copy of the values", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); setCellContent(model, "A3", "3"); setCellContent(model, "B1", "=TRANSPOSE(A1:A3)"); cut(model, "C1:D1"); paste(model, "C2"); expect(getEvaluatedCell(model, "B1").value).toBe(1); expect(getEvaluatedCell(model, "C1").value).toBe(2); expect(getEvaluatedCell(model, "C2").value).toBe(2); expect(getEvaluatedCell(model, "D1").value).toBe(3); expect(getEvaluatedCell(model, "D2").value).toBe(3); }); test("can copy and paste format only from spread value", () => { ({ model, store } = makeStore(ClipboardStore)); // formula without format setCellContent(model, "A1", "=SUM(1+2)"); // formula with format set on it setCellContent(model, "A2", "=SUM(1+2)"); setCellFormat(model, "A2", "0%"); // formula that return value with format setCellContent(model, "A3", "=DATE(2042,1,1)"); // formula that return value with format and other format seted on it setCellContent(model, "A4", "=DATE(2042,1,1)"); setCellFormat(model, "A4", "0%"); // formula that return value with format inferred from reference setCellContent(model, "A5", "3"); setCellFormat(model, "A5", "0%"); setCellContent(model, "A6", "=SUM(1+A5)"); // formula that return value with format inferred from reference and other format seted on it setCellContent(model, "A7", "3"); setCellFormat(model, "A7", "0%"); setCellContent(model, "A8", "=SUM(1+A7)"); setCellFormat(model, "A8", "#,##0[$$]"); setCellContent(model, "B1", "=TRANSPOSE(A1:A8)"); for (const cell of ["C2", "D2", "E2", "F2", "G2", "H2", "I2"]) { setCellContent(model, cell, "42"); } copy(model, "C1:I1"); paste(model, "C2", "onlyFormat"); expect(getCellContent(model, "C2")).toBe("4200%"); expect(getCellContent(model, "D2")).toBe("2/10/1900"); expect(getCellContent(model, "E2")).toBe("4200%"); expect(getCellContent(model, "F2")).toBe("4200%"); expect(getCellContent(model, "G2")).toBe("4200%"); expect(getCellContent(model, "H2")).toBe("4200%"); expect(getCellContent(model, "I2")).toBe("42$"); }); describe("add col/row can invalidate the clipboard of cut", () => { test("adding a column before a cut zone is invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "B1", "2"); cut(model, "A1:B1"); addColumns(model, "before", "A", 1); paste(model, "A2"); expect(getCellContent(model, "B1")).toBe("1"); expect(getCellContent(model, "C1")).toBe("2"); expect(getCellContent(model, "A2")).toBe(""); expect(getCellContent(model, "B2")).toBe(""); expect(getCellContent(model, "C2")).toBe(""); }); test("adding a column after a cut zone is not invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "B1", "2"); cut(model, "A1:B1"); addColumns(model, "after", "B", 1); paste(model, "A2"); expect(getCellContent(model, "A1")).toBe(""); expect(getCellContent(model, "B1")).toBe(""); expect(getCellContent(model, "A2")).toBe("1"); expect(getCellContent(model, "B2")).toBe("2"); }); test("adding a column inside a cut zone is invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "B1", "2"); cut(model, "A1:B1"); addColumns(model, "after", "A", 1); paste(model, "A2"); expect(getCellContent(model, "A1")).toBe("1"); expect(getCellContent(model, "C1")).toBe("2"); expect(getCellContent(model, "A2")).toBe(""); expect(getCellContent(model, "C2")).toBe(""); }); test("adding multipe columns inside a cut zone is invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "B1", "2"); cut(model, "A1:B1"); addColumns(model, "after", "A", 5); paste(model, "A2"); expect(getCellContent(model, "A1")).toBe("1"); expect(getCellContent(model, "G1")).toBe("2"); expect(getCellContent(model, "A2")).toBe(""); expect(getCellContent(model, "C2")).toBe(""); }); test("adding a row before a cut zone is invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); cut(model, "A1:A2"); addRows(model, "before", 0, 1); paste(model, "C1"); expect(getCellContent(model, "A2")).toBe("1"); expect(getCellContent(model, "A3")).toBe("2"); expect(getCellContent(model, "C1")).toBe(""); expect(getCellContent(model, "C2")).toBe(""); expect(getCellContent(model, "C3")).toBe(""); }); test("adding a row after a cut zone is not invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); cut(model, "A1:A2"); addRows(model, "after", 2, 1); paste(model, "C1"); expect(getCellContent(model, "A1")).toBe(""); expect(getCellContent(model, "A2")).toBe(""); expect(getCellContent(model, "C1")).toBe("1"); expect(getCellContent(model, "C2")).toBe("2"); }); test("adding a row inside a cut zone is invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); cut(model, "A1:A2"); addRows(model, "after", 0, 1); paste(model, "C1"); expect(getCellContent(model, "A1")).toBe("1"); expect(getCellContent(model, "A3")).toBe("2"); expect(getCellContent(model, "C1")).toBe(""); expect(getCellContent(model, "C3")).toBe(""); }); test("adding multiple rows inside a cut zone is invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); cut(model, "A1:A2"); addRows(model, "after", 0, 5); paste(model, "C1"); expect(getCellContent(model, "A1")).toBe("1"); expect(getCellContent(model, "A7")).toBe("2"); expect(getCellContent(model, "C1")).toBe(""); expect(getCellContent(model, "C3")).toBe(""); }); test("Adding rows in another sheet does not invalidate the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); cut(model, "A1:A2"); createSheet(model, { activate: true }); addRows(model, "after", 0, 5); paste(model, "A1"); expect(getCellContent(model, "A1")).toBe("1"); expect(getCellContent(model, "A2")).toBe("2"); }); }); describe("remove col/row can invalidate the clipboard of cut", () => { test("removing a column before a cut zone is invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "1"); setCellContent(model, "C2", "2"); cut(model, "B2:C2"); deleteColumns(model, ["A"]); paste(model, "D1"); expect(getCellContent(model, "A2")).toBe("1"); expect(getCellContent(model, "B2")).toBe("2"); expect(getCellContent(model, "D1")).toBe(""); expect(getCellContent(model, "E1")).toBe(""); }); test("removing a column after a cut zone is not invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "1"); setCellContent(model, "C2", "2"); cut(model, "B2:C2"); deleteColumns(model, ["D"]); paste(model, "D1"); expect(getCellContent(model, "B2")).toBe(""); expect(getCellContent(model, "C2")).toBe(""); expect(getCellContent(model, "D1")).toBe("1"); expect(getCellContent(model, "E1")).toBe("2"); }); test("removing a column inside a cut zone is invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "1"); setCellContent(model, "C2", "2"); cut(model, "B2:C2"); deleteColumns(model, ["C"]); paste(model, "D1"); expect(getCellContent(model, "B2")).toBe("1"); expect(getCellContent(model, "D1")).toBe(""); }); test("removing a row before a cut zone is invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "1"); setCellContent(model, "C2", "2"); cut(model, "B2:C2"); deleteRows(model, [0]); paste(model, "D1"); expect(getCellContent(model, "B1")).toBe("1"); expect(getCellContent(model, "C1")).toBe("2"); expect(getCellContent(model, "D1")).toBe(""); expect(getCellContent(model, "E1")).toBe(""); }); test("removing a row after a cut zone is not invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "1"); setCellContent(model, "C2", "2"); cut(model, "B2:C2"); deleteRows(model, [3]); paste(model, "D1"); expect(getCellContent(model, "B2")).toBe(""); expect(getCellContent(model, "C2")).toBe(""); expect(getCellContent(model, "D1")).toBe("1"); expect(getCellContent(model, "E1")).toBe("2"); }); test("removing a row inside a cut zone is invalidating the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "B2", "1"); setCellContent(model, "B3", "2"); cut(model, "B2:B3"); deleteRows(model, [2]); paste(model, "D1"); expect(getCellContent(model, "B2")).toBe("1"); expect(getCellContent(model, "D1")).toBe(""); }); test("Removing rows in another sheet does not invalidate the clipboard", () => { ({ model, store } = makeStore(ClipboardStore)); setCellContent(model, "A1", "1"); setCellContent(model, "A2", "2"); cut(model, "A1:A2"); createSheet(model, { activate: true }); deleteRows(model, [1]); paste(model, "A1"); expect(getCellContent(model, "A1")).toBe("1"); expect(getCellContent(model, "A2")).toBe("2"); }); }); }); describe("cross spreadsheet copy/paste", () => { test("should copy/paste a cell with basic formatting", async () => { const { model: modelA, store: storeA } = makeStore(ClipboardStore); const { model: modelB } = makeStore(ClipboardStore); const cellStyle = { bold: true, fillColor: "#00FF00", fontSize: 20 }; setCellContent(modelA, "B2", "b2"); setFormatting(modelA, "B2", cellStyle); expect(getCell(modelA, "B2")).toMatchObject({ content: "b2", style: cellStyle, }); copy(modelA, "B2"); const clipboardContent = await storeA.getClipboardTextAndImageContent(); expect(clipboardContent["text/plain"]).toBe("b2"); const osClipboardContent = parseOSClipboardContent(clipboardContent); pasteFromOSClipboard(modelB, "D2", osClipboardContent); expect(getCellRawContent(modelA, "B2")).toBe("b2"); expect(getCellRawContent(modelB, "D2")).toBe("b2"); expect(getStyle(modelA, "B2")).toEqual(cellStyle); expect(getStyle(modelB, "D2")).toEqual(cellStyle); }); test("should copy/paste a cell with a border", async () => { const { model: modelA, store: storeA } = makeStore(ClipboardStore); const { model: modelB } = makeStore(ClipboardStore); selectCell(modelA, "B2"); setZoneBorders(modelA, { position: "top" }); expect(getBorder(modelA, "B2")).toEqual({ top: DEFAULT_BORDER_DESC }); copy(modelA, "B2"); const clipboardContent = await storeA.getClipboardTextAndImageContent(); const osClipboardContent = parseOSClipboardContent(clipboardContent); pasteFromOSClipboard(modelB, "D2", osClipboardContent); expect(getBorder(modelA, "B2")).toEqual({ top: DEFAULT_BORDER_DESC }); expect(getBorder(modelB, "D2")).toEqual({ top: DEFAULT_BORDER_DESC }); }); test("should copy/paste a cell with a formula", async () => { const { model: modelA, store: storeA } = makeStore(ClipboardStore); const { model: modelB } = makeStore(ClipboardStore); setCellContent(modelA, "A1", "=SUM(1,2)"); setCellContent(modelA, "A2", "=SUM(1,2)"); setCellFormat(modelA, "A2", "0%"); setCellContent(modelA, "A3", "=DATE(2024,1,1)"); setCellContent(modelA, "A4", "=DATE(2024,1,1)"); setCellFormat(modelA, "A4", "m/d/yyyy hh:mm:ss a"); setCellContent(modelA, "A5", "=SOMME(1,2)"); copy(modelA, "A1:A5"); const clipboardContent = await storeA.getClipboardTextAndImageContent(); const osClipboardContent = parseOSClipboardContent(clipboardContent); pasteFromOSClipboard(modelB, "D1", osClipboardContent); expect(getCellRawContent(modelB, "D1")).toBe("=SUM(1,2)"); expect(getCellRawContent(modelB, "D2")).toBe("=SUM(1,2)"); expect(getCellRawContent(modelB, "D3")).toBe("=DATE(2024,1,1)"); expect(getCellRawContent(modelB, "D4")).toBe("=DATE(2024,1,1)"); expect(getCellRawContent(modelB, "D5")).toBe("=SOMME(1,2)"); }); test("should copy/paste a cell with a markdown link", async () => { const { model: modelA, store: storeA } = makeStore(ClipboardStore); const { model: modelB } = makeStore(ClipboardStore); const url = "https://www.odoo.com"; const urlLabel = "Odoo Website"; setCellContent(modelA, "A1", markdownLink(urlLabel, url)); copy(modelA, "A1"); const clipboardContent = await storeA.getClipboardTextAndImageContent(); const osClipboardContent = parseOSClipboardContent(clipboardContent); pasteFromOSClipboard(modelB, "D1", osClipboardContent); const cell = getEvaluatedCell(modelB, "D1"); expect(cell.link?.label).toBe(urlLabel); expect(cell.link?.url).toBe(url); expect(urlRepresentation(cell.link!, modelB.getters)).toBe(url); expect(getCellRawContent(modelB, "D1")).toBe("[Odoo Website](https://www.odoo.com)"); expect(getStyle(modelB, "D1")).toEqual({ textColor: LINK_COLOR }); expect(getCellText(modelB, "D1")).toBe("Odoo Website"); }); test("should copy/paste a table", async () => { const { model: modelA, store: storeA } = makeStore(ClipboardStore); const { model: modelB } = makeStore(ClipboardStore); createTable(modelA, "A1:B2"); const tableA = modelA.getters.getCoreTables(modelA.getters.getActiveSheetId())[0]; expect(tableA).toMatchObject({ range: { zone: toZone("A1:B2") }, type: "static" }); copy(modelA, "A1:B2"); const clipboardContent = await storeA.getClipboardTextAndImageContent(); const osClipboardContent = parseOSClipboardContent(clipboardContent); pasteFromOSClipboard(modelB, "D1", osClipboardContent); const tableB = modelB.getters.getCoreTables(modelA.getters.getActiveSheetId())[0]; expect(tableB).toMatchObject({ range: { zone: toZone("D1:E2") }, type: "static" }); expect(tableB.config).toEqual(tableA.config); }); test("should copy/paste a cell with the cell content and format copied last from an external spreadsheet", async () => { const { model: modelA, store: storeA } = makeStore(ClipboardStore); const { model: modelB } = makeStore(ClipboardStore); const cellStyle = { bold: true, fillColor: "#00FF00", fontSize: 20 }; setCellContent(modelA, "A1", "a1"); setFormatting(modelA, "A1", cellStyle); setCellContent(modelB, "C1", "c1"); setFormatting(modelB, "C1", cellStyle); expect(getCell(modelA, "A1")).toMatchObject({ content: "a1", style: cellStyle, }); expect(getCell(modelB, "C1")).toMatchObject({ content: "c1", style: cellStyle, }); copy(modelB, "C1"); copy(modelA, "A1"); const clipboardContent = await storeA.getClipboardTextAndImageContent(); expect(clipboardContent["text/plain"]).toBe("a1"); const osClipboardContent = parseOSClipboardContent(clipboardContent); pasteFromOSClipboard(modelB, "B1", osClipboardContent); expect(getCell(modelA, "A1")).toMatchObject({ content: "a1", }); expect(getCell(modelB, "B1")).toMatchObject({ content: "a1", }); expect(getStyle(modelA, "A1")).toMatchObject(cellStyle); expect(getStyle(modelB, "B1")).toMatchObject(cellStyle); }); test("should copy/paste a formula cell with dependencies", async () => { const { model: modelA, store: storeA } = makeStoreWithModel( new Model({ sheets: [{ id: "sheetA" }] }), ClipboardStore ); const { model: modelB } = makeStoreWithModel( new Model({ sheets: [{ id: "sheetB" }] }), ClipboardStore ); setCellContent(modelA, "C1", "=A1*B1"); setCellContent(modelA, "C2", "=A2*B2"); setCellContent(modelA, "C3", "=A3*B3"); copy(modelA, "A1:C3"); const osClipboardContent = parseOSClipboardContent( await storeA.getClipboardTextAndImageContent() ); pasteFromOSClipboard(modelB, "E1", osClipboardContent); expect(getCellRawContent(modelB, "G1")).toBe("=E1*F1"); expect(getCellRawContent(modelB, "G2")).toBe("=E2*F2"); expect(getCellRawContent(modelB, "G3")).toBe("=E3*F3"); }); test("can copy/paste cells with escapable content", async () => { const { model: modelA, store: storeA } = makeStore(ClipboardStore); const { model: modelB } = makeStore(ClipboardStore); const escapableString = ` & " < > / \ '`; setCellContent(modelA, "A1", escapableString); copy(modelA, "A1"); const clipboardContent = await storeA.getClipboardTextAndImageContent(); expect(clipboardContent["text/plain"]).toBe(escapableString); const osClipboardContent = parseOSClipboardContent(clipboardContent); pasteFromOSClipboard(modelB, "D2", osClipboardContent); expect(getCellRawContent(modelA, "A1")).toBe(escapableString); expect(getCellRawContent(modelB, "D2")).toBe(escapableString); }); test("o-spreadsheet data from Excel clipboard is ignored", async () => { const { model: modelA, store: storeA } = makeStore(ClipboardStore); const { model: modelB } = makeStore(ClipboardStore); setCellContent(modelA, "A1", "oldContent"); copy(modelA, "A1"); const oldHTML = await storeA["getHTMLContent"](); let content = parseOSClipboardContent({ "text/html": `${oldHTML}`, "text/plain": "newContent", }); pasteFromOSClipboard(modelB, "D2", content); expect(getCellContent(modelB, "D2")).toBe("newContent"); content = parseOSClipboardContent({ "text/html": `${oldHTML}
randomContent
`, "text/plain": "newContent", }); pasteFromOSClipboard(modelB, "D2", content); expect(getCellContent(modelB, "D2")).toBe("newContent"); }); test("can extract o-spreadsheet clipboard id from HTML without parsing DOM", () => { const clipboardId = "1234-uuid"; expect( getOSheetClipboardIdFromHTML( `
x
` ) ).toBe(clipboardId); expect(getOSheetClipboardIdFromHTML(`
x
`)).toBeUndefined(); }); test("should only paste text content if there is a version mismatch", async () => { const { model: modelA, store: storeA } = makeStore(ClipboardStore); const { model: modelB } = makeStore(ClipboardStore); setCellContent(modelA, "B2", "Hello"); setFormatting(modelA, "B2", { bold: true }); copy(modelA, "B2"); const clipboardContent = await storeA.getClipboardTextAndImageContent(); const parsedContent = parseOSClipboardContent(clipboardContent); parsedContent.data!.version = "3"; // Version mismatch pasteFromOSClipboard(modelB, "D2", parsedContent); expect(getCellContent(modelB, "D2")).toBe("Hello"); expect(getStyle(modelB, "D2")).toEqual({}); }); }); test("Can use clipboard handlers to paste in a sheet other than the active sheet", () => { model = new Model(); const sheetId = model.getters.getActiveSheetId(); createSheet(model, { sheetId: "sh2" }); setCellContent(model, "A1", "1"); addEqualCf(model, "A1", { fillColor: "#FF0000" }, "1"); createTable(model, "A1"); const handlers = clipboardHandlersRegistries.cellHandlers .getAll() .map((handler) => new handler(model.getters, model.dispatch)); let copiedData = {}; const clipboardData = getClipboardDataPositions(sheetId, [toZone("A1")]); for (const handler of handlers) { copiedData = { ...copiedData, ...handler.copy(clipboardData, false) }; } const pasteTarget: ClipboardPasteTarget = { sheetId: "sh2", zones: target("A1") }; for (const handler of handlers) { handler.paste(pasteTarget, copiedData, { isCutOperation: false }); } expect(getCellContent(model, "A1", "sh2")).toBe("1"); expect(model.getters.getConditionalFormats(sheetId)).toMatchObject([ { ranges: ["A1"], rule: { style: { fillColor: "#FF0000" } } }, ]); expect(model.getters.getTables(sheetId)).toMatchObject([{ range: { zone: toZone("A1") } }]); });