Wednesday, 15 June 2011

Reading the Data From EXCEL Sheet in SharePoint and Asp.net




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 



1 comment: