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