Unity读取excel 数据 并创建对应的json文件

https://download.csdn.net/download/jinxiul5/85188120

工具下载链接是 :

https://download.csdn.net/download/jinxiul5/85188120

 

 

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

namespace ExcelTool
{

   // 如果xlsx文件的后缀为.xls，读取的代码应该为
   //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
   //如果xlsx文件的后缀为.xlsx，读取的代码应该为
   //IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

    public class ExcelTool
    {
        /// <summary>
        /// 读取表数据，生成对应的数组
        /// </summary>
        /// <param name="filePath">excel文件全路径</param>
        /// <returns>Item数组</returns>
        public static ExcelDataRow[] CreateItemArrayWithExcel(string filePath)
        {
            //获得表数据
            int columnNum = 0, rowNum = 0;
            //读取excel
            DataRowCollection collect = ReadExcel(filePath, ref columnNum, ref rowNum);

            //根据excel的定义，第二行开始才是数据 
            ExcelDataRow[] array = new ExcelDataRow[rowNum];
            for (int i = 0; i < rowNum; i++)
            {
                ExcelDataRow item = new ExcelDataRow();
                //解析每列的数据
                item.level = int.Parse(collect[i][0].ToString());
                item.id = int.Parse(collect[i][1].ToString());
                item.phrase = collect[i][2].ToString();
                array[i] = item;
            }
            return array;
        }

        /// <summary>
        /// 读取excel文件内容
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="columnNum">行数</param>
        /// <param name="rowNum">列数</param>
        /// <returns></returns>
        static DataRowCollection ReadExcel(string filePath, ref int columnNum, ref int rowNum)
        {
            FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

            //返回的结果
            DataSet result = excelReader.AsDataSet();
            //Tables[0] 下标0表示excel文件中第一张表的数据
            columnNum = result.Tables[0].Columns.Count;
            rowNum = result.Tables[0].Rows.Count;
            return result.Tables[0].Rows;
        }
    }


    public class ExcelBuild : Editor
    {
        public class ExcelConfig
        {
            /// <summary>
            /// 存放excel表文件夹的的路径，本例xecel表放在了"Assets/Excels/"当中
            /// </summary>
            public static readonly string excelsFolderPath = 
                Application.dataPath + "/ReadExcel/Excel/";

            /// <summary>
            /// 存放Excel转化CS文件的文件夹路径
            /// </summary>
            public static readonly string assetPath = "Assets/Resources/ExcelDataAssets/"; 
            
            /// <summary>
            /// 存放josn转化CS文件路径
            /// </summary>
            public static readonly string assetjsonPath = Application.dataPath + "/ReadExcel/Json/";

        }


        [MenuItem("ExcelTool/CreateExcelJson")]
        public static void CreatJsonData()
        {
            ExcelDataRow[] excelData;
            //赋值
            excelData =
                ExcelTool.CreateItemArrayWithExcel(ExcelConfig.excelsFolderPath + "cylevel.xlsx");

            foreach (var item in excelData)
            {
                Debug.Log(item.phrase);
            }

            JsonLevelData leveData = new JsonLevelData();

            //因为 第一行是头文件介绍 所以从第二行开始添加
            int count = 1;

            while (true)
            {
                int back = 0;
                List<string> Alevel = new List<string>();
                for (int i = 0; i < excelData.Length; i++)
                {
                    if (excelData[i].level== count)
                    {
                        back++;
                        Alevel.Add(excelData[i].phrase);
                    }
                }
                leveData.level.Add(Alevel);
                if (back==0)
                {
                    break;
                }
                count++;
            }
            string levelJsonData = LitJson.JsonMapper.ToJson(leveData);

            Regex reg = new Regex(@"(?i)\\[uU]([0-9a-f]{4})");//正则表达式规定格式
            var levelJsonDataChinese = reg.Replace(levelJsonData,
            delegate (Match m)
            {
                return ((char)Convert.ToInt32(m.Groups[1].Value, 16)).ToString();
            });


            //确保文件夹存在
            if (!Directory.Exists(ExcelConfig.assetjsonPath))
            {
                Directory.CreateDirectory(ExcelConfig.assetjsonPath);
            }

            if (!File.Exists(ExcelConfig.assetjsonPath+"level.json"))
            {
                File.Create(ExcelConfig.assetjsonPath+"level.json");
               
            }
            File.WriteAllText(ExcelConfig.assetjsonPath + "level.json", levelJsonDataChinese);
            AssetDatabase.Refresh();
        }



    }



    /// <summary>
    /// Excel所有数据
    /// </summary>
    public class ExcelData : ScriptableObject
    {
        public ExcelDataRow[] excelDataArray;
    }

    /// <summary>
    ///Excel 每行的数据
    /// </summary>
    [System.Serializable]
    public class ExcelDataRow
    {
        public int level;

        public int id;

        public string phrase;
    }



    /// <summary>
    /// 关卡数据类
    /// </summary>
    public class JsonLevelData
    {
        public JsonLevelData()
        {
            level = new List<List<string>>();
        }
        /// <summary>
        /// 所有关卡数据
        /// </summary>
        public List<List<string>> level;
    }



}