/// <summary> /// get customized datatable /// </summary> /// <param name="filePath">the source file path, contain data, txt or excel</param> /// <param name="result">template of xml that we setup with ourself</param> /// <param name="pattern">regex of the file, replace some string to the other string</param> /// <param name="replacement">replacement string</param> /// <returns>customized datatable</returns> public static DataTable GetCustomizedDataTable(string filePath, ParserEntity result, string pattern, string replacement) { DataTable dataTable = ExcelTextUtil.ReadText(filePath, result.SplitRegex, pattern, replacement); DataTable dt = GetCustomizedDataTable(result, dataTable); return(dt); }
/// <summary> /// customized datatable /// </summary> /// <param name="filePath">the source file path, contain data, txt or excel</param> /// <param name="result">template of xml that we setup with ourself</param> /// <returns>customized datatable</returns> public static DataTable GetCustomizedDataTable(string filePath, ParserEntity result) { try { DataTable dataTable = null; if (result.IsExcel) { try { dataTable = ExcelTextUtil.ReadSheet(filePath, result.SheetName); } catch (Exception ex) { try { dataTable = ExcelTextUtil.ReadFirstSheet(filePath); } catch (Exception exp) { throw new Exception("Excel 文件格式不正确,请检查!"); } } } else { dataTable = ExcelTextUtil.ReadText(filePath, result.SplitRegex); } DataTable dt = GetCustomizedDataTable(result, dataTable); return(dt); } catch (Exception e) { throw new Exception("Excel 文件格式不正确,请检查。"); } }
/// <summary> /// get customized datatable, the mandantory fields are defined in the xmlPath /// </summary> /// <param name="filePath">the source file path, contain data, txt or excel</param> /// <param name="xmlPath">template file path</param> /// <param name="keyName">ensure that it's unique string from template</param> /// <param name="pattern">regex of the file, replace some string to the other string</param> /// <param name="replacement">replacement string</param> /// <returns>customized datatable</returns> public static DataTable GetCustomizedDataTable(string filePath, string xmlPath, string keyName, string pattern, string replacement) { ParserEntity result = GetReadingResult(xmlPath, keyName); DataTable dataTable = GetCustomizedDataTable(filePath, result, pattern, replacement); return(dataTable); }
/// <summary> /// get customized datatable, the mandantory fields are defined in the xmlPath /// </summary> /// <param name="filePath">the source file path, contain data, txt or excel</param> /// <param name="xmlPath">template file path</param> /// <param name="keyName">ensure that it's unique string from template</param> /// <returns>customized datatable</returns> public static DataTable GetCustomizedDataTable(string filePath, string xmlPath, string keyName) { try { ParserEntity result = GetReadingResult(xmlPath, keyName); DataTable dataTable = GetCustomizedDataTable(filePath, result); return(dataTable); } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// get mandantory field and configure value /// </summary> /// <param name="xmlPath">congigure xml file</param> /// <param name="keyName">the unique of read object</param> /// <returns>return the configuration object</returns> public static ParserEntity GetReadingResult(string xmlPath, string keyName) { ParserEntity result = new ParserEntity(); IDictionary <string, string> columns = GetXmlMappingField(xmlPath, keyName); result.KeyName = ActualResult("KeyName", ref columns); result.KeyName = keyName; result.TitleRowNo = Convert.ToInt32(ActualResult("TitleRowNo", ref columns)); result.StartRowNo = Convert.ToInt32(ActualResult("StartRowNo", ref columns)); result.SheetName = ActualResult("SheetName", ref columns).ToLower(); result.SplitRegex = ActualResult("SplitRegex", ref columns); result.IsExcel = Convert.ToBoolean(ActualResult("IsExcel", ref columns)); result.Results = columns; return(result); }
/// <summary> /// Optimize DataTable and get customized datatable /// </summary> /// <param name="result">template of xml that we setup with ourself</param> /// <param name="dt">the original datatable and</param> /// <returns>customized datatable</returns> public static DataTable GetCustomizedDataTable(ParserEntity result, DataTable dt) { IDictionary <string, string> dict = result.Results; DataTable dataTable = new DataTable(); DataRow titleRow = null; //title in a line titleRow = dt.Rows[result.TitleRowNo - 1]; foreach (string key in dict.Keys) { for (int i = 0; i < titleRow.ItemArray.Length; i++) { if (dict[key].ToLower().Trim() == titleRow.ItemArray[i].ToString().ToLower().Trim()) { dt.Columns[i].ColumnName = key; } } } //remove nonsense rows data for (int i = 0; i < result.StartRowNo - 1; i++) { dt.Rows.Remove(dt.Rows[0]); } //remove nosense columns data int j = dt.Columns.Count; for (int i = 0; i < j; i++) { if (!dict.ContainsKey(dt.Columns[i].ToString())) { dt.Columns.Remove(dt.Columns[i]); --j; --i; } } return(dt); }