Explain json data in spreadsheets: serialization and deserialization

Keywords: Javascript

From XML to JSON

Under the common B/S architecture of current application development, we will encounter many scenarios requiring front and rear data transmission. In the process of this transmission, the format of data transmission, whether the method is fast and convenient, and whether the writing method is simple and easy to learn have become the problems that programmers should consider when developing.

In 1996, the W3C (World Wide Web Consortium) officially announced the XML 1.0 standard,

XML uses a standard format to provide a unified standard for data description and data exchange for Web-based applications. Unlike HTML, which focuses on "how to display files in the browser", XML focuses more on "how to describe data in a structured way".

(note that XML is not a programming language, but a cross language data format.)

XML itself is not complex, but after adding more than 20 standards such as DTD, XSD, XPath and XSLT formulated by W3C, this simple data exchange format becomes complex. Programmers can only have big heads when they encounter them. After studying hard for more than half a month, it's not easy to say that you know everything.

At this time, another steam engine driving the progress of technology was also ignited - Ajax technology began to be popular, reflecting the shortcomings of XML that can not be ignored more and more. The implementation of XML is based on DOM tree, and the implementation details of DOM in various browsers are different, so the cross browser compatibility of XML is not good. At this time, a new data load format needs to be integrated into HTML pages to meet the requirements of Ajax.

Finally, in 2002, the eighth year after the birth of XML, Douglas Crockford began to use JSON, a lightweight data exchange format.

After the first JSON message was sent, the most surprising thing is that this is not a new data format, it is JavaScript.

document.domain = 'fudco';parent.session.receive( { to: "session", do: "test", text: "Hello world" } )

Since the data content itself is JavaScript, there is no need to do any additional parsing. Everything can be solved by using JS compiler.

Because JSON is very simple, it soon became popular in the Web world and became the ECMA standard. Almost all programming languages have libraries for parsing JSON. In JavaScript, we can use JSON directly because JavaScript has built-in JSON parsing. To turn a JavaScript object into JSON is to serialize the object into a string in JSON format, so that it can be transmitted to other computers through the network. If we receive a string in JSON format, we just need to deserialize it into a JavaScript object, and we can use this object directly in JavaScript.

Serialization and deserialization of Json

Just as a dish needs to be put on a plate to the customer after it is ready, so does the data transmission at the front and back ends. Through the specified format, the transmitted object is serialized into a binary data stream, and then the content of the data stream is transformed into the corresponding data object through deserialization.

Data form and transformation in JSON

In JSON, data has the following forms:

  • Object: a "key / value" without order, in the format of

  • Array: used to set the numerical order. The format is as follows:

  • String: any number of Unicode characters in the format of

There are three ways to serialize and deserialize data:

  • Using the JavaScript serializer class
  • Using the DataContractJsonSerializer class
  • Using JSON.NET class libraries

Take the JavaScript serializer class as an example,

//Create user list
List<UserInfo> userList = new List<UserInfo>();
userList.Add(new UserInfo() { ID = 1, Name = "Zhang San", CreateTime = DateTime.Now });
userList.Add(new UserInfo() { ID = 2, Name = "Li Si", CreateTime = DateTime.Now });
userList.Add(new UserInfo() { ID = 2, Name = "Wang Wu" });
//Create a JavaScript serializer object
JavaScriptSerializer serializer = new JavaScriptSerializer();
//Serialize user lists into JSON
string serializedResult = serializer.Serialize(userList);
//Deserialize JOSN into user list
List<UserInfo> deserializeResult = serializer.Deserialize<List<UserInfo>>(serializedResult);

You can directly serialize the data content by calling the corresponding method.

Do you think it's over here? Of course not. In practical application, there is no difficulty in processing the data itself. The real problem to be considered is the additional attributes and settings of the data itself. Taking ourselves as an example, the real demand of customers for JSON data transmission in pure front-end spreadsheets is that this data needs to ensure the complete transmission of all visual contents.

JSON data processing in pure front-end tables

When actually processing user requirements, after setting the cell as shown in the figure below, users will not only have numbers in the cell, but also encounter the cell's own style, user-defined function, user-defined format, user-defined function Mini chart, user-defined label, and user-defined row screening.

When we open the relevant code, we can clearly see that these cell settings in the format have been saved.

In this figure, we can see that different types of data content can complete the process of serialization and deserialization. In the process of using custom serialization, check the relevant code. The core of serialization is the typeName field in the process of calling toJSON function. For example, such name can be associated with window object. While anti serialization, call the getTypeFromString function to get the type name and construct the type instance object, then call the fromJSON method on the type instance.

In addition, there are many other attribute contents. Examples of other style settings are listed below:

Background picture:

//This example sets the backgroundImageLayout property.
var style = new GC.Spread.Sheets.Style();
style.backColor = "lightgreen";
style.backgroundImage = "/css/images/quarter1.png";
style.backgroundImageLayout  = GC.Spread.Sheets.ImageLayout.center;

Watermark settings:

//This example sets the cell fill of the watermark.
var type = new GC.Spread.Sheets.Style();
type.watermark = "User name";
type.cellPadding = "20";
type.labelOptions = {alignment:GC.Spread.Sheets.LabelAlignment.topLeft, visibility: GC.Spread.Sheets.LabelVisibility.visible};
activeSheet.setStyle(0, 1, type);
activeSheet.getRange(0, -1, 1, -1, GC.Spread.Sheets.SheetArea.viewport).height(60);
activeSheet.getRange(-1, 1, -1, 1).width(150);
var combo = new GC.Spread.Sheets.CellTypes.ComboBox();
combo.items([{ text: "Oranges", value: "11k" }, { text: "Apples", value: "15k" }, { text: "Grape", value: "100k" }]);
activeSheet.setCellType(2, 1, combo, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.getCell(2, 1, GC.Spread.Sheets.SheetArea.viewport).watermark("ComboBox Cell Type").cellPadding('10 10 20 10');
activeSheet.getCell(2, 1, GC.Spread.Sheets.SheetArea.viewport).labelOptions({alignment: GC.Spread.Sheets.LabelAlignment.bottomCenter, foreColor: 'yellowgreen', font: 'bold 15px Arial'});
activeSheet.getRange(2, -1, 1, -1, GC.Spread.Sheets.SheetArea.viewport).height(60);

Theme font:

//This example uses the themeFont attribute.
var style = new GC.Spread.Sheets.Style();
style.formatter = "0.000%";
style.themeFont = "Body";

There are also many settings for cells. These style contents can be completely saved and transmitted as json data, which brings the convenience of real table json data transmission.

The following problems should be noted during use:

  • Set the complete type name string for the typeName field (including namespace if any).
  • If the custom type has circular dependency, or you want to reduce the size of JSON data, or you have other higher-level requirements, your custom type needs to override the toJSON and fromJSON methods.
  • If the custom type is defined in a closure, in other words, you don't want to define the custom type on the window object. You need to override the getTypeFromString function to manually parse the string of the type.

Code example:

 GC.Spread.Sheets.getTypeFromString = function(typeString) {
        switch (typeString) {
            case "MyFormatter":
                return MyFormatter;
            case "MyRowFilter":
                return MyRowFilter;
                return oldFun.apply(this, arguments);

MyTag.prototype.toJSON = function() {
    return {
        typeName: this.typeName, //necessary
        name: this.name,
        age: this.age
MyTag.prototype.fromJSON = function(settings) {
    if (settings.name !== undefined) {
        this.name = settings.name;
    if (settings.age !== undefined) {
        this.age = settings.age;


This paper introduces the story of data transmission from XML to JSON in detail, as well as the working principle of serialization and deserialization of JSON. At the same time, it takes you to understand how to fully realize the data serialization and deserialization of the whole content in the front-end spreadsheet.

The follow-up will also bring you more interesting or serious content~

I think it's good. Give me a compliment before you go.

Posted by beta0x64 on Mon, 15 Nov 2021 23:43:59 -0800