Wednesday, 26 February 2014

Excel Column Name Change private Table ExportAllData() { Table table = new Table(); TableRow row = null; //DTVendorDetails = RPT_VITS_VendorDetails(Decrypt(Request.QueryString["Division"].ToString(), strSecurityCode), lblCompanyCode.Text.Trim(), Decrypt(Request.QueryString["Plant"].ToString(), strSecurityCode), txtVendorName.Text.Trim(), txtLogFrom.Text.Trim(), txtLogTo.Text.Trim(), txtBarCode.Text.Trim(), txtVendorNameUnknown.Text.Trim(), txtUser.Text.Trim(), txtPDOCNO.Text.Trim(), string.Empty, txtInvoiceNo.Text.Trim(), ddlStatus.SelectedValue.Trim()); string[] VendorCode = { string.Empty }; string[] companycode = lblCompanyCode.Text.Trim().Split('-'); string selVal = string.Empty; for (int j = 0; j < cblCustomerList.Items.Count; j++) { if (cblCustomerList.Items[j].Selected) { selVal = selVal + "," + cblCustomerList.Items[j].Value.Trim(); } } if (txtVendorName.Text.Length > 0) { VendorCode = txtVendorName.Text.Trim().Split('-'); } //if (txtUser.Text.Length > 0) //{ // string[] sponsor = txtUser.Text.Trim().Split('-'); // DTVendorDetails = RPT_VITS_VendorDetails(Decrypt(Request.QueryString["Division"].ToString(), strSecurityCode), companycode[0].Trim(), Decrypt(Request.QueryString["Plant"].ToString(), strSecurityCode), VendorCode[0].ToString().Trim(), txtLogFrom.Text.Trim(), txtLogTo.Text.Trim(), txtBarCode.Text.Trim(), txtVendorNameUnknown.Text.Trim(), sponsor[0].ToString().Trim(), txtPDOCNO.Text.Trim(), string.Empty, txtInvoiceNo.Text.Trim(), selVal.Trim(), hdnLevel1.Value.Trim(), hdnLevel2.Value.Trim(), hdnLevel3.Value.Trim()); //} //else //{ // DTVendorDetails = RPT_VITS_VendorDetails(Decrypt(Request.QueryString["Division"].ToString(), strSecurityCode), companycode[0].Trim(), Decrypt(Request.QueryString["Plant"].ToString(), strSecurityCode), VendorCode[0].ToString().Trim(), txtLogFrom.Text.Trim(), txtLogTo.Text.Trim(), txtBarCode.Text.Trim(), txtVendorNameUnknown.Text.Trim(), txtUser.Text.Trim(), txtPDOCNO.Text.Trim(), string.Empty, txtInvoiceNo.Text.Trim(), selVal.Trim(), hdnLevel1.Value.Trim(), hdnLevel2.Value.Trim(), hdnLevel3.Value.Trim()); //} objBll.Division = Decrypt(Request.QueryString["Division"].ToString(), strSecurityCode); objBll.Company = companycode[0].Trim(); objBll.Plant = Decrypt(Request.QueryString["Plant"].ToString(), strSecurityCode); objBll.Vendor_Code = VendorCode[0].ToString().Trim(); objBll.Log_From = txtLogFrom.Text.Trim(); objBll.Log_To = txtLogTo.Text.Trim(); objBll.Srlno = txtBarCode.Text.Trim(); objBll.Vendor_Name = txtVendorNameUnknown.Text.Trim(); objBll.User = txtUser.Text.Trim(); objBll.Pdno = txtPDOCNO.Text.Trim(); objBll.Invoice_No = txtInvoiceNo.Text.Trim(); objBll.Approved_By = ""; objBll.App_Status = selVal.Trim(); objBll.LevelCode1 = hdnLevel1.Value.Trim(); objBll.LevelCode2 = hdnLevel2.Value.Trim(); objBll.LevelCode3 = hdnLevel3.Value.Trim(); objBll.SGID = Session["SG_ID"].ToString(); DTVendorDetails = objBll.RPT_VITS_VendorDetails(objBll); // Remove unwanted columns DTVendorDetails.Columns.Remove("UNAME"); DTVendorDetails.Columns.Remove("SGTXT_VEN"); DTVendorDetails.Columns.Remove("BEWTP"); DTVendorDetails.Columns.Remove("XBLNR"); DTVendorDetails.Columns.Remove("APDSTAT"); DTVendorDetails.Columns.Remove("BILLTRACK"); DTVendorDetails.Columns.Remove("ZSABE"); DTVendorDetails.Columns.Remove("CHECT"); DTVendorDetails.Columns.Remove("EXPN"); DTVendorDetails.Columns.Remove("FNAME"); DTVendorDetails.Columns.Remove("URL"); DTVendorDetails.Columns.Remove("BUDAT"); DTVendorDetails.Columns.Remove("LOC"); DTVendorDetails.Columns.Remove("ROW_ID"); DTVendorDetails.Columns.Remove("SERVICE_ENTRY_DT"); DTVendorDetails.Columns.Remove("MYINFO_APPROVAL_DATE"); DTVendorDetails.Columns.Remove("PAYMENT_DATE"); DTVendorDetails.Columns.Remove("ITAD_INDEXNO"); DTVendorDetails.Columns.Remove("AUGDT"); DTVendorDetails.Columns.Remove("DELFLG"); DTVendorDetails.Columns.Remove("EXKEY"); //Add the Headers row = new TableRow(); for (int j = 0; j < DTVendorDetails.Columns.Count; j++) { TableHeaderCell headerCell = new TableHeaderCell(); switch (DTVendorDetails.Columns[j].ColumnName) { case "MANDT": headerCell.Text = "Division"; break; case "BUKRS": headerCell.Text = "Company Code"; break; case "LOGSRL": headerCell.Text = "Login Number"; break; case "LIFNR": headerCell.Text = "Vendor code"; break; case "BELNR": headerCell.Text = "Invoice Number"; break; case "INVOICE_STATUS": headerCell.Text = "Invoice Status"; break; case "INVOICE_DESC": headerCell.Text = "Material/Service"; break; case "LOGDATE": headerCell.Text = "Login Date"; break; case "APNAM": headerCell.Text = "User"; break; case "CGRYFLG": headerCell.Text = "Invoice Category"; break; case "EBELN": headerCell.Text = "Purchase Order"; break; case "BELNR1": headerCell.Text = "Invoice Number"; break; case "DUEDAT": headerCell.Text = "Due date"; break; case "GTOTAL": headerCell.Text = "Exces Duty"; break; case "WODOC": headerCell.Text = "Processing Document"; break; case "INVDT": headerCell.Text = "Invoice Date"; break; case "LIFRE": headerCell.Text = "Invoicing Party"; break; case "TOTAL": headerCell.Text = "Invoice Amount"; break; case "GTOTAL1": headerCell.Text = "Grand Total"; break; case "WMWST": headerCell.Text = "Service Tax"; break; case "REMARKS": headerCell.Text = "Remarks"; break; case "BDC_FLG": headerCell.Text = "Processing Invoice Status"; break; case "AUGBL": headerCell.Text = "Clearing Document"; break; case "VENDET": headerCell.Text = "Vendor Deatails"; break; case "EXCEPTIONS": headerCell.Text = "Exception"; break; case "LOC": headerCell.Text = "Location"; break; case "BUDAT1": headerCell.Text = "Logged Date"; break; case "VENDORNAME": headerCell.Text = "Vendor Name"; break; case "INVPARTYNAME": headerCell.Text = "Invoice Party Name"; break; case "STATUS": headerCell.Text = "Status"; break; case "UTR_NO": headerCell.Text = "UTR Number"; break; case "UTR_DATE": headerCell.Text = "UTR Date"; break; } headerCell.BackColor = System.Drawing.Color.FromName("#FFDD6F"); headerCell.ForeColor = System.Drawing.Color.FromName("#795E00"); row.Cells.Add(headerCell); } table.Rows.Add(row); //Add the Column values for (int i = 0; i < DTVendorDetails.Rows.Count; i++) { row = new TableRow(); for (int j = 0; j < DTVendorDetails.Columns.Count; j++) { TableCell cell = new TableCell(); cell.Text = DTVendorDetails.Rows[i][j].ToString(); row.Cells.Add(cell); } // Add the TableRow to the Table table.Rows.Add(row); } return table; }
Row DataBound Control public void gvVendorDetails_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { HiddenField hdnFlag = e.Row.FindControl("hdnFlag") as HiddenField; HiddenField hdnAUGBL = e.Row.FindControl("hdnAugBl") as HiddenField; HiddenField hdnAPDSTAT = e.Row.FindControl("hdnAPDSTAT") as HiddenField; //Invoice Status HiddenField hdnInv_Status = e.Row.FindControl("hdnInv_Status") as HiddenField; //LinkButton lnkBarCode = e.Row.FindControl("lnkBarCode") as LinkButton; Label lnkBarCode = e.Row.FindControl("lnkBarCode") as Label; HiddenField hdnEXKEY = e.Row.FindControl("hdnExkey") as HiddenField; Label lblEXKEY = e.Row.FindControl("lblPending") as Label; // Commented by Jagan on 20121120 //Label lblWODOC = e.Row.FindControl("lblDocNo") as Label; //Label lblAUGBL = e.Row.FindControl("lblClearing") as Label; HiddenField hdnDelFlags = e.Row.FindControl("hdnDelFlag") as HiddenField; HiddenField hdnProcessDoc = e.Row.FindControl("hdnProcessDoc") as HiddenField; HiddenField hdnUrl = e.Row.FindControl("hdnUrl") as HiddenField; HiddenField hdnVendorCode = e.Row.FindControl("hdnVendorCode") as HiddenField; HiddenField hdnVendorDetails = e.Row.FindControl("hdnVendorDetails") as HiddenField; HiddenField hdnVendorName = e.Row.FindControl("hdnVendorName") as HiddenField; ImageButton imgbtnShowInvoice = e.Row.FindControl("imgbtnShowInvoice") as ImageButton; ImageButton imgbtnShowPrevApproval = e.Row.FindControl("imgbtnShowPrevApproval") as ImageButton; Label lblCategory = e.Row.FindControl("lblCategory") as Label; string Path = string.Concat(hdnBarCode.Value.Trim(), hdnUrl.Value); lnkBarCode.Attributes.Add("onclick", "return false;"); imgbtnShowInvoice.Attributes.Add("onclick", "return ShowInvoiceImage('" + Path + "','" + lnkBarCode.Text + "');"); imgbtnShowPrevApproval.Visible = false; if (DTBarCode.Rows.Count > 0) { DataView dv = new DataView(DTBarCode); dv.RowFilter = "ITAH_LOGSRLNO ='" + lnkBarCode.Text.Trim() + "'"; if (dv.Count > 0) { imgbtnShowPrevApproval.Visible = true; } else { imgbtnShowPrevApproval.Visible = false; } } imgbtnShowPrevApproval.Attributes.Add("onclick", "return showPreviousApprovals('" + Encrypt(lnkBarCode.Text.Trim(), strSecurityCode) + "')"); Image imgStatus = e.Row.FindControl("imgStatus") as Image; string VendorCode = hdnVendorCode.Value.Trim(); //DataRowView drv = (DataRowView)e.Row.DataItem; if (tmpCategoryName != VendorCode) { tmpCategoryName = VendorCode; // Get a reference to the current row's Parent, which is the Gridview (which happens to be a table) Table tbl = e.Row.Parent as Table; if (tbl != null) { GridViewRow row = new GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal); TableCell cell = new TableCell(); // Span the row across all of the columns in the Gridview cell.ColumnSpan = this.gvVendorDetails.Columns.Count; cell.Width = Unit.Percentage(100); cell.Height = Unit.Percentage(100); cell.Style.Add("font-weight", "bold"); cell.Style.Add("background-color", "#DDFFDD"); cell.Style.Add("color", "#006C00"); HtmlGenericControl span = new HtmlGenericControl("span"); if (hdnVendorDetails.Value.Trim().Length > 0) { span.InnerHtml = hdnVendorCode.Value.Trim() + " - " + hdnVendorDetails.Value.Trim(); } else { span.InnerHtml = hdnVendorCode.Value.Trim() + " - " + hdnVendorName.Value.Trim(); } cell.Controls.Add(span); row.Cells.Add(cell); tbl.Rows.AddAt(tbl.Rows.Count - 1, row); } } if (hdnInv_Status.Value.Trim().ToUpper().Equals("INVOICE RECEIVED.")) { imgStatus.ImageUrl = "Images/invoice_receive.png"; imgStatus.ToolTip = "Invoice Received."; } else if (hdnInv_Status.Value.Trim().ToUpper().Equals("PENDING FOR USER/SPONSOR APPROVAL.")) { imgStatus.ImageUrl = "Images/user-sponsor.png"; imgStatus.ToolTip = "Pending for User/Sponsor Approval."; } else if (hdnInv_Status.Value.Trim().ToUpper().Equals("PENDING FOR CLARIFICATION.")) { imgStatus.ImageUrl = "Images/user-reject.png"; imgStatus.ToolTip = "Pending for Clarification."; } else if (hdnInv_Status.Value.Trim().ToUpper().Equals("WAITING FOR INVOICE PROCESSING.")) { imgStatus.ImageUrl = "Images/Pending-Process.png"; imgStatus.ToolTip = "Waiting for Invoice Processing."; } else if (hdnInv_Status.Value.Trim().ToUpper().Equals("INVOICE PROCESSED, WAITING FOR PAYMENT.")) { imgStatus.ImageUrl = "Images/Process-Completes.png"; imgStatus.ToolTip = "Invoice Processed, Waiting for Payment."; } else if (hdnInv_Status.Value.Trim().ToUpper().Equals("INVOICE PAID.")) { imgStatus.ImageUrl = "Images/paid.png"; imgStatus.ToolTip = "Invoice Paid."; } else if (hdnInv_Status.Value.Trim().ToUpper().Equals("INVOICE PROCESSED, NOT DUE FOR PAYMENT.")) { imgStatus.ImageUrl = "Images/clock_red.png"; imgStatus.ToolTip = "Invoice Processed, Not Due for Payment."; } else if (hdnInv_Status.Value.Trim().ToUpper().Equals("INVOICE REJECTED BY SGGI.")) { imgStatus.ImageUrl = "Images/invoice-delete.png"; imgStatus.ToolTip = "Invoice Rejected By SGGI."; } else { imgStatus.ImageUrl = "Images/empty.png"; imgStatus.ToolTip = ""; } if (hdnDelFlags.Value.Trim().ToString().Equals("X")) { imgStatus.ImageUrl = "Images/invoice-delete.png"; imgStatus.ToolTip = "Invoice Delete."; } e.Row.Cells[8].Attributes.Add("style", "cursor:hand"); e.Row.Cells[8].Attributes.Add("onclick", "return ShowImageDescription()"); } }
Excel Download Option public override void VerifyRenderingInServerForm(Control control) { return; } // Upload grid to excel protected void btnExcel_Click(object sender, EventArgs e) { try { Response.ClearContent(); Response.AddHeader("content-disposition", "attachment; filename=Traceinvoicestatus.xls"); string style = @"