ExcelToJson

using System;
using System.Collections.Generic;
using Excel;
using System.Data;
using System.IO;
using LitJson;
using System.Text;
using System.Text.RegularExpressions;
using UnityEngine;

public class TOJSON
{
    private static readonly char nodeSignal = '^';
    private static readonly string nodeTableSignal = "+";
    private static readonly string arrayColumnSignal = "#";
    private static readonly string tableToDic = "~";
    private static readonly string uselessCol = "&";

    private static readonly string excelPath = Application.dataPath +
                                               @"/../../../../../Doc/Config/GameConfig";

    /// <summary>
    /// 文件保存目录
    /// </summary>
    private static readonly string SavePath = Application.streamingAssetsPath + "/ConfigDatas";

    static string pattern = "[\u4e00-\u9fbb]";

    public static void Load()
    {
        if (Directory.Exists(TOJSON.SavePath))
        {
            Directory.Delete(TOJSON.SavePath,true);
        }

        Directory.CreateDirectory(TOJSON.SavePath);

        AutoScanFile(excelPath, false);
    }
    
    private static void AutoScanFile(string dir, bool islIST)
    {
        if (!Directory.Exists(dir)) return;

        string[] tempDirs = Directory.GetDirectories(dir);

        foreach (var item in tempDirs)
        {
            AutoScanFile(item, islIST);
        }

        string[] files = Directory.GetFiles(dir);

        Debug.Log(files.Length);
        foreach (var item in files)
        {
            if (!string.Equals(Path.GetExtension(item), ".xlsx")) continue;
            if (Path.GetFileName(item).Contains("~$")) continue;

            Excel2Json(item);
        }
    }
    
    private static void Excel2Json(string excelPath)
    {

        try
        {
            if (!File.Exists(excelPath))
            {
                Debug.Log(excelPath + ":不存在");
                return;
            }
            
            excelPath=excelPath.Replace("/", "\\");
            
            FileStream stream = File.Open(excelPath, FileMode.Open, FileAccess.Read);
            
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

            DataSet result = excelReader.AsDataSet();

            string table = "";

            foreach (DataTable item in result.Tables)
            {
                Save2Json(item);
                
                Debug.Log(item);
            }
        }
        catch (Exception e)
        {
            Debug.LogError(e);
            throw;
        }
        
    }
    
    private static void Save2Json(DataTable table)
    {

        int startRow = 1;

        if (table.TableName.Contains(nodeTableSignal)
            || Regex.IsMatch(table.TableName, pattern)) return;
       
        string json;

        int rowCount = table.Rows.Count;
        int columnsCount = table.Columns.Count;

        List<string> keys = GetPropertyName(table.Rows[startRow], columnsCount);

        if(table.TableName.Contains(tableToDic))
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();

            for (int i = 0; i < rowCount; i++)
            {
                DataRow row = table.Rows[i];
                dict[row[0].ToString()] = row[1].ToString();
            }

            json = JsonMapper.ToJson(dict);
        }
        else
        {
            StringBuilder builder = new StringBuilder();
            JsonWriter writer = new JsonWriter(builder);

            writer.WriteObjectStart();
            writer.WritePropertyName("datas");

            writer.WriteArrayStart();
            for (int i = startRow + 1; i < rowCount; i++)
            {
                if (string.IsNullOrEmpty(table.Rows[i][0].ToString())) continue;
                DataRow row = table.Rows[i];

                writer.WriteObjectStart();
                for (int j = 0; j < columnsCount; j++)
                {
                    //无效列判断
                    if (keys[j].Contains(uselessCol)) continue;
                    
                    
                    string value = row[j].ToString();

                    if (string.IsNullOrEmpty(value)) continue;
                    if ((value == "0" || value == "Null") &&
                        !keys[j].Contains(arrayColumnSignal)) continue;

                    writer.WritePropertyName(RemoveColumnNameSignal(keys[j]));

                    //被括号包裹的数据
                    if (value[0] == '(')
                    {
                        if (value[value.Length - 1] == ')')
                        {
                            writer.WriteObjectStart();
                            string tempValue = value.Substring(1, value.Length - 2);
                            string[] values = tempValue.Split(',');
                            for (int k = 0; k < values.Length; k++)
                            {
                                string[] item = values[k].Split(':');
                                writer.WritePropertyName(item[0]);
                                writer.Write(item[1]);
                            }
                            writer.WriteObjectEnd();
                        }
                    }
                    //转换成Array
                    else if (value.Contains("|") || keys[j].Contains(arrayColumnSignal))
                    {
                        string[] values = value.Split('|');
                        writer.WriteArrayStart();
                        for (int k = 0; k < values.Length; k++)
                        {
                            if (string.IsNullOrEmpty(values[k])) continue;

                            writer.Write(values[k]);
                        }
                        writer.WriteArrayEnd();
                    }
                    else
                    {
                        value = RemoveNote(value);
                        writer.Write(value);
                    }
                }

                writer.WriteObjectEnd();
            }

            writer.WriteArrayEnd();
            writer.WriteObjectEnd();

            json = builder.ToString();
        }

       

        File.WriteAllText(SavePath + "/" + table.ToString().Replace(tableToDic, "") + ".json", json);
    }

    /// <summary>
    /// 移除列名中的特殊标记
    /// </summary>
    /// <returns></returns>
    private static string RemoveColumnNameSignal(string columnName)
    {
        return columnName.Replace(arrayColumnSignal, "");
    }

    /// <summary>
    /// 移除注释:^符号后的内容为注释内容
    /// </summary>
    /// <param name="value"></param>
    /// <returns></returns>
    private static string RemoveNote(string value)
    {
        int index = value.IndexOf(nodeSignal);
        if (index < 1) return value;
        value = value.Substring(0, index);
        return value;
    }

    private static List<string> GetPropertyName(DataRow row, int count)
    {
        List<string> result = new List<string>();
        for (int i = 0; i < count; i++)
        {
            string listName = row[i].ToString();
            result.Add(row[i].ToString());
        }
        return result;
    }
}