FullCalendarDemo5 控件的实例讲解—拖拽实现值班排班(五)
(五)c# asp.net 操作FullCalendarDemo5 导出排班记录
目的:点击导出按钮,弹出参数指定对话框,选择要导出数据的起始日期,点击导出按钮,程序将指定日期内的值班员数据按照值班日志模板文件进行导出下载。
引入以下两个插件,fancybox用于弹出模式窗口,配置参数;bootstrap-datepicker为日期选择插件,用于指定起始日期,该插件是基于bootstrap,所以还要引入bootstrap相应文件。
样式文件:
<link type="text/css" rel="stylesheet" href="css/bootstrap.min.css"/>
<link rel="stylesheet" href="assets/js/datepicker/datepicker.css"/>
<link type="text/css" rel="stylesheet" href="css/index_style.css"/>
<link type="text/css" rel="stylesheet" href="css/jquery.fancybox.css"/>
脚本文件:
<script src="./js/jquery-1.10.1.min.js"></script>
<script type="text/javascript" src="./js/jquery.fancybox.js"></script>
<script type="text/javascript" src="assets/js/datepicker/bootstrap-datepicker.js"></script>
因为fancybox弹出的时候至于页面顶部,挡住了日期选择插件的相关内容,因此要将日期选择插件的z-index值指定的比fancybox更大一点,下面自定义的样式:
<style>
.dropdown-menu {
z-index:9999 !important;/*日期插件显示要高于fancybox的显示*/
}
.callendar {
font-size:19px;
}
</style>
页面脚本:
$(function () {
//=======模式窗口======
$("#dutyconfig").fancybox({
'overlayOpacity': 0.8,
'centerOnScroll': true,
afterClose:function(){
window.location.href="dutyplay2-2.aspx";//窗口关闭的时候刷新页面
}
});
//日期选择模式窗口
$("#export").fancybox({
'overlayOpacity': 0.8,
'centerOnScroll': true
});
});
<body>页面加入如下内容:
导出按钮:
<a class="out" id="export" href="#export-duty" >
<i class="fa fa-sign-out" style="font-size:20px;" runat="server" onclick="exportduty_Click"></i>
</a>
点击导出按钮,弹出导出模式窗口:
<div style="display:block;">
<div id="export-duty" style="width:600px;height:100px;overflow:auto;display:none;">
<div id="wrap2">
<div id="external-events2">
<div style="margin:10px;">
选择要导出的排班日期
</div>
<div style="margin:10px;">
<label>从</label>
<input type="text" id="start" />
<label>到</label>
<input type="text" id="end" />
<button id="exporttheduty" onclick="" class="btn">导出</button>
</div>
</div>
</div>
</div>
</div>
将执行脚本写到页面的下面,原创不易,转载请注明作者蓝海云梦:
<script type="text/javascript">
$(function(){
$("#start").datepicker({
format: 'yyyy-mm-dd',
startDate: '2021' + '-01-01',
endDate: '2022-01-07',
autoclose:true,
todayHighlight: true,
language: 'zh-CN',
orientation:'button'
});
$("#end").datepicker({
format: 'yyyy-mm-dd',
startDate: '2021' + '-01-01',
endDate: '2022-01-07',
autoclose: true,
todayHighlight: true,
language: 'zh-CN',
orientation: 'button'
});
$("#exporttheduty").click(function () {
var start = $("#start").val();
var end = $("#end").val();
$.ajax({
type: "post",
url: "./ashx/exporttheduty.ashx", //使用一般处理程序来处理导出
data: {
startdate: start,
enddate: end,
},
success: function (data) {
// alert(data);
window.location.href=data;
},
error: function (data) {
alert("导出指定日期值班表出错," + data);
}
});
});
})
</script>
提交到exporttheduty.ashx进行处理导出到Excel模板文件过程。
引用:
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Collections.Generic;
核心程序:
public void ProcessRequest (HttpContext context) {
context.Response.ContentType = "text/plain";
//context.Response.Write("Hello World");
string startdate = context.Request["startdate"];
string enddate = context.Request["enddate"];
DateTime beginDate = Convert.ToDateTime(startdate);// DateTime.Today.AddDays(-(week - 1));
DateTime endDate = Convert.ToDateTime(enddate);
DateTime tempDate = beginDate;
List<dutyplay> lst_duty = new List<dutyplay>();
do
{
string sql = "select * from dutyplay where dutydate='" + tempDate + "' order by duty_time asc";
DataTable dt = DBHelp.GetDataSet(sql);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
dutyplay dp = new dutyplay();
dp.id = int.Parse(dt.Rows[i]["id"].ToString());
dp.dutydate = Convert.ToDateTime(dt.Rows[i]["dutydate"].ToString());
dp.personid = int.Parse(dt.Rows[i]["personid"].ToString());
if (dp.personid > 0 || dp.personid != null)
{
dp.personname = UserService.GetTheUsers(dp.personid.ToString()).UserName;
}
else
dp.personname = "";
lst_duty.Add(dp);
}
}
tempDate = tempDate.AddDays(1);
} while (tempDate != endDate.AddDays(1));
//导出数据
// string NewFileName = "本周值班表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
string tempFilePath = context.Request.PhysicalApplicationPath;
string newfilename = "值班表" +DateTime.Now.ToString("yyyy-MM-dd-HHmmss") +".xlsx";//Guid.NewGuid()
string newpath = tempFilePath + "web\\" + newfilename;
FileInfo mode = new FileInfo(tempFilePath+("web\\filetemplate\\dutyplay.xlsx"));
Excel.Application app = new Excel.Application();
if (app == null)
{
return;
}
app.Application.DisplayAlerts = false;
app.Visible = false;
if (mode.Exists)
{
Excel.Workbook tworkbook;
Object missing = System.Reflection.Missing.Value;
app.Workbooks.Add(missing);
//调用模板
tworkbook = app.Workbooks.Open(mode.FullName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing);
Excel.Worksheet tworksheet = (Excel.Worksheet)tworkbook.Sheets[1];
tworksheet.Cells[1, 1] = "值班表(" + lst_duty[0].dutydate.ToString("yyyy年MM月dd日") + "—" + lst_duty[lst_duty.Count - 1].dutydate.ToString("yyyy年MM月dd日") + ")";//星期一时间
tworksheet.Cells.HorizontalAlignment = HorizontalAlign.Center;
((Excel.Range)tworksheet.Cells[1, 1]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//当前行居中
int ii = 0;
for (int i = 0, c = 3; i < (lst_duty.Count / 2); i++, c++)
{
if (i == 0)
{
tworksheet.Cells[3, c] = lst_duty[i].dutydate.ToString("yyyy/MM/dd");//星期几对应的时间
tworksheet.Cells[4, c] = lst_duty[i].personname;
tworksheet.Cells[5, c] = lst_duty[i + 1].personname;
ii = i + 1;
}
else
{
tworksheet.Cells[3, c] = lst_duty[ii + 1].dutydate.ToString("yyyy/MM/dd");//星期几对应的时间
tworksheet.Cells[4, c] = lst_duty[ii + 1].personname;
tworksheet.Cells[5, c] = lst_duty[ii + 1 + 1].personname;
ii = ii + 2;
}
}
tworksheet.SaveAs(newpath, missing, missing, missing, missing, missing, missing, missing, missing, missing);
tworkbook.Close(false, mode.FullName, missing);
app.Workbooks.Close();
app.Quit();
if (app != null)
{
foreach (System.Diagnostics.Process p in System.Diagnostics.Process.GetProcessesByName("Excel"))
{
if (!p.CloseMainWindow())
{
p.Kill();
}
}
}
tworkbook = null;
app = null;
GC.Collect();
}
string downfile ="http://"+context.Request.UrlReferrer.Authority+ context.Request.ApplicationPath + @"/web/" + newfilename;
HttpContext.Current.Response.Write(downfile);
}
值班表的模板文件格式如下: