在创建完工具类后,接下来就要创建excel的管理类来管理工具类。代码如下:
using System.Collections.Generic;
using System.IO;
using UnityEngine;
using System.Data;
using Excel;
using UnityEditor;
public class ExcelManager
{
#if UNITY_EDITOR
public static string filePath = "Assets/Resources/AssetData/";
public static List<string> excelExporters = new List<string>();
public static List<string> jsonExporters = new List<string>();
public static void CreateScrpits()
{
excelExporters.Clear();
jsonExporters.Clear();
#region 物品配置
ReadExcel("XX配置表.xlsx", "Tyre", "Sheet1");
ReadExcel("YY配置表.xlsx", "PlaneStation", "Sheet1");
#endregion
CreateExporter();
}
public static void SerializeDataToFile()
{
ExcelLoad.LoadExcel(filePath);
}
#region ExcelRead
public static void ReadExcel(string excelName, string titleName, string sheetName)
{
excelExporters.Add(titleName);
string titleLowName = titleName.Substring(0, 1).ToLower() + titleName.Substring(1);
DirectoryInfo pathInfo = new DirectoryInfo(Application.dataPath);
string fullPath = pathInfo.Parent.FullName + @"\配置表\";
string path = @"/配置表/";
FileStream stream = null;
try
{
Debug.Log(fullPath + excelName);
stream = File.Open(fullPath + excelName, FileMode.Open, FileAccess.Read, FileShare.Read);
}
catch (System.Exception ex)
{
string err = string.Format("读取配置表[{0}]失败, 检查是否用Excel打开了这个配置表", excelName);
EditorUtility.DisplayDialog("", err, "失败");
Debug.LogError(err);
return;
}
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
System.Data.DataTable wookSheet = result.Tables[sheetName];
if (wookSheet == null)
{
Debug.LogError("wookSheet is null");
}
int columnCount = wookSheet.Columns.Count;
List<string> keys = new List<string>();
List<string> typeChecks = new List<string>();
List<string> values = new List<string>();
for (int index = 0; index < columnCount; index++)
{
string keyText = wookSheet.Rows[0][index].ToString();
if (!keyText.Equals(string.Empty))
{
keys.Add(keyText.ToLower());
typeChecks.Add(wookSheet.Rows[1][index].ToString());
values.Add(wookSheet.Rows[2][index].ToString());
}
}
#region 创建C#文本
string scriptPath = Application.dataPath + "/Script/protype/" + titleName + "Protypes" + ".cs";
FileStream fs = new FileStream(scriptPath, FileMode.Create, FileAccess.Write);
StreamWriter sr = new StreamWriter(fs);
sr.WriteLine("using System;");
sr.WriteLine("using System.Collections.Generic;");
sr.WriteLine("using UnityEngine;");
sr.WriteLine();
sr.WriteLine(string.Format("public class {0}Protypes : ScriptableObject", titleName));
sr.WriteLine("{");
sr.WriteLine(string.Format("\tpublic List<{0}Protype> {1}s = new List<{0}Protype>();", titleName, titleLowName));
sr.WriteLine("}");
sr.WriteLine();
sr.WriteLine("[Serializable]");
sr.WriteLine(string.Format("public class {0}Protype", titleName));
sr.WriteLine("{");
for (int i = 0; i < keys.Count; i++)
{
//默认为int
string valueType = "int";
string checkName = typeChecks[i].ToLower();
if (checkName.Contains("list"))
{
if (checkName.Contains("int"))
valueType = "int[]";
else if (checkName.Contains("float"))
valueType = "float[]";
else if (checkName.Contains("string"))
valueType = "string[]";
}
else
{
valueType = keys[i];
}
sr.WriteLine(string.Format("\tpublic {0} _{1};", valueType, GetValueType(values[i])));
}
sr.WriteLine("}");
sr.Close();
fs.Close();
#endregion
#region 创建读表脚本
string readPath = Application.dataPath + "/Script/ExcelConfig/" + titleName + "Config" + ".cs";
FileStream readfs = new FileStream(readPath, FileMode.Create, FileAccess.Write);
StreamWriter readsr = new StreamWriter(readfs);
readsr.WriteLine("using System;");
readsr.WriteLine("using System.Collections.Generic;");
readsr.WriteLine("using UnityEngine;");
readsr.WriteLine();
readsr.WriteLine(string.Format("public class {0}Config ", titleName));
readsr.WriteLine("{");
readsr.WriteLine(string.Format("\tprivate Dictionary<int, {0}> _{1}ConfigProtypes=new Dictionary<int, {0}>();", titleName + "Protype", titleName));
readsr.WriteLine(string.Format("\tpublic Dictionary<int, {0}> {1}", titleName + "Protype", titleName + "Protypes"));
readsr.WriteLine("\t{");
readsr.WriteLine("\t\tget");
readsr.WriteLine("\t\t{");
readsr.WriteLine(string.Format("\t\t return _{0}ConfigProtypes;", titleName));
readsr.WriteLine("\t\t}");
readsr.WriteLine("\t}");
readsr.WriteLine();
readsr.WriteLine(string.Format("\tprivate List<{0}> _{1}ConfigProtypesList = null;", titleName + "Protype", titleName));
readsr.WriteLine(string.Format("\tpublic List<{0}> {1}ConfigProtypesList", titleName + "Protype", titleName));
readsr.WriteLine("\t{");
readsr.WriteLine("\t\tget");
readsr.WriteLine("\t\t{");
readsr.WriteLine(string.Format("\t\treturn _{0}ConfigProtypesList;", titleName));
readsr.WriteLine("\t\t}");
readsr.WriteLine("\t}");
readsr.WriteLine();
readsr.WriteLine(string.Format("\tpublic void SetProtypes(List<{0}> {1}ConfigProtypesLists)", titleName + "Protype", titleName));
readsr.WriteLine("\t{");
readsr.WriteLine(string.Format("\t_{0}ConfigProtypesList = {0}ConfigProtypesLists;", titleName ));
readsr.WriteLine(string.Format("\t_{0}ConfigProtypes.Clear();", titleName));
readsr.WriteLine(string.Format("\tforeach (var item in {0}ConfigProtypesLists)", titleName));
readsr.WriteLine("\t{");
readsr.WriteLine(string.Format("\t\t_{0}ConfigProtypes.Add(item._id,item);", titleName));
readsr.WriteLine("\t}");
readsr.WriteLine("\t}");
readsr.WriteLine("}");
readsr.Close();
readfs.Close();
#endregion
#region 创建Excel导出
string editorPath = Application.dataPath + "/Script/Editor/ExporterConfig/" + titleName + "Exporter" + ".cs";
FileStream exporFs = new FileStream(editorPath, FileMode.Create, FileAccess.Write);
StreamWriter exporSw = new StreamWriter(exporFs);
exporSw.WriteLine("using System.Collections.Generic;");
exporSw.WriteLine("using UnityEngine;");
exporSw.WriteLine("using UnityEditor;");
exporSw.WriteLine("using Excel;");
exporSw.WriteLine("using System.IO;");
exporSw.WriteLine("using System.Data;");
exporSw.WriteLine();
exporSw.WriteLine(string.Format("public class {0}Exporter", titleName));
exporSw.WriteLine("{");
//exporSw.WriteLine(string.Format("\tpublic static string ExcelName = @\"{0}\";", path + excelName));
exporSw.WriteLine();
exporSw.WriteLine(string.Format("\tpublic static {0}Protypes ReadExcel()", titleName));
exporSw.WriteLine("\t{");
exporSw.WriteLine("\t\tDirectoryInfo pathInfo = new DirectoryInfo(Application.dataPath);");
exporSw.WriteLine(string.Format("\t\tstring excelName = pathInfo.Parent.FullName + @\"{0}\";", path + excelName));
exporSw.WriteLine("\t\tFileStream stream = File.Open(excelName, FileMode.Open, FileAccess.Read, FileShare.Read);");
exporSw.WriteLine("\t\tIExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);");
exporSw.WriteLine("\t\tDataSet result = excelReader.AsDataSet();");
exporSw.WriteLine();
exporSw.WriteLine(string.Format("\t\tSystem.Data.DataTable wookSheet = result.Tables[\"{0}\"];", sheetName));
exporSw.WriteLine("\t\tint startRow = 5;");
exporSw.WriteLine("\t\tint endRow = wookSheet.Rows.Count;");
exporSw.WriteLine("\t\tint columnCount = wookSheet.Columns.Count;");
exporSw.WriteLine();
exporSw.WriteLine("\t\tList<string> keys = new List<string>();");
exporSw.WriteLine("\t\tfor (int index = 0; index < columnCount; index++)");
exporSw.WriteLine("\t\t{");
exporSw.WriteLine("\t\t\tstring keyText = wookSheet.Rows[2][index].ToString();");
exporSw.WriteLine("\t\t\tif (!keyText.Equals(string.Empty))");
exporSw.WriteLine("\t\t\tkeys.Add(keyText);");
exporSw.WriteLine("\t\t}");
exporSw.WriteLine("\t\tvar headerColumns = ExcelHelper.GetColumnsHeader(wookSheet, keys);");
exporSw.WriteLine(string.Format("\t\t{0}Protypes {1}Protypes = {0}Protypes.CreateInstance<{0}Protypes>();", titleName, titleLowName));
exporSw.WriteLine("\t\tfor (int row = startRow; row < endRow; row++)");
exporSw.WriteLine("\t\t{");
exporSw.WriteLine("\t\t\tstring checkStr = ExcelHelper.GetSheetValue(wookSheet, row, headerColumns[keys[0]]);");
exporSw.WriteLine("\t\t\tif(checkStr.Equals(string.Empty))");
exporSw.WriteLine("\t\t\t\tcontinue;");
exporSw.WriteLine(string.Format("\t\t\t{0}Protype {1} = new {0}Protype();", titleName, titleLowName));
for (int index = 0; index < values.Count; index++)
{
exporSw.WriteLine(string.Format("\t\t\t{0}._{1} = ", titleLowName, GetValueType(values[index])) + GetKeyType(keys[index], typeChecks[index], values[index]));
}
exporSw.WriteLine(string.Format("\t\t\t{0}Protypes.{0}s.Add({0});", titleLowName));
exporSw.WriteLine("\t\t}");
exporSw.WriteLine(string.Format("\t\treturn {0}Protypes;", titleLowName));
exporSw.WriteLine("\t}");
exporSw.WriteLine("}");
exporSw.Close();
exporFs.Close();
#endregion
Debug.Log(excelName + " 生成数据结构成功!");
}
public static void CreateExporter()
{
string excelPath = Application.dataPath + "/Script/Editor/ExcelLoad/" + "ExcelLoad" + ".cs";
FileStream excelFs = new FileStream(excelPath, FileMode.Create, FileAccess.Write);
StreamWriter excelSw = new StreamWriter(excelFs);
excelSw.WriteLine("using System;");
excelSw.WriteLine("using UnityEditor;");
excelSw.WriteLine("using UnityEngine;");
excelSw.WriteLine();
excelSw.WriteLine("public class ExcelLoad");
excelSw.WriteLine("{");
excelSw.WriteLine("\tpublic static void LoadExcel(string filePath)");
excelSw.WriteLine("\t{");
//string[] assets = Directory.GetFiles(Application.dataPath + "/Script/external/protype/");
for (int i = 0; i < excelExporters.Count; i++)
{
if (excelExporters[i].Contains("meta"))
continue;
string assetName = excelExporters[i];
string assetLowName = assetName.Substring(0, 1).ToLower() + assetName.Substring(1);
excelSw.WriteLine("\t\t{0}Protypes {1}Protypes = {0}Protypes.CreateInstance<{0}Protypes>();", assetName, assetLowName);
excelSw.WriteLine("\t\t{0}Protypes = {1}Exporter.ReadExcel();", assetLowName, assetName);
excelSw.WriteLine("\t\tAssetDatabase.CreateAsset({0}Protypes, filePath + \"{1}Protypes\" + \".asset\");", assetLowName, assetName);
excelSw.WriteLine("\t\tDebug.Log(\"ReadExecel:{0}Protypes export data succ!\");\n", assetName);
}
//excelSw.WriteLine("\t\t//import item datas from excel to json");
//excelSw.WriteLine("\t\tExcelAddNewData.ExcelToJson();");
//excelSw.WriteLine("\t\tDebug.Log(\"ReadJSON: ItemModelProtypeConfig and DressModelProtypeConfig export data succ!\");\n");
excelSw.WriteLine("\t}");
excelSw.WriteLine("}");
excelSw.Close();
excelFs.Close();
}
#endregion
public static string GetKeyType(string keyName, string checkName, string columnName)
{
//默认为int
string valueType = "";
//先小写
checkName = checkName.ToLower();
//如果第二列没有List标志
if (checkName.Contains("list"))
{
if (checkName.Contains("int"))
return string.Format("StrParser.ParseDecIntList(ExcelHelper.GetSheetValue(wookSheet, row, headerColumns[\"{0}\"]), 0);", columnName);
else if (checkName.Contains("float"))
return string.Format("StrParser.ParseFloatList(ExcelHelper.GetSheetValue(wookSheet, row, headerColumns[\"{0}\"]), 0f);", columnName);
else if (checkName.Contains("string"))
return string.Format("StrParser.ParseStrList(ExcelHelper.GetSheetValue(wookSheet, row, headerColumns[\"{0}\"]), new string[0]);", columnName);
}
else
{
if (keyName.Equals("int"))
return string.Format("StrParser.ParseDecInt(ExcelHelper.GetSheetValue(wookSheet, row, headerColumns[\"{0}\"]), 0);", columnName);
else if (keyName.Equals("float"))
return string.Format("StrParser.ParseFloat(ExcelHelper.GetSheetValue(wookSheet, row, headerColumns[\"{0}\"]), 0f);", columnName);
else if (keyName.Equals("string"))
return string.Format("StrParser.ParseStr(ExcelHelper.GetSheetValue(wookSheet, row, headerColumns[\"{0}\"]), string.Empty);", columnName);
}
return valueType;
}
public static string GetValueType(string value)
{
//连续两个字母都为大写则全部小写
if (value.Length > 1 && !(char.IsUpper(value.ToCharArray()[0]) && char.IsUpper(value.ToCharArray()[1])))
return value.Substring(0, 1).ToLower() + value.Substring(1);
return value.ToLower();
}
#endif
}
以下说一下需要注意的点吧,首先CreateScrpits()以及SerializeDataToFile()这两个函数是工具类控制执行的,之后需要添加excel表或者删减都在这两个函数中执行。ReadExcel()是读取excel的脚本,excelName变量为需要读取的配置表名字,记得需要加上后缀,titleName是省的的数据的文件名字,sheetName是需要读取的配置表中那几页的名字。读取的配置表的路径需要配置在项目工程下第一级的名为配置表的文件夹中,当然这个路径是可以更改的在ReadExcel()脚本中的fullPath属性即为配置表的配置路径。然后关于自动化生成脚本需要注意的是生成的文件路径一定要注意路径,注意路径,注意路径强调说三遍。在此脚本中有4个自动化生成的脚本,有两个是引用UnityEditor类,有两个脚本不引用UnityEditor,所以在路径设置是有两个必须在Editor文件下,因为在打包的时候Unity不会对Editor下的文件进行打包,如果路径设置错误会导致打包出错。
此外还有两个独立的独立的工具脚本:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using UnityEngine;
public class Range
{
public int row = 0;
public int col = 0;
public int Row
{
get { return this.row; }
set { this.row = value; }
}
public int Column
{
get { return this.col; }
set { this.col = value; }
}
};
public class ExcelHelper
{
public static Range RangeFind(string content, System.Data.DataTable dt)
{
Range Range = new Range();
bool flag = false;
int i = 0;
for (i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Rows[i][j].Equals(content))
{
Range.row = i;
Range.col = j;
flag = true;
break;
}
}
if (flag) break;
}
if (i == dt.Rows.Count) return null;
return Range;
}
public static Dictionary<string, int> GetColumnsHeader(System.Data.DataTable dt, string[] keyWords)
{
Dictionary<string, int> temp = new Dictionary<string, int>();
for (int i = 0; i < keyWords.Length; i++)
{
Range iRange = RangeFind(keyWords[i], dt);
if (iRange != null)
temp.Add(keyWords[i], iRange.Column);
}
return temp;
}
public static Dictionary<string, int> GetColumnsHeader(System.Data.DataTable dt, List<string> keyWords)
{
Dictionary<string, int> temp = new Dictionary<string, int>();
for (int i = 0; i < keyWords.Count; i++)
{
Range iRange = RangeFind(keyWords[i], dt);
if (iRange != null)
temp.Add(keyWords[i], iRange.Column);
}
return temp;
}
public static string GetSheetValue(System.Data.DataTable dt, int row, int column)
{
if (row >= dt.Rows.Count)
{
Debug.LogError("dt.name" + dt.TableName + " row " + row + " 该行不存在");
return "";
}
if (column >= dt.Columns.Count)
{
Debug.LogError("dt.name" + dt.TableName + " row: " + row + " column " + column + " 该列不存在");
return "";
}
return dt.Rows[row][column].ToString();
}
}
以及:
using System;
using UnityEngine;
using System.Collections;
using System.Collections.Generic;
using System.Globalization;
using System.Text;
using System.IO;
public static class StrParser
{
public static readonly char[] splitter = { '|' };
public static bool ParseBool(string str, bool defVal)
{
if (string.IsNullOrEmpty(str))
return defVal;
int v;
if (Int32.TryParse(str, NumberStyles.Integer, provider, out v))
return v != 0;
else
{
Debug.LogError(string.Format("Invalid parameter when parsing Bool value : {0}", str));
return defVal;
}
}
public static int ParseDecInt(string str, int defVal,out bool tf)
{
return (int)Math.Round(ParseFloat(str, defVal, out tf));
}
public static int ParseDecInt(string str, int defVal)
{
// Parse as float as int
//bool tf;
//return ParseDecInt(str, defVal/*, out tf*/);
if (string.IsNullOrEmpty(str))
return defVal;
int result = defVal;
if (!int.TryParse(str, out result))
{
Debug.LogError(string.Format("Invalid parameter when parsing Float value : {0}", str));
}
return result;
}
public static long ParseDecLong(string str, long defVal)
{
if (string.IsNullOrEmpty(str))
return defVal;
long v;
if (Int64.TryParse(str, NumberStyles.Integer, provider, out v))
return v;
else
{
Debug.LogError(string.Format("Invalid parameter when parsing DecLong value : {0}", str));
return defVal;
}
}
public static long ParseHexLong(string str, long defVal)
{
if (string.IsNullOrEmpty(str))
return defVal;
long v;
if (Int64.TryParse(str, NumberStyles.HexNumber, provider, out v))
return v;
else
{
Debug.LogError(string.Format("Invalid parameter when parsing HexLong value : {0}", str));
return defVal;
}
}
public static float ParseFloat(string str, float defVal, out bool tf)
{
tf = false;
if (string.IsNullOrEmpty(str))
return defVal;
float v = 0;
if (tf = Single.TryParse(str, out v))
return (float)v;
else
{
Debug.LogError(string.Format("Invalid parameter when parsing Float value : {0}", str));
return defVal;
}
}
public static float ParseFloat(string str, float defVal)
{
bool tf;
return ParseFloat(str, defVal, out tf);
}
public static float[] ParseFloatList(string str, float defVal)
{
List<float> values = new List<float>();
if (str == null)
return new float[0];
string[] vecs = str.Split(splitter);
for (int i = 0; i < vecs.Length; i++)
values.Add(ParseFloat(vecs[i], defVal));
return values.ToArray();
}
public static double ParseDouble(string str, double defVal)
{
if (string.IsNullOrEmpty(str))
return defVal;
double v = 0;
if (Double.TryParse(str, out v))
return v;
else
{
Debug.LogError(string.Format("Invalid parameter when parsing Double value : {0}", str));
return defVal;
}
}
public static int[] ParseDecIntList(string str, int defVal)
{
List<int> values = new List<int>();
if (str == null)
return new int[0];
string[] vecs = str.Split(splitter);
for (int i = 0; i < vecs.Length; i++)
values.Add(ParseDecInt(vecs[i], defVal));
return values.ToArray();
}
public static string ParseStr(string str, string defValue)
{
return str == null ? defValue : str;
}
public static string[] ParseStrList(string str, string[] defValue)
{
return string.IsNullOrEmpty(str) ? defValue : str.Split(splitter);
}
private static CultureInfo provider = CultureInfo.InvariantCulture;
}
完成之后打开unity就可以使用打包工具了,生成的asset文件在Resources文件下的AssetData中。