Metasheet

The metasheet is a worksheet named "@TABLEAU" to specify sheet-level options of tableau parser.

Overview #

Options below can be specified in the metasheet @TABLEAU to affect the corresponding worksheet’s layout, ability, loader and so on.

Empty @TABLEAU #

If metasheet @TABLEAU is empty, then all other worksheets in the same workbook will be processed.

A simple example #

There is a worksheet Sheet1 in HelloWorld.xlsx, we want to rename sheet to ItemConf, define custom seperator as |, and generate ordered map accessers.

So the metasheet @TABLEAU in HelloWorld.xlsx should be configured as:

HelloWorld.xlsx 

• Sheet1
• @TABLEAU

ID	Name
map<uint32, Item>	string
Item’s ID	Item’s Name
1	Apple
2	Orange
3	Banana

Sheet	Alias	Sep	OrderedMap
Sheet1	ItemConf	|	true

Workbook Alias #

The generated proto file name is the snake case of input file name. For example, if you have a workbook named HelloWorld.xlsx, the generated proto file name is hello_world.proto. If you want to manually specify a name for the generated proto file, you can also use the Alias option. In this scenario, # refers to the workbook name.

A worksheet ItemConf in HelloWorld.xlsx:

HelloWorld.xlsx 

• Sheet1
• @TABLEAU

ID	Name
map<uint32, Item>	string
Item’s ID	Item’s Name
1	Apple
2	Orange
3	Banana

Sheet	Alias
#	custom_conf
Sheet1	ItemConf

Generated:

// --snip--
option (tableau.workbook) = {name:"HelloWorld.xlsx"};

message ItemConf {
  option (tableau.worksheet) = {name:"ItemConf" namerow:1 typerow:2 noterow:3 datarow:4};

  map<uint32, Item> item_map = 1 [(tableau.field) = {key:"ID" layout:LAYOUT_VERTICAL}];
  message Item {
    uint32 id = 1 [(tableau.field) = {name:"ID"}];
    string name = 2 [(tableau.field) = {name:"Name"}];
  }
}

Option Transpose #

In linear algebra, the transpose of a matrix is an operator which flips a matrix over its diagonal. Likewise, the transpose of a sheet (2D matrix) means interchanging its rows into columns or columns into rows.

See more details about Excel: Transpose (rotate) data from rows to columns or vice versa.

Option Transpose is specified as true in the metasheet @TABLEAU.

A worksheet HeroConf in HelloWorld.xlsx:

HelloWorld.xlsx 

• HeroConf
• @TABLEAU

ID	int32	Hero’s ID	123
Name	string	Hero’s name	Robin
Desc	string	Hero’s description	A big hero!
Skill	[]int32	Hero’s skills	100,101,102

Sheet	Transpose
HeroConf	true

Generated:

// --snip--
option (tableau.workbook) = {name:"HelloWorld.xlsx"};

message HeroConf {
  option (tableau.worksheet) = {name:"HeroConf" namerow:1 typerow:2 noterow:3 datarow:4 transpose:true};

  int32 id = 1 [(tableau.field) = {name:"ID"}];
  string name = 2 [(tableau.field) = {name:"Name"}];
  string desc = 3 [(tableau.field) = {name:"Desc"}];
  repeated int32 skill_list = 4 [(tableau.field) = {name:"Skill" layout:LAYOUT_INCELL}];
}

{
    "id": 123,
    "name": "Robin",
    "desc": "A big hero!",
    "skillList": [
        100,
        101,
        102
    ]
}

Option Merger #

Option Merger is used to merge multiple sheets (comma-separated) with same schema to one.

Each element can be:

1. just a workbook file path or Glob path (relative to this workbook): <Workbook>, then the sheet name is the same as this sheet.
2. a workbook file path (relative to this workbook) with a worksheet name: <Workbook>#<Worksheet>.

For example:

The first (main) workbook: a worksheet ZoneConf in MergerMain.xlsx (with @TABLEAU):

MergerMain.xlsx 

• ZoneConf
• @TABLEAU

ID	Name	Difficulty
map<uint32, Zone>	string	int32
Zone’s ID	Zone’s name	Zone’s difficulty
1	Infinity	100

Sheet	Merger
ZoneConf	Merger*.xlsx

The second (sub) workbook: a worksheet ZoneConf in Merger2.xlsx (without @TABLEAU):

Merger2.xlsx 

• ZoneConf

ID	Name	Difficulty
map<uint32, Zone>	string	int32
Zone’s ID	Zone’s name	Zone’s difficulty
2	Desert	200

The third (sub) workbook: a worksheet ZoneConf in Merger3.xlsx (without @TABLEAU):

Merger3.xlsx 

• ZoneConf

ID	Name	Difficulty
map<uint32, Zone>	string	int32
Zone’s ID	Zone’s name	Zone’s difficulty
3	Snowfield	300

Generated:

// --snip--
option (tableau.workbook) = {name:"HelloWorld.xlsx"};

message ZoneConf {
  option (tableau.worksheet) = {name:"ZoneConf" namerow:1 typerow:2 noterow:3 datarow:4 merger:"Merger*.xlsx"};

  map<uint32, Zone> zone_map = 1 [(tableau.field) = {key:"ID" layout:LAYOUT_VERTICAL}];
  message Zone {
    uint32 id = 1 [(tableau.field) = {name:"ID"}];
    string name = 2 [(tableau.field) = {name:"Name"}];
    int32 difficulty = 3 [(tableau.field) = {name:"Difficulty"}];
  }
}

{
    "zoneMap": {
        "1": {
            "id": 1,
            "name": "Infinity",
            "difficulty": 100
        },
        "2": {
            "id": 2,
            "name": "Desert",
            "difficulty": 200
        },
        "3": {
            "id": 3,
            "name": "Snowfield",
            "difficulty": 300
        }
    }
}

Option Scatter #

Option Scatter is used to scatter multiple sheets (comma-separated) with same schema to different generated config files.

Each element can be:

1. just a workbook file path or Glob path (relative to this workbook): <Workbook>, then the sheet name is the same as this sheet.
2. a workbook file path (relative to this workbook) with a worksheet name: <Workbook>#<Worksheet>.

For example, there are three workbooks (each with same sheet schema, and Scatter1.xlsx is the main workbook):

• Scatter1.xlsx
• Scatter2.xlsx
• Scatter3.xlsx

The first (main) workbook: a worksheet ZoneConf in Scatter1.xlsx (with @TABLEAU):

Scatter1.xlsx 

• ZoneConf
• @TABLEAU

ID	Name	Difficulty
map<uint32, Zone>	string	int32
Zone’s ID	Zone’s name	Zone’s difficulty
1	Infinity	100

Sheet	Scatter
ZoneConf	Scatter*.xlsx

The second (sub) workbook: a worksheet ZoneConf in Scatter2.xlsx (without @TABLEAU):

Scatter2.xlsx 

• ZoneConf

ID	Name	Difficulty
map<uint32, Zone>	string	int32
Zone’s ID	Zone’s name	Zone’s difficulty
2	Desert	200

The third (sub) workbook: a worksheet ZoneConf in Scatter3.xlsx (without @TABLEAU):

Scatter3.xlsx 

• ZoneConf

ID	Name	Difficulty
map<uint32, Zone>	string	int32
Zone’s ID	Zone’s name	Zone’s difficulty
3	Snowfield	300

Generated protoconf:

// --snip--
option (tableau.workbook) = {name:"HelloWorld.xlsx"};

message ZoneConf {
  option (tableau.worksheet) = {name:"ZoneConf" namerow:1 typerow:2 noterow:3 datarow:4 scatter:"Scatter*.xlsx"};

  map<uint32, Zone> zone_map = 1 [(tableau.field) = {key:"ID" layout:LAYOUT_VERTICAL}];
  message Zone {
    uint32 id = 1 [(tableau.field) = {name:"ID"}];
    string name = 2 [(tableau.field) = {name:"Name"}];
    int32 difficulty = 3 [(tableau.field) = {name:"Difficulty"}];
  }
}

It is supposed to generate three different config files (name pattern: <BookName>_<SheetName>):

{
    "zoneMap": {
        "1": {
            "id": 1,
            "name": "Infinity",
            "difficulty": 100
        }
    }
}

{
    "zoneMap": {
        "2": {
            "id": 2,
            "name": "Desert",
            "difficulty": 200
        }
    }
}

{
    "zoneMap": {
        "3": {
            "id": 3,
            "name": "Snowfield",
            "difficulty": 300
        }
    }
}

Option Index #

Option Index can be specified to generate index accessers, and multiple indexes are comma-separated. There are two kinds of indexes in tableau: one is single-column index, and another is multi-column index (aka composite index).

Each column type can be:

• scalar: numbers, booleans, strings, and bytes.
• enum: e.g.: enum<.FruiteType>
• incell scalar list: e.g: []int32
• incell enum list: e.g: []enum<.FruiteType>

Example: two worksheets ItemConf and ShopConf in HelloWorld.xlsx:

• ItemConf: index on columns of the same struct as map value.
• ShopConf: index on columns of the same struct as list element.

HelloWorld.xlsx 

• ItemConf
• ShopConf
• @TABLEAU

ID	Name	Desc
map<int32, Item>	string	string
Item’s ID	Item’s name	Item’s desc
1	Apple	A kind of delicious fruit.
2	Orange	A kind of sour fruit.
3	Banana	A kind of calorie-rich fruit.

ID	Type	Desc
[Shop]int32	int32	string
Shop’s ID	Shop’s type	Shop’s desc
1	1	Shoes shop.
2	1	T-Shirt shop.
3	2	Fruite shop.

Sheet	Index
ItemConf	ID@Item, Name@AwardItem, (ID,Name)@SpecialItem
ShopConf	ID@Shop, Type@ThemeShop, (ID,Type)@SpecialShop

Single-column index #

Format: <ColumnName>[@IndexName].

The sign @ is the separator between column name and index name. if IndexName is not set, it will be this column’s parent struct type name. One or more indexes can be specified by comma-separated rule.

Examples:

• ID
• ID@Item
• ID, Name@AwardItem
• ID@Item, Name@AwardItem

Multi-column index #

Format: ([ColumnName1, ColumnName2, ColumnName3,...])[@IndexName].

Multi-column index (or composite index) is composed of multiple columns in the same struct (in list or map) to increase query speed.

The sign @ is the separator between enclosed column names by parentheses and index name. if IndexName is not set, it will be this column’s parent struct type name. One or more indexes can be specified by comma-separated rule.

Examples:

• (ID,Name)
• (ID,Name)@AwardItem
• ID@Item, (ID,Name)@AwardItem: one single-column index and one multi-column index.

Option Patch #

// Patch type for both sheet-level and field-level.
enum Patch {
  PATCH_NONE = 0;
  // 1 Sheet-level patch option "PATCH_REPLACE"
  //   - replace whole message
  // 2 Top-field patch option "PATCH_REPLACE"
  //   - list: Clear field firstly, and then all elements of this list field
  //     in src are appended to the corresponded list fields in dst.
  //   - map: Clear field firstly, and then all entries of this map field in src
  //     are copied into the corresponding map field in dst.
  PATCH_REPLACE = 1;
  // Merge src into dst, which must be a message with the same descriptor.
  //  - scalar: Populated scalar fields in src are copied to dst.
  //  - message: Populated singular messages in src are merged into dst by
  //     recursively calling [proto.Merge](https://pkg.go.dev/google.golang.org/protobuf/proto#Merge).
  //  - list: The elements of every list field in src are appended to the
  //     corresponded list fields in dst.
  //  - map: The entries of every map field in src are copied into the
  //     corresponding map field in dst, possibly replacing existing entries.
  //  - unknown: The unknown fields of src are appended to the unknown
  //     fields of dst.
  PATCH_MERGE = 2;
}