在Unity上编写一个小工具--Excel导出Json

这个工具运行需要一个插件----NPOI，这个是用来读取excel文件的，这个插件的dll文件可以去网上找一下

------------------------------------------------------注意-----------------------------------------------------------------

//需要在unity工程根目录下创建“Excel”文件夹（这个根据个人需求可以更改），用来存放excel文件，
//导出会把该文件夹下的所有文件导出，如果一个excel文件有多个sheet，会分成多个json文件

//用WPS创建的excel文件，使用NPOI会报错，这个要注意一下

------------------------------------------------------注意-----------------------------------------------------------------

excel文件内容：

导出的样子大致是这样：

{"data":[{"a":10,"b":"adadada","c":true,"d":10.22},{"a":20,"b":"","c":false,"d":5.6}]}

这个格式可以改的，改代码就行

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using UnityEditor;
using UnityEngine;

public class ExcelExportJsonEditor : EditorWindow
{
    [MenuItem("Tools/Excel导出")]
    public static void ShowWindow()
    {
        CreateInstance<ExcelExportJsonEditor>().Show();
    }

    private void OnGUI()
    {
        if (GUILayout.Button("Excel导出Json"))
        {
            if (Directory.Exists("./Excel"))
            {
                foreach (var filePath in Directory.GetFiles("./Excel"))
                {
                    ExportJson(filePath);
                }
            }
            else
            {
                Debug.Log("无");
            }
        }
    }

    private Dictionary<string, string> firstRowCells;

    private void ExportJson(string filePath)
    {
        if (Path.GetFileName(filePath).StartsWith("~"))
        {
            return;
        }

        var extension = Path.GetExtension(filePath);

        if (extension != ".xlsx" && extension != ".xls")
        {
            return;
        }

        var fileName = Path.GetFileNameWithoutExtension(filePath);
        IWorkbook wk = null;
        try
        {
            if (extension == ".xlsx")
            {
                wk = new XSSFWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite));
            }
            else if (extension == ".xls")
            {
                wk = new HSSFWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite));
            }

            if (!Directory.Exists("./Json")) Directory.CreateDirectory("./Json");
            var exportPath = Path.Combine("./Json", $"{fileName}");

            for (int i = 0; i < wk.NumberOfSheets; i++)
            {
                ISheet sheet = wk.GetSheetAt(i);
                IRow firstRow = sheet.GetRow(0);
                if (firstRow == null)
                {
                    //第一行必须要写内容
                    Debug.Log($"{fileName}---{sheet.SheetName}的第一行什么都没有....");
                    continue;
                }
                //读取第一行每个单元的内容格式：(name&type)
                firstRowCells = new Dictionary<string, string>();
                foreach (var cell in firstRow.Cells)
                {
                    var strs = cell.ToString().Split('&');
                    firstRowCells.Add(strs[0], strs[1]);
                }

                using (FileStream fs = File.Create($"{exportPath}_{i}.json"))
                {
                    using (StreamWriter sw = new StreamWriter(fs))
                    {
                        StringBuilder sb = new StringBuilder();
                        sb.Append("{\"data\":[");

                        for (int j = 1; j < sheet.LastRowNum + 1; j++)
                        {
                            sb.Append("{");
                            IRow row = sheet.GetRow(j);
                            if (row != null)
                            {
                                for (int k = 0; k < firstRow.Cells.Count; k++)
                                {
                                    sb.Append($"\"{firstRowCells.Keys.ElementAt(k)}\":");
                                    var type = firstRowCells.Values.ElementAt(k);
                                    var cell = row.GetCell(k);
                                    switch (type)
                                    {
                                        case "number":
                                            if (cell == null)
                                            {
                                                sb.Append("0");
                                            }
                                            else
                                            {
                                                sb.Append($"{cell}");
                                            }
                                            break;
                                        case "string":
                                            if (cell == null)
                                            {
                                                sb.Append("\"\"");
                                            }
                                            else
                                            {
                                                sb.Append($"\"{cell}\"");
                                            }
                                            break;
                                        case "bool":
                                            if (cell == null)
                                            {
                                                sb.Append("false");
                                            }
                                            else
                                            {
                                                sb.Append(cell.ToString().ToLower());
                                            }
                                            break;
                                    }

                                    if (k != firstRow.Cells.Count - 1)
                                        sb.Append(",");
                                }
                            }

                            if (j == sheet.LastRowNum)
                                sb.Append("}");
                            else
                                sb.Append("},");
                        }
                        sb.Append("]}");
                        sw.Write(sb);
                    }
                }
            }

            Debug.Log($"《{fileName}》导出完成....");
        }
        catch (Exception e)
        {
            Debug.Log(e);
            Debug.Log($"《{fileName}》导出失败....");
        }
    }
}