/// <summary>
/// 取得匯出Excel資料
/// </summary>
/// <param name="SearchTime"></param>
/// <returns></returns>
public async Task <ResponseViewModel> GetCheckRecordExcel(SearchTimeModel SearchTime)
{
ResponseViewModel res = new ResponseViewModel();
List <CheckRecordExportViewModel> list = new List <CheckRecordExportViewModel>();
try
{
var query = from a in _user.GetAll()
select new CheckRecordExportViewModel
{
ur_id = a.ur_id,
ur_ac = a.ur_ac,
checkRecordTestViewModel = (from b in _db.check_in
join c in _db.user on b.ur_id equals c.ur_id
where b.ur_id == a.ur_id && b.ci_da >= SearchTime.StartDateTime && b.ci_da <= SearchTime.EndDateTime
select new CheckRecordViewModel
{
ci_sn = b.ci_sn,
ur_id = b.ur_id,
ci_da = b.ci_da,
ci_ut = b.ci_ut,
ci_dt = b.ci_dt,
ci_ct = b.ci_ct,
Remark = b.Remark,
ur_ac = c.ur_ac
}).OrderBy(x => x.ci_da).ToList()
};
if (query.Any())
{
list = query.ToList();
res.Data = list;
res.Success = true;
res.Message = "取得匯出資料成功";
}
}
catch
{
res.Success = false;
res.Message = "取得匯出資料失敗";
}
return(await Task.Run(() => res));
}
public JsonResult GetExportCheckRecord(SearchTimeModel SearchTime)
{
ResponseViewModel res = new ResponseViewModel();
try
{
res.Data = SearchTime;
res.Success = true;
res.Message = "打卡紀錄匯出Excel成功";
res.HttpStatusCode = System.Net.HttpStatusCode.OK;
}
catch (Exception ex)
{
res.Exception = ex;
res.Success = false;
res.Message = "與伺服器連接發生錯誤";
res.HttpStatusCode = System.Net.HttpStatusCode.InternalServerError;
}
res.ResponseTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
return(Json(res, JsonRequestBehavior.DenyGet));
}
/// <summary>
/// 打卡紀錄列表轉Excel
/// </summary>
/// <param name="StartDateTime"></param>
/// <param name="EndDateTime"></param>
/// <returns></returns>
public async Task <FileContentResult> ExportCheckRecord(string StartDateTime, string EndDateTime)
{
List <CheckRecordExportViewModel> xlsList = new List <CheckRecordExportViewModel>();
SearchTimeModel SearchTime = new SearchTimeModel
{
StartDateTime = DateTime.Parse(StartDateTime),
EndDateTime = DateTime.Parse(EndDateTime)
};
var result = await _checkRecord.GetCheckRecordExcel(SearchTime);
var data = (List <CheckRecordExportViewModel>)result.Data;
xlsList = data;
////-----------------------------------------------------------------------------------------
var workbook = new XSSFWorkbook();
ISheet sheet;
MemoryStream output = new MemoryStream(); //Write the workbook to a memory stream
foreach (var item in xlsList)
{
sheet = workbook.CreateSheet(item.ur_ac);
_checkRecord.ExportExcel(item.checkRecordTestViewModel, workbook, sheet, item.ur_ac);
}
string fileName = string.Format("打卡紀錄-{0}.xlsx", string.Format("{0:yyyy-MM-dd hh:mm:ss}", DateTime.Now));
workbook.Write(output);
////---------------------------------------- Return the result to the end user
return(File(output.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
fileName)); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}