// Attempt to parse s as cell contents, and set selected cell(s) private void SetCell(int col, int row, String text, DataGridViewCellValidatingEventArgs arg = null) { Cell cell = Cell.Parse(text, sheet.workbook, col, row); ArrayFormula oldArrayFormula = sheet[col, row] as ArrayFormula; gui.SetCyclicError(null); if (cell == null) { if (text.TrimStart(' ').StartsWith("=")) // Ill-formed formula, cancel edit { if (arg != null) { ErrorMessage("Bad formula"); arg.Cancel = true; } return; } else if (!String.IsNullOrWhiteSpace(text)) // Assume a quote expression { cell = Cell.Parse("'" + text, sheet.workbook, col, row); } } DataGridViewSelectedCellCollection dgvscc = dgv.SelectedCells; if (dgvscc.Count > 1 && cell is Formula) // Array formula { int ulCol = col, ulRow = row, lrCol = col, lrRow = row; foreach (DataGridViewCell dgvc in dgvscc) { ulCol = Math.Min(ulCol, dgvc.ColumnIndex); ulRow = Math.Min(ulRow, dgvc.RowIndex); lrCol = Math.Max(lrCol, dgvc.ColumnIndex); lrRow = Math.Max(lrRow, dgvc.RowIndex); } CellAddr ulCa = new CellAddr(ulCol, ulRow), lrCa = new CellAddr(lrCol, lrRow); if (oldArrayFormula != null && arg != null && !(oldArrayFormula.caf.ulCa.Equals(ulCa) && oldArrayFormula.caf.lrCa.Equals(lrCa))) { ErrorMessage("Cannot edit part of array formula"); arg.Cancel = true; return; } sheet.SetArrayFormula(cell, col, row, ulCa, lrCa); } else // One-cell formula, or constant, or null (parse error) { if (oldArrayFormula != null && arg != null) { ErrorMessage("Cannot edit part of array formula"); arg.Cancel = true; return; } sheet.SetCell(cell, col, row); } RecalculateAndShow(); gui.SetCyclicError("Cyclic dependency"); }
// Detect blocks of formula copies, for finding compact support sets public void AddToSupportSets() { int sheetCols = Cols, sheetRows = Rows; cells.Forall((int col, int row, Cell cell) => { if (cell is ArrayFormula) { // Do not try to detect copies of array formulas. // CHECK THIS! ArrayFormula af = cell as ArrayFormula; af.AddToSupportSets(this, col, row, 1, 1); } else if (cell is Formula) { Formula f = cell as Formula; if (!f.Visited) { Expr expr = f.Expr; // (1) Find a large rectangle containing only formula f // (1a) Find largest square of copies with upper left corner = (col, row) int size = 1; while (col + size < sheetCols && row + size < sheetRows && CheckCol(col + size, row, expr, size) && CheckRow(col, row + size, expr, size)) { size++; } // All cells in sheet[col..col+size-1, row..row+size-1] contain expression expr // (1b) Try extending square with more rows below int rowSize = size; while (row + rowSize < sheetRows && CheckRow(col, row + rowSize, expr, size - 1)) { rowSize++; } // sheet[col..col+size-1, row..row+rows-1] contains expr // (1c) Try extending square with more columns to the right int cols = size; while (col + cols < sheetCols && CheckCol(col + cols, row, expr, size - 1)) { cols++; } // sheet[col..col+cols-1, row..row+size-1] contains expr if (rowSize > cols) { cols = size; } else { rowSize = size; } // All cells in sheet[col..col+cols-1, row..row+rows-1] contain expression expr // (2) Mark all cells in the rectangle visited for (int deltaCol = 0; deltaCol < cols; deltaCol++) { for (int deltaRow = 0; deltaRow < rowSize; deltaRow++) { (this[col + deltaCol, row + deltaRow] as Formula).Visited = true; } } // (3) Update the support sets of cells referred to from expr expr.AddToSupportSets(this, col, row, cols, rowSize); } } }); this.ResetCellState(); // Undo changes made to the sheet's cells' states }
// Insert N new rows just before row R >= 0 // TODO: Consider replacing all assignments to cells[,] with assignments to this[,], // for proper maintenance of support sets and volatile status public void InsertRowCols(int R, int N, bool doRows) { // Check that this will not split a array formula if (R >= 1) { if (doRows) { for (int col = 0; col < Cols; col++) { Cell cell = cells[col, R - 1]; ArrayFormula mf = cell as ArrayFormula; if (mf != null && mf.Contains(col, R)) { throw new Exception("Row insert would split array formula"); } } } else { for (int row = 0; row < Rows; row++) { Cell cell = cells[R - 1, row]; ArrayFormula mf = cell as ArrayFormula; if (mf != null && mf.Contains(R, row)) { throw new Exception("Column insert would split array formula"); } } } } // Adjust formulas in all sheets. The dictionary records adjusted // expressions to preserve sharing of expressions where possible. Dictionary <Expr, Adjusted <Expr> > adjusted = new Dictionary <Expr, Adjusted <Expr> >(); foreach (Sheet sheet in workbook) { for (int r = 0; r < sheet.Rows; r++) { for (int c = 0; c < sheet.Cols; c++) { Cell cell = sheet.cells[c, r]; cell?.InsertRowCols(adjusted, this, sheet == this, R, N, doRows ? r : c, doRows); } } } if (doRows) { // Move the rows R, R+1, ... later by N rows in current sheet for (int r = Rows - 1; r >= R + N; r--) { for (int c = 0; c < Cols; c++) { cells[c, r] = cells[c, r - N]; } } // Finally, null out the fresh rows for (int r = 0; r < N; r++) { for (int c = 0; c < Cols; c++) { cells[c, r + R] = null; } } } else { // Move the columns R, R+1, ... later by N columns in current sheet for (int c = Cols - 1; c >= R + N; c--) { for (int r = 0; r < Rows; r++) { cells[c, r] = cells[c - N, r]; } } // Finally, null out the fresh columns for (int c = 0; c < N; c++) { for (int r = 0; r < Rows; r++) { cells[c + R, r] = null; } } } }