private void RptBind(string _strWhere, string _orderby) { this.page = DTRequest.GetQueryInt("page", 1); BLL.statisticBLL bll = new BLL.statisticBLL(); string _where = ""; Dictionary <string, string> dict = getDict(); DataTable dt = bll.getAchievementStatisticData(dict, this.pageSize, this.page, _orderby, out this.totalCount, out _tOrderCount, out _tOrderShou, out _tUnIncome, out _tOrderFu, out _tUnCost, out _tOrderProfit, out _tCust, out _tProfit).Tables[0]; rptList.DataSource = dt; rptList.DataBind(); //绑定页码 txtPageNum.Text = this.pageSize.ToString(); string pageUrl = Utils.CombUrlTxt("AchievementStatistic_list.aspx", "page={0}&txtsDate={1}&txteDate={2}&ddlstatus={3}&ddllock={4}&hide_place={5}&hide_employee2={6}&cbIsRemove={7}&cbIsCust={8}&action={9}&ddltype={10}", "__id__", _sMonth, _eMonth, _status, _lockstatus, _area, _person, _isRemove, _isCust, action, _type); PageContent.InnerHtml = Utils.OutPageList(this.pageSize, this.page, this.totalCount, pageUrl, 8); if (dt != null) { foreach (DataRow dr in dt.Rows) { _pOrderCount += Utils.ObjToInt(dr["oCount"]); _pOrderShou += Utils.ObjToDecimal(dr["shou"], 0); _pUnIncome += Utils.ObjToDecimal(dr["unIncome"], 0); _pOrderFu += Utils.ObjToDecimal(dr["fu"], 0); _pUnCost += Utils.ObjToDecimal(dr["unCost"], 0); _pOrderProfit += Utils.ObjToDecimal(dr["oProfit"], 0); _pCust += Utils.ObjToDecimal(dr["oCust"], 0); _pProfit += Utils.ObjToDecimal(dr["bProfit"], 0); } } pCount.Text = dt.Rows.Count.ToString(); pOrderCount.Text = _pOrderCount.ToString(); pOrderShou.Text = _pOrderShou.ToString(); pUnIncome.Text = _pUnIncome.ToString(); pOrderFu.Text = _pOrderFu.ToString(); pUnCost.Text = _pUnCost.ToString(); pOrderProfit.Text = _pOrderProfit.ToString(); pCust.Text = _pCust.ToString(); pProfit.Text = _pProfit.ToString(); tCount.Text = totalCount.ToString(); tOrderCount.Text = _tOrderCount.ToString(); tOrderShou.Text = _tOrderShou.ToString(); tUnIncome.Text = _tUnIncome.ToString(); tOrderFu.Text = _tOrderFu.ToString(); tUnCost.Text = _tUnCost.ToString(); tOrderProfit.Text = _tOrderProfit.ToString(); tCust.Text = _tCust.ToString(); tProfit.Text = _tProfit.ToString(); }
protected void Excel() { BLL.statisticBLL bll = new BLL.statisticBLL(); Dictionary <string, string> dict = getDict(); DataTable dt = bll.getAchievementStatisticData(dict, this.pageSize, this.page, "op_number asc", out this.totalCount, out _tOrderCount, out _tOrderShou, out _tUnIncome, out _tOrderFu, out _tUnCost, out _tOrderProfit, out _tCust, out _tProfit, false).Tables[0]; string fileName = "员工业绩统计-下单"; if (_type == "1") { fileName = "员工业绩统计-接单(业务策划)"; } else if (_type == "2") { fileName = "员工业绩统计-接单(业务设计)"; } HttpContext.Current.Response.Clear(); HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + 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.CreateCell(5).SetCellValue("应付"); headRow.CreateCell(6).SetCellValue("非考核成本"); headRow.CreateCell(7).SetCellValue("订单利润"); headRow.CreateCell(8).SetCellValue("订单税费"); headRow.CreateCell(9).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; headRow.GetCell(5).CellStyle = titleCellStyle; headRow.GetCell(6).CellStyle = titleCellStyle; headRow.GetCell(7).CellStyle = titleCellStyle; headRow.GetCell(8).CellStyle = titleCellStyle; headRow.GetCell(9).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); sheet.SetColumnWidth(5, 20 * 256); sheet.SetColumnWidth(6, 20 * 256); sheet.SetColumnWidth(7, 20 * 256); sheet.SetColumnWidth(8, 20 * 256); sheet.SetColumnWidth(9, 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]["op_area"]) + "-" + new BLL.department().getAreaText(Utils.ObjectToStr(dt.Rows[i]["op_area"]))); row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["oCount"])); row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["shou"])); row.CreateCell(4).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["unIncome"])); row.CreateCell(5).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fu"])); row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["unCost"])); row.CreateCell(7).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["oProfit"])); row.CreateCell(8).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["oCust"])); row.CreateCell(9).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["bProfit"])); 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; row.GetCell(5).CellStyle = cellStyle; row.GetCell(6).CellStyle = cellStyle; row.GetCell(7).CellStyle = cellStyle; row.GetCell(8).CellStyle = cellStyle; row.GetCell(9).CellStyle = cellStyle; } } MemoryStream file = new MemoryStream(); hssfworkbook.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); }