# Table Operation

luckysheet stores all operations in the history to undo and redo. If allowupdate is set to true and updateURL is available in initial, operations will be updated on the backend in real-time via webSocket. And every one can edit same sheet on the same time.

Source code [src/controllers/server.js] (https://github.com/mengshukeji/Luckysheet/blob/master/src/controllers/server.js) The module shows us the function of background saving.

In general, shared editing(or collaborative editing) is controled by the account system created by developers to control permissions.

The following are all types of operations that support transferring to the background. In this case, I use mongodb as a storage example to explain how front-end and back-end interacts with eachother.

Pay attention, i in the object is the index of the sheet rather than order.

# Cell refresh

# single cell refresh

Format：

{
    "t": "v",
    "i": "Sheet_0554kKiKl4M7_1597974810804",
    "v": {
        "v": 233,
        "ct": { "fa": "General", "t": "n" },
        "m": "233"
    },
    "r": 0,
    "c": 1
}

Explanation：

Parameter Explanation

t Operation type symbol

i The index value of the current sheet

v Cell value, refer to 单元格属性表

r Row number of cell

c The column number of the cell
Backend update：

The cell update is mainly to update the luckysheetfile[i].celldata parameter, which is an array:
```
[
    {r:0, c:1, v: "value1"},
    {r:10, c:11, v:"value2"},
    {r:10, c:11, v:{f:"=sum", v:"100"}}
]
```
Store the values in all the cells in the sheet, Luckysheet will create a new table data according to the number of rows and columns in luckysheetfile[i].row and luckysheetfile[i].column when it is created, and then use data[ r][c]=v to fill the table data, empty data cells are represented by null.

When saving the data posted by the frontend, the backend needs to convert the parameters to the format of {r:0, c:1:v:100} first, and then update the field of luckysheetfile[i].celldata, if the cell exists the cell is updated, if not, it is added, and if the cell exists but v is null, the cell is deleted.
Frontend view：

You can modify the value of any cell, and then go to the chrome console to view the operation of "t"=="v".

# Config operation

Format：

{
  "t": "cg",
  "i": 3,
  "v": {
      "7": 192
  },
  "k": "rowlen"
}

Explanation：

Parameter Explanation

t Operation type symbol

i The index value of the current sheet

v The internal key-value that needs to be updated

k Operation key name
Backend update：

Update luckysheetfile[i].config.[k][v.key] = v.value, if k does not exist in config, then create a new k attribute and set it to empty, If there is no v.key in k, create a new v.key and update v.value.
1. Examples of modifying row height:
  - Enter: {"t":"cg","i":3,"v":{"3":10, "5":70, "10":100},"k":" rowlen"}
  - Update: luckysheetfile[3].config.["rowlen"]["3"] = 10
2. Examples of modifying column width:
  - Enter: {"t":"cg","i":1,"v":{"20":74, "15":170, "6":40},"k":" columnlen"}
  - Update: luckysheetfile[1].config.["columnlen"]["20"] = 74
3. Examples of merged cells:
  - Enter: {"t":"cg","i":1,"v":{"5_10":{row:[1,3], column:[3,5]},"k":" merge "}
  - Update: luckysheetfile[1].config.["merge"]["5_10"] = {row:[1,3], column:[3,5]}

# General save

Format：

{
  "t": "all",
  "i": 3,
  "v": {
      "v": 1,
      "m":1,
  },
  "k": "freezen",
  "s": false
}

Explanation：

Parameter	Explanation
t	Operation type symbol
i	The index value of the current sheet
v	The internal key-value that needs to be updated
k	The `value` in the key-value that needs to be saved
s	If it is `true` then `v` is saved as a string, otherwise it is saved according to the object

Backend update：

luckysheetfile[3].[k]= v If s is true, it is luckysheetfile[3].[k]= JSON.stringify(v)
1. Pivot table:
  - Enter: {"t":"all","i":1,"v":{………},"k":"pivotTable", "s": false}
  - Update: luckysheetfile[1].["pivotTable"] = {………}
2. Freeze rows and columns:
  - Enter: {"t":"all","i":3,"v":{………},"k":"freezen", "s": false}
  - Update: luckysheetfile[3].["freezen"] = {………}
3. Filter range:
  - Enter: {"t":"all","i":3,"v":{………},"k":"filter_select", "s": true }
  - Update: luckysheetfile[3].["filter_select"] = JSON.stringify ({………})
4. Sheet name:
  - Enter: {"t":"all","i":1,"v":"doc","k":"name", "s": false}
  - Update: luckysheetfile[1].["name"] = "doc"
5. Sheet color:
  - Enter: {"t":"all","i":2,"v":"#FFF000","k":"color", "s": false}
  - Update: luckysheetfile[2].["color"] = "#FFF000"

# Function chain operation

Format：

{
  "t": "fc",
  "i": 0,
  "v": {
      "r": 3,
      "c": 7,
      "index": 0,
      "func": [
          true,
          187282,
          "=SUM(E4:G4)"
      ]
  },
  "op": "add",
  "pos": 0
}

Explanation：

Parameter	Explanation
t	Operation type symbol
i	The index value of the current sheet
v	Object value, the internal fields of the object do not need to be updated separately, so save as text
op	Operation type, `add` is add, `update` is update, and `del` is delete
pos	Updated or deleted function location

Backend update：

calcChain is an array
- If the value of op is add then add to the end luckysheetfile[i].calcChain.push (v)，
- If the value of op is update then update luckysheetfile[i].calcChain[pos]= v，
- If the value of op is del then delete luckysheetfile[i].calcChain.splice(pos, 1)。
Frontend view：

You can modify the value of any cell, and then go to the chrome console to view the operation of "t"=="v".

# Row and column operations

# Delete rows or columns

Format：

{
  "t": "drc",
  "i": 3,
  "v": {
      "index": 6,
      "len": 2
  },
  "rc": "r"
}

Explanation：

Parameter		Explanation
t		Operation type symbol
i		The index value of the current sheet
rc		Row operation or column operation, the value `r` stands for row, and `c` stands for column
v	index	Delete from which row or column
	len	Number of rows or columns deleted

Backend update：

If the value of rc is 'r' then delete the row, if the value of rc is 'c' then delete the column, eg rc='r', index=4, len= 5, means to delete the next 5 lines (4, 5, 6, 7, 8) from the 4th line.

Mainly operate on the cells in luckysheetfile[i].celldata, delete the qualified cells described in the parameters and update the row and column values of other cells. Taking the above as an example, first find the r in the cell Delete all the cells with values from 4 to 8, and then subtract the value of 5 from the original cell number 9 and later, and finally subtract 5 from luckysheetfile[i].row. If the v value is "#__qkdelete#" (without quotes), then this is the cell to be deleted.
Front view：

You can delete rows or columns, and then go to the chrome console to view the operation of "t"=="drc".

# Add rows or columns

Format：

{
  "t": "arc",
  "i": 0,
  "v": {
      "index": 5,
      "len": 10,
      "data": []
  },
  "rc": "c"
}

Explanation：

Parameter		Explanation
t		Operation type symbol
i		The index value of the current sheet
rc		Row operation or column operation, the value `r` stands for row, and `c` stands for column
v	index	Start from which row or column
	len	How many rows or columns to add
	data	New row or column content

Backend update：

If the value of rc is r, add a new row. If the value of rc is c, add a new column. For example, rc=r, index=4, len=5 then it means increase 5 lines from line 4. If data is empty, add an empty line. If data is not empty, use the array in data to add a new line.

Mainly operate on the cells in luckysheetfile[i].celldata. Taking the above as an example, first add 5 to luckysheetfile[i].row, and then add r greater than 4 to the entire cellrValue +5, if data is empty, add an empty line to end, if data is not empty, convert the two-dimensional array data to {r:0,c:0,v:100} Format and added to celldata, the pseudo code for conversion is as follows:
```
var ret = [];
for(var r=0;r<data.length;r++){
    for(var c=0;c<data[0].length;c++){
        if(d[r][c]==null){
            continue;
        }
        ret.push({r:r+5, c:c, v: data[r][c]});
    }
}
return ret;
```
Front view： You can add rows or columns, and then go to the chrome console to view the operation of "t"=="arc". If you want to view the operation with the value of data, you can delete some rows or columns, and then undelete (Ctrl+Z), you can see.

# Filter operating

# Select filter condition

Format：

{
  "t": "f",
  "i": 0,
  "v": "{\"caljs\":{},\"selected\":{\"Qingdao\":\"1\",\"Guangxi\":\"1\",\"Chongqing\":\"1\"},\"rowhidden\":{\"1\":0,\"2\":0,\"3\":0,\"4\":0,\"6\":0,\"7\":0,\"8\":0,\"9\":0,\"10\":0,\"11\":0,\"12\":0,\"13\":0,\"14\":0,\"15\":0,\"16\":0,\"17\":0,\"18\":0,\"19\":0,\"21\":0,\"22\":0,\"24\":0,\"25\":0,\"26\":0,\"27\":0,\"28\":0,\"29\":0,\"30\":0,\"31\":0,\"32\":0,\"33\":0,\"34\":0,\"35\":0}}",
  "op": "upOrAdd",
  "pos": 1
}

Explanation：

Parameter	Explanation
t	Operation type symbol
i	The index value of the current sheet
v	Object value, the internal fields of the object do not need to be updated separately, so save as text
op	The operation type `upOrAdd` is update, if it does not exist, it is added, and `del` is delete
pos	Updated or deleted `option` location

Backend update：

Update luckysheetfile[i].filter = {pos: v }, the value of v is a string in JSON format. filter is a key-value pair, key is the index value (in characters) of the option position, and v is a json string parameter. filter represents a set of filter conditions.

# Clear filter

Format：

{
  "t": "fsc",
  "i": 0,
  "v": null
}

Backend update：

Clear luckysheetfile[i]. filter = null ， luckysheetfile[i]. filter_select = null。

# Restore filter

Format：

{
  "t": "fsr",
  "i": 0,
  "v": {
      "filter": [],
      "filter_select": {}
  }
}

Backend update：

Clear luckysheetfile[i]. filter = v.filter， luckysheetfile[i]. filter_select = v. filter_select。

# Sheet operation

# New sheet

Format：

{
  "t": "sha",
  "i": null,
  "v": {
      "name": "Sheet4",
      "color": "",
      "status": "0",
      "order": 3,
      "index": 3,
      "data": [],
      "config": {},
      "pivotTable": null,
      "isPivotTable": false
  }
}

Explanation：

Parameter		Explanation
t		Operation type symbol
i		The index value of the current sheet
v	name	The index value of the sheet jumped after hiding
	color	Sheet color
	status	Active state
	order	Sheet order
	index	Sheet Index
	celldata	Cell dataset
	config	Setting
	pivotTable	Pivot table settings
	isPivotTable	Whether the pivot table

Backend update：

Add a line (a document) to the database.

# Copy sheet

Format：

{
  "t": "shc",
  "i": "New sheet location",
  "v": {
      "copyindex": "copyindex"
  }
}

Backend update：

Copy the sheet index value in the table, set it to copyindex and add it to the database, set the index of the new document to the value corresponding to i.

# Delete sheet

Format：

{
  "t": "shd",
  "i": null,
  "v": {
      "deleIndex": 0
  }
}

Explanation：

Parameter Explanation

t Operation type symbol

v deleIndex The sheet index to be deleted
Backend update：

Delete the sheet whose index is the value corresponding to deleIndex.

# restore from a deleted sheet

format：

{
  "t": "shre",
  "i": null,
  "v": {
      "reIndex": "0"
  }
}

Explanation：

Parameter Explanation

t Operation type symbol

v deleIndex 需要恢复的sheet索引
Backend update：

restore the sheet whose index is the number of reIndex.

# Position

Format：

{
  "t": "shr",
  "i": null,
  "v": {
      "0": 1,
      "1": 0,
      "2": 2,
      "3": 3,
      "4": 4,
      "5": 5,
      "6": 6,
      "7": 7,
      "Sheet_6az6nei65t1i_1596209937084": 8
  }
}

Explanation：

Parameter	Explanation
t	Operation type symbol
v	Set the sorting of Sheet as a key-value pair, `key` represents the index of the sheet, and `value` represents the order value. The format is: `{"1": 3, "2":1, "0": 2, "3":0}`

Backend update：

For the page where the index of the sheet is equal to the key, set its order attribute to the value. Examples:

luckysheetfile[key1].order = value1 luckysheetfile[key2].order = value2 luckysheetfile[key3].order = value3

# switch to the specified sheet

format：

{
  "t": "shs",
  "i": null,
  "v": 1
}

Explanation：

Parameter Explanation

t Operation type symbol

v index of the specified sheet
Backend update：

setting the status = 1, when the index of a sheet is eaqul to v：

luckysheetfile[v].status = 1

# Sheet attributes (hide or show)

Format：

{
  "t": "sh",
  "i": 0,
  "v": 1,
  "op": " hide",
  "cur": 2
}

Explanation：

Parameter	Explanation
t	Operation type symbol
i	The index value of the current sheet
op	Operation options include hide and show
v	Hide if `hide` is `1`, show if `0` or empty
cur	After hiding, set sheet which index corresponding to the `cur` to the active state

Backend update：

The hide field of the root path of the sheet corresponding to i is updated to v. When hidden, the status value is 0. When displayed, it is 1. If hidden, the sheet which cur corresponds to index, its status is updated to 1.

# Table information change

# Table name

Format：

{
  "t": "na",
  "i": null,
  "v": "Data"
}

Explanation：

Parameter Explanation

t Operation type symbol

v The name of the table
Backend update：

Update the table name in the database according to gridkey.

# Thumbnail

Format：

{
  "t": "thumb",
  "img": "base64",
  "curindex": "curindx"
}

Explanation：

Parameter Explanation

t Operation type symbol

img Thumbnail of current table, base64 string

curindex The current sheet opened by default
Backend update：

According to gridkey, update the thumbnail field of the table in mysql to the img value, and update the status field of the sheet whose index is the curindex value to 1, and set the status value of other sheets to 0.

# Chart(TODO)

There are four types of chart operations: add new chart -"add", move chart position-"xy", zoom chart-"wh", and update chart configuration-"update".

# new chart

format：

{
    "t": "c",
    "i": 0,
    "op":"add",
    "v": {
        "chart_id": "chart_p145W6i73otw_1596209943446",
        "width": 400,
        "height": 250,
        "left": 20,
        "top": 120,
        "sheetIndex": "Sheet_6az6nei65t1i_1596209937084",
        "needRangeShow": true,
        "chartOptions": {
            "chart_id": "chart_p145W6i73otw_1596209943446",
            "chartAllType": "echarts|line|default",
            "rangeArray": [ { "row": [ 0, 4 ], "column": [ 0, 7 ] } ],
            "rangeColCheck": { "exits": true, "range": [ 0, 0 ] },
            "rangeRowCheck": { "exits": true, "range": [ 0, 0 ] },
            "rangeConfigCheck": false,
            "defaultOption": {
                "title": {
                    "show": true,
                    "text": "default title"
                }
            }
        },
        "isShow": true
    }
}

Explanation：

Parameter Explanation

t Operation type symbol

i The index value of the current sheet

op Operation options include hide and show

v configuration information of charts
Backend update：

update the chart settings in the current sheet,ifluckysheetfile[i].chart is null，the array should be [] on initial.
```
luckysheetfile[0].chart.push(v)
```

# move chart position

format：

{
    "t": "c",
    "i": 0,
    "op":"xy",
    "v": {
        "chart_id": "chart_p145W6i73otw_1596209943446",
        "left": 20,
        "top": 120
    }
}

Explanation：

Parameter Explanation

t Operation type symbol

i The index value of the current sheet

op Operation options include hide and show

v configuration information of charts

Backend update：

update the chart settings in the current sheet

luckysheetfile[0].chart[v.chart_id].left = v.left;
luckysheetfile[0].chart[v.chart_id].top = v.top;

# zoom chart

format：

{
    "t": "c",
    "i": 0,
    "op":"wh",
    "v": {
        "chart_id": "chart_p145W6i73otw_1596209943446",
        "width": 400,
        "height": 250,
        "left": 20,
        "top": 120
    }
}

Explanation：

Parameter Explanation

t Operation type symbol

i The index value of the current sheet

op Operation options include hide and show

v configuration information of charts

Backend update：

update the chart settings in the current sheet

luckysheetfile[0].chart[v.chart_id].left = v.left;
luckysheetfile[0].chart[v.chart_id].top = v.top;
luckysheetfile[0].chart[v.chart_id].width = v.width;
luckysheetfile[0].chart[v.chart_id].height = v.height;

# change the configuration of charts

format：

{
    "t": "c",
    "i": 0,
    "op":"update",
    "v": {
        "chart_id": "chart_p145W6i73otw_1596209943446",
        "width": 400,
        "height": 250,
        "left": 20,
        "top": 120,
        "sheetIndex": "Sheet_6az6nei65t1i_1596209937084",
        "needRangeShow": true,
        "chartOptions": {
            "chart_id": "chart_p145W6i73otw_1596209943446",
            "chartAllType": "echarts|line|default",
            "rangeArray": [ { "row": [ 0, 4 ], "column": [ 0, 7 ] } ],
            "rangeColCheck": { "exits": true, "range": [ 0, 0 ] },
            "rangeRowCheck": { "exits": true, "range": [ 0, 0 ] },
            "rangeConfigCheck": false,
            "defaultOption": {
                "title": {
                    "show": true,
                    "text": "default title"
                }
            }
        },
        "isShow": true
    }
}

Explanation：

Parameter Explanation

t Operation type symbol

i The index value of the current sheet

op Operation options include hide and show

v configuration information of charts
Backend update：

update the chart settings in the current sheet
```
luckysheetfile[0].chart[v.chart_id] = v;
```

# Backend return format

Data format returned by websocket backend

{
     createTime: command sending time
     data:{} modified command
     id: "7a" websocket id
     returnMessage: "success"
     status: "0" 0 tells the front end to modify according to the data command 1 meaningless
     type: 0: connection is successful, 1: send to the currently connected user, 2: send information to other users, 3: send selection location information, 999: user disconnected
     username: username
}

← Format attributes API →

Parameter	Explanation
t	Operation type symbol
i	The index value of the current sheet
v	Cell value, refer to 单元格属性表
r	Row number of cell
c	The column number of the cell

Parameter	Explanation
t	Operation type symbol
img	Thumbnail of current table, base64 string
curindex	The current sheet opened by default