The first method of JSON data transmission - processing date format with OADate - 葡萄城技术团队

As a lightweight data interchange format, JSON is usually used to store and represent data in a text format that is completely independent of the programming language. Its hierarchical structure is concise and clear, which is easy for people to read and write. In addition, it becomes easier for machines to write and generate, which can effectively improve the efficiency of network transmission. These factors make JSON an ideal data exchange language.

Due to the simplicity of using JSON, this convenient form of transport quickly took the Web world by storm and became an ECMA standard. Almost all programming languages have libraries for parsing JSON, and in JavaScript, we can use JSON directly because JavaScript has built-in JSON parsing. Converting a JavaScript object into JSON is to serialize the object into a JSON-formatted string so that it can be transmitted to other computers over 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.

Use of JSON

One of the most common uses of JSON is to read JSON data from a web server (either as a file or as an HttpRequest), convert the JSON data to a JavaScript object, and then use that data in a web page.

Create a JavaScript string containing JSON syntax:

 var txt = '{ "employees" : [' +      
'{ "firstName":"John" , "lastName":"Doe" },' +        
'{ "firstName":"Anna" , "lastName":"Smith" },' +       
'{ "firstName":"Peter" , "lastName":"Jones" } ]}';

Since JSON syntax is a subset of JavaScript syntax, the JavaScript function eval() can be used to convert JSON text to JavaScript objects.

The eval() function uses a JavaScript compiler that parses JSON text and produces JavaScript objects. The text must be enclosed in parentheses to avoid syntax errors:

 var obj = eval ("(" + txt + ")");

Other common methods include:

1. Convert map to json

 JSONObject json = new JSONObject(map);

2. Convert String to json

 JSONObject.parseObject(StringText)

3. Convert List to json

 JSONArray json = new JSONArray(list);

4. Convert json to List

 list = JSONObject.parseArray(list,String.class)

But in fact, when we transmit data, in addition to our common data formats such as arrays, "time" is also an important type of data transmission.

Handling of OADate

There is a joke that the end of enterprise data is the data table, and the important item in the data table is the time data.

I believe that friends who have used the date format and have exported json data should not be difficult to find that when the value of the cell is a date, the date data we export will be stored as OADate.

(The picture comes from the Internet)

Usually we use OADate to solve date serialization and time zone issues, so we use this special way to save dates. But in many cases, we need to do some processing on date data, but unfortunately we don't know how to convert it.

Today - here it comes, here it comes, the grapes come with the tutorial.

Next, let's solve the problem of JSON date format data processing, and there are solutions for different programming environments.

First let's look at the front end.

 function fromOADate(date) {
            var oaDateReg = new RegExp('^/OADate\\(([-+]?(\\d+(\\.\\d*)?|\\.\\d+)([eE][-+]?\\d+)?)\\)/\\s*

);
            if (typeof date === "string" && oaDateReg.test(date)) {
                var oadate = parseFloat(date.match(oaDateReg)[1]);
                var ms = (oadate * 86400000 * 1440 - 25569 * 86400000 * 1440 + new Date((oadate - 25569) * 86400000).getTimezoneOffset() * 86400000 ) / 1440;
                return new Date(ms);
            }else{
                return date;
            }
        }

In addition to the above-mentioned content, there is also a tricky way to perform a conversion through tag:

 sheet.tag("/OADate(44542)/");
var date = sheet.tag();

In the back-end java environment:

 long d = 44542;
        double  mantissa = d - (long) d;
        double hour = mantissa*24;
        double min =(hour - (long)hour) * 60;
        double sec=(min- (long)min) * 60;

        SimpleDateFormat myFormat = new SimpleDateFormat("dd MM yyyy");
        Date baseDate = myFormat.parse("30 12 1899");
        Calendar c = Calendar.getInstance();
        c.setTime(baseDate);
        c.add(Calendar.DATE,(int)d);
        c.add(Calendar.HOUR,(int)hour);
        c.add(Calendar.MINUTE,(int)min);
        c.add(Calendar.SECOND,(int)sec);

        System.out.println(c.getTime());