/// <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);
}
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);
}
}
/// <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);
}
/// <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);
}
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);
}