Spreadsheet
TsgcHTMLComponent_Spreadsheet: a grid with formulas for Delphi, C++ Builder and .NET web applications. Users type values and formulas as they do in Excel, the server calculates them, and every change reaches your code before it is kept.
TsgcHTMLComponent_Spreadsheet: a grid with formulas for Delphi, C++ Builder and .NET web applications. Users type values and formulas as they do in Excel, the server calculates them, and every change reaches your code before it is kept.
Put it on a form, fill its sheet in code and render it. Every edit goes to the server, which checks it, recalculates the cells that depend on it and answers the cells that changed. There is no formula engine in the browser, so Delphi and .NET compute the same values.
TsgcHTMLComponent_Spreadsheet, with the sheet model TsgcHTMLSpreadsheetSheet in its Sheet property
A virtualized grid with a toolbar, a formula bar and a status bar, in plain JavaScript and CSS with no external library
Delphi, C++ Builder, .NET
Set cells in code with SetCellByName. OnCellChange checks and stores every cell a user changes, and with LiveSync the host broadcasts GetLiveSyncFragmentHTML after a change, so every open page reloads the cells that changed.
uses
sgcHTML_Formula, sgcHTML_Component_Spreadsheet, sgcHTMX_Engine_Server;
// On the form: a TsgcHTMLComponent_Spreadsheet (Spreadsheet1) with
// LiveSync = True, and the TsgcHTMX_Engine_Server (HTMXEngine1) of the page
procedure TMainModule.DataModuleCreate(Sender: TObject);
begin
Spreadsheet1.Sheet.SetCellByName('A1', 'Item');
Spreadsheet1.Sheet.SetCellByName('B1', 'Amount');
Spreadsheet1.Sheet.SetCellByName('A2', 'Rent');
Spreadsheet1.Sheet.SetCellByName('B2', '1200');
Spreadsheet1.Sheet.SetCellByName('A3', 'Travel');
Spreadsheet1.Sheet.SetCellByName('B3', '340.50');
Spreadsheet1.Sheet.SetCellByName('A4', 'Total');
Spreadsheet1.Sheet.SetCellByName('B4', '=SUM(B2:B3)');
end;
// Every cell a user changes: typing, paste, fill or clear
procedure TMainModule.Spreadsheet1CellChange(Sender: TObject; aRow, aCol: Integer;
const aOldRaw: string; var aNewRaw: string; var aAccept: Boolean);
begin
if aRow = 0 then
aAccept := False // the title row stays as it is
else
StoreCell(sgcHTMLCellName(aRow, aCol), aNewRaw); // your own store: a table, a file
end;
// The change is applied and recalculated: tell every open page
procedure TMainModule.Spreadsheet1Change(Sender: TObject;
aSheet: TsgcHTMLSpreadsheetSheet);
begin
HTMXEngine1.BroadcastFragment(Spreadsheet1.GetLiveSyncFragmentHTML);
end;
// GET /budget.xlsx: the sheet as a workbook, with formulas and styles
procedure TMainModule.ExportBudget(aStream: TStream);
begin
Spreadsheet1.SaveToXLSX(aStream);
end;@* Views/Sheets/Budget.cshtml: the shared team budget *@
@model BudgetModel
<sgc-spreadsheet name="budget" rows="40" cols="14"
frozen-rows="1" frozen-cols="1" css-height="560px"
allow-insert-delete="false" live-sync="true"
on-get-sheet="Model.GetSheet"
on-cell-change="Model.CellChange"
on-change="Model.Changed" />using esegece.sgcWebSockets;
var budget = new TsgcHTMLComponent_Spreadsheet();
budget.LiveSync = true;
budget.Sheet.SetCellByName("A1", "Item");
budget.Sheet.SetCellByName("B1", "Amount");
budget.Sheet.SetCellByName("A2", "Rent");
budget.Sheet.SetCellByName("B2", "1200");
budget.Sheet.SetCellByName("B3", "340.50");
budget.Sheet.SetCellByName("A4", "Total");
budget.Sheet.SetCellByName("B4", "=SUM(B2:B3)");
budget.OnCellChange += CellChange;
budget.OnChange += Changed;
// OnCellChange: check and keep one cell
public void CellChange(object Sender, int aRow, int aCol, string aOldRaw,
ref string aNewRaw, ref bool aAccept)
{
if (aRow == 0)
aAccept = false;
else
store.Save(aRow, aCol, aNewRaw);
}
// OnChange: applied and recalculated, every open page reloads the changed cells
public void Changed(object Sender, TsgcHTMLSpreadsheetSheet aSheet)
{
_ = hub.BroadcastAsync(((TsgcHTMLComponent_Spreadsheet)Sender).GetLiveSyncFragmentHTML());
}
// OnGetSheet: one sheet per user
public void GetSheet(object Sender, ref TsgcHTMLSpreadsheetSheet? aSheet)
{
aSheet = sheets.ForUser(User.Identity.Name);
}
// GET /budget.xlsx
using var ms = new MemoryStream();
budget.SaveToXLSX(ms);The keyboard, the clipboard and the formulas they know from Excel, on one sheet that your server keeps.
A cell that starts with = is a formula: the operators + - * / ^ % &, comparisons, parentheses, cell references and ranges such as A1:C5, with absolute parts written $A$1, A$1 or $A1. Precedence and text to number coercion work as in Excel. Errors show as #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! and #N/A, and #CIRC! marks a cell in a circular reference.
Math and statistics: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, COUNTBLANK, PRODUCT, ROUND, ROUNDUP, ROUNDDOWN, INT, MOD, ABS, SQRT, POWER, PI. Logic and information: IF, IFERROR, AND, OR, NOT, TRUE, FALSE, ISBLANK, ISNUMBER, ISTEXT, ISERROR. Text: CONCAT, CONCATENATE, LEN, LEFT, RIGHT, MID, UPPER, LOWER, TRIM, VALUE.
Conditional: SUMIF, COUNTIF and AVERAGEIF with criteria such as ">10", "<>x" and the wildcards * and ?. Lookup: VLOOKUP and HLOOKUP, exact or approximate, INDEX and MATCH. Date: DATE, YEAR, MONTH, DAY, TODAY, on Excel serial numbers. Financial: PMT, FV, PV, NPV. 54 functions in all, with Excel semantics, and the formula bar completes their names.
Bold, italic, alignment, text color and fill color, and a number format per cell: General, Number, Currency, Percent, Date or Text, with 0 to 10 decimals, a thousands separator, the currency symbol and the date format. The display takes the decimal and thousands separators of the sgcHTML locale in use when the locale defines them.
Arrows, Tab and Shift+Tab, Enter and Shift+Enter, Home, End, Ctrl+Home, Ctrl+End, Ctrl+arrows, PageUp and PageDown. F2 or typing starts editing, Esc cancels and Delete clears. Shift+arrows, Shift+click or a mouse drag select a range, a header click selects a row or a column, Ctrl+D and Ctrl+R fill down and right, and Ctrl+Z and Ctrl+Y undo and redo the user's own edits.
The formula bar shows the raw content of the cell, and its name box jumps to a cell: type B12 and press Enter. While a formula is edited its references are highlighted in colors, and the user can click cells or drag ranges into it.
Copy, cut and paste use tab separated text, the format Excel and Google Sheets put on the clipboard, so a block of cells moves between them in both directions. A formula copied inside the sheet and pasted or filled elsewhere has its relative references shifted and its absolute ones kept.
Insert rows above or below and columns left or right, delete rows and columns, from the toolbar or the context menu of a cell or a header. Every reference in every formula is adjusted as in Excel, and a reference to a deleted cell becomes #REF!. Columns resize by dragging the header edge, and rows and columns can be frozen.
Export to Excel downloads an .xlsx with the formulas and their values, the cell styles and number formats, the column widths and the frozen panes. Import reads an .xlsx, with its formulas and styles, or a .csv, once the user confirms that it replaces the current sheet.
With LiveSync, the people who have the same sheet open see each other's changes: the cells another user changed reload and flash. Every accepted change bumps the revision of the sheet, and a page that missed a revision loads the whole sheet again.
The status bar shows the Sum, Average and Count of the selection, and an indicator tells whether the changes are saved. Only the visible rows and columns are in the page, so a sheet of 10,000 rows by 104 columns scrolls smoothly. Without JavaScript the page still holds a plain table of the cells, readable by crawlers.
The sheet is a WAI-ARIA grid with row and column counts and indexes, and a polite live region announces the cell reference and its value. It follows the page theme, mirrors itself under dir="rtl", respects reduced motion and works at phone width: touch scrolling, tap to select and double tap to edit.
The members you reach for most often.
Sheet is a TsgcHTMLSpreadsheetSheet. SetCell(aRow, aCol, aRaw) and SetCellByName('B2', '=A1*2') set a cell, and Raw, Value, Text and Style read it. A raw that starts with = is a formula and a leading apostrophe forces text. Rows and columns are 0 based in the API, so A1 is row 0, column 0. Lock and Unlock guard changes made from your own threads.
OnCellChange(Sender, aRow, aCol, aOldRaw, aNewRaw, aAccept) runs for every cell a user changes. Change aNewRaw to correct the value, set aAccept to False to refuse it, or store it. OnChange(Sender, aSheet) follows once the change is applied and recalculated: the place to broadcast GetLiveSyncFragmentHTML.
OnGetSheet(Sender, aSheet) returns the sheet of the user behind the request. It is called at render time and on every request, so one component serves every user, each with a sheet of their own.
The browser sends what the user typed and the server does the rest: it applies the change, recalculates the cells that depend on it through a dependency graph and answers only the cells that changed. Recalculate runs a full calculation and Evaluate computes a formula outside any cell. Delphi and .NET give the same results, the same JSON and the same XLSX bytes.
The server never trusts the browser. It checks ReadOnly, the switches AllowFormulas, AllowFormatting, AllowInsertDelete, AllowImport and AllowExport, the coordinates against MaxRows (10000) and MaxCols (104), MaxCells (100000), MaxCellLength (8192) and MaxImportSize (5 MB), then calls OnCellChange for each cell. OnImport can refuse a file.
RowCount (100) and ColCount (26) size the sheet, FrozenRows and FrozenCols fix the titles, DefaultColWidth, RowHeight and CSSHeight set the sizes, and ShowToolbar, ShowFormulaBar and ShowStatusBar turn the bars on or off. ExportFileName names the downloaded workbook.
SaveToXLSX and LoadFromXLSX write and read a workbook from code, formulas and styles included. On the sheet, SaveToJSON and LoadFromJSON keep it in one JSON format that Delphi and .NET write byte for byte the same, SaveToCSV and LoadFromCSV use RFC 4180 with the raw values, and LoadFromDataSet loads a header row and the records.
InsertRows, DeleteRows, InsertCols and DeleteCols(aIndex, aCount) adjust every reference as in Excel. ShiftFormula(aFormula, aDeltaRow, aDeltaCol) moves the relative references of a formula and keeps the absolute ones, the same rule the browser applies on paste and fill.
The sheet posts to the component events endpoint of the HTMX engine, or to ActionURL. Without either it is a read only viewer of the sheet.
In a Razor view, the <sgc-spreadsheet> Tag Helper renders the sheet, and its requests are answered by the sgcHTML middleware of UseSgcHtml. Its attributes are name, read-only, rows, cols, frozen-rows, frozen-cols, css-height, the allow-* switches, live-sync, action-url and the handlers on-get-sheet, on-cell-change and on-change.
Demos\60.HTML\01.RunTime\35.Spreadsheet is sgc Sheets, a server on port 8137 with three sheets: a team budget everyone edits live, with SUM totals, percent of total, currency format and a chart that refreshes on every change; a loan calculator with a sheet per visitor, PMT, an amortization table filled down and a balance chart; and sales by region with SUMIF and VLOOKUP, XLSX export and import. The design time, Lazarus and .NET versions mirror it.
| Try It OnlineSee it in the Spreadsheet demo, running live on the eSeGeCe demo server. | Open | |
| Online HelpFull API reference and usage guide for this component. | Open | |
| Grid ComponentThe data grid for datasets: sort, filter, group, inline edit and export. | Open | |
| All sgcHTML ComponentsBrowse the full feature matrix of 80+ components. | Open | |
| Download Free TrialThe 30-day trial ships the 60.HTML demo projects. | Open | |
| PricingSingle, Team and Site licenses with full source code. | Open |