files, handling more sheets.
(in points a.,b.,c.) or other files (in point d.), bit array.
1. bit
button on toolbar. The Import button has assigned
action.
2. bit
- If the file can be dropped to the grid to open it. The dropping must be permitted also by
.
event handler.
If the xlsx will be added to existing data or the existing data will be discarded.
Data source for downloading layout for the imported xlsx, use if the source grid does not have xlsx source.
It can be XML string, JSON string or URL. The URL cannot contain characters '<' and '{'.
TreeGrid source tag for the imported xlsx. It should contain the <treegrid> /
tag definition.
Useful to add some other setting to the imported xlsx grid, if the source grid has not have xlsx source.
new 16.1 <Cfg> int
ImportRows
[0]
Permits importing data from xlsx exported from the same grid (grid with the same layout).
0 - Not permitted importing exported data.
1 - Tries to import any opened xlsx/csv data.
2 - Imports only xlsx saved by TreeGrid.
3 - Imports only xlsx saved by TreeGrid with the same grid id.
Can be used with any grid or Gantt chart with fixed layout (columns), but not with sheet.
Imported are these cell attributes: Img, Link, Tip, Span, RowSpan.
Imported are these row attributes: id (if exported to id column), Level, Expanded, Def (if exported to _Def column).
Imported are also row and cell attributes set in ImportAttrs.
Imported are also row attributes set in ImportRowAttrs.
Note that base cell attributes like Type, Range (except from comment), Formula, Button, Icon are not imported!
The not imported attributes should be defined in default row or in column.
Imports only rows, does not change grid layout (columns) and configuration.
If chosen to add the xlsx to the existing grid, the existing rows and cells preserve only these imported attributes!
new 16.1 <Cfg> string[]
ImportAttrs
It can contain only these attributes: Range, Format, TextStyle, TextSize, TextFont,
TextColor, Color, PatternColor, Pattern, BorderLeft, BorderRight, BorderTop, BorderBottom,
Align, VertAlign, Wrap, Indent, Rotate, CenterTo, CanEdit, FormulaCanEdit.
Range attribute is not stored in xlsx, if set Range in ImportAttrs, the Range value is chosen only from the comment, if it contains ValueSeparator or RangeSeparator.
new 16.1 <Cfg> string[]
ImportRowAttrs
It can contain only these attributes: Height, Visible.
new 14.0 <Cfg> string[]
ImportExt
["xlsx"]
Comma separated list of permitted file extensions for both import actions.
Use with OnImportText for custom import of any textual file.
new 15.0 <Cfg> string[]
ImportImg
["Rotate"]
Comma separated list of flags to control importing images in xlsx. Case insensitive.
RowSpan - The cell with image will be spanned vertically to show the whole image. If not set, OverlayImg can be applied.
ColSpan - The cell with image will be spanned horizontally to show the whole image. If not set, OverlayImg can be applied.
Rotate90 - Rotated images will be imported with converted degree 0, 90, 180, 270. If not set, the images are never imported rotated. Ignored if set Rotate.
Rotate - Rotated images will be imported with the original rotation degree. If set with RowSpan / ColSpan, the span will be updated to show the whole image.
new 15.0 <Cfg> int
ImportPassword
[0]
How the locked sheets and workbooks with password will be imported.
0 - password will be removed and the sheet can be unlocked.
1 - password will be stored and the saved sheet will be locked with this password and the changed locks if any. Sheet can be unlocked in TreeGrid.
2 - password will be stored and the saved sheet will be locked with this password and the same locks. Sheet can be unlocked in TreeGrid.
3 - password will be stored and the saved sheet will be locked with this password and the same locks. Sheet cannot be unlocked.
new 15.0 <Cfg> int
ImportLimitCols
[52]
For empty columns after this limit ignores their style and width. If no columns with data follow.
0 means to ignore all empty columns, if no columns with data follow.
new 15.0 <Cfg> int
ImportLimitRows
[1000]
For empty rows after this limit ignores their style and height. If no rows with data follow.
0 means to ignore all empty rows, if no rows with data follow.
new 15.0 <Cfg> int
ImportLimitCells
[1000000]
Hard limit for very large sheets.
If the sheet contains more cells than this limit, the next columns are ignored and their data is lost.
new 13.0 deleted 15.0 <Cfg> bool
ImportWidths
[1]
If column widths will be imported from xlsx.
new 13.0 deleted 15.0 <Cfg> int
ImportHeader
[0]
Count of first rows in xlsx set as Header rows in Head section.
new 13.0 deleted 15.0 <Cfg> int
ImportHead
[0]
Count of first rows in xlsx assigned to the Head section (evaluated after ImportHeader).
new 13.0 deleted 15.0 <Cfg> int
ImportFoot
[0]
Count of last rows in xlsx assigned to the Foot section.
new 13.0 deleted 15.0 <Cfg> int
ImportLeftCols
[0]
Count of first columns in xlsx assigned to the LeftCols section.
new 13.0 deleted 15.0 <Cfg> int
ImportRightCols
[0]
Count of last columns in xlsx assigned to the RightCols section.
new 13.0 deleted 15.0 <Cfg> string[]
ImportAttrs
["all"]
Comma separated list of attribute names which will be recognized/applied during the import (if present where relevant).
The default is "all", i.e. all the supported attributes.
For example: ImportAttrs="Value,Align,Color,EFormula"
new 13.0 <Actions>
Import
Attached to event OnClickButtonImport
Shows open dialog and lets a user to choose xlsx file to open it in grid.
It asks a user to merge the files or discard the old file.
new 13.0 <Actions>
ImportAdd
Not attached to any event
Shows open dialog and lets a user to choose xlsx file to open it in grid. It merges the old and new files.
new 13.0 <Actions>
ImportDiscard
Not attached to any event
Shows open dialog and lets a user to choose xlsx file to open it in grid. It discards the old file.
new 14.0 API event void
OnImportText
(TGrid grid, string data, object file, int type)
It is called when defined ImportExt, for both import actions.
Use if for custom import of any textual file, this event handler must parse the input data and pass it to TreeGrid, for example:
Grids.OnImportText = function(G,csv){ var json = CsvToJson(G,csv); /* Custom parse function */ G.Source.Data.Url = null; G.Source.Data.Data = json; G.ReloadBody(); }
data is the file content in string
file is JavaScript File object if loaded from dropped or opened file. It has set e.g. the name property as the file name.
type is 1 to merge files or 2 to discard the old file.
new 13.0 API event bool
OnImportData
(TGrid grid, object file, int type)
Called before the xlsx file is opened in grid, return true to not open it.
It is called only if the file is dropped or opened in grid, not if the file is loaded from Data_Url.
file is JavaScript File object. It has set e.g. the name property as the file name.
type is 1 to merge files or 2 to discard the old file.
new 13.0 API event bool
OnImportedData
(TGrid grid, object file, object buffer, int type)
Called after the xlsx file is opened and set to grid data source grid.Source.Data.File. It is called before the grid is reloaded from this data.
file is JavaScript File object if loaded from dropped or opened file. It has set e.g. the name property as the file name.
file is string url if loaded from Data_Url.
buffer is JavaScript ArrayBuffer object with raw data loaded from file.
type is 1 to merge files or 2 to discard the old file or 0 if loaded directly from Data_Url.
new 13.0 API event bool
OnImportDataError
(TGrid grid, int error, string message, object file, object data)
Called if the xlsx file cannot be opened in grid.
error is error number:
-1 - Missing TreeGrid module Import - the module Import is included in Sheet component only.
-2 - Unknown or incorrect file format.
-3 - Missing TreeGrid external module JSZip. By default it is located in Grid/External/jszip.min.js.
-4 - Error parsing file. message contains more information about the error.
-5 - Error loading file. message and data contain more information about the error.
message is string error message for the error.
file is JavaScript File object if loaded from dropped or opened file. It has set e.g. the name property as the file name.
file is string url if loaded from Data_Url.
data is JavaScript ArrayBuffer object with raw data loaded from file. Or it is error object, if the file cannot be loaded. Or it is null if missing TreeGrid module.
new 13.0 API method void
ImportData
(object file, int add)
Loads given xlsx file to grid. file is JavaScript File object.
add controls add or discard action and showing confirmation message, if not set,
ImportAdd is used.
new 16.1 API event void
OnImportCell
(TGrid grid, object imrow, string imcol, object row, string col)
Called when importing one cell from xlsx to data when set ImportRows.
imrow, imcol is the converted full cell from xlsx being imported.
row, col is the cell that contains value and attributes that will be imported.
row is JSON object that will be set in source data (grid.Source.Data.Data) to reload TreeGrid body from it.
row and imrow do not have Def attribute, they has set all its attributes directly.
It is possible to modify the row to change the imported cell, like row[col] = "new value" or row[col+"Tip"] = "new comment".
new 16.1 API event void
OnImportCopyCell
(TGrid grid, TRow srcrow, string srccol, object row, string col)
Called when copying cell from existing data to data that will be reloaded for import. When adding imported data to existing data.
srcrow, srccol is the cell in existing data.
row, col is the cell that contains value and attributes that will be imported.
row is JSON object that will be set in source data (grid.Source.Data.Data) to reload TreeGrid body from it.
row does not have Def attribute, it has set all its attributes directly.
It is possible to modify the row to change the imported cell, like row[col] = "new value" or row[col+"Tip"] = "new comment".
Sheet manipulation
new 13.0 <Space cell> string
Type
Type="Sheets
"
Shows list of sheet names in actually opened xlsx.
It creates one space cell for every sheet.
The sheets can be moved, copied and deleted by default, the tabs have predefined
CanEdit="1" CanFocus="2" FocusCell="" CanCopy="1" CanDelete="1" and it presets also DragTab if not defined.
If the cell value is 2, it creates one combo with popup list with all sheets and the sheets cannot be moved or deleted in the combo.
To add new sheets defined new button like <Tabber Space="-1" Cells="Sheets,New" SheetsType="Sheets" SheetsWidth="50" NewButtonText="+" NewOnClick="AddSheet"/>
The tabs with sheets can be controlled also as standard tabs, as described at Radio / tab button .
new 13.0 <Space cell> bool
ShowHidden
[0]
If the Sheets Type cell will display also hidden sheets.
new 13.0 <Actions>
AddSheet
Not attached to any event
Adds new empty sheet to the end of sheets and shows it in grid.
new 16.0 <Actions>
ShowSheets
Not attached to any event
Shows list with hidden sheets to choose one sheet to show and load.
new 16.0 <Actions>
HideSheet
Not attached to any event
Hides actual sheet, if it is not the only visible.
new 13.0 API event bool
OnLoadSheet
(TGrid grid, string name, bool hidden)
Called before given sheet is shown in grid. Return true to not load the sheet.
Showing sheet reloads the whole grid by Reload method, therefore the grid object cannot be used later.
hidden is true if the sheet is loaded hidden, usually as formula source for active sheet.
new 13.0 API method bool
LoadSheet
(string name, bool save)
Loads given sheet into grid. If save is true, saves all changes done in grid to actual sheet.
Showing sheet reloads the whole grid by Reload method, therefore the grid object cannot be used later.
Returns true if reloading started successfully. The reloading is asynchronous, the sheet is not loaded in grid when function returns.
new 13.3 API method TGrid
LoadSheetHidden
(string name)
Loads given sheet synchronously without rendering it and returns the created grid.
If the sheet is already loaded, just returns its grid object. The grid object is normally placed to Grids array.
All the hidden sheets are deleted when reloading the active sheet.
It can be used to access data on other sheets than the actual one.
For example to upload data from all sheets in xlsx at once to server:
Grids.OnUpload = function(G,xml,row,auto){
if(row||auto) return;
var S = G.GetSheets(), A = [];
for(var i=0;i<S.length;i++) A[A.length] = "<Grid id='"+S[i]+"'>" + G.LoadSheetHidden(S[i]).GetXmlData("Body,NoGrid") + "</Grid>";
return "<Grids>"+A.join("")+"</Grids>";
}
new 13.0 API method string[]
GetSheets
(bool hidden = 0)
Returns list of sheet names in actual opened xlsx. If set hidden, it returns also hidden sheets.
new 13.0 API method bool
HasSheet
(string name, bool hidden = 0)
Returns if given sheet exists in actual xlsx. If set hidden, checks also hidden sheets.
new 13.0 API method string
GetActiveSheet
( )
Returns name of active sheet in actual opened xlsx.
new 13.0 API method string
GetUniqueSheet
(string name)
Changes and returns given sheet name to be unique in actual opened xlsx.
new 13.0 API method void
DeleteSheet
(string name)
Deletes given sheet. If the sheet is active, loads next sheet or for only sheet it loads new empty sheet.
new 13.0 API method string
RenameSheet
(string name,string newname)
Renames sheet name to newname. If the newname already exists in grid, changes it to be unique.
Returns the name that was set to sheet.
new 13.0 API method void
MoveSheet
(string name,string next, bool copy)
Moves or copies the sheet name left side to the sheet next. If next is null, moves or copies the sheet as the last sheet.
If copied actual sheet, it activates the new copied sheet.
new 16.0 API method bool
ShowSheet
(string name,bool hide = 0)
Shows (hide=0) or hides (hide=1) the sheet name.
If shows the sheet, it also loads it.
If hides active sheet, it loads the next one.
Returns true if the sheet changed its visibility, otherwise false.