using UnityEngine;
using UnityEditor;
using System.Collections;
using OfficeOpenXml;
using System.IO;
using System.Collections.Generic;
using LitJson;
using System.Text;

public class Excel4Unity : Editor
{

[MenuItem("Excel4Unity/Test/ReadWrite")] 
static void ReadWrite()
{
    Excel xls = new Excel();
    ExcelTable table = new ExcelTable();
    table.TableName = "test";
    string outputPath = Application.dataPath + "/Test/Test2.xlsx";
    xls.Tables.Add(table);
    Debug.Log("數(shù)量==" + xls.Tables.Count);
    xls.Tables[0].SetValue(1, 1, "1");
    xls.Tables[0].SetValue(1, 2, "2");
    xls.Tables[0].SetValue(2, 1, "3");
    xls.Tables[0].SetValue(2, 2, "4");
    xls.ShowLog();
    ExcelHelper.SaveExcel(xls, outputPath);
}
/// <summary>
/// 讀
/// </summary>
[MenuItem("Excel4Unity/Test/Read")] 
static void Read()
{
    string path = Application.dataPath + "/Test/Test3.xlsx";
    Excel xls =  ExcelHelper.LoadExcel(path);
    xls.ShowLog();
}
/// <summary>
/// 新建一張excel表，覆蓋了原來(lái)的表（沒有這張表就創(chuàng)建一下）
/// </summary>
[MenuItem("Excel4Unity/Test/Write")] 
static void Write()
{
    Excel xls = new Excel();
    ExcelTable table = new ExcelTable();
    table.TableName = "test";
    string outputPath = Application.dataPath + "/Test/Test5.xlsx";
    xls.Tables.Add(table);
    xls.Tables[0].SetValue(1, 1, Random.Range(1000,100000).ToString());
    xls.ShowLog();
    ExcelHelper.SaveExcel(xls, outputPath);
}
/// <summary>
/// 把.xlsx根據(jù)寫好的格式轉(zhuǎn)換成.cs  
/// 這里設(shè)置.xlsx的第一行為字段名字 ed.FieldNameLine = 1;
/// 這里設(shè)置.xlsx的第二行為字段類型 ed.FieldTypeLine = 2;
/// 這里設(shè)置.xlsx的第三行為字段值   ed.FieldValueLine = 3;
/// 這里設(shè)置如果字段名字為空或者類型為空或者字段名字以#為開頭則不添加該字段锰霜，ed.IgnoreSymbol = "#";#為忽略值
/// </summary>
[MenuItem("Excel4Unity/Test/GenerateModel")] 
static void GenerateModel()
{
    string path = Application.dataPath + "/Test/Test4.xlsx";
    Excel xls =  ExcelHelper.LoadExcel(path);
    ExcelDeserializer ed = new ExcelDeserializer();
    ed.FieldNameLine = 1;
    ed.FieldTypeLine = 2;
    ed.FieldValueLine = 3;
    ed.IgnoreSymbol = "#";
    ed.ModelPath = Application.dataPath + "/Editor/Excel4Unity/DataItem.txt";
    ed.GenerateCS(xls.Tables[1]);
}

[MenuItem(@"Excel4Unity/Test/Excel2JSON")]
static void Excel2JSON()
{
    Object[] objs = Selection.objects;
    for (int i = 0; i < objs.Length; i++)
    {
        string path = AssetDatabase.GetAssetPath(objs[i]);
        if (path.EndsWith(".xlsx"))
        {
            Excel4Unity.ParseFile(path);
        }
        else
        {
            EditorUtility.DisplayDialog("提示", "暫不支持的文件格式" + path, "ok");
            return;
        }
    }
    AssetDatabase.Refresh();
}
/// <summary>
/// 解析excel文件在這里 for (int i = 4; i < = table.NumberOfRows; i++)寫死了從第四行開始
/// </summary>
/// <param name="path"></param>
/// <param name="createCS"></param>
/// <param name="isMac"></param>
/// <returns></returns>
public static string ParseFile(string path, bool createCS = true, bool isMac = false)
{
    //      UnityEngine.Debug.LogError ("path " + path);
    if (!path.EndsWith("xlsx"))
    {
        return null;
    }

    string tableName = "";
    string currentPropName = "";
    int tableRow = 0;
    int tableColumn = 0;
    string v = "";
    Excel excel = null;
    excel = ExcelHelper.LoadExcel(path);
    try
    {
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        JsonWriter writer = new JsonWriter(sb);
        writer.WriteObjectStart();
        foreach (ExcelTable table in excel.Tables)
        {
            tableName = table.TableName;
            bool language = tableName.ToLower().Contains("language");
            if (table.TableName.StartsWith("#"))
            {
                continue;
            }
            if (createCS)
            {
                ExcelDeserializer ed = new ExcelDeserializer();
                ed.FieldNameLine = 1;
                ed.FieldTypeLine = 2;
                ed.FieldValueLine = 3;
                ed.IgnoreSymbol = "#";
                ed.ModelPath = Application.dataPath + "/Editor/Excel4Unity/DataItem.txt";
                ed.GenerateCS(table);
            }
            writer.WritePropertyName(table.TableName);
            writer.WriteArrayStart();
            //這里寫死了從excel的第四行開始解析
            for (int i = 4; i <= table.NumberOfRows; i++)
            {
                tableRow = i;
                string idStr = table.GetValue(i, 1).ToString();
                if (idStr.Length <= 0)
                {
                    //                      UnityEngine.Debug.LogError ("ID error:" + tableName + "  (第" + i + "行)");
                    break;
                }
                writer.WriteObjectStart();

                for (int j = 1; j <= table.NumberOfColumns; j++)
                {
                    tableColumn = j;
                    string propName = table.GetValue(1, j).ToString();
                    string propType = table.GetValue(3, j).ToString();
                    propName = propName.Replace("*", "");
                    currentPropName = propName;

                    if (propName.StartsWith("#"))
                    {
                        continue;
                    }
                    if (string.IsNullOrEmpty(propName) || string.IsNullOrEmpty(propType))
                    {
                        continue;
                    }
                    writer.WritePropertyName(propName);
                    v = table.GetValue(i, j).ToString();
                    if (propType.Equals("int"))
                    {
                        int value = v.Length > 0 ? int.Parse(v) : 0;
                        writer.Write(value);
                    }
                    else if (propType.Equals("bool"))
                    {
                        int value = v.Length > 0 ? int.Parse(v) : 0;
                        writer.Write(value);
                    }
                    else if (propType.Equals("float"))
                    {
                        float value = v.Length > 0 ? float.Parse(v) : 0;
                        writer.Write(value);
                    }
                    else
                    {
                        string ss = table.GetValue(i, j).ToString();
                        if (language && ss.Contains(" "))
                        {
                            ss = ss.Replace(" ", "\u00A0");
                        }
                        writer.Write(ss);
                    }
                }
                writer.WriteObjectEnd();
            }
            writer.WriteArrayEnd();
        }
        writer.WriteObjectEnd();
        string outputDir = Application.dataPath + "/Resources/DataFiles/";
        string outputPath = outputDir + Path.GetFileNameWithoutExtension(path) + ".txt";
        if (!Directory.Exists(outputDir)) {
            Directory.CreateDirectory(outputDir);
        }
        string str = string.Empty;
        if (File.Exists(path))
        {
            byte[] bytes = File.ReadAllBytes(path);
            UTF8Encoding encoding = new UTF8Encoding();
            str = encoding.GetString(bytes);
        }
        string content = sb.ToString();
        if (str != content)
        {
            File.WriteAllText(outputPath, content);
        }
        Debug.Log("convert success! path = " + path);

        return sb.ToString();
    }
    catch (System.Exception e)
    {
        if (excel == null)
        {
            //                EditorUtility.DisplayDialog("ERROR!", "open excel failed!","ok"); 
            UnityEngine.Debug.LogError("open excel failed!");
        }
        else
        {
            string msg = "解析錯(cuò)誤！ \n表:" + tableName + " \n字段:" + currentPropName + "  \n第" + tableRow + "行,第" + tableColumn + "列 \nvalue = " + v;
            EditorUtility.DisplayDialog("error!", msg, "ok");
            UnityEngine.Debug.LogError(e);
            UnityEngine.Debug.LogError(e.StackTrace);
            UnityEngine.Debug.LogError(msg);
        }
        UnityEngine.Debug.LogError(e.StackTrace.ToString());
        return null;
    }
}

}

using UnityEngine;
using System.Collections;
using LitJson;
/// <summary>
/// 此為生成的類
/// </summary>
public class TestItem : DataItem {
public int ID;
public override int Identity(){ return ID; }
public string Name;
public int Type;

public override void Setup(JsonData data) {
    base.Setup(data);
    ID = int.Parse(data["ID"].ToString());
    Name = data["Name"].ToString();
    Type = int.Parse(data["Type"].ToString());

}

public TestItem () {

}

}

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using LitJson;
public class TestExcel2Json : MonoBehaviour {

TestItem testItem;



// Use this for initialization
void Start () {
    //拿到測(cè)試類對(duì)象
    testItem = new TestItem();
    //加載寫好的json
    Object obj= Resources.Load("DataFiles/Test4") ;
    //把加載的數(shù)據(jù)轉(zhuǎn)為string
    string str = obj.ToString();
    Debug.Log("str==" + str);
    //new一個(gè)jsondata
    JsonData data = new JsonData();
    //把加載的字符串轉(zhuǎn)換成obj給data
    data = JsonMapper.ToObject(str);
    Debug.Log("data==" + data["Test"][0]);
    //調(diào)用setup函數(shù)給測(cè)試類字段賦值
    testItem.Setup(data["Test"][0]);
    Debug.Log("ID==" + testItem.ID);
    //Debug.Log(data[0]);
}

// Update is called once per frame
void Update () {
    
}

}