private void RptBind(string _strWhere, string _orderby)
{
this.page = DTRequest.GetQueryInt("page", 1);
BLL.statisticBLL bll = new BLL.statisticBLL();
DataTable dt = bll.getReceiveOrderAnalyzeData(this.pageSize, this.page, _strWhere, _orderby, out _tcount3, out _tcount5, out _tcount, out this.totalCount).Tables[0];
this.rptList.DataSource = dt;
this.rptList.DataBind();
//绑定页码
txtPageNum.Text = this.pageSize.ToString();
string pageUrl = backUrl();
PageContent.InnerHtml = Utils.OutPageList(this.pageSize, this.page, this.totalCount, pageUrl, 8);
int _pcount3 = 0, _pcount5 = 0, _pcount = 0;
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
_pcount3 += Utils.ObjToInt(dr["type3"], 0);
_pcount5 += Utils.ObjToInt(dr["type5"], 0);
_pcount += Utils.ObjToInt(dr["sumType"], 0);
}
}
pCount.Text = dt.Rows.Count.ToString();
pOrder3Count.Text = _pcount3.ToString();
pOrder5Count.Text = _pcount5.ToString();
pOrderCount.Text = _pcount.ToString();
tCount.Text = this.totalCount.ToString();
tOrder3Count.Text = _tcount3.ToString();
tOrder5Count.Text = _tcount5.ToString();
tOrderCount.Text = _tcount.ToString();
txtsDate.Text = _sdate;
txteDate.Text = _edate;
txtPerson.Text = _person;
txtDepart.Text = _depart;
ddlstatus.SelectedValue = _status;
ddldstatus.SelectedValue = _dstatus;
ddllock.SelectedValue = _lockstatus;
ddlarea.SelectedValue = _area;
ddlorderarea.SelectedValue = _orderarea;
}
protected void btnExcel_Click(object sender, EventArgs e)
{
_sdate = DTRequest.GetString("txtsDate");
_edate = DTRequest.GetString("txteDate");
_person = DTRequest.GetString("txtPerson");
_depart = DTRequest.GetString("txtDepart");
_status = DTRequest.GetString("ddlstatus");
_dstatus = DTRequest.GetString("ddldstatus");
_lockstatus = DTRequest.GetString("ddllock");
_area = DTRequest.GetString("ddlarea");
_orderarea = DTRequest.GetString("ddlorderarea");
BLL.statisticBLL bll = new BLL.statisticBLL();
DataTable dt = bll.getReceiveOrderAnalyzeData(this.pageSize, this.page, CombSqlTxt(), " op_number asc", out _tcount3, out _tcount5, out _tcount, out this.totalCount, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=策划与设计.xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("明细");
IFont font = hssfworkbook.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 11;
#region 表格样式
//设置单元格的样式：水平垂直对齐居中
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BottomBorderColor = HSSFColor.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.Black.Index;
cellStyle.RightBorderColor = HSSFColor.Black.Index;
cellStyle.TopBorderColor = HSSFColor.Black.Index;
cellStyle.WrapText = true;//自动换行
//设置表头的样式：水平垂直对齐居中，加粗
ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle();
titleCellStyle.Alignment = HorizontalAlignment.Center;
titleCellStyle.VerticalAlignment = VerticalAlignment.Center;
titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色
titleCellStyle.FillPattern = FillPattern.SparseDots; //图案样式
titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色
//设置边框
titleCellStyle.BorderBottom = BorderStyle.Thin;
titleCellStyle.BorderLeft = BorderStyle.Thin;
titleCellStyle.BorderRight = BorderStyle.Thin;
titleCellStyle.BorderTop = BorderStyle.Thin;
titleCellStyle.BottomBorderColor = HSSFColor.Black.Index;
titleCellStyle.LeftBorderColor = HSSFColor.Black.Index;
titleCellStyle.RightBorderColor = HSSFColor.Black.Index;
titleCellStyle.TopBorderColor = HSSFColor.Black.Index;
//设置字体
titleCellStyle.SetFont(font);
#endregion
//表头
IRow headRow = sheet.CreateRow(0);
headRow.HeightInPoints = 25;
headRow.CreateCell(0).SetCellValue("人员");
headRow.CreateCell(1).SetCellValue("岗位");
headRow.CreateCell(2).SetCellValue("策划订单数");
headRow.CreateCell(3).SetCellValue("设计订单数");
headRow.CreateCell(4).SetCellValue("合计订单数");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
headRow.GetCell(3).CellStyle = titleCellStyle;
headRow.GetCell(4).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(dt.Rows[i]["op_number"].ToString() + "(" + dt.Rows[i]["op_name"].ToString() + ")");
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["detaildepart"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["type3"]));
row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["type5"]));
row.CreateCell(4).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["sumType"]));
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
row.GetCell(3).CellStyle = cellStyle;
row.GetCell(4).CellStyle = cellStyle;
}
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
