Because the table structure planned for calculation is not necessarily the same as the data table structure actually used by the program, sometimes data conversion is often required. Convert the table to the format required by the program, and then import the table to the database. This is also the planning of a table with multiple fields representing multiple attributes, but the program considers the general purpose and does not want to add a field to an attribute. Therefore, we want to use a field, all attributes are displayed in JSON format.
Therefore, a VBA is written for planning to merge multiple fields into JSON. This VBA can be configured through the CTRL table:
Source table name: name of the planning table
Target table name: name of the program table
Field ing: The field name of the program table corresponds to the field name of the planning table. Currently, field merging is supported (that is, merging multiple fields in the planning table into a field required by the program table using JSON ). The number of fields mapped to a field is the same as the number of fields in the program table.
Sub button export click () searchcolumn = 1ctrl_table_name = "Ctrl" totalrow = countrow (ctrl_table_name) source_table = partition (ctrl_table_name, "source table name", totalrow, searchcolumn) target_table = partition (ctrl_table_name, "Target table name", totalrow, searchcolumn) dim srcfieldsarr () as stringfieldnum = 0 set dict = Createobject ("scripting. dictionary ") 'set dict = Createobject (" scripting. dictionary ") 'Total number of source table rows srctablerowcount = countrow (source_table) for ROW = 1 to totalrow if cells (row, 1) = "field ing" then fieldnum = fieldnum + 1 redim preserve srcfieldsarr (fieldnum) Partition (fieldnum) = cells (row, 2) totalcolumn = countcolumn (ctrl_table_name, row) dim Arr () as string 'stores the target table column redim Arr (1 to totalcolumn-2) for I = 3 to totalcolumn Arr (I-2) = cells (row, I) Next dict. add fieldnum, arr end ifnext row' ing set srcrownametoindex = Createobject ("scripting. dictionary ") for column = 1 to countcolumn (source_table, 1) srcrownametoindex. add worksheets (source_table ). cells (1, column ). value, columnnext column 'process data for ROW = 2 to srctablerowcount for I = 1 to fieldnum arr = dict (I) If ubound (ARR) = 1 then srccolumnindex = srcrownametoindex (ARR (1) worksheets (target_table ). cells (row, I) = worksheets (source_table ). cells (row, srccolumnindex) else prostr = "{" for j = 1 to ubound (ARR) srccolumnindex = srcrownametoindex (ARR (j )) prostr = prostr & "& worksheets (source_table ). cells (1, srccolumnindex) & "&": "& worksheets (source_table ). cells (row, srccolumnindex) If j <ubound (ARR) Then prostr = prostr & "," end if next prostr = prostr & "}" worksheets (target_table ). cells (row, I) = prostr end if nextnext end sub 'index the cell based on input parameters, and then return the value of the cell following it. Function getvaluebykey (byval sheetname as string, key as string, byval rowlimit as integer, byval searchcolumn as integer) as string for ROW = 1 to rowlimit if worksheets (sheetname ). cells (row, searchcolumn) = key then getvaluebykey = worksheets (sheetname ). cells (row, searchcolumn + 1) end if next rowend function 'calculates the number of rows, starting from the first row and going down until column 1st of the nth row is empty, the number of rows is the N-1Function countrow (byval sheetname as string) as integer COUNT = 1 countrow = 1 while count> 0 if worksheets (sheetname ). cells (count, 1) <> "" Then countrow = count + 1 else count = 0 end if wendend function 'calculates the number of columns in a row function countcolumn (byval sheetname as string, byval row as integer) as integer COUNT = 1 countcolumn = 1 while count> 0 if worksheets (sheetname ). cells (row, count) <> "" Then countcolumn = count + 1 else count = 0 end if wendend function 'Letter column number to numeric function columnnumber (byval columnletter as string) as integer if Len (columnletter)> 1 then columnnumber = (ASC (mid (columnletter, 1, 1)-64) * 26 + (ASC (mid (columnletter, 2, 1)-64) else columnnumber = ASC (columnletter)-64 end ifend Function
This is the control page
The configuration in the CTRL table above indicates:
Copy the data in the mechaid column in the planning table source to the mechaid column in the program table;
Copy the data in the Quality column of the planning table source to the quality column of the program table;
Put the planning table
HP |
Wuliattack |
Wulidefend |
Nengliangattack |
Nengliangdefend |
Critrate |
Anticritrate |
Hitrate |
Missrate |
Gedangdj |
Pojidj |
Recoverenergy |
Attackspeed |
Critharmrate |
Damageleixin |
These columns are merged in JSON format and copied to the Properties column of the program table.
This is the planned data table.
This is the data table of the Program (generated by clicking the control page button)
It took about two hours to write the information including VBA query. I have to say that the syntax of VBA is really not good!