/// <summary>
/// 10行数据内分析导入文件的字段信息与设置的字段描述匹配程度
/// </summary>
/// <param name="orderInfo">导入命令描述</param>
/// <param name="descCol">设置的字段导入描述</param>
/// <param name="noMarchCol">未匹配到的字段信息</param>
/// <returns>匹配结果</returns>
public ResultInfo AnalysisField(ref DataImportOrderInfo orderInfo, ref DataImportDescCol descCol, out Dictionary <string, int> noMarchCol)
{
ResultInfo res = new ResultInfo();
noMarchCol = new Dictionary <string, int>();
// 获取文件数据摘要
DataTable dtSummer;
res = this.LoadFileSummerData(orderInfo, descCol, out dtSummer);
if (!res.Successed)
{
return(res);
}
if (orderInfo.HasTitle)
{
// 进入有标题行字段匹配过程
res = this.AnalysisField_hasTitle(dtSummer, ref orderInfo, ref descCol, out noMarchCol);
}
else
{
// 进入无标题行字段匹配过程
res = this.AnalysisField_noTitle(dtSummer, ref orderInfo, ref descCol, out noMarchCol);
}
return(res);
}
/// <summary>
/// 根据导入文件名，判断文件类型是否符合导入类型
/// </summary>
/// <param name="orderInfo">导入命令描述</param>
/// <returns>判断结果</returns>
protected ResultInfo checkFileType(DataImportOrderInfo orderInfo)
{
if (orderInfo.CheckExtensionAvailable())
{
return(ResultInfo.SuccessResultInfo());
}
else
{
return(ResultInfo.UnSuccessResultInfo("导入文件类型不匹配"));
}
}
/// <summary>
/// 获取可选导入表集合，Excel为Sheet，Txt为文件名
/// </summary>
/// <param name="orderInfo">导入命令描述</param>
/// <param name="tables">可选导入表集合</param>
/// <returns>获取结果</returns>
public override ResultInfo GetTables(DataImportOrderInfo orderInfo, out List <string> tables)
{
tables = new List <string>();
ResultInfo rst = base.checkFileType(orderInfo);
if (!rst.Successed)
{
return(rst);
}
tables.Add(Path.GetFileNameWithoutExtension(orderInfo.FileName));
return(ResultInfo.SuccessResultInfo());
}
/// <summary>
/// 根据导入命令描述，创建数据导入操作类
/// </summary>
/// <param name="orderInfo">导入命令描述</param>
/// <returns>数据导入操作类</returns>
public static IImportBase CreateImportHelper(DataImportOrderInfo orderInfo)
{
IImportBase im = null;
switch (orderInfo.FileType)
{
case EnumIOFileType.Excel:
im = new ImportFromExcel();
break;
case EnumIOFileType.Txt:
im = new ImportFromTxt();
break;
}
return(im);
}
/// <summary>
/// 获取可选导入表集合，Excel为Sheet，Txt为文件名
/// </summary>
/// <param name="orderInfo">导入命令描述</param>
/// <param name="tables">可选导入表集合</param>
/// <returns>获取结果</returns>
public override ResultInfo GetTables(DataImportOrderInfo orderInfo, out List <string> tables)
{
tables = new List <string>();
ResultInfo rst = base.checkFileType(orderInfo);
if (!rst.Successed)
{
return(rst);
}
DataTable dt = new DataTable();
using (OleDbConnection con = new OleDbConnection(this.CreateExcelConnStr(orderInfo)))
{
con.Open();
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, null);
con.Close();
}
foreach (DataRow dr in dt.Rows)
{
var tableName = dr["TABLE_NAME"].ToString();
//sheet名为数字的时候，获取的TABLE_NAME两边会有单引号，先去单引号--update xyp 2015年7月14日14:34:24
if (tableName.IndexOf('\'') == 0)
{
tableName = tableName.Trim('\'');
}
if (dr["TABLE_NAME"].ToString().Contains("$"))
{
//去除最后一个$符号之后的内容，TABLE_NAME解析出来最后必已$结尾
tableName = tableName.Substring(0, tableName.LastIndexOf('$'));
tables.Add(tableName);
}
}
return(ResultInfo.SuccessResultInfo());
}
/// <summary> /// 从文件中读取数据摘要，10行数据 /// </summary> /// <param name="orderInfo">导入命令描述</param> /// <param name="descCol">导入字段描述</param> /// <param name="dtSummer">获得的摘要数据</param> /// <returns>读取结果</returns> protected abstract ResultInfo LoadFileSummerData(DataImportOrderInfo orderInfo, DataImportDescCol descCol, out DataTable dtSummer);
/// <summary> /// 获取可选导入表集合，Excel为Sheet，Txt为文件名 /// </summary> /// <param name="orderInfo">导入命令描述</param> /// <param name="tables">可选导入表集合</param> /// <returns>获取结果</returns> public abstract ResultInfo GetTables(DataImportOrderInfo orderInfo, out List <string> tables);
/// <summary>
/// 将数据导入到DataTable
/// </summary>
/// <param name="orderInfo">导入命令描述</param>
/// <param name="descCol">设置的字段导入描述</param>
/// <param name="dtResult">获取到数据的DataTable</param>
/// <returns>导入结果</returns>
public ResultInfo LoadDataToDataTable(DataImportOrderInfo orderInfo, DataImportDescCol descCol, out DataTable dtResult)
{
ResultInfo rst = new ResultInfo();
// 根据导入字段，构建导入DataTable框架
dtResult = descCol.CreateDataTableByDescInfo();
DataTable dtFile;
rst = this.LoadFileAllData(orderInfo, descCol, out dtFile);
if (!rst.Successed)
{
return(rst);
}
// 加载导入字段到待导入字典，同时计算已匹配到的最大列号
Dictionary <int, DataImportDescInfo> dicDescDiction = new Dictionary <int, DataImportDescInfo>();
foreach (DataImportDescInfo descInfo in descCol)
{
if (descInfo.Fielddesc_External_Index >= 0)
{
dicDescDiction.Add(descInfo.Fielddesc_External_Index, descInfo);
}
}
DataTableReader reader = dtFile.CreateDataReader();
for (int row = 0; row < dtFile.Rows.Count; row++)
{
// 跳过有效数据行之前的数据
if (row < orderInfo.StartReadIndex)
{
continue;
}
DataRow drCur = dtFile.Rows[row];
//若读入空行，则跳过读取
string s = String.Join("", drCur.ItemArray);
if (string.IsNullOrEmpty(s))
{
continue;
}
string error = string.Empty;
DataRow dr = dtResult.NewRow();
bool isIgnore = false;
for (int i = 0; i < drCur.Table.Columns.Count; i++)
{
if (dicDescDiction.ContainsKey(i))
{
object value;
rst = dicDescDiction[i].GetImportValue(drCur[i].ToString(), out value, out isIgnore);
//符合过滤策略则跳过
if (isIgnore)
{
break;
}
dr[dicDescDiction[i].Fielddesc_Internal] = value;
if (!rst.Successed)
{
error += string.Format("[{0}]", rst.FailReasonDesc);
}
}
}
if (!isIgnore)
{
dr["Error"] = error;
dtResult.Rows.Add(dr);
}
}
// 处理尾行数据
if (orderInfo.EndReadIndex > 0)
{
for (int i = 0; i < orderInfo.EndReadIndex; i++)
{
dtResult.Rows.RemoveAt(dtResult.Rows.Count - 1 - i);
}
}
return(rst);
}
/// <summary>
/// 分析所需字段与导入文件内字段的匹配关系，无标题行
/// </summary>
/// <param name="dtSummer">10行摘要数据</param>
/// <param name="orderInfo">导入命令描述</param>
/// <param name="descCol">导入字段描述</param>
/// <param name="noMarchCol">未匹配到的字段</param>
/// <returns>匹配结果</returns>
private ResultInfo AnalysisField_noTitle(DataTable dtSummer, ref DataImportOrderInfo orderInfo, ref DataImportDescCol descCol, out Dictionary <string, int> noMarchCol)
{
// 未匹配字段集合
noMarchCol = new Dictionary <string, int>();
// 初始化字段匹配集合，清空导入字段的匹配信息
foreach (DataImportDescInfo descInfo in descCol)
{
descInfo.Fielddesc_External = string.Empty;
descInfo.Fielddesc_External_Index = -1;
}
int titleRowIndex = -1;
// 若指定了读入行，则该行为标题行
if (orderInfo.StartReadIndex > 0)
{
titleRowIndex = orderInfo.StartReadIndex;
}
// 若未发现标题行，则定位首行非空数据为标题行，只要有一个字段非空即为标题行
if (titleRowIndex < 0)
{
for (int row = 0; row < dtSummer.Rows.Count; row++)
{
for (int col = 0; col < dtSummer.Columns.Count; col++)
{
string drCell = dtSummer.Rows[row][col].ToString();
if (!string.IsNullOrEmpty(drCell))
{
titleRowIndex = row;
break;
}
}
if (titleRowIndex >= 0)
{
break;
}
}
}
// 若未发现非空行，则报错退出
if (titleRowIndex < 0)
{
return(ResultInfo.UnSuccessResultInfo("10行内未找到标题行"));
}
// 针对标题行，进行字段匹配
DataRow drTitle = dtSummer.Rows[titleRowIndex];
for (int i = 0; i < drTitle.Table.Columns.Count; i++)
{
string drCell = drTitle[i].ToString();
if (i < descCol.Count)
{
descCol[i].Fielddesc_External = string.Format("第{0}列 首行样例：{1}", i + 1, drCell);
descCol[i].Fielddesc_External_Index = i;
}
else
{
noMarchCol.Add(string.Format("第{0}列 首行样例：{1}", i + 1, drCell), i);
}
}
// 设置有效数据启始行号
orderInfo.StartReadIndex = titleRowIndex;
return(ResultInfo.SuccessResultInfo());
}
/// <summary>
/// 从Txt中读取指定行数
/// </summary>
/// <param name="orderInfo">导入命令描述</param>
/// <param name="descCol">导入字段描述</param>
/// <param name="LoadLine">要读取的行数，0为全部行</param>
/// <param name="dtResult">获得的指定行数的数据</param>
/// <returns>读取结果</returns>
private ResultInfo LoadTxtData(DataImportOrderInfo orderInfo, DataImportDescCol descCol, int LoadLine, out DataTable dtResult)
{
dtResult = new DataTable();
ResultInfo rst = base.checkFileType(orderInfo);
if (!rst.Successed)
{
return(rst);
}
// 若txt文件不按间隔符分割，则对字段开始位置排序读取
List <DataImportDescInfo> listDataCol = new List <DataImportDescInfo>();
if (string.IsNullOrEmpty(orderInfo.TxtSplitStr))
{
listDataCol = descCol.OrderBy(d => d.TxtFieldStartIndex).ToList();
}
using (FileStream fs = new FileStream(orderInfo.FileName, System.IO.FileMode.Open, System.IO.FileAccess.Read))
{
StreamReader sr = new StreamReader(fs, System.Text.Encoding.Default);
string strLine;
// 在CSV文件中寻找标题行，并匹配字段匹配集合
int readLineIndex = 0;
while ((strLine = sr.ReadLine()) != null)
{
if (LoadLine > 0)
{
if (readLineIndex == LoadLine)
{
break;
}
}
List <string> aryLine;
if (!string.IsNullOrEmpty(orderInfo.TxtSplitStr))
{
aryLine = this.AnalysisTxtLine(strLine, orderInfo.TxtSplitStr);
}
else
{
aryLine = this.AnalysisTxtLine(strLine, listDataCol);
}
// 判断dtResult列数是否一致
if (dtResult.Columns.Count < aryLine.Count)
{
int tag = dtResult.Columns.Count;
for (int i = 0; i < aryLine.Count - tag; i++)
{
DataColumn dc = new DataColumn();
dc.DataType = typeof(string);
dc.DefaultValue = string.Empty;
dc.ColumnName = string.Format("Col{0}", tag + i + 1);
dtResult.Columns.Add(dc);
}
}
DataRow dr = dtResult.NewRow();
dr.ItemArray = aryLine.ToArray();
dtResult.Rows.Add(dr);
readLineIndex++;
}
}
return(ResultInfo.SuccessResultInfo());
}
/// <summary>
/// 从文件中读取全部,重写基类
/// </summary>
/// <param name="orderInfo">导入命令描述</param>
/// <param name="descCol">导入字段描述</param>
/// <param name="dtAllDate">获得的全部数据</param>
/// <returns>读取结果</returns>
protected override ResultInfo LoadFileAllData(DataImportOrderInfo orderInfo, DataImportDescCol descCol, out DataTable dtAllDate)
{
return(this.LoadTxtData(orderInfo, descCol, 0, out dtAllDate));
}
/// <summary>
/// 从文件中读取数据摘要，10行数据,重写基类
/// </summary>
/// <param name="orderInfo">导入命令描述</param>
/// <param name="descCol">导入字段描述</param>
/// <param name="dtSummer">获得的摘要数据</param>
/// <returns>读取结果</returns>
protected override ResultInfo LoadFileSummerData(DataImportOrderInfo orderInfo, DataImportDescCol descCol, out DataTable dtSummer)
{
return(this.LoadTxtData(orderInfo, descCol, 10, out dtSummer));
}
/// <summary>
/// 从Excel中读取指定行数
/// </summary>
/// <param name="orderInfo">导入命令描述</param>
/// <param name="LoadLine">要读取的行数，0为全部行</param>
/// <param name="dtResult">获得的指定行数的数据</param>
/// <returns>读取结果</returns>
private ResultInfo LoadExcelData(DataImportOrderInfo orderInfo, int LoadLine, out DataTable dtResult)
{
dtResult = new DataTable();
ResultInfo rst = base.checkFileType(orderInfo);
if (!rst.Successed)
{
return(rst);
}
string sql = string.Format("select * from [{0}$]", orderInfo.TableName);
if (LoadLine > 0)
{
sql = string.Format("select top {1} * from [{0}$]", orderInfo.TableName, LoadLine - 1);
}
using (OleDbConnection con = new OleDbConnection(this.CreateExcelConnStr(orderInfo)))
{
OleDbCommand ocm = new OleDbCommand(sql, con);
OleDbDataReader reader = null;
con.Open();
try
{
reader = ocm.ExecuteReader();
for (int i = 0; i < reader.FieldCount; i++)
{
DataColumn dc = new DataColumn();
dc.DataType = typeof(string);
dc.DefaultValue = string.Empty;
dc.ColumnName = reader.GetName(i);
dtResult.Columns.Add(dc);
}
while (reader.Read())
{
DataRow dr = dtResult.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
dr[i] = reader[i].ToString();
}
dtResult.Rows.Add(dr);
}
DataRow rows;
rows = dtResult.NewRow();
for (int i = 0; i < dtResult.Columns.Count; i++)
{
rows[i] = (dtResult.Columns[i].ColumnName.ToString());
}
dtResult.Rows.InsertAt(rows, 0);
}
catch (Exception ex)
{
return(ResultInfo.UnSuccessResultInfo(ex.Message));
}
finally
{
if (!reader.IsClosed)
{
reader.Close();
}
con.Close();
}
}
return(ResultInfo.SuccessResultInfo());
}
/// <summary>
/// 从文件中读取全部,重写基类
/// </summary>
/// <param name="orderInfo">导入命令描述</param>
/// <param name="descCol">导入字段描述</param>
/// <param name="dtAllData">获得的全部数据</param>
/// <returns>读取结果</returns>
protected override ResultInfo LoadFileAllData(DataImportOrderInfo orderInfo, DataImportDescCol descCol, out DataTable dtAllData)
{
return(LoadExcelData(orderInfo, 0, out dtAllData));
}
/// <summary> /// 从文件中读取全部 /// </summary> /// <param name="orderInfo">导入命令描述</param> /// <param name="descCol">导入字段描述</param> /// <param name="dtAllDate">获得的全部数据</param> /// <returns>读取结果</returns> protected abstract ResultInfo LoadFileAllData(DataImportOrderInfo orderInfo, DataImportDescCol descCol, out DataTable dtAllDate);
/// <summary>
/// 分析所需字段与导入文件内字段的匹配关系，有标题行
/// </summary>
/// <param name="dtSummer">10行摘要数据</param>
/// <param name="orderInfo">导入命令描述</param>
/// <param name="descCol">导入字段描述</param>
/// <param name="noMarchCol">未匹配到的字段</param>
/// <returns>匹配结果</returns>
private ResultInfo AnalysisField_hasTitle(DataTable dtSummer, ref DataImportOrderInfo orderInfo, ref DataImportDescCol descCol, out Dictionary <string, int> noMarchCol)
{
//未匹配字段集合
noMarchCol = new Dictionary <string, int>();
// 初始化字段匹配集合，清空导入字段的匹配信息，加载到待匹配字段（方便匹配字段名时，可以直接通过key来查找）
Dictionary <string, DataImportDescInfo> dicDescDiction = new Dictionary <string, DataImportDescInfo>();
foreach (DataImportDescInfo descInfo in descCol)
{
descInfo.Fielddesc_External = string.Empty;
descInfo.Fielddesc_External_Index = -1;
dicDescDiction.Add(descInfo.Fielddesc_Internal_Desc, descInfo);
}
int titleRowIndex = -1;
// 在摘要数据中定位标题行，只要有一个字段匹配到即为标题行
for (int row = 0; row < dtSummer.Rows.Count; row++)
{
for (int col = 0; col < dtSummer.Columns.Count; col++)
{
string drCell = dtSummer.Rows[row][col].ToString();
if (dicDescDiction.ContainsKey(drCell))
{
titleRowIndex = row;
break;
}
}
if (titleRowIndex >= 0)
{
break;
}
}
// 若未发现标题行，则定位首行非空数据为标题行，只要有一个字段非空即为标题行
if (titleRowIndex < 0)
{
for (int row = 0; row < dtSummer.Rows.Count; row++)
{
for (int col = 0; col < dtSummer.Columns.Count; col++)
{
string drCell = dtSummer.Rows[row][col].ToString();
if (!string.IsNullOrEmpty(drCell))
{
titleRowIndex = row;
break;
}
}
if (titleRowIndex >= 0)
{
break;
}
}
}
// 若未发现非空行，则报错退出
if (titleRowIndex < 0)
{
return(ResultInfo.UnSuccessResultInfo("10行内未找到标题行"));
}
// 针对标题行，进行字段匹配
DataRow drTitle = dtSummer.Rows[titleRowIndex];
for (int i = 0; i < drTitle.Table.Columns.Count; i++)
{
string drCell = drTitle[i].ToString();
if (dicDescDiction.ContainsKey(drCell))
{
dicDescDiction[drCell].Fielddesc_External = string.Format("{0} 第{1}列", drCell, i + 1);
dicDescDiction[drCell].Fielddesc_External_Index = i;
}
else
{
noMarchCol.Add(string.Format("{0} 第{1}列", drCell, i + 1), i);
}
}
// 设置有效数据启始行号
orderInfo.StartReadIndex = titleRowIndex + 1;
return(ResultInfo.SuccessResultInfo());
}
/// <summary>
/// 根据导入命令描述构造导入操作类
/// </summary>
/// <param name="orderInfo">导入命令描述</param>
private string CreateExcelConnStr(DataImportOrderInfo orderInfo)
{
string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + orderInfo.FileName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
return(conString);
}
