脚本：
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 ExcelPath = new List();
string JsonPath;
string CSharpPath;
string JsonName;
List dataType = new List();
List dataName = new List();
List ExcelDateList = new List();
[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("\\", "/"));
}
}
}
///
/// 读取Excel
///
/// excel路径
/// 列
/// 行
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”:“搞完然后与”}]
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/hz/131272.html