public void BindMenuItem(int profitCenter) { if (Session["menuContent"] != null) { cssmenu.InnerHtml = Session["menuContent"].ToString(); return; } ReportDB _db = new ReportDB(); DataTable dt = _db.GetMenuItem(profitCenter); StringBuilder sb = new StringBuilder(); sb.Append("<ul>"); ArrayList str = new ArrayList(); for (int i = 0; i < dt.Rows.Count; i++) { string strFyear = dt.Rows[i][0].ToString(); string[] arrFy = strFyear.Split('('); if (arrFy.Length > 1) { if (!str.Contains(arrFy[0])) { if (str.Count == 0) sb.Append("<li class='has-sub'><a href='#'><span>" + arrFy[0] + "</span></a><ul>"); else sb.Append("</ul></li><li class='has-sub'><a href='#'><span>" + arrFy[0] + "</span></a><ul>"); str.Add(arrFy[0]); } sb.Append("<li class='has-sub'><a href='" + string.Format("Report.aspx?centerid={0}&tfName={1}&unit={2}&role={3}&link=menu", profitCenter, dt.Rows[i][0].ToString(), strRsType, strRole) + "'><span>" + dt.Rows[i][0].ToString() + "</span></a></li>"); } else { sb.Append("<li><a href='" + dt.Rows[i][0].ToString() + "'><span>" + dt.Rows[i][0].ToString() + "</span></a><ul>"); } } sb.Append("</ul> </li></ul>"); Session["menuContent"] = sb.ToString(); cssmenu.InnerHtml = sb.ToString(); }
protected void btnReport_Click(object sender, EventArgs e) { ReportDB _db = new ReportDB(); DataTable formTable = _db.GetMenuItem(pcId); DataSet ds = null; string actualYear = ConfigurationManager.AppSettings["ActualYear"]; ////Create new Excel Workbook var workbook = new HSSFWorkbook(); CellStyle cellStyleBold = workbook.CreateCellStyle(); Font boldFont = workbook.CreateFont(); boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; cellStyleBold.SetFont(boldFont); foreach (DataRow item in formTable.Rows) { HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(item[0].ToString()); //Getting reportData ds = new ReportDB().GetReportDataForExcel(pcId, item[0].ToString(), Convert.ToInt32(actualYear), unit); //setting width of cloumns in excel sheet.SetColumnWidth(0, 25 * 256); for (int i = 2; i < ds.Tables[0].Columns.Count; i++) { sheet.SetColumnWidth(i, 15 * 256); } HSSFRow firstRow = (HSSFRow)sheet.CreateRow(0); firstRow.CreateCell(1).SetCellValue(headerContent); firstRow.GetCell(1).CellStyle = cellStyleBold; firstRow.GetCell(1).CellStyle.WrapText = true; //firstRow.GetCell(1).Row.Height = 800; NPOI.SS.Util.CellRangeAddress cra = new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 5); sheet.AddMergedRegion(cra); HSSFRow thirdRow = (HSSFRow)sheet.CreateRow(2); thirdRow.CreateCell(0).SetCellValue(string.Format(ConfigurationManager.AppSettings["formName"], tfName.ToUpper())); thirdRow.GetCell(0).CellStyle = cellStyleBold; HSSFRow forthRow = (HSSFRow)sheet.CreateRow(3); if (ds.Tables[2].Rows.Count > 0) { forthRow.CreateCell(0).SetCellValue(Convert.ToString(ds.Tables[2].Rows[0][0])); forthRow.GetCell(0).CellStyle = cellStyleBold; } HSSFRow fifthRow = (HSSFRow)sheet.CreateRow(5); fifthRow.CreateCell(1).SetCellValue(GetActualYear(actualYear)); for (int i = 2; i < ds.Tables[1].Rows.Count + 2; i++) { var row = ds.Tables[1].Rows[i - 2]; fifthRow.CreateCell(i).SetCellValue(Convert.ToString(row[0])); } // handling value. int rowIndex = 6; HSSFRow sixthRow = (HSSFRow)sheet.CreateRow(rowIndex); for (int i = 0; i < ds.Tables[0].Columns.Count - 1; i++) { sixthRow.CreateCell(i).SetCellValue(ds.Tables[0].Columns[i].ColumnName); sixthRow.GetCell(i).CellStyle = cellStyleBold; } foreach (DataRow headerRow in ds.Tables[3].Rows) { string filterExp = string.Format("MainHead_tariff_Name='{0}'", headerRow[0]); HSSFRow subHeadRow = (HSSFRow)sheet.CreateRow(++rowIndex); subHeadRow.CreateCell(0).SetCellValue(Convert.ToString(headerRow[0])); subHeadRow.GetCell(0).CellStyle = cellStyleBold; foreach (DataRow row in ds.Tables[0].Select(filterExp)) { HSSFRow dataRow = (HSSFRow)sheet.CreateRow(++rowIndex); foreach (DataColumn column in ds.Tables[0].Columns) { if (column.ColumnName == "MainHead_tariff_Name") continue; if (!System.DBNull.Value.Equals(row[column])) { if (column.DataType == typeof(Nullable)) dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(row[column])); else if (column.DataType == typeof(DateTime)) dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToDateTime(row[column])); else if (column.DataType == typeof(decimal)) dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToDouble(row[column])); else dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(row[column])); } } } HSSFRow subTotalRow = (HSSFRow)sheet.CreateRow(++rowIndex); subTotalRow.CreateCell(0).SetCellValue("Sub Total"); subTotalRow.GetCell(0).CellStyle = cellStyleBold; foreach (DataColumn column in ds.Tables[0].Columns) { } for (int i = 1; i < ds.Tables[0].Columns.Count - 1; i++) { if (headerRow[i] != DBNull.Value) { subTotalRow.CreateCell(i).SetCellValue(Convert.ToDouble(headerRow[i])); subTotalRow.GetCell(i).CellStyle = cellStyleBold; } } HSSFRow blankRow = (HSSFRow)sheet.CreateRow(++rowIndex); } } //Write the Workbook to a memory stream MemoryStream output = new MemoryStream(); workbook.Write(output); //return File(output.ToArray(), //The binary data of the XLS file //"application/vnd.ms-excel",//MIME type of Excel files //"ArticleList.xls"); string fileName = "report.xls"; const int bufferLength = 10000; byte[] buffer = new Byte[bufferLength]; int length = 0; if (!string.IsNullOrEmpty(Convert.ToString(Session["centername"]))) { fileName = Convert.ToString(Session["centername"]) + ".xls"; } Response.ContentType = "Application/x-msexcel"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ""); Stream download = null; try { using (FileStream file = new FileStream(Server.MapPath("/Export/" + fileName + ""), FileMode.Create, FileAccess.Write)) { output.WriteTo(file); } download = new FileStream(Server.MapPath("/Export/" + fileName + ""), FileMode.Open, FileAccess.Read); do { if (Response.IsClientConnected) { length = download.Read(buffer, 0, bufferLength); Response.OutputStream.Write(buffer, 0, length); buffer = new Byte[bufferLength]; } else { length = -1; } } while (length > 0); Response.Flush(); Response.End(); } finally { if (download != null) download.Close(); } BindGrid(); }
private void BindGrid() { try { ReportDB _db = new ReportDB(); string actualYear = ConfigurationManager.AppSettings["ActualYear"]; DataSet dt = _db.GetExpenseData(pcId, tfName, Convert.ToInt32(actualYear), unit); if (dt.Tables[2].Rows.Count > 0) lblHeaderName.Text = Convert.ToString(dt.Tables[2].Rows[0][0]); if (dt.Tables[0] != null) { GridView1.DataSource = dt.Tables[0]; GridView1.DataBind(); if (dt.Tables[0].Rows.Count > 0) { GridView1.HeaderRow.Cells[1].Text = GetActualYear(actualYear) + "<br/>" + GridView1.HeaderRow.Cells[1].Text; for (int i = 0; i < dt.Tables[1].Rows.Count; i++) { GridView1.HeaderRow.Cells[i + 2].Text = dt.Tables[1].Rows[i][0] + "<br/>" + GridView1.HeaderRow.Cells[i + 2].Text; } //making particular bold //GridView1.Rows[GridView1.Rows.Count - 1].Cells[0].Font.Bold = true; //making total row bold foreach (TableCell item in GridView1.Rows[GridView1.Rows.Count - 1].Cells) { //item.Font.Bold = true; } } } } catch (Exception ex) { throw; } }
protected void btnUpdate_Click(object sender, EventArgs e) { if (Page.IsValid) { ReportDB _db = new ReportDB(); _db.UpdateReportData(Convert.ToInt32(hidden.Value), GetDecimalValue(txt1.Text), GetDecimalValue(TextBox1.Text), GetDecimalValue(TextBox2.Text), GetDecimalValue(TextBox3.Text), GetDecimalValue(TextBox4.Text), GetDecimalValue(TextBox5.Text)); BindGrid(); } }