Airtableで宴のシナリオを書く

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using NPOI.SS.UserModel;
using Project.Scripts.Application.Extensions;
using Project.Scripts.Application.Tools.AirtableClient;
using Cysharp.Threading.Tasks;
using UnityEditor;
using UnityEngine;

namespace Project.Editor
{
    public class CreateXlsFromAirTableEditorWindow : EditorWindow
    {
        [SerializeField] private string excelFileName = "Project/Scenario/Scenario.xls";
        [SerializeField] private SheetsName worksheetName = SheetsName.Start;
        [SerializeField] private string accessToken = "key***********";
        private Vector2 _scrollPosition = Vector2.zero;

        [MenuItem("Window/CreateXlsFromAirTable")]
        private static void Create()
        {
            GetWindow<CreateXlsFromAirTableEditorWindow>("UtageScenario");
        }

        private async void OnGUI()
        {
            _scrollPosition = EditorGUILayout.BeginScrollView(_scrollPosition, GUI.skin.scrollView);
            {
                EditorGUILayout.BeginVertical(GUI.skin.box);
                {
                    EditorGUILayout.LabelField("CreateXlsFromAirTable");
                    EditorGUILayout.Space();

                    excelFileName = EditorGUILayout.TextField(".xlsファイルへのローカルパス", excelFileName);
                    accessToken = EditorGUILayout.TextField("Airtable AccessToken", accessToken);

                    EditorGUILayout.Space();
                    EditorGUILayout.LabelField("全てのシート");
                    var allSheetsButton = GUILayout.Button("実行");

                    EditorGUILayout.Space();
                    EditorGUILayout.LabelField("選択したシート");
                    worksheetName = (SheetsName) EditorGUILayout.EnumPopup("シート", worksheetName);
                    var oneSheetButton = GUILayout.Button("実行");

                    if (allSheetsButton)
                    {
                        foreach (SheetsName sheet in Enum.GetValues(typeof(SheetsName)))
                        {
                            var sheetName = sheet.ToString();
                            if (sheet == SheetsName.ParamTbl) sheetName = "ParamTbl{}";
                            var data = await FetchJsonFromAirTableAsync(sheetName);
                            SaveXls(sheetName, data);
                            Debug.Log($"{sheetName}を更新します");
                        }

                        Debug.Log("全てのシートを更新しました");
                        return;
                    }

                    if (oneSheetButton)
                    {
                        var sheetName = worksheetName.ToString();
                        var data = await FetchJsonFromAirTableAsync(sheetName);
                        SaveXls(sheetName, data);
                        Debug.Log($"{sheetName}の更新が完了しました");
                        return;
                    }
                } EditorGUILayout.EndVertical();
            } EditorGUILayout.EndScrollView();
        }

        private async UniTask<IEnumerable<Dictionary<string, dynamic>>> FetchJsonFromAirTableAsync(string sheetName)
        {
            var client = new AirtableClient(accessToken);
            var tableBase = client.GetBase("app*************");
            var allRows = await tableBase.LoadTableAsync<Dictionary<string, dynamic>>(sheetName);

            return allRows;
        }

        private void SaveXls(string sheetName, IEnumerable<Dictionary<string, dynamic>> fields)
        {
            try
            {
                var excelFilePath = $"{UnityEngine.Application.dataPath}/{excelFileName}";
                
                //ブック読み込み
                var book = WorkbookFactory.Create(excelFilePath);

                //シート名からシート作成
                var sheet = book.GetSheet(sheetName);
                if (sheet == null)
                {
                    sheet = book.CloneSheet(book.GetSheetIndex(SheetsName.Start.ToString()));
                    book.SetSheetName(book.GetSheetIndex(sheet), sheetName);
                }
                for (var i = 1;  i <= sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    if (row != null) sheet.RemoveRow(row);
                }

                var fieldTypes = sheet.GetRow(0).Cells.Select(cell => cell.StringCellValue).ToList();

                //セルに設定
                foreach (var (field, r) in fields.Indexed())
                {
                    var row = r + 1;
                    if(sheet.GetRow(row) != null) sheet.RemoveRow(sheet.GetRow(row));
                    foreach (var data in field)
                    {
                        WriteCell(
                            sheet, 
                            fieldTypes.FindIndex(type => type == data.Key),
                            row,
                            data.Value);
                    }
                }

                //ブックを保存
                using (var fs = new FileStream(excelFilePath, FileMode.Create))
                {
                    book.Write(fs);
                }
            }
            catch (Exception ex)
            {
                Debug.Log(ex);
            }
        }

        //セル設定
        private static void WriteCell<T>(ISheet sheet, int columnIndex, int rowIndex, T value)
        {
            var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
            var cell = row.GetCell(columnIndex) ?? row.CreateCell(columnIndex);

            cell.SetCellValue(value);
        }
        
        private enum SheetsName
        {
            Character,
            Texture,
            Particle,
            Layer,
            EyeBlink,
            LipSynch,
            Animation,
            Sound,
            Param,
            ParamTbl,
            SceneGallery,
            Localize,
            Macro,
            Start,
        }
    }

    internal static class NPoiExtension
    {
        internal static void SetCellValue<T>(this ICell cell, T value)
        {
            if (typeof(T) == typeof(double))
            {
                var d = value is double dValue
                    ? dValue
                    : 0;
                cell.SetCellValue(d);
            }
            else if (typeof(T) == typeof(DateTime))
            {
                var dt = value is double dtValue
                    ? dtValue
                    : 0;
                cell.SetCellValue(dt);
            }
            else cell.SetCellValue(value as string);
        } 
    }
}

private async UniTask<IEnumerable<Dictionary<string, dynamic>>> FetchJsonFromAirTableAsync(string sheetName)
{
    var url = $@"{airTableUrl}/{sheetName}?view=Grid%20view";
    using (var request = UnityWebRequest.Get(url))
    {
        request.SetRequestHeader("Authorization", $"Bearer {accessToken}");
        await request.SendWebRequest();
        var fields = JsonSerializer.Deserialize<Dictionary<string, List<Dictionary<string, dynamic>>>>
            ($@"{request.downloadHandler.text}")["records"]
            .Select(record => record["fields"]);
        var fieldsText = JsonSerializer.ToJsonString(fields);
        
        return JsonSerializer.Deserialize<List<Dictionary<string, dynamic>>>($@"{fieldsText}");
    }
}

private enum SheetsName
{
     Character,
     Texture,
     Particle,
     Layer,
     EyeBlink,
     LipSynch,
     Animation,
     Sound,
     Param,
     ParamTbl,
     SceneGallery,
     Localize,
     Macro,
     Start,
}

public class CreateXlsFromAirTableEditorWindow : EditorWindow
{
    [SerializeField] private string airTableUrl  = default;
    [SerializeField] private string accessToken = default;
    [SerializeField] private string excelFileName = "Project/Scenario/Scenario.xls";
    [SerializeField] private SheetsName worksheetName = SheetsName.Start;
    [MenuItem("Window/CreateXlsxFromAirTable")]
    private static void Create()
    {
        GetWindow<CreateXlsFromAirTableEditorWindow>("CreateXlsxFromAirTable");
    }

    private async void OnGUI()
    {
        EditorGUILayout.LabelField("CreateXlsxFromAirTable");
        EditorGUILayout.Space();
        excelFileName = EditorGUILayout.TextField("ExcelFileName", excelFileName);
        accessToken = EditorGUILayout.TextField("AccessToken of AirTable", accessToken);
        EditorGUILayout.Space();
        EditorGUILayout.LabelField("All sheets");
        var allSheetsButton = GUILayout.Button("Download all the sheets");       
        EditorGUILayout.Space();
        EditorGUILayout.LabelField("Selected sheet");
        worksheetName = (SheetsName) EditorGUILayout.EnumPopup("WorksheetName", worksheetName);
        var oneSheetButton = GUILayout.Button("Download the selected sheet");
        
        if (allSheetsButton)
        {
            foreach (SheetsName sheet in Enum.GetValues(typeof(SheetsName)))
            {
                var sheetName = sheet.ToString();
                if (sheet == SheetsName.ParamTbl) sheetName = "ParamTbl{}"; // 「宴」のシート名に「{}」が含まれるのでその対応
                var data = await FetchJsonFromAirTableAsync(sheetName);
                SaveXls(sheetName, data);
                Debug.Log($"update {sheetName}");
            }
            Debug.Log($"Update all the sheets");
        }
        if (oneSheetButton)
        {
            var sheetName = worksheetName.ToString();
            var data = await FetchJsonFromAirTableAsync(sheetName);
            SaveXls(sheetName, data);
            Debug.Log($"Update {sheetName}");
        }
    }

    private void SaveXls(string sheetName, IEnumerable<Dictionary<string, dynamic>> fields)
    {
            var excelFilePath = $"{UnityEngine.Application.dataPath}/{excelFileName}";
            
            var book = WorkbookFactory.Create(excelFilePath);
            var sheet = book.GetSheet(sheetName);
            var fieldTypes = sheet.GetRow(0).Cells.Select(cell => cell.StringCellValue).ToList();
            foreach (var (field, r) in fields.Indexed())
            {
                var row = r + 1;
                if(sheet.GetRow(row) != null) sheet.RemoveRow(sheet.GetRow(row));
                foreach (var data in field)
                {
                    WriteCell(
                        sheet, 
                        fieldTypes.FindIndex(type => type == data.Key),
                        row,
                        data.Value);
                }
            }
            using (var fs = new FileStream(excelFilePath, FileMode.Create))
            {
                book.Write(fs);
            }
    }

    private static void WriteCell(ISheet sheet, int columnIndex, int rowIndex, string value)
    {
        var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
        var cell = row.GetCell(columnIndex) ?? row.CreateCell(columnIndex);
        cell.SetCellValue(value);
    }

    private static void WriteCell(ISheet sheet, int columnIndex, int rowIndex, double value)
    {
        var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
        var cell = row.GetCell(columnIndex) ?? row.CreateCell(columnIndex);
        cell.SetCellValue(value);
    }
}