/* End: Constructor.....*/
public DataTable GetRecordsForMappedColumnFromCSVToDataTable(string _strTableName, string _strPrimaryKey, string filePath, string[] SeprateColumns)
{
dtb = new DataTable();
var objClsOledbdatalayer = new ClsOleDBDataWrapper();
string FileColumnName = "", DBColumnName = "", DBDataType = "", DBSize = "", StrColumn = "";
foreach (string strColumns in SeprateColumns)
{
var arrColumns = strColumns.Split('|');
FileColumnName = Convert.ToString(arrColumns[0]).Trim();
DBColumnName = Convert.ToString(arrColumns[1]).Trim();
DBDataType = Convert.ToString(arrColumns[2]).Trim();
DBSize = Convert.ToString(arrColumns[3]).Trim();
StrColumn += FileColumnName + ",";
}
StrColumn = StrColumn.TrimEnd(',');
string location = HttpContext.Current.Server.MapPath("~/Resource/ExcelUpload/" + filePath);
try
{
var excelSheets = objClsOledbdatalayer.CountTotalSheets(location);
if (excelSheets.Length > 0)
{
foreach (DataTable dt in excelSheets.Select(t => objClsOledbdatalayer.GetExcelSheetValueBasedOnColumn(location, StrColumn, t)).Where(dt => dt.Rows.Count > 0))
{
dtb = dt.Rows.Cast <DataRow>().Where(row => !row.ItemArray.All(field => field is System.DBNull || string.Compare((field as string).Trim(), string.Empty) == 0)).CopyToDataTable();
}
}
}
catch (Exception ex)
{
var err = ex.Message;
if (ex.InnerException != null)
{
err = err + " :: Inner Exception :- " + ex.ToString();
}
const string addInfo = "Error while executing GetRecordsForMappedColumnFromCSVToDataTable in ExcelToDataTable.cs :: -> ";
var objPub = new ClsExceptionPublisher();
objPub.Publish(err, addInfo);
}
return(dtb);
}
// Method to Get The List Of the column name from the excel File
public List <string> GetColumnListofExcel(string fileName)
{
ClsOleDBDataWrapper _objClsOleDbDataWrapper = new ClsOleDBDataWrapper();
Common objCommon = new Common();
List <string> _strColumnName = new List <string>();
var path = HttpContext.Current.Server.MapPath("~/" + objCommon.GetFilepath("ExcelUpload") + fileName);
var excelSheets = _objClsOleDbDataWrapper.CountTotalSheets(path);
foreach (var t in excelSheets)
{
dtb = _objClsOleDbDataWrapper.GetExcelColumnHeader(path, t);
foreach (DataColumn dc in dtb.Columns)
{
_strColumnName.Add(dc.ColumnName);
}
}
return(_strColumnName);
}