protected void btnExcel_Click(object sender, EventArgs e)
{
_orderid = DTRequest.GetFormString("txtOrderID");
_cusName = DTRequest.GetFormString("txtCusName");
_cid = DTRequest.GetFormString("hCusId");
_status = DTRequest.GetFormString("ddlstatus");
_dstatus = DTRequest.GetFormString("ddldstatus");
_lockstatus = DTRequest.GetFormString("ddllock");
_pushstatus = DTRequest.GetFormString("ddlispush");
_flag = DTRequest.GetFormString("ddlflag");
_content = DTRequest.GetFormString("txtContent");
_address = DTRequest.GetFormString("txtAddress");
_sign = DTRequest.GetFormString("ddlsign");
_money = DTRequest.GetFormString("txtMoney");
_person1 = DTRequest.GetFormString("txtPerson1");
_person3 = DTRequest.GetFormString("txtPerson3");
_person5 = DTRequest.GetFormString("txtPerson5");
_sdate = DTRequest.GetFormString("txtsDate");
_edate = DTRequest.GetFormString("txteDate");
_sdate1 = DTRequest.GetFormString("txtsDate1");
_edate1 = DTRequest.GetFormString("txteDate1");
_area = DTRequest.GetFormString("ddlarea");
_sign1 = DTRequest.GetFormString("ddlsign1");
_money1 = DTRequest.GetFormString("txtMoney1");
_sign2 = DTRequest.GetFormString("ddlsign2");
_money2 = DTRequest.GetFormString("txtMoney2");
_sign3 = DTRequest.GetFormString("ddlsign3");
_money3 = DTRequest.GetFormString("txtMoney3");
_sign4 = DTRequest.GetFormString("ddlsign4");
_money4 = DTRequest.GetFormString("txtMoney4");
_sign5 = DTRequest.GetFormString("ddlsign5");
_money5 = DTRequest.GetFormString("txtMoney5");
_orderarea = DTRequest.GetFormString("ddlorderarea");
_sdate2 = DTRequest.GetFormString("txtsDate2");
_edate2 = DTRequest.GetFormString("txteDate2");
BLL.statisticBLL bll = new BLL.statisticBLL();
DataTable dt = bll.GetList(this.pageSize, this.page, "1=1" + CombSqlTxt(), "o_addDate desc,o_id desc", out this.totalCount, out money1, out money2, out money3, out money4, out money5, out money6, 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.CreateCell(5).SetCellValue("归属地");
headRow.CreateCell(6).SetCellValue("订单状态");
headRow.CreateCell(7).SetCellValue("锁单状态");
headRow.CreateCell(8).SetCellValue("业务员");
headRow.CreateCell(9).SetCellValue("策划人员");
headRow.CreateCell(10).SetCellValue("设计人员");
headRow.CreateCell(11).SetCellValue("应收款");
headRow.CreateCell(12).SetCellValue("未收款");
headRow.CreateCell(13).SetCellValue("应付款");
headRow.CreateCell(14).SetCellValue("未付款");
headRow.CreateCell(15).SetCellValue("税费");
headRow.CreateCell(16).SetCellValue("业绩利润");
headRow.CreateCell(17).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;
headRow.GetCell(10).CellStyle = titleCellStyle;
headRow.GetCell(11).CellStyle = titleCellStyle;
headRow.GetCell(12).CellStyle = titleCellStyle;
headRow.GetCell(13).CellStyle = titleCellStyle;
headRow.GetCell(14).CellStyle = titleCellStyle;
headRow.GetCell(15).CellStyle = titleCellStyle;
headRow.GetCell(16).CellStyle = titleCellStyle;
headRow.GetCell(17).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, 15 * 256);
sheet.SetColumnWidth(6, 20 * 256);
sheet.SetColumnWidth(7, 20 * 256);
sheet.SetColumnWidth(8, 20 * 256);
sheet.SetColumnWidth(9, 20 * 256);
sheet.SetColumnWidth(10, 20 * 256);
sheet.SetColumnWidth(11, 20 * 256);
sheet.SetColumnWidth(12, 20 * 256);
sheet.SetColumnWidth(13, 20 * 256);
sheet.SetColumnWidth(14, 20 * 256);
sheet.SetColumnWidth(15, 20 * 256);
sheet.SetColumnWidth(16, 20 * 256);
sheet.SetColumnWidth(17, 25 * 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]["o_id"].ToString());
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["o_content"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["o_address"]));
row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["c_name"]));
row.CreateCell(4).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["o_sdate"]).Value.ToString("yyyy-MM-dd") + "/" + ConvertHelper.toDate(dt.Rows[i]["o_edate"]).Value.ToString("yyyy-MM-dd"));
row.CreateCell(5).SetCellValue(new MettingSys.BLL.department().getAreaText(dt.Rows[i]["o_place"].ToString()));
row.CreateCell(6).SetCellValue(BusinessDict.fStatus()[Utils.ObjToByte(dt.Rows[i]["o_status"])]);
row.CreateCell(7).SetCellValue(BusinessDict.lockStatus()[Utils.ObjToByte(dt.Rows[i]["o_lockStatus"])]);
row.CreateCell(8).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["op_name"]));
row.CreateCell(9).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["person3"]));
row.CreateCell(10).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["person4"]));
row.CreateCell(11).SetCellValue(Utils.ObjToDecimal(dt.Rows[i]["shou"], 0).ToString());
row.CreateCell(12).SetCellValue(Utils.ObjToDecimal(dt.Rows[i]["weishou"], 0).ToString());
row.CreateCell(13).SetCellValue(Utils.ObjToDecimal(dt.Rows[i]["fu"], 0).ToString());
row.CreateCell(14).SetCellValue(Utils.ObjToDecimal(dt.Rows[i]["weifu"], 0).ToString());
row.CreateCell(15).SetCellValue(Utils.ObjToDecimal(dt.Rows[i]["o_financeCust"], 0).ToString());
row.CreateCell(16).SetCellValue(Utils.ObjToDecimal(dt.Rows[i]["profit"], 0).ToString());
row.CreateCell(17).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["o_statusTime"]) == "" ? "" : Utils.ObjectToDateTime(dt.Rows[i]["o_statusTime"]).ToString("yyyy-MM-dd HH:mm:ss"));
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;
row.GetCell(10).CellStyle = cellStyle;
row.GetCell(11).CellStyle = cellStyle;
row.GetCell(12).CellStyle = cellStyle;
row.GetCell(13).CellStyle = cellStyle;
row.GetCell(14).CellStyle = cellStyle;
row.GetCell(15).CellStyle = cellStyle;
row.GetCell(16).CellStyle = cellStyle;
row.GetCell(17).CellStyle = cellStyle;
}
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
private void RptBind(string _strWhere, string _orderby)
{
this.page = DTRequest.GetQueryInt("page", 1);
BLL.statisticBLL bll = new BLL.statisticBLL();
DataTable dt = bll.GetList(this.pageSize, this.page, _strWhere, _orderby, out this.totalCount, out money1, out money2, out money3, out money4, out money5, out money6).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);
pCount.Text = dt.Rows.Count.ToString();
decimal _pmoney1 = 0, _pmoney2 = 0, _pmoney3 = 0, _pmoney4 = 0, _pmoney5 = 0, _pmoney6 = 0;
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
_pmoney1 += Utils.ObjToDecimal(dr["shou"], 0);
_pmoney2 += Utils.ObjToDecimal(dr["weishou"], 0);
_pmoney3 += Utils.ObjToDecimal(dr["fu"], 0);
_pmoney4 += Utils.ObjToDecimal(dr["weifu"], 0);
_pmoney5 += Utils.ObjToDecimal(dr["o_financeCust"], 0);
_pmoney6 += Utils.ObjToDecimal(dr["profit"], 0);
}
}
tCount.Text = totalCount.ToString();
pMoney1.Text = _pmoney1.ToString();
pMoney2.Text = _pmoney2.ToString();
pMoney3.Text = _pmoney3.ToString();
pMoney4.Text = _pmoney4.ToString();
pMoney5.Text = _pmoney5.ToString();
pMoney6.Text = _pmoney6.ToString();
tMoney1.Text = money1.ToString();
tMoney2.Text = money2.ToString();
tMoney3.Text = money3.ToString();
tMoney4.Text = money4.ToString();
tMoney5.Text = money5.ToString();
tMoney6.Text = money6.ToString();
txtOrderID.Text = _orderid;
txtCusName.Text = _cusName;
hCusId.Value = _cid;
ddlstatus.SelectedValue = _status;
ddldstatus.SelectedValue = _dstatus;
ddllock.SelectedValue = _lockstatus;
txtContent.Text = _content;
txtAddress.Text = _address;
ddlsign.SelectedValue = _sign;
txtMoney.Text = _money;
txtPerson1.Text = _person1;
txtPerson3.Text = _person3;
txtPerson5.Text = _person5;
txtsDate.Text = _sdate;
txteDate.Text = _edate;
txtsDate1.Text = _sdate1;
txteDate1.Text = _edate1;
ddlarea.SelectedValue = _area;
ddlsign1.SelectedValue = _sign1;
txtMoney1.Text = _money1;
ddlsign2.SelectedValue = _sign2;
txtMoney2.Text = _money2;
ddlsign3.SelectedValue = _sign3;
txtMoney3.Text = _money3;
ddlsign4.SelectedValue = _sign4;
txtMoney4.Text = _money4;
ddlsign5.SelectedValue = _sign5;
txtMoney5.Text = _money5;
ddlorderarea.SelectedValue = _orderarea;
ddlmethod.SelectedValue = _method;
}
