BakingSheet

Easy datasheet management for C# and Unity. Supports Excel, Google Sheet, JSON and CSV format.

Install

Download with NuGet or download .unitypackage release

Need help?

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

Concept

BakingSheet's core concept is controlling datasheet schema from C# code, make things flexible while supporting multiple sources like .xlsx or Google sheets. Also, it helps to avoid having source datasheet files or parsing libraries for production applications. BakingSheet supports JSON serialization by default.

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 Items.xlsx. Also, any column starts with $ will be considered as comment and ignored.

Id	Name	Price	$Comment
ITEM_LVUP001	Warrior's Shield	10000	Warrior Lv up material
ITEM_LVUP002	Mage's Staff	10000	Mage Lv up material
ITEM_LVUP003	Assassin's Dagger	10000	Assassin Lv up material
ITEM_POTION001	Health Potion	30	Heal 20 Hp
ITEM_POTION002	Mana Potion	50	Heal 20 Mp

Code below is corresponding BakingSheet class.

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

Note that Id column is already defined in base SheetRow class.

To represent collection of sheets, implement SheetContainerBase class.

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

    // use name of each matching sheet name from source
    public ItemSheet Items { get; private set; }
}

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 .unitypackage 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

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

// 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);

Save and Load Converted Datasheet

Below code shows how to save and load converted json.

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

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

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

You can extend JsonSheetConverter to customize serialization process.

Accessing Row

Below code shows how to access specific ItemSheet.Row.

var row = sheetContainer.Items["ITEM_LVUP003"];

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

// loop through all rows
foreach (var value in sheetContainer.Items.Values)
    logger.LogInformation(value.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.

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 that properties without setter are not serialized. Alternatively you can use [NonSerialized] attribute.

Using List Column

List columns are used for simple array.

Id	Name	Monsters:1	Monsters:2	Monsters:3	Items:1	Items:2
DUNGEON001	Easy Field	MONSTER001			ITEM_POTION001	ITEM_LVUP001
DUNGEON002	Expert Zone	MONSTER001	MONSTER002		ITEM_POTION002	ITEM_LVUP002
DUNGEON003	Dragon’s Nest	MONSTER003	MONSTER004	MONSTER005	ITEM_LVUP003

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

        public List<MonsterSheet.Reference> Monsters { get; private set; }
        public List<ItemSheet.Reference> Items { 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.

Using Dictionary Column

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

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.

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.

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.

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; }
    }
}

Note that the content of 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.

Id	Name	Strength	Inteligence	Vitality	StatMultiplier	RequiredExp	RequiredItem
HERO001	Warrior	100	80	140	1	0
					1.2	10
					1.4	20
					1.6	40
					2	100	ITEM_LVUP001
HERO002	Mage	60	160	80	1	0
					1.2	10
					1.4	20
					1.6	40
					2	100	ITEM_LVUP002
HERO003	Assassin	140	100	80	1	0
					1.2	10
					1.4	20
					1.6	40
					2	100	ITEM_LVUP003

Rows without Id is considered as part of previous row. 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)
        {
            return this[level - 1];
        }
    }

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

Note that SheetRowArray<TElem> is implementing IEnumerable<TElem> and indexer.

Using Cross-Sheet Reference

Below code shows how to replace string RequiredItem to ItemSheet.Reference RequiredItem 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 ItemSheet.Reference RequiredItem { get; private set; }
    }
}

public class SheetContainer : SheetContainerBase
{
    // ...

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

Note that both ItemSheet and HeroSheet have to be one of the properties on same SheetContainer class.

Custom Converters

User can create and customize their own converter by implementing ISheetImporter and ISheetExporter.

Custom Verifiers

You can verify datasheet sanity with custom verifiers. For example, you can define ResourceAttribute to mark columns that should reference path inside of Unity's Resources folder.

Terry-Jiang-A/BakingSheet