Method to read the Data from Excel Sheet
#region Read Data from Excel Sheet
/// <summary>
/// Read the Data from Excel Sheet.
/// </summary>
/// <param name="filePath">Excel File Path</param>
/// <returns>Return in Datatable format</returns>
private DataTable ReadExcelData(string filePath)
{
DataTable excelData = new DataTable();
try
{
string fileExtension = Path.GetExtension(filePath).ToLower();
string excelConnectionString = "";
if (fileExtension == ".xlsx")
{
//Get the Office 2007 Format
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;\"";
}
else
{
//Get the Office 2003 Format
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;'";
}
excelConnectionString = string.Format(excelConnectionString, filePath);
//System.Threading.Thread.Sleep(10000);
//Open the Oledb connection and get the data from Excel Sheet
//Fill the data in DataTable (excelData).
OleDbConnection connection = new OleDbConnection(excelConnectionString);
OleDbCommand command = new OleDbCommand("select SAPCode,ImpactProdDesc,Notes from [Sheet1$]", connection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
//excelData.TableName = "tempCandidateList";
connection.Open();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(excelData);
connection.Close();
}
catch (Exception ex)
{
Response.Write(ex.Message);
objBusinessEntity.ErrorLog(ex.Message + ex.StackTrace);
//utility.saveError(ex, "Create_candidate.aspx", "ReadExcelData: create_candidate");
}
return excelData;
}
#endregion
Method to insert the Data into SPLIST item or Table row:
#region Insert Excel Data
/// <summary>
/// Validate the Mandatory fields
/// If Mandatory fields are missing, update the status is Invalid
/// If Mandatory fields are satisfied, Insert into Database and update the status as InsertionSuccess.
/// </summary>
/// <param name="excelData">Excel Data within Datatable</param>
/// <returns>List of Candidate with updated status.</returns>
private DataTable UpdataExcelData(DataTable excelData)
{
Guid candidateID;
DataColumn columnStatus = new DataColumn("ExcelStatus");
excelData.Columns.Add(columnStatus);
try
{
SPListItemCollection splistcollImpact = slImpactAnalysis.Items;
//int intNumber = splistcollDesigncheck.Count;
for (int count = 0; count < excelData.Rows.Count; count++)
{
SPListItem splistitemImpact = null;
string excelstatus = "";
if ((excelData.Rows[count]["SAPCode"].ToString() == "") || (excelData.Rows[count]["ImpactProdDesc"].ToString() == "")
|| (excelData.Rows[count]["Notes"].ToString() == "")
)
{
excelstatus = "invalid";
goto excelLable;
}
EntityList<GenerateIDItem> entGenerateID = dt.GetList<GenerateIDItem>("GenerateID");
var generatedID = from gen in entGenerateID.ToList()
where gen.Title == "ImpactAnalysis"
select gen;
// splistitemMomMastergenlist = splistcollMomMastergencol.GetItemById(2);
// splistitemMomMastergenlist["MOMID"] = Convert.ToInt32(splistitemMomMastergenlist["MOMID"]) + 1;
int intItemID = Convert.ToInt32(generatedID.ToList()[0].LastID);
generatedID.ToList()[0].LastID = generatedID.ToList()[0].LastID + 1;
splistitemImpact = splistcollImpact.Add();
EntityList<PartPhaseMasterItem> entityPhaseMaster = dt.GetList<PartPhaseMasterItem>(objBusinessEntity.PartPhaseMaster);
string strPhaseno = objBusinessEntity.PartPhaseNo(entityPhaseMaster, "Impact Analysis");
if (!string.IsNullOrEmpty(strPhaseno))
{
splistitemImpact["Title"] = string.Concat(strBOMID, ":", strPhaseno);
}
splistitemImpact["ImpactAnalysisNo"] = intItemID;
splistitemImpact["BOMID"] = objBusinessEntity.GetLookupValue(objBusinessEntity.BOM, "BOMNo", strBOMID); ;
splistitemImpact["SAPCode"] = excelData.Rows[count]["SAPCode"].ToString();
splistitemImpact["ImpactedProductDescription"] = excelData.Rows[count]["ImpactProdDesc"].ToString();
splistitemImpact["ImpactedPartNo"] = strPartNo;
splistitemImpact["ImpactedPartDescription"] = strPartDesc;
splistitemImpact["RetrievalStatus"] = "Manual";
splistitemImpact["FlowChartID"] = strFlowChartID;
splistitemImpact["Notes"] = excelData.Rows[count]["Notes"].ToString();
excelLable:
if (string.IsNullOrEmpty(excelstatus))
{
try
{
splistitemImpact.Update();
dt.SubmitChanges();
excelData.Rows[count]["ExcelStatus"] = "InsertionSuccess";
}
catch
{
excelData.Rows[count]["ExcelStatus"] = "InsertionFailed";
}
}
else
{
excelData.Rows[count]["ExcelStatus"] = excelstatus;
}
}
}
catch (Exception ex)
{
objBusinessEntity.ErrorLog(ex.Message + ex.StackTrace);//utility.saveError(ex, "Create_candidate.aspx", "UpdataExcelData: create_candidate");
}
return excelData;
}
#endregion
Button click event to process the Data from Excel Sheet:
protected void btnProcess_Click(object sender, EventArgs e)
{
try
{
if (fuimpactedxls.PostedFile != null)
{
//File Name of the Excel sheet
string excelFileName = fuimpactedxls.PostedFile.FileName.ToLower();
excelFileName = Path.GetFileName(excelFileName);
//Get the file extension of the excel sheet
string fileExtension = Path.GetExtension(excelFileName);
string excelFullPath = "";
//excelFullPath = @"C:\Users\vinitkumar.poshan\Desktop\ImpactedTemplate";
if (fileExtension == ".xlsx")
{
// Get the directory to save the excel sheet
excelFullPath = strFilepath;
//Create the Directory if not exists.
if (!Directory.Exists(excelFullPath))
{
Directory.CreateDirectory(excelFullPath);
}
excelFullPath += Guid.NewGuid().ToString() + "_" + excelFileName;
//Save the Excel sheet.
fuimpactedxls.SaveAs(excelFullPath);
//Read the excel sheet and get the data in DataTable format.
DataTable excelData = new DataTable();
excelData = ReadExcelData(excelFullPath);
Boolean isEmpty = false;
for (int i = 0; i < excelData.Rows.Count; i++)
{
isEmpty = true;
for (int j = 0; j < excelData.Columns.Count; j++)
{
if (
string.IsNullOrEmpty(excelData.Rows[i][j].ToString()) == false)
{
isEmpty = false; break;
}
} if (isEmpty == true)
{
excelData.Rows.RemoveAt(i); i--;
}
}
//DataTable dt = ValidateExcelData(excelData);
DataTable excelExecutionStatus = UpdataExcelData(excelData);
DataTable dtxls = getImpactManual();
//Bind the Data to Gridview.
gvfinal.DataSource = dtxls;
gvfinal.DataBind();
}
}
}
catch (Exception ex)
{
objBusinessEntity.ErrorLog(ex.Message + ex.StackTrace);
}
}
Here three Methods are there ,using these three methods we can read excel data and store into a list or Table.
Please create a Excel based on your table column name or list column name.
First row of excel sheet it will use as a header column name and then data will be read.
Hope this is enough ....if any problem comes please let me know
No comments:
Post a Comment