/// <summary>
/// 去除以指定字符开头的临时文件
/// </summary>
/// <param name="allFilePaths">全部文件</param>
/// <param name="isMoreLangue">是否为多语言</param>
/// <param name="TempFileFileNameStartString">临时文件前缀</param>
/// <returns></returns>
private static Dictionary <string, List <string> > RemoveTempFile(Dictionary <string, List <string> > allFilePaths, string TempFileFileNameStartString = ExcelTableSetting.ExcelTempFileFileNameStartString)
{
Dictionary <string, List <string> > allFilePathsTemp = new Dictionary <string, List <string> >();
if (AppLanguage.AppConfigIDArr == null)//只有基本语言
{
foreach (KeyValuePair <string, List <string> > kvp in allFilePaths)
{
if (!kvp.Key.StartsWith(TempFileFileNameStartString))
{
allFilePathsTemp.Add(ExcelMethods.GetTableName(kvp.Key), kvp.Value);
}
}
}
else//多语言
{
Dictionary <string, List <string> > allFilePathsTemp2 = new Dictionary <string, List <string> >();
foreach (KeyValuePair <string, List <string> > kvp in allFilePaths)
{
//指定语言直接保存
if (TheLanguage != "" && (ExcelMethods.GetTableName(kvp.Key)).EndsWith(TheLanguage))
{
allFilePathsTemp.Add(ExcelMethods.GetTableName(kvp.Key), kvp.Value);
continue;
}
bool isNeedLanguage = true;
foreach (string str in AppLanguage.AppConfigIDArr)
{
if (ExcelMethods.GetTableName(kvp.Key).EndsWith(str))
{
isNeedLanguage = false;
break;
}
}
if (isNeedLanguage == true)
{
allFilePathsTemp2.Add(kvp.Key, kvp.Value);
}
else
{
AppLog.LogWarning(string.Format("文件：{0}，已忽略，因为不是指定的语言", kvp.Key));
}
}
foreach (KeyValuePair <string, List <string> > kvp in allFilePathsTemp2)
{
if (!allFilePathsTemp.ContainsKey(ExcelMethods.GetTableName(kvp.Key) + TheLanguage))
{
allFilePathsTemp.Add(ExcelMethods.GetTableName(kvp.Key), kvp.Value);
}
}
}
return(allFilePathsTemp);
}
/// <summary>
/// 用于递归对表格进行数据完整性检查
/// </summary>
public static void _CheckIntegrity(List <FieldInfo> indexField, Dictionary <object, object> parentDict, List <object> parentKeys, List <List <object> > effectiveValues, ref int currentLevel, StringBuilder errorStringBuilder)
{
if (effectiveValues[currentLevel] != null)
{
List <object> inputData = new List <object>(parentDict.Keys);
foreach (object value in effectiveValues[currentLevel])
{
if (!inputData.Contains(value))
{
if (currentLevel > 0)
{
StringBuilder parentKeyInfoBuilder = new StringBuilder();
for (int i = 0; i <= currentLevel - 1; ++i)
{
parentKeyInfoBuilder.AppendFormat("{0}={1},", indexField[i].FieldName, parentKeys[i]);
}
string parentKeyInfo = parentKeyInfoBuilder.ToString().Substring(0, parentKeyInfoBuilder.Length - 1);
errorStringBuilder.AppendFormat("字段\"{0}\"（列号：{1}）缺少在{2}情况下值为\"{3}\"的数据\n", indexField[currentLevel].FieldName, ExcelMethods.GetExcelColumnName(indexField[currentLevel].ColumnSeq + 1), parentKeyInfo, value);
}
else
{
errorStringBuilder.AppendFormat("字段\"{0}\"（列号：{1}）缺少值为\"{2}\"的数据\n", indexField[currentLevel].FieldName, ExcelMethods.GetExcelColumnName(indexField[currentLevel].ColumnSeq + 1), value);
}
}
}
}
if (currentLevel < effectiveValues.Count - 1)
{
foreach (var key in parentDict.Keys)
{
parentKeys[currentLevel] = key;
++currentLevel;
_CheckIntegrity(indexField, (Dictionary <object, object>)(parentDict[key]), parentKeys, effectiveValues, ref currentLevel, errorStringBuilder);
--currentLevel;
}
}
}
private static string _GetOneField(FieldInfo fieldInfo, int row, int level, out string errorString, bool isExportLuaNilConfig = true)
{
errorString = null;
// 对应数据值
string value = null;
switch (fieldInfo.DataType)
{
case DataType.Int:
case DataType.Long:
case DataType.Float:
{
value = _GetNumberValue(fieldInfo, row, level);
break;
}
case DataType.String:
{
value = _GetStringValue(fieldInfo, row, level);
break;
}
case DataType.Bool:
{
value = _GetBoolValue(fieldInfo, row, level);
break;
}
case DataType.Lang:
{
value = _GetLangValue(fieldInfo, row, level);
break;
}
case DataType.Date:
{
value = _GetDateValue(fieldInfo, row, level);
break;
}
case DataType.Time:
{
value = _GetTimeValue(fieldInfo, row, level);
break;
}
case DataType.Json:
{
value = _GetJsonValue(fieldInfo, row, level);
break;
}
case DataType.TableString:
{
value = _GetTableStringValue(fieldInfo, row, level, out errorString);
break;
}
case DataType.MapString:
{
value = _GetMapStringValue(fieldInfo, row, level);
break;
}
case DataType.Dict:
case DataType.Array:
{
value = _GetSetValue(fieldInfo, row, level, out errorString);
break;
}
default:
{
errorString = string.Format("_GetOneField函数中未定义{0}类型数据导出至lua文件的形式", fieldInfo.DataType);
AppLog.LogErrorAndExit(errorString);
return(null);
}
}
if (errorString != null)
{
errorString = string.Format("第{0}行第{1}列的数据存在错误无法导出，", row + ExcelTableSetting.DataFieldDataStartRowIndex + 1, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1)) + errorString;
return(null);
}
StringBuilder content = new StringBuilder();
if (LuaStruct.IsExportLuaNilConfig)
{
// 变量名前的缩进
content.Append(_GetLuaIndentation(level));
if (LuaStruct.IsArrayFieldName == true)
{
// 变量名，注意array下属的子元素在json中不含key的声明
if (fieldInfo.ParentField != null && fieldInfo.ParentField.DataType == DataType.Array)
{
content.Append(fieldInfo.FieldName);
content.Append(" = ");
}
}
// 变量名，注意array下属的子元素在json中不含key的声明
if (!(fieldInfo.ParentField != null && fieldInfo.ParentField.DataType == DataType.Array))
{
content.Append(fieldInfo.FieldName);
content.Append(" = ");
}
content.Append(value);
// 一个字段结尾加逗号
content.AppendLine(",");
return(content.ToString());
}
else
{
if (value != "nil")
{
// 变量名前的缩进
content.Append(_GetLuaIndentation(level));
if (LuaStruct.IsArrayFieldName == true)
{
// 变量名，注意array下属的子元素在json中不含key的声明
if (fieldInfo.ParentField != null && fieldInfo.ParentField.DataType == DataType.Array)
{
content.Append(fieldInfo.FieldName);
content.Append(" = ");
}
}
// 变量名，注意array下属的子元素在json中不含key的声明
if (!(fieldInfo.ParentField != null && fieldInfo.ParentField.DataType == DataType.Array))
{
content.Append(fieldInfo.FieldName);
content.Append(" = ");
}
content.Append(value);
// 一个字段结尾加逗号
content.AppendLine(",");
return(content.ToString());
}
else
{
return(null);
}
}
}
/// <summary>
/// 按指定索引方式导出数据时,通过此函数递归生成层次结构,当递归到最内层时输出指定table value中的数据
/// </summary>
private static void _GetIndexFieldData(StringBuilder content, Dictionary <object, object> parentDict, List <FieldInfo> tableValueField, ref int currentLevel, out string errorString)
{
string oneTableValueFieldData = null;
foreach (var key in parentDict.Keys)
{
/*加上这2句大文件卡死*/
//if (content.ToString().EndsWith("}"))
// content.Remove(content.Length - 1, 1).Append(",");
// content.Append(_GetJsonIndentation(currentLevel));
// 生成key
//if(key==null | key.ToString()=="")
//{
// errorString = "错误：嵌套导出的key不能为空！";
// AppLog.LogErrorAndExit(errorString);
// return;
//}
if (key.GetType() == typeof(int) || key.GetType() == typeof(long) || key.GetType() == typeof(float))
{
content.Append("\"").Append(key).Append("\"");
}
else if (key.GetType() == typeof(string))
{
//// 检查作为key值的变量名是否合法
//TableCheckHelper.CheckFieldName(key.ToString(), out errorString);
//if (errorString != null)
//{
// errorString = string.Format("作为第{0}层索引的key值不是合法的变量名，你填写的为\"{1}\"", currentLevel - 1, key.ToString());
// return;
//}
//content.Append(key);
content.Append("\"").Append(key).Append("\"");
}
else
{
errorString = string.Format("SpecialExportTableToJson中出现非法类型的索引列类型{0}", key.GetType());
AppLog.LogErrorAndExit(errorString);
return;
}
content.Append(":{");// content.AppendLine(":{");
++currentLevel;
// 如果已是最内层，输出指定table value中的数据
if (parentDict[key].GetType() == typeof(int))
{
foreach (FieldInfo fieldInfo in tableValueField)
{
int rowIndex = (int)parentDict[key];
oneTableValueFieldData = _GetOneField(fieldInfo, rowIndex, out errorString);//
if (errorString != null)
{
errorString = string.Format("第{0}行的字段\"{1}\"（列号：{2}）导出数据错误：{3}", rowIndex + ExcelTableSetting.DataFieldDataStartRowIndex + 1, fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), errorString);
return;
}
else
{
if (oneTableValueFieldData == null)
{
errorString = string.Format("第{0}行的字段\"{1}\"（列号：{2}）导出数据错误：{3}", rowIndex + ExcelTableSetting.DataFieldDataStartRowIndex + 1, fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), "嵌套导出的值不能为空");
//AppLog.LogErrorAndExit(errorString);
//return;
content.Append("\"").Append(fieldInfo.FieldName).Append("\"");
content.Append(":");
switch (fieldInfo.DataType)
{
case DataType.Int:
case DataType.Long:
case DataType.Float:
{
content.Append("0").Append(",");
break;
}
case DataType.String:
{
content.Append("\"\"").Append(",");
break;
}
case DataType.Bool:
{
content.Append("false").Append(",");
break;
}
case DataType.Lang:
case DataType.Date:
case DataType.Time:
default:
{
content.Append("\"\"").Append(",");
break;
}
}
}
else
{
content.Append(oneTableValueFieldData);
}
}
}
// 去掉最后一个子元素后多余的英文逗号
if (content.ToString().EndsWith(","))
{
content.Remove(content.Length - 1, 1);
}
}
// 否则继续递归生成索引key
else
{
// if (content.ToString().EndsWith("}"))
// content.Remove(content.Length - 1, 1).Append(",");
_GetIndexFieldData(content, (Dictionary <object, object>)(parentDict[key]), tableValueField, ref currentLevel, out errorString);
if (errorString != null)
{
return;
}
if (content.ToString().EndsWith(","))
{
content.Remove(content.Length - 1, 1);
}
//content.Append(",");
}
--currentLevel;
// content.Append(_GetJsonIndentation(currentLevel));
content.Append("},"); // content.AppendLine("},");
}
errorString = null;
}
public static bool ExportTableToJson(TableInfo tableInfo, out string errorString)
{
StringBuilder content = new StringBuilder();
// 若生成为各行数据对应的json object包含在一个json array的形式
if (JsonStruct.ExportJsonIsExportJsonArrayFormat == true)
{
// 生成json字符串开头，每行数据为一个json object，作为整张表json array的元素
content.Append("[");
// 逐行读取表格内容生成json
List<FieldInfo> allField = tableInfo.GetAllClientFieldInfo();
int dataCount = tableInfo.GetKeyColumnFieldInfo().Data.Count;
int fieldCount = allField.Count;
for (int row = 0; row < dataCount; ++row)
{
// 生成一行数据json object的开头
content.Append("{");
for (int column = 0; column < fieldCount; ++column)
{
string oneFieldString = _GetOneField(allField[column], row, out errorString);
if (errorString != null)
{
errorString = string.Format("导出表格{0}为json文件失败，", tableInfo.TableName) + errorString;
return false;
}
else
{
content.Append(oneFieldString);
}
}
// 去掉本行最后一个字段后多余的英文逗号，json语法不像lua那样最后一个字段后的逗号可有可无
content.Remove(content.Length - 1, 1);
// 生成一行数据json object的结尾
content.Append("}");
// 每行的json object后加英文逗号
content.Append(",");
}
// 去掉最后一行后多余的英文逗号，此处要特殊处理当表格中没有任何数据行时的情况
if (content.Length > 1)
content.Remove(content.Length - 1, 1);
// 生成json字符串结尾
content.Append("]");
}
else
{
// 生成json字符串开头，每行数据以表格主键列为key，各字段信息组成的json object为value，作为整张表json object的元素
content.Append("{");
// 逐行读取表格内容生成json
List<FieldInfo> allField = tableInfo.GetAllClientFieldInfo();
FieldInfo keyColumnInfo = tableInfo.GetKeyColumnFieldInfo();
int dataCount = keyColumnInfo.Data.Count;
int fieldCount = allField.Count;
for (int row = 0; row < dataCount; ++row)
{
// 将主键列的值作为key
string keyString = null;
StringBuilder contentkey = new StringBuilder();
if (keyColumnInfo.DataType == DataType.String)
{
keyString = _GetStringValue(keyColumnInfo, row);
contentkey.Append(keyString);
}
else if (keyColumnInfo.DataType == DataType.Int || keyColumnInfo.DataType == DataType.Long)
{
keyString = _GetNumberValue(keyColumnInfo, row);
contentkey.Append("\"").Append(keyString).Append("\"");
}
else
{
errorString = string.Format("ExportTableToJson函数中未定义{0}类型的主键数值导出至json文件的形式", keyColumnInfo.DataType);
AppLog.LogErrorAndExit(errorString);
return false;
}
StringBuilder contenvalue = new StringBuilder();
int startColumn = (JsonStruct.ExportJsonIsExportJsonMapIncludeKeyColumnValue == true ? 0 : 1);
for (int column = startColumn; column < fieldCount; ++column)
{
string oneFieldString = _GetOneField(allField[column], row, out errorString);
if (errorString != null)
{
errorString = string.Format("额外导出表格{0}为json文件失败，", tableInfo.TableName) + errorString;
return false;
}
else
{
contenvalue.Append(oneFieldString);
}
}
string str = contenvalue.ToString();
if (JsonStruct.ExportJsonIsExportJsonMapIncludeKeyColumnValue == true)
{
// 生成一行数据json object的开头
content.Append(contentkey);
content.Append(":{");
content.Append(contenvalue);
// 去掉本行最后一个字段后多余的英文逗号，json语法不像lua那样最后一个字段后的逗号可有可无
content.Remove(content.Length - 1, 1);
// 生成一行数据json object的结尾
content.Append("}");
// 每行的json object后加英文逗号
content.Append(",");
}
else if (str != "")
{
// 生成一行数据json object的开头
content.Append(contentkey);
content.Append(":{");
content.Append(contenvalue);
// 去掉本行最后一个字段后多余的英文逗号，json语法不像lua那样最后一个字段后的逗号可有可无
content.Remove(content.Length - 1, 1);
// 生成一行数据json object的结尾
content.Append("}");
// 每行的json object后加英文逗号
content.Append(",");
}
}
// 去掉最后一行后多余的英文逗号，此处要特殊处理当表格中没有任何数据行时的情况
if (content.Length > 1)
content.Remove(content.Length - 1, 1);
// 生成json字符串结尾
content.Append("}");
}
string exportString = content.ToString();
// 如果声明了要整理为带缩进格式的形式
if (JsonStruct.ExportJsonIsFormat == true)
exportString = _FormatJson(exportString);
// 保存为json文件
if (SaveJson.SaveJsonFile(tableInfo.ExcelName, ExcelMethods.GetSaveTableName(tableInfo.TableName), exportString) == true)
{
errorString = null;
//try
//{
// LitJson.JsonData jsonData = LitJson.JsonMapper.ToObject(exportString);
//}
//catch (LitJson.JsonException exception)
//{
// errorString = "错误：导出json出现异常，请检查导出的json及Excel\n";
//}
return true;
}
else
{
errorString = "保存为json文件失败\n";
return false;
}
}
/// <summary>
/// 特殊导出erlang
/// </summary>
/// <param name="tableInfo"></param>
/// <param name="exportRule"></param>
/// <param name="errorString"></param>
/// <returns></returns>
public static bool SpecialExportTableToErlang(TableInfo tableInfo, string exportRule, out string errorString)
{
errorString = null;
exportRule = exportRule.Trim();
// 解析按这种方式导出后的erlang文件名
int colonIndex = exportRule.IndexOf(':');
if (colonIndex == -1)
{
errorString = string.Format("导出配置\"{0}\"定义错误，必须在开头声明导出lua文件名\n", exportRule);
return(false);
}
string fileName = exportRule.Substring(0, colonIndex).Trim();
// 判断是否在最后的花括号内声明table value中包含的字段
int leftBraceIndex = exportRule.LastIndexOf('{');
int rightBraceIndex = exportRule.LastIndexOf('}');
// 解析依次作为索引的字段名
string indexFieldNameString = null;
// 注意分析花括号时要考虑到未声明table value中的字段而在某索引字段完整性检查规则中用花括号声明了有效值的情况
if (exportRule.EndsWith("}") && leftBraceIndex != -1)
{
indexFieldNameString = exportRule.Substring(colonIndex + 1, leftBraceIndex - colonIndex - 1);
}
else
{
indexFieldNameString = exportRule.Substring(colonIndex + 1, exportRule.Length - colonIndex - 1);
}
string[] indexFieldDefine = indexFieldNameString.Split(new char[] { '-' }, System.StringSplitOptions.RemoveEmptyEntries);
// 用于索引的字段列表
List <FieldInfo> indexField = new List <FieldInfo>();
// 索引字段对应的完整性检查规则
List <string> integrityCheckRules = new List <string>();
if (indexFieldDefine.Length < 1)
{
errorString = string.Format("导出配置\"{0}\"定义错误，用于索引的字段不能为空，请按fileName:indexFieldName1-indexFieldName2{otherFieldName1,otherFieldName2}的格式配置\n", exportRule);
return(false);
}
// 检查字段是否存在且为int、float、string或lang型
foreach (string fieldDefine in indexFieldDefine)
{
string fieldName = null;
// 判断是否在字段名后用小括号声明了该字段的完整性检查规则
int leftBracketIndex = fieldDefine.IndexOf('(');
int rightBracketIndex = fieldDefine.IndexOf(')');
if (leftBracketIndex > 0 && rightBracketIndex > leftBracketIndex)
{
fieldName = fieldDefine.Substring(0, leftBracketIndex);
string integrityCheckRule = fieldDefine.Substring(leftBracketIndex + 1, rightBracketIndex - leftBracketIndex - 1).Trim();
if (string.IsNullOrEmpty(integrityCheckRule))
{
errorString = string.Format("导出配置\"{0}\"定义错误，用于索引的字段\"{1}\"若要声明完整性检查规则就必须在括号中填写否则不要加括号\n", exportRule, fieldName);
return(false);
}
integrityCheckRules.Add(integrityCheckRule);
}
else
{
fieldName = fieldDefine.Trim();
integrityCheckRules.Add(null);
}
FieldInfo fieldInfo = tableInfo.GetFieldInfoByFieldName(fieldName);
if (fieldInfo == null)
{
errorString = string.Format("导出配置\"{0}\"定义错误，声明的索引字段\"{1}\"不存在\n", exportRule, fieldName);
return(false);
}
if (fieldInfo.DataType != DataType.Int && fieldInfo.DataType != DataType.Long && fieldInfo.DataType != DataType.Float && fieldInfo.DataType != DataType.String && fieldInfo.DataType != DataType.Lang)
{
errorString = string.Format("导出配置\"{0}\"定义错误，声明的索引字段\"{1}\"为{2}型，但只允许为int、long、float、string或lang型\n", exportRule, fieldName, fieldInfo.DataType);
return(false);
}
// 对索引字段进行非空检查
if (fieldInfo.DataType == DataType.String)
{
FieldCheckRule stringNotEmptyCheckRule = new FieldCheckRule();
stringNotEmptyCheckRule.CheckType = TableCheckType.NotEmpty;
stringNotEmptyCheckRule.CheckRuleString = "notEmpty[trim]";
TableCheckHelper.CheckNotEmpty(fieldInfo, stringNotEmptyCheckRule, out errorString);
if (errorString != null)
{
errorString = string.Format("按配置\"{0}\"进行自定义导出错误，string型索引字段\"{1}\"中存在以下空值，而作为索引的key不允许为空\n{2}\n", exportRule, fieldName, errorString);
return(false);
}
}
else if (fieldInfo.DataType == DataType.Lang)
{
FieldCheckRule langNotEmptyCheckRule = new FieldCheckRule();
langNotEmptyCheckRule.CheckType = TableCheckType.NotEmpty;
langNotEmptyCheckRule.CheckRuleString = "notEmpty[key|value]";
TableCheckHelper.CheckNotEmpty(fieldInfo, langNotEmptyCheckRule, out errorString);
if (errorString != null)
{
errorString = string.Format("按配置\"{0}\"进行自定义导出错误，lang型索引字段\"{1}\"中存在以下空值，而作为索引的key不允许为空\n{2}\n", exportRule, fieldName, errorString);
return(false);
}
}
else if (CheckTableInfo.IsAllowedNullNumber == true)
{
FieldCheckRule numberNotEmptyCheckRule = new FieldCheckRule();
numberNotEmptyCheckRule.CheckType = TableCheckType.NotEmpty;
numberNotEmptyCheckRule.CheckRuleString = "notEmpty";
TableCheckHelper.CheckNotEmpty(fieldInfo, numberNotEmptyCheckRule, out errorString);
if (errorString != null)
{
errorString = string.Format("按配置\"{0}\"进行自定义导出错误，{1}型索引字段\"{2}\"中存在以下空值，而作为索引的key不允许为空\n{3}\n", exportRule, fieldInfo.DataType.ToString(), fieldName, errorString);
return(false);
}
}
indexField.Add(fieldInfo);
}
// 解析table value中要输出的字段名
List <FieldInfo> tableValueField = new List <FieldInfo>();
// 如果在花括号内配置了table value中要输出的字段名
if (exportRule.EndsWith("}") && leftBraceIndex != -1 && leftBraceIndex < rightBraceIndex)
{
string tableValueFieldName = exportRule.Substring(leftBraceIndex + 1, rightBraceIndex - leftBraceIndex - 1);
string[] fieldNames = tableValueFieldName.Split(new char[] { ',' }, System.StringSplitOptions.RemoveEmptyEntries);
if (fieldNames.Length < 1)
{
errorString = string.Format("导出配置\"{0}\"定义错误，花括号中声明的table value中的字段不能为空，请按fileName:indexFieldName1-indexFieldName2{otherFieldName1,otherFieldName2}的格式配置\n", exportRule);
return(false);
}
// 检查字段是否存在
foreach (string fieldName in fieldNames)
{
FieldInfo fieldInfo = tableInfo.GetFieldInfoByFieldName(fieldName);
if (fieldInfo == null)
{
errorString = string.Format("导出配置\"{0}\"定义错误，声明的table value中的字段\"{1}\"不存在\n", exportRule, fieldName);
return(false);
}
if (tableValueField.Contains(fieldInfo))
{
AppLog.LogWarning(string.Format("警告：导出配置\"{0}\"定义中，声明的table value中的字段存在重复，字段名为{1}（列号{2}），本工具只生成一次，请修正错误\n", exportRule, fieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1)));
}
else
{
tableValueField.Add(fieldInfo);
}
}
}
else if (exportRule.EndsWith("}") && leftBraceIndex == -1)
{
errorString = string.Format("导出配置\"{0}\"定义错误，声明的table value中花括号不匹配\n", exportRule);
return(false);
}
// 如果未在花括号内声明，则默认将索引字段之外的所有字段进行填充
else
{
List <string> indexFieldNameList = new List <string>(indexFieldDefine);
foreach (FieldInfo fieldInfo in tableInfo.GetAllClientFieldInfo())
{
if (!indexFieldNameList.Contains(fieldInfo.FieldName))
{
tableValueField.Add(fieldInfo);
}
}
}
// 解析完依次作为索引的字段以及table value中包含的字段后，按索引要求组成相应的嵌套数据结构
Dictionary <object, object> data = new Dictionary <object, object>();
int rowCount = tableInfo.GetKeyColumnFieldInfo().Data.Count;
for (int i = 0; i < rowCount; ++i)
{
Dictionary <object, object> temp = data;
// 生成除最内层的数据结构
for (int j = 0; j < indexField.Count - 1; ++j)
{
FieldInfo oneIndexField = indexField[j];
var tempData = oneIndexField.Data[i];
if (!temp.ContainsKey(tempData))
{
temp.Add(tempData, new Dictionary <object, object>());
}
temp = (Dictionary <object, object>)temp[tempData];
}
// 最内层的value存数据的int型行号（从0开始计）
FieldInfo lastIndexField = indexField[indexField.Count - 1];
var lastIndexFieldData = _GetOneField(lastIndexField, i, 1, out errorString);
if (!temp.ContainsKey(lastIndexFieldData))
{
temp.Add(lastIndexFieldData, i);
}
else
{
errorString = string.Format("错误：对表格{0}按\"{1}\"规则进行特殊索引导出时发现第{2}行与第{3}行在各个索引字段的值完全相同，导出被迫停止，请修正错误后重试\n", tableInfo.TableName, exportRule, i + ExcelTableSetting.DataFieldDataStartRowIndex + 1, temp[lastIndexFieldData]);
AppLog.LogErrorAndExit(errorString);
return(false);
}
}
// 进行数据完整性检查
if (CheckTableInfo.IsNeedCheck == true)
{
TableCheckHelper.CheckTableIntegrity(indexField, data, integrityCheckRules, out errorString);
if (errorString != null)
{
errorString = string.Format("错误：对表格{0}按\"{1}\"规则进行特殊索引导时未通过数据完整性检查，导出被迫停止，请修正错误后重试：\n{2}\n", tableInfo.TableName, exportRule, errorString);
return(false);
}
}
// 生成导出的文件内容
StringBuilder content = new StringBuilder();
// 生成数据内容开头
content.AppendLine("%%--- coding:utf-8 ---");
content.Append("-module(").Append(ErlangStruct.ExportNameBeforeAdd + tableInfo.TableName).AppendLine(").");
string oneFieldString = null;
// 当前缩进量
int currentLevel = 1;
foreach (var key in data.Keys)
{
// 生成key
if (key.GetType() == typeof(int) || key.GetType() == typeof(float))
{
content.Append("get(").Append(key).AppendLine(")->");
}
else if (key.GetType() == typeof(string))
{
content.Append("get(").Append(key).AppendLine(")->");
}
else
{
errorString = string.Format("SpecialExportTableToErlang中出现非法类型的索引列类型{0}", key.GetType());
AppLog.LogErrorAndExit(errorString);
return(false);
}
// content.Append(_GetErlangIndentation(currentLevel));
content.Append(" #{");
// 如果已是最内层，输出指定table value中的数据
if (data[key].GetType() == typeof(int))
{
int column = 0;
foreach (FieldInfo fieldInfo in tableValueField)
{
column++;
if (column > 1)
{
content.Append(_GetErlangIndentation(currentLevel));
}
int rowIndex = (int)data[key];
oneFieldString = _GetOneField(fieldInfo, rowIndex, currentLevel, out errorString);
if (errorString != null)
{
errorString = string.Format("导出表格{0}失败，", tableInfo.TableName) + errorString;
return(false);
}
else
{
if (column > 1)
{
content.Append(",");
}
content.Append("'").Append(fieldInfo.DatabaseFieldName).Append("' => ").AppendLine(oneFieldString);
}
}
}
content.AppendLine(" },");
}
// 生成数据内容结尾
content.AppendLine("get(_N) -> false.");
content.AppendLine("get_list() ->");
content.Append("\t[");
foreach (var key in data.Keys)
{
content.Append(key).Append(",");
}
content.Remove(content.Length - 1, 1);
content.Append("]");
string exportString = content.ToString();
//if (ErlangStruct.IsNeedColumnInfo == true)
// exportString = _GetColumnInfo(tableInfo) + exportString;
// 保存为erlang文件
if (SaveErlang.SaveErlangFile(tableInfo.ExcelName, ExcelMethods.GetSaveTableName(fileName), exportString) == true)
{
errorString = null;
return(true);
}
else
{
errorString = "保存为erlang文件失败\n";
return(false);
}
}
private static bool ExportOneTable(TableInfo tableInfo, Export export, out string errorString)
{
try
{
StringBuilder stringBuilder = new StringBuilder();
foreach (DataTable dt in AppValues.ExcelDataSet[tableInfo.ExcelFilePath].Tables)
{
if (dt.TableName == "config$")
{
continue;
}
StringBuilder content = new StringBuilder();
for (int row = 0; row < dt.Rows.Count; ++row)
{
List <string> strList = new List <string>();
string str = null;
for (int column = 0; column < dt.Columns.Count; ++column)
{
str = str + "{" + column + "}" + export.ExportSpaceString;
strList.Add(dt.Rows[row][column].ToString());
}
string[] str2 = strList.ToArray();
content.Append(string.Format(str, str2)).Append(export.ExportLineString);
}
string exportString = content.ToString();
if (exportString.Length < 2)
{
continue;
}
// 保存为txt文件
if (export.ExportPath == null || export.ExportPath == "")
{
export.ExportPath = Path.GetDirectoryName(tableInfo.ExcelFilePath);
}
export.ExportContent = content.ToString();
string sheetName = dt.TableName;
if (sheetName.StartsWith("'"))
{
sheetName = sheetName.Substring(1);
}
if (sheetName.EndsWith("'"))
{
sheetName = sheetName.Substring(0, sheetName.Length - 1);
}
if (sheetName.EndsWith("$"))
{
sheetName = sheetName.Substring(0, sheetName.Length - 1);
}
string s = ExcelMethods.GetTableName(tableInfo.ExcelName, "-", ExcelFolder.TheLanguage);
export.SaveFile(s, tableInfo.ExcelName, sheetName);
AppLog.Log(string.Format("成功导出：{0}{1}{2}.{3}", tableInfo.ExcelName, "-", sheetName, export.ExportExtension));
errorString = null;
return(true);
}
if (stringBuilder == null)
{
errorString = null;
return(true);
}
else
{
errorString = stringBuilder.ToString();
if (errorString.Length > 0)
{
return(false);
}
else
{
errorString = null;
return(true);
}
}
}
catch (Exception e)
{
#if DEBUG
errorString = e.ToString();
#endif
errorString = "遇到错误";
return(false);
}
}
private static string _GetOneField(FieldInfo fieldInfo, int row, int level, out string errorString)
{
StringBuilder content = new StringBuilder();
errorString = null;
// 变量名前的缩进
content.Append(_GetJsonIndentation(level));
// 变量名
if (!(fieldInfo.ParentField != null && fieldInfo.ParentField.DataType == DataType.Array))
{
content.Append("\"").Append(fieldInfo.FieldName).Append("\"");
content.Append(":");
}
// 对应数据值
string value = null;
switch (fieldInfo.DataType)
{
case DataType.Int:
case DataType.Long:
case DataType.Float:
{
value = _GetNumberValue(fieldInfo, row, level);
break;
}
case DataType.String:
{
value = _GetStringValue(fieldInfo, row, level);
break;
}
case DataType.Bool:
{
value = _GetBoolValue(fieldInfo, row, level);
break;
}
case DataType.Lang:
{
value = _GetLangValue(fieldInfo, row, level);
break;
}
case DataType.Date:
{
value = _GetDateValue(fieldInfo, row, level);
break;
}
case DataType.Time:
{
value = _GetTimeValue(fieldInfo, row, level);
break;
}
case DataType.Json:
{
value = _GetJsonValue(fieldInfo, row, level);
break;
}
case DataType.TableString:
{
value = _GetTableStringValue(fieldInfo, row, level, out errorString);
break;
}
case DataType.MapString:
{
value = _GetMapStringValue(fieldInfo, row, level);
break;
}
case DataType.Dict:
case DataType.Array:
{
value = _GetSetValue(fieldInfo, row, level, out errorString);
break;
}
default:
{
errorString = string.Format("_GetOneField函数中未定义{0}类型数据导出至json文件的形式", fieldInfo.DataType);
AppLog.LogErrorAndExit(errorString);
return(null);
}
}
if (errorString != null)
{
errorString = string.Format("第{0}行第{1}列的数据存在错误无法导出，", row + ExcelTableSetting.DataFieldDataStartRowIndex + 1, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1)) + errorString;
return(null);
}
content.Append(value);
// 一个字段结尾加逗号并换行
content.AppendLine(",");
return(content.ToString());
}
/// <summary>
/// 解析一列的数据结构及数据，返回FieldInfo
/// </summary>
private static FieldInfo _AnalyzeOneField(DataTable dt, TableInfo tableInfo, int columnIndex, FieldInfo parentField, out int nextFieldColumnIndex, out string errorString)
{
// 判断列号是否越界
if (columnIndex >= dt.Columns.Count)
{
errorString = "需要解析的列号越界，可能因为dict或array中实际的子元素个数低于声明的个数";
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
FieldInfo fieldInfo = new FieldInfo();
// 所在表格
fieldInfo.TableName = tableInfo.TableName;
// 字段描述中的换行全替换为空格
fieldInfo.Desc = dt.Rows[ExcelTableSetting.DataFieldDescRowIndex][columnIndex].ToString().Trim().Replace(System.Environment.NewLine, " ").Replace('\n', ' ').Replace('\r', ' ').Replace('\t', ' ');
// 字段所在列号
fieldInfo.ColumnSeq = columnIndex;
// 检查规则字符串
string checkRuleString = null;
if (ExcelTableSetting.DataFieldCheckRuleRowIndex >= 0)
{
checkRuleString = dt.Rows[ExcelTableSetting.DataFieldCheckRuleRowIndex][columnIndex].ToString().Trim().Replace(System.Environment.NewLine, " ").Replace('\n', ' ').Replace('\r', ' ').Replace('\t', ' ');
}
fieldInfo.CheckRule = string.IsNullOrEmpty(checkRuleString) ? null : checkRuleString;
// 导出到数据库中的字段名及类型
if (ExcelTableSetting.DataFieldExportDataBaseFieldInFoRowIndex >= 0)
{
string databaseInfoString = dt.Rows[ExcelTableSetting.DataFieldExportDataBaseFieldInFoRowIndex][columnIndex].ToString().Trim();
fieldInfo.DatabaseInfoString = databaseInfoString;
if (string.IsNullOrEmpty(databaseInfoString))
{
fieldInfo.DatabaseFieldName = null;
fieldInfo.DatabaseFieldType = null;
}
else
{
int leftBracketIndex = databaseInfoString.IndexOf('(');
int rightBracketIndex = databaseInfoString.LastIndexOf(')');
if (leftBracketIndex == -1 || rightBracketIndex == -1 || leftBracketIndex > rightBracketIndex)
{
errorString = "导出到数据库中表字段信息声明错误，必须在字段名后的括号中声明对应数据库中的数据类型";
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
fieldInfo.DatabaseFieldName = databaseInfoString.Substring(0, leftBracketIndex);
fieldInfo.DatabaseFieldType = databaseInfoString.Substring(leftBracketIndex + 1, rightBracketIndex - leftBracketIndex - 1);
}
}
// 引用父FileInfo
fieldInfo.ParentField = parentField;
// 如果该字段是array类型的子元素，则不填写变量名（array下属元素的变量名自动顺序编号）
// 并且如果子元素不是集合类型，也不需配置数据类型（直接使用array声明的子元素数据类型，子元素列不再单独配置），直接依次声明各子元素列
string inputFieldName = dt.Rows[ExcelTableSetting.DataFieldNameRowIndex][columnIndex].ToString().Trim();
if (parentField != null && parentField.DataType == DataType.Array)
{
// array的子元素如果为array或dict，则必须像array、dict定义格式那样通过一列声明子元素类型（目的为了校验，防止类似array声明子元素为dict[2]，而实际子元素为dict[3]，导致程序仍能正确解析之后的字段，但逻辑上却把后面的独立字段误认为是声明的array的子元素），但变量名仍旧不需填写
if (parentField.ArrayChildDataType == DataType.Array)
{
string inputDataTypeString = dt.Rows[ExcelTableSetting.DataFieldDataTypeRowIndex][columnIndex].ToString().Trim();
if (string.IsNullOrEmpty(inputDataTypeString))
{
errorString = "array的子元素若为array型，则必须在每个子元素声明列中声明具体的array类型";
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
// 判断本列所声明的类型是否为array型
DataType inputDataType = _AnalyzeDataType(inputDataTypeString);
if (inputDataType != DataType.Array)
{
errorString = string.Format("父array声明的子元素类型为array，但第{0}列所填的类型为{1}", ExcelMethods.GetExcelColumnName(columnIndex + 1), inputDataType.ToString());
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
// 解析本列实际填写的array的子元素数据类型定义
string childDataTypeString;
DataType childDataType;
int childCount;
_GetArrayChildDefine(inputDataTypeString, out childDataTypeString, out childDataType, out childCount, out errorString);
if (errorString != null)
{
errorString = string.Format("解析第{0}列定义的array类型声明错误，{1}", ExcelMethods.GetExcelColumnName(columnIndex + 1), errorString);
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
// 解析父array声明的子类型数据类型定义（之前经过了检测，这里无需进行容错）
string defineDataTypeString;
DataType defineDataType;
int defineCount;
_GetArrayChildDefine(parentField.ArrayChildDataTypeString, out defineDataTypeString, out defineDataType, out defineCount, out errorString);
if (childDataType != defineDataType)
{
errorString = string.Format("父array的array类型子元素所声明的子元素为{0}型，但你填写的经过解析为{1}型", defineDataType.ToString(), childDataType.ToString());
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
if (childCount != defineCount)
{
errorString = string.Format("父array的array类型子元素所声明的子元素共有{0}个，但你填写的经过解析为{1}个", defineCount, childCount);
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
}
else if (parentField.ArrayChildDataType == DataType.Dict)
{
string inputDataTypeString = dt.Rows[ExcelTableSetting.DataFieldDataTypeRowIndex][columnIndex].ToString().Trim();
// dict类型子元素定义列中允许含有无效列
if (string.IsNullOrEmpty(inputDataTypeString))
{
errorString = null;
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
else
{
// 判断本列所声明的类型是否为dict型
DataType inputDataType = _AnalyzeDataType(inputDataTypeString);
if (inputDataType != DataType.Dict)
{
errorString = string.Format("父array声明的子元素类型为dict，但第{0}列所填的类型为{1}", ExcelMethods.GetExcelColumnName(columnIndex + 1), inputDataType.ToString());
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
// 解析本列实际填写的dict的子元素格式
int childCount;
_GetDictChildCount(inputDataTypeString, out childCount, out errorString);
if (errorString != null)
{
errorString = string.Format("解析第{0}列定义的dict类型声明错误，{1}", ExcelMethods.GetExcelColumnName(columnIndex + 1), errorString);
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
// 解析父array声明的dict子元素个数（之前经过了检测，这里无需进行容错）
int defineCount;
_GetDictChildCount(parentField.ArrayChildDataTypeString, out defineCount, out errorString);
if (childCount != defineCount)
{
errorString = string.Format("父array的dict类型子元素所声明的子元素共有{0}个，但你填写的经过解析为{1}个", defineCount, childCount);
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
}
}
// 检查array下属子元素列，不允许填写变量名（如果不进行此强制限制，当定表时没有空出足够的array子元素列数，并且后面为独立字段时，解析array型时会把后面的独立字段当成array子元素声明列，很可能造成格式上可以正确解析，但逻辑上和定表者想法不符但不易发觉的问题）
if (!string.IsNullOrEmpty(inputFieldName))
{
errorString = string.Format("array下属的子元素列不允许填写变量名，第{0}列错误地填写了变量名{1}", ExcelMethods.GetExcelColumnName(columnIndex + 1), inputFieldName);
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
fieldInfo.FieldName = null;
fieldInfo.DataType = parentField.ArrayChildDataType;
// array类型的ArrayChildDataTypeString中还包含了子元素个数，需要去除
int lastColonIndex = parentField.ArrayChildDataTypeString.LastIndexOf(':');
if (lastColonIndex == -1)
{
AppLog.LogErrorAndExit("错误：用_AnalyzeOneField函数解析array子元素类型时发现array类型的ArrayChildDataTypeString中不含有子元素个数");
errorString = null;
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
else
{
fieldInfo.DataTypeString = parentField.ArrayChildDataTypeString.Substring(0, lastColonIndex);
}
}
else if (string.IsNullOrEmpty(inputFieldName))//客户端字段名为空时
{
// dict下属字段的变量名为空的列，视为无效列，直接忽略
if (fieldInfo.ParentField != null && fieldInfo.ParentField.DataType == DataType.Dict)
{
// AppLog.LogWarning(string.Format("警告：第{0}列为dict下属字段，但未填写变量名，将被视为无效列而忽略", ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1)));
errorString = null;
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
// 独立字段未填写字段名以及导出数据库信息，视为无效列，直接忽略
else if (string.IsNullOrEmpty(fieldInfo.DatabaseInfoString))
{
// AppLog.LogWarning(string.Format("警告：第{0}列未填写变量名，也未填写导出数据库信息，将被视为无效列而忽略", ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1)));
errorString = null;
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
// 未填写字段名但填写了导出数据库的信息，视为只进行MySQL导出的字段，自动进行字段命名
else
{
// AppLog.LogWarning(string.Format("警告：第{0}列未填写变量名，仅填写了导出数据库信息，将不会进行lua、csv、json等客户端形式导出", ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1)));
fieldInfo.FieldName = string.Concat(AppValues.AutoFieldNamePrefix, fieldInfo.ColumnSeq);
fieldInfo.IsIgnoreClientExport = true;
// 读取填写的数据类型
fieldInfo.DataTypeString = dt.Rows[ExcelTableSetting.DataFieldDataTypeRowIndex][columnIndex].ToString().Trim();
fieldInfo.DataType = _AnalyzeDataType(fieldInfo.DataTypeString);
}
}
else
{
// 检查字段名是否合法
if (!TableCheckHelper.CheckFieldName(inputFieldName, out errorString))
{
nextFieldColumnIndex = columnIndex + 1;
return(null);
}
else
{
fieldInfo.FieldName = inputFieldName;
}
// 读取填写的数据类型
fieldInfo.DataTypeString = dt.Rows[ExcelTableSetting.DataFieldDataTypeRowIndex][columnIndex].ToString().Trim();
fieldInfo.DataType = _AnalyzeDataType(fieldInfo.DataTypeString);
}
switch (fieldInfo.DataType)
{
case DataType.Int:
{
_AnalyzeIntType(fieldInfo, tableInfo, dt, columnIndex, parentField, out nextFieldColumnIndex, out errorString);
break;
}
case DataType.Long:
{
_AnalyzeLongType(fieldInfo, tableInfo, dt, columnIndex, parentField, out nextFieldColumnIndex, out errorString);
break;
}
case DataType.Float:
{
_AnalyzeFloatType(fieldInfo, tableInfo, dt, columnIndex, parentField, out nextFieldColumnIndex, out errorString);
break;
}
case DataType.Bool:
{
_AnalyzeBoolType(fieldInfo, tableInfo, dt, columnIndex, parentField, out nextFieldColumnIndex, out errorString);
break;
}
case DataType.String:
{
_AnalyzeStringType(fieldInfo, tableInfo, dt, columnIndex, parentField, out nextFieldColumnIndex, out errorString);
break;
}
case DataType.Lang:
{
_AnalyzeLangType(fieldInfo, tableInfo, dt, columnIndex, parentField, out nextFieldColumnIndex, out errorString);
break;
}
case DataType.Date:
{
_AnalyzeDateType(fieldInfo, tableInfo, dt, columnIndex, parentField, out nextFieldColumnIndex, out errorString);
break;
}
case DataType.Time:
{
_AnalyzeTimeType(fieldInfo, tableInfo, dt, columnIndex, parentField, out nextFieldColumnIndex, out errorString);
break;
}
case DataType.Json:
{
_AnalyzeJsonType(fieldInfo, tableInfo, dt, columnIndex, parentField, out nextFieldColumnIndex, out errorString);
break;
}
case DataType.TableString:
{
_AnalyzeTableStringType(fieldInfo, tableInfo, dt, columnIndex, parentField, out nextFieldColumnIndex, out errorString);
break;
}
case DataType.MapString:
{
_AnalyzeMapStringType(fieldInfo, tableInfo, dt, columnIndex, parentField, out nextFieldColumnIndex, out errorString);
break;
}
case DataType.Array:
{
_AnalyzeArrayType(fieldInfo, tableInfo, dt, columnIndex, parentField, out nextFieldColumnIndex, out errorString);
break;
}
case DataType.Dict:
{
_AnalyzeDictType(fieldInfo, tableInfo, dt, columnIndex, parentField, out nextFieldColumnIndex, out errorString);
break;
}
default:
{
errorString = string.Format("数据类型无效，填写的为{0}", fieldInfo.DataTypeString);
nextFieldColumnIndex = columnIndex + 1;
break;
}
}
if (errorString == null)
{
return(fieldInfo);
}
else
{
return(null);
}
}
/// <summary>
/// JSON、LUA自定义导出规则检查
/// </summary>
/// <param name="tableInfo"></param>
/// <param name="exportRule">自定义导出字符串</param>
/// <param name="tableValueField">解析table value中要输出的字段名</param>
/// <param name="data">解析完依次作为索引的字段以及table value中包含的字段后，按索引要求组成相应的嵌套数据结构</param>
/// <param name="errorString"></param>
/// <returns></returns>
public static bool CheckSpecialExportRule(TableInfo tableInfo, string exportRule, out List <FieldInfo> tableValueField, out Dictionary <object, object> data, out string errorString)
{
tableValueField = new List <FieldInfo>();
data = new Dictionary <object, object>();
// 解析按这种方式导出后的json文件名
int colonIndex = exportRule.IndexOf(':');
if (colonIndex == -1)
{
errorString = string.Format("导出配置\"{0}\"定义错误，必须在开头声明导出json文件名\n", exportRule);
return(false);
}
// 判断是否在最后的花括号内声明table value中包含的字段
int leftBraceIndex = exportRule.LastIndexOf('{');
int rightBraceIndex = exportRule.LastIndexOf('}');
// 解析依次作为索引的字段名
string indexFieldNameString = null;
// 注意分析花括号时要考虑到未声明table value中的字段而在某索引字段完整性检查规则中用花括号声明了有效值的情况
if (exportRule.EndsWith("}") && leftBraceIndex != -1)
{
indexFieldNameString = exportRule.Substring(colonIndex + 1, leftBraceIndex - colonIndex - 1);
}
else
{
indexFieldNameString = exportRule.Substring(colonIndex + 1, exportRule.Length - colonIndex - 1);
}
string[] indexFieldDefine = indexFieldNameString.Split(new char[] { '-' }, System.StringSplitOptions.RemoveEmptyEntries);
// 用于索引的字段列表
List <FieldInfo> indexField = new List <FieldInfo>();
// 索引字段对应的完整性检查规则
List <string> integrityCheckRules = new List <string>();
if (indexFieldDefine.Length < 1)
{
errorString = string.Format("导出配置\"{0}\"定义错误，用于索引的字段不能为空，请按fileName:indexFieldName1-indexFieldName2{otherFieldName1,otherFieldName2}的格式配置\n", exportRule);
return(false);
}
// 检查字段是否存在且为int、float、string或lang型
foreach (string fieldDefine in indexFieldDefine)
{
string fieldName = null;
// 判断是否在字段名后用小括号声明了该字段的完整性检查规则
int leftBracketIndex = fieldDefine.IndexOf('(');
int rightBracketIndex = fieldDefine.IndexOf(')');
if (leftBracketIndex > 0 && rightBracketIndex > leftBracketIndex)
{
fieldName = fieldDefine.Substring(0, leftBracketIndex);
string integrityCheckRule = fieldDefine.Substring(leftBracketIndex + 1, rightBracketIndex - leftBracketIndex - 1).Trim();
if (string.IsNullOrEmpty(integrityCheckRule))
{
errorString = string.Format("导出配置\"{0}\"定义错误，用于索引的字段\"{1}\"若要声明完整性检查规则就必须在括号中填写否则不要加括号\n", exportRule, fieldName);
return(false);
}
integrityCheckRules.Add(integrityCheckRule);
}
else
{
fieldName = fieldDefine.Trim();
integrityCheckRules.Add(null);
}
FieldInfo fieldInfo = tableInfo.GetFieldInfoByFieldName(fieldName);
if (fieldInfo == null)
{
errorString = string.Format("导出配置\"{0}\"定义错误，声明的索引字段\"{1}\"不存在\n", exportRule, fieldName);
return(false);
}
if (fieldInfo.DataType != DataType.Int && fieldInfo.DataType != DataType.Long && fieldInfo.DataType != DataType.Float && fieldInfo.DataType != DataType.String && fieldInfo.DataType != DataType.Lang)
{
errorString = string.Format("导出配置\"{0}\"定义错误，声明的索引字段\"{1}\"为{2}型，但只允许为int、long、float、string或lang型\n", exportRule, fieldName, fieldInfo.DataType);
return(false);
}
// 对索引字段进行非空检查
if (fieldInfo.DataType == DataType.String)
{
FieldCheckRule stringNotEmptyCheckRule = new FieldCheckRule();
stringNotEmptyCheckRule.CheckType = TableCheckType.NotEmpty;
stringNotEmptyCheckRule.CheckRuleString = "notEmpty[trim]";
TableCheckHelper.CheckNotEmpty(fieldInfo, stringNotEmptyCheckRule, out errorString);
if (errorString != null)
{
errorString = string.Format("按配置\"{0}\"进行自定义导出错误，string型索引字段\"{1}\"中存在以下空值，而作为索引的key不允许为空\n{2}\n", exportRule, fieldName, errorString);
return(false);
}
}
else if (fieldInfo.DataType == DataType.Lang)
{
FieldCheckRule langNotEmptyCheckRule = new FieldCheckRule();
langNotEmptyCheckRule.CheckType = TableCheckType.NotEmpty;
langNotEmptyCheckRule.CheckRuleString = "notEmpty[key|value]";
TableCheckHelper.CheckNotEmpty(fieldInfo, langNotEmptyCheckRule, out errorString);
if (errorString != null)
{
errorString = string.Format("按配置\"{0}\"进行自定义导出错误，lang型索引字段\"{1}\"中存在以下空值，而作为索引的key不允许为空\n{2}\n", exportRule, fieldName, errorString);
return(false);
}
}
else if (global::ExcelFolder.IsAllowedNullNumber == true)
{
FieldCheckRule numberNotEmptyCheckRule = new FieldCheckRule();
numberNotEmptyCheckRule.CheckType = TableCheckType.NotEmpty;
numberNotEmptyCheckRule.CheckRuleString = "notEmpty";
TableCheckHelper.CheckNotEmpty(fieldInfo, numberNotEmptyCheckRule, out errorString);
if (errorString != null)
{
errorString = string.Format("按配置\"{0}\"进行自定义导出错误，{1}型索引字段\"{2}\"中存在以下空值，而作为索引的key不允许为空\n{3}\n", exportRule, fieldInfo.DataType.ToString(), fieldName, errorString);
return(false);
}
}
indexField.Add(fieldInfo);
}
// 解析table value中要输出的字段名
//List<FieldInfo> tableValueField = new List<FieldInfo>();
// 如果在花括号内配置了table value中要输出的字段名
if (exportRule.EndsWith("}") && leftBraceIndex != -1 && leftBraceIndex < rightBraceIndex)
{
string tableValueFieldName = exportRule.Substring(leftBraceIndex + 1, rightBraceIndex - leftBraceIndex - 1);
string[] fieldNames = tableValueFieldName.Split(new char[] { ',' }, System.StringSplitOptions.RemoveEmptyEntries);
if (fieldNames.Length < 1)
{
errorString = string.Format("导出配置\"{0}\"定义错误，花括号中声明的table value中的字段不能为空，请按fileName:indexFieldName1-indexFieldName2{otherFieldName1,otherFieldName2}的格式配置\n", exportRule);
return(false);
}
// 检查字段是否存在
foreach (string fieldName in fieldNames)
{
FieldInfo fieldInfo = tableInfo.GetFieldInfoByFieldName(fieldName);
if (fieldInfo == null)
{
errorString = string.Format("导出配置\"{0}\"定义错误，声明的table value中的字段\"{1}\"不存在\n", exportRule, fieldName);
return(false);
}
if (tableValueField.Contains(fieldInfo))
{
AppLog.LogWarning(string.Format("警告：导出配置\"{0}\"定义中，声明的table value中的字段存在重复，字段名为{1}（列号{2}），本工具只生成一次，请修正错误\n", exportRule, fieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1)));
}
else
{
tableValueField.Add(fieldInfo);
}
}
}
else if (exportRule.EndsWith("}") && leftBraceIndex == -1)
{
errorString = string.Format("导出配置\"{0}\"定义错误，声明的table value中花括号不匹配\n", exportRule);
return(false);
}
// 如果未在花括号内声明，则默认将索引字段之外的所有字段进行填充
else
{
List <string> indexFieldNameList = new List <string>(indexFieldDefine);
foreach (FieldInfo fieldInfo in tableInfo.GetAllClientFieldInfo())
{
if (!indexFieldNameList.Contains(fieldInfo.FieldName))
{
tableValueField.Add(fieldInfo);
}
}
}
// 解析完依次作为索引的字段以及table value中包含的字段后，按索引要求组成相应的嵌套数据结构
// Dictionary<object, object> data = new Dictionary<object, object>();
int rowCount = tableInfo.GetKeyColumnFieldInfo().Data.Count;
for (int i = 0; i < rowCount; ++i)
{
Dictionary <object, object> temp = data;
// 生成除最内层的数据结构
for (int j = 0; j < indexField.Count - 1; ++j)
{
FieldInfo oneIndexField = indexField[j];
var tempData = oneIndexField.Data[i];
if (!temp.ContainsKey(tempData))
{
temp.Add(tempData, new Dictionary <object, object>());
}
temp = (Dictionary <object, object>)temp[tempData];
}
// 最内层的value存数据的int型行号（从0开始计）
FieldInfo lastIndexField = indexField[indexField.Count - 1];
var lastIndexFieldData = lastIndexField.Data[i];
if (!temp.ContainsKey(lastIndexFieldData))
{
temp.Add(lastIndexFieldData, i);
}
else
{
errorString = string.Format("错误：对表格{0}按\"{1}\"规则进行特殊索引导出时发现第{2}行与第{3}行在各个索引字段的值完全相同，导出被迫停止，请修正错误后重试\n", tableInfo.TableName, exportRule, i + ExcelTableSetting.DataFieldDataStartRowIndex + 1, temp[lastIndexFieldData]);
AppLog.LogErrorAndExit(errorString);
return(false);
}
}
// 进行数据完整性检查
if (global::ExcelFolder.IsNeedCheck == true)
{
TableCheckHelper.CheckTableIntegrity(indexField, data, integrityCheckRules, out errorString);
if (errorString != null)
{
errorString = string.Format("错误：对表格{0}按\"{1}\"规则进行特殊索引导时未通过数据完整性检查，导出被迫停止，请修正错误后重试：\n{2}\n", tableInfo.TableName, exportRule, errorString);
return(false);
}
}
errorString = null;
return(true);
}
/// <summary>
/// 用于int、long、float、string、date或time型取值不允许为指定集合值中的一个的检查
/// </summary>
public static bool CheckIllegal(FieldInfo fieldInfo, FieldCheckRule checkRule, out string errorString)
{
List <object> repeatedSetValue = null;
List <int> errorDataIndex = null;
string setValueDefineString = checkRule.CheckRuleString.Substring(1).Trim();
_GetValueIsInSet(fieldInfo.Data, fieldInfo.DataType, setValueDefineString, false, out repeatedSetValue, out errorDataIndex, out errorString);
if (errorString == null)
{
if (repeatedSetValue.Count > 0)
{
foreach (object setValue in repeatedSetValue)
{
if (fieldInfo.DataType == DataType.Int || fieldInfo.DataType == DataType.Long || fieldInfo.DataType == DataType.Float || fieldInfo.DataType == DataType.String)
{
AppLog.LogWarning(string.Format("警告：字段{0}（列号：{1}）的非法值检查规则定义中，出现了相同的非法值\"{2}\"，本工具忽略此问题继续进行检查，需要你之后修正规则定义错误\n", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), setValue));
}
else if (fieldInfo.DataType == DataType.Date)
{
DateTime dataTimeSetValue = (DateTime)setValue;
AppLog.LogWarning(string.Format("警告：字段{0}（列号：{1}）的非法值检查规则定义中，出现了相同的非法值\"{2}\"，本工具忽略此问题继续进行检查，需要你之后修正规则定义错误\n", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), dataTimeSetValue.ToString(DateTimeValue.APP_DEFAULT_DATE_FORMAT)));
}
else if (fieldInfo.DataType == DataType.Time)
{
DateTime dataTimeSetValue = (DateTime)setValue;
AppLog.LogWarning(string.Format("警告：字段{0}（列号：{1}）的非法值检查规则定义中，出现了相同的非法值\"{2}\"，本工具忽略此问题继续进行检查，需要你之后修正规则定义错误\n", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), dataTimeSetValue.ToString(DateTimeValue.APP_DEFAULT_TIME_FORMAT)));
}
}
}
if (errorDataIndex.Count > 0)
{
StringBuilder illegalValueInfo = new StringBuilder();
foreach (int dataIndex in errorDataIndex)
{
if (fieldInfo.DataType == DataType.Int || fieldInfo.DataType == DataType.Long || fieldInfo.DataType == DataType.Float || fieldInfo.DataType == DataType.String)
{
illegalValueInfo.AppendFormat("第{0}行数据\"{1}\"属于非法取值中的一个\n", dataIndex + ExcelTableSetting.DataFieldDataStartRowIndex + 1, fieldInfo.Data[dataIndex]);
}
else if (fieldInfo.DataType == DataType.Date)
{
DateTime dataTimeValue = (DateTime)fieldInfo.Data[dataIndex];
illegalValueInfo.AppendFormat("第{0}行数据\"{1}\"属于非法取值中的一个\n", dataIndex + ExcelTableSetting.DataFieldDataStartRowIndex + 1, dataTimeValue.ToString(DateTimeValue.APP_DEFAULT_DATE_FORMAT));
}
else if (fieldInfo.DataType == DataType.Time)
{
DateTime dataTimeValue = (DateTime)fieldInfo.Data[dataIndex];
illegalValueInfo.AppendFormat("第{0}行数据\"{1}\"属于非法取值中的一个\n", dataIndex + ExcelTableSetting.DataFieldDataStartRowIndex + 1, dataTimeValue.ToString(DateTimeValue.APP_DEFAULT_TIME_FORMAT));
}
}
errorString = illegalValueInfo.ToString();
return(false);
}
else
{
return(true);
}
}
else
{
errorString = errorString + "\n";
return(false);
}
}
/// <summary>
/// 检查奖励列表字段是否配置正确，要求字段的数据结构必须为array[dict[3]:n]，定义一种奖励类型的三个int型字段分别叫type、id、count，每个奖励项的类型必须存在，除道具类型之外不允许奖励同一种类型，奖励数量必须大于0，如果是道具类型则道具id在道具表中要存在
/// </summary>
public static bool CheckRewardListField(FieldInfo fieldInfo, out string errorString)
{
// 道具类型对应的type
int PROP_TYPE;
const string PROP_TYPE_CONFIG_KEY = "propType";
if (AppValues.ConfigData.ContainsKey(PROP_TYPE_CONFIG_KEY))
{
string configValue = AppValues.ConfigData[PROP_TYPE_CONFIG_KEY];
if (int.TryParse(configValue, out PROP_TYPE) == false)
{
errorString = string.Format("config配置中用于表示道具类型对应数字（名为\"{0}\"）的配置项所填值不是合法的数字（你填的为\"{1}\"），无法进行奖励列表字段的检查，请修正配置后再重试\n", PROP_TYPE_CONFIG_KEY, configValue);
return(false);
}
}
else
{
errorString = string.Format("config配置文件找不到名为\"{0}\"的表示道具类型对应数字的配置，无法进行奖励列表字段的检查，请填写配置后再重试\n", PROP_TYPE_CONFIG_KEY);
return(false);
}
// 合法奖励类型检查规则
List <FieldCheckRule> CHECK_TYPE_RULES;
const string TYPE_CONFIG_KEY = "$type";
if (AppValues.ConfigData.ContainsKey(TYPE_CONFIG_KEY))
{
CHECK_TYPE_RULES = TableCheckHelper.GetCheckRules(AppValues.ConfigData[TYPE_CONFIG_KEY], out errorString);
if (errorString != null)
{
errorString = string.Format("config文件中用于检查奖励类型是否合法的规则\"{0}\"有误，{1}\n", TYPE_CONFIG_KEY, errorString);
return(false);
}
if (CHECK_TYPE_RULES == null)
{
errorString = string.Format("config文件中用于检查奖励类型是否合法的规则\"{0}\"为空，无法进行奖励列表字段的检查，请填写配置后再重试\n", TYPE_CONFIG_KEY, errorString);
return(false);
}
}
else
{
errorString = string.Format("config配置文件找不到名为\"{0}\"的表示合法奖励类型对应数字数组的配置，无法进行奖励列表字段的检查，请填写配置后再重试\n", TYPE_CONFIG_KEY);
return(false);
}
// 读取Prop表的主键id字段，用于填写道具id的值引用检查
List <object> PROP_KEYS = null;
const string PROP_TABLE_NAME = "Prop";
if (AppValues.TableInfo.ContainsKey(PROP_TABLE_NAME))
{
PROP_KEYS = AppValues.TableInfo[PROP_TABLE_NAME].GetKeyColumnFieldInfo().Data;
}
else
{
errorString = string.Format("找不到名为\"{0}\"用于配置道具属性的表格，无法进行奖励列表字段的检查\n", PROP_TABLE_NAME);
return(false);
}
// 要求字段的数据结构必须为array[dict[3]:n]
if (!(fieldInfo.DataType == DataType.Array && fieldInfo.ArrayChildDataType == DataType.Dict))
{
errorString = "奖励列表字段的数据结构必须为array[dict[3]:n]";
return(false);
}
// 标识组成一个奖励项的三个字段的名称（type、id、count）
List <string> FIELD_NAMES = new List <string>();
FIELD_NAMES.Add("type");
FIELD_NAMES.Add("id");
FIELD_NAMES.Add("count");
// 要求定义一种奖励类型的三个int型字段分别叫type、id、count
foreach (FieldInfo childDictField in fieldInfo.ChildField)
{
if (childDictField.ChildField.Count != 3)
{
errorString = string.Format("一个奖励项的dict必须由type、id、count三个int型字段组成，而你填写的奖励项由{0}个字段组成，出错的dict列号为{1}", childDictField.ChildField.Count, ExcelMethods.GetExcelColumnName(childDictField.ColumnSeq + 1));
return(false);
}
foreach (FieldInfo childBaseField in childDictField.ChildField)
{
if (!FIELD_NAMES.Contains(childBaseField.FieldName.ToLower()))
{
errorString = string.Format("一个奖励项的dict必须由type、id、count三个int型字段组成，而你填写的奖励项中含有名为\"{0}\"的字段，出错的dict列号为{1}", childBaseField.FieldName, ExcelMethods.GetExcelColumnName(childDictField.ColumnSeq + 1));
return(false);
}
else if (childBaseField.DataType != DataType.Int)
{
errorString = string.Format("一个奖励项的dict必须由type、id、count三个int型字段组成，而你填写的奖励项中的{0}字段的数据类型为{1}，出错的dict列号为{2}", childBaseField.DataType, childBaseField.FieldName, ExcelMethods.GetExcelColumnName(childDictField.ColumnSeq + 1));
return(false);
}
}
}
// 读取并检查每一行数据是否符合奖励列表的要求
StringBuilder errorStringBuilder = new StringBuilder();
for (int i = 0; i < fieldInfo.Data.Count; ++i)
{
// 如果本行定义的奖励列表声明为无效，直接跳过
if ((bool)fieldInfo.Data[i] == false)
{
continue;
}
// 记录该行已配置的奖励类型（key：type， value：恒为true），但注意道具类型不计入因为允许奖励多种道具类型，只要id不同即可
Dictionary <int, bool> rewardType = new Dictionary <int, bool>();
// 记录该行已配置的奖励道具的id（key：道具id， value：恒为true）
Dictionary <int, bool> rewardPropId = new Dictionary <int, bool>();
//bool isCorrectForTheLineData = true;
foreach (FieldInfo childDictField in fieldInfo.ChildField)
{
// 一旦读到用-1标识的无效数据，则不再读取array中后面的dict字段，即如果为array[dict[3]:4]并且第二个dict用-1标为无效数据，则认为此行仅配置了1个奖励项，不再读取判断第三四个奖励项是否配置
if ((bool)childDictField.Data[i] == false)
{
break;
}
int type = -1;
int id = -1;
int count = -1;
foreach (FieldInfo field in childDictField.ChildField)
{
if (field.FieldName.Equals("type", System.StringComparison.CurrentCultureIgnoreCase))
{
if (field.Data[i] == null)
{
errorString = string.Format("第{0}行第{1}列\"type\"字段填写值为空", i + ExcelTableSetting.DataFieldDataStartRowIndex + 1, ExcelMethods.GetExcelColumnName(field.ColumnSeq + 1));
return(false);
}
else
{
type = (int)field.Data[i];
}
}
else if (field.FieldName.Equals("id", System.StringComparison.CurrentCultureIgnoreCase))
{
if (field.Data[i] == null)
{
errorString = string.Format("第{0}行第{1}列\"id\"字段填写值为空", i + ExcelTableSetting.DataFieldDataStartRowIndex + 1, ExcelMethods.GetExcelColumnName(field.ColumnSeq + 1));
return(false);
}
else
{
id = (int)field.Data[i];
}
}
else if (field.FieldName.Equals("count", System.StringComparison.CurrentCultureIgnoreCase))
{
if (field.Data[i] == null)
{
errorString = string.Format("第{0}行第{1}列\"count\"字段填写值为空", i + ExcelTableSetting.DataFieldDataStartRowIndex + 1, ExcelMethods.GetExcelColumnName(field.ColumnSeq + 1));
return(false);
}
else
{
count = (int)field.Data[i];
}
}
}
// 对于道具类型，需检查奖励的道具id不能重复且Prop表中要存在该道具id
if (type == PROP_TYPE)
{
if (rewardPropId.ContainsKey(id))
{
errorStringBuilder.AppendFormat("第{0}行的奖励列表中含有同种道具（id={1}）类型\n", i + ExcelTableSetting.DataFieldDataStartRowIndex + 1, id);
}
else
{
rewardPropId.Add(id, true);
}
if (!PROP_KEYS.Contains(id))
{
errorStringBuilder.AppendFormat("第{0}行第{1}列的奖励项中所填的奖励道具（id={2}）在道具表中不存在\n", i + ExcelTableSetting.DataFieldDataStartRowIndex + 1, ExcelMethods.GetExcelColumnName(childDictField.ColumnSeq + 1), id);
}
}
// 对于非道具类型，需检查奖励类型type不能重复
else
{
if (rewardType.ContainsKey(type))
{
errorStringBuilder.AppendFormat("第{0}行的奖励列表中含有同种奖励类型（{1}）\n", i + ExcelTableSetting.DataFieldDataStartRowIndex + 1, type);
}
else
{
rewardType.Add(type, true);
}
}
// 均要检查奖励count不能低于1
if (count < 1)
{
errorStringBuilder.AppendFormat("第{0}行第{1}列的奖励项中所填的奖励数量低于1\n", i + ExcelTableSetting.DataFieldDataStartRowIndex + 1, ExcelMethods.GetExcelColumnName(childDictField.ColumnSeq + 1), type);
}
}
}
// 按字段检查所有type列所填的类型是否合法
foreach (FieldInfo childDictField in fieldInfo.ChildField)
{
foreach (FieldInfo field in childDictField.ChildField)
{
if (field.FieldName.Equals("type", System.StringComparison.CurrentCultureIgnoreCase))
{
TableCheckHelper.CheckByRules(CHECK_TYPE_RULES, field, out errorString);
if (errorString != null)
{
errorStringBuilder.AppendFormat("检查type列发现以下无效type类型：\n{0}", errorString);
errorString = null;
}
}
}
}
if (string.IsNullOrEmpty(errorStringBuilder.ToString()))
{
errorString = null;
return(true);
}
else
{
errorString = errorStringBuilder.ToString();
return(false);
}
}
public static void ExportToMySQL()
{
if (MySQLStruct.IsExport == false)
{
return;
}
string errorString = null;
AppLog.Log("\n导出表格数据到MySQL数据库\n");
errorString = null;
List <string> errorInfo = new List <string>();
foreach (KeyValuePair <string, TableInfo> kvp in AppValues.TableInfo)
{
string tableExportNameforMySQL = kvp.Key;
TableInfo tableInfo = kvp.Value;
foreach (FieldInfo fieldInfo in tableInfo.GetAllFieldInfo())
{
string databaseInfoString = null;
if (string.IsNullOrEmpty(databaseInfoString))
{
fieldInfo.DatabaseFieldName = null;
fieldInfo.DatabaseFieldType = null;
}
else
{
int leftBracketIndex = databaseInfoString.IndexOf('(');
int rightBracketIndex = databaseInfoString.LastIndexOf(')');
if (leftBracketIndex == -1 || rightBracketIndex == -1 || leftBracketIndex > rightBracketIndex)
{
errorString = string.Format("表名为：{0}，的第 {1} 列（第 {2} 行声明字段名为 {3} 错误，\n导出到MySQL中表字段信息声明错误，必须在字段名后的括号中声明对应数据库中的数据类型）", tableInfo.ExcelName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), ExcelTableSetting.DataFieldExportDataBaseFieldInFoRowIndex, fieldInfo.DatabaseInfoString);
errorInfo.Add(errorString);
}
fieldInfo.DatabaseFieldName = databaseInfoString.Substring(0, leftBracketIndex);
fieldInfo.DatabaseFieldType = databaseInfoString.Substring(leftBracketIndex + 1, rightBracketIndex - leftBracketIndex - 1);
}
}
}
if (errorInfo.Count > 0)
{
AppLog.LogErrorAndExit(errorInfo.ToString());
}
TableExportToMySQLHelper.ConnectToDatabase(out errorString);
if (!string.IsNullOrEmpty(errorString))
{
AppLog.LogErrorAndExit(string.Format("无法连接至MySQL数据库：{0}\n导出至MySQL数据库被迫中止，请修正错误后重试\n", errorString));
}
foreach (KeyValuePair <string, TableInfo> kvp in AppValues.TableInfo)
{
string tableExportNameforMySQL = kvp.Key;
TableInfo tableInfo = kvp.Value;
List <string> inputParams = new List <string>();
if (TableAnalyzeHelper.GetOneConfigData(tableInfo, MySQLStruct.CONFIG_NAME_EXPORT_DATABASE_TABLE_NAME, ref inputParams))
{
tableExportNameforMySQL = inputParams[0];
}
if (TableAnalyzeHelper.GetOneConfigData(tableInfo, MySQLStruct.Excel_Config_ExportMySQL, ref MySQLStruct.IsExportMySQL))
{
if (MySQLStruct.IsExportMySQL == true)
{
TableExportToMySQLHelper.ExportTableToDatabase(kvp.Value, tableExportNameforMySQL, out errorString);
if (!string.IsNullOrEmpty(errorString))
{
AppLog.LogErrorAndExit(string.Format("导出失败：{0}\n导出至MySQL数据库被迫中止，请修正错误后重试\n", errorString));
}
}
}
else
{
//if (MySQLStruct.IsExport == true)
//{
TableExportToMySQLHelper.ExportTableToDatabase(kvp.Value, tableExportNameforMySQL, out errorString);
if (!string.IsNullOrEmpty(errorString))
{
AppLog.LogErrorAndExit(string.Format("导出失败：{0}\n导出至MySQL数据库被迫中止，请修正错误后重试\n", errorString));
}
//}
}
}
}
private static bool _CreateTable(string tableName, TableInfo tableInfo, string comment, out string errorString)
{
// 生成在创建数据表时所有字段的声明
StringBuilder fieldDefineStringBuilder = new StringBuilder();
foreach (FieldInfo fieldInfo in GetAllDatabaseFieldInfo(tableInfo))
{
// 在这里并不对每种本工具的数据类型是否能导出为指定的SQLite数据类型进行检查（比如本工具中的string型应该导出为SQLite中的文本类型如varchar，而不应该导出为数值类型）
if (fieldInfo.DataType == DataType.Date)
{
string toDatabaseFormatDefine = fieldInfo.ExtraParam[SQLiteStruct.TABLE_INFO_EXTRA_PARAM_KEY_DATE_TO_DATABASE_FORMAT].ToString();
DateFormatType toDatabaseFormatType = TableAnalyzeHelper.GetDateFormatType(toDatabaseFormatDefine);
if (fieldInfo.DatabaseFieldType.StartsWith("time", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("date型字段\"{0}\"（列号：{1}）声明导出到SQLite中的数据类型错误，不允许为time型，如果仅需要时分秒部分，请在Excel中将该字段在本工具中的数据类型改为time型", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1));
return(false);
}
if (toDatabaseFormatType == DateFormatType.ReferenceDateSec || toDatabaseFormatType == DateFormatType.ReferenceDateMsec)
{
if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase) || fieldInfo.DatabaseFieldType.Equals("date", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("date型字段\"{0}\"（列号：{1}）声明导出到SQLite中的形式为距1970年的时间（{2}），但所填写的导出到SQLite中的格式为时间型的{3}，请声明为SQLite中的数值型", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), toDatabaseFormatDefine, fieldInfo.DatabaseFieldType);
return(false);
}
}
}
else if (fieldInfo.DataType == DataType.Time)
{
string toDatabaseFormatDefine = fieldInfo.ExtraParam[SQLiteStruct.TABLE_INFO_EXTRA_PARAM_KEY_TIME_TO_DATABASE_FORMAT].ToString();
TimeFormatType toDatabaseFormatType = TableAnalyzeHelper.GetTimeFormatType(toDatabaseFormatDefine);
if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase) || fieldInfo.DatabaseFieldType.Equals("date", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("time型字段\"{0}\"（列号：{1}）声明导出到SQLite中的数据类型错误，不允许为datetime或date型，如果需要年月日部分，请在Excel中将该字段在本工具中的数据类型改为date型", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1));
return(false);
}
if (toDatabaseFormatType == TimeFormatType.ReferenceTimeSec && fieldInfo.DatabaseFieldType.StartsWith("time", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("time型字段\"{0}\"（列号：{1}）声明导出到SQLite中的形式为距0点的秒数（#sec），但所填写的导出到SQLite中的格式为时间型的time，请声明为SQLite中的数值型", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1));
return(false);
}
}
fieldDefineStringBuilder.AppendFormat("{0} {1},", _CombineDatabaseTableFullName(fieldInfo.DatabaseFieldName), fieldInfo.DatabaseFieldType);
}
fieldDefineStringBuilder.Remove(fieldDefineStringBuilder.Length - 1, 1);
string createTableExtraParam = AppValues.ConfigData.ContainsKey(MySQLStruct.APP_CONFIG_KEY_CREATE_DATABASE_TABLE_EXTRA_PARAM) ? AppValues.ConfigData[MySQLStruct.APP_CONFIG_KEY_CREATE_DATABASE_TABLE_EXTRA_PARAM] : string.Empty;
string createTableSql = string.Format(_CREATE_TABLE_SQL, tableName, fieldDefineStringBuilder.ToString());
try
{
SQLiteCommand cmd = new SQLiteCommand(createTableSql, _conn);
cmd.ExecuteNonQuery();
errorString = null;
return(true);
}
catch (SQLiteException exception)
{
errorString = exception.Message;
return(false);
}
}
private static bool CheckOneField(FieldInfo fieldInfo, out string errorString)
{
StringBuilder errorStringBuilder = new StringBuilder();
// 解析检查规则
List <FieldCheckRule> checkRules = GetCheckRules(fieldInfo.CheckRule, out errorString);
if (errorString != null)
{
errorStringBuilder.AppendFormat("字段\"{0}\"（列号：{1}）填写的检查规则\"{2}\"不合法：{3}，不得不跳过对该字段的检查\n\n", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), fieldInfo.CheckRule, errorString);
errorString = null;
}
else if (checkRules != null)
{
CheckByRules(checkRules, fieldInfo, out errorString);
if (errorString != null)
{
errorStringBuilder.Append(errorString);
errorString = null;
}
}
// array或dict下属子元素字段的检查
if (fieldInfo.DataType == DataType.Array || fieldInfo.DataType == DataType.Dict)
{
foreach (FieldInfo childField in fieldInfo.ChildField)
{
CheckOneField(childField, out errorString);
if (errorString != null)
{
errorStringBuilder.Append(errorString);
errorString = null;
}
}
}
errorString = errorStringBuilder.ToString();
if (string.IsNullOrEmpty(errorString))
{
errorString = null;
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 用于检查按自定义索引字段导出lua文件的表格中相关索引字段的数据完整性
/// </summary>
public static bool CheckTableIntegrity(List <FieldInfo> indexField, Dictionary <object, object> data, List <string> integrityCheckRules, out string errorString)
{
StringBuilder errorStringBuilder = new StringBuilder();
// 解析各个需要进行数据完整性检查的字段声明的有效值
List <List <object> > effectiveValues = new List <List <object> >();
for (int i = 0; i < integrityCheckRules.Count; ++i)
{
if (integrityCheckRules[i] == null)
{
effectiveValues.Add(null);
}
else
{
List <object> oneFieldEffectiveValues = Utils.GetEffectiveValue(integrityCheckRules[i], indexField[i].DataType, out errorString);
if (errorString != null)
{
errorStringBuilder.AppendFormat("字段\"{0}\"（列号：{1}）的数据完整性检查规则定义错误，{2}\n", indexField[i].FieldName, ExcelMethods.GetExcelColumnName(indexField[i].ColumnSeq + 1), errorString);
errorString = null;
}
else
{
effectiveValues.Add(oneFieldEffectiveValues);
}
}
}
errorString = errorStringBuilder.ToString();
if (string.IsNullOrEmpty(errorString))
{
errorString = null;
}
else
{
return(false);
}
// 进行数据完整性检查
List <object> parentKeys = new List <object>();
for (int i = 0; i < integrityCheckRules.Count; ++i)
{
parentKeys.Add(null);
}
int currentLevel = 0;
_CheckIntegrity(indexField, data, parentKeys, effectiveValues, ref currentLevel, errorStringBuilder);
errorString = errorStringBuilder.ToString();
if (string.IsNullOrEmpty(errorString))
{
errorString = null;
return(true);
}
else
{
return(false);
}
}
private static bool _AnalyzeLangType(FieldInfo fieldInfo, TableInfo tableInfo, DataTable dt, int columnIndex, FieldInfo parentField, out int nextFieldColumnIndex, out string errorString)
{
fieldInfo.LangKeys = new List <object>();
fieldInfo.Data = new List <object>();
// 如果是统一指定key名规则，需要解析替换规则
Dictionary <string, List <object> > replaceInfo = null;
string configString = null;
if (!fieldInfo.DataTypeString.Equals("lang", StringComparison.CurrentCultureIgnoreCase))
{
replaceInfo = _GetLangKeyReplaceInfo(fieldInfo.DataTypeString, tableInfo, out configString, out errorString);
if (errorString != null)
{
errorString = string.Format("lang型格式定义错误，{0}", errorString);
nextFieldColumnIndex = columnIndex + 1;
return(false);
}
if (replaceInfo.Count < 1)
{
AppLog.LogWarning(string.Format("警告：表格{0}中的Lang型字段{1}（列号：{2}）进行统一配置但使用的是完全相同的key名（{3}），请确定是否要这样设置", tableInfo.TableName, fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), configString));
}
}
for (int row = ExcelTableSetting.DataFieldDataStartRowIndex; row < dt.Rows.Count; ++row)
{
// 如果本行该字段的父元素标记为无效则该数据也标为无效
if (parentField != null && (bool)parentField.Data[row - ExcelTableSetting.DataFieldDataStartRowIndex] == false)
{
fieldInfo.LangKeys.Add(null);
fieldInfo.Data.Add(null);
continue;
}
string inputData = null;
// 未统一指定key名规则的Lang型数据，需在单元格中填写key
if (replaceInfo == null)
{
inputData = dt.Rows[row][columnIndex].ToString().Trim();
}
// 统一指定key名规则的Lang型数据，根据规则生成在Lang文件中的具体key名
else
{
inputData = configString;
foreach (var item in replaceInfo)
{
if (item.Value[row - ExcelTableSetting.DataFieldDataStartRowIndex] != null)
{
inputData = configString.Replace(item.Key, item.Value[row - ExcelTableSetting.DataFieldDataStartRowIndex].ToString());
}
}
}
if (string.IsNullOrEmpty(inputData))
{
fieldInfo.LangKeys.Add(string.Empty);
fieldInfo.Data.Add(null);
}
else
{
fieldInfo.LangKeys.Add(inputData);
if (AppLang.LangData.ContainsKey(inputData))
{
fieldInfo.Data.Add(AppLang.LangData[inputData]);
}
else
{
fieldInfo.Data.Add(null);
}
}
}
errorString = null;
nextFieldColumnIndex = columnIndex + 1;
return(true);
}
public static TableInfo AnalyzeTable(DataTable dt, string filePath, out string errorString)
{
if (dt.Rows.Count < ExcelTableSetting.DataFieldDataStartRowIndex)
{
errorString = "表格格式不符合要求，必须在表格前五行中依次声明字段描述、字段名、数据类型、检查规则、导出到MySQL数据库中的配置";
return(null);
}
if (dt.Columns.Count < 2)
{
errorString = "表格中至少需要配置2个字段";
return(null);
}
TableInfo tableInfo = new TableInfo();
tableInfo.ExcelFilePath = filePath;
tableInfo.ExcelName = Path.GetFileNameWithoutExtension(filePath);
tableInfo.TableName = ExcelMethods.GetTableName(tableInfo.ExcelName, "-", ExcelFolder.TheLanguage);
string tableName = tableInfo.ExcelName;
string temps = Path.GetDirectoryName(filePath);
int tempi = temps.LastIndexOf(@"\");
string temps2 = temps.Substring(tempi, temps.Length - tempi);
tableInfo.ExcelDirectory = temps2.Substring(1);
// 当前解析到的列号
int curColumnIndex = 0;
// 解析第一列（主键列，要求数据类型为int、long或string且数据非空、唯一）
DataType primaryKeyColumnType = _AnalyzeDataType(dt.Rows[ExcelTableSetting.DataFieldDataTypeRowIndex][0].ToString().Trim());
if (!(primaryKeyColumnType == DataType.Int || primaryKeyColumnType == DataType.Long || primaryKeyColumnType == DataType.String))
{
errorString = _GetTableAnalyzeErrorString(tableName, dt.TableName, 0) + "主键列的类型只能为int、long或string";
return(null);
}
else
{
// 解析出主键列，然后检查是否非空、唯一，如果是string型主键还要检查是否符合变量名的规范（只能由英文字母、数字、下划线组成）
FieldInfo primaryKeyField = _AnalyzeOneField(dt, tableInfo, 0, null, out curColumnIndex, out errorString);
if (errorString != null)
{
errorString = _GetTableAnalyzeErrorString(tableName, dt.TableName, 0) + "主键列解析错误\n" + errorString;
return(null);
}
// 主键列字段名不允许为空
else if (primaryKeyField == null)//else if (primaryKeyField.IsIgnoreClientExport == true)
{
errorString = _GetTableAnalyzeErrorString(tableName, dt.TableName, 0) + "主键列必须指定字段名\n" + errorString;
return(null);
}
else
{
// 唯一性检查
FieldCheckRule uniqueCheckRule = new FieldCheckRule();
uniqueCheckRule.CheckType = TableCheckType.Unique;
uniqueCheckRule.CheckRuleString = "unique";
TableCheckHelper.CheckUnique(primaryKeyField, uniqueCheckRule, out errorString);
if (errorString != null)
{
errorString = _GetTableAnalyzeErrorString(tableName, dt.TableName, 0) + "主键列存在重复错误\n" + errorString;
return(null);
}
// string型主键检查是否符合变量名的规范
if (primaryKeyColumnType == DataType.String)
{
StringBuilder errorStringBuilder = new StringBuilder();
for (int row = 0; row < primaryKeyField.Data.Count; ++row)
{
string tempError = null;
TableCheckHelper.CheckFieldName(primaryKeyField.Data[row].ToString(), out tempError);
if (tempError != null)
{
errorStringBuilder.AppendFormat("第{0}行所填主键{1}\n", row + ExcelTableSetting.DataFieldDataStartRowIndex + 1, tempError);
}
}
if (!string.IsNullOrEmpty(errorStringBuilder.ToString()))
{
errorString = _GetTableAnalyzeErrorString(tableName, dt.TableName, 0) + "string型主键列存在非法数据\n" + errorStringBuilder.ToString();
return(null);
}
}
// 非空检查（因为string型检查是否符合变量名规范时以及未声明数值型字段中允许空时，均已对主键列进行过非空检查，这里只需对数据值字段且声明数值型字段中允许空的情况下进行非空检查）
if (ExcelFolder.IsAllowedNullNumber == true && (primaryKeyColumnType == DataType.Int || primaryKeyColumnType == DataType.Long))
{
FieldCheckRule notEmptyCheckRule = new FieldCheckRule();
uniqueCheckRule.CheckType = TableCheckType.NotEmpty;
uniqueCheckRule.CheckRuleString = "notEmpty";
TableCheckHelper.CheckNotEmpty(primaryKeyField, notEmptyCheckRule, out errorString);
if (errorString != null)
{
errorString = _GetTableAnalyzeErrorString(tableName, dt.TableName, 0) + "主键列存在非空错误\n" + errorString;
return(null);
}
}
tableInfo.AddField(primaryKeyField);
}
}
// 存储定义过的字段名，不允许有同名字段assddds（key：字段名， value：列号)
Dictionary <string, int> clientFieldNames = new Dictionary <string, int>();
//Dictionary<string, int> serverFieldNames = new Dictionary<string, int>();
// 先加入主键列
clientFieldNames.Add(tableInfo.GetKeyColumnFieldInfo().FieldName, 0);
// if(tableInfo.GetKeyColumnFieldInfo().DatabaseFieldName!=null)
// serverFieldNames.Add(tableInfo.GetKeyColumnFieldInfo().DatabaseFieldName, 0);
// 解析剩余的列
while (curColumnIndex < dt.Columns.Count)
{
int nextColumnIndex = curColumnIndex;
FieldInfo oneField = _AnalyzeOneField(dt, tableInfo, nextColumnIndex, null, out curColumnIndex, out errorString);
if (errorString != null)
{
errorString = _GetTableAnalyzeErrorString(tableName, dt.TableName, nextColumnIndex) + errorString;
return(null);
}
else
{
// 跳过未声明变量名以及数据库导出信息的无效列
if (oneField != null)
{
// 检查字段名是否重复
if (clientFieldNames.ContainsKey(oneField.FieldName))
{
errorString = _GetTableAnalyzeErrorString(tableName, dt.TableName, nextColumnIndex) + string.Format("表格中存在字段名同为{0}的字段，分别位于第{1}列和第{2}列", oneField.FieldName, ExcelMethods.GetExcelColumnName(clientFieldNames[oneField.FieldName] + 1), ExcelMethods.GetExcelColumnName(oneField.ColumnSeq + 1));
return(null);
}
else
{
tableInfo.AddField(oneField);
clientFieldNames.Add(oneField.FieldName, oneField.ColumnSeq);
}
// 检查字段名是否重复
//if (serverFieldNames.ContainsKey(oneField.DatabaseFieldName))
//{
// errorString = _GetTableAnalyzeErrorString(tableName, dt.TableName, nextColumnIndex) + string.Format("表格中存在字段名同为{0}的字段，分别位于第{1}列和第{2}列", oneField.DatabaseFieldName, ExcelMethods.GetExcelColumnName(serverFieldNames[oneField.DatabaseFieldName] + 1), ExcelMethods.GetExcelColumnName(oneField.ColumnSeq + 1));
// return null;
//}
//else
// {
// tableInfo.AddField(oneField);
// serverFieldNames.Add(oneField.DatabaseFieldName, oneField.ColumnSeq);
// }
}
}
}
errorString = null;
return(tableInfo);
}
public static bool ExportTableToDatabase(TableInfo tableInfo, string tableName, out string errorString)
{
AppLog.Log(string.Format("导入MySQL数据库:{0} \"{1}\"：", MySQLStruct.DataBase, tableInfo.TableName), ConsoleColor.Green);
//if (tableInfo.TableConfig != null && tableInfo.TableConfig.ContainsKey(MySQLStruct.CONFIG_NAME_EXPORT_DATABASE_TABLE_NAME))
//{
//List<string> inputParams = tableInfo.TableConfig[MySQLStruct.CONFIG_NAME_EXPORT_DATABASE_TABLE_NAME];
//if (inputParams == null || inputParams.Count < 1 || string.IsNullOrEmpty(inputParams[0]))
//{
// // AppLog.LogWarning("警告：未在表格配置中声明该表导出到数据库中的表名，此表将不被导出到数据库，请确认是否真要如此");
// errorString = null;
// return true;
//}
//string tableName = inputParams[0];
// 检查数据库中是否已经存在同名表格，若存在删除旧表
if (_existTableNames.Contains(tableName))
{
_DropTable(tableName, out errorString);
if (!string.IsNullOrEmpty(errorString))
{
errorString = string.Format("数据库中存在同名表格，但删除旧表失败，{0}", errorString);
return(false);
}
}
// 警告未设置导出到数据库信息的字段，这些字段将不被导出
const string WARNING_INFO_FORMAT = "第{0}列（字段名为{1}）";
List <string> warningInfo = new List <string>();
foreach (FieldInfo fieldInfo in tableInfo.GetAllFieldInfo())
{
if (fieldInfo.DataType != DataType.Array && fieldInfo.DataType != DataType.Dict && fieldInfo.DatabaseFieldName == null)
{
warningInfo.Add(string.Format(WARNING_INFO_FORMAT, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), fieldInfo.FieldName));
}
}
if (warningInfo.Count > 0)
{
//Utils.LogWarning("警告：以下字段未设置导出数据库的信息，将被忽略：");
// Utils.LogWarning(Utils.CombineString(warningInfo, " ,"));
}
// 按Excel表格中字段定义新建数据库表格
string comment = tableInfo.TableConfigData2 != null && tableInfo.TableConfigData2.ContainsKey(MySQLStruct.CONFIG_NAME_EXPORT_DATABASE_TABLE_COMMENT) && tableInfo.TableConfigData2[MySQLStruct.CONFIG_NAME_EXPORT_DATABASE_TABLE_COMMENT].Count > 0 ? tableInfo.TableConfigData2[MySQLStruct.CONFIG_NAME_EXPORT_DATABASE_TABLE_COMMENT][0] : string.Empty;
_CreateTable(tableName, tableInfo, comment, out errorString);
if (string.IsNullOrEmpty(errorString))
{
// 将Excel表格中的数据添加至数据库
_InsertData(tableName, tableInfo, out errorString);
if (string.IsNullOrEmpty(errorString))
{
AppLog.Log("成功");
errorString = null;
return(true);
}
else
{
errorString = string.Format("插入数据失败，{0}", errorString);
return(false);
}
}
else
{
errorString = string.Format("创建表格失败，{0}", errorString);
return(false);
}
//}
//else
//{
// AppLog.LogWarning("警告：未在表格配置中声明该表导出到数据库中的表名，此表将不被导出到数据库，请确认是否真要如此");
// errorString = null;
// return true;
//}
}
private static bool _AnalyzeDictType(FieldInfo fieldInfo, TableInfo tableInfo, DataTable dt, int columnIndex, FieldInfo parentField, out int nextFieldColumnIndex, out string errorString)
{
// dict或array集合类型中，如果定义列中的值填-1代表这行数据的该字段不生效，Data中用true和false代表该集合字段的某行数据是否生效
fieldInfo.Data = _GetValidInfoForSetData(dt, columnIndex, fieldInfo.TableName);
// 为了在多重嵌套的集合结构中快速判断出是不是在任意上层集合中已经被标为无效，这里只要在上层标为无效，在其所有下层都会进行进行标记，从而在判断数据是否有效时只要判断向上一层是否标记为无效即可
if (parentField != null)
{
int dataCount = Math.Min(fieldInfo.Data.Count, parentField.Data.Count);
for (int i = 0; i < dataCount; ++i)
{
if ((bool)parentField.Data[i] == false)
{
fieldInfo.Data[i] = false;
}
}
}
// 记录dict中子元素的字段名，dict中不允许同名字段
List <string> inputFieldNames = new List <string>();
// 解析dict声明的子元素个数
int childCount;
_GetDictChildCount(fieldInfo.DataTypeString, out childCount, out errorString);
if (errorString != null)
{
nextFieldColumnIndex = columnIndex + 1;
return(false);
}
// 解析之后的几列作为array的下属元素
fieldInfo.ChildField = new List <FieldInfo>();
fieldInfo.ChildFieldString = new List <string>();
nextFieldColumnIndex = columnIndex + 1;
int tempCount = childCount;
while (tempCount > 0)
{
int nextColumnIndex = nextFieldColumnIndex;
FieldInfo childFieldInfo = _AnalyzeOneField(dt, tableInfo, nextColumnIndex, fieldInfo, out nextFieldColumnIndex, out errorString);
if (errorString != null)
{
errorString = string.Format("dict类型数据下属元素（列号：{0}）的解析存在错误\n{1}", ExcelMethods.GetExcelColumnName(nextColumnIndex + 1), errorString);
return(false);
}
else
{
// 忽略无效列
if (childFieldInfo != null)
{
// 检查dict子元素中是否已经存在同名的字段
if (inputFieldNames.Contains(childFieldInfo.FieldName))
{
errorString = string.Format("dict类型的子元素中不允许含有同名字段（{0}）\n", childFieldInfo.FieldName);
return(false);
}
else
{
inputFieldNames.Add(childFieldInfo.FieldName);
}
fieldInfo.ChildField.Add(childFieldInfo);
fieldInfo.ChildFieldString.Add(childFieldInfo.FieldName);
--tempCount;
}
}
}
errorString = null;
return(true);
}
private static bool _CreateTable(string tableName, TableInfo tableInfo, string comment, Export export, out string errorString)
{
// 将主键列作为key生成
FieldInfo keyColumnField = tableInfo.GetKeyColumnFieldInfo();
if (keyColumnField.DatabaseFieldName == null)
{
AppLog.Log("主键未设置，已忽略导入数据库！", ConsoleColor.Yellow);
errorString = null;
return(true);
}
// 生成在创建数据表时所有字段的声明
StringBuilder fieldDefineStringBuilder = new StringBuilder();
foreach (FieldInfo fieldInfo in GetAllDatabaseFieldInfo(tableInfo))
{
// 在这里并不对每种本工具的数据类型是否能导出为指定的MySQL数据类型进行检查（比如本工具中的string型应该导出为MySQL中的文本类型如varchar，而不应该导出为数值类型）
if (fieldInfo.DataType == DataType.Date)
{
//string toDatabaseFormatDefine = fieldInfo.ExtraParam[DateToExportFormatKey].ToString();
string toDatabaseFormatDefine = export.DateToExportFormat;
DateFormatType toDatabaseFormatType = DateTimeValue.GetDateFormatType(toDatabaseFormatDefine);
if (fieldInfo.DatabaseFieldType.StartsWith("time", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("date型字段\"{0}\"（列号：{1}）声明导出到MySQL中的数据类型错误，不允许为time型，如果仅需要时分秒部分，请在Excel中将该字段在本工具中的数据类型改为time型", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1));
return(false);
}
if (toDatabaseFormatType == DateFormatType.ReferenceDateSec || toDatabaseFormatType == DateFormatType.ReferenceDateMsec)
{
if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase) || fieldInfo.DatabaseFieldType.Equals("date", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("date型字段\"{0}\"（列号：{1}）声明导出到MySQL中的形式为距1970年的时间（{2}），但所填写的导出到MySQL中的格式为时间型的{3}，请声明为MySQL中的数值型", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), toDatabaseFormatDefine, fieldInfo.DatabaseFieldType);
return(false);
}
}
}
else if (fieldInfo.DataType == DataType.Time)
{
//string toDatabaseFormatDefine = fieldInfo.ExtraParam[TimeToExportFormatKey].ToString();
string toDatabaseFormatDefine = export.TimeToExportFormat;
TimeFormatType toDatabaseFormatType = DateTimeValue.GetTimeFormatType(toDatabaseFormatDefine);
if (fieldInfo.DatabaseFieldType.StartsWith("datetime", StringComparison.CurrentCultureIgnoreCase) || fieldInfo.DatabaseFieldType.Equals("date", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("time型字段\"{0}\"（列号：{1}）声明导出到MySQL中的数据类型错误，不允许为datetime或date型，如果需要年月日部分，请在Excel中将该字段在本工具中的数据类型改为date型", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1));
return(false);
}
if (toDatabaseFormatType == TimeFormatType.ReferenceTimeSec && fieldInfo.DatabaseFieldType.StartsWith("time", StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("time型字段\"{0}\"（列号：{1}）声明导出到MySQL中的形式为距0点的秒数（#sec），但所填写的导出到MySQL中的格式为时间型的time，请声明为MySQL中的数值型", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1));
return(false);
}
}
fieldDefineStringBuilder.AppendFormat("`{0}` {1} COMMENT '{2}',", fieldInfo.DatabaseFieldName, fieldInfo.DatabaseFieldType, fieldInfo.Desc);
}
string createTableExtraParam = AppValues.ConfigData.ContainsKey(createDatabaseTableExtraParam) ? AppValues.ConfigData[createDatabaseTableExtraParam] : string.Empty;
string createTableSql = string.Format(createTableSQL, _CombineDatabaseTableFullName(tableName), fieldDefineStringBuilder.ToString(), tableInfo.GetKeyColumnFieldInfo().DatabaseFieldName, comment, createTableExtraParam);
try
{
MySqlCommand cmd = new MySqlCommand(createTableSql, conn);
cmd.ExecuteNonQuery();
errorString = null;
return(true);
}
catch (MySqlException exception)
{
errorString = exception.Message;
return(false);
}
}
/// <summary>
/// 获取dict或array数据类型中，每一行数据的有效性（填-1表示本行的这个数据无效）
/// </summary>
private static List <object> _GetValidInfoForSetData(DataTable dt, int columnIndex, string tableName)
{
List <object> validInfo = new List <object>();
for (int row = ExcelTableSetting.DataFieldDataStartRowIndex; row < dt.Rows.Count; ++row)
{
string inputData = dt.Rows[row][columnIndex].ToString().Trim();
if ("-1".Equals(inputData))
{
validInfo.Add(false);
}
else if (string.IsNullOrEmpty(inputData))
{
validInfo.Add(true);
}
else
{
AppLog.LogWarning(string.Format("警告：表格{0}的第{1}列的第{2}行数据有误，array或dict类型中若某行不需要此数据请填-1，否则留空，你填写的为\"{3}\"，本工具按有效值进行处理，但请按规范更正", tableName, ExcelMethods.GetExcelColumnName(columnIndex + 1), row, inputData));
validInfo.Add(true);
}
}
return(validInfo);
}
public static bool ExportTableToErlang(TableInfo tableInfo, out string errorString)
{
StringBuilder content = new StringBuilder();
// 当前缩进量
int currentLevel = 1;
// 判断是否设置要将主键列的值作为导出的table中的元素
bool isAddKeyToLuaTable = tableInfo.TableConfigData2 != null && tableInfo.TableConfigData2.ContainsKey(ErlangStruct.Excel_Config_AddKeyToErlangTable) && tableInfo.TableConfigData2[ErlangStruct.Excel_Config_AddKeyToErlangTable].Count > 0 && "true".Equals(tableInfo.TableConfigData2[ErlangStruct.Excel_Config_AddKeyToErlangTable][0], StringComparison.CurrentCultureIgnoreCase);
//if(tableInfo.ExcelName== "ad_watch_videoType_info-视频类型信息")
//{
// errorString = "该表没有字段";
//}
// 逐行读取表格内容生成erlang table
List <FieldInfo> allField = tableInfo.GetAllFieldInfo();//获取所有字段，第2行没有定义也获取
int dataCount = tableInfo.GetKeyColumnFieldInfo().Data.Count;
for (int row = 0; row < dataCount; ++row)
{
// 将主键列作为key生成
FieldInfo keyColumnField = allField[0];
if (keyColumnField.DatabaseFieldName == null)
{
errorString = null;
return(true);
}
if (keyColumnField.DataType == DataType.Int || keyColumnField.DataType == DataType.Long)
{
content.Append("get(").Append(keyColumnField.Data[row]).Append(")->");
}
// 注意：像“1_2”这样的字符串作为table的key必须加[""]否则lua认为是语法错误
else if (keyColumnField.DataType == DataType.String)
{
string FieldString = keyColumnField.Data[row].ToString();// _GetOneField(keyColumnField, row, currentLevel, out errorString);
content.Append("get(").Append(FieldString.ToLower()).Append(")->");
}
else
{
errorString = "用ExportTableToErlang导出不支持的主键列数据类型";
AppLog.LogErrorAndExit(errorString);
return(false);
}
// content.Append(_GetErlangIndentation(currentLevel));
content.AppendLine(" #{");
// ++currentLevel;
// 如果设置了要将主键列的值作为导出的table中的元素
//if (isAddKeyToLuaTable == true)
//{
// content.Append(_GetErlangIndentation(currentLevel));
// content.Append(keyColumnField.FieldName);
// content.Append(" = ");
// if (keyColumnField.DataType == DataType.Int || keyColumnField.DataType == DataType.Long)
// content.Append(keyColumnField.Data[row]);
// else if (keyColumnField.DataType == DataType.String)
// content.AppendFormat("\"{0}\"", keyColumnField.Data[row]);
// content.AppendLine(",");
//}
// 将其他列依次作为value生成
int i = 0;
for (int column = 1; column < allField.Count; ++column)
{
string oneFieldString = _GetOneField(allField[column], row, currentLevel, out errorString);
if (oneFieldString == null)
{
continue;
}
else
{
i++;
}
// if (i > 1)
// {
if (ErlangStruct.ExportErlangIsFormat == true)
{
content.Append(_GetErlangIndentation(currentLevel));
}
// }
if (errorString != null)
{
errorString = string.Format("导出表格{0}失败，", tableInfo.TableName) + errorString;
return(false);
}
else
{
if (i > 1)
{
content.Append(",");
}
if (ErlangStruct.ExportErlangIsFormat == true)
{
content.Append("'").Append(allField[column].DatabaseFieldName.ToLower()).Append("' => ").AppendLine(oneFieldString);
}
else
{
content.Append("'").Append(allField[column].DatabaseFieldName.ToLower()).Append("' => ").Append(oneFieldString);
}
//content.AppendLine(",");
}
}
// 一行数据生成完毕后添加右括号结尾等
// --currentLevel;
// content.Append(_GetErlangIndentation(currentLevel));
//content.Remove(content.Length - 3, 1);
content.AppendLine("};");
}
string exportString2 = content.ToString();
//if (ErlangStruct.ExportErlangIsFormat == false)
//{
// StringBuilder stringBuilder2 = new StringBuilder();
// for (int i = 0; i < exportString2.Length; ++i)
// {
// char c = exportString2[i];
// if (c == '\n' || c == '\r')
// {
// }
// else
// stringBuilder2.Append(c);
// }
// exportString2 = stringBuilder2.ToString();
//}
StringBuilder stringBuilder = new StringBuilder();
// 生成数据内容开头
string erlangTableName = tableInfo.TableName;
TableAnalyzeHelper.GetOneConfigData(tableInfo, ErlangStruct.Excel_Config_ExportErlangOtherName, ref erlangTableName);
stringBuilder.AppendLine("%%--- coding:utf-8 ---");
stringBuilder.Append("-module(").Append(ErlangStruct.ExportNameBeforeAdd + erlangTableName).AppendLine(").");
stringBuilder.AppendLine(@"-export([get/1,get_list/0]).");
stringBuilder.Append(exportString2);
// 生成数据内容结尾
stringBuilder.AppendLine("get(_N) -> false.");
stringBuilder.AppendLine("get_list() ->");
stringBuilder.Append("\t[");
for (int i = 0; i < dataCount; i++)
{
string FieldString = allField[0].Data[i].ToString();// _GetOneField(allField[0], i, currentLevel, out errorString);
stringBuilder.Append(FieldString.ToLower()).Append(",");
}
stringBuilder.Remove(stringBuilder.Length - 1, 1);
stringBuilder.AppendLine("].");
string exportString = stringBuilder.ToString();
//if (ErlangStruct.IsNeedColumnInfo == true)
// exportString = _GetColumnInfo(tableInfo) + exportString;
// 保存为erlang文件
if (SaveErlang.SaveErlangFile(tableInfo.ExcelName, ExcelMethods.GetSaveTableName(erlangTableName), exportString) == true)
{
errorString = null;
return(true);
}
else
{
errorString = "保存为erlang文件失败\n";
return(false);
}
}
/// <summary>
/// 当表格存在错误无法继续时，输出内容前统一加上表格名和列名
/// </summary>
private static string _GetTableAnalyzeErrorString(string tableName, string sheetName, int columnIndex)
{
return(string.Format("表格{0}-{1}中列号为{2}的字段存在以下严重错误，导致无法继续，请修正错误后重试\n", tableName, sheetName, ExcelMethods.GetExcelColumnName(columnIndex + 1)));
}
public static bool ExportTableToLua(TableInfo tableInfo, out string errorString)
{
StringBuilder content = new StringBuilder();
// 生成数据内容开头
if (LuaStruct.IsTableNameStart)
{
content.Append(tableInfo.TableName).AppendLine(" = {");
}
else
{
content.AppendLine("return {");
}
// 当前缩进量
int currentLevel = 1;
// 判断是否设置要将主键列的值作为导出的table中的元素
bool isAddKeyToLuaTable = tableInfo.TableConfigData2 != null && tableInfo.TableConfigData2.ContainsKey(LuaStruct.Excel_Config_AddKeyToLuaTable) && tableInfo.TableConfigData2[LuaStruct.Excel_Config_AddKeyToLuaTable].Count > 0 && "true".Equals(tableInfo.TableConfigData2[LuaStruct.Excel_Config_AddKeyToLuaTable][0], StringComparison.CurrentCultureIgnoreCase);
// 逐行读取表格内容生成lua table
List <FieldInfo> allField = tableInfo.GetAllClientFieldInfo();
int dataCount = tableInfo.GetKeyColumnFieldInfo().Data.Count;
for (int row = 0; row < dataCount; ++row)
{
// 将主键列作为key生成
content.Append(_GetLuaIndentation(currentLevel));
FieldInfo keyColumnField = allField[0];
if (keyColumnField.DataType == DataType.Int || keyColumnField.DataType == DataType.Long)
{
content.AppendFormat("[{0}]", keyColumnField.Data[row]);
}
// 注意：像“1_2”这样的字符串作为table的key必须加[""]否则lua认为是语法错误
else if (keyColumnField.DataType == DataType.String)
{
content.AppendFormat("[\"{0}\"]", keyColumnField.Data[row]);
}
else
{
errorString = "用ExportTableToLua导出不支持的主键列数据类型";
AppLog.LogErrorAndExit(errorString);
return(false);
}
content.AppendLine(" = {");
++currentLevel;
// 如果设置了要将主键列的值作为导出的table中的元素
if (isAddKeyToLuaTable == true)
{
content.Append(_GetLuaIndentation(currentLevel));
content.Append(keyColumnField.FieldName);
content.Append(" = ");
if (keyColumnField.DataType == DataType.Int || keyColumnField.DataType == DataType.Long)
{
content.Append(keyColumnField.Data[row]);
}
else if (keyColumnField.DataType == DataType.String)
{
content.AppendFormat("\"{0}\"", keyColumnField.Data[row]);
}
content.AppendLine(",");
}
// 将其他列依次作为value生成
for (int column = 1; column < allField.Count; ++column)
{
string oneFieldString = _GetOneField(allField[column], row, currentLevel, out errorString);
if (errorString != null)
{
errorString = string.Format("导出表格{0}失败，", tableInfo.TableName) + errorString;
return(false);
}
else
{
content.Append(oneFieldString);
}
}
// 一行数据生成完毕后添加右括号结尾等
--currentLevel;
content.Append(_GetLuaIndentation(currentLevel));
content.AppendLine("},");
}
// 生成数据内容结尾
content.AppendLine("}");
if (LuaStruct.IsTableNameStart)
{
content.Append("return ").Append(tableInfo.TableName);
}
string exportString = content.ToString();
if (LuaStruct.ExportLuaIsFormat == false)
{
StringBuilder stringBuilder2 = new StringBuilder();
for (int i = 0; i < exportString.Length; ++i)
{
char c = exportString[i];
if (c == '\n' || c == '\r' || c.ToString() == " ")
{
}
else
{
stringBuilder2.Append(c);
}
}
exportString = stringBuilder2.ToString();
}
if (LuaStruct.IsNeedColumnInfo == true)
{
exportString = _GetColumnInfo(tableInfo) + exportString;
}
// 保存为lua文件
if (SaveLua.SaveLuaFile(tableInfo.ExcelName, ExcelMethods.GetSaveTableName(tableInfo.TableName), exportString) == true)
{
errorString = null;
return(true);
}
else
{
errorString = "保存为lua文件失败\n";
return(false);
}
}
private static bool _AnalyzeArrayType(FieldInfo fieldInfo, TableInfo tableInfo, DataTable dt, int columnIndex, FieldInfo parentField, out int nextFieldColumnIndex, out string errorString)
{
// dict或array集合类型中，如果定义列中的值填-1代表这行数据的该字段不生效，Data中用true和false代表该集合字段的某行数据是否生效
fieldInfo.Data = _GetValidInfoForSetData(dt, columnIndex, fieldInfo.TableName);
// 为了在多重嵌套的集合结构中快速判断出是不是在任意上层集合中已经被标为无效，这里只要在上层标为无效，在其所有下层都会进行进行标记，从而在判断数据是否有效时只要判断向上一层是否标记为无效即可
if (parentField != null)
{
int dataCount = Math.Min(fieldInfo.Data.Count, parentField.Data.Count);
for (int i = 0; i < dataCount; ++i)
{
if ((bool)parentField.Data[i] == false)
{
fieldInfo.Data[i] = false;
}
}
}
// 解析array声明的子元素的数据类型和个数
string childDataTypeString;
DataType childDataType;
int childCount;
_GetArrayChildDefine(fieldInfo.DataTypeString, out childDataTypeString, out childDataType, out childCount, out errorString);
if (errorString != null)
{
nextFieldColumnIndex = columnIndex + 1;
return(false);
}
// 存储子类型的类型以及类型定义字符串
fieldInfo.ArrayChildDataTypeString = childDataTypeString;
fieldInfo.ArrayChildDataType = childDataType;
// 解析之后的几列作为array的下属元素
fieldInfo.ChildField = new List <FieldInfo>();
fieldInfo.ChildFieldString = new List <string>();
nextFieldColumnIndex = columnIndex + 1;
int tempCount = childCount;
int seq = 1;
while (tempCount > 0)
{
int nextColumnIndex = nextFieldColumnIndex;
FieldInfo childFieldInfo = _AnalyzeOneField(dt, tableInfo, nextColumnIndex, fieldInfo, out nextFieldColumnIndex, out errorString);
if (errorString != null)
{
errorString = string.Format("array类型数据下属元素（列号：{0}）的解析存在错误\n{1}", ExcelMethods.GetExcelColumnName(nextColumnIndex + 1), errorString);
return(false);
}
else
{
// 忽略无效列
if (childFieldInfo != null)
{
// 将array下属子元素的fieldName改为顺序编号
childFieldInfo.FieldName = string.Format("[{0}]", seq);
++seq;
fieldInfo.ChildField.Add(childFieldInfo);
fieldInfo.ChildFieldString.Add(childFieldInfo.FieldName);
--tempCount;
}
}
}
// 如果array的子元素为array或dict类型，当前面的子元素用-1标识为无效后，后面的数据也必须声明为无效的，比如用array[dict[3]:5]表示一场推图战斗胜利最多可获得的5种奖励物，如果某一行对应的关卡至多只有3种奖励物，则必须填在前3个子元素列中，后面2个声明为无效
if (fieldInfo.ArrayChildDataType == DataType.Array || fieldInfo.ArrayChildDataType == DataType.Dict)
{
for (int i = 0; i < fieldInfo.Data.Count; ++i)
{
// 如果本行数据中array下属的集合型子元素已经读取到-1标识的无效数据，记录其是第几个子元素
int invalidDataIndex = 0;
for (int j = 0; j < fieldInfo.ChildField.Count; ++j)
{
if ((bool)fieldInfo.ChildField[j].Data[i] == true)
{
if (invalidDataIndex != 0)
{
errorString = string.Format("array的子元素为array或dict类型时，当前面的子元素用-1标识为无效后，后面的数据也必须声明为无效的。而第{0}行第{1}个子元素声明为无效，而后面第{2}个子元素却有效\n", i + ExcelTableSetting.DataFieldDataStartRowIndex + 1, invalidDataIndex, j + 1);
return(false);
}
}
else
{
invalidDataIndex = j + 1;
}
}
}
}
errorString = null;
return(true);
}
/// <summary>
/// 按配置的特殊索引导出方式输出json文件（如果声明了在生成的json文件开头以注释形式展示列信息，将生成更直观的嵌套字段信息，而不同于普通导出规则的列信息展示）
/// </summary>
public static bool SpecialExportTableToJson(TableInfo tableInfo, string exportRule, out string errorString)
{
exportRule = exportRule.Trim();
// 解析按这种方式导出后的json文件名
int colonIndex = exportRule.IndexOf(':');
// 解析table value中要输出的字段名
List <FieldInfo> tableValueField = new List <FieldInfo>();
// 解析完依次作为索引的字段以及table value中包含的字段后，按索引要求组成相应的嵌套数据结构
Dictionary <object, object> data = new Dictionary <object, object>();
//自定义导出规则检查
TableCheckHelper.CheckSpecialExportRule(tableInfo, exportRule, out tableValueField, out data, out errorString);
if (errorString != null)
{
errorString = string.Format("错误：对表格{0}按\"{1}\"规则进行特殊索引导出时发现以下错误，导出被迫停止，请修正错误后重试：\n{2}\n", tableInfo.TableName, exportRule, errorString);
return(false);
}
// 生成导出的文件内容
StringBuilder content = new StringBuilder();
// 生成数据内容开头
content.Append("{");//content.AppendLine("{");
// 当前缩进量
int currentLevel = 1;
// 逐层按嵌套结构输出数据
_GetIndexFieldData(content, data, tableValueField, ref currentLevel, out errorString);
if (errorString != null)
{
errorString = string.Format("错误：对表格{0}按\"{1}\"规则进行特殊索引导出时发现以下错误，导出被迫停止，请修正错误后重试：\n{2}\n", tableInfo.TableName, exportRule, errorString);
return(false);
}
// 去掉最后一个子元素后多余的英文逗号
content.Remove(content.Length - 1, 1);
// 生成数据内容结尾
content.AppendLine("}");
string exportString = content.ToString();
// 如果声明了要整理为带缩进格式的形式
if (JsonStruct.ExportJsonIsFormat == true)
{
exportString = _FormatJson2(exportString);
// exportString = JsonConvert.SerializeObject(exportString);
}
// 保存为json文件
string fileName = exportRule.Substring(0, colonIndex).Trim();
if (SaveJson.SaveJsonFile(tableInfo.ExcelName, ExcelMethods.GetSaveTableName(fileName), exportString) == true)
{
errorString = null;
try
{
LitJson.JsonData jsonData = LitJson.JsonMapper.ToObject(exportString);
}
catch (LitJson.JsonException exception)
{
errorString = "错误：导出json出现异常，请检查导出的json及Excel\n";
}
return(true);
}
else
{
errorString = "保存为json文件失败\n";
return(false);
}
}
/// <summary>
/// 将指定Excel文件的内容读取到DataSet中
/// </summary>
public static DataSet ReadXlsxFileForExcelDataReader(string filePath, string excelName, ref string tableName, out string errorString)
{
DataSet ds = null;
errorString = null;
var file = new FileInfo(filePath);
using (var stream = new FileStream(filePath, FileMode.Open))
{
IExcelDataReader reader = null;
if (file.Extension == ".xls")
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (file.Extension == ".xlsx" || file.Extension == ".xlsm")
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (reader == null)
{
errorString = "只支持的.xls，.xlsx，.xlsm三种excel扩展类型，此文件的类型是" + file.Extension + "\n";
return(null);
}
ds = reader.AsDataSet();
tableName = ExcelMethods.GetTableName(excelName);
//List<string> excelSheetNames = new List<string>();
//excelSheetNames = ExcelMethods.GetExcelSheetName(tableName, dtSheet);
//if (excelSheetNames.Count == 1)
//{
// if (!excelSheetNames.Contains(ExcelTableSetting.ExcelDataSheetName))
// {
// errorString = string.Format("错误：{0}中不含有Sheet名为{1}或以{2}开头的数据表", filePath, ExcelTableSetting.ExcelDataSheetName.Replace("$", ""), tableName);
// return null;
// }
//}
//else if (excelSheetNames.Count == 0)
//{
// errorString = string.Format("错误：{0}中不含有Sheet名为{1}或以{2}开头的数据表", filePath, ExcelTableSetting.ExcelDataSheetName.Replace("$", ""), tableName);
// return null;
//}
var removeDataTableList = new List <DataTable>();
tableName = ExcelMethods.GetTableName(excelName);
foreach (DataTable da in ds.Tables)
{
//Utils.Log(string.Format("Table: {0}", da.TableName), ConsoleColor.Cyan);
if (da.TableName.Equals(ExcelTableSetting.ExcelDataSheetName.Replace("$", "")))
{
da.TableName = ExcelTableSetting.ExcelDataSheetName;
}
else if (da.TableName.Equals(ExcelTableSetting.ExcelConfigSheetName.Replace("$", "")))
{
da.TableName = ExcelTableSetting.ExcelConfigSheetName;
}
else if (da.TableName.StartsWith(tableName))
{
// da.TableName = da.TableName;
}
else
{
removeDataTableList.Add(da);
}
}
foreach (DataTable da in removeDataTableList)
{
ds.Tables.Remove(da);
}
//foreach (DataTable da in ds.Tables)
//{
// Utils.Log(string.Format("Table: {0}", da.TableName), ConsoleColor.Cyan);
//}
}
bool removeConfig = false;
foreach (DataTable da in ds.Tables)
{
DataRowCollection rows = da.Rows;
int rowCount = rows.Count;
if (da.TableName != ExcelTableSetting.ExcelConfigSheetName)
{
// 删除表格末尾的空行
for (int i = rowCount - 1; i >= ExcelTableSetting.DataFieldDataStartRowIndex; --i)
{
if (string.IsNullOrEmpty(rows[i][0].ToString()))
{
rows.RemoveAt(i);
}
else
{
break;
}
}
}
else
{
if (rowCount == 0)
{
removeConfig = true;
}
}
}
if (removeConfig == true)
{
ds.Tables.Remove(ds.Tables[ExcelTableSetting.ExcelConfigSheetName]);
}
//// 删除表格末尾的空行
//DataRowCollection rows = ds.Tables[ExcelTableSetting.ExcelDataSheetName].Rows;
//int rowCount = rows.Count;
//for (int i = rowCount - 1; i >= ExcelTableSetting.DataFieldDataStartRowIndex; --i)
//{
// if (string.IsNullOrEmpty(rows[i][0].ToString()))
// rows.RemoveAt(i);
// else
// break;
//}
return(ds);
}
/// <summary>
/// 用于检查mapString型当逻辑上某列对应不同的类型取值时，其数据按是否要求含有或不允许有某些元素
/// </summary>
public static bool CheckMapString(FieldInfo fieldInfo, FieldCheckRule checkRule, out string errorString)
{
if (fieldInfo.DataType != DataType.MapString)
{
errorString = string.Format("mapString型的内容检查只适用于mapString类型的字段，要检查的这列类型为{0}\n", fieldInfo.DataType.ToString());
return(false);
}
MapStringCheckRule mapStringCheckRule = new MapStringCheckRule();
// 解析检查规则
const string CHECK_RULE_START_STRING = "mapString:";
string checkRuleString = null;
if (checkRule.CheckRuleString.Equals(CHECK_RULE_START_STRING, StringComparison.CurrentCultureIgnoreCase))
{
errorString = "mapString型的内容检查规则声明中必须含有具体的检查规则\n";
return(false);
}
else if (!checkRule.CheckRuleString.StartsWith(CHECK_RULE_START_STRING, StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("mapString型的内容检查规则声明错误，必须以\"{0}\"开头\n", CHECK_RULE_START_STRING);
return(false);
}
else
{
checkRuleString = checkRule.CheckRuleString.Substring(CHECK_RULE_START_STRING.Length).Trim();
}
// 通过|分隔不同条件下的内容检查规则
string[] checkRuleList = checkRuleString.Split(new string[] { "|" }, StringSplitOptions.RemoveEmptyEntries);
// 解析每一种条件下对应的mapString内容
for (int i = 0; i < checkRuleList.Length; ++i)
{
// 条件解析
MapStringCondition mapStringCondition = new MapStringCondition();
string oneCheckRule = checkRuleList[i].Trim();
string tempCheckRule = oneCheckRule;
if (string.IsNullOrEmpty(oneCheckRule))
{
errorString = "mapString型的内容检查规则声明错误，不允许含有空的规则声明，请检查是否含有多余的|分隔符\n";
return(false);
}
const string IF_CONDITION_START_STRING = "if(";
if (oneCheckRule.Equals(IF_CONDITION_START_STRING, StringComparison.CurrentCultureIgnoreCase) || !oneCheckRule.StartsWith(IF_CONDITION_START_STRING, StringComparison.CurrentCultureIgnoreCase))
{
errorString = string.Format("mapString型的内容检查规则声明错误，必须在if后面的括号中声明其他字段需满足的条件。若无要求，请填写为\"if(all)\"，你填写的为{0}\n", oneCheckRule);
return(false);
}
tempCheckRule = tempCheckRule.Substring(IF_CONDITION_START_STRING.Length);
int rightBracket = tempCheckRule.IndexOf(')');
if (rightBracket == -1)
{
errorString = string.Format("mapString型的内容检查规则声明错误，if后面的右括号缺失，你填写的为{0}\n", oneCheckRule);
return(false);
}
if (rightBracket == tempCheckRule.Length - 1)
{
errorString = string.Format("mapString型的内容检查规则声明错误，在if后面的括号中声明其他字段需满足的条件之后，还需在方括号内声明对mapString型下属字段的要求\n", oneCheckRule);
return(false);
}
string ifConditionString = tempCheckRule.Substring(0, rightBracket).Trim();
if (string.IsNullOrEmpty(ifConditionString))
{
errorString = "mapString型的内容检查规则声明错误，if后的括号中为空，若要设置为在任何条件下，请填写为\"if(all)\"\n";
return(false);
}
else if ("all".Equals(ifConditionString, StringComparison.CurrentCultureIgnoreCase))
{
Condition condition = new Condition();
condition.FieldInfo = null;
mapStringCondition.ConditionList.Add(condition);
}
else
{
// 通过英文逗号分隔要同时满足的条件
string[] ifConditionStringList = ifConditionString.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
for (int j = 0; j < ifConditionStringList.Length; j++)
{
Condition condition = new Condition();
string oneKeyValuePairString = ifConditionStringList[j].Trim();
string fieldName = null;
StringBuilder fieldNameBuilder = new StringBuilder();
int charIndex = 0;
bool isFoundSignOfRelation = false;
for (charIndex = 0; charIndex < oneKeyValuePairString.Length; ++charIndex)
{
char c = oneKeyValuePairString[charIndex];
if (c == '=' || c == '>' || c == '<')
{
fieldName = fieldNameBuilder.ToString().Trim();
isFoundSignOfRelation = true;
break;
}
else
{
fieldNameBuilder.Append(c);
}
}
if (string.IsNullOrEmpty(fieldName))
{
errorString = string.Format("mapString型的内容检查规则声明错误，if后的括号中（{0}）未声明字段名，请按字段名、关系符、取值的形式进行声明，如type>1\n", oneKeyValuePairString);
return(false);
}
else if (isFoundSignOfRelation == false)
{
errorString = string.Format("mapString型的内容检查规则声明错误，if后的括号中（{0}）未声明关系符，请按字段名、关系符、取值的形式进行声明，如type>1\n", oneKeyValuePairString);
return(false);
}
// 检查字段是否存在
FieldInfo targetFieldInfo = TableCheckHelper.GetFieldByIndexDefineString(fieldName, AppValues.TableInfo[fieldInfo.TableName], out errorString);
if (errorString != null)
{
errorString = string.Format("mapString型的内容检查规则声明错误，无法根据索引字符串\"{0}\"在表格{1}找到要对应的字段，错误信息为：{2}\n", fieldName, fieldInfo.TableName, errorString);
return(false);
}
// 检查字段类型是否符合要求
if (targetFieldInfo.DataType != DataType.Int && targetFieldInfo.DataType != DataType.Long && targetFieldInfo.DataType != DataType.Float && targetFieldInfo.DataType != DataType.Bool && targetFieldInfo.DataType != DataType.String)
{
errorString = string.Format("mapString型的内容检查规则声明错误，条件字段（{0}）的数据类型为{1}，而mapString型内容检查规则中，if条件字段只能为int、long、float、bool或string型\n", fieldName, targetFieldInfo.DataType);
return(false);
}
condition.FieldInfo = targetFieldInfo;
// 解析填写的关系符
string signOfRelation = null;
StringBuilder signOfRelationBuilder = new StringBuilder();
if (charIndex == oneKeyValuePairString.Length)
{
errorString = string.Format("mapString型的内容检查规则声明错误，if后的括号中（{0}）未声明取值，请按字段名、关系符、取值的形式进行声明，如type>1\n", oneKeyValuePairString);
return(false);
}
for (; charIndex < oneKeyValuePairString.Length; ++charIndex)
{
char c = oneKeyValuePairString[charIndex];
if (c == '=' || c == '>' || c == '<')
{
signOfRelationBuilder.Append(c);
}
else
{
break;
}
}
signOfRelation = signOfRelationBuilder.ToString();
if (signOfRelation == "=")
{
condition.Relation = Relation.Equal;
}
else if (signOfRelation == ">")
{
condition.Relation = Relation.GreaterThan;
}
else if (signOfRelation == ">=")
{
condition.Relation = Relation.GreaterThanOrEqual;
}
else if (signOfRelation == "<")
{
condition.Relation = Relation.LessThan;
}
else if (signOfRelation == "<=")
{
condition.Relation = Relation.LessThanOrEqual;
}
else
{
errorString = string.Format("mapString型的内容检查规则声明错误，关系符非法，只支持=、>、>=、<、<=，你填写的为{0}\n", oneKeyValuePairString);
return(false);
}
// bool、string型只有关系符=
if (condition.Relation != Relation.Equal && (targetFieldInfo.DataType == DataType.Bool || targetFieldInfo.DataType == DataType.String))
{
errorString = string.Format("mapString型的内容检查规则声明错误，条件字段（{0}）为{1}类型，只能进行等于判定，而你设置的关系符为{2}\n", fieldName, targetFieldInfo.DataType, condition.Relation);
return(false);
}
// 解析填写的取值
string valueString = oneKeyValuePairString.Substring(charIndex).Trim();
if (targetFieldInfo.DataType == DataType.Int)
{
int value = 0;
if (int.TryParse(valueString, out value) == false)
{
errorString = string.Format("mapString型的内容检查规则声明错误，条件字段（{0}）对应int型的取值（{1}）非法\n", fieldName, valueString);
return(false);
}
else
{
condition.Value = value;
}
}
else if (targetFieldInfo.DataType == DataType.Long)
{
long value = 0;
if (long.TryParse(valueString, out value) == false)
{
errorString = string.Format("mapString型的内容检查规则声明错误，条件字段（{0}）对应long型的取值（{1}）非法\n", fieldName, valueString);
return(false);
}
else
{
condition.Value = value;
}
}
else if (targetFieldInfo.DataType == DataType.Float)
{
double value = 0;
if (double.TryParse(valueString, out value) == false)
{
errorString = string.Format("mapString型的内容检查规则声明错误，条件字段（{0}）对应float型的取值（{1}）非法\n", fieldName, valueString);
return(false);
}
else
{
condition.Value = value;
}
}
else if (targetFieldInfo.DataType == DataType.Bool)
{
if ("1".Equals(valueString) || "true".Equals(valueString, StringComparison.CurrentCultureIgnoreCase))
{
condition.Value = true;
}
else if ("0".Equals(valueString) || "false".Equals(valueString, StringComparison.CurrentCultureIgnoreCase))
{
condition.Value = true;
}
else
{
errorString = string.Format("mapString型的内容检查规则声明错误，条件字段（{0}）对应bool型的取值（{1}）非法，bool型的值应用数字1、0或true、false进行声明\n", fieldName, valueString);
return(false);
}
}
else if (targetFieldInfo.DataType == DataType.String)
{
condition.Value = valueString;
}
else
{
errorString = "用CheckMapString函数处理非法的mapString型检查规则中定义的条件字段类型";
AppLog.LogErrorAndExit(errorString);
return(false);
}
mapStringCondition.ConditionList.Add(condition);
}
}
// mapString型下属字段要求解析
string mapStringRequiredString = tempCheckRule.Substring(rightBracket + 1).Trim();
if (!mapStringRequiredString.StartsWith("[") || !mapStringRequiredString.EndsWith("]"))
{
errorString = string.Format("mapString型的内容检查规则声明错误，每条检查规则中的字段要求声明必须在if条件声明后，在方括号内声明，你填写的为{0}\n", mapStringRequiredString);
return(false);
}
mapStringRequiredString = mapStringRequiredString.Substring(1, mapStringRequiredString.Length - 2).Trim();
MapStringRequiredInfo mapStringRequiredInfo = _GetMapStringRequiredInfo(mapStringRequiredString, fieldInfo, out errorString);
if (errorString != null)
{
errorString = string.Format("mapString型的内容检查规则声明错误，输入的字段要求错误，你填写的为\"{0}\"，错误原因为：{1}\n", mapStringRequiredString, errorString);
return(false);
}
else
{
mapStringCheckRule.CheckRuleList.Add(mapStringCondition, mapStringRequiredInfo);
}
}
// 按解析出的检查规则对mapString型进行检查
StringBuilder errorStringBuilder = new StringBuilder();
int ruleIndex = 0;
foreach (var item in mapStringCheckRule.CheckRuleList)
{
MapStringCondition condition = item.Key;
MapStringRequiredInfo requiredInfo = item.Value;
StringBuilder oneConditionStringBuilder = new StringBuilder();
// 先找出其他列满足检查条件的行
// 记录符合检查条件的数据索引值（从0计），这里先将所有数据行加入，然后逐步排除不满足条件的数据行
List <int> targetDataIndex = new List <int>();
int dataCount = fieldInfo.Data.Count;
for (int i = 0; i < dataCount; ++i)
{
targetDataIndex.Add(i);
}
List <int> emptyConditionRowIndex = new List <int>();
for (int conditionIndex = 0; conditionIndex < condition.ConditionList.Count; ++conditionIndex)
{
Condition oneCondition = condition.ConditionList[conditionIndex];
// 排除标为all的条件
if (oneCondition.FieldInfo != null)
{
List <int> tempEmptyConditionRowIndex = null;
_GetTargetDataIndex(oneCondition, targetDataIndex, oneCondition.FieldInfo, out tempEmptyConditionRowIndex);
if (tempEmptyConditionRowIndex != null && tempEmptyConditionRowIndex.Count > 0)
{
foreach (int rowIndex in tempEmptyConditionRowIndex)
{
if (!emptyConditionRowIndex.Contains(rowIndex))
{
emptyConditionRowIndex.Add(rowIndex);
}
}
}
}
}
if (emptyConditionRowIndex.Count > 0)
{
string warningString = string.Format("警告：mapString型字段\"{0}\"（列号：{1}）的检查条件（{2}）中的字段，因为以下行中数据无效，视为不满足条件，不对对应行中的mapString进行检查：{3}\n", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), checkRuleList[ruleIndex], Utils.CombineString(emptyConditionRowIndex, ","));
AppLog.LogWarning(warningString);
}
// 对满足检查条件的数据行进行mapString内容检查
List <int> emptyDataRowIndex = new List <int>();
foreach (int index in targetDataIndex)
{
// 若为空值，跳过检查，但进行警告
if (fieldInfo.Data[index] == null)
{
emptyDataRowIndex.Add(index + ExcelTableSetting.DataFieldDataStartRowIndex + 1);
continue;
}
JsonData jsonData = fieldInfo.Data[index] as JsonData;
if (_CheckMapStringData(jsonData, requiredInfo, out errorString) == false)
{
oneConditionStringBuilder.AppendFormat("第{0}行填写的数据（{1}）：\n{2}\n", index + ExcelTableSetting.DataFieldDataStartRowIndex + 1, fieldInfo.JsonString[index], errorString);
}
}
string oneConditionString = oneConditionStringBuilder.ToString();
if (!string.IsNullOrEmpty(oneConditionString))
{
errorStringBuilder.AppendFormat("以下行数据未通过检查规则\"{0}\"：\n{1}", checkRuleList[ruleIndex], oneConditionString);
}
if (emptyDataRowIndex.Count > 0)
{
string warningString = string.Format("警告：在对mapString型字段\"{0}\"（列号：{1}）执行检查条件（{2}）时，因为以下行中数据无效，跳过对mapString的检查：{3}\n", fieldInfo.FieldName, ExcelMethods.GetExcelColumnName(fieldInfo.ColumnSeq + 1), checkRuleList[ruleIndex], Utils.CombineString(emptyDataRowIndex, ","));
AppLog.LogWarning(warningString);
}
++ruleIndex;
}
errorString = errorStringBuilder.ToString();
if (string.IsNullOrEmpty(errorString))
{
errorString = null;
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 将指定Excel文件的内容读取到DataSet中
/// </summary>
public static DataSet ReadXlsxFileForOleDb(string filePath, string excelName, ref string tableName, out string errorString)
{
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataSet ds = null;
try
{
// 初始化连接并打开，framework2.0时需要将平台目标改为x86否则会报错
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"";
conn = new OleDbConnection(connectionString);
conn.Open();
// 获取数据源的表定义元数据
DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
tableName = ExcelMethods.GetTableName(excelName);
List <string> excelSheetNames = new List <string>();
excelSheetNames = ExcelMethods.GetExcelSheetName(tableName, dtSheet);
if (excelSheetNames.Count == 1)
{
if (excelSheetNames.Contains(ExcelTableSetting.ExcelConfigSheetName))
{
errorString = string.Format("错误：{0}中不含有Sheet名为{1}或以{2}开头的数据表", filePath, ExcelTableSetting.ExcelDataSheetName.Replace("$", ""), tableName);
return(null);
}
}
else if (excelSheetNames.Count == 0)
{
errorString = string.Format("错误：{0}中不含有Sheet名为{1}或以{2}开头的数据表", filePath, ExcelTableSetting.ExcelDataSheetName.Replace("$", ""), tableName);
return(null);
}
ds = new DataSet();
foreach (string sheetName in excelSheetNames)
{
if (sheetName != ExcelTableSetting.ExcelConfigSheetName)
{
da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand(String.Format("Select * FROM [{0}]", sheetName), conn);
da.Fill(ds, sheetName);
// 删除表格末尾的空行
DataRowCollection rows = ds.Tables[sheetName].Rows;
int rowCount = rows.Count;
for (int i = rowCount - 1; i >= ExcelTableSetting.DataFieldDataStartRowIndex; --i)
{
if (string.IsNullOrEmpty(rows[i][0].ToString()))
{
rows.RemoveAt(i);
}
else
{
break;
}
}
}
else
{
//da.Dispose();
da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand(String.Format("Select * FROM [{0}]", ExcelTableSetting.ExcelConfigSheetName), conn);
da.Fill(ds, ExcelTableSetting.ExcelConfigSheetName);
}
}
}
catch (Exception e)
{
errorString = "错误：连接Excel失败，你可能尚未安装Office数据连接组件: http://www.microsoft.com/en-US/download/details.aspx?id=23734 \n";
tableName = null;
return(null);
}
finally
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
// 由于C#运行机制，即便因为表格中没有Sheet名为data的工作簿而return null，也会继续执行finally，而此时da为空，故需要进行判断处理
if (da != null)
{
da.Dispose();
}
conn.Dispose();
}
}
errorString = null;
return(ds);
}
