Exemple #1
0
        /// <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);
        }
Exemple #2
0
 /// <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());
        }
Exemple #4
0
        /// <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());
        }
Exemple #6
0
 /// <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);
Exemple #7
0
 /// <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);
Exemple #8
0
        /// <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);
        }
Exemple #9
0
        /// <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));
 }
Exemple #15
0
 /// <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);
Exemple #16
0
        /// <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);
        }