/// <summary>
/// 多列表头 过滤部分数据
/// </summary>
/// <returns></returns>
public ActionResult Down2()
{
List <TestExportData2> datas = new List <TestExportData2>()
{
new TestExportData2()
{
UserName = "******", Address = "tests地址1", Age = "12", bloodSugar = "2333", HeartRate = "332", id = 1, Sex = 0
},
new TestExportData2()
{
UserName = "******", Address = "tests地址12", Age = "12", bloodSugar = "2333", HeartRate = "332", id = 2, Sex = 0
},
new TestExportData2()
{
UserName = "******", Address = "tests地址4", Age = "12", bloodSugar = "33", HeartRate = "33", id = 3, Sex = 1
},
new TestExportData2()
{
UserName = "******", Address = "tests地址5", Age = "12", bloodSugar = "44", HeartRate = "44", id = 4, Sex = 1
}
};
string[,] header = new string[2, 5];
header[0, 0] = "用户基本信息";
header[0, 3] = "用户身体状况";
header[1, 0] = "用户名字";
header[1, 1] = "用户年龄";
header[1, 2] = "用户地址";
header[1, 3] = "心率";
header[1, 4] = "血糖";
var bytes = NOPIHelper.Export <TestExportData2>(header, datas, c => new { c.UserName, c.Address, c.Age, c.bloodSugar, c.HeartRate });
return(File(bytes, "application/vnd.ms-excel", "用户信息.xls"));
}
/// <summary>
/// 多列表头并且合并单元格导出
/// </summary>
/// <returns></returns>
public ActionResult Down1()
{
List <TestExportData> datas = new List <TestExportData>()
{
new TestExportData()
{
UserName = "******", Address = "tests地址1", Age = "12", bloodSugar = "2333", HeartRate = "332"
},
new TestExportData()
{
UserName = "******", Address = "tests地址12", Age = "12", bloodSugar = "2333", HeartRate = "332"
},
new TestExportData()
{
UserName = "******", Address = "tests地址4", Age = "12", bloodSugar = "33", HeartRate = "33"
},
new TestExportData()
{
UserName = "******", Address = "tests地址5", Age = "12", bloodSugar = "44", HeartRate = "44"
}
};
string[,] header = new string[2, 5];
header[0, 0] = "用户基本信息";
header[0, 3] = "用户身体状况";
header[1, 0] = "用户名字";
header[1, 1] = "用户年龄";
header[1, 2] = "用户地址";
header[1, 3] = "心率";
header[1, 4] = "血糖";
var bytes = NOPIHelper.Export <TestExportData>(header, datas, null);
return(File(bytes, "application/vnd.ms-excel", "用户信息.xls"));
}
public ActionResult Down4()
{
List <TestExportData2> datas = new List <TestExportData2>()
{
new TestExportData2()
{
UserName = "******", Address = "tests地址1", Age = "12", bloodSugar = "2333", HeartRate = "332", id = 1, Sex = 0
},
new TestExportData2()
{
UserName = "******", Address = "tests地址12", Age = "12", bloodSugar = "2333", HeartRate = "332", id = 2, Sex = 0
},
new TestExportData2()
{
UserName = "******", Address = "tests地址4", Age = "12", bloodSugar = "33", HeartRate = "33", id = 3, Sex = 1
},
new TestExportData2()
{
UserName = "******", Address = "tests地址5", Age = "12", bloodSugar = "44", HeartRate = "44", id = 4, Sex = 1
}
};
Dictionary <string, string> dic = new Dictionary <string, string>();
dic.Add("address", "南方医院");
dic.Add("phone", "13243868978");
dic.Add("username", "李强");
dic.Add("phoneb", "13128273410");
dic.Add("user", "小明");
dic.Add("exportdate", DateTime.Now.ToString());
var bytes = NOPIHelper.Export <TestExportData2>(HttpContext.Server.MapPath("/Temp/测试单模板.xls"), 3, dic, datas, datas.Count,
c => new { c.UserName, c.Address, c.Age, c.bloodSugar, c.HeartRate });
return(File(bytes, "application/vnd.ms-excel", "用户信息.xls"));
}
public void SetRowStyleTest()
{
string _path = string.Format(@"{0}NOPI.xlsx", UnitTestHelper.GetExecuteDirectory());
IWorkbook _workbook = NOPIHelper.GetExcelWorkbook(_path);
using (FileStream file = new FileStream(_path, FileMode.Create))
{
IRow _row = _workbook.GetSheetAt(0).GetRow(1);
NOPIHelper.SetRowStyle(_row, CreateCellStly(_workbook));
_workbook.Write(file);
}
}
public void ProcessRequest(HttpContext context)
{
LoginUser loginUser = new LoginUser(context, "SalesInvoiceSummaryReport");
LotteryOrderBLL bll = new LotteryOrderBLL(context, loginUser);
//加载DataGrid
if (context.Request["action"] == "gridLoad")
{
int page = int.Parse(context.Request["page"]);
int rows = int.Parse(context.Request["rows"]);
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
string city = context.Request["cityId"];
string county = context.Request["countyId"];
string siteId = context.Request["siteId"];
string agentId = context.Request["agentId"];
string terminalId = context.Request["terminalId"];
bll.SalesInvoiceSummaryLoadGrid(page, rows, startDate, endDate, city, county, siteId, agentId, terminalId);
}
//获取销售总计
if (context.Request["action"] == "totalSale")
{
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
string city = context.Request["cityId"];
string county = context.Request["countyId"];
string siteId = context.Request["siteId"];
string agentId = context.Request["agentId"];
string terminalId = context.Request["terminalId"];
DataTable dt = bll.GetCommissionTotalSale(startDate, endDate, city, county, siteId, agentId, terminalId);
DataRow dr = dt.Rows[0];
context.Response.Write("{\"totalSumbetfee\":" + dr["totalSumbetfee"] + ",\"totalCommissionFee\":" + dr["totalCommissionFee"] + ",\"totalAwardAmount\":" + dr["totalAwardAmount"] + ",\"totalSumTakeCashFee\":" + dr["totalSumTakeCashFee"] + ",\"totalRefundAmount\":" + dr["totalRefundAmount"] + "}");
}
//加载市
if (context.Request["action"] == "cityListLoad")
{
Combobox com = new Combobox(context, loginUser);
com.CityCombobox();
}
//加载区县
if (context.Request["action"] == "countyListLoad")
{
string cityId = context.Request["cityId"];
Combobox com = new Combobox(context, loginUser);
com.CountyCombobox(cityId);
}
//加载执法文书类型
if (context.Request["action"] == "siteListLoad")
{
Combobox com = new Combobox(context, loginUser);
string cityId = context.Request["cityId"];
string countyId = context.Request["countyId"];
com.SiteByAreaCombobox(cityId, countyId);
}
if (context.Request["action"] == "ToExcel")
{
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
string city = context.Request["cityId"];
string county = context.Request["countyId"];
string siteId = context.Request["siteId"];
string agentId = context.Request["agentId"];
string terminalId = context.Request["terminalId"];
AgentAccDetailBLL agentAccDetailBLL = new AgentAccDetailBLL(context, loginUser);
DataTable dt = bll.SalesInvoiceSummaryReport(startDate, endDate, city, county, siteId, agentId, terminalId);
//获取预存款余额
List <TTAgentAccDetail> balanceList = agentAccDetailBLL.GetTTAgentAccDetailList(city, county, siteId, agentId, terminalId);
int dtRowsCount = dt.Rows.Count + 5;
context.Response.ContentType = "application/x-excel";
string fileName = HttpUtility.UrlEncode(startDate + "至" + endDate + "销售统计报表.xls");
context.Response.AddHeader("Content-Disposition", "attachment; fileName=" + fileName);
IWorkbook workbook = new HSSFWorkbook();
//创建表
ISheet sheet = workbook.CreateSheet("销售统计报表");
//设置单元的宽度
sheet.SetColumnWidth(0, 20 * 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);
#region 合并单元格
CellRangeAddress regionTitle = new CellRangeAddress(0, 0, 0, 6);
sheet.AddMergedRegion(regionTitle);
CellRangeAddress regionDate = new CellRangeAddress(1, 1, 0, 6);
sheet.AddMergedRegion(regionDate);
CellRangeAddress regionSearch = new CellRangeAddress(2, 2, 0, 6);
sheet.AddMergedRegion(regionSearch);
//CellRangeAddress（）该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。
IRow row0 = sheet.CreateRow(0);
row0.Height = 20 * 20;
ICell icell1top0 = row0.CreateCell(0);
icell1top0.SetCellValue("销售统计报表");
NOPIHelper.RegionMethod(workbook, sheet, regionTitle, NOPIHelper.Stylexls.标题);
IRow row1 = sheet.CreateRow(1);
row1.Height = 20 * 20;
ICell icell1top1 = row1.CreateCell(0);
icell1top1.SetCellValue("日期:" + startDate + "至" + endDate + "");
NOPIHelper.RegionMethod(workbook, sheet, regionDate, NOPIHelper.Stylexls.头);
IRow row2 = sheet.CreateRow(2);
row2.Height = 20 * 20;
ICell icell1top2 = row2.CreateCell(0);
var cityName = "";
var countyName = "";
var agentName = "";
var siteName = "";
AgentBLL agentBLL = new AgentBLL(context, loginUser);
SiteBLL siteBLL = new SiteBLL(context, loginUser);
AreaBLL areaBLL = new AreaBLL(context, loginUser);
if (!string.IsNullOrEmpty(agentId))
{
var agent = agentBLL.Get(agentId);
if (agent != null)
{
agentName = agent.agentName;
}
}
if (!string.IsNullOrEmpty(siteId))
{
siteName = siteBLL.Get(siteId).siteName;
}
if (!string.IsNullOrEmpty(city))
{
cityName = areaBLL.Get(city).areaName;
}
if (!string.IsNullOrEmpty(county))
{
countyName = areaBLL.Get(county).areaName;
}
icell1top2.SetCellValue("市:" + cityName + " 区县:" + countyName + " 执法文书类型:" + siteName + " 代理商编号:" + agentName + " 终端号:" + terminalId + "");//搜索条件
NOPIHelper.RegionMethod(workbook, sheet, regionSearch, NOPIHelper.Stylexls.头);
#endregion
#region 设置表头
int rowsNum = 3; //行号
IRow row3 = sheet.CreateRow(rowsNum);
row3.Height = 20 * 20;
ICell icell1top = row3.CreateCell(0);
icell1top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell1top.SetCellValue("时间");
ICell icell2top = row3.CreateCell(1);
icell2top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell2top.SetCellValue("预存款金额");
ICell icell3top = row3.CreateCell(2);
icell3top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell3top.SetCellValue("销售总额");
ICell icell4top = row3.CreateCell(3);
icell4top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell4top.SetCellValue("佣金金额");
ICell icell5top = row3.CreateCell(4);
icell5top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell5top.SetCellValue("中奖金额");
ICell icell6top = row3.CreateCell(5);
icell6top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell6top.SetCellValue("提现金额");
ICell icell7top = row3.CreateCell(6);
icell7top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell7top.SetCellValue("退款金额");
#endregion
#region 合计
rowsNum = 4; //行号
IRow row4 = sheet.CreateRow(rowsNum);
row4.Height = 20 * 20;
ICell icell1Total = row4.CreateCell(0);
icell1Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell1Total.SetCellValue("合计");
ICell icell2Total = row4.CreateCell(1);
icell2Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell2Total.SetCellValue("");
ICell icell3Total = row4.CreateCell(2);
icell3Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
icell3Total.SetCellFormula("SUM(C6:C" + dtRowsCount + ")");
ICell icell4Total = row4.CreateCell(3);
icell4Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
icell4Total.SetCellFormula("SUM(D6:D" + dtRowsCount + ")");
ICell icell5Total = row4.CreateCell(4);
icell5Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
icell5Total.SetCellFormula("SUM(E6:E" + dtRowsCount + ")");
ICell icell6Total = row4.CreateCell(5);
icell6Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
icell6Total.SetCellFormula("SUM(F6:F" + dtRowsCount + ")");
ICell icell7Total = row4.CreateCell(6);
icell7Total.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
icell7Total.SetCellFormula("SUM(G6:G" + dtRowsCount + ")");
#endregion
ICellStyle cellStyleMoney = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
ICellStyle cellStyleTime = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.时间);
ICellStyle cellStyleDefault = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.默认);
rowsNum = 5;
foreach (DataRow dr in dt.Rows)
{
/******************写入字段值*********************/
row4 = sheet.CreateRow(rowsNum);
ICell icel1DateDay = row4.CreateCell(0);
icel1DateDay.SetCellValue(dr["dateday"].ToString());
icel1DateDay.CellStyle = cellStyleTime;
var balanceGroupList = balanceList.Where(c => DateTime.Parse(c.createTime.Substring(0, 10)) <= DateTime.Parse(dr["dateday"].ToString()))
.OrderByDescending(x => x.createTime).GroupBy(x => x.agentId)//必须排序
.Select(g => new { g, count = g.Count() })
.SelectMany(t => t.g.Select(b => b)
.Zip(Enumerable.Range(1, t.count), (j, i) =>
new { j.balance, j.createTime, rn = i }));
var advanceBalance = balanceGroupList.Where(c => c.rn == 1).Sum(c => c.balance);
ICell icellAdvanceBalance = row4.CreateCell(1);
icellAdvanceBalance.SetCellValue(advanceBalance);
icellAdvanceBalance.CellStyle = cellStyleMoney;
ICell icellSumBetFee = row4.CreateCell(2);
icellSumBetFee.SetCellValue(double.Parse(dr["sumBetFee"].ToString()));
icellSumBetFee.CellStyle = cellStyleMoney;
ICell icel1CommissionFee = row4.CreateCell(3);
icel1CommissionFee.SetCellValue(double.Parse(dr["commissionFee"].ToString()));
icel1CommissionFee.CellStyle = cellStyleMoney;
ICell icel1AwardAmount = row4.CreateCell(4);
icel1AwardAmount.SetCellValue(double.Parse(dr["awardAmount"].ToString()));
icel1AwardAmount.CellStyle = cellStyleMoney;
ICell icel1SumTakeCashFee = row4.CreateCell(5);
icel1SumTakeCashFee.SetCellValue(double.Parse(dr["sumTakeCashFee"].ToString()));
icel1SumTakeCashFee.CellStyle = cellStyleMoney;
ICell icel1RefundAmount = row4.CreateCell(6);
icel1RefundAmount.SetCellValue(double.Parse(dr["refundAmount"].ToString()));
icel1RefundAmount.CellStyle = cellStyleMoney;
rowsNum++;
}
workbook.Write(context.Response.OutputStream); //输出到流中
}
}
public void ProcessRequest(HttpContext context)
{
LoginUser loginUser = new LoginUser(context, "ClientTakeCashReport");
ClientTakeCashBLL bll = new ClientTakeCashBLL(context, loginUser);
//加载DataGrid
if (context.Request["action"] == "gridLoad")
{
int page = int.Parse(context.Request["page"]);
int rows = int.Parse(context.Request["rows"]);
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
string agentId = context.Request["agentId"];
string clientId = context.Request["clientId"];
bll.ClientTakeCashLoadGrid(page, rows, startDate, endDate, agentId, clientId);
}
if (context.Request["action"] == "ToExcel")
{
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
string agentId = context.Request["agentId"];
string clientId = context.Request["clientId"];
DataTable dt = bll.ClientTakeCashReport(startDate, endDate, agentId, clientId);
object sumObject = dt.Compute("sum(fee)", "TRUE");
context.Response.ContentType = "application/x-excel";
string fileName = HttpUtility.UrlEncode(startDate + "至" + endDate + "客户提现报表.xls");
context.Response.AddHeader("Content-Disposition", "attachment; fileName=" + fileName);
IWorkbook workbook = new HSSFWorkbook();
//创建表
ISheet sheet = workbook.CreateSheet("客户提现报表");
//设置单元的宽度
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 15 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 15 * 256);
sheet.SetColumnWidth(5, 20 * 256);
sheet.SetColumnWidth(6, 15 * 256);
#region 合并单元格
CellRangeAddress regionTitle = new CellRangeAddress(0, 0, 0, 6);
sheet.AddMergedRegion(regionTitle);
CellRangeAddress regionDate = new CellRangeAddress(1, 1, 0, 6);
sheet.AddMergedRegion(regionDate);
//CellRangeAddress（）该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。
IRow row0 = sheet.CreateRow(0);
row0.Height = 20 * 20;
ICell icell1top0 = row0.CreateCell(0);
icell1top0.SetCellValue("客户提现报表");
NOPIHelper.RegionMethod(workbook, sheet, regionTitle, NOPIHelper.Stylexls.标题);
IRow row1 = sheet.CreateRow(1);
row1.Height = 20 * 20;
ICell icell1top1 = row1.CreateCell(0);
icell1top1.SetCellValue("日期:" + startDate + "至" + endDate + " 代理商编号:" + agentId + " 客户编号:" + clientId + " 提现总额:" + double.Parse(sumObject == DBNull.Value ? "0" : sumObject.ToString()).ToString("￥#,##0.00") + "");
NOPIHelper.RegionMethod(workbook, sheet, regionDate, NOPIHelper.Stylexls.头);
#endregion
#region 设置表头
int rowsNum = 2; //行号
IRow row2 = sheet.CreateRow(rowsNum);
row2.Height = 20 * 20;
ICell icell1top = row2.CreateCell(0);
icell1top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell1top.SetCellValue("客户编号");
ICell icell2top = row2.CreateCell(1);
icell2top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell2top.SetCellValue("提现金额");
ICell icell3top = row2.CreateCell(2);
icell3top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell3top.SetCellValue("提现方式");
ICell icell4top = row2.CreateCell(3);
icell4top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell4top.SetCellValue("提现时间");
ICell icell5top = row2.CreateCell(4);
icell5top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell5top.SetCellValue("提现单号");
ICell icell6top = row2.CreateCell(5);
icell6top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell6top.SetCellValue("代理商");
ICell icell7top = row2.CreateCell(6);
icell7top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell7top.SetCellValue("操作员");
#endregion
rowsNum = 3; //行号
ICellStyle cellStyleMoney = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
ICellStyle cellStyleTime = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.时间);
ICellStyle cellStyleDefault = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.默认);
foreach (DataRow dr in dt.Rows)
{
/******************写入字段值*********************/
row2 = sheet.CreateRow(rowsNum);
ICell icel1ClientId = row2.CreateCell(0);
icel1ClientId.SetCellValue(dr["ClientId"].ToString());
icel1ClientId.CellStyle = cellStyleDefault;
ICell icellFee = row2.CreateCell(1);
icellFee.SetCellValue(double.Parse(dr["Fee"].ToString()));
icellFee.CellStyle = cellStyleMoney;
ICell icellHandleMode = row2.CreateCell(2);
icellHandleMode.SetCellValue(dr["HandleMode"].ToString());
icellHandleMode.CellStyle = cellStyleDefault;
ICell icel1CreateTime = row2.CreateCell(3);
icel1CreateTime.SetCellValue(dr["CreateTime"].ToString());
icel1CreateTime.CellStyle = cellStyleTime;
ICell icel1FlowId = row2.CreateCell(4);
icel1FlowId.SetCellValue(dr["FlowId"].ToString());
icel1FlowId.CellStyle = cellStyleDefault;
ICell icel1AgentName = row2.CreateCell(5);
icel1AgentName.SetCellValue(dr["AgentId"].ToString());
icel1AgentName.CellStyle = cellStyleDefault;
ICell icel1OperatorName = row2.CreateCell(6);
icel1OperatorName.SetCellValue(dr["operatorname"].ToString());
icel1OperatorName.CellStyle = cellStyleDefault;
rowsNum++;
}
workbook.Write(context.Response.OutputStream); //输出到流中
}
}
public void ProcessRequest(HttpContext context)
{
LoginUser loginUser = new LoginUser(context, "ClientReport");
ClientBLL bll = new ClientBLL(context, loginUser);
if (context.Request["action"] == "agentListLoad")
{
Combobox com = new Combobox(context, loginUser);
com.AgentCombobox();
}
if (context.Request["action"] == "siteListLoad")
{
string agentId = context.Request["agentId"];
Combobox com = new Combobox(context, loginUser);
com.SiteByAgentCombobox(agentId);
}
//加载DataGrid
if (context.Request["action"] == "gridLoad")
{
int page = int.Parse(context.Request["page"]);
int rows = int.Parse(context.Request["rows"]);
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
string agentId = context.Request["agentId"];
string siteId = context.Request["siteId"];
string status = context.Request["status"];
bll.ClientLoadGrid(page, rows, startDate, endDate, agentId, siteId, status);
}
if (context.Request["action"] == "ToExcel")
{
AgentBLL agentBLL = new AgentBLL(context, loginUser);
SiteBLL siteBLL = new SiteBLL(context, loginUser);
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
string agentId = context.Request["agentId"];
string siteId = context.Request["siteId"];
string status = context.Request["status"];
var agentName = "";
var siteName = "";
var usedStatus = "全部";
if (!string.IsNullOrEmpty(agentId))
{
agentName = agentBLL.Get(agentId).agentName;
}
if (!string.IsNullOrEmpty(siteId))
{
siteName = siteBLL.Get(siteId).siteName;
}
if (!string.IsNullOrEmpty(status))
{
if (status == "0")
{
usedStatus = "激活";
}
else if (status == "1")
{
usedStatus = "封存";
}
}
DataTable dt = bll.ClientReport(startDate, endDate, agentId, siteId, status);
context.Response.ContentType = "application/x-excel";
string fileName = HttpUtility.UrlEncode(startDate + "至" + endDate + "客户信息报表.xls");
context.Response.AddHeader("Content-Disposition", "attachment; fileName=" + fileName);
IWorkbook workbook = new HSSFWorkbook();
//创建表
ISheet sheet = workbook.CreateSheet("客户信息报表");
//设置单元的宽度
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 15 * 256);
sheet.SetColumnWidth(2, 15 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 20 * 256);
sheet.SetColumnWidth(6, 15 * 256);
#region 合并单元格
CellRangeAddress regionTitle = new CellRangeAddress(0, 0, 0, 6);
sheet.AddMergedRegion(regionTitle);
CellRangeAddress regionDate = new CellRangeAddress(1, 1, 0, 6);
sheet.AddMergedRegion(regionDate);
//CellRangeAddress（）该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。
IRow row0 = sheet.CreateRow(0);
row0.Height = 20 * 20;
ICell icell1top0 = row0.CreateCell(0);
icell1top0.SetCellValue("客户信息报表");
NOPIHelper.RegionMethod(workbook, sheet, regionTitle, NOPIHelper.Stylexls.标题);
IRow row1 = sheet.CreateRow(1);
row1.Height = 20 * 20;
ICell icell1top1 = row1.CreateCell(0);
icell1top1.SetCellValue("日期:" + startDate + "至" + endDate + " 代理商:" + agentName + " 执法文书类型:" + siteName + " 使用状态:" + usedStatus + "");
NOPIHelper.RegionMethod(workbook, sheet, regionDate, NOPIHelper.Stylexls.头);
#endregion
#region 设置表头
int rowsNum = 2; //行号
IRow row2 = sheet.CreateRow(rowsNum);
row2.Height = 20 * 20;
ICell icell1top = row2.CreateCell(0);
icell1top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell1top.SetCellValue("客户编号");
ICell icell2top = row2.CreateCell(1);
icell2top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell2top.SetCellValue("客户名称");
ICell icell3top = row2.CreateCell(2);
icell3top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell3top.SetCellValue("执法文书类型");
ICell icell4top = row2.CreateCell(3);
icell4top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell4top.SetCellValue("卡号");
ICell icell5top = row2.CreateCell(4);
icell5top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell5top.SetCellValue("账户余额");
ICell icell6top = row2.CreateCell(5);
icell6top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell6top.SetCellValue("冻结金额");
ICell icell7top = row2.CreateCell(6);
icell7top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell7top.SetCellValue("使用状态");
#endregion
rowsNum = 3; //行号
ICellStyle cellStyleMoney = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
ICellStyle cellStyleDefault = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.默认);
foreach (DataRow dr in dt.Rows)
{
/******************写入字段值*********************/
row2 = sheet.CreateRow(rowsNum);
ICell icel1ClientId = row2.CreateCell(0);
icel1ClientId.SetCellValue(dr["ClientId"].ToString());
icel1ClientId.CellStyle = cellStyleDefault;
ICell icellClientName = row2.CreateCell(1);
icellClientName.SetCellValue(dr["ClientName"].ToString());
icellClientName.CellStyle = cellStyleDefault;
ICell icellSiteName = row2.CreateCell(2);
icellSiteName.SetCellValue(dr["SiteName"].ToString());
icellSiteName.CellStyle = cellStyleDefault;
ICell icel1CardId = row2.CreateCell(3);
icel1CardId.SetCellValue(dr["CardId"].ToString());
icel1CardId.CellStyle = cellStyleDefault;
ICell icel1Balance = row2.CreateCell(4);
icel1Balance.SetCellValue(double.Parse(dr["Balance"].ToString()));
icel1Balance.CellStyle = cellStyleMoney;
ICell icel1LockFee = row2.CreateCell(5);
icel1LockFee.SetCellValue(double.Parse(dr["LockFee"].ToString()));
icel1LockFee.CellStyle = cellStyleMoney;
ICell icel1Status = row2.CreateCell(6);
icel1Status.SetCellValue((dr["Status"].ToString() == "0"?"激活":"封存"));
icel1Status.CellStyle = cellStyleDefault;
rowsNum++;
}
workbook.Write(context.Response.OutputStream); //输出到流中
}
}
public ActionResult AjaxUpload()
{
HttpFileCollection files = System.Web.HttpContext.Current.Request.Files;
if (files.Count == 0)
{
return(Json("Faild", JsonRequestBehavior.AllowGet));
}
MD5 md5Hasher = new MD5CryptoServiceProvider();
/*计算指定Stream对象的哈希值*/
byte[] arrbytHashValue = md5Hasher.ComputeHash(files[0].InputStream);
/*由以连字符分隔的十六进制对构成的String，其中每一对表示value中对应的元素；例如“F-2C-4A”*/
//string strHashData = System.BitConverter.ToString(arrbytHashValue).Replace("-", "");
//string FileEextension = Path.GetExtension(files[0].FileName);//後綴名字
string FileName = Path.GetFileName(files[0].FileName);
//string uploadDate = DateTime.Now.ToString("yyyyMMddHHmmss");
//string virtualPath = string.Format("../Shared/Upload/{0}/{1}{2}", uploadDate, strHashData, FileEextension);
string virtualPath = string.Format("../File/Excel/{0}", FileName);
string fullFileName = Server.MapPath(virtualPath);
//创建文件夹，保存文件
string path = Path.GetDirectoryName(fullFileName);
Directory.CreateDirectory(path);
//判断文件是不是存在
if (System.IO.File.Exists(fullFileName))
{
//如果存在则删除
System.IO.File.Delete(fullFileName);
}
if (!System.IO.File.Exists(fullFileName))
{
files[0].SaveAs(fullFileName);
}
bool chliec = true;
DataTable dt = NOPIHelper.GetTable(fullFileName, true, 1);
if (dt.Columns.Count < 8)
{
chliec = false;
}
int aa = dt.Rows.Count;
ViewData["js"] = aa;
//判斷格式 職位是不是 線長 線員 ，白班是不是白晚班
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < 8; i++)
{
if (string.IsNullOrEmpty(dr[i].ToString()))
{
chliec = false; break;
}
}
if (!chliec)
{
break;
}
string a = dr.Field <string>("班別").ToString();
if (dr.Field <string>("職位").ToString() != "線長" && dr.Field <string>("職位").ToString() != "線員" && dr.Field <string>("職位").ToString() != "主管")
{
chliec = false; break;
}
if (dr.Field <string>("班別").ToString() != "白班" && dr.Field <string>("班別").ToString() != "晚班")
{
chliec = false; break;
}
}
if (chliec)
{
ExcelIOCommon comm = new ExcelIOCommon();
comm.addStaff(dt);
comm.addPhoneUsers(dt);
comm.addLienNumber(dt);
}
string fileName = files[0].FileName.Substring(files[0].FileName.LastIndexOf("\\") + 1, files[0].FileName.Length - files[0].FileName.LastIndexOf("\\") - 1);
string fileSize = GetFileSize(files[0].ContentLength);
//ViewData["seccuss"] = "OK";
if (chliec)
{
return(Json(new { FileName = fileName, FilePath = virtualPath, FileSize = fileSize }, "text/html", JsonRequestBehavior.AllowGet));
}
else
{
return(View());
}
}
public void ProcessRequest(HttpContext context)
{
LoginUser loginUser = new LoginUser(context, "WinPrizeReport");
LotteryOrderBLL bll = new LotteryOrderBLL(context, loginUser);
//加载DataGrid
if (context.Request["action"] == "gridLoad")
{
int page = int.Parse(context.Request["page"]);
int rows = int.Parse(context.Request["rows"]);
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
string city = context.Request["cityId"];
string county = context.Request["countyId"];
string siteId = context.Request["siteId"];
string terminalId = context.Request["terminalId"];
string lotteryType = context.Request["lotteryType"];
string gameId = context.Request["gameId"];
bll.WinPrizeDataLoadGrid(page, rows, startDate, endDate, city, county, siteId, terminalId, lotteryType, gameId);
}
if (context.Request["action"] == "totalFee")
{
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
string city = context.Request["cityId"];
string county = context.Request["countyId"];
string siteId = context.Request["siteId"];
string terminalId = context.Request["terminalId"];
string lotteryType = context.Request["lotteryType"];
string gameId = context.Request["gameId"];
context.Response.Write(bll.GetTotalWinPrize(startDate, endDate, city, county, siteId, terminalId, lotteryType, gameId));
}
//加载市
if (context.Request["action"] == "cityListLoad")
{
Combobox com = new Combobox(context, loginUser);
com.CityCombobox();
}
//加载区县
if (context.Request["action"] == "countyListLoad")
{
string cityId = context.Request["cityId"];
Combobox com = new Combobox(context, loginUser);
com.CountyCombobox(cityId);
}
//加载执法文书类型
if (context.Request["action"] == "siteListLoad")
{
Combobox com = new Combobox(context, loginUser);
string cityId = context.Request["cityId"];
string countyId = context.Request["countyId"];
com.SiteByAreaCombobox(cityId, countyId);
}
//加载彩种
if (context.Request["action"] == "lotteryTypeListLoad")
{
Combobox com = new Combobox(context, loginUser);
com.LotteryTypeCombobox();
}
//加载游戏
if (context.Request["action"] == "gameListLoad")
{
Combobox com = new Combobox(context, loginUser);
com.GameCombobox();
}
if (context.Request["action"] == "ToExcel")
{
string startDate = context.Request["startDate"];
string endDate = context.Request["endDate"];
string city = context.Request["cityId"];
string county = context.Request["countyId"];
string siteId = context.Request["siteId"];
string terminalId = context.Request["terminalId"];
string lotteryType = context.Request["lotteryType"];
string gameId = context.Request["gameId"];
DataTable dt = bll.WinPrizeDataReport(startDate, endDate, city, county, siteId, terminalId, lotteryType, gameId);
object sumObject = dt.Compute("sum(awardmoney)", "TRUE");
context.Response.ContentType = "application/x-excel";
string fileName = HttpUtility.UrlEncode(startDate + "至" + endDate + "中奖数据报表.xls");
context.Response.AddHeader("Content-Disposition", "attachment; fileName=" + fileName);
IWorkbook workbook = new HSSFWorkbook();
//创建表
ISheet sheet = workbook.CreateSheet("中奖数据报表");
//设置单元的宽度
sheet.SetColumnWidth(0, 10 * 256);
sheet.SetColumnWidth(1, 10 * 256);
sheet.SetColumnWidth(2, 15 * 256);
sheet.SetColumnWidth(3, 10 * 256);
sheet.SetColumnWidth(4, 13 * 256);
sheet.SetColumnWidth(5, 10 * 256);
sheet.SetColumnWidth(6, 10 * 256);
sheet.SetColumnWidth(7, 13 * 256);
sheet.SetColumnWidth(8, 20 * 256);
sheet.SetColumnWidth(9, 30 * 256);
sheet.SetColumnWidth(10, 15 * 256);
#region 合并单元格
CellRangeAddress regionTitle = new CellRangeAddress(0, 0, 0, 10);
sheet.AddMergedRegion(regionTitle);
CellRangeAddress regionDate = new CellRangeAddress(1, 1, 0, 10);
sheet.AddMergedRegion(regionDate);
CellRangeAddress regionSearch = new CellRangeAddress(2, 2, 0, 10);
sheet.AddMergedRegion(regionSearch);
//CellRangeAddress（）该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。
IRow row0 = sheet.CreateRow(0);
row0.Height = 20 * 20;
ICell icell1top0 = row0.CreateCell(0);
icell1top0.SetCellValue("中奖数据报表");
NOPIHelper.RegionMethod(workbook, sheet, regionTitle, NOPIHelper.Stylexls.标题);
IRow row1 = sheet.CreateRow(1);
row1.Height = 20 * 20;
ICell icell1top1 = row1.CreateCell(0);
icell1top1.SetCellValue("日期:" + startDate + "至" + endDate + " 中奖总额:" + double.Parse(sumObject == DBNull.Value ? "0" : sumObject.ToString()).ToString("￥#,##0.00") + "");
NOPIHelper.RegionMethod(workbook, sheet, regionDate, NOPIHelper.Stylexls.头);
IRow row2 = sheet.CreateRow(2);
row2.Height = 20 * 20;
ICell icell1top2 = row2.CreateCell(0);
var cityName = "";
var countyName = "";
var siteName = "";
var lotteryName = "";
var gameName = "";
SiteBLL siteBLL = new SiteBLL(context, loginUser);
AreaBLL areaBLL = new AreaBLL(context, loginUser);
LotteryBLL lotteryBLL = new LotteryBLL(context, loginUser);
GameBLL gameBLL = new GameBLL(context, loginUser);
if (!string.IsNullOrEmpty(siteId))
{
siteName = siteBLL.Get(siteId).siteName;
}
if (!string.IsNullOrEmpty(city))
{
cityName = areaBLL.Get(city).areaName;
}
if (!string.IsNullOrEmpty(county))
{
countyName = areaBLL.Get(county).areaName;
}
if (!string.IsNullOrEmpty(lotteryType))
{
lotteryName = lotteryBLL.Get(lotteryType).LotteryTypeName;
}
if (!string.IsNullOrEmpty(gameId))
{
gameName = gameBLL.Get(gameId).gameName;
}
icell1top2.SetCellValue("市:" + cityName + " 区县:" + countyName + " 执法文书类型:" + siteName + " 终端号:" + terminalId + " 彩种:" + lotteryName + " 游戏:" + gameName + "");//搜索条件
NOPIHelper.RegionMethod(workbook, sheet, regionSearch, NOPIHelper.Stylexls.头);
#endregion
#region 设置表头
int rowsNum = 3; //行号
IRow row3 = sheet.CreateRow(rowsNum);
row3.Height = 20 * 20;
ICell icell1top = row3.CreateCell(0);
icell1top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell1top.SetCellValue("市");
ICell icell2top = row3.CreateCell(1);
icell2top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell2top.SetCellValue("区(县)");
ICell icell3top = row3.CreateCell(2);
icell3top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell3top.SetCellValue("执法文书类型名称");
ICell icell4top = row3.CreateCell(3);
icell4top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell4top.SetCellValue("终端号");
ICell icell5top = row3.CreateCell(4);
icell5top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell5top.SetCellValue("客户编号");
ICell icell6top = row3.CreateCell(5);
icell6top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell6top.SetCellValue("彩种");
ICell icell7top = row3.CreateCell(6);
icell7top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell7top.SetCellValue("游戏");
ICell icell8top = row3.CreateCell(7);
icell8top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell8top.SetCellValue("期次");
ICell icell9top = row3.CreateCell(8);
icell9top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell9top.SetCellValue("交易时间");
ICell icell10top = row3.CreateCell(9);
icell10top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell10top.SetCellValue("订单号");
ICell icell11top = row3.CreateCell(10);
icell11top.CellStyle = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.头);
icell11top.SetCellValue("中奖金额");
#endregion
rowsNum = 4; //行号
ICellStyle cellStyleMoney = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.钱);
ICellStyle cellStyleTime = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.时间);
ICellStyle cellStyleDefault = NOPIHelper.Getcellstyle(workbook, NOPIHelper.Stylexls.默认);
foreach (DataRow dr in dt.Rows)
{
/******************写入字段值*********************/
row3 = sheet.CreateRow(rowsNum);
ICell icel1City = row3.CreateCell(0);
icel1City.SetCellValue(dr["city"].ToString());
icel1City.CellStyle = cellStyleDefault;
ICell icellCounty = row3.CreateCell(1);
icellCounty.SetCellValue(dr["county"].ToString());
icellCounty.CellStyle = cellStyleDefault;
ICell icellSiteName = row3.CreateCell(2);
icellSiteName.SetCellValue(dr["siteName"].ToString());
icellSiteName.CellStyle = cellStyleDefault;
ICell icel1TerminalId = row3.CreateCell(3);
icel1TerminalId.SetCellValue(dr["terminalId"].ToString());
icel1TerminalId.CellStyle = cellStyleDefault;
ICell icel1ClientId = row3.CreateCell(4);
icel1ClientId.SetCellValue(dr["clientId"].ToString());
icel1ClientId.CellStyle = cellStyleDefault;
ICell icel1LotteryType = row3.CreateCell(5);
icel1LotteryType.SetCellValue(dr["lotteryType"].ToString());
icel1LotteryType.CellStyle = cellStyleDefault;
ICell icel1GameId = row3.CreateCell(6);
icel1GameId.SetCellValue(dr["gameId"].ToString());
icel1GameId.CellStyle = cellStyleDefault;
ICell icel1Period = row3.CreateCell(7);
icel1Period.SetCellValue(dr["period"].ToString());
icel1Period.CellStyle = cellStyleDefault;
ICell icel1AwardTime = row3.CreateCell(8);
icel1AwardTime.SetCellValue(dr["awardtime"].ToString());
icel1AwardTime.CellStyle = cellStyleTime;
ICell icel1OrderId = row3.CreateCell(9);
icel1OrderId.SetCellValue(dr["orderid"].ToString());
icel1OrderId.CellStyle = cellStyleDefault;
ICell icel1AwardMoney = row3.CreateCell(10);
icel1AwardMoney.SetCellValue(double.Parse(dr["awardmoney"].ToString()));
icel1AwardMoney.CellStyle = cellStyleMoney;
rowsNum++;
}
workbook.Write(context.Response.OutputStream); //输出到流中
}
}
