Unity Excel转json且自动生成C#脚本

大家好，又见面了，我是你们的朋友全栈君。

脚本：

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEditor;
using System.Windows.Forms; //必须是 Unity安装目录\Editor\Data\Mono\lib\mono\2.0下的System.Windows.Forms.dll, 否则会导致报错或者Unity闪退
using System.Data;
using OfficeOpenXml.DataValidation;
using Excel;
using System.IO;
using LitJson;
using System.Text;
using System.Text.RegularExpressions;
using System;
using System.CodeDom;
using System.Reflection;
using System.CodeDom.Compiler;
public class ExcelToJson : EditorWindow
{ 

List<string> ExcelPath = new List<string>();
string JsonPath;
string CSharpPath;
string JsonName;
List<string> dataType = new List<string>();
List<string> dataName = new List<string>();
List<string[]> ExcelDateList = new List<string[]>();
[UnityEditor.MenuItem("Tools/ExcelToJson")]
static void ExceltoJson()
{ 

ExcelToJson toJson = (ExcelToJson)EditorWindow.GetWindow(typeof(ExcelToJson), true, "ExcelToJson");
toJson.Show();
}
private void OnGUI()
{ 

Color oldColor = GUI.backgroundColor;
GUI.backgroundColor = Color.red;
if (GUILayout.Button("选择需要转换的excel文件"))
{ 

GetAllExcelPath();
}
GUI.backgroundColor = oldColor;
//Color color = new Color(201, 232, 255);
//GUI.backgroundColor = Color.yellow;
//if (GUILayout.Button("ExcelToJson"))
//{ 

// CreatJsonFile();
//}
//GUI.backgroundColor = oldColor;
//GUI.backgroundColor = Color.gray;
//if (GUILayout.Button("CreatCSharp"))
//{ 

// CreatCSharp();
//}
//GUI.backgroundColor = oldColor;
}
#region Excel文件处理
void GetAllExcelPath()
{ 

OpenFileDialog openFlie = new OpenFileDialog();
openFlie.Title = "选择需要转换的excel文件";
openFlie.InitialDirectory = @"F:\Cards\Tools\Excel";
//openFlie.Filter = "(*.xlsm)|*.xlsm)";
openFlie.Multiselect = true;    //可以多选
ExcelPath.Clear();
if (openFlie.ShowDialog() == DialogResult.OK)
{ 

string[] strPath = openFlie.FileNames;
for (int i = 0; i < strPath.Length; i++)
{ 

ExcelPath.Add(strPath[i]);
Debug.LogError(ExcelPath[i]);
ReadExcel(strPath[i].Replace("\\", "/"));
}
}
}
/// <summary>
/// 读取Excel
/// </summary>
/// <param name="path">excel路径</param>
/// <param name="columnNum">列</param>
/// <param name="rowNum">行</param>
void ReadExcel(string path)
{ 

FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet data = excelReader.AsDataSet();
dataName.Clear();
dataType.Clear();
//ExcelDateList.Clear();
// 读取Excel的所有页签
for (int i = 0; i < data.Tables.Count; i++)
{ 

DataRowCollection dataRow = data.Tables[i].Rows;            // 每行
DataColumnCollection dataColumn = data.Tables[i].Columns;   // 每列
string tableName = data.Tables[i].TableName;
JsonPath = UnityEngine.Application.dataPath + "/Editor/Json/";
JsonName = tableName + ".json";
JsonPath = JsonPath + JsonName;
CSharpPath = UnityEngine.Application.dataPath + "/Scripts/ClassMgr/" + tableName + ".cs";
for (int rowNum = 0; rowNum < data.Tables[i].Rows.Count; rowNum++)
{ 

string[] table = new string[data.Tables[i].Columns.Count];
for (int columnNum = 0; columnNum < data.Tables[i].Columns.Count; columnNum++)
{ 

if (rowNum == 0)  // 第一行的值：数据类型
{ 

dataType.Add(data.Tables[i].Rows[0][columnNum].ToString());
}
else if (rowNum == 1)  // 第二行的值：数据名
{ 

dataName.Add(data.Tables[i].Rows[1][columnNum].ToString());
}
else
{ 

//Debug.Log(data.Tables[i].Rows[rowNum][columnNum].ToString() + "\n");
table[columnNum] = data.Tables[i].Rows[rowNum][columnNum].ToString();
}
}
if (rowNum > 1)
{ 

//将一行数据存入list
ExcelDateList.Add(table);
}
}
CreatJsonFile();
CreatCSharp(tableName);
}
}
#endregion
#region Excel转json
void CreatJsonFile()
{ 

if (File.Exists(JsonPath))
{ 

File.Delete(JsonPath);
}
JsonData jsonDatas = new JsonData();
jsonDatas.SetJsonType(JsonType.Array);
for (int i = 0; i < ExcelDateList.Count; i++)
{ 

JsonData jsonData = new JsonData();
for (int j = 0; j < dataName.Count; j++)
{ 

jsonData[dataName[j]] = ExcelDateList[i][j].ToString();
//Debug.Log("第二轮输出:\n");
//Debug.Log(ExcelDateList[i][j].ToString() + "\n");
}
jsonDatas.Add(jsonData);
}
string json = jsonDatas.ToJson();
//防止中文乱码
Regex reg = new Regex(@"(?i)\\[uU]([0-9a-f]{4})");
StreamWriter writer = new StreamWriter(JsonPath, false, Encoding.GetEncoding("UTF-8"));
writer.WriteLine(reg.Replace(json, delegate (Match m) { 
 return ((char)Convert.ToInt32(m.Groups[1].Value, 16)).ToString(); }));
writer.Flush();
writer.Close();
System.Diagnostics.Process.Start("explorer.exe", JsonPath.Replace("/", "\\"));
}
#endregion
#region 创建C#代码
void CreatCSharp(string name)
{ 

if (File.Exists(CSharpPath))
{ 

File.Delete(CSharpPath);
}
//CodeTypeDeclaration 代码类型声明类
CodeTypeDeclaration CSharpClass = new CodeTypeDeclaration(name);
CSharpClass.IsClass = true;
CSharpClass.TypeAttributes = TypeAttributes.Public;
// 设置成员的自定义属性
//CodeAttributeDeclaration代码属性声明
//CodeTypeReference代码类型引用类
//System.Serializable 给脚本打上[System.Serializable()]标签，将 成员变量 在Inspector中显示
//CSharpClass.CustomAttributes.Add(new CodeAttributeDeclaration(new CodeTypeReference("System.Serializable")));
for (int i = 0; i < dataName.Count; i++)
{ 

// 创建字段
//CodeMemberField 代码成员字段类 => (Type, string name)
CodeMemberField member = new CodeMemberField(GetTypeForExcel(dataName[i], dataType[i]), dataName[i]);
member.Attributes = MemberAttributes.Public;
CSharpClass.Members.Add(member);
}
// 获取C#语言的实例
CodeDomProvider provider = CodeDomProvider.CreateProvider("CSharp");
//代码生成器选项类
CodeGeneratorOptions options = new CodeGeneratorOptions();
//设置支撑的样式
options.BracingStyle = "C";
//在成员之间插入空行
options.BlankLinesBetweenMembers = true;
StreamWriter writer = new StreamWriter(CSharpPath, false, Encoding.GetEncoding("UTF-8"));
//生成最终代码
provider.GenerateCodeFromType(CSharpClass, writer, options);
writer.Flush();
writer.Close();
System.Diagnostics.Process.Start("explorer.exe", CSharpPath.Replace("/", "\\"));
}
Type GetTypeForExcel(string Name, string Type) { 

if (Type == "int")
return typeof(Int32);
if (Type == "float")
return typeof(Single);  //float关键字是System.Single的别名
if (Type == "double")
return typeof(Double);
return typeof(String);
}
#endregion
}

Excel示例：

生成的C#脚本：

生成的json文件：
[{“ID”:“10001”,“Name”:“a”,“Explain”:“卡牌a”},{“ID”:“10002”,“Name”:“b”,“Explain”:“卡牌b”},{“ID”:“10003”,“Name”:“c”,“Explain”:“卡牌c”},{“ID”:“10004”,“Name”:“d”,“Explain”:“卡牌d”},{“ID”:“10005”,“Name”:“e”,“Explain”:“卡牌e”},{“ID”:“10006”,“Name”:“f”,“Explain”:“卡牌f”},{“ID”:“10007”,“Name”:“g”,“Explain”:“fas”},{“ID”:“10008”,“Name”:“h”,“Explain”:“gbfdsg”},{“ID”:“10009”,“Name”:“i”,“Explain”:“ewtg”},{“ID”:“10010”,“Name”:“j”,“Explain”:“sgs”},{“ID”:“10011”,“Name”:“k”,“Explain”:“mje”},{“ID”:“10012”,“Name”:“l”,“Explain”:“归属感”},{“ID”:“10013”,“Name”:“m”,“Explain”:“格式”},{“ID”:“10014”,“Name”:“n”,“Explain”:“搞完然后与”}]