Shulou(Shulou.com)11/24 Report--

In the actual development process, planners are used to using Excel to fill in game data, and the configuration data used by the program are usually xml,json,lua or some custom DSL (Domain Specific Language), or binary files such as scriptable, so there is a process of converting the data in Excel into the "guide table" needed by the program.

Generally speaking, the guide table tools of the team are written by programs, with both good and bad aspects. The good news is that the program will compare the MD5 code to make sure the file is consistent. The bad side is: to specify the file path, you can only convert all the tables at once, or choose the UI that is difficult to use when you want to convert the table, the sheet name of the table and a row of the table must have special provisions. The biggest problem is that this tool is not open source for planning. in addition, there are a series of automatic operations such as uploading SVN and restarting the server in the guide table tool. after a mistake, it is impossible to determine whether it is a configuration data problem, a tool problem, or a network problem. In this pipeline, there is a huge space to throw off the pot.

So the planner should firmly grasp this pot, if something goes wrong, it is your configuration fault, who told you to leave the Excel and can't even play the game? The following is an example of json format, talking about several methods that planners can learn to make their own guide table tools.

Beside the point, it is not recommended to import Excel directly into the database.

Method 1: the easiest way to convert manually is to copy and paste and save it into json manually. Yes, let's do it, because all the program wants is a json file. The merchant in Shatas once said:

"if you want war, I'll give you war, fruit seller."

Let's first analyze what the json format generally looks like, taking the following paragraph as an example:

As you can see, the whole file is wrapped in curly braces {}, with key in quotation marks on the left and value on the right. There are many types of Value: it's a number / enclosed in quotation marks / enclosed in curly braces / and enclosed in square braces, so we just have to do it this way.

Let's take a look at the format in the excel table that generally looks like this:

The first line is the field name, the following is the value of the field, generally use id as the key in the database, or there is an automatically generated uid, add a line to mark the type of the field, and so on. What we need is to pass every row through the "field: value, field: value." Join together and wrap it in curly braces.

After we add a column, through the function of Excel, to get the data we need. A suggestion to the new planners is that the function should be written step by step, even if there are more than a dozen steps, and the results of each step are put in a column. After confirming that the result is correct, and then splicing it together, write a super-long function (no more than 255 characters). Then delete the middle steps and say to the people next to you, "isn't it that simple?"

Step one:

The index function is used to get the field name. The first parameter, $A$1:$F$1, is the field range required by the first line. Note that with the $symbol, the shortcut key is F4. (search by yourself, there is a feeling of 'this is the refrigerator'). The second parameter is the column number of the current value, which should be subtracted here if there is a blank line ahead.

Drag horizontally and vertically, and see that the content we need is the key we want.

Then concatenate the colon and the value of each line:

= INDEX ($Aguil1VOGRAPHY Found1MagneCOLUMN (A2)) & ":" & A2

Then use the textJoin function, concatenate together, and put braces around it.

= "{" & TEXTJOIN (",", TRUE,G2:L2) & "}"

Someone asked, what should I do if the key of json has quotation marks?

The simplest thing is to put quotation marks on the field name!

At this point, the body part is completed, and each {} curly braces is an object of json. The objects are wrapped with [], which is a list of json, or there are {} packages to form kv pairs, so you just need to splice the spliced content again as required.

Some people may ask, the most important thing, why haven't you talked about it yet? With so many auxiliary columns, are you obviously in a hurry? How can it be written into a function?

Don't worry, we need to use the array formula here. Go directly to the formula:

{= "{" & TEXTJOIN (", TRUE,INDEX ($Aq1VON1VON1MagneF1MagneCOLUMN (A2:F2)) &": "& A2:F2) &"} "} just change the value from a lattice A2 to a range A2:F2, and then press CTRL+SHITF+ENTER to enter the array formula. The outermost braces in the array formula are not typed.

At this point, you can finally be light, because the average programmer is not very good at using Excel, which makes you look very professional.

Method 2: in the above example of a custom function add-on, the values of some fields are arrays, enclosed in [] parentheses, for example

"keywords": ["pig", "belly", "chicken"] or something similar to a custom lambda expression

Scripts: (Count (Unit)) = 100&Count (Wonder) = 3IsDead (Bob) at this time, in order to plan and fill in the form, each column may have a special stitching method. We write the type of value in the second line. The example is as follows:

This will have a lot of special, customized content, such as when you see lambda, assign a value to a temporary variable name, such as

Lambda1= (Count (Unit)) = 100&Count (Wonder) = 3IsDead (Bob) array fields are separated by commas for easy planning and are generated in quotation marks and enclosed in square brackets

"keyword": [pig, belly, chicken "] in the face of these customized needs, it is difficult to use the functions in Excel directly. So let's write a highly customized function ourselves.

Open the IDE of Visual Basic. If your Excel does not display the developer tools tab, you need to check it in the custom ribbon.

Insert a module and write the following code:

Function textToJson (ByVal s As Variant) Dim myKey,myValueDim valueTypeDim output' uses the cell range as the selected range Dim mr As RangeSet mr = s'to read the key of the first row Form a pair of For Each i In mr If Not IsEmpty (I) And I 0 Then 'and the current value to process the corresponding value valueType = Cells (2, i.Column) myKey = Cells (1) through the type of the second line I.Column) myValue = i.value Select Case valueType 'lambda special treatment of key Add a variable Case "lambda" myKey = "lambda" & i.Row-2 output = output & myKey & "=" & myValue & "," 'array handles the value specially Put comma-separated strings in an array Case "array" temp = "tempString = Split (i.value,", ") For Each k In tempString temp = temp & Chr (34) & k & Chr (34) &" "Next k temp = Left (temp, Len (temp)-1) temp =" ["& temp &"] "myValue = temp output = output & myKey &": & myValue & " "Case Else output = output & myKey &": "& myValue &", "End Select End IfNext i' finally splice If IsError (output) Or Len (output)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.