需要用到Excel.dll和ICSharpCode.SharpZipLib.dll
表格示例:
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEditor;
using System.IO;
using System.Text;
using Excel;
using System.Reflection;
using System.Reflection.Emit;
using System;
public class ExcelTool : Editor
{
public class ExcelClassData
{
public string className; //类名
public string[] infos; //注释
public string[] types; //类型
public string[] propertyName; //属性名
public List<string[]> datas; //数据
}
//excel文件夹
const string EXCEL_PATH = "ExcelData/Excel";
[MenuItem("ExcelTool/生成数据")]
static void ExcelToData()
{
//所有xlsx表格
string[] files = Directory.GetFiles(Application.dataPath + "/" + EXCEL_PATH, "*.xlsx");
//Debug.Log(files.Length);
//所有数据信息
List<ExcelClassData> allClassDatas = new List<ExcelClassData>();
for (int i = 0; i < files.Length; i++)
{
string file = files[i];
FileStream fileStream = File.Open(file, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
if (!excelReader.IsValid)
{
Debug.Log("读取excel失败" + file);
continue;
}
//构建数据
ExcelClassData exdata = new ExcelClassData();
exdata.className = excelReader.Name;
exdata.datas = new List<string[]>();
int line = 1;
while (excelReader.Read())
{
//一行数据
string[] strLineDatas = new string[excelReader.FieldCount];
for (int j = 0; j < strLineDatas.Length; j++)
{
strLineDatas[j] = excelReader.GetString(j);
}
空行处理
//if (string.IsNullOrEmpty(strLineDatas[0]))
//{
// continue;
//}
//注释行
if (line == 2)
{
exdata.infos = strLineDatas;
}
//类型行
if (line == 3)
{
exdata.types = strLineDatas;
}
//属性名行
if (line == 4)
{
exdata.propertyName = strLineDatas;
}
//数据行
if(line > 4)
{
exdata.datas.Add(strLineDatas);
}
line++;
}
allClassDatas.Add(exdata);
}
//写出数据和脚本操作
Writer(allClassDatas);
Debug.Log("----->excel datas trans finish!");
AssetDatabase.Refresh();
}
static void Writer(List<ExcelClassData> exDataList)
{
#region//---json---
//AssemblyName assemblyName = new AssemblyName("dynamicAssembly");
//AssemblyBuilder assemblyBuilder = AppDomain.CurrentDomain.DefineDynamicAssembly(assemblyName, AssemblyBuilderAccess.Run);
//ModuleBuilder moduleBuilder = assemblyBuilder.DefineDynamicModule(assemblyName.Name);
//for (int i = 0; i < exDataList.Count; i++)
//{
// ExcelClassData exData = exDataList[i];
// //定义类型
// TypeBuilder typeBuilder = moduleBuilder.DefineType(exData.className, TypeAttributes.Public);
// //定义属性
// for (int j = 0; j < exData.types.Length; j++)
// {
// typeBuilder.DefineField(exData.propertyName[j], GetType(exData.types[j]), FieldAttributes.Public);
// }
// //t
// Type t = typeBuilder.CreateType();
// List<object> allObjList = new List<object>();
// for (int j = 0; j < exData.datas.Count; j++)
// {
// //一行数据
// string[] strDatas = exData.datas[j];
// //反射实例
// object obj = Activator.CreateInstance(t);
// for (int k = 0; k < exData.types.Length; k++)
// {
// //设置属性值
// FieldInfo fieldInfo = t.GetField(exData.propertyName[k]);
// object value = GetValue(exData.types[k], strDatas[k]);
// fieldInfo.SetValue(obj, value);
// }
// allObjList.Add(obj);
// }
// string jsonData = Newtonsoft.Json.JsonConvert.SerializeObject(allObjList, Newtonsoft.Json.Formatting.Indented);
// string dataFloder = Application.streamingAssetsPath + "/Datas";
// if (!System.IO.Directory.Exists(dataFloder))
// {
// Directory.CreateDirectory(dataFloder);
// }
// File.WriteAllText(dataFloder + "/" + exData.className + ".json", jsonData);
//}
#endregion
#region//---bytes---
for (int i = 0; i < exDataList.Count; i++)
{
ExcelClassData exData = exDataList[i];
List<byte> byteList = new List<byte>();
int dataCount = exData.datas.Count;
byteList.AddRange(PickData.WriteInt(dataCount));
int tempIndex = 0;
while (tempIndex < dataCount)
{
string[] data = exData.datas[tempIndex];
for (int j = 0; j < exData.types.Length; j++)
{
byte[] tbytes = GetBytes(exData.types[j], data[j]);
byteList.AddRange(tbytes);
}
tempIndex++;
}
//bytes数据文件生成
string savePath = Application.streamingAssetsPath + "/Datas/" + exData.className + ".data";
File.WriteAllBytes(savePath, byteList.ToArray());
//脚本生成
string saveCodePath = Application.dataPath + "/Scripts/Excels";
if (!Directory.Exists(saveCodePath))
{
Directory.CreateDirectory(saveCodePath);
}
string strCode = CreateCode(exData.className, exData.types, exData.propertyName, exData.infos);
File.WriteAllText(saveCodePath + "/" + exData.className + ".cs", strCode);
}
#endregion
}
/// <summary>
/// 获取类型
/// </summary>
static Type GetType(string typeName)
{
switch (typeName)
{
case "int":
return typeof(int);
case "float":
return typeof(float);
case "string":
return typeof(string);
case "int[]":
return typeof(int[]);
case "float[]":
return typeof(float[]);
case "string[]":
return typeof(string[]);
//default:
// return null;
}
return null;
}
/// <summary>
/// 获取数据Obj
/// </summary>
static object GetValue(string typeName, string data)
{
string[] ss = data.Split('|');
switch (typeName)
{
case "int":
return int.Parse(data);
case "float":
return float.Parse(data);
case "string":
return data;
case "int[]":
int[] intArray = new int[ss.Length];
for (int i = 0; i < ss.Length; i++)
{
intArray[i] = int.Parse(ss[i]);
}
return intArray;
case "float[]":
float[] floatArray = new float[ss.Length];
for (int i = 0; i < ss.Length; i++)
{
floatArray[i] = float.Parse(ss[i]);
}
return floatArray;
case "string[]":
return ss;
//default:
// return null;
}
return null;
}
/// <summary>
/// 获取数据Obj的Bytes
/// </summary>
static byte[] GetBytes(string typeName, string data)
{
List<byte> bytes = new List<byte>();
object obj = GetValue(typeName, data);
switch (typeName)
{
case "int":
bytes.AddRange(PickData.WriteInt((int)obj));
break;
case "float":
bytes.AddRange(PickData.WriteFloat((float)obj));
break;
case "string":
bytes.AddRange(PickData.WriteString((string)obj));
break;
case "int[]":
bytes.AddRange(PickData.WriteIntArray((int[])obj));
break;
case "float[]":
bytes.AddRange(PickData.WriteFloatArray((float[])obj));
break;
case "string[]":
bytes.AddRange(PickData.WriteStringArray((string[])obj));
break;
default:
break;
}
return bytes.ToArray();
}
/// <summary>
/// 生成代码
/// </summary>
static string CreateCode(string className, string[] types, string[] names, string[] texts)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("using UnityEngine;\n");
stringBuilder.Append("using System;\n");
stringBuilder.Append("\n");
//stringBuilder.Append("[Serializable]\n");
stringBuilder.Append("public class " + className + ":PickData\n");
stringBuilder.Append("{\n");
//属性定义
for (int i = 0; i < types.Length; i++)
{
//注释
stringBuilder.Append(StrNotes(texts[i], 1));
//定义
stringBuilder.Append("\tpublic " + types[i] + " " + names[i] + ";\n");
}
stringBuilder.Append("\n");
#region//---bytes解析---
//ReadData(bytes)
stringBuilder.Append(StrNotes("解析数据", 1));
stringBuilder.Append("\tpublic override void ReadData(byte[] datas, ref int index)\n");
stringBuilder.Append("\t{\n");
for (int i = 0; i < types.Length; i++)
{
string readInfo = "";
switch (types[i])
{
case "int":
readInfo = "PickData.ReadInt";
break;
case "float":
readInfo = "PickData.ReadFloat";
break;
case "string":
readInfo = "PickData.ReadString";
break;
case "int[]":
readInfo = "PickData.ReadIntArray";
break;
case "float[]":
readInfo = "PickData.ReadFloatArray";
break;
case "string[]":
readInfo = "PickData.ReadStringArray";
break;
default:
break;
}
stringBuilder.Append("\t\t" + names[i] + " = " + readInfo + "(datas, ref index);\n");
}
stringBuilder.Append("\t}\n");
#endregion
stringBuilder.Append("}\n");
return stringBuilder.ToString();
}
/// <summary>
/// 注释
/// </summary>
static string StrNotes(string tip, int t = 0)
{
StringBuilder stringBuilder = new StringBuilder();
string st = "";
for (int i = 0; i < t; i++)
{
st += "\t";
}
stringBuilder.Append(st + "/// <summary>\n");
stringBuilder.Append(st + "/// " + tip + "\n");
stringBuilder.Append(st + "/// <summary>\n");
return stringBuilder.ToString();
}
}
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System;
using System.IO;
using System.Text;
public class PickData
{
public virtual void ReadData(byte[] datas, ref int index)
{ }
public static T[] GetObjs<T>(byte[] datas) where T : PickData, new()
{
int index = 0;
int count = PickData.ReadInt(datas, ref index);
T[] results = new T[count];
for (int i = 0; i < count; i++)
{
results[i] = new T();
results[i].ReadData(datas, ref index);
}
return results;
}
public static byte[] WriteInt(int value)
{
byte[] data = System.BitConverter.GetBytes(value);
return data;
}
public static byte[] WriteFloat(float value)
{
byte[] data = System.BitConverter.GetBytes(value);
return data;
}
public static byte[] WriteString(string value)
{
byte[] data = Encoding.UTF8.GetBytes(value);
int length = data.Length;
List<byte> byteList = new List<byte>();
byteList.AddRange(WriteInt(length));
byteList.AddRange(data);
return byteList.ToArray();
}
public static byte[] WriteIntArray(int[] values)
{
List<byte> byteList = new List<byte>();
int count = values.Length;
byteList.AddRange(WriteInt(count));
for (int i = 0; i < count; i++)
{
byteList.AddRange(WriteInt(values[i]));
}
return byteList.ToArray();
}
public static byte[] WriteFloatArray(float[] values)
{
List<byte> byteList = new List<byte>();
int count = values.Length;
byteList.AddRange(WriteInt(count));
for (int i = 0; i < count; i++)
{
byteList.AddRange(WriteFloat(values[i]));
}
return byteList.ToArray();
}
public static byte[] WriteStringArray(string[] values)
{
List<byte> byteList = new List<byte>();
int count = values.Length;
byteList.AddRange(WriteInt(count));
for (int i = 0; i < count; i++)
{
byteList.AddRange(WriteString(values[i]));
}
return byteList.ToArray();
}
public static int ReadInt(byte[] data, ref int index)
{
byte[] read = new byte[sizeof(int)];
Array.Copy(data, index, read, 0, read.Length);
index += read.Length;
return BitConverter.ToInt32(read);
}
public static float ReadFloat(byte[] data, ref int index)
{
byte[] read = new byte[sizeof(float)];
Array.Copy(data, index, read, 0, read.Length);
index += read.Length;
return BitConverter.ToSingle(read);
}
public static string ReadString(byte[] data, ref int index)
{
int length = ReadInt(data, ref index);
if (length <= 0)
return "";
byte[] read = new byte[length];
Array.Copy(data, index, read, 0, length);
index += length;
return Encoding.UTF8.GetString(read);
}
public static int[] ReadIntArray(byte[] data, ref int index)
{
int count = ReadInt(data, ref index);
int[] array = new int[count];
for (int i = 0; i < count; i++)
{
array[i] = ReadInt(data, ref index);
}
return array;
}
public static float[] ReadFloatArray(byte[] data, ref int index)
{
int count = ReadInt(data, ref index);
float[] array = new float[count];
for (int i = 0; i < count; i++)
{
array[i] = ReadFloat(data, ref index);
}
return array;
}
public static string[] ReadStringArray(byte[] data, ref int index)
{
int count = ReadInt(data, ref index);
string[] array = new string[count];
for (int i = 0; i < count; i++)
{
array[i] = ReadString(data, ref index);
}
return array;
}
}