kimsama/Unity-QuickSheet

About multiple worksheets combine to a asset

Personuo opened this issue · 3 comments

Hello,this is a great asset,I'm using it in my new project,and I have a question.
Is it possible to combine multiple worksheets or xls of the same format into one ASSET? For example, I have a lot of data in an equipment table and I want to split it into 2 tables, but I need them to be combined in one asset

You can make 1 total data. Add 1 button combine to copy all data from single data

Add 1 button combine to copy all data from single data

What does the above mean?

I think this request could be“ one datamodel used as a common datamodel for multiple-xls files” in my words.
if so,you can check the code below.

using UnityEngine;
using UnityEditor;
using System.IO;
using UnityQuickSheet;

public static class ExcelFileProcessor
{
    static string modelDataPath = "Assets/Model/PropertyData"; //1.  you must gather your xls in a specify folder first 
    [MenuItem("WebGL/Convert .xlsx to asset")]   //2. then you can use this menu to convert each xls to a unity asset
    public static void FileHandler()
    {
        var files = Directory.GetFiles(modelDataPath, "*.xlsx", SearchOption.AllDirectories);
        foreach (var item in files)
        {
            var filename = Path.GetFileName(item);
            if (filename.StartsWith("~")) continue; //2.1 XLS temp file always start with "~" , ignore them.
            CreatAndFillDataFromExcelFile(item);
        }
        Debug.Log($"{nameof(ExcelFileProcessor)}:  所有 excel 文件处理完毕 !");
        AssetDatabase.Refresh();
    }
    [MenuItem("WebGL/删除转换好的数据文件")] // 3. you can delete those converted asset as you wish for some reason
    public static void AssetFileHandler()
    {
        var files = Directory.GetFiles(modelDataPath, "*.xlsx", SearchOption.AllDirectories);
        foreach (var file in files)
        {
            var filename = Path.GetFileName(file);
            if (filename.StartsWith("~")) continue;
            var assetFilePath = file.Substring(0, file.LastIndexOf(".")) + ".asset";
            //var assetFilePath = file.Substring(0, file.LastIndexOf(@"\")+1) + "Data.asset";
            AssetDatabase.DeleteAsset(assetFilePath);
        }
        Debug.Log($"{nameof(ExcelFileProcessor)}:  所有 asset 文件处理完毕 !");
        AssetDatabase.Refresh();
    }

    const string sheetName = "Sheet1";
    const string assetsuffix = ".asset";
    private static void CreatAndFillDataFromExcelFile(string file)
    {
        var assetFilePath = file.Substring(0, file.LastIndexOf(".")) + assetsuffix;
        Sheet1 data = (Sheet1)AssetDatabase.LoadAssetAtPath(assetFilePath, typeof(Sheet1));
        if (data == null)
        {
            data = ScriptableObject.CreateInstance<Sheet1>();
            data.SheetName = file;
            data.WorksheetName = sheetName;
            AssetDatabase.CreateAsset(data, assetFilePath);
        }
        ExcelQuery query = new ExcelQuery(file, sheetName);
        if (query != null && query.IsValid())
        {
            data.dataArray = query.Deserialize<Sheet1Data>().ToArray(); // 4. Sheet1Data  should be replaced by your data model.
            ScriptableObject obj = AssetDatabase.LoadAssetAtPath<ScriptableObject>(assetFilePath);
            EditorUtility.SetDirty(obj);
        }
    }
}

put this code in a folder named Editor .
see commit in this code , you'd better make it fit you situation.
and combine those asset you can do it by yourself. just operate arrays .