Spreadsheet

Complete

Een volledige spreadsheet: formules en functies, opmaak, samengevoegde cellen, vastgezette deelvensters, grafieken, voorwaardelijke opmaak, gegevensvalidatie, meerdere bladen, ongedaan maken en opnieuw, kopiëren en plakken, doorvoeren, sorteren, zoeken en vervangen, en CSV-, .xlsx- en JSON-bestanden. De formule-engine en de bestandscode maken deel uit van RayCraft.Blazor.Office en bevatten geen onderdelen van derden.

Parameters

Rules colour cells by their values. Rules are checked in order; when two rules set the same property, the first one wins. A Formula rule is written for the top-left cell of its range and applies to every cell relative to it.

Parameter	Type	Beschrijving
`Rule`	`Effect`
`GreaterThan, GreaterOrEqual, LessThan, LessOrEqual, Equal, NotEqual`	`Colours cells that compare that way with the value (a number, text or =formula).`
`Between, NotBetween`	`Colours cells inside or outside the two limits.`
`TextContains, TextNotContains, BeginsWith, EndsWith`	`Colours cells by their shown text, ignoring case.`
`Blank, NotBlank, Error`	`Colours empty cells, filled cells or cells showing an error.`
`Duplicate, Unique`	`Colours values that appear more than once, or only once, in the range.`
`Top, Bottom`	`Colours the highest or lowest numbers (default 10).`
`AboveAverage, BelowAverage`	`Colours numbers above or below the average of the range.`
`Formula`	`Colours cells where the formula is true; references move with the cell.`
`ColorScale`	`Fills numbers from one colour to another (Background = lowest, Color = highest, MidColor optional).`
`DataBar`	`Draws a bar behind each number, as long as its share of the largest value (Background = bar colour).`

Everything is reachable by keyboard and mouse, with the shortcuts people know from other spreadsheets.

Parameter	Type	Beschrijving
`Keys or gesture`	`What it does`
`Arrow keys, Tab, Enter`	`Move the active cell (Shift reverses Tab and Enter)`
`Shift + arrows / click / drag`	`Extend the selection`
`Ctrl + arrows`	`Jump to the edge of a block of filled cells`
`Ctrl + Home / End`	`Go to A1 / the last used cell`
`Home, End, PageUp, PageDown`	`Row start or end, page up or down`
`Ctrl + A, Ctrl + Space, Shift + Space`	`Select everything, the column or the row`
`Type, F2, double-click`	`Edit the cell (typing replaces its content, F2 keeps it)`
`Enter / Tab / Esc while editing`	`Accept and move down or right / cancel`
`Delete, Backspace`	`Clear the selected cells`
`Ctrl + C, X, V`	`Copy, cut and paste; formulas keep their relative references, and text from other programs is pasted as values`
`Ctrl + Z, Ctrl + Y`	`Undo and redo (also Ctrl + Shift + Z)`
`Ctrl + B, I, U`	`Bold, italic, underline`
`Ctrl + D, Ctrl + R`	`Fill the selection down or right from its first row or column`
`Ctrl + F`	`Find and replace`
`Drag the fill handle`	`Copy a cell, continue a number series (1, 2 gives 3, 4...) or count up text such as Item 1`
`Click a column or row header`	`Select the whole column or row; drag across headers to select several`
`Drag a header edge, double-click it`	`Resize a column or row, or reset it`
`Right-click a cell, a column or row header, or a chart`	`Context menu with the commands that fit what you clicked`
`Click a chart, drag it, drag its corner, Delete`	`Select, move, resize and remove a chart`
`Arrow next to a cell with a list rule`	`Open the drop-down and pick a value`
`Name box (top left)`	`Type A1 or B2:D9 to jump to a cell or range`
`F7, cell with a coloured corner`	`Spelling and grammar panel; right-click a marked cell for its corrections`

The menu bar above the toolbar groups every command. Click a menu name (or move to it with the arrow keys) to open it; Left and Right switch between menus, Up and Down move through the entries, Right opens a submenu and Escape closes the menu. Right-click opens a context menu that fits what you clicked: a cell, a column header, a row header or a chart.

Parameter	Type	Beschrijving
`Menu`	`Contents`
`File`	`Import a file; export as CSV, XLSX or JSON.`
`Edit`	`Undo, redo, cut, copy, paste, paste special (values or formatting only), select all, clear contents or formatting, find and replace.`
`View`	`Show or hide the toolbar, formula bar, sheet tabs and status bar; freeze panes.`
`Insert`	`Rows and columns; open the chart, conditional formatting and data validation panels.`
`Format`	`Bold, italic, underline, strikethrough; number format, alignment, wrap text, borders; merge cells; clear formatting.`
`Data`	`Sort, fill down and right, data validation, conditional formatting.`
`Review`	`Spelling, grammar and formula checks.`
`Sheet`	`Add, rename, delete and move the current sheet.`

Operators and functions that take single values work on whole ranges and arrays, element by element: =SUMPRODUCT((A1:A5>2)B1:B5), =SUM(IF(A1:A5>2,A1:A5,0)) or =TEXTJOIN(",",TRUE,IF(A1:A5>2,C1:C5,"")). A formula that produces several values (=A1:A52, =UPPER(C1:C5), =SORT(A1:A5), ={1,2,3}) fills the cells below or to the right and shows #SPILL! when one of them is not empty.

Parameter	Type	Beschrijving
`Group`	`Functions`
`Math`	`ABSSIGNSQRTPOWEREXPLNLOGLOG10MODQUOTIENTINTTRUNCROUNDROUNDUPROUNDDOWNCEILINGFLOOREVENODDFACTGCDLCMPIRANDRANDBETWEENSINCOSTANASINACOSATANATAN2DEGREESRADIANSSUMPRODUCTSUMSQSUMPRODUCTSINHCOSHTANHSQRTPIMROUNDCOMBINPERMUTSUBTOTAL`
`Statistics`	`AVERAGEMINMAXMEDIANMODECOUNTCOUNTACOUNTBLANKLARGESMALLRANKVARVARPSTDEVSTDEVPSUMIFSUMIFSCOUNTIFCOUNTIFSAVERAGEIFAVERAGEIFSMAXIFSMINIFSAVERAGEAPERCENTILEQUARTILECORRELSLOPEINTERCEPTFORECASTGEOMEANHARMEANAGGREGATE`
`Logic`	`IFIFSIFERRORIFNASWITCHANDORXORNOTTRUEFALSE`
`Text`	`LENUPPERLOWERPROPERTRIMCLEANLEFTRIGHTMIDCONCATCONCATENATETEXTJOINREPTSUBSTITUTEREPLACEFINDSEARCHEXACTVALUETEXTCHARCODETNTEXTBEFORETEXTAFTERNUMBERVALUEUNICODEUNICHAR`
`Lookup`	`VLOOKUPHLOOKUPINDEXMATCHCHOOSEROWCOLUMNROWSCOLUMNSINDIRECTLOOKUPXLOOKUPXMATCHOFFSETADDRESS`
`Date and time`	`TODAYNOWDATETIMEYEARMONTHDAYHOURMINUTESECONDWEEKDAYEDATEEOMONTHDAYSDATEDIFWEEKNUMISOWEEKNUMNETWORKDAYSWORKDAYDATEVALUE`
`Dynamic arrays`	`SEQUENCETRANSPOSEUNIQUESORTSORTBYFILTER`
`Financial`	`PMTFVPVNPERNPVIRRRATESLN`
`Number bases and numerals`	`BIN2DECOCT2DECHEX2DECDEC2BINDEC2OCTDEC2HEXBASEDECIMALROMANARABIC`
`Information`	`ISNUMBERISTEXTISNONTEXTISLOGICALISBLANKISERRORISERRISNAISEVENISODDNAERROR.TYPE`
`Distributions`	`NORM.DISTNORM.INVNORM.S.DISTNORM.S.INVPHIGAUSSLOGNORM.DISTLOGNORM.INVEXPON.DISTPOISSON.DISTBINOM.DISTBINOM.INVHYPGEOM.DISTNEGBINOM.DISTT.DISTT.DIST.2TT.DIST.RTT.INVT.INV.2TCHISQ.DISTCHISQ.DIST.RTCHISQ.INVCHISQ.INV.RTGAMMA.DISTGAMMA.INVWEIBULL.DISTBETA.DISTBETA.INVCONFIDENCE.NORMCONFIDENCE.TZ.TESTSTANDARDIZEFISHERFISHERINVERFERFCGAMMAGAMMALN`
`More statistics`	`AVEDEVDEVSQSKEWSKEW.PKURTSTDEVASTDEVPAVARAVARPAMAXAMINATRIMMEANRSQPEARSONSTEYXCOVARCOVARIANCE.PCOVARIANCE.SPERCENTRANKPERCENTRANK.EXCPERCENTILE.EXCQUARTILE.EXCRANK.AVGFREQUENCYMODE.MULTLINESTTRENDFORECAST.LINEAR`
`More financial`	`IPMTPPMTCUMIPMTCUMPRINCISPMTEFFECTNOMINALRRIPDURATIONMIRRXNPVXIRRFVSCHEDULEDBDDBSYDDOLLARDEDOLLARFRDAYS360YEARFRAC`
`Database`	`DSUMDAVERAGEDCOUNTDCOUNTADGETDMAXDMINDPRODUCTDSTDEVDSTDEVPDVARDVARP`
`More arrays`	`TAKEDROPCHOOSECOLSCHOOSEROWSVSTACKHSTACKTOCOLTOROWWRAPROWSWRAPCOLSEXPANDTEXTSPLITRANDARRAYMMULTMINVERSEMDETERMMUNIT`
`More math and information`	`ACOSHASINHATANHCOTCOTHCSCCSCHSECSECHACOTACOTHCEILING.MATHFLOOR.MATHCEILING.PRECISEFLOOR.PRECISEFACTDOUBLEMULTINOMIALSERIESSUMSUMX2MY2SUMX2PY2SUMXMY2BITANDBITORBITXORBITLSHIFTBITRSHIFTDELTAGESTEPCOMBINAPERMUTATIONATYPEISFORMULAFORMULATEXTISREFAREASCELLSHEETSHEETSHYPERLINKDOLLARFIXEDENCODEURLTIMEVALUEVALUETOTEXT`
`Lambda`	`LETLAMBDAMAPREDUCESCANBYROWBYCOLMAKEARRAY`
`Engineering`	`NETWORKDAYS.INTLWORKDAY.INTLCONVERTCOMPLEXIMREALIMAGINARYIMABSIMARGUMENTIMCONJUGATEIMSUMIMSUBIMPRODUCTIMDIVIMPOWERIMSQRTIMEXPIMLNIMSINIMCOSBIN2HEXBIN2OCTHEX2BINHEX2OCTOCT2BINOCT2HEX`
`Other`	`BINOMDISTCHIDISTCHIINVCONFIDENCECRITBINOMERF.PRECISEERFC.PRECISEEXPONDISTFINDBGAMMADISTGAMMAINVGAMMALN.PRECISEHYPGEOMDISTMODE.SNGLNORMDISTNORMINVNORMSDISTNORMSINVPERCENTILE.INCPERCENTRANK.INCPOISSONQUARTILE.INCRANK.EQSEARCHBSTDEV.PSTDEV.STDISTTINVVAR.PVAR.SWEIBULLZTEST`

=A1+B1*2

Parameter	Type	Beschrijving
`Error`	`Meaning`
`#DIV/0!`	`Division by zero.`
`#VALUE!`	`A value has the wrong type, for example text where a number is needed.`
`#REF!`	`A reference points to a cell that was deleted or to a sheet that does not exist.`
`#NAME?`	`A function or name is not known.`
`#N/A`	`A lookup found nothing.`
`#NUM!`	`A number is not valid for the function, or the calculation is too deep.`
`#CIRC!`	`The formula depends on itself.`
`#ERROR!`	`The formula could not be understood.`

API

Parameter	Type	Beschrijving
`AllowFileTransfer`	`bool`	Toont de opdrachten voor import en export (standaard true).
`AllowSheetChanges`	`bool`	Laat mensen bladen toevoegen, hernoemen en verwijderen (standaard true).
`Class`	`string`	Extra CSS-klassen die aan het hoofdelement worden toegevoegd.
`FileName`	`string`	Naam die aan geëxporteerde bestanden wordt gegeven, zonder extensie (standaard workbook).
`Height`	`string`	Hoogte van het hele besturingselement als CSS-lengte (standaard 34rem).
`IgnoredWords`	`IReadOnlyCollection<string>`	Woorden die nooit als fout gespeld worden gemeld.
`Label`	`string`	Toegankelijke naam van het raster. Standaard een gelokaliseerde Spreadsheet.
`MaxImportSize`	`Int64`	Grootste bestand dat kan worden geïmporteerd, in bytes (standaard 5.000.000).
`PdfFonts`	`PdfFontSet`
`ProofAsYouType`	`bool`	Markeert cellen met problemen terwijl het blad verandert (bladen met maximaal 50.000 gevulde cellen); de gebruiker kan dit in het menu Review schakelen.
`Proofing`	`bool`	Controleert spelling, grammatica, formules, getallen en totalen: menu Review, werkbalkknop en F7 (standaard true).
`ReadOnly`	`bool`	Geen bewerking, maar selecteren, kopiëren en exporteren werken nog.
`ShowFormulaBar`	`bool`	Toont het naamvak en de formulebalk (standaard true).
`ShowMenuBar`	`bool`	Toont de menubalk (standaard true).
`ShowSheetTabs`	`bool`	Toont de bladtabbladen (standaard true).
`ShowStatusBar`	`bool`	Toont de statusbalk met Sum, Average, Count, Min en Max van de selectie (standaard true).
`ShowToolbar`	`bool`	Toont de opmaak- en bewerkingswerkbalk (standaard true).
`Style`	`string`	Inline stijl die aan het hoofdelement wordt toegevoegd.
`Workbook`	`Workbook`	De te tonen en te bewerken gegevens. Laat weg voor een nieuw leeg werkboek (lees het terug met GetWorkbook()).

Gebeurtenissen

API

Gebeurtenis	Type	Beschrijving
`OnChanged`	`EventCallback`	Geactiveerd nadat het werkboek is gewijzigd: een bewerking, ongedaan maken of opnieuw.
`OnNew`	`EventCallback`
`OnSave`	`EventCallback<OfficeFileSave>`
`OnSaveAs`	`EventCallback<OfficeFileSave>`
`OnSelectionChanged`	`EventCallback<CellRange>`	Geactiveerd wanneer de selectie is verplaatst.

Pakket

Spreadsheet maakt deel uit van RayCraft Complete. Pakket: RayCraft.Blazor.Complete. Het komt uit de privéfeed van RayCraft en vereist een actieve licentie. Zie Aan de slag.

Feedback sturenVersie 1.0.3