public FileResult OmitCheckCountExportExcel()
{
PatrolRouteStat_SW sw = new PatrolRouteStat_SW();
sw.orgNo = Request.Params["BYORGNO"];
sw.TopORGNO = Request.Params["BYORGNO"];
sw.DateBegin = Request.Params["TIMEBegin"];
sw.DateEnd = Request.Params["TIMEEnd"];
var list = HUReportCls.getPatrolRouteStatModel(sw);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
sheet1.IsPrintGridlines = true; //打印时显示网格线
sheet1.DisplayGridlines = true; //查看时显示网格线
sheet1.SetColumnWidth(0, 30 * 256);
sheet1.SetColumnWidth(1, 10 * 256);
sheet1.SetColumnWidth(2, 10 * 256);
sheet1.SetColumnWidth(3, 10 * 256);
sheet1.SetColumnWidth(4, 10 * 256);
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue("未巡统计表");
row.GetCell(0).CellStyle = getCellStyleTitle(book);
row = sheet1.CreateRow(1);
row.CreateCell(0).SetCellValue("单位/姓名");
row.CreateCell(1).SetCellValue("应巡");
row.CreateCell(2).SetCellValue("已巡");
row.CreateCell(3).SetCellValue("未巡");
row.CreateCell(4).SetCellValue("完成率");
row.GetCell(0).CellStyle = getCellStyleHead(book);
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(2).CellStyle = getCellStyleHead(book);
row.GetCell(3).CellStyle = getCellStyleHead(book);
row.GetCell(4).CellStyle = getCellStyleHead(book);
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4));
int rowI = 0;
foreach (var v in list)
{
row = sheet1.CreateRow(rowI + 2);
row.CreateCell(0).SetCellValue(v.ORGName);
row.CreateCell(1).SetCellValue(v.PointCount);
row.CreateCell(2).SetCellValue(v.PointCount0);
row.CreateCell(3).SetCellValue(v.PointCount1);
row.CreateCell(4).SetCellValue(v.PointCount2);
rowI++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
string fileName = "未巡统计表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
/// <summary>
/// 返回查询表格
/// </summary>
/// <returns></returns>
public ActionResult getHUCountJson()
{
T_IPSFR_USER_SW sw = new T_IPSFR_USER_SW();
sw.TopORGNO = Request.Params["TopORGNO"];
var list = HUReportCls.getHUCountModel(sw);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("<table id=\"tb\" cellpadding=\"0\" cellspacing=\"0\">");
sb.AppendFormat("<thead>");
sb.AppendFormat(" <tr>");
sb.AppendFormat(" <th rowspan=\"2\">单位</th>");
sb.AppendFormat(" <th rowspan=\"2\">总数</th>");
sb.AppendFormat(" <th colspan=\"2\">性别</th>");
sb.AppendFormat(" <th colspan=\"2\">固/兼职</th>");
sb.AppendFormat(" </tr>");
sb.AppendFormat(" <tr>");
sb.AppendFormat(" <th>男</th>");
sb.AppendFormat(" <th>女</th>");
sb.AppendFormat(" <th>固职</th>");
sb.AppendFormat(" <th>兼职</th>");
sb.AppendFormat(" </tr>");
sb.AppendFormat("</thead>");
sb.AppendFormat("<tbody>");
int i = 0;
foreach (var item in list)
{
i++;
if (i % 2 == 0)
{
sb.AppendFormat(" <tr>");
}
else
{
sb.AppendFormat(" <tr class='row1'>");
}
sb.AppendFormat("<td class=\"left\">{0}</td>", item.ORGName);
sb.AppendFormat("<td class=\"center\">{0}</td>", item.HUCount);
sb.AppendFormat("<td class=\"center\">{0}</td>", item.Sex1Count);
sb.AppendFormat("<td class=\"center\">{0}</td>", item.Sex0Count);
sb.AppendFormat("<td class=\"center\">{0}</td>", item.Onstate0Count);
sb.AppendFormat("<td class=\"center\">{0}</td>", item.Onstate1Count);
sb.AppendFormat("</tr>");
}
sb.AppendFormat("</tbody>");
sb.AppendFormat("</table>");
return(Content(JsonConvert.SerializeObject(new Message(true, sb.ToString(), "")), "text/html;charset=UTF-8"));
}
/// <summary>
/// 出勤查询Json
/// </summary>
/// <returns></returns>
public ActionResult getOmitCheckCountJson()
{
PatrolRouteStat_SW sw = new PatrolRouteStat_SW();
sw.orgNo = Request.Params["BYORGNO"];
sw.TopORGNO = Request.Params["BYORGNO"];
sw.DateBegin = Request.Params["TIMEBegin"];
sw.DateEnd = Request.Params["TIMEEnd"];
var list = HUReportCls.getPatrolRouteStatModel(sw);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("<table id=\"tb\" cellpadding=\"0\" cellspacing=\"0\">");
sb.AppendFormat("<thead>");
sb.AppendFormat(" <tr>");
sb.AppendFormat(" <th>单位</th>");
sb.AppendFormat(" <th>应巡总数</th>");
sb.AppendFormat(" <th>已完成</th>");
sb.AppendFormat(" <th>未完成</th>");
sb.AppendFormat(" <th>完成率(%)</th>");
sb.AppendFormat(" </tr>");
sb.AppendFormat("</thead>");
sb.AppendFormat("<tbody>");
int i = 0;
foreach (var v in list)
{
i++;
string orgName = v.ORGName;
string orgNo = v.ORGNo;
if (i % 2 == 0)
{
sb.AppendFormat("<tr>");
}
else
{
sb.AppendFormat("<tr class='row1'>");
}
sb.AppendFormat("<td class=\"center\">{0}</td>", v.ORGName + ""); //
sb.AppendFormat("<td class=\"center\">{0}</td>", v.PointCount + ""); //总
sb.AppendFormat("<td class=\"center\">{0}</td>", v.PointCount0 + ""); //完成
sb.AppendFormat("<td class=\"center\">{0}</td>", v.PointCount1 + ""); //未完成
sb.AppendFormat("<td class=\"center\">{0}</td>", v.PointCount2 + ""); //完成率
sb.AppendFormat("</tr>");
}
return(Content(JsonConvert.SerializeObject(new Message(true, sb.ToString(), "")), "text/html;charset=UTF-8"));
}
/// <summary>
/// 导出
/// </summary>
/// <returns></returns>
public FileResult HUCountExportExcel()
{
var vMenu = T_SYS_MENUCls.getModel(new T_SYS_MENU_SW {
MENUCODE = "004001", SYSFLAG = ConfigCls.getSystemFlag()
});
//vMenu.MENUNAME 页面/菜单名称
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
sheet1.IsPrintGridlines = true; //打印时显示网格线
sheet1.DisplayGridlines = true; //查看时显示网格线
sheet1.SetColumnWidth(0, 50 * 256);
sheet1.SetColumnWidth(1, 10 * 256);
sheet1.SetColumnWidth(2, 10 * 256);
sheet1.SetColumnWidth(3, 10 * 256);
sheet1.SetColumnWidth(4, 10 * 256);
sheet1.SetColumnWidth(5, 10 * 256);
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue(vMenu.MENUNAME);
row.GetCell(0).CellStyle = getCellStyleTitle(book);
row = sheet1.CreateRow(1);
row.CreateCell(0).SetCellValue("单位");
row.CreateCell(1).SetCellValue("总数");
row.CreateCell(2).SetCellValue("性别");
row.CreateCell(4).SetCellValue("固/兼职");
row.GetCell(0).CellStyle = getCellStyleHead(book);
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(2).CellStyle = getCellStyleHead(book);
row.GetCell(4).CellStyle = getCellStyleHead(book);
row = sheet1.CreateRow(2);
row.CreateCell(2).SetCellValue("男");
row.CreateCell(3).SetCellValue("女");
row.CreateCell(4).SetCellValue("固职");
row.CreateCell(5).SetCellValue("兼职");
row.GetCell(2).CellStyle = getCellStyleHead(book);
row.GetCell(3).CellStyle = getCellStyleHead(book);
row.GetCell(4).CellStyle = getCellStyleHead(book);
row.GetCell(5).CellStyle = getCellStyleHead(book);
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 5));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 2, 3));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 4, 5));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 0));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 1));
T_IPSFR_USER_SW sw = new T_IPSFR_USER_SW();
sw.TopORGNO = Request.Params["TopORGNO"];
var list = HUReportCls.getHUCountModel(sw);
int rowI = 0;
foreach (var item in list)
{
row = sheet1.CreateRow(rowI + 3);
row.CreateCell(0).SetCellValue(item.ORGName);
row.CreateCell(1).SetCellValue(item.HUCount);
row.CreateCell(2).SetCellValue(item.Sex0Count);
row.CreateCell(3).SetCellValue(item.Sex1Count);
row.CreateCell(4).SetCellValue(item.Onstate0Count);
row.CreateCell(5).SetCellValue(item.Onstate1Count);
for (int i = 0; i < 6; i++)
{
if (i == 0)
{
row.GetCell(0).CellStyle = getCellStyleLeft(book);
}
else
{
row.GetCell(i).CellStyle = getCellStyleCenter(book);
}
}
rowI++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
string fileName = vMenu.MENUNAME + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
public FileResult PatrolRouteStatExportExcel()
{
//string BYORGNO = Request.Params["BYORGNO"];
//string TIMEBegin = Request.Params["TIMEBegin"];
//string TIMEEnd = Request.Params["TIMEEnd"];
//string HNamePhone = Request.Params["HNamePhone"];
//var vMenu = T_SYS_MENUCls.getModel(new T_SYS_MENU_SW { MENUCODE = "004002", SYSFLAG = ConfigCls.getSystemFlag() });
//vMenu.MENUNAME 页面/菜单名称
#region 导出统计表
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
sheet1.IsPrintGridlines = true; //打印时显示网格线
sheet1.DisplayGridlines = true; //查看时显示网格线
sheet1.SetColumnWidth(0, 30 * 256);
sheet1.SetColumnWidth(1, 10 * 256);
sheet1.SetColumnWidth(2, 10 * 256);
sheet1.SetColumnWidth(3, 10 * 256);
sheet1.SetColumnWidth(4, 20 * 256);
sheet1.SetColumnWidth(5, 10 * 256);
sheet1.SetColumnWidth(6, 10 * 256);
sheet1.SetColumnWidth(7, 10 * 256);
sheet1.SetColumnWidth(8, 20 * 256);
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue("巡检路线统计总表");
row.GetCell(0).CellStyle = getCellStyleTitle(book);
row = sheet1.CreateRow(1);
row.CreateCell(0).SetCellValue("单位");
row.CreateCell(1).SetCellValue("巡检次数(条)");
row.CreateCell(5).SetCellValue("巡检地点(个)");
row.GetCell(0).CellStyle = getCellStyleHead(book);
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(5).CellStyle = getCellStyleHead(book);
row = sheet1.CreateRow(2);
row.CreateCell(1).SetCellValue("总数");
row.CreateCell(2).SetCellValue("巡检");
row.CreateCell(3).SetCellValue("未巡检");
row.CreateCell(4).SetCellValue("巡检率(%)");
row.CreateCell(5).SetCellValue("总数");
row.CreateCell(6).SetCellValue("完成");
row.CreateCell(7).SetCellValue("未完成");
row.CreateCell(8).SetCellValue("完成率(%)");
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(2).CellStyle = getCellStyleHead(book);
row.GetCell(3).CellStyle = getCellStyleHead(book);
row.GetCell(4).CellStyle = getCellStyleHead(book);
row.GetCell(5).CellStyle = getCellStyleHead(book);
row.GetCell(6).CellStyle = getCellStyleHead(book);
row.GetCell(7).CellStyle = getCellStyleHead(book);
row.GetCell(8).CellStyle = getCellStyleHead(book);
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, 4));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 5, 8));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 0));
//var list = HUReportCls.getPatrolRouteStatModel(new PatrolRouteStat_SW { DateBegin = TIMEBegin, DateEnd = TIMEEnd, orgNo = BYORGNO, TopORGNO = BYORGNO });
PatrolRouteStat_SW sw = new PatrolRouteStat_SW();
sw.orgNo = Request.Params["BYORGNO"];
sw.TopORGNO = Request.Params["BYORGNO"];
sw.DateBegin = Request.Params["TIMEBegin"];
sw.DateEnd = Request.Params["TIMEEnd"];
var list = HUReportCls.getPatrolRouteStatModel(sw);
int rowI = 0;
foreach (var item in list)
{
row = sheet1.CreateRow(rowI + 3);
row.CreateCell(0).SetCellValue(item.ORGName);
row.CreateCell(1).SetCellValue(item.LineCount);
row.CreateCell(2).SetCellValue(item.LineCount0);
row.CreateCell(3).SetCellValue(item.LineCount1);
row.CreateCell(4).SetCellValue(item.LineCount2);
row.CreateCell(5).SetCellValue(item.PointCount);
row.CreateCell(6).SetCellValue(item.PointCount0);
row.CreateCell(7).SetCellValue(item.PointCount1);
row.CreateCell(8).SetCellValue(item.PointCount2);
for (int i = 0; i < 9; i++)
{
if (i == 0)
{
row.GetCell(0).CellStyle = getCellStyleLeft(book);
}
else
{
row.GetCell(i).CellStyle = getCellStyleCenter(book);
}
}
rowI++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
string fileName = "巡检路线统计总表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
#endregion
}
