hnb-rabear / excel-to-unity-example

This is a simple example project. It shows how to use the Excel-To-Unity tool to easily change game stats. This project is a good example for anyone working on games that need a lot of static databases.

This project is a simple guide to help you understand the Excel To Unity tool.

Getting Started

This repository consists of two main parts:

• Basic part: This section will help you understand more about designing data to use Excel To Unity.
• Advanced part: In this section, we will explore how a real mid-core RPG game uses this tool.

However, in this article, I will only be discussing the basic part. The advanced part will be supplemented later.

1. Structure of the Excel File

First, open the excel file located at /Assets/Basic/Data/Example.xlsx. This is a sample Excel file. Within this file, there are sheets containing sample data that will help you understand how to design various types of data such as IDs, Constants, and Data Tables.

Constants:

| Name | Type | Value | Comment |
| ---- | ---- | ----- | ------- |

• The sheet name needs to have Constants as a prefix or suffix.
• There are four columns: Name, Type, Value, and Comment.
• Name: This is the name of the constant, it must be written continuously, does not contain special characters, and should be capitalized.
• Type: This is the data type of the constant. You can use the following data types: int, float, bool, string, int-array, float-array, vector2, and vector3.
• Value: The value corresponding to the data type. For array data types, elements must be separated by : or | or newline.

IDs:

| Group | Key | Comment |
| ----- | --- | ------- |

• The sheet name needs to have IDs as a prefix or suffix.
• In this Sheet, only use the Integer data type.
• Each group is arranged in 3 consecutive columns.
• The first row contains the group name for easy lookup.
• The first column contains the Key Name, and the next column contains the Key Value.
• Key Value must be an integer.
• By default, all ids of a column will be exported as Integer Constants, but you can also export them as enum by adding the suffix [enum] to the group name.
• You can choose to only export enum and ignore Integer Constant by selecting Only enum as IDs in the Settings section.

Localization:

| idString | relativeId | english | spanish | japan | .... |
| -------- | ---------- | ------- | ------- | ----- | ---- |

• The sheet name must have Localization as a prefix or suffix.
• This sheet has a structure that includes 2 key columns, one is the main key idString and the other is the additional key relativeId.
• The following columns will contain localized content.
• The key of a row is a combination of idString and relativeId.

For example, if idString is "hero_name" and relativeId is 1, then the key will be hero_name_1

• relativeId can reference the id of the IDs sheet.

Data Table:

• The name of the data table sheet should not contain the strings IDs, Constants, and Localization.

• This sheet can use the following data types: number, string, boolean, list/array, JSON object, and attribute object.

• The first row is used to name the data fields, columns without a name will be skipped when exporting Json data.

• Add a [] suffix to the column name to define the list/array data type.

• Add a {} suffix to the column name to define the JSON object data type.

• Cells with empty values, 0, or FALSE will be skipped when exporting Json Data.

• Columns that only have a name but no value, empty value, 0, or FALSE will be skipped when exporting JSON Data. This helps avoid redundant data and optimizes the size of JSON Data.

• To keep columns from being skipped, add the column name to the Unminimized Fields cell.

• Add a [x] suffix to the column name to exclude that column from the Json data export.

• To define the attribute object type, follow these rules:

  • The attribute column must be placed at the end of the data table.

  • Attribute id is a constant integer, so it should be defined in the IDs sheet.

  • An attribute has the following structure:

    1. attribute: The column name follows the pattern attribute + (index), where index can be any number, but should start from 0 and increase. The value of this column is the id of the attribute, which is an Integer type, this value should be set in the IDs sheet.
    2. value: The column name follows the pattern value + (index) or value + (index) + [], the value of the column can be a number or a number array.
    3. increase: The column name follows the pattern increase + (index) or increase + (index) + []. This is an additional value, which can be optional, usually used for level-up situations, specifying the additional increase when a character or item levels up.
    4. unlock: The column name follows the pattern unlock + (index) or unlock + (index) + []. This is an additional value, which can be optional, usually used for situations where the attribute needs conditions to unlock, such as minimum level or minimum rank.
    5. max: The column name follows the pattern max + (index) or max + (index) + []. This is an additional value, which can be optional, usually used for situations where the attribute has a maximum value.

    Example 1: attribute0, value0, increase0, value0, max0.
    Example 2: attribute1, value1[], increase1[], value1[], max1[].
    

2. Exporting

Unity

Create 3 directories to store the files that will be exported:

• Assets\Basic\Scripts\Generated to store the IDs, Constants, Localization API, and LocalizationText Component scripts.
• Assets\Basic\Data to store the exported Json data.
• Assets\Basic\Resources\Data to store the Localization data.

Excel To Unity

Enter the paths to the directories created above, and other necessary settings.

• Json Data Output: [your project path]\Assets\Basic\Data.
• Constant Output: [Your project path]\Assets\Basic\Scripts\Generated, IDs, Constants, Localization API, and LocalizationText Component will be stored here.
• Localization Output: [Your project path]\Assets\Basic\Resources\Data, Localization data needs to be stored in the Resources folder to load/unload language files.
• Namespace: Excel2Unity.Basic.
• Languages maps: korean, japanese, chinese, we will create a separate characters map for these 3 languages

• Enter the path to the excel file or select the file using the Select File button
• Finally, press Export Json, Export IDs, Export Constants, and Export Localization to export data and scripts

The exported files will be as follows

3. Scripting

Create a ScriptableObject as Static Database

• Create Serializable Objects corresponding to the data fields in the Data Tables.

[Serializable]
public class DataBasic1
{
    public int numberExample1;
    public int numberExample2;
    public int numberExample3;
    public bool boolExample;
    public string stringExample;
}

[Serializable]
public class DataBasic2
{
    [Serializable]
    public class Example
    {
        public int id;
        public string name;
    }

    public string[] array1;
    public int[] array2;
    public int[] array3;
    public bool[] array4;
    public int[] array5;
    public string[] array6;
    public Example json1;
}

//NOTE: To use the Attributes feature, the class needs to inherit from AttributesCollection.
[Serializable]
public class DataAdvanced : AttributesCollection<AttributeParse>
{
    public int id;
    public string name;
}

• Create a ScriptableObject that contains the above Serializable Objects.

[CreateAssetMenu(fileName = "DataCollectionBasic", menuName = "Excel2Unity/DataCollectionBasic")]
public class DataCollectionBasic : ScriptableObject
{
    public List<DataBasic1> dataBasic1;
    public List<DataBasic2> dataBasic2;
    public List<DataAdvanced> dataAdvanced;
}

• Load Json Data into Serializable Objects

// NOTE: This function uses the UnityEditor library, so it must be located in the Editor directory or within #if UNITY_EDITOR
// If you don't want to use Editor code, you can choose to store the Json Data files in the Resources directory or Asset Bundles and load them using the corresponding method.
private void LoadData()
{
    var txt =  AssetDatabase.LoadAssetAtPath<TextAsset>("Assets/Basic/Data/ExampleDataBasic1.txt");
    dataBasic1 = JsonHelper.ToList<DataBasic1>(txt.text);
    txt =  AssetDatabase.LoadAssetAtPath<TextAsset>("Assets/Basic/Data/ExampleDataBasic2.txt");
    dataBasic2 = JsonHelper.ToList<DataBasic2>(txt.text);
    txt = AssetDatabase.LoadAssetAtPath<TextAsset>("Assets/Basic/Data/ExampleDataAdvanced.txt");
    dataAdvanced = JsonHelper.ToList<DataAdvanced>(txt.text);
}

• Whenever there are changes, you just need to edit on excel and export the new data. Then, in Unity, you just need to Reload the Static Database.

Localization

• Change the language.

// Set the language japanese
Localization.currentLanguage = "japanese";

• Register an event handler for the language change event.

// Register an action when language changed
Localization.onLanguageChanged += OnLanguageChanged;

// Display current language
m_txtCurrentLanguage.text = Localization.currentLanguage;

• Get the localized content using a Key. However, with this method, the Text will not automatically update its display when the language changes.

// Get the localized text using integer key
m_txtExample1.text = Localization.Get(Localization.hero_name_1).ToString();
// Get the localized text using string key
m_txtExample2.text = Localization.Get("DAY_X", 100).ToString();

• You can link a gameObject which contain a Text or TextMeshProUGUI Component with a Key so that the Text automatically updates when the language changes.

// Register Dynamic localized Text
Localization.RegisterDynamicText(m_textGameObject1, Localization.hero_name_5);
// Register Dynamic localized Text
Localization.RegisterDynamicText(m_textGameObject2, "TAP_TO_COLLECT");
// Unregister the gameObject
Localization.UnregisterDynamicText(m_textGameObject1);
Localization.UnregisterDynamicText(m_textGameObject2);

• Using LocalizationText Component.

Separate Localization

In case you choose Separate Localization in the Settings table. The output Localization data and Localization scripts will look like this.

The Localization Code will change as follows.

LocalizationsManager.currentLanguage = "spanish";

private IEnumerator Start()
{
    yield return LocalizationsManager.InitAsync(null);
}

// Register an action when language changed
LocalizationsManager.onLanguageChanged += OnLanguageChanged;

// Display current language
m_txtCurrentLanguage.text = LocalizationsManager.currentLanguage;

// Get localized string from sheet ExampleLocalization
m_txtExample1.text = ExampleLocalization.Get(ExampleLocalization.hero_name_1).ToString();
// Get localized string from sheet ExampleLocalization2
m_txtExample2.text = ExampleLocalization2.Get("DAY_X", 100).ToString();

// Register Dynamic localized Text in sheet ExampleLocalization
ExampleLocalization.RegisterDynamicText(m_textGameObject1, ExampleLocalization.hero_name_5);
// Register Dynamic localized Text in sheet ExampleLocalization2
ExampleLocalization2.RegisterDynamicText(m_textGameObject2, "TAP_TO_COLLECT");
// Unregister gameObject
ExampleLocalization.UnregisterDynamicText(m_textGameObject1);
ExampleLocalization2.UnregisterDynamicText(m_textGameObject2);

TextMeshPro custom font.

We will use three files characters_map_japan, characters_map_korean, and characters_map_chinese to create three TextMeshPro fonts for these languages. These three characters_map files contain all the characters appearing in the Localization sheet of each language.

In this example, I use three different fonts to create three TextMeshPro fonts:

• Japanese: NotoSerif-Bold
• Korean: NotoSerifJP-Bold
• Chinese: NotoSerifTC-Bold

For each of these fonts, create a TextMeshPro font. In the Font Asset Creator window, under the Character Set section, select Character From File. Then, select the corresponding characters_map file under the Character File section.

With the features shown, you now have all the tools you need to build a Static Database with Excel. This is enough to meet the needs of any Casual or Mid-core game.