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 = @"