5、VS2010+ASP.NET MVC4+EF4+JqueryEasyUI+Oracle项目开发之——使用datagrid做报表统计
来源：互联网 发布：电脑认证淘宝开店时间 编辑：程序博客网 时间：2024/06/05 16:10
需要说明一点：数据库访问我使用的是EF框架，但是在报表统计着一块由于使用到了动态表，即根据每一天单独生成了一张数据表，所以不好使用EF进行预先添加，于是我在数据库访问层里面封装了两套数据库访问框架，一个就是EF，一个就是传统的ADO.NET。当设计到使用动态表时，我就调用用ADO.NET进行数据库访问操作了。
先看下View，这里我使用的是jquery easyUI里面的datagrid来做的报表。如下：
单击选中日报：
年报如下：
@model YKT.Model.SubCompanyReport@{ ViewBag.Title = "分公司营收统计"; Layout = "~/Views/Shared/Base.cshtml";}<style type="text/css">.datagrid-header-row .datagrid-cell-group{ font-size:12px; font-weight:bold;}.datagrid-header td{font-weight:bold; }</style><script src="../../Res/My97DatePicker/WdatePicker.js" type="text/javascript"></script><script type="text/javascript"> var cTime = ""; var cUser = '<div style="text-align: left; float:left;">制表人：</div>'; var dataDate = ""; var yearMonthTitle = ""; var dayMonthTitle = ""; var tbTitle=""; //获取组织类型 function getComponentType() { $("#sltCompany").empty(); var url = "SubCompanyReport/GetComList" $.getJSON(url, function (data) { if (data != null) { $.each(data, function (i, item) { $("<option></option>") .val(item["COMPONENT_ID"]) .text(item["COMPONENT_NAME"]) .appendTo($("#sltCompany")); }); } }); } //“查询”按钮，弹出查询框 function flexiQuery() { if ($("#Start_Time").val() == "") { $.messager.alert('提示', '结算日期不能为空'); } else { //将查询条件按照分隔符拼接成字符串 var search = "Start_Time=" + $("#Start_Time").val() + "&&End_Time=" + $("#End_Time").val() + "&&sltCompany=" + $("#sltCompany").val() + "&&sltReportType=" + $("#sltReportType").val(); var url = 'SubCompanyReport/GetData?' + search; cTime = '<div style="text-align: left; float:left;">制表时间：' + CurentDate() + '</div>'; var myDate = new Date(); var startDate = StringToDate($("#Start_Time").val()); dataDate = '<div style="text-align: left; float:left;">数据日期：'; if ($("#sltReportType").val() == "0") { var endDate = StringToDate($("#End_Time").val()); if (endDate == "") { dataDate += getDateByStr(startDate) + getWeek(startDate); } else { dataDate += getDateByStr(startDate) + getWeek(startDate) + " 到 " + getDateByStr(endDate) + getWeek(endDate); } dataDate += '</div>'; $('#div1').panel('close'); $('#div').panel('open'); yearMonthTitle = "月累计"; dayMonthTitle = "当日"; //执行查询 getData(url); } else if ($("#sltReportType").val() == "1") { $('#div1').panel('close'); $('#div').panel('open'); var v = getDateByStr(startDate); dataDate += v.substring(0, 8); dayMonthTitle = "当月"; yearMonthTitle = "年累计"; dataDate += '</div>'; //执行查询 getData(url); } else if ($("#sltReportType").val() == "2") { var v1 = getDateByStr(startDate); dataDate = v1.substring(0, 5); tbTitle = '公交总公司' + dataDate + '运营收入统计表(按分公司)'; $('#div1').panel('open'); $('#div').panel('close'); dataDate += '</div>'; //执行查询 getData1(url); } } } $(function () { initData(); $("#sltReportType").change(function () { if ($("#sltReportType").val() == "0") { $("#spnEndTime").css("display", "inline"); } else { $("#spnEndTime").css("display", "none"); } }); }); //初始化数据 function initData() { var d = getDate(0); $("#Start_Time").val(d); //$("#End_Time").val(d); getComponentType(); } //导出 function outputExcel() { $.messager.confirm('操作提示', "确认导出数据吗?", function (r) { if (r) { //将查询条件按照分隔符拼接成字符串 var search = "Start_Time=" + $("#Start_Time").val() + "&&End_Time=" + $("#End_Time").val() + "&&sltCompany=" + $("#sltCompany").val() + "&&sltReportType=" + $("#sltReportType").val(); var url = 'SubCompanyReport/Export?' + search; window.location.href = url; } }); } //日报表 function getData(url) { var searchH = $("#divQuery").height(); var h = document.documentElement.clientHeight - searchH - 26; //var w = document.documentElement.clientWidth; $('#flexigridData').datagrid({ title: '分公司营收统计', //列表的标题 iconCls: 'icon-site', // fit: true, //列表自动适应宽度 width: 'auto', singleSelect: true, height: h, nowrap: false, //True 就会把数据显示在一行里。 striped: true, //True 就把行条纹化。（即奇偶行使用不同背景色） collapsible: true, //可调节列宽 //remoteSort: true, //定义是否从服务器给数据排序。 url: url, //获取数据的url toolbar: [], columns: [ [{ title: '公交公司全日运营收入统计表(按分公司)', colspan: 17,align:'center'}], //第一行 [{ title: '<div style="text-align: left; float:left;">制表单位：吴江公交总公司IC卡结算管理中心</div>', colspan: 8, headalign: 'left' }, { title: cTime, colspan: 7 }, { title: cUser, colspan: 2}], //第二行 [{ title: dataDate, colspan: 17}], //第三行 [{ title: '单位', rowspan: 3, field: 'ComponentName' }, { title: '投币收入', colspan: 4 }, { title: 'IC卡收入', colspan: 4 }, { title: 'IC卡收入占%', colspan: 4 }, { title: '合计', colspan: 4}], //第四行 [{ title: dayMonthTitle, colspan: 2 }, { title: yearMonthTitle, colspan: 2 }, { title: dayMonthTitle, colspan: 2 }, { title: yearMonthTitle, colspan: 2 }, { title: dayMonthTitle, colspan: 2 }, { title: yearMonthTitle, colspan: 2 }, { title: dayMonthTitle, colspan: 2 }, { title: yearMonthTitle, colspan: 2}], //第五行 [{ title: '人次(次)', field: 'tbrec' }, { title: '金额(元)', field: 'tbamt', width: 80 }, { title: '人次(次)', field: 'tbljrec' }, { title: '金额(元)', field: 'tbljamt', width: 80 }, { title: '人次(次)', field: 'icrec' }, { title: '金额(元)', field: 'icamt', width: 80 }, { title: '人次(次)', field: 'icljrec' }, { title: '金额(元)', field: 'icljamt', width: 80 }, { title: '人次(次)', field: 'dayrecPer' }, { title: '金额(元)', field: 'dayAmtPer' }, { title: '人次(次)', field: 'monthrecPer' }, { title: '金额(元)', field: 'monthAmtPer' }, { title: '人次(次)', field: 'dayrecTotal' }, { title: '金额(元)', field: 'dayAmtTotal',width:80 }, { title: '人次(次)', field: 'monthrecTotal' }, { title: '金额(元)', field: 'monthAmtTotal',width:80}], //第六行] //, //pagination: true, //在 datagrid 的底部显示分页栏。 //rownumbers: true //显示行号的列 }); //异步获取按钮 //首先获取iframe标签的id值 var iframeid = window.parent.$('#tabs').tabs('getSelected').find('iframe').attr("id"); //然后关闭AJAX相应的缓存 $.ajaxSetup({ cache: false }); //获取按钮值 $.getJSON("../Home/GetToolbar", { id: iframeid }, function (data) { if (data == null) { return; } $('#flexigridData').datagrid("addToolbarItem", data); }); } //年报表 function getData1(url) { var searchH = $("#divQuery").height(); var h = document.documentElement.clientHeight - searchH - 26; //var w = document.documentElement.clientWidth; $('#flexigridData1').datagrid({ title: '分公司营收统计', //列表的标题 iconCls: 'icon-site', // fit: true, //列表自动适应宽度 width: 'auto', singleSelect: true, height: h, nowrap: false, //True 就会把数据显示在一行里。 striped: true, //True 就把行条纹化。（即奇偶行使用不同背景色） collapsible: true, //可调节列宽 //remoteSort: true, //定义是否从服务器给数据排序。 url: url, //获取数据的url toolbar: [], columns: [ [{ title: tbTitle, colspan: 9, align: 'center'}], //第一行 [{ title: '<span style="text">制表单位：吴江公交总公司IC卡结算管理中心</span>', colspan: 4, headalign: 'left' }, { title: cTime, colspan: 3 }, { title: cUser, colspan: 2}], //第二行 [{ title: '单位', rowspan: 2, field: 'ComponentName' }, { title: '投币收入', colspan: 2 }, { title: 'IC卡收入', colspan: 2 }, { title: 'IC卡收入占%', colspan: 2 }, { title: '合计', colspan: 2}], //第三行 [{ title: '人次(次)', field: 'tbrec' }, { title: '金额(元)', field: 'tbamt', width: 120 }, { title: '人次(次)', field: 'icrec' }, { title: '金额(元)', field: 'icamt', width: 120 }, { title: '人次(次)', field: 'yearrecPer' }, { title: '金额(元)', field: 'yearAmtPer', width: 120 }, { title: '人次(次)', field: 'yearrecTotal' }, { title: '金额(元)', field: 'yearAmtTotal', width: 120}]] //, //pagination: true, //在 datagrid 的底部显示分页栏。 //rownumbers: true //显示行号的列 }); //异步获取按钮 //首先获取iframe标签的id值 var iframeid = window.parent.$('#tabs').tabs('getSelected').find('iframe').attr("id"); //然后关闭AJAX相应的缓存 $.ajaxSetup({ cache: false }); //获取按钮值 $.getJSON("../Home/GetToolbar", { id: iframeid }, function (data) { if (data == null) { return; } $('#flexigridData1').datagrid("addToolbarItem", data); }); }</script><div id="content" region="center" fit="true"><form id="form1"><div id="divQuery" style="padding: 5px; font-size: 12px;" title="查询列表" class="easyui-toolbar"> <span>结算日期：</span><span> <input type="text" readonly="readonly" id="Start_Time" onclick="WdatePicker({maxDate:'#F{$dp.$D(\'End_Time\');}'})" /> </span> <span id="spnEndTime"> 到 <input type="text" readonly="readonly" id="End_Time" onclick="WdatePicker({minDate:'#F{$dp.$D(\'Start_Time\');}'})" /></span><span>公司类型：</span> <span><select id="sltCompany"></select></span> <span style=" margin:4px;">报表类型：</span><span><select id="sltReportType"> <option value="0">日报</option> <option value="1">月报</option> <option value="2">年报</option> </select></span> <a href="javascript:flexiQuery()" class="easyui-linkbutton" data-options="iconCls:'icon-search'">查询</a> <a href="javascript:resetData()" class="easyui-linkbutton" data-options="iconCls:'icon-redo'"> 重置</a></div></form> <div id="div" class="easyui-panel" closed="true"> <table id="flexigridData" style=" text-align:center"> </table> </div> <div id="div1" class="easyui-panel" closed="true"> <table id="flexigridData1" style=" text-align:center"> </table> </div></div>控制器SubCompanyReportController如下：using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.Mvc;using YKT.BLL;using YKT.Common;using YKT.Model;using System.IO;using Microsoft.Office.Interop.Excel;using System.Text;namespace YKT.Controllers{ public class CompanyClass { public string COMPONENT_ID { get; set; } public string COMPONENT_NAME { get; set; } } public class SubCompanyReportController : Controller { #region 初始化 string ClassName = "SubCompanyReport"; //类名 SubCompanyReportService _service; public SubCompanyReportController() { if (_service == null) { _service = new SubCompanyReportService(); } } ValidationErrors validationErrors = new ValidationErrors(); OMOPERATIONLOGTB OperLog = new OMOPERATIONLOGTB(); #endregion public ActionResult Index() { return View(); } public JsonResult GetComList() { System.Data.DataTable data = _service.GetComList(1, -1, 0, "COMMON,BUS"); List<CompanyClass> list = new List<CompanyClass>(); if (data != null && data.Rows.Count > 0) { for (int i = 0; i < data.Rows.Count; i++) { list.Add(new CompanyClass { COMPONENT_ID = data.Rows[i][0].ToString(), COMPONENT_NAME = data.Rows[i][1].ToString() }); } } return Json(list, JsonRequestBehavior.AllowGet); } [HttpPost] public JsonResult GetData(string sltReportType, string Start_Time, string End_Time, string sltCompany, string sltSubCompany) { List<SubCompanyReport> list = _service.GetTotalStatBySubCompany(sltReportType, Start_Time, End_Time); return Json(list, JsonRequestBehavior.AllowGet); } #region 导出 /// <summary> /// 数据导出 /// </summary> /// <param name="search"></param> /// <returns></returns> public ActionResult Export(string sltReportType, string Start_Time, string End_Time, string sltCompany, string sltSubCompany) { List<SubCompanyReport> queryData = _service.GetTotalStatBySubCompany(sltReportType, Start_Time, End_Time); if (queryData == null || queryData.Count < 1) { Response.Write("<script> alert('没有要导出的数据！'); </script>"); return View("Index"); } int sheet; //结算起始时间 #region 导出Excel string newpath = AppDomain.CurrentDomain.BaseDirectory + "Excel\\"; string newFileName = newpath + "temp.xls";//+ Guid.NewGuid() if (!Directory.Exists(newpath)) { Directory.CreateDirectory(newpath); } //string newpath = Server.MapPath(".") + @"\Excel\" + Guid.NewGuid() + ".xls"; //调用的模板文件 FileInfo mode = new FileInfo(Server.MapPath("~/xlsMode/营收报表.xls")); Application app = new Application(); if (app == null) { return Json("当前用户电脑未安装EXCEL!", JsonRequestBehavior.AllowGet); } app.Application.DisplayAlerts = false; app.Visible = false; if (mode.Exists) { Workbook tworkbook; Object missing = System.Reflection.Missing.Value; app.Workbooks.Add(missing); string beginTime = string.IsNullOrEmpty(Start_Time) ? string.Empty : DateTime.Parse(Start_Time).ToString("yyyyMMdd"); string endTime = string.IsNullOrEmpty(End_Time) ? string.Empty : DateTime.Parse(End_Time).ToString("yyyyMMdd"); //调用模板 写表头 tworkbook = app.Workbooks.Open(mode.FullName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Worksheet tworksheet=new Worksheet(); string ctitle = "制表时间："; string dataDateText = "数据日期："; switch (sltReportType) { case "0": //日报 sheet = 8; tworksheet = (Worksheet)tworkbook.Sheets[sheet]; if (!string.IsNullOrEmpty(beginTime) && string.IsNullOrEmpty(endTime)) { dataDateText += ValueConvert.GetDatetimeString(beginTime); } else if (string.IsNullOrEmpty(beginTime) && !string.IsNullOrEmpty(endTime)) { dataDateText += ValueConvert.GetDatetimeString(endTime); } else if (!string.IsNullOrEmpty(beginTime) && !string.IsNullOrEmpty(endTime)) { dataDateText += ValueConvert.GetDatetimeString(beginTime) + " 到 " + ValueConvert.GetDatetimeString(endTime); ; } tworksheet.Cells[3, 9] = ctitle+ValueConvert.GetDatetimeString(DateTime.Now.ToString("yyyyMMdd")); tworksheet.Cells[4, 1] = dataDateText; break; case "1": //月报 sheet = 9; tworksheet = (Worksheet)tworkbook.Sheets[sheet]; tworksheet.Cells[1, 1] = "吴江市公交公司" + beginTime.Substring(4, 2) + "月份运营收入统计表(按分公司)"; tworksheet.Cells[3, 9] = ctitle + ValueConvert.GetDatetimeString(DateTime.Now.ToString("yyyyMMdd")); break; case "2": //年报 sheet = 10; tworksheet = (Worksheet)tworkbook.Sheets[sheet]; tworksheet.Cells[1, 1] = "吴江市公交公司" + beginTime.Substring(0, 4) + "年运营收入统计表(按分公司)"; tworksheet.Cells[3, 5] = ctitle + ValueConvert.GetDatetimeString(DateTime.Now.ToString("yyyyMMdd")); break; } #region 写Excel的内容 if (queryData != null && queryData.Count > 0) { if (sltReportType == "0" || sltReportType == "1") { int m = 7; for (int i = 0; i < queryData.Count; i++) { tworksheet.Cells[m + i, 1] = queryData[i].ComponentName; tworksheet.Cells[m + i, 2] = queryData[i].tbrec; tworksheet.Cells[m + i, 3] = queryData[i].tbamt; tworksheet.Cells[m + i, 4] = queryData[i].tbljrec; tworksheet.Cells[m + i, 5] = queryData[i].tbljamt; tworksheet.Cells[m + i, 6] = queryData[i].icrec; tworksheet.Cells[m + i, 7] = queryData[i].icamt; tworksheet.Cells[m + i, 8] = queryData[i].icljrec; tworksheet.Cells[m + i, 9] = queryData[i].icljamt; tworksheet.Cells[m + i, 10] = queryData[i].dayrecPer; tworksheet.Cells[m + i, 11] = queryData[i].dayAmtPer; tworksheet.Cells[m + i, 12] = queryData[i].monthrecPer; tworksheet.Cells[m + i, 13] = queryData[i].monthAmtPer; tworksheet.Cells[m + i, 14] = queryData[i].dayrecTotal; tworksheet.Cells[m + i, 15] = queryData[i].dayAmtTotal; tworksheet.Cells[m + i, 16] = queryData[i].monthrecTotal; tworksheet.Cells[m + i, 17] = queryData[i].monthAmtTotal; } } else if (sltReportType == "2") { int m = 4; for (int i = 0; i < queryData.Count; i++) { tworksheet.Cells[m + i, 1] = queryData[i].ComponentName; tworksheet.Cells[m + i, 2] = queryData[i].tbrec; tworksheet.Cells[m + i, 3] = queryData[i].tbamt; tworksheet.Cells[m + i, 4] = queryData[i].icrec; tworksheet.Cells[m + i, 5] = queryData[i].icamt; tworksheet.Cells[m + i, 6] = queryData[i].yearrecPer; tworksheet.Cells[m + i, 7] = queryData[i].yearAmtPer; tworksheet.Cells[m + i, 8] = queryData[i].yearrecTotal; tworksheet.Cells[m + i, 9] = queryData[i].yearAmtTotal; } } } #endregion tworksheet.SaveAs(newFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing); tworkbook.Close(false, mode.FullName, missing); app.Workbooks.Close(); app.Quit(); tworkbook = null; app = null; //强制对所有代进行垃圾回收 GC.Collect(); } System.IO.FileInfo file = new System.IO.FileInfo(newFileName); Response.Clear(); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.Default; // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 string nm = "分公司_" + mode.Name; Response.AddHeader("Content-Disposition", "attachment; filename=" +HttpUtility.UrlEncode(nm, Encoding.UTF8).ToString() );//Server.UrlEncode(mode.Name)); // 添加头信息，指定文件大小，让浏览器能够显示下载进度 Response.AddHeader("Content-Length", file.Length.ToString()); // 指定返回的是一个不能被客户端读取的流，必须被下载 Response.ContentType = "application/ms-excel"; // 把文件流发送到客户端 Response.WriteFile(file.FullName); // 停止页面的执行 Response.End(); return Json("导出成功!", JsonRequestBehavior.AllowGet); #endregion } #endregion }} 4 0
- 5、VS2010+ASP.NET MVC4+EF4+JqueryEasyUI+Oracle项目开发之——使用datagrid做报表统计
- 3、VS2010+ASP.NET MVC4+EF4+JqueryEasyUI+Oracle项目开发之——用户登录
- 4、VS2010+ASP.NET MVC4+EF4+JqueryEasyUI+Oracle项目开发之——后台管理界面
- 6、VS2010+ASP.NET MVC4+EF4+JqueryEasyUI+Oracle项目开发之——增删改查CRUD
- 1、VS2010+ASP.NET MVC4+EF4+JqueryEasyUI+Oracle项目开发之环境准备
- 2、VS2010+ASP.NET MVC4+EF4+JqueryEasyUI+Oracle项目开发之架构设计
- Asp.Net中使用JQueryEasyUI--善良公社项目
- ASP.NET MVC4使用MongoDB做的相册管理
- ASP.NET之WEB项目中水晶报表的使用
- ASP.NET之WEB项目中水晶报表的使用
- ASP.Net中使用Excel做报表
- ASP.NET MVC4+ORACLE+EF+EXTJS4.2.1开发
- 大型ASP.NET MVC4项目之插入式程序结构
- JqueryEasyUI之DataGrid扩展
- ExtJS 6 Grid使用示例(ASP.NET MVC4 项目)
- 在Asp.Net中使用JQueryEasyUI
- 在Asp.Net中使用JQueryEasyUI
- 在Asp.Net中使用JQueryEasyUI
- 在64位系统下安装32位ODBC驱动问题How to install and configure a 32 bit ODBC driver on a 64 bit Operating System?
- 在使用W3C中的Node时，无法找到getTextConent方法异常
- android模拟器操作
- Spice Usbredirect 性能改进
- 【EZCONNECT】 sqlnet.ora文件中EZCONNECT参数与Easy Connect Naming Method方法
- 5、VS2010+ASP.NET MVC4+EF4+JqueryEasyUI+Oracle项目开发之——使用datagrid做报表统计
- for_each循环操作
- Cocos2d-x 深入解析系列 : 以XML文件方式保存用户数据
- 深入理解 SQL Server 2008 的锁机制
- Objective-C 2.0的运行时编程
- URI和URL的区别
- java个人学习笔记19（多生产者多消费者+循环判断标记+notifyAll()+Lock+Condition）
- JS实现加密解密(转)
- 修改状态栏字体颜色
