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();
}
}
