public void ConvertTextDocumentToDataTable(string filePath, int FileSerial)
{
DataTable dt = new DataTable();
if (File.Exists(filePath))
{
using (StreamReader sr = new StreamReader(filePath, Encoding.GetEncoding(1251)))
{
var line = sr.ReadLine();
if (null != line)
{
string[] headers = line.Split('|');
DataValidationAPI ob = new DataValidationAPI();
if (ob.ChekCol(headers, FileSerial) == true)
{
foreach (string header in headers)
{
dt.Columns.Add(header);
}
while (!sr.EndOfStream)
{
string[] rows = sr.ReadLine().Split('|');
DataRow dr = dt.NewRow();
for (int i = 0; i < headers.Length; i++)
{
dr[i] = rows[i];
}
dt.Rows.Add(dr);
}
if (sr.EndOfStream == true)
{
dataTable = dt;
}
else
{
Message = "DataEmpty";
}
}
else
{
Message = "Missing Column";
}
}
else
{
Message = "Missing Column";
}
}
}
else
{
Message = "File_not_Exists";
}
}
public void ConvertExcelToDataTable(string filePath, bool isXlsx, int FileSerial)
{
FileStream stream = null;
IExcelDataReader excelReader = null;
DataTable dataTables = null;
if (File.Exists(filePath))
{
stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
excelReader = isXlsx ? ExcelReaderFactory.CreateOpenXmlReader(stream) : ExcelReaderFactory.CreateBinaryReader(stream);
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
if (result != null && result.Tables.Count > 0)
{
dataTables = result.Tables[0];
}
List <string> columnNameList = new List <string>();
foreach (DataColumn dataColumn in dataTables.Columns)
{
columnNameList.Add(dataColumn.ColumnName);
}
DataValidationAPI ob = new DataValidationAPI();
string[] headers = columnNameList.ToArray();
if (ob.ChekCol(headers, FileSerial) == true)
{
ArrayList rows = new ArrayList();
foreach (DataRow dataRow in dataTables.Rows)
{
rows.Add(dataRow.ItemArray.Select(item => item.ToString()));
}
if (rows.Count > 0)
{
dataTable = dataTables;
}
else
{
Message = "DataEmpty";
}
}
else
{
Message = "Missing Column";
}
}
else
{
Message = "File_not_Exists";
}
}
