private FieldValue GetValues(ExcelField field, List <XZC> value, List <ExcelField> fields, string tableName)
{
var item = _results.FirstOrDefault(e => e.Index == field.Index);
if (item != null)
{
return(item);
}
var a = 0;
var b = .0;
var IntSum = 0;
var DoubleSum = .0;
foreach (var index in field.Indexs)
{
var f = fields.FirstOrDefault(e => e.Index == index);
if (f != null)
{
var val = f.Indexs != null?GetValues(f, value, fields, tableName) : GetValue(f, value, tableName);
if (val != null)
{
switch (field.Type)
{
case ExcelType.Double:
DoubleSum += Math.Round(double.TryParse(val.Val.ToString(), out b) ? b : .0, 4);
break;
case ExcelType.Int:
IntSum += int.TryParse(val.Val.ToString(), out a) ? a : 0;
break;
}
}
}
}
var ee = new FieldValue
{
Index = field.Index,
Type = field.Type,
Title = field.Title,
Val = field.Type == ExcelType.Double ? DoubleSum : IntSum
};
_results.Add(ee);
return(ee);
}
private FieldValue GetValue(ExcelField field, List <XZC> value, string tableName)
{
var item = _results.FirstOrDefault(e => e.Index == field.Index);
if (item != null)
{
return(item);
}
var qy = string.Join(" OR ", value.Select(e => string.Format("{0}.XZCDM = '{1}'", string.IsNullOrEmpty(field.FieldTableName) ? tableName : field.FieldTableName, e.XZCDM)).ToArray());
var val = GainCommon(field, qy, tableName);
if (val != null)
{
_results.Add(val);
}
return(val);
}
private FieldValue GetValues(ExcelField field, List <XZC> value)
{
var a = 0;
var b = .0;
var IntSum = 0;
var DoubleSum = .0;
foreach (var index in field.Indexs)
{
var f = Fields.FirstOrDefault(e => e.Index == index);
if (f != null)
{
var val = GetValue(f, value);
if (val != null)
{
switch (field.Type)
{
case ExcelType.Double:
DoubleSum += Math.Round(double.TryParse(val.Val.ToString(), out b) ? b : .0, 4);
break;
case ExcelType.Int:
IntSum += int.TryParse(val.Val.ToString(), out a) ? a : 0;
break;
}
}
}
}
return(new FieldValue
{
Index = field.Index,
Type = field.Type,
Title = field.Title,
Val = field.Type == ExcelType.Double ? DoubleSum : IntSum
});
}
private FieldValue GainCommon(ExcelField field, string dmWhere, string tableName)
{
var a = 0;
var b = .0;
var val = string.Empty;
var sb = new StringBuilder(string.Format("Select {0} from ", field.SQL));
if (string.IsNullOrEmpty(field.View))
{
if (string.IsNullOrEmpty(field.FieldTableName))
{
sb.Append(tableName);
}
else
{
sb.Append(field.FieldTableName);
}
}
else
{
sb.AppendFormat("({0})", field.View);
}
sb.AppendFormat(" Where ( {0} )", dmWhere);
if (!string.IsNullOrEmpty(field.WhereClause))
{
sb.AppendFormat(" AND {0}", field.WhereClause);
}
Console.WriteLine("{0}:{1}", tableName, sb.ToString());
var obj = ADOSQLHelper.ExecuteScalar(Connection, sb.ToString());
if (obj == null)
{
return(null);
}
if (field.Type == ExcelType.Double)
{
double.TryParse(obj.ToString(), out b);
switch (field.Unit)
{
case "亩":
b = b / 15;
break;
case "平方米":
b = b / 10000;
break;
default:
break;
}
val = Math.Round(b, 4).ToString();
}
else
{
int.TryParse(obj.ToString(), out a);
val = a.ToString();
}
var result = new FieldValue
{
Index = field.Index,
Type = field.Type,
Title = field.Title,
Val = val
};
return(result);
}
private FieldValue GetValue(ExcelField field, string tableName)
{
return(GainCommon(field, tableName));
}
/// <summary>
/// 只支持.xlsx格式，不支持03版本excel(.xls)
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static List <ExcelTable> Read(string filePath)
{
Debug.Log("read file:" + filePath);
List <ExcelTable> excelTables = new List <ExcelTable>();
string fileName = Path.GetFileName(filePath);
FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs);
DataSet result = excelReader.AsDataSet();
//获得一个xlsx文件中所有的tables
DataTableCollection tables = result.Tables;
fs.Close();
foreach (DataTable table in tables)
{
ExcelTable et = new ExcelTable();
et.tableName = table.TableName;
////中文表不读，用于整体性的说明、注释作用
////要导出的表需要以t_开头
if (et.tableName.ContainChinese() || !et.tableName.StartsWith("t_"))
{
continue;
}
Debug.Log("begin read------>fileName:" + fileName + ",tableName:" + et.tableName);
et.rowCount = table.Rows.Count;
if (et.rowCount < 6)
{
Debug.LogWarnning("fileName:" + fileName + ", tableName:" + et.tableName + ", line count less than 6, we will not handle with it");
continue;
}
for (int i = 0; i < table.Columns.Count; i++)
{
//init ExcelField
string name = table.Rows[0][i].ToString();
if (name.ContainChinese())//中文字段用于注释，不处理
{
continue;
}
string typeDes = table.Rows[1][i].ToString();
string tag = table.Rows[3][i].ToString();
if (i == 0 && tag == "KVT")//check KVT format whether correct
{
et.isKVT = true;
if (table.Rows[0][0].ToString() != "Key" ||
table.Rows[0][1].ToString() != "Value" ||
table.Rows[0][2].ToString() != "KeyDes")
{
Debug.ThrowException("table:" + et.tableName + "is KVT,but field name is not correct!");
}
if (table.Rows[1][0].ToString() != "string")
{
Debug.ThrowException("table:" + et.tableName + "is KVT,but key's type is not string");
}
}
string defaultValue = table.Rows[2][i].ToString();
string fieldDes = table.Rows[4][i].ToString();
string fieldDesMore = table.Rows[5][i].ToString();
ExcelField field = new ExcelField(et.tableName, name, typeDes, defaultValue, tag, fieldDes, fieldDesMore, i == 0 ? true : false);
//read Field datas
for (int j = 6; j < et.rowCount; j++)
{
string tmpValue = table.Rows[j][i].ToString().Trim();
field.AddData(tmpValue == "" ? defaultValue : tmpValue);
}
et.AddExcelField(field);
}
#region old
//////for (int i = 0; i < et.rowCount; i++)
//////{
////// if (i == 0)//获得key
////// {
////// for (int j = 0; j < et.columnCount; j++)
////// {
////// var key = table.Rows[0][j].ToString();
////// if (j == 0)//主键
////// {
////// et.primaryKeyName = key;
////// et.primaryKeyType = table.Rows[1][j].ToString();
////// if (table.Rows[2][j].ToString().Contains("KVT"))
////// {
////// et.isKVPairTable = true;
////// et.valueKeyType = table.Rows[1][1].ToString();
////// if (table.Rows[0][1].ToString() != "Value" || table.Rows[0][2].ToString() != "KeyDes")
////// {
////// throw new Exception(et.tableName + "表有问题，KVT表，要求第二个字段名为 Value，第三个字段名为 KeyDes");
////// }
////// }
////// else
////// {
////// et.isKVPairTable = false;
////// }
////// }
////// Console.WriteLine("@@初始化map, key:" + key);
////// et.tableContent[key] = new List<string>();
////// if (et.keys.Contains(key))
////// {
////// throw new Exception("fatal error, table:" + et.tableName + ",有重复的字段:" + key);
////// }
////// else
////// {
////// if (!key.ContainChinese() && key != "KeyDes")//字段非中文才加入,且 KeyDes字段不用加入
////// {
////// et.keys.Add(key);
////// }
////// }
////// }
////// Console.WriteLine("@@初始化map key完毕");
////// }
////// else
////// {
////// for (int k = 0; k < et.columnCount; k++)
////// {
////// string key = table.Rows[0][k].ToString();
////// string property = table.Rows[i][k].ToString();
////// Console.WriteLine("table:" + et.tableName + " , key: " + key + " , property：" + property);
////// //主键的数据要保持唯一性
////// if (k == 0 && i > 4 && et.tableContent[key].Contains(property))
////// {
////// throw new Exception("fatal error, table:" + et.tableName + "的主键" + key + "，property:" + property);
////// }
////// else
////// {
////// if (property == "")
////// {
////// property = "囧";//对于数据表中，用户没有填写的空格，默认赋值为 囧 ，读取的时候需要特殊处理
////// }
////// et.tableContent[key].Add(property);
////// }
////// }
////// }
//////}
#endregion
excelTables.Add(et);
}
return(excelTables);
}
public static void WriteLuaCode(string outputDir, List <ExcelTable> tables)
{
if (!Directory.Exists(outputDir))
{
Directory.CreateDirectory(outputDir);
}
string outputFile = outputDir + @"\ConfigTable.lua";
string luaContent = "---@class ConfigTable\r\n";
luaContent += "local ConfigTable={}\r\n";
for (int i = 0; i < tables.Count; i++)
{
var table = tables[i];
ExcelField primaryField = table.GetPrimaryField();
string luaContentSub = "";
if (table.isKVT)
{
luaContentSub += "\r\n";
luaContentSub += "---@class " + table.tableName + ":ConfigTable\r\n";
luaContentSub += "ConfigTable." + table.tableName + "={\r\n";
for (int j = 6; j < table.rowCount; j++)
{
var kStr = table.fields[0].datas[j - 6];
var vStr = table.fields[1].datas[j - 6];
//var kStr = table.tableContent[table.primaryKeyName][j];
//var vStr = table.tableContent["Value"][j];
var commentStr = table.fields[2].datas[j - 6];
if (j == 6)
{
luaContentSub = "---@field public " + kStr + " @" + commentStr + luaContentSub;
}
else
{
luaContentSub = "---@field public " + kStr + " @" + commentStr + "\r\n" + luaContentSub;
}
luaContentSub += "\t" + kStr + "=\"" + vStr + "\",\r\n";
}
luaContentSub += "}\r\n";
}
else
{
luaContentSub += "\r\n";
luaContentSub += "---@class " + table.tableName + ":ConfigTable\r\n";
luaContentSub += "ConfigTable." + table.tableName + "={\r\n";
for (int j = 6; j < table.rowCount; j++)
{
var typeDes = table.fields[0].typeDes;
if (typeDes == "int")
{
luaContentSub += "\t[" + table.fields[0].datas[j - 6] + "]={";
}
else if (typeDes == "string")
{
luaContentSub += "\t[\"" + table.fields[0].datas[j - 6] + "\"]={";
}
else
{
Debug.LogError("error, primary type only support int and string, cur is:" + typeDes);
}
for (int k = 0; k < table.fields.Count; k++)
{
var field = table.fields[k];
var keyName = field.name;
var value = field.datas[j - 6];
if (field.typeDes == "string")
{
luaContentSub += keyName + "=\"" + value + "\",";
}
else if (field.typeDes == "Vector3" || field.typeDes == "float[]" || field.typeDes == "int[]" || field.typeDes == "string[]")
{
luaContentSub += keyName + "=";
//+ value + ",";
luaContentSub += "{";
if (value != "")
{
var vs = value.Split('|');
for (int p = 0; p < vs.Length; p++)
{
if (field.typeDes == "float[]" || field.typeDes == "Vector3")
{
luaContentSub += float.Parse(vs[p]);
}
else if (field.typeDes == "int[]")
{
luaContentSub += int.Parse(vs[p]);
}
else if (field.typeDes == "string[]")
{
luaContentSub += "\"" + vs[p] + "\"";
}
if (p < vs.Length - 1)
{
luaContentSub += ",";
}
}
}
luaContentSub.TrimEnd(',');
luaContentSub += "},";
}
else if (field.typeDes != "")
{
luaContentSub += keyName + "= " + value + " ,";
}
}
luaContentSub += "},\r\n";
}
luaContentSub += "}\r\n";
}
luaContentSub += "local mt_" + table.tableName + "={}\r\n";
luaContentSub += "mt_" + table.tableName + ".__index=function(t,k)\r\n";
luaContentSub += "\tLogE(\"can not find item with key:\"..k..\" in " + table.tableName + "\")\r\n";
luaContentSub += "end\r\n";
luaContentSub += "setmetatable(ConfigTable." + table.tableName + ",mt_" + table.tableName + ")\r\n";
luaContentSub += "\r\n";
luaContent += luaContentSub;
}
luaContent += "return ConfigTable";
File.WriteAllText(outputFile, luaContent.TrimEnd(), new UTF8Encoding(false));
Console.WriteLine("ConfigTable.lua输出完毕");
}
public static void WriteCSCode(string outputDir, ExcelTable et)
{
if (!Directory.Exists(outputDir))
{
Directory.CreateDirectory(outputDir);
}
ExcelField primaryField = et.GetPrimaryField();
#region step1:output Setting.cs base class
string setting = "namespace ZGame.ZTable{\r\n";
setting += "\tpublic class Setting{\r\n";
setting += "\t}\r\n";
setting += "}";
File.WriteAllText(outputDir + @"\" + "Setting.cs", setting.TrimEnd(), Encoding.UTF8);
#endregion
#region step2:output table class
string content = "";;
content += "using System;\r\n";
content += "using UnityEngine;\r\n";
content += "using System.Text;\r\n";
content += "\r\n";
content += "namespace ZGame.ZTable{\r\n";
if (et.isKVT)//if KVT,we should output addition class,which can direct get value by key
{
content += "public class " + et.tableName + "KVP{\r\n";
for (int i = 0; i < primaryField.datas.Count; i++)
{
string keyName = primaryField.datas[i].ToString();
content += "\t/// <summary>\r\n";
content += "\t/// " + et.fields[2].datas[i].ToString() + "\r\n";//KeyDes
content += "\t/// </summary>\r\n";
content += "\tpublic static " + et.fields[1].typeDes + " " + keyName + "{\r\n";
content += "\t\tget{\r\n";
content += "\t\t\treturn " + et.tableName + "Reader.Instance.GetEntityByPrimaryKey(\"" + keyName + "\").Value;\r\n";
content += "\t\t}\r\n"; //get
content += "\t}\r\n"; //attribute
}
content += "}\r\n"; //class
}
content += "public class " + et.tableName + ":Setting{\r\n";
for (int l = 0; l < et.fields.Count; l++)
{
if (et.fields[l].name.ContainChinese() ||
et.fields[l].name == "KeyDes")
{
continue;//key为汉字的时候跳过（汉字作为字段的 用于注释说明）
}
content += "\t /// <summary>\r\n";
content += "\t /// " + et.fields[l].fieldDes + "\r\n";
if (et.fields[l].fieldDesMore != "")
{
content += "\t /// " + et.fields[l].fieldDesMore + "\r\n";
}
content += "\t /// </summary>\r\n";
content += "\t public " + et.fields[l].typeDes + " " + et.fields[l].name + ";\r\n";
content += "\r\n";
}
content += "\t public static string FileName = " + "\"" + et.tableName + "\";\r\n";
content += "}\r\n";
content += "}";
File.WriteAllText(outputDir + @"\" + et.tableName + ".cs", content.TrimEnd(), Encoding.UTF8);
#endregion
#region step3:output table reader class
content = "";
content += "using UnityEngine;\r\n";
content += "using System;\r\n";
content += "using System.Text;\r\n";
content += "using System.Collections.Generic;\r\n";
content += "\r\n";
content += "namespace ZGame.ZTable{\r\n";
content += "\tpublic class " + et.tableName + "Reader{\r\n";
content += "\t\tprivate " + et.tableName + "[] entities;\r\n";
content += "\t\tprivate Dictionary<" + primaryField.typeDes + ",int> keyIndexMap = new Dictionary<" + primaryField.typeDes + ",int>();\r\n";
content += "\r\n\t\tprivate int count;\r\n";
content += "\t\t public int Count{\r\n";
content += "\t\t\tget{ return this.count;}\r\n";
content += "\t\t}\r\n\r\n";
content += "\t\tstatic " + et.tableName + "Reader instance=null;\r\n";
content += "\t\tpublic static " + et.tableName + "Reader Instance{\r\n";
content += "\t\t\tget{\r\n";
content += "\t\t\t\tif(instance==null){\r\n";
content += "\t\t\t\t\tinstance=new " + et.tableName + "Reader();\r\n";
content += "\t\t\t\t\tinstance.Load();\r\n";
content += "\t\t\t\t}\r\n";
content += "\t\t\t\treturn instance;\r\n";
content += "\t\t\t}\r\n";
content += "\t\t}\r\n\r\n";
content += "\t\tvoid Load(){\r\n";
content += "\t\t\tAction<string> onTableLoad=(text)=>{\r\n";
content += "\t\t\t\t";
content += @"string[] lines=text.Split(new string[]{""\r\n""},System.StringSplitOptions.RemoveEmptyEntries);";
content += "\r\n";
content += "\t\t\t\tint count=lines.Length;\r\n";
content += "\t\t\t\tif(count<0){\r\n";
content += "\t\t\t\t\treturn;\r\n";
content += "\t\t\t\t}\r\n";
content += "\t\t\t\tthis.count = count;\r\n";
content += "\t\t\t\tentities=new " + et.tableName + "[count];\r\n";
content += "\t\t\t\tfor(int i=0;i<count;i++){\r\n";
content += "\t\t\t\t\tstring line=lines[i];\r\n";
content += "\t\t\t\t\tif(string.IsNullOrEmpty(line)){\r\n";
content += "\t\t\t\t\t\t";
content += @"Debug.LogError(""data error, line ""+i+"" is null"");";
content += "\r\n\t\t\t\t\t}\r\n";
content += "\t\t\t\t\tstring[] vals=line.Split('\\t');\r\n";
content += "\t\t\t\t\tentities[i]=new " + et.tableName + "();\r\n";
for (int i = 0; i < et.fields.Count; i++)
{
string filedName = et.fields[i].name;
string typeStr = et.fields[i].typeDes;
if (typeStr == "int" || typeStr == "float")
{
content += "\t\t\t\t\tentities[i]." + filedName + "=" + typeStr + ".Parse(vals[" + i + "].Trim());\r\n";
}
else if (typeStr == "object")
{
content += "\t\t\t\t\tentities[i]." + filedName + "=(" + typeStr + ")(vals[" + i + "].Trim());\r\n";
}
else if (typeStr == "bool")
{
content += "\t\t\t\t\tentities[i]." + filedName + "=" + typeStr + ".Parse(vals[" + i + "].Trim());\r\n";
}
else if (typeStr == "string")
{
content += "\t\t\t\t\tentities[i]." + filedName + "=" + "vals[" + i + "];\r\n";
}
else if (typeStr == "string[]")
{
content += "\t\t\t\t\tentities[i]." + filedName + "=" + "vals[" + i + "].Split(\'|\');\r\n";
}
else if (typeStr == "int[]")
{
content += "\t\t\t\t\tentities[i]." + filedName + "=" + "vals[" + i + "].Split(\'|\').ToIntArray();\r\n";
}
else if (typeStr == "float[]")
{
content += "\t\t\t\t\tentities[i]." + filedName + "=" + "vals[" + i + "].Split(\'|\').ToFloatArray();\r\n";
}
else if (typeStr == "Vector3")
{
content += "\t\t\t\t\tentities[i]." + filedName + "=" + "new Vector3("
+ "float.Parse(vals[" + i + "].Trim().Split(\'|\')[0]),"
+ "float.Parse(vals[" + i + "].Trim().Split(\'|\')[1]),"
+ "float.Parse(vals[" + i + "].Trim().Split(\'|\')[2])"
+ ");\r\n";
}
//TODO:support more type
}
content += "\t\t\t\t\tkeyIndexMap[entities[i]." + primaryField.name + "]=i;\r\n";
content += "\t\t\t\t}\r\n"; //for
content += "\t\t\t};\r\n\r\n"; //Action
content += "\t\t\tstring fileName=" + et.tableName + ".FileName;\r\n";
content += "\t\t\tFileMgr.ReadFile(fileName,onTableLoad);\r\n";
content += "\t\t}\r\n\r\n";//Load()
content += "\t\t/// <summary>\r\n";
content += "\t\t/// get datas of a row by Index\r\n";
content += "\t\t/// index starts form 0,which marching the line 7 of excel table\r\n";
content += "\t\t/// </summary>\r\n";
content += "\t\tpublic " + et.tableName + " GetEntityByRowIndex(int index){\r\n";
content += "\t\t\tif(index<0||index>count){\r\n";
content += "\t\t\t\t";
content += @"Debug.LogError(""index:""+index+"" is not valid"");";
content += "\r\n";
content += "\t\t\t\treturn null;\r\n";
content += "\t\t\t}\r\n\t\t\telse{\r\n";
content += "\t\t\t\treturn entities[index];\r\n";
content += "\t\t\t}\r\n";
content += "\t\t}\r\n";//GetEntityByRowIndex
content += "\t\t/// <summary>\r\n";
content += "\t\t/// get datas of a row by primary key\r\n";
content += "\t\t/// </summary>\r\n";
content += "\t\tpublic " + et.tableName + " GetEntityByPrimaryKey(" + primaryField.typeDes + " key){\r\n";
content += "\t\t\tint index;\r\n";
content += "\t\t\tif(keyIndexMap.TryGetValue(key,out index)){\r\n";
content += "\t\t\t\treturn entities[index];\r\n";
content += "\t\t\t}\r\n\t\t\telse{\r\n";
content += "\t\t\t\t";
content += @"Debug.LogError(""no entity with key:""+key);";
content += "\r\n";
content += "\t\t\t\treturn default(" + et.tableName + ");\r\n";
content += "\t\t\t}\r\n";
content += "\t\t}\r\n";//GetEntityByPrimaryKey
content += "\t\t/// <summary>\r\n";
content += "\t\t/// get all row datas\r\n";
content += "\t\t/// </summary>\r\n";
content += "\t\tpublic " + et.tableName + "[]" + " AllItems(){\r\n";
content += "\t\t\treturn this.entities;\r\n";
content += "\t\t}\r\n";//AllItems
content += "\t}\r\n"; //class
content += "}"; //namespace
File.WriteAllText(outputDir + @"\" + et.tableName + "Reader.cs", content.TrimEnd(), Encoding.UTF8);
#endregion
Debug.Log("write file " + et.tableName + ".cs");
}
//public List<ExcelField> Fields { get { return _fields == null ? _fields = GetFields() : _fields; } }
private List <ExcelField> GetFields()
{
var list = new List <ExcelField>();
var nodeP = XmlManager.GetSingle(string.Format("/Tables/Excel[@Name='{0}']", CollectTable.Name), XmlEnum.Field);
if (nodeP != null)
{
var nodes = nodeP.SelectNodes("Field");
if (nodes != null && nodes.Count > 0)
{
for (var i = 0; i < nodes.Count; i++)
{
var node = nodes[i];
var val = new ExcelField
{
TableName = CollectTable.Name,
Name = node.Attributes["Name"].Value,
Title = node.Attributes["Title"].Value,
Index = int.Parse(node.Attributes["Index"].Value),
Type = node.Attributes["Type"].Value.ToLower() == "int" ? ExcelType.Int : ExcelType.Double,
Compute = node.Attributes["Compute"].Value.ToLower() == "sum" ? Compute.Sum : Compute.Count,
};
if (node.Attributes["Unit"] != null)
{
val.Unit = node.Attributes["Unit"].Value.Trim();
}
if (node.Attributes["View"] != null)
{
val.View = node.Attributes["View"].Value;
}
if (node.Attributes["WhereClause"] != null)
{
val.WhereClause = node.Attributes["WhereClause"].Value;
}
if (node.Attributes["TableName"] != null)
{
val.FieldTableName = node.Attributes["TableName"].Value;
}
if (node.Attributes["Indexs"] != null)
{
var indexs = node.Attributes["Indexs"].Value;
if (!string.IsNullOrEmpty(indexs))
{
var temps = indexs.Split(',');
var res = new int[temps.Length];
for (var j = 0; j < temps.Length; j++)
{
var a = 0;
res[j] = int.TryParse(temps[j], out a) ? a : 0;
}
val.Indexs = res;
}
}
list.Add(val);
}
}
}
return(list);
}
