Skip to content

Latest commit

 

History

History
640 lines (507 loc) · 30.3 KB

README.md

File metadata and controls

640 lines (507 loc) · 30.3 KB
Raw

Nuget GitHub release (latest by date) openupm GitHub Discord

BakingSheet 🍞

Easy datasheet management for C# and Unity. Supports Excel, Google Sheet, JSON and CSV format. It has been used for several mobile games that released on Google Play and AppStore.

Table of Contents

Concept

Throughout all stage of game development, you'll need to deal with various data. Characters, stats, stages, currencies and so on! If you're using Unity, scriptable object and inspector is not good enough for mass edition and lacks powerful features like functions or fill up. With BakingSheet your designers can use existing spreadsheet editor, while you, the programmer, can directly use C# object without messy parsing logics or code generations.

Let's say your team is making a RPG game. Your game has 100 characters and 10 stats for each character. If your team use Unity's scriptable object, designers will have to spend lots of time adding and editing from Unity inspector. And after setup what if you need mass edit, like to double ATK stat of all characters? Will you go through all characters with Unity inspector, or make Editor script for every time mass edit is required? With BakingSheet, designers can work easily with spreadsheet functions and fill ups without programmer help!

Concept

BakingSheet's core concept is controlling datasheet schema from C# code, make things flexible while supporting multiple sources like Excel files or Google sheets. You can think it as datasheet version of ORM. Also, you won't have to include source Excel files or parsing libraries for production builds. BakingSheet supports JSON serialization by default, you can ship your build with JSON or your custom format.

BakingSheet's basic workflow is like this:

  1. Programmers make C# schema that represents Datasheet. (They can provide sample Excel files or Google Sheet with headers.)
  2. Designers fill up the Datasheet, using any powerful functions and features of spreadsheet.
  3. Edit-time script converts Datasheet to JSON (or any custom format) with your C# schema and validates data.
  4. Runtime script reads from JSON (or any custom format) with your C# schema.
  5. Your business logic directly uses C# instance of your schema.
  6. Profit!

Don't trust me that it's better than using ScriptableObject? You might change your mind if you see how famous SuperCell ships their games with CSV, like Clash Royale or Brawl Stars. Though of course, their games aren't made with Unity, still a very good example to show how you can utilize spreadsheet!

Sample1 Sample2

Features

Install

For C# projects or server, download with NuGet.

For Unity projects, add git package from Package Manager.

https://github.com/cathei/BakingSheet.git?path=UnityProject/Packages/com.cathei.bakingsheet#v4.1.3

Or install it via OpenUPM.

openupm add com.cathei.bakingsheet

Sample .unitypackage is available in releases. (Main package should be installed first.)

If you are planning to use StreamingAssets folder on Android, install BetterStreamingAssets as well.

Need help?

Before you start, we want to mention that if you have problem or need help, you can always ask directly on Discord Channel!

Contribution

We appreciate any contribution. Please create issue for bugs or feature requests. Any contribution to feature, test case, or documentation through pull requests are welcome! Any blog posts, articles, shares about this project will be greatful!

First Step

BakingSheet manages datasheet schema as C# code. Sheet class represents a table and SheetRow class represents a record. Below is example content of file Consumables page in MySheets.xlsx. Also, any column starts with $ will be considered as comment and ignored.

Plain Sample

Markdown version
Id Name Price $Comment
LVUP_001 Warrior's Shield 10000 Warrior Lv up material
LVUP_002 Mage's Staff 10000 Mage Lv up material
LVUP_003 Assassin's Dagger 10000 Assassin Lv up material
POTION_001 Health Potion 30 Heal 20 Hp
POTION_002 Mana Potion 50 Heal 20 Mp

Code below is corresponding BakingSheet class.

public class ConsumableSheet : Sheet<ConsumableSheet.Row>
{
    public class Row : SheetRow
    {
        // use name of matching column
        public string Name { get; private set; }
        public int Price { get; private set; }
    }
}

You can see there are two classes, ConsumableSheet and ConsumableSheet.Row. Each represents a page of sheet and a single row. ConsumableSheet is surrounding Row class (It is not forced but recommended convention). Important part is they will inherit from Sheet<TRow> and SheetRow.

Id column is mandatory, so it is already defined in base SheetRow class. Id is string by default, but you can change type. See this section to use non-string type for Id.

To represent collection of sheets, a document, let's create SheetContainer class inherits from SheetContainerBase.

public class SheetContainer : SheetContainerBase
{
    public SheetContainer(Microsoft.Extensions.Logging.ILogger logger) : base(logger) {}

    // property name matches with corresponding sheet name
    // for .xlsx or google sheet, **property name matches with the name of sheet tab in workbook**
    // for .csv or .json, **property name matches with the name of file**
    public ConsumableSheet Consumables { get; private set; }

    // add other sheets as you extend your project
    public CharacterSheet Characters { get; private set; }
}

You can add as many sheets you want as properties of your SheetContainer. This class is designed to be "fat", means single SheetContainer should contain all your sheets unless there is specific reason to partition your sheets. For example when you want to deploy some Sheet only exclusive to server program, you might want to partition ServerSheetContainer and ClientSheetContainer.

Supported Column Type

  • string
  • Numeric primitive types (int, long, float, double, and so on)
  • bool ("TRUE" or "FALSE")
  • Custom enum types
  • DateTime and TimeSpan
  • Cross-sheet reference (Sheet<>.Reference)
  • Nullable for any other supported value type (for example int?)
  • List<> and Dictionary<,>
  • Custom struct and class as nested column
  • Custom type converted with ValueConverter

Note
When using JsonConverter, enum is serialized as string by default so you won't have issue when reordering them.

Converters

Converters are simple implementation import/export records from datasheet sources. These come as separated library, as it's user's decision to select datasheet source. User can have converting process, to convert datasheet to other format ahead of time and not include heavy converters in production applications.

BakingSheet supports four basic converters. They're included in Unity package as well.

Package Name Format Supports Import Supports Export
BakingSheet.Converters.Excel Microsoft Excel O X
BakingSheet.Converters.Google Google Sheet O X
BakingSheet.Converters.Csv Comma-Separated Values (CSV) O O
BakingSheet.Converters.Json JSON O O
ScriptableObject Converter (Unity only) ScriptableObject O O (Read-only)

Below code shows how to convert .xlsx files from Excel/Files/Path directory.

// any ILogger will work, there is built-in UnityLogger
var logger = new UnityLogger();

// pass logger to receive logs
var sheetContainer = new SheetContainer(logger);

// create excel converter from path
var excelConverter = new ExcelSheetConverter("Excel/Files/Path");

// bake sheets from excel converter
await sheetContainer.Bake(excelConverter);

For Google Sheet, first create your service account through Google API Console. Then add it to your sheet with Viewer permission. Use Google credential for that service account to create converter. For detailed information about how to create service account and link to your sheet, see How to import from Google Sheet.

// replace with your Google sheet identifier
// https://developers.google.com/sheets/api/guides/concepts
string googleSheetId = "1iWMZVI4FgtGbig4EgPIun_BRbzp4ulqRIzINZQl-AFI";

// service account credential than can read the sheet you're converting
// this starts with { "type": "service_account", "project_id": ...
string googleCredential = File.ReadAllText("Some/Path/Credential.json");

var googleConverter = new GoogleSheetConverter(googleSheetId, googleCredential);

// bake sheets from google converter
await sheetContainer.Bake(googleConverter);

Save and Load Converted Datasheet

Below code shows how to load sheet from Excel and save as JSON. This typically happens through Unity Editor script or any pre-build time script.

// create excel converter from path
var excelConverter = new ExcelSheetConverter("Excel/Files/Path");

// create json converter from path
var jsonConverter = new JsonSheetConverter("Json/Files/Path");

// convert from excel
await sheetContainer.Bake(excelConverter);

// save as json
await sheetContainer.Store(jsonConverter);

Then, for runtime you can load your data from JSON.

// create json converter from path
var jsonConverter = new JsonSheetConverter("Json/Files/Path");

// load from json
await sheetContainer.Bake(jsonConverter);

You can extend JsonSheetConverter to customize serialization process. For example encrypting data or prettifying JSON.

Note
For AOT platforms (iOS, Android), read about AOT Code Stripping.

Note
If you are using StreamingAssets on Android, also see Reading From StreamingAssets.

Accessing Row

Now you have SheetContainer loaded from your data, accessing to the row is fairly simple. Below code shows how to access specific ConsumableSheet.Row.

// same as sheetContainer.Consumables.Find("LVUP_003");
// returns null if no row found
var row = sheetContainer.Consumables["LVUP_003"];

// print "Assassin's dagger"
logger.LogInformation(row.Name);

Sheet<T> is KeyedCollection, you can loop through it and order is guaranteed to be as same as your spreadsheet. Plus of course you can use all benefits of IEnumerable<T>.

// loop through all rows and print their names
foreach (var row in sheetContainer.Consumables)
    logger.LogInformation(row.Name);

// loop through consumable ids that price over 5000
foreach (var consumableId in sheetContainer.Consumables.Where(row => row.Price > 5000).Select(row => row.Id))
    logger.LogInformation(consumableId);

Using List Column

List columns are used for simple array.

List Sample

Flat header
Id Name Monsters:1 Monsters:2 Monsters:3 Loots:1 Loots:2
DUNGEON001 Easy Field MONSTER001 POTION_001 LVUP_001
DUNGEON002 Expert Zone MONSTER001 MONSTER002 POTION_002 LVUP_002
DUNGEON003 Dragon’s Nest MONSTER003 MONSTER004 MONSTER005 LVUP_003
Split header
Id Name Monsters Loots
1 2 3 1 2
DUNGEON001 Easy Field MONSTER001 POTION_001 LVUP_001
DUNGEON002 Expert Zone MONSTER001 MONSTER002 POTION_002 LVUP_002
DUNGEON003 Dragon’s Nest MONSTER003 MONSTER004 MONSTER005 LVUP_003 
public class DungeonSheet : Sheet<DungeonSheet.Row>
{
    public class Row : SheetRow
    {
        public string Name { get; private set; }

        // you can use any supported type as list
        // to know more about sheet reference types, see cross-sheet reference section
        public List<MonsterSheet.Reference> Monsters { get; private set; }
        public List<ConsumableSheet.Reference> Loots { get; private set; }
    }
}

Use it as simple as just including a column has type implmenting IList<T>. Since spreadsheet is designer's area, index on sheet is 1-based. So be aware when you access it from code.

Also you can pick between flat-header style(Monsters:1) and split-header style(Monsters, 1) as the example shows. There is no problem to mix-and-match or nest them.

Using Dictionary Column

Dictionary columns are used when key-based access of value is needed.

Dictionary Sample

Flat header
Id Name Texts:Greeting Texts:Purchasing Texts:Leaving
NPC001 Fat Baker Morning traveler! Thank you! Come again!
NPC002 Blacksmith G'day! Good choice. Take care.
NPC003 Potion Master What do you want? Take it already. Don't come again.
Split header
Id Name Texts
Greeting Purchasing Leaving
NPC001 Fat Baker Morning traveler! Thank you! Come again!
NPC002 Blacksmith G'day! Good choice. Take care.
NPC003 Potion Master What do you want? Take it already. Don't come again. 
public enum Situation
{
    Greeting,
    Purchasing,
    Leaving
}

public class NpcSheet : Sheet<NpcSheet.Row>
{
    public class Row : SheetRow
    {
        public string Name { get; private set; }

        public Dictionary<Situation, string> Texts { get; private set; }
    }
}

Use it as simple as just including a column has type implmenting IDictionary<TKey, TValue>.

Using Nested Type Column

Nested type columns are used for complex structure.

Nested Type Sample

Flat header
Id Name Texts:Greeting Texts:Purchasing Texts:Leaving
NPC001 Fat Baker Morning traveler! Thank you! Come again!
NPC002 Blacksmith G'day! Good choice. Take care.
NPC003 Potion Master What do you want? Take it already. Don't come again.
Split header
Id Name Texts
Greeting Purchasing Leaving
NPC001 Fat Baker Morning traveler! Thank you! Come again!
NPC002 Blacksmith G'day! Good choice. Take care.
NPC003 Potion Master What do you want? Take it already. Don't come again. 
public struct SituationText
{
    public string Greeting { get; private set; }
    public string Purchasing { get; private set; }
    public string Leaving { get; private set; }
}

public class NpcSheet : Sheet<NpcSheet.Row>
{
    public class Row : SheetRow
    {
        public string Name { get; private set; }

        public SituationText Texts { get; private set; }
    }
}

As you see, content of the datasheet is just same as when using Dictionary column. The data type of column determines how BakingSheet reads the column.

Using Row Array

Row arrays are used for 2-dimentional structure. Below is example content of file Heroes.xlsx.

Row Array Sample

Markdown version
Id Name Strength Inteligence Vitality StatMultiplier RequiredExp RequiredMaterial
HERO001 Warrior 100 80 140 1 0
1.2 10
1.4 20
1.6 40
2 100 LVUP_001
HERO002 Mage 60 160 80 1 0
1.2 10
1.4 20
1.6 40
2 100 LVUP_002
HERO003 Assassin 140 100 80 1 0
1.2 10
1.4 20
1.6 40
2 100 LVUP_003

Rows without Id is considered as part of previous row. You can merge the non-array cells to make it visually intuitive. Below corresponding code shows how to define row arrays.

public class HeroSheet : Sheet<HeroSheet.Row>
{
    public class Row : SheetRowArray<Elem>
    {
        public string Name { get; private set; }

        public int Strength { get; private set; }
        public int Inteligence { get; private set; }
        public int Vitality { get; private set; }

        public Elem GetLevel(int level)
        {
            // Level 1 would be index 0
            return this[level - 1];
        }

        // Max level would be count of elements
        public int MaxLevel => Count;
    }

    public class Elem : SheetRowElem
    {
        public float StatMultiplier { get; private set; }
        public int RequiredExp { get; private set; }
        public string RequiredMaterial { get; private set; }
    }
}

SheetRowArray<TElem> implements IEnumerable<TElem>, indexer this[int] and Count property.

Note
It is worth mention you can use VerticalList<T> to cover the case you want to vertically extend your List<T> without pairing them as Elem. Though we recommend to split the sheet in that case if possible.

Using Cross-Sheet Reference

Below code shows how to replace string RequiredMaterial to ConsumableSheet.Reference RequiredMaterial to add extra reliablity. Sheet<TKey, TRow>.Reference type is serialized as TKey, and verifies that row with same id exists in the sheet.

public class HeroSheet : Sheet<HeroSheet.Row>
{
    public class Row : SheetRowArray<Elem>
    {
        // ...
    }

    public class Elem : SheetRowElem
    {
        public float StatMultiplier { get; private set; }
        public int RequiredExp { get; private set; }
        public ConsumableSheet.Reference RequiredMaterial { get; private set; }
    }
}
public class SheetContainer : SheetContainerBase
{
    // ...

    // use name of each matching sheet name from source
    public HeroSheet Heroes { get; private set; }
    public ConsumableSheet Consumables { get; private set; }
}

Both ConsumableSheet and HeroSheet must be the properties on same SheetContainer class to reference each other's row.

Now, not only error message will pop up when RequiredMaterial doesn't exist in SheetContainer.Consumables, you can access ConsumableSheet.Row directly through it.

var heroRow = sheetContainer.Heroes["HERO001"];

// LVUP_001 from Consumables sheet
var consumableRow = heroRow.GetLevel(5).RequiredMaterial.Ref;

// print "Warrior's Shield"
logger.LogInformation(consumableRow.Name);

Using Non-String Column as Id

Any type can be used value can be also used as Id. This is possible as passing type argument to generic class SheetRow<TKey> and Sheet<TKey, TRow>. Below is example content of file Contstants.xlsx.

Const Sample

Markdown version
Id Value
ServerAddress https://github.com/cathei/BakingSheet
InitialGold 1000
CriticalChance 0.1

Below code shows how to use enumeration type as Id.

public enum GameConstant
{
    ServerAddress,
    InitialGold,
    CriticalChance,
}

public class ConstantSheet : Sheet<GameConstant, ConstantSheet.Row>
{
    public class Row : SheetRow<GameConstant>
    {
        public string Value { get; private set; }
    }
}

Using Post Load Hook

You can override PostLoad method of Sheet, SheetRow or SheetRowElem to execute post load process.

Below code shows how to convert loaded sheet value dynamically.

public class ConstantSheet : Sheet<GameConstant, ConstantSheet.Row>
{
    public class Row : SheetRow<GameConstant>
    {
        public string Value { get; private set; }

        private int valueInt;
        public int ValueInt => valueInt;

        private float valueFloat;
        public float ValueFloat => valueFloat;

        public override void PostLoad(SheetConvertingContext context)
        {
            base.PostLoad(context);

            int.TryParse(Value, out valueInt);
            float.TryParse(Value, out valueFloat);
        }
    }

    public string GetString(GameConstant key)
    {
        return Find(key).Value;
    }

    public int GetInt(GameConstant key)
    {
        return Find(key).ValueInt;
    }

    public float GetFloat(GameConstant key)
    {
        return Find(key).ValueFloat;
    }
}

Note
Properties without setter are not serialized. Alternatively you can use [NonSerialized] attribute.

Using AssetPostProcessor to Automate Converting

For Excel and CSV, you could set up AssetPostProcessor to automate converting process. Recommended practice is keeping both source .xlsx and .csv files alongside with destination .json files in your version control system. For Google Sheet, it is instead recommended to use custom MenuItem to convert into destination .json files that keeped in your version control.

The below is example source code that triggers when any .xlsx is changed, convert Excel sheet under Assets/Excel into .json under Assets/StreamingAssets/Json. You can customize this logic with your desired source and destination folder.

public class ExcelPostprocessor : AssetPostprocessor
{
    static async void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
    {
        // automatically run postprocessor if any excel file is imported
        string excelAsset = importedAssets.FirstOrDefault(x => x.EndsWith(".xlsx"));

        if (excelAsset != null)
        {
            // excel path as "Assets/Excel"
            var excelPath = Path.Combine(Application.dataPath, "Excel");

            // json path as "Assets/StreamingAssets/Json"
            var jsonPath = Path.Combine(Application.streamingAssetsPath, "Json");

            var logger = new UnityLogger();
            var sheetContainer = new SheetContainer(logger);

            // create excel converter from path
            var excelConverter = new ExcelSheetConverter(excelPath);

            // bake sheets from excel converter
            await sheetContainer.Bake(excelConverter);

            // create json converter to path
            var jsonConverter = new JsonSheetConverter(jsonPath);

            // save datasheet to streaming assets
            await sheetContainer.Store(jsonConverter);

            AssetDatabase.Refresh();

            Debug.Log("Excel sheet converted.");
        }
    }
}

About AOT Code Stripping (Unity)

If you are working on AOT (IL2CPP) environment, you would have option called Managed Stripping Level in your Player Settings. Since BakingSheet uses reflection, if you set stripping level Medium or High, the stripper might remove the code piece that required. Especially some property setters.

You can prevent this by either using Low stripping level, or adding own link.xml to preserve your sheet classes. The below is simplest example of link.xml. If you want to know more about it, see Unity's Documentation.

<?xml version="1.0" encoding="utf-8" ?>
<linker>
  <!--
    Replace `MyCompany.MyGame.Sheet` to your assembly to prevent Unity code stripping
  -->
  <assembly fullname="MyCompany.MyGame.Sheet" preserve="all"/>
</linker>

Optional Script Defining Symbols (Unity)

There is few optional symbols that can be defined for runtime usage. By default only JSON and ScriptableObject converters will be included to the build.

Symbol Effect
BAKINGSHEET_RUNTIME_GOOGLECONVERTER Include Google Converter to your build.
See also: Google Sheet Converter
BAKINGSHEET_RUNTIME_CSVCONVERTER Include CSV Converter to your build.
BAKINGSHEET_EXTERNAL_LOGGING_DLL Use external Microsoft.Extensions.Logging.Abstractions.dll. Useful if you already have same dll in your project for different dependency.