698 lines
26 KiB
TypeScript
698 lines
26 KiB
TypeScript
import * as z from "zod";
|
|
|
|
import {
|
|
applyRichTextStylePatch,
|
|
clearRichTextFormatting,
|
|
extraRichTextToPlainText,
|
|
normalizeExtraRichText,
|
|
type RichTextStylePatch,
|
|
} from "@/lib/rich-text";
|
|
|
|
const extraSheetId = z.string().min(1).max(128);
|
|
export const DEFAULT_EXTRA_SHEET_COLUMN_WIDTH = 140;
|
|
export const MIN_EXTRA_SHEET_COLUMN_WIDTH = 96;
|
|
export const MAX_EXTRA_SHEET_COLUMN_WIDTH = 480;
|
|
export const extraSheetAlignmentSchema = z.enum(["left", "center", "right"]);
|
|
export type ExtraSheetAlignment = z.infer<typeof extraSheetAlignmentSchema>;
|
|
export const extraSheetVerticalAlignmentSchema = z.enum(["top", "middle", "bottom"]);
|
|
export type ExtraSheetVerticalAlignment = z.infer<typeof extraSheetVerticalAlignmentSchema>;
|
|
|
|
export const extraSheetSchema = z.strictObject({
|
|
columns: z.array(z.strictObject({
|
|
id: extraSheetId,
|
|
label: z.string().trim().min(1).max(120),
|
|
width: z.number().int().min(MIN_EXTRA_SHEET_COLUMN_WIDTH).max(MAX_EXTRA_SHEET_COLUMN_WIDTH)
|
|
.default(DEFAULT_EXTRA_SHEET_COLUMN_WIDTH),
|
|
})).min(1),
|
|
merges: z.array(z.strictObject({
|
|
rowIds: z.array(extraSheetId).min(1),
|
|
columnIds: z.array(extraSheetId).min(1),
|
|
})).optional(),
|
|
rows: z.array(z.strictObject({
|
|
id: extraSheetId,
|
|
cells: z.record(z.string(), z.string().max(10_000)),
|
|
backgrounds: z.record(z.string(), z.string().regex(/^#[0-9A-Fa-f]{6}$/)).default({}),
|
|
alignments: z.record(z.string(), extraSheetAlignmentSchema).default({}),
|
|
verticalAlignments: z.record(z.string(), extraSheetVerticalAlignmentSchema).default({}),
|
|
})).min(1),
|
|
}).superRefine((sheet, context) => {
|
|
const columnIds = new Set(sheet.columns.map((column) => column.id));
|
|
if (columnIds.size !== sheet.columns.length) {
|
|
context.addIssue({ code: "custom", path: ["columns"], message: "รหัสคอลัมน์ต้องไม่ซ้ำกัน" });
|
|
}
|
|
const columnLabels = new Set(sheet.columns.map((column) => column.label.toLocaleLowerCase()));
|
|
if (columnLabels.size !== sheet.columns.length) {
|
|
context.addIssue({ code: "custom", path: ["columns"], message: "ชื่อคอลัมน์ต้องไม่ซ้ำกัน" });
|
|
}
|
|
const rowIds = new Set(sheet.rows.map((row) => row.id));
|
|
if (rowIds.size !== sheet.rows.length) {
|
|
context.addIssue({ code: "custom", path: ["rows"], message: "รหัสแถวต้องไม่ซ้ำกัน" });
|
|
}
|
|
const occupied = new Set<string>();
|
|
sheet.merges?.forEach((merge, index) => {
|
|
const consecutive = (ids: string[], axis: { id: string }[]) => {
|
|
const start = axis.findIndex((item) => item.id === ids[0]);
|
|
return start >= 0 && ids.every((id, offset) => axis[start + offset]?.id === id);
|
|
};
|
|
if (merge.rowIds.length * merge.columnIds.length < 2
|
|
|| !consecutive(merge.rowIds, sheet.rows)
|
|
|| !consecutive(merge.columnIds, sheet.columns)) {
|
|
context.addIssue({ code: "custom", path: ["merges", index], message: "ช่วงเซลล์ที่ผสานต้องเป็นสี่เหลี่ยมต่อเนื่องและมีอย่างน้อยสองเซลล์" });
|
|
}
|
|
for (const rowId of merge.rowIds) {
|
|
for (const columnId of merge.columnIds) {
|
|
const key = JSON.stringify([rowId, columnId]);
|
|
if (occupied.has(key)) {
|
|
context.addIssue({ code: "custom", path: ["merges", index], message: "ช่วงเซลล์ที่ผสานต้องไม่ซ้อนกัน" });
|
|
}
|
|
occupied.add(key);
|
|
}
|
|
}
|
|
});
|
|
sheet.rows.forEach((row, rowIndex) => {
|
|
[
|
|
...Object.keys(row.cells),
|
|
...Object.keys(row.backgrounds ?? {}),
|
|
...Object.keys(row.alignments ?? {}),
|
|
...Object.keys(row.verticalAlignments ?? {}),
|
|
].forEach((columnId) => {
|
|
if (!columnIds.has(columnId)) {
|
|
context.addIssue({
|
|
code: "custom",
|
|
path: ["rows", rowIndex, "cells", columnId],
|
|
message: "ไม่พบคอลัมน์ของเซลล์นี้",
|
|
});
|
|
}
|
|
});
|
|
});
|
|
});
|
|
|
|
export type ExtraSheet = z.output<typeof extraSheetSchema>;
|
|
|
|
export type ExtraSheetCell = {
|
|
value: string;
|
|
backgroundColor?: string;
|
|
alignment?: ExtraSheetAlignment;
|
|
verticalAlignment?: ExtraSheetVerticalAlignment;
|
|
};
|
|
export type ExtraSheetMatrix = Array<Array<ExtraSheetCell | undefined>>;
|
|
type SheetPoint = { row: number; column: number };
|
|
|
|
export type ExtraSheetRange = { start: SheetPoint; end: SheetPoint };
|
|
export type ExtraSheetMerge = NonNullable<ExtraSheet["merges"]>[number];
|
|
|
|
export function extraSheetMergeRange(sheet: ExtraSheet, merge: ExtraSheetMerge): ExtraSheetRange {
|
|
const row = sheet.rows.findIndex((item) => item.id === merge.rowIds[0]);
|
|
const column = sheet.columns.findIndex((item) => item.id === merge.columnIds[0]);
|
|
return {
|
|
start: { row, column },
|
|
end: { row: row + merge.rowIds.length - 1, column: column + merge.columnIds.length - 1 },
|
|
};
|
|
}
|
|
|
|
function contains(range: ExtraSheetRange, point: SheetPoint): boolean {
|
|
return point.row >= range.start.row && point.row <= range.end.row
|
|
&& point.column >= range.start.column && point.column <= range.end.column;
|
|
}
|
|
|
|
function intersects(a: ExtraSheetRange, b: ExtraSheetRange): boolean {
|
|
return a.start.row <= b.end.row && a.end.row >= b.start.row
|
|
&& a.start.column <= b.end.column && a.end.column >= b.start.column;
|
|
}
|
|
|
|
export function extraSheetMergeAt(sheet: ExtraSheet, point: SheetPoint): ExtraSheetRange | undefined {
|
|
const merge = sheet.merges?.find((item) => contains(extraSheetMergeRange(sheet, item), point));
|
|
return merge ? extraSheetMergeRange(sheet, merge) : undefined;
|
|
}
|
|
|
|
export function extraSheetMergeMap(sheet: ExtraSheet): Map<string, ExtraSheetRange> {
|
|
const map = new Map<string, ExtraSheetRange>();
|
|
for (const merge of sheet.merges ?? []) {
|
|
const range = extraSheetMergeRange(sheet, merge);
|
|
for (const point of extraSheetRangePoints(range)) map.set(`${point.row}:${point.column}`, range);
|
|
}
|
|
return map;
|
|
}
|
|
|
|
export function extraSheetAnchor(sheet: ExtraSheet, point: SheetPoint): SheetPoint {
|
|
return extraSheetMergeAt(sheet, point)?.start ?? point;
|
|
}
|
|
|
|
export function nextExtraSheetPoint(
|
|
sheet: ExtraSheet, point: SheetPoint, direction: SheetPoint, wrap = false,
|
|
): SheetPoint | undefined {
|
|
const merge = extraSheetMergeAt(sheet, point);
|
|
const anchor = merge?.start ?? point;
|
|
let next = {
|
|
row: direction.row > 0 && merge ? merge.end.row + 1 : point.row + direction.row,
|
|
column: direction.column > 0 && merge ? merge.end.column + 1 : point.column + direction.column,
|
|
};
|
|
while (true) {
|
|
if (wrap && next.column >= sheet.columns.length) next = { row: next.row + 1, column: 0 };
|
|
if (wrap && next.column < 0) next = { row: next.row - 1, column: sheet.columns.length - 1 };
|
|
if (!sheet.rows[next.row] || !sheet.columns[next.column]) return undefined;
|
|
const target = extraSheetAnchor(sheet, next);
|
|
if (target.row !== anchor.row || target.column !== anchor.column) return target;
|
|
if (!direction.row && !direction.column) return undefined;
|
|
next = { row: next.row + direction.row, column: next.column + direction.column };
|
|
}
|
|
}
|
|
|
|
export function extraSheetRangePoints(range: ExtraSheetRange): SheetPoint[] {
|
|
return Array.from({ length: range.end.row - range.start.row + 1 }, (_, row) =>
|
|
Array.from({ length: range.end.column - range.start.column + 1 }, (_, column) => ({
|
|
row: range.start.row + row, column: range.start.column + column,
|
|
})),
|
|
).flat();
|
|
}
|
|
|
|
export function expandExtraSheetRange(sheet: ExtraSheet, start: SheetPoint, end: SheetPoint): ExtraSheetRange {
|
|
const range = {
|
|
start: { row: Math.min(start.row, end.row), column: Math.min(start.column, end.column) },
|
|
end: { row: Math.max(start.row, end.row), column: Math.max(start.column, end.column) },
|
|
};
|
|
let changed = true;
|
|
while (changed) {
|
|
changed = false;
|
|
for (const merge of sheet.merges ?? []) {
|
|
const bounds = extraSheetMergeRange(sheet, merge);
|
|
if (!intersects(range, bounds)) continue;
|
|
if (contains(range, bounds.start) && contains(range, bounds.end)) continue;
|
|
range.start.row = Math.min(range.start.row, bounds.start.row);
|
|
range.start.column = Math.min(range.start.column, bounds.start.column);
|
|
range.end.row = Math.max(range.end.row, bounds.end.row);
|
|
range.end.column = Math.max(range.end.column, bounds.end.column);
|
|
changed = true;
|
|
}
|
|
}
|
|
return range;
|
|
}
|
|
|
|
export function canMergeExtraSheetRange(sheet: ExtraSheet, range: ExtraSheetRange): boolean {
|
|
if (!sheet.rows[range.start.row] || !sheet.rows[range.end.row]
|
|
|| !sheet.columns[range.start.column] || !sheet.columns[range.end.column]) return false;
|
|
if (range.end.row < range.start.row || range.end.column < range.start.column
|
|
|| (range.start.row === range.end.row && range.start.column === range.end.column)) return false;
|
|
return !(sheet.merges ?? []).some((merge) => {
|
|
const bounds = extraSheetMergeRange(sheet, merge);
|
|
const identical = bounds.start.row === range.start.row && bounds.start.column === range.start.column
|
|
&& bounds.end.row === range.end.row && bounds.end.column === range.end.column;
|
|
return identical || (intersects(range, bounds)
|
|
&& !(contains(range, bounds.start) && contains(range, bounds.end)));
|
|
});
|
|
}
|
|
|
|
export function unmergeExtraSheetRange(sheet: ExtraSheet, range: ExtraSheetRange): ExtraSheet {
|
|
if (!sheet.merges?.some((merge) => intersects(range, extraSheetMergeRange(sheet, merge)))) return sheet;
|
|
return { ...sheet, merges: sheet.merges.filter((merge) => !intersects(range, extraSheetMergeRange(sheet, merge))) };
|
|
}
|
|
|
|
export function mergeExtraSheetRange(sheet: ExtraSheet, range: ExtraSheetRange): ExtraSheet {
|
|
if (!canMergeExtraSheetRange(sheet, range)) return sheet;
|
|
const points = extraSheetRangePoints(range);
|
|
const text = points.flatMap(({ row, column }) => {
|
|
const value = sheet.rows[row].cells[sheet.columns[column].id] ?? "";
|
|
return extraRichTextToPlainText(value).trim() ? [normalizeExtraRichText(value)] : [];
|
|
}).join("");
|
|
if (text.length > 10_000) throw new Error("ข้อความที่ผสานต้องไม่เกิน 10,000 ตัวอักษร");
|
|
const unmerged = unmergeExtraSheetRange(sheet, range);
|
|
const cleared = updateExtraSheetCells(unmerged, points, { value: "" });
|
|
const updated = updateExtraSheetCell(cleared, range.start.row, range.start.column, text);
|
|
return {
|
|
...updated,
|
|
merges: [...(updated.merges ?? []), {
|
|
rowIds: sheet.rows.slice(range.start.row, range.end.row + 1).map((row) => row.id),
|
|
columnIds: sheet.columns.slice(range.start.column, range.end.column + 1).map((column) => column.id),
|
|
}],
|
|
};
|
|
}
|
|
|
|
function anchorKeys(sheet: ExtraSheet, points: Iterable<SheetPoint>): Set<string> {
|
|
const merges = extraSheetMergeMap(sheet);
|
|
return new Set(Array.from(points, (point) => {
|
|
const { row, column } = merges.get(`${point.row}:${point.column}`)?.start ?? point;
|
|
return `${row}:${column}`;
|
|
}));
|
|
}
|
|
|
|
// Remove only merges touched by a structural edit. Insertion at a boundary is outside a merge.
|
|
function unmergeExtraSheetAxis(
|
|
sheet: ExtraSheet, axis: "row" | "column", from: number, to = from, insertion = false,
|
|
): ExtraSheet {
|
|
if (!sheet.merges?.length) return sheet;
|
|
return {
|
|
...sheet,
|
|
merges: sheet.merges.filter((merge) => {
|
|
const range = extraSheetMergeRange(sheet, merge);
|
|
const start = range.start[axis];
|
|
const end = range.end[axis];
|
|
return insertion ? !(from > start && from <= end)
|
|
: !(Math.min(from, to) <= end && Math.max(from, to) >= start);
|
|
}),
|
|
};
|
|
}
|
|
|
|
export function extraSheetUsedRange(sheet: ExtraSheet): ExtraSheet | null {
|
|
const usedPoints = sheet.rows.flatMap((row, rowIndex) =>
|
|
sheet.columns.flatMap((column, columnIndex) => {
|
|
const used = extraRichTextToPlainText(row.cells[column.id] ?? "").trim()
|
|
|| row.backgrounds?.[column.id]
|
|
|| row.alignments?.[column.id]
|
|
|| row.verticalAlignments?.[column.id];
|
|
return used ? [{ row: rowIndex, column: columnIndex }] : [];
|
|
}),
|
|
);
|
|
for (const merge of sheet.merges ?? []) {
|
|
const range = extraSheetMergeRange(sheet, merge);
|
|
usedPoints.push(range.start, range.end);
|
|
}
|
|
if (!usedPoints.length) return null;
|
|
|
|
const rowIndexes = usedPoints.map((point) => point.row);
|
|
const columnIndexes = usedPoints.map((point) => point.column);
|
|
const firstRow = Math.min(...rowIndexes);
|
|
const lastRow = Math.max(...rowIndexes);
|
|
const firstColumn = Math.min(...columnIndexes);
|
|
const lastColumn = Math.max(...columnIndexes);
|
|
|
|
const columns = sheet.columns.slice(firstColumn, lastColumn + 1);
|
|
return {
|
|
...sheet,
|
|
columns,
|
|
rows: sheet.rows.slice(firstRow, lastRow + 1).map((row) => ({
|
|
...row,
|
|
cells: Object.fromEntries(columns.map((column) => [column.id, row.cells[column.id] ?? ""])),
|
|
backgrounds: Object.fromEntries(columns.flatMap((column) =>
|
|
row.backgrounds?.[column.id] ? [[column.id, row.backgrounds[column.id]]] : [],
|
|
)),
|
|
alignments: Object.fromEntries(columns.flatMap((column) =>
|
|
row.alignments?.[column.id] ? [[column.id, row.alignments[column.id]]] : [],
|
|
)),
|
|
verticalAlignments: Object.fromEntries(columns.flatMap((column) =>
|
|
row.verticalAlignments?.[column.id] ? [[column.id, row.verticalAlignments[column.id]]] : [],
|
|
)),
|
|
})),
|
|
};
|
|
}
|
|
|
|
export function nextExtraSheetColumnLabel(columns: ExtraSheet["columns"]): string {
|
|
const labels = new Set(columns.map((column) => column.label.toLocaleLowerCase()));
|
|
let index = columns.length + 1;
|
|
while (labels.has(`คอลัมน์ ${index}`.toLocaleLowerCase())) index += 1;
|
|
return `คอลัมน์ ${index}`;
|
|
}
|
|
|
|
export function createExtraSheet(createId: (prefix: string) => string): ExtraSheet {
|
|
const columns = Array.from({ length: 6 }, (_, index) => ({
|
|
id: createId("extra-sheet-column"),
|
|
label: `คอลัมน์ ${index + 1}`,
|
|
width: DEFAULT_EXTRA_SHEET_COLUMN_WIDTH,
|
|
}));
|
|
return {
|
|
columns,
|
|
rows: Array.from({ length: 4 }, () => ({
|
|
id: createId("extra-sheet-row"),
|
|
cells: Object.fromEntries(columns.map((column) => [column.id, ""])),
|
|
backgrounds: {},
|
|
alignments: {},
|
|
verticalAlignments: {},
|
|
})),
|
|
};
|
|
}
|
|
|
|
export function extraSheetToMatrix(sheet: ExtraSheet): ExtraSheetMatrix {
|
|
return sheet.rows.map((row) =>
|
|
sheet.columns.map((column) => ({
|
|
value: row.cells[column.id] ?? "",
|
|
backgroundColor: row.backgrounds?.[column.id],
|
|
alignment: row.alignments?.[column.id],
|
|
verticalAlignment: row.verticalAlignments?.[column.id],
|
|
})),
|
|
);
|
|
}
|
|
|
|
export function matrixToExtraSheet(
|
|
sheet: ExtraSheet,
|
|
matrix: ExtraSheetMatrix,
|
|
createId: (prefix: string) => string,
|
|
): ExtraSheet {
|
|
const columnCount = Math.max(
|
|
sheet.columns.length,
|
|
...matrix.map((row) => row.length),
|
|
);
|
|
const columns = [...sheet.columns];
|
|
while (columns.length < columnCount) {
|
|
columns.push({
|
|
id: createId("extra-sheet-column"),
|
|
label: nextExtraSheetColumnLabel(columns),
|
|
width: DEFAULT_EXTRA_SHEET_COLUMN_WIDTH,
|
|
});
|
|
}
|
|
const rowCount = Math.max(sheet.rows.length, matrix.length);
|
|
const rows = Array.from({ length: rowCount }, (_, rowIndex) => ({
|
|
id: sheet.rows[rowIndex]?.id ?? createId("extra-sheet-row"),
|
|
cells: Object.fromEntries(columns.map((column, columnIndex) => [
|
|
column.id,
|
|
String(matrix[rowIndex]?.[columnIndex]?.value ?? ""),
|
|
])),
|
|
backgrounds: Object.fromEntries(columns.flatMap((column, columnIndex) => {
|
|
const backgroundColor = matrix[rowIndex]?.[columnIndex]?.backgroundColor
|
|
?? sheet.rows[rowIndex]?.backgrounds?.[column.id];
|
|
return backgroundColor ? [[column.id, backgroundColor]] : [];
|
|
})),
|
|
alignments: Object.fromEntries(columns.flatMap((column, columnIndex) => {
|
|
const alignment = matrix[rowIndex]?.[columnIndex]?.alignment
|
|
?? sheet.rows[rowIndex]?.alignments?.[column.id];
|
|
return alignment ? [[column.id, alignment]] : [];
|
|
})),
|
|
verticalAlignments: Object.fromEntries(columns.flatMap((column, columnIndex) => {
|
|
const alignment = matrix[rowIndex]?.[columnIndex]?.verticalAlignment
|
|
?? sheet.rows[rowIndex]?.verticalAlignments?.[column.id];
|
|
return alignment ? [[column.id, alignment]] : [];
|
|
})),
|
|
}));
|
|
return { ...sheet, columns, rows };
|
|
}
|
|
|
|
export function addExtraSheetColumn(
|
|
sheet: ExtraSheet,
|
|
label: string,
|
|
id: string,
|
|
): ExtraSheet {
|
|
return insertExtraSheetColumn(sheet, sheet.columns.length, label, id);
|
|
}
|
|
|
|
export function insertExtraSheetColumn(
|
|
sheet: ExtraSheet,
|
|
index: number,
|
|
label: string,
|
|
id: string,
|
|
): ExtraSheet {
|
|
const position = Math.max(0, Math.min(index, sheet.columns.length));
|
|
sheet = unmergeExtraSheetAxis(sheet, "column", position, position, true);
|
|
const columns = [...sheet.columns];
|
|
columns.splice(position, 0, {
|
|
id,
|
|
label,
|
|
width: DEFAULT_EXTRA_SHEET_COLUMN_WIDTH,
|
|
});
|
|
return {
|
|
...sheet,
|
|
columns,
|
|
rows: sheet.rows.map((row) => ({ ...row, cells: { ...row.cells, [id]: "" } })),
|
|
};
|
|
}
|
|
|
|
export function addExtraSheetRow(sheet: ExtraSheet, id: string): ExtraSheet {
|
|
return insertExtraSheetRow(sheet, sheet.rows.length, id);
|
|
}
|
|
|
|
export function insertExtraSheetRow(
|
|
sheet: ExtraSheet,
|
|
index: number,
|
|
id: string,
|
|
): ExtraSheet {
|
|
const position = Math.max(0, Math.min(index, sheet.rows.length));
|
|
sheet = unmergeExtraSheetAxis(sheet, "row", position, position, true);
|
|
const rows = [...sheet.rows];
|
|
rows.splice(position, 0, {
|
|
id,
|
|
cells: Object.fromEntries(sheet.columns.map((column) => [column.id, ""])),
|
|
backgrounds: {},
|
|
alignments: {},
|
|
verticalAlignments: {},
|
|
});
|
|
return {
|
|
...sheet,
|
|
rows,
|
|
};
|
|
}
|
|
|
|
export function resizeExtraSheetColumn(
|
|
sheet: ExtraSheet,
|
|
index: number,
|
|
width: number,
|
|
): ExtraSheet {
|
|
if (!sheet.columns[index]) return sheet;
|
|
const normalized = Math.round(Math.max(
|
|
MIN_EXTRA_SHEET_COLUMN_WIDTH,
|
|
Math.min(MAX_EXTRA_SHEET_COLUMN_WIDTH, width),
|
|
));
|
|
return {
|
|
...sheet,
|
|
columns: sheet.columns.map((column, columnIndex) =>
|
|
columnIndex === index ? { ...column, width: normalized } : column),
|
|
};
|
|
}
|
|
|
|
export function moveExtraSheetColumn(
|
|
sheet: ExtraSheet,
|
|
from: number,
|
|
to: number,
|
|
): ExtraSheet {
|
|
if (to < 0 || to >= sheet.columns.length || from === to) return sheet;
|
|
sheet = unmergeExtraSheetAxis(sheet, "column", from, to);
|
|
const columns = [...sheet.columns];
|
|
const [column] = columns.splice(from, 1);
|
|
columns.splice(to, 0, column);
|
|
return { ...sheet, columns };
|
|
}
|
|
|
|
export function moveExtraSheetRow(
|
|
sheet: ExtraSheet,
|
|
from: number,
|
|
to: number,
|
|
): ExtraSheet {
|
|
if (to < 0 || to >= sheet.rows.length || from === to) return sheet;
|
|
sheet = unmergeExtraSheetAxis(sheet, "row", from, to);
|
|
const rows = [...sheet.rows];
|
|
const [row] = rows.splice(from, 1);
|
|
rows.splice(to, 0, row);
|
|
return { ...sheet, rows };
|
|
}
|
|
|
|
export function deleteExtraSheetColumn(sheet: ExtraSheet, index: number): ExtraSheet {
|
|
if (sheet.columns.length === 1) return sheet;
|
|
const columnId = sheet.columns[index]?.id;
|
|
if (!columnId) return sheet;
|
|
sheet = unmergeExtraSheetAxis(sheet, "column", index);
|
|
return {
|
|
...sheet,
|
|
columns: sheet.columns.filter((_, columnIndex) => columnIndex !== index),
|
|
rows: sheet.rows.map((row) => ({
|
|
...row,
|
|
cells: Object.fromEntries(Object.entries(row.cells).filter(([key]) => key !== columnId)),
|
|
backgrounds: Object.fromEntries(Object.entries(row.backgrounds ?? {}).filter(([key]) => key !== columnId)),
|
|
alignments: Object.fromEntries(Object.entries(row.alignments ?? {}).filter(([key]) => key !== columnId)),
|
|
verticalAlignments: Object.fromEntries(Object.entries(row.verticalAlignments ?? {}).filter(([key]) => key !== columnId)),
|
|
})),
|
|
};
|
|
}
|
|
|
|
export function deleteExtraSheetRow(sheet: ExtraSheet, index: number): ExtraSheet {
|
|
if (sheet.rows.length === 1) return sheet;
|
|
sheet = unmergeExtraSheetAxis(sheet, "row", index);
|
|
return { ...sheet, rows: sheet.rows.filter((_, rowIndex) => rowIndex !== index) };
|
|
}
|
|
|
|
export function updateExtraSheetCell(
|
|
sheet: ExtraSheet,
|
|
rowIndex: number,
|
|
columnIndex: number,
|
|
value: string,
|
|
): ExtraSheet {
|
|
const anchor = extraSheetAnchor(sheet, { row: rowIndex, column: columnIndex });
|
|
rowIndex = anchor.row;
|
|
columnIndex = anchor.column;
|
|
const row = sheet.rows[rowIndex];
|
|
const column = sheet.columns[columnIndex];
|
|
if (!row || !column) return sheet;
|
|
return {
|
|
...sheet,
|
|
rows: sheet.rows.map((candidate, index) => index === rowIndex
|
|
? { ...candidate, cells: { ...candidate.cells, [column.id]: value } }
|
|
: candidate),
|
|
};
|
|
}
|
|
|
|
export function updateExtraSheetCells(
|
|
sheet: ExtraSheet,
|
|
points: Iterable<SheetPoint>,
|
|
update: {
|
|
value?: string;
|
|
backgroundColor?: string | null;
|
|
alignment?: ExtraSheetAlignment | null;
|
|
verticalAlignment?: ExtraSheetVerticalAlignment | null;
|
|
},
|
|
): ExtraSheet {
|
|
const selected = anchorKeys(sheet, points);
|
|
return {
|
|
...sheet,
|
|
rows: sheet.rows.map((row, rowIndex) => {
|
|
const cells = { ...row.cells };
|
|
const backgrounds = { ...(row.backgrounds ?? {}) };
|
|
const alignments = { ...(row.alignments ?? {}) };
|
|
const verticalAlignments = { ...(row.verticalAlignments ?? {}) };
|
|
sheet.columns.forEach((column, columnIndex) => {
|
|
if (!selected.has(`${rowIndex}:${columnIndex}`)) return;
|
|
if (update.value !== undefined) cells[column.id] = update.value;
|
|
if (update.backgroundColor === null) delete backgrounds[column.id];
|
|
else if (update.backgroundColor !== undefined) backgrounds[column.id] = update.backgroundColor;
|
|
if (update.alignment === null) delete alignments[column.id];
|
|
else if (update.alignment !== undefined) alignments[column.id] = update.alignment;
|
|
if (update.verticalAlignment === null) delete verticalAlignments[column.id];
|
|
else if (update.verticalAlignment !== undefined) verticalAlignments[column.id] = update.verticalAlignment;
|
|
});
|
|
return { ...row, cells, backgrounds, alignments, verticalAlignments };
|
|
}),
|
|
};
|
|
}
|
|
|
|
export function clearExtraSheetCellFormatting(
|
|
sheet: ExtraSheet,
|
|
points: Iterable<SheetPoint>,
|
|
): ExtraSheet {
|
|
const selected = anchorKeys(sheet, points);
|
|
return {
|
|
...sheet,
|
|
rows: sheet.rows.map((row, rowIndex) => {
|
|
const cells = { ...row.cells };
|
|
const backgrounds = { ...(row.backgrounds ?? {}) };
|
|
const alignments = { ...(row.alignments ?? {}) };
|
|
const verticalAlignments = { ...(row.verticalAlignments ?? {}) };
|
|
sheet.columns.forEach((column, columnIndex) => {
|
|
if (!selected.has(`${rowIndex}:${columnIndex}`)) return;
|
|
cells[column.id] = clearRichTextFormatting(cells[column.id] ?? "");
|
|
delete backgrounds[column.id];
|
|
delete alignments[column.id];
|
|
delete verticalAlignments[column.id];
|
|
});
|
|
return { ...row, cells, backgrounds, alignments, verticalAlignments };
|
|
}),
|
|
};
|
|
}
|
|
|
|
function quoteTsvValue(value: string): string {
|
|
return /[\t\r\n"]/.test(value) ? `"${value.replaceAll('"', '""')}"` : value;
|
|
}
|
|
|
|
export function extraSheetRangeToTsv(
|
|
sheet: ExtraSheet,
|
|
start: SheetPoint,
|
|
end: SheetPoint,
|
|
): string {
|
|
const range = expandExtraSheetRange(sheet, start, end);
|
|
const { row: top, column: left } = range.start;
|
|
const { row: bottom, column: right } = range.end;
|
|
return sheet.rows.slice(top, bottom + 1).map((row) =>
|
|
sheet.columns.slice(left, right + 1).map((column) =>
|
|
quoteTsvValue(extraRichTextToPlainText(row.cells[column.id] ?? "")),
|
|
).join("\t"),
|
|
).join("\n");
|
|
}
|
|
|
|
export function parseExtraSheetTsv(value: string): string[][] {
|
|
const rows: string[][] = [[]];
|
|
let field = "";
|
|
let quoted = false;
|
|
for (let index = 0; index < value.length; index += 1) {
|
|
const character = value[index];
|
|
if (character === '"') {
|
|
if (quoted && value[index + 1] === '"') {
|
|
field += '"';
|
|
index += 1;
|
|
} else quoted = !quoted;
|
|
} else if (character === "\t" && !quoted) {
|
|
rows.at(-1)!.push(field);
|
|
field = "";
|
|
} else if ((character === "\n" || character === "\r") && !quoted) {
|
|
if (character === "\r" && value[index + 1] === "\n") index += 1;
|
|
rows.at(-1)!.push(field);
|
|
rows.push([]);
|
|
field = "";
|
|
} else field += character;
|
|
}
|
|
rows.at(-1)!.push(field);
|
|
return rows.length > 1 && rows.at(-1)?.length === 1 && rows.at(-1)?.[0] === ""
|
|
? rows.slice(0, -1)
|
|
: rows;
|
|
}
|
|
|
|
export function pasteExtraSheetText(
|
|
sheet: ExtraSheet,
|
|
start: SheetPoint,
|
|
text: string,
|
|
createId: (prefix: string) => string,
|
|
): ExtraSheet {
|
|
const pasted = parseExtraSheetTsv(text);
|
|
if (!pasted.length) return sheet;
|
|
if (pasted.length === 1 && pasted[0].length === 1) {
|
|
return updateExtraSheetCell(sheet, start.row, start.column, normalizeExtraRichText(pasted[0][0]));
|
|
}
|
|
let next = unmergeExtraSheetRange(sheet, {
|
|
start,
|
|
end: { row: start.row + pasted.length - 1, column: start.column + Math.max(...pasted.map((row) => row.length)) - 1 },
|
|
});
|
|
const requiredRows = start.row + pasted.length;
|
|
const requiredColumns = start.column + Math.max(...pasted.map((row) => row.length));
|
|
while (next.rows.length < requiredRows) {
|
|
next = addExtraSheetRow(next, createId("extra-sheet-row"));
|
|
}
|
|
while (next.columns.length < requiredColumns) {
|
|
next = addExtraSheetColumn(
|
|
next,
|
|
nextExtraSheetColumnLabel(next.columns),
|
|
createId("extra-sheet-column"),
|
|
);
|
|
}
|
|
return {
|
|
...next,
|
|
rows: next.rows.map((row, rowIndex) => {
|
|
const pastedRow = pasted[rowIndex - start.row];
|
|
if (!pastedRow) return row;
|
|
const cells = { ...row.cells };
|
|
pastedRow.forEach((cell, offset) => {
|
|
const column = next.columns[start.column + offset];
|
|
if (column) cells[column.id] = normalizeExtraRichText(cell);
|
|
});
|
|
return { ...row, cells };
|
|
}),
|
|
};
|
|
}
|
|
|
|
export function styleExtraSheetCells(
|
|
sheet: ExtraSheet,
|
|
points: Iterable<SheetPoint>,
|
|
patch: RichTextStylePatch,
|
|
): ExtraSheet {
|
|
const selected = anchorKeys(sheet, points);
|
|
return {
|
|
...sheet,
|
|
rows: sheet.rows.map((row, rowIndex) => ({
|
|
...row,
|
|
cells: Object.fromEntries(Object.entries(row.cells).map(([columnId, value]) => {
|
|
const columnIndex = sheet.columns.findIndex((column) => column.id === columnId);
|
|
return [
|
|
columnId,
|
|
selected.has(`${rowIndex}:${columnIndex}`)
|
|
? applyRichTextStylePatch(value, patch)
|
|
: value,
|
|
];
|
|
})),
|
|
})),
|
|
};
|
|
}
|
|
|
|
export function normalizeExtraSheetRichText(sheet: ExtraSheet): ExtraSheet {
|
|
return {
|
|
...sheet,
|
|
rows: sheet.rows.map((row) => ({
|
|
...row,
|
|
cells: Object.fromEntries(Object.entries(row.cells).map(([columnId, value]) => [
|
|
columnId,
|
|
normalizeExtraRichText(value),
|
|
])),
|
|
})),
|
|
};
|
|
}
|