protected void btnExcel_Click(object sender, EventArgs e) { _cusName = DTRequest.GetFormString("txtCusName"); _cid = DTRequest.GetFormString("hCusId"); _customer = DTRequest.GetFormString("txtCustomer"); _hcustomer = DTRequest.GetFormString("hCustomer"); _type = DTRequest.GetFormString("ddltype"); _sign = DTRequest.GetFormString("ddlsign"); _money1 = DTRequest.GetFormString("txtMoney1"); _nature = DTRequest.GetFormString("ddlnature"); _sdate = DTRequest.GetFormString("txtsDate"); _edate = DTRequest.GetFormString("txteDate"); _sdate1 = DTRequest.GetFormString("txtsDate1"); _edate1 = DTRequest.GetFormString("txteDate1"); _name = DTRequest.GetFormString("txtName"); _address = DTRequest.GetFormString("txtAddress"); _sign1 = DTRequest.GetFormString("ddlsign1"); _money2 = DTRequest.GetFormString("txtMoney2"); _person1 = DTRequest.GetFormString("txtPerson1"); _person2 = DTRequest.GetFormString("txtPerson2"); _person3 = DTRequest.GetFormString("txtPerson3"); _person4 = DTRequest.GetFormString("txtPerson4"); _person5 = DTRequest.GetFormString("txtPerson5"); _oid = DTRequest.GetFormString("txtOrderID"); _chk = DTRequest.GetFormString("txtChk"); _status = DTRequest.GetFormString("ddlstatus"); _lockstatus = DTRequest.GetFormString("ddllock"); _area = DTRequest.GetFormString("ddlarea"); _sdate2 = DTRequest.GetFormString("txtsDate2"); _edate2 = DTRequest.GetFormString("txteDate2"); _check = DTRequest.GetFormString("ddlcheck"); _self = DTRequest.GetFormString("self"); _detail = DTRequest.GetFormString("txtDetails"); string _where = ""; Dictionary <string, string> dict = getDict(out _where); DataTable dt = new BLL.finance().getReconciliationDetail(dict, this.pageSize, this.page, "o_id asc", out this.totalCount, out _p24, out _p25, out _p26, out _p13, out _p14, 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((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "对账金额/") + (_type == "True" ? "应收" : "应付")); headRow.CreateCell(11).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "对账金额/") + (_type == "True" ? "已收" : "已付")); headRow.CreateCell(12).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "对账金额/") + (_type == "True" ? "未收" : "未付")); 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; 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, 10 * 256); sheet.SetColumnWidth(6, 20 * 256); sheet.SetColumnWidth(7, 15 * 256); sheet.SetColumnWidth(8, 15 * 256); sheet.SetColumnWidth(9, 20 * 256); sheet.SetColumnWidth(10, 15 * 256); sheet.SetColumnWidth(11, 15 * 256); sheet.SetColumnWidth(12, 15 * 256); if (dt != null) { int rowIndex = 1; DataTable finDt = null; for (int i = 0; i < dt.Rows.Count; i++) { DataSet ds = new BLL.finance().GetList(0, "fin_oid='" + dt.Rows[i]["o_id"] + "'" + _where, _chk, ""); if (ds != null && ds.Tables.Count > 0) { finDt = ds.Tables[0]; if (finDt != null && finDt.Rows.Count > 0) { for (int j = 0; j < finDt.Rows.Count; j++) { IRow row = sheet.CreateRow(rowIndex); row.HeightInPoints = 22; row.CreateCell(0).SetCellValue(dt.Rows[i]["o_id"].ToString()); row.CreateCell(1).SetCellValue(dt.Rows[i]["c_name"].ToString()); row.CreateCell(2).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(3).SetCellValue(dt.Rows[i]["o_address"].ToString() + "/" + dt.Rows[i]["o_content"].ToString()); row.CreateCell(4).SetCellValue(finDt.Rows[j]["chk"].ToString()); row.CreateCell(5).SetCellValue(finDt.Rows[j]["chkMoney"].ToString()); row.CreateCell(6).SetCellValue(finDt.Rows[j]["na_name"] + "/" + finDt.Rows[j]["fin_detail"]); row.CreateCell(7).SetCellValue(finDt.Rows[j]["fin_illustration"].ToString()); row.CreateCell(8).SetCellValue(finDt.Rows[j]["fin_expression"] + "=" + finDt.Rows[j]["fin_money"]); row.CreateCell(9).SetCellValue(BusinessDict.checkStatus()[Utils.ObjToByte(finDt.Rows[j]["fin_flag"])]); row.CreateCell(10).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "" + dt.Rows[i]["fcMoney"] + "/") + dt.Rows[i]["fin_money"]); row.CreateCell(11).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "" + dt.Rows[i]["chkMoney"] + "/") + dt.Rows[i]["rpd_money"]); row.CreateCell(12).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "" + dt.Rows[i]["unChkMoney"] + "/") + dt.Rows[i]["unReceiptPay"]); 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; rowIndex++; } } } } } MemoryStream file = new MemoryStream(); hssfworkbook.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); }
protected void btnExcel_Click(object sender, EventArgs e) { DataTable dt = new BLL.finance().GetApprovalList(this.pageSize, this.page, "1=1" + CombSqlTxt(), "o_addDate desc,o_id desc", 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.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.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; 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, 10 * 256); sheet.SetColumnWidth(6, 20 * 256); sheet.SetColumnWidth(7, 15 * 256); sheet.SetColumnWidth(8, 15 * 256); sheet.SetColumnWidth(9, 20 * 256); sheet.SetColumnWidth(10, 15 * 256); sheet.SetColumnWidth(11, 15 * 256); sheet.SetColumnWidth(12, 15 * 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(dt.Rows[i]["c_name"].ToString()); row.CreateCell(2).SetCellValue(dt.Rows[i]["o_content"].ToString()); row.CreateCell(3).SetCellValue(dt.Rows[i]["o_contractPrice"].ToString()); 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 BLL.department().getAreaText(dt.Rows[i]["o_place"].ToString())); row.CreateCell(6).SetCellValue(Common.BusinessDict.fStatus()[Utils.ObjToByte(dt.Rows[i]["o_status"])]); row.CreateCell(7).SetCellValue(Common.BusinessDict.pushStatus()[Utils.StrToBool(Utils.ObjectToStr(dt.Rows[i]["o_isPush"]), false)]); row.CreateCell(8).SetCellValue(Common.BusinessDict.checkStatus()[Utils.ObjToByte(dt.Rows[i]["o_flag"])]); row.CreateCell(9).SetCellValue(Common.BusinessDict.lockStatus()[Utils.ObjToByte(dt.Rows[i]["o_lockStatus"])]); row.CreateCell(10).SetCellValue(dt.Rows[i]["op_name"].ToString() + "" + dt.Rows[i]["op_number"].ToString()); row.CreateCell(11).SetCellValue(dt.Rows[i]["count0"].ToString()); row.CreateCell(12).SetCellValue(dt.Rows[i]["count1"].ToString()); 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; } } MemoryStream file = new MemoryStream(); hssfworkbook.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); }
private void RptBind() { if (!this.isSearch) { this.page = DTRequest.GetQueryInt("page", 1); } else { this.page = 1; } BLL.finance bll = new BLL.finance(); string _where = ""; Dictionary <string, string> dict = getDict(out _where); DataTable dt = bll.getReconciliationDetail(dict, this.pageSize, this.page, "o_id asc", out this.totalCount, out _p24, out _p25, out _p26, out _p13, out _p14).Tables[0]; int _colspan = 1; DataTable finDt = null; if (dt.Rows.Count > 0) { #region 拼接列表Html foreach (DataRow dr in dt.Rows) { finDt = bll.GetList(0, "fin_oid='" + dr["o_id"] + "'" + _where, _chk, "").Tables[0]; _colspan = finDt.Rows.Count == 0 ? 1 : finDt.Rows.Count; trHtml += "<tr style=\"text-align: center;\">"; trHtml += "<td rowspan=\"" + _colspan + "\"><input type=\"checkbox\" class=\"checkall\" data-id=\"" + dr["o_id"] + "\" data-finmoney=\"" + dr["fin_money"] + "\" data-rpdmoney=\"" + dr["rpd_money"] + "\" data-unMoney=\"" + dr["unReceiptPay"] + "\" data-unchkmoney=\"" + (string.IsNullOrEmpty(_chk) ? "--" : "" + dr["unChkMoney"] + "") + "\" data-unrpmoney=\"" + (string.IsNullOrEmpty(_chk) ? "" + dr["unReceiptPay"] + "" : "" + dr["unChkMoney"] + "") + "\"/></td>"; trHtml += "<td rowspan=\"" + _colspan + "\"><a href=\"../order/order_edit.aspx?action=" + DTEnums.ActionEnum.Edit.ToString() + "&oID=" + dr["o_id"] + "\"><span class=\"orderstatus_" + dr["o_status"] + "\">" + dr["o_id"] + "</span></a></td>"; trHtml += "<td style=\"text-align: left;\" rowspan=\"" + _colspan + "\">" + dr["c_name"] + "</td>"; trHtml += "<td rowspan=\"" + _colspan + "\">" + ConvertHelper.toDate(dr["o_sdate"]).Value.ToString("yyyy-MM-dd") + "<br/>" + ConvertHelper.toDate(dr["o_edate"]).Value.ToString("yyyy-MM-dd") + "</td>"; trHtml += "<td style=\"text-align: left;\" rowspan=\"" + _colspan + "\">" + dr["o_address"] + "/" + dr["o_content"] + "</td>"; if (finDt == null || finDt.Rows.Count == 0) { trHtml += "<td></td>"; trHtml += "<td></td>"; trHtml += "<td></td>"; //trHtml += "<td></td>"; trHtml += "<td></td>"; trHtml += "<td></td>"; trHtml += "<td></td>"; trHtml += "<td></td>"; } else { _p11 += Utils.ObjToDecimal(finDt.Rows[0]["chkMoney"], 0); trHtml += "<td><input type=\"checkbox\" class=\"check\" data-oid=\"" + dr["o_id"] + "\" data-id=\"" + finDt.Rows[0]["fin_id"] + "\" data-money=\"" + finDt.Rows[0]["fin_money"] + "\" data-chkmoney=\"" + finDt.Rows[0]["chkMoney"] + "\" /></td>"; trHtml += "<td style=\"word-wrap:break-word;word-break:break-all;\"><a href=\"javascript:;\" onclick=\"addFinChk(" + finDt.Rows[0]["fin_id"] + ",'" + finDt.Rows[0]["fin_oid"] + "')\">" + finDt.Rows[0]["chk"] + "</a></td>"; trHtml += "<td style=\"text-align: right;\">" + finDt.Rows[0]["chkMoney"] + "</td>"; //trHtml += "<td>" + ConvertHelper.toDate(finDt.Rows[0]["fin_sdate"]).Value.ToString("yyyy-MM-dd") + "<br/>" + ConvertHelper.toDate(finDt.Rows[0]["fin_edate"]).Value.ToString("yyyy-MM-dd") + "</td>"; trHtml += "<td style=\"text-align: center;\">" + finDt.Rows[0]["na_name"] + "<br/>" + finDt.Rows[0]["fin_detail"] + "</td>"; trHtml += "<td title=\"" + finDt.Rows[0]["fin_illustration"] + "\">" + (finDt.Rows[0]["fin_illustration"].ToString().Length <= 30 ? finDt.Rows[0]["fin_illustration"].ToString() : finDt.Rows[0]["fin_illustration"].ToString().Substring(0, 30)) + "</td>"; trHtml += "<td style=\"word-wrap:break-word;word-break:break-all;\">" + finDt.Rows[0]["fin_expression"] + "<br/>=" + finDt.Rows[0]["fin_money"] + "</td>"; trHtml += "<td><span onmouseover=\"tip_index = layer.tips('审批人:" + finDt.Rows[0]["fin_checkNum"] + "-" + finDt.Rows[0]["fin_checkName"] + "<br/>审批备注:" + finDt.Rows[0]["fin_checkRemark"] + "', this, { time: 0 }); \" onmouseout=\"layer.close(tip_index); \" class=\"check_" + finDt.Rows[0]["fin_flag"] + "\"></span></td>"; } trHtml += "<td style=\"text-align: right;\" rowspan=\"" + _colspan + "\">" + ((string.IsNullOrEmpty(_chk) || _chk == "空")? "" : "<font color='green'>" + dr["fcMoney"] + "/</font>") + "" + dr["fin_money"] + "</td>"; trHtml += "<td style=\"text-align: right;\" rowspan=\"" + _colspan + "\">" + ((string.IsNullOrEmpty(_chk) || _chk == "空")? "" : "<font color='green'>" + dr["chkMoney"] + "/</font>") + "" + dr["rpd_money"] + "</td>"; trHtml += "<td style=\"text-align: right;\" rowspan=\"" + _colspan + "\">" + ((string.IsNullOrEmpty(_chk) || _chk == "空")? "" : "<font color='green'>" + dr["unChkMoney"] + "/</font>") + "" + dr["unReceiptPay"] + "</td>"; trHtml += "</tr>"; if (!string.IsNullOrEmpty(_chk)) { _p12 += Utils.ObjToDecimal(dr["unChkMoney"], 0); } _p21 += Utils.ObjToDecimal(dr["fin_money"], 0); _p22 += Utils.ObjToDecimal(dr["rpd_money"], 0); _p23 += Utils.ObjToDecimal(dr["unReceiptPay"], 0); if (finDt != null && finDt.Rows.Count > 1) { for (int i = 1; i < finDt.Rows.Count; i++) { trHtml += "<tr style=\"text-align: center;\">"; trHtml += "<td><input type=\"checkbox\" class=\"check\" data-oid=\"" + dr["o_id"] + "\" data-id=\"" + finDt.Rows[i]["fin_id"] + "\" data-money=\"" + finDt.Rows[i]["fin_money"] + "\" data-chkmoney=\"" + finDt.Rows[i]["chkMoney"] + "\" /></td>"; trHtml += "<td style=\"word-wrap:break-word;word-break:break-all;\"><a href=\"javascript:;\" onclick=\"addFinChk(" + finDt.Rows[i]["fin_id"] + ",'" + finDt.Rows[i]["fin_oid"] + "')\">" + finDt.Rows[i]["chk"] + "</a></td>"; trHtml += "<td style=\"text-align: right;\">" + finDt.Rows[i]["chkMoney"] + "</td>"; //trHtml += "<td>" + ConvertHelper.toDate(finDt.Rows[i]["fin_sdate"]).Value.ToString("yyyy-MM-dd") + "<br/>" + ConvertHelper.toDate(finDt.Rows[i]["fin_edate"]).Value.ToString("yyyy-MM-dd") + "</td>"; trHtml += "<td style=\"text-align: center;\">" + finDt.Rows[i]["na_name"] + "<br/>" + finDt.Rows[i]["fin_detail"] + "</td>"; trHtml += "<td title=\"" + finDt.Rows[i]["fin_illustration"] + "\">" + (finDt.Rows[i]["fin_illustration"].ToString().Length <= 30 ? finDt.Rows[i]["fin_illustration"].ToString() : finDt.Rows[i]["fin_illustration"].ToString().Substring(0, 30)) + "</td>"; trHtml += "<td style=\"word-wrap:break-word;word-break:break-all;\">" + finDt.Rows[i]["fin_expression"] + "<br/>=" + finDt.Rows[i]["fin_money"] + "</td>"; trHtml += "<td><span onmouseover=\"tip_index = layer.tips('审批人:" + finDt.Rows[i]["fin_checkNum"] + "-" + finDt.Rows[i]["fin_checkName"] + "<br/>审批备注:" + finDt.Rows[i]["fin_checkRemark"] + "', this, { time: 0 }); \" onmouseout=\"layer.close(tip_index); \" class=\"check_" + finDt.Rows[i]["fin_flag"] + "\"></span></td>"; trHtml += "</tr>"; _p11 += Utils.ObjToDecimal(finDt.Rows[i]["chkMoney"], 0); } } } #endregion } else { trHtml = "<tr><td align=\"center\" colspan=\"14\">暂无记录</td></tr>"; } p11.Text = _p11.ToString(); if (!string.IsNullOrEmpty(_chk)) { p12.Text = _p12.ToString(); p14.Text = _p14.ToString(); } else { p12.Text = "--"; p14.Text = "--"; } p13.Text = _chk == "空"?"0":_p13.ToString(); p21.Text = _p21.ToString(); p22.Text = _p22.ToString(); p23.Text = _p23.ToString(); p24.Text = _p24.ToString(); p25.Text = _p25.ToString(); p26.Text = _p26.ToString(); //绑定页码 txtPageNum.Text = this.pageSize.ToString(); string pageUrl = backUrl(); PageContent.InnerHtml = Utils.OutPageList(this.pageSize, this.page, this.totalCount, pageUrl, 8); ddltype.SelectedValue = _type; txtCusName.Text = _cusName; hCusId.Value = _cid; txtCustomer.Text = _customer; hCustomer.Value = _hcustomer; txtsDate.Text = _sdate; txteDate.Text = _edate; txtsDate1.Text = _sdate1; txteDate1.Text = _edate1; txtsDate2.Text = _sdate2; txteDate2.Text = _edate2; //txtsDate3.Text = _sdate3; //txteDate3.Text = _edate3; ddlstatus.SelectedValue = _status; ddlsign.SelectedValue = _sign; txtMoney1.Text = _money1; ddlsign1.SelectedValue = _sign1; txtMoney2.Text = _money2; ddlnature.SelectedValue = _nature; txtChk.Text = _chk; ddlarea.SelectedValue = _area; ddllock.SelectedValue = _lockstatus; txtOrderID.Text = _oid; txtName.Text = _name; txtAddress.Text = _address; txtPerson1.Text = _person1; txtPerson2.Text = _person2; txtPerson3.Text = _person3; txtPerson4.Text = _person4; txtPerson5.Text = _person5; ddlcheck.SelectedValue = _check; txtDetails.Text = _detail; }
protected void btnExcel_Click(object sender, EventArgs e) { _ordernum = DTRequest.GetFormString("txtOrder"); _cusname = DTRequest.GetFormString("txtCusName"); _cusid = DTRequest.GetFormString("hCusId"); _status = DTRequest.GetFormString("ddlcheck"); _type = DTRequest.GetFormString("type"); _smonth = DTRequest.GetFormString("txtsDate"); _emonth = DTRequest.GetFormString("txteDate"); _sdate = DTRequest.GetFormString("txtOsdate"); _edate = DTRequest.GetFormString("txtOedate"); _sign = DTRequest.GetFormString("ddlsign"); _money = DTRequest.GetFormString("txtMoney"); _nid = DTRequest.GetFormString("ddlnature"); _detail = DTRequest.GetFormString("txtDetails"); _ostatus = DTRequest.GetFormString("ddlstatus"); _lock = DTRequest.GetFormString("ddllock"); _area = DTRequest.GetFormString("ddlarea"); _person1 = DTRequest.GetFormString("txtPerson1"); _person3 = DTRequest.GetFormString("txtPerson3"); _person5 = DTRequest.GetFormString("txtPerson5"); _orderarea = DTRequest.GetFormString("ddlorderarea"); _finarea = DTRequest.GetFormString("ddlfinarea"); BLL.finance bll = new BLL.finance(); manager = GetAdminInfo(); DataTable dt = bll.GetList(this.pageSize, this.page, "fin_type='" + (_type == "true" ? "True" : "False") + "'" + CombSqlTxt(), "fin_adddate desc,fin_id desc", manager, out this.totalCount, false).Tables[0]; HttpContext.Current.Response.Clear(); HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + ((_type == "true" ? "应收列表" : "应付列表")) + ".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("" + (_type == "true" ? "应收对象" : "应付对象") + ""); headRow.CreateCell(2).SetCellValue("对账凭证"); headRow.CreateCell(3).SetCellValue("业务性质/明细"); //headRow.CreateCell(4).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.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; 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, 15 * 256); sheet.SetColumnWidth(11, 15 * 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]["fin_oid"].ToString()); row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["c_name"])); row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["chk"])); row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["na_name"]) + "/" + Utils.ObjectToStr(dt.Rows[i]["fin_detail"])); //row.CreateCell(4).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["fin_sdate"]).Value.ToString("yyyy-MM-dd") +"/"+ConvertHelper.toDate(dt.Rows[i]["fin_edate"]).Value.ToString("yyyy-MM-dd")); 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(Utils.ObjectToStr(dt.Rows[i]["fin_illustration"])); row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fin_expression"])); row.CreateCell(7).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fin_money"])); row.CreateCell(8).SetCellValue(dt.Rows[i]["fin_area"].ToString()); row.CreateCell(9).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["fin_month"])); row.CreateCell(10).SetCellValue(dt.Rows[i]["fin_flag"].ToString() == "0" ? "待审批" : dt.Rows[i]["fin_flag"].ToString() == "1" ? "审批未通过" : "审批通过"); row.CreateCell(11).SetCellValue(dt.Rows[i]["fin_personName"].ToString()); 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; } } MemoryStream file = new MemoryStream(); hssfworkbook.Write(file); HttpContext.Current.Response.BinaryWrite(file.GetBuffer()); HttpContext.Current.Response.End(); }