private void FillGSCommonItems(DataSet ds, List <ReportItemModel> rimList, ReportFormModel rfm)
{
DataTable dt = ds.Tables["GSReportItem"];
DataRow dr = dt.NewRow();
dr["ReceiveDate"] = rfm.ReceiveDate;
dr["SectionNo"] = rfm.SectionNo;
dr["TestTypeNo"] = rfm.TestTypeNo;
dr["SampleNo"] = rfm.SampleNo;
GSCommonItemModel gsItem;
string columnName;
foreach (ReportItemModel rim in rimList)
{
gsItem = rim as GSCommonItemModel;
if (gsItem == null)
{
continue;
}
columnName = "i" + gsItem.ItemNo.ToString() + "_" + "xue";
dr[columnName] = gsItem.XueValue;
columnName = "i" + gsItem.ItemNo.ToString() + "_" + "gs";
dr[columnName] = gsItem.GSValue;
}
dt.Rows.Add(dr);
}
/// <summary>
/// RG01 外出異常表
/// </summary>
/// <returns></returns>
public ActionResult RG01()
{
ViewData["LoginUserInfo"] = LoginUserInfo;
string ProgramID = "RG01";//員工外出異常表
ProgramModel SysProgram = null;
if (!GeneralObj.CheckProgID(LoginUserInfo.ProgramList, ProgramID))
{
return(View("../SysMsg/NoAccess"));
}
else
{
SysProgram = GeneralObj.GetProgram(LoginUserInfo.ProgramList, ProgramID);
ViewData["SysProgram"] = SysProgram;
}
ReportFormModel ReportForm = new ReportFormModel();
ReportForm.Company = LoginUserInfo.Company;
ReportForm.DepartMentNo = GeneralObj.SearchDepartMentDefault(LoginUserInfo, SysProgram);
ReportForm.EmpNo = "";
ReportForm.StartDate = DateTime.Now.ToString("yyyy-MM-dd");
ReportForm.EndDate = DateTime.Now.ToString("yyyy-MM-dd");
ViewData["ReportForm"] = ReportForm;
return(View());
}
public LisReport()
{
this._reportItemList = new List <ReportItemModel>();
this._specItemsTable = new Hashtable();
this._parItemList = new List <int>();
this._reportInfo = new ReportFormModel();
this._specItemsList = new List <ReportItemModel>();
}
private Hashtable GenderItemEqualFields(ReportFormModel rfm)
{
Hashtable ht = new Hashtable();
ht.Add("receivedate", rfm.ReceiveDate);
ht.Add("sectionno", rfm.SectionNo);
ht.Add("testtypeno", rfm.TestTypeNo);
ht.Add("sampleno", rfm.SampleNo);
return(ht);
}
//获取报告项查询条件集合
private void GenderItemEqualFields(ReportFormModel rfm, Hashtable ht)
{
//
ht.Remove("receivedate");
ht.Add("receivedate", rfm.ReceiveDate);
ht.Remove("sectionno");
ht.Add("sectionno", rfm.SectionNo);
ht.Remove("testtypeno");
ht.Add("testtypeno", rfm.TestTypeNo);
ht.Remove("sampleno");
ht.Add("sampleno", rfm.SampleNo);
}
//报告项集合填充
private void FillReportItems(List <ReportItemModel> rimList, ReportFormModel rfm, DataSet ds)
{
DataTable dt = ds.Tables["ReportItem"];
DataRow dr;
foreach (ReportItemModel rim in rimList)
{
//过滤不显示项
if (rim.SecretGrade > 0)
{
continue;
}
dr = dt.NewRow();
FillReportItem(rim, dr, rfm);
dt.Rows.Add(dr);
}
}
public IActionResult Add(ReportFormModel rep)
{
if (ModelState.IsValid)
{
var report = new EventReport
{
reportDescription = rep.reportDescription,
Approved = false,
areaId = _areaRepo.getAreaByName(rep.areaName).AreaId,
NocSubsQTYChange = rep.NocSubsQTYChange,
PHChange = rep.PHChange,
PollutionPercentageChange = rep.PollutionPercentageChange,
reporterUsername = User.Identity.Name,
ReportDate = DateTime.Now.ToString("MM/dd/yyyy")
};
_reportRepo.Add(report);
return(View("ReportAdded"));
}
return(View(new EventReport()));
}
//报告信息填充
private void FillReportInfo(ReportFormModel rfm, DataSet ds)
{
//Type reportFormType = rfm.GetType();
//PropertyInfo property;
DataTable dt = ds.Tables["ReportForm"];
DataRow dr = dt.NewRow();
dr["ReceiveDate"] = rfm.ReceiveDate;
dr["SectionNo"] = rfm.SectionNo;
dr["TestTypeNo"] = rfm.TestTypeNo;
dr["SampleNo"] = rfm.SampleNo;
dr["SerialNo"] = rfm.SerialNo;
dr["DeptName"] = rfm.DeptName;
dr["BedNo"] = rfm.BedNo;
dr["CName"] = rfm.CName;
dr["Sex"] = rfm.Sex;
dr["Age"] = rfm.Ager.ToString();
dr["PID"] = rfm.PID;
dr["SampleTypeName"] = rfm.SampleTypeName;
dr["ClinicTypeName"] = rfm.ClinicTypeName;
dr["DoctorName"] = rfm.BillingDoctor;
dr["ClinicalDiagnosis"] = rfm.ClinicalDiagnosis;
dr["Explanation"] = rfm.Explanation;
dr["ParItemName"] = rfm.ParItemName;
dr["TestDateTime"] = rfm.TestDateTime.ToString("yyyy-MM-dd");
dr["CollectDateTime"] = FormatDateTime(rfm.CollectDateTime, "yyyy-MM-dd HH:mm", "");
dr["InceptDateTime"] = FormatDateTime(rfm.InceptDateTime, "yyyy-MM-dd HH:mm", "");
dr["ReportDateTime"] = FormatDateTime(rfm.ReportDateTime, "yyyy-MM-dd HH:mm", "");
dr["ReportTitle"] = "";
dr["Remark"] = rfm.Remark;
dr["FormMemo"] = rfm.FormMemo;
dr["FormComment"] = rfm.FormComment;
dr["FormComment2"] = rfm.FormComment2;
dr["TechnicianImage"] = rfm.TechnicianImage;
dr["CheckerImage"] = rfm.CheckerImage;
dt.Rows.Add(dr);
}
//通用报告项数据填充
private void FillReportItem(ReportItemModel rim, DataRow dr, ReportFormModel rfm)
{
dr["ReceiveDate"] = rfm.ReceiveDate;
dr["SectionNo"] = rfm.SectionNo;
dr["TestTypeNo"] = rfm.TestTypeNo;
dr["SampleNo"] = rfm.SampleNo;
dr["ItemNo"] = rim.ItemNo;
dr["DispNo"] = rim.DispNo;
dr["ItemName"] = rim.ItemName;
if (rim.Precision > 0)
{
double temp, temp1;
bool r = double.TryParse(rim.ItemResult, out temp);
bool r1 = double.TryParse(rim.ItemEName, out temp1);
if (r == true)
{
dr["ItemResult"] = temp.ToString(NumberFormatString(rim.Precision));
}
else
{
dr["ItemResult"] = rim.ItemResult;
}
if (r1 == true)
{
dr["ItemEName"] = temp1.ToString(NumberFormatString(rim.Precision));
}
else
{
dr["ItemEName"] = rim.ItemEName;
}
}
else
{
dr["ItemEName"] = rim.ItemEName;
dr["ItemResult"] = rim.ItemResult;
}
dr["ResultStatus"] = rim.ResultStatus;
dr["ItemUnit"] = rim.ItemUnit;
dr["RefRange"] = rim.RefRange;
}
//获取生成文件全路径
private string GenderFileFullName(ReportFormModel rf, string rootPath, int order)
{
string fileFullPath = Path.Combine(rootPath, DateTime.Now.ToString("yyyy-MM-dd"), rf.SectionNo.ToString());
if (!Directory.Exists(fileFullPath))
{
Directory.CreateDirectory(fileFullPath);
}
string fileName = GenderFileName(rf, order);
string fileFullName = Path.Combine(fileFullPath, fileName);
if (File.Exists(fileFullName))
{
string newFileName = fileName.Substring(0, fileName.Length - 4);
newFileName += "_";
newFileName += DateTime.Now.ToString("HHmmss");
newFileName += ".pdf";
return(Path.Combine(fileFullPath, newFileName));
}
else
{
return(fileFullName);
}
}
//遗传染色体检验项填充
private void FillRanReportItem(List <ReportItemModel> rimList, DataSet ds, ReportFormModel rfm)
{
DataTable dt = ds.Tables["RanReportItem"];
DataRow dr = dt.NewRow();
dr["ReceiveDate"] = rfm.ReceiveDate;
dr["SectionNo"] = rfm.SectionNo;
dr["TestTypeNo"] = rfm.TestTypeNo;
dr["SampleNo"] = rfm.SampleNo;
foreach (ReportItemModel rim in rimList)
{
switch (rim.ItemNo)
{
case 90008528:
dr["CaryogramDesc"] = rim.ItemResult;
break;
case 90008797:
dr["SampleQuality"] = rim.ItemResult;
break;
case 90008798:
dr["CulturalMethod"] = rim.ItemResult;
break;
case 90008799:
dr["BandMethod"] = rim.ItemResult;
break;
default:
dr["Remark"] = rim.ItemResult;
break;
}
}
dt.Rows.Add(dr);
}
//获取生成文件名
private string GenderFileName(ReportFormModel rf, int order)
{
StringBuilder sb = new StringBuilder();
//病案号
if (rf.PID != null && !rf.PID.Equals(""))
{
sb.Append(rf.PID);
}
else
{
sb.Append('%');
}
sb.Append('_');
//身份证号
if (rf.CID != null && !rf.CID.Equals(""))
{
sb.Append(rf.CID);
}
else
{
sb.Append('%');
}
sb.Append('_');
//lis
sb.Append("lis");
sb.Append('_');
//门诊类型
if (rf.SickTypeNo == 1 || rf.SickTypeNo == 3)
{
//住院
sb.Append('I');
}
else if (rf.SickTypeNo == 2 || rf.SickTypeNo == 4)
{
//menzhen
sb.Append('O');
}
else
{
sb.Append('Q');
}
sb.Append('_');
//住院次数
if (rf.VisitTimes > 0)
{
sb.Append(rf.VisitTimes);
}
else
{
sb.Append('%');
}
sb.Append('_');
//采集时间
sb.Append(FormatDateTime(rf.CollectDateTime, "yyyyMMdd", "%"));
sb.Append('_');
//签收时间
sb.Append(FormatDateTime(rf.InceptDateTime, "yyyyMMdd", "%"));
sb.Append('_');
//报告时间
sb.Append(FormatDateTime(rf.ReportDateTime, "yyyyMMdd", "%"));
sb.Append('_');
//小组号
sb.Append(rf.SectionNo);
sb.Append('_');
//样本号 将\进行转义
sb.Append(rf.SampleNo.Replace('/', '-'));
sb.Append('_');
if (rf.SerialNo != null && !rf.SerialNo.Equals(""))
{
sb.Append(rf.SerialNo);
}
else
{
sb.Append('%');
}
sb.Append('_');
sb.Append(order);
sb.Append(".pdf");
return(sb.ToString());
}
public JsonResult GetRG01ToExcel(ReportFormModel ReportForm)
{
//初始****
if (ReportForm.EmpNo == null)
{
ReportForm.EmpNo = "";
}
if (ReportForm.Company == null)
{
ReportForm.Company = "";
}
if (ReportForm.DepartMentNo == null)
{
ReportForm.DepartMentNo = "";
}
if (ReportForm.EndDate == null)
{
ReportForm.EndDate = "";
}
if (ReportForm.StartDate == null)
{
ReportForm.StartDate = "";
}
JsonresultModel ResultModel = new JsonresultModel();
ResultModel.Result = "0";
try
{
DataTable AttendanceDT = null;
// DateTime StartDate = DateTime.Parse(ReportForm.StartDate);
// DateTime EndDate = DateTime.Parse(ReportForm.EndDate);
string filename = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
//SavePath=儲存路徑
string filenameStr = AppDomain.CurrentDomain.BaseDirectory.ToString() + "Download\\" + filename;
using (MemoryStream msExcel = ModelFactory.RG01ToExcel(ReportForm) as MemoryStream)// 新增試算表
{
byte[] data = msExcel.ToArray();
using (FileStream fileStream = new FileStream(filenameStr, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.None))
{
fileStream.Write(data, 0, data.Length);
fileStream.Flush();
fileStream.Close();
}
}
//賦予報表位址
ExcelModel Excel = new ExcelModel();
Excel.Url = "../Download/" + filename;
ResultModel.Result = "1";
ResultModel.Query = Excel;
return(Json(ResultModel, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(ResultModel, JsonRequestBehavior.AllowGet));
}
}
/// <summary>
/// 輸出出勤資料Excel
/// </summary>
/// <param name="SourceTable"></param>
/// <returns></returns>
public Stream RG01ToExcel(ReportFormModel ReportFrom)
{
List <RG01Model> RG01List = new List <RG01Model>();
string SQL = "SELECT *, ";
SQL += " CASE Status WHEN 'A' THEN '新增'WHEN 'U' THEN '編輯'WHEN 'D' THEN '刪除' WHEN 'AM' THEN '補登新增' WHEN 'U' THEN '補登編輯' WHEN 'DM' THEN '補登刪除' END AS StatusDisplay, ";
SQL += " Convert(char(10),Convert(datetime,OutDate),20) as OutDateFormat,Convert(char(10),Convert(datetime,UpdateDate),20) as UpdateDateFormat,left(UpdateTime,2)+':'+left(right(UpdateTime,4),2) AS UpdateTimeFormat ,left(OutTime,2)+':'+right(OutTime,2) AS OutTimeFormat ,left(GoOutTime,2)+':'+right(GoOutTime,2) AS GoOutTimeFormat FROM gvOutgoing WHERE 1=1";
if (ReportFrom.Company != "")
{
SQL += " AND Company = @Company ";
}
if (ReportFrom.EmpNo != "")
{
SQL += " AND OutMan = @OutMan ";
}
if (ReportFrom.DepartMentNo != "")
{
SQL += " AND DepartMentNo = @DepartMentNo ";
}
SQL += " AND OutDate BETWEEN @StartDate AND @EndDate ";
SQL += " AND AlermTotal > 0 Order by OutMan,OutDate,OutTime";
DataTable dt = DbAccess.ExecuteDataTable(SQL,
new DbParameter[] {
DataAccess.CreateParameter("Company", DbType.String, ReportFrom.Company.ToString()),
DataAccess.CreateParameter("OutMan", DbType.String, ReportFrom.EmpNo.ToString()),
DataAccess.CreateParameter("DepartMentNo", DbType.String, ReportFrom.DepartMentNo.ToString()),
DataAccess.CreateParameter("StartDate", DbType.String, ReportFrom.StartDate.ToString().Replace("-", "")),
DataAccess.CreateParameter("EndDate", DbType.String, ReportFrom.EndDate.ToString().Replace("-", ""))
}
);
for (int i = 0; i < dt.Rows.Count; i++)
{
RG01Model tmpM = new RG01Model();
tmpM.OutId = dt.Rows[i]["OutId"].ToString();
tmpM.OutDate = dt.Rows[i]["OutDateFormat"].ToString();
tmpM.EmployeeName = dt.Rows[i]["EmployeeName"].ToString();
tmpM.OutTime = dt.Rows[i]["OutTimeFormat"].ToString();
tmpM.OutMan = dt.Rows[i]["OutMan"].ToString();
tmpM.Status = dt.Rows[i]["StatusDisplay"].ToString();
tmpM.CompanyName = dt.Rows[i]["CompanyName"].ToString();
tmpM.DepartMentName = dt.Rows[i]["DepartMentName"].ToString();
tmpM.AlermTotal = dt.Rows[i]["AlermTotal"].ToString();
tmpM.GoOutTime = dt.Rows[i]["GoOutTimeFormat"].ToString();
tmpM.CustomerName = dt.Rows[i]["CustomerName"].ToString();
tmpM.Location = dt.Rows[i]["Location"].ToString();
/*
* SELECT
* Convert(char(10),Convert(datetime,UpdateDate),20) as UpdateDateFormat,left(UpdateTime,2)+':'+left(right(UpdateTime,4),2) AS UpdateTimeFormat ,left(OutTime,2)+':'+right(OutTime,2) AS OutTimeFormat ,left(GoOutTime,2)+':'+right(GoOutTime,2) AS GoOutTimeFormat
* FROM gvOutgoing
*/
SQL = "SELECT *, ";
SQL += " CASE Alerm WHEN 0 THEN '正常' WHEN 1 THEN '異常' END AS AlermDisplay,CASE Status WHEN 'A' THEN '新增'WHEN 'U' THEN '編輯'WHEN 'D' THEN '刪除' WHEN 'AM' THEN '補登新增' WHEN 'U' THEN '補登編輯' WHEN 'DM' THEN '補登刪除' END AS StatusDisplay, ";
SQL += " Convert(char(10),Convert(datetime,OutDate),20) as OutDateFormat,Convert(char(10),Convert(datetime,UpdateDate),20) as UpdateDateFormat,left(UpdateTime,2)+':'+left(right(UpdateTime,4),2) AS UpdateTimeFormat ,left(OutTime,2)+':'+right(OutTime,2) AS OutTimeFormat ,left(GoOutTime,2)+':'+right(GoOutTime,2) AS GoOutTimeFormat ";
SQL += " FROM gvOutHistory WHERE OutId = @OutId AND OutMan = @OutMan Order by UpdateDate,UpdateTime";
DataTable DtDtl = DbAccess.ExecuteDataTable(SQL,
new DbParameter[] {
DataAccess.CreateParameter("OutId", DbType.String, tmpM.OutId),
DataAccess.CreateParameter("OutMan", DbType.String, tmpM.OutMan),
}
);
for (int j = 0; j < DtDtl.Rows.Count; j++)
{
RG01DtlModel tmpDtl = new RG01DtlModel();
tmpDtl.OutDate = DtDtl.Rows[j]["OutDateFormat"].ToString();
tmpDtl.OutTime = DtDtl.Rows[j]["OutTimeFormat"].ToString();
tmpDtl.CustomerName = DtDtl.Rows[j]["CustomerName"].ToString();
tmpDtl.GoOutTime = DtDtl.Rows[j]["GoOutTimeFormat"].ToString();
tmpDtl.RecordManName = DtDtl.Rows[j]["RecordManName"].ToString();
tmpDtl.Location = DtDtl.Rows[j]["Location"].ToString();
tmpDtl.Status = DtDtl.Rows[j]["StatusDisplay"].ToString();
tmpDtl.UpdateDate = DtDtl.Rows[j]["UpdateDateFormat"].ToString();
tmpDtl.UpdateTime = DtDtl.Rows[j]["UpdateTimeFormat"].ToString();
tmpDtl.Alerm = DtDtl.Rows[j]["AlermDisplay"].ToString();
tmpM.RG01Dtl.Add(tmpDtl);
}
RG01List.Add(tmpM);
}
IWorkbook workbook = new XSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = (XSSFSheet)workbook.CreateSheet();
//定義EmpInfo顏色
XSSFCellStyle EmpInfoStyle = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFColor BgColor = new XSSFColor();
BgColor.SetRgb(new byte[] { 102, 204, 255 });
EmpInfoStyle.SetFillForegroundColor(BgColor);
EmpInfoStyle.FillPattern = FillPattern.SolidForeground;
//定義Header顏色
XSSFCellStyle HeaderStyle = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFColor BgColorHeader = new XSSFColor();
BgColorHeader.SetRgb(new byte[] { 252, 213, 180 });
HeaderStyle.SetFillForegroundColor(BgColorHeader);
HeaderStyle.FillPattern = FillPattern.SolidForeground;
//定義Header顏色
XSSFCellStyle BodyStyle = (XSSFCellStyle)workbook.CreateCellStyle() as XSSFCellStyle;
XSSFColor BgColorBody = new XSSFColor();
BgColorBody.SetRgb(new byte[] { 216, 218, 188 });
BodyStyle.SetFillForegroundColor(BgColorBody);
BodyStyle.FillPattern = FillPattern.SolidForeground;
int IntRow = 0; //Excel 第幾列
string IntEmp = "";
for (int i = 0; i < RG01List.Count; i++)
{
if (IntEmp != RG01List[i].OutMan)
{
if (IntEmp != "")
{
//換行!!
IRow SpaceDataRowV = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell tmpCellV = (XSSFCell)SpaceDataRowV.CreateCell(0);
IntRow++;
}
//員工資料＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
IRow EmpInfo = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell Cell0 = (XSSFCell)EmpInfo.CreateCell(0);
Cell0.SetCellValue("員工編號");
Cell0.CellStyle = EmpInfoStyle;
XSSFCell Cell1 = (XSSFCell)EmpInfo.CreateCell(1);
Cell1.SetCellValue("員工姓名");
Cell1.CellStyle = EmpInfoStyle;
XSSFCell Cell2 = (XSSFCell)EmpInfo.CreateCell(2);
Cell2.SetCellValue("公 司 別");
Cell2.CellStyle = EmpInfoStyle;
XSSFCell Cell3 = (XSSFCell)EmpInfo.CreateCell(3);
Cell3.SetCellValue("部 門");
Cell3.CellStyle = EmpInfoStyle;
XSSFCell Cell4 = (XSSFCell)EmpInfo.CreateCell(4);
Cell4.SetCellValue("異常次數");
Cell4.CellStyle = EmpInfoStyle;
IntRow++;
IRow EmpInfoContent = (XSSFRow)sheet.CreateRow(IntRow);
EmpInfoContent.CreateCell(0).SetCellValue(RG01List[i].OutMan);
EmpInfoContent.CreateCell(1).SetCellValue(RG01List[i].EmployeeName);
EmpInfoContent.CreateCell(2).SetCellValue(RG01List[i].CompanyName);
EmpInfoContent.CreateCell(3).SetCellValue(RG01List[i].DepartMentName);
int AlermCount = 0;
AlermCount = (from tmp in RG01List
where tmp.OutMan == RG01List[i].OutMan
select tmp).Count();
EmpInfoContent.CreateCell(4).SetCellValue(AlermCount);
IntRow++;
IntEmp = RG01List[i].OutMan;
//＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊員工資料
}
//單頭資料＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
IRow headerRow = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell headerCell2 = (XSSFCell)headerRow.CreateCell(2);
headerCell2.SetCellValue("外出日期");
headerCell2.CellStyle = HeaderStyle;
XSSFCell headerCell3 = (XSSFCell)headerRow.CreateCell(3);
headerCell3.SetCellValue("預計外出時間");
headerCell3.CellStyle = HeaderStyle;
XSSFCell headerCell4 = (XSSFCell)headerRow.CreateCell(4);
headerCell4.SetCellValue("會議時間");
headerCell4.CellStyle = HeaderStyle;
XSSFCell headerCell5 = (XSSFCell)headerRow.CreateCell(5);
headerCell5.SetCellValue("客戶名稱");
headerCell5.CellStyle = HeaderStyle;
XSSFCell headerCell6 = (XSSFCell)headerRow.CreateCell(6);
headerCell6.SetCellValue("地 點");
headerCell6.CellStyle = HeaderStyle;
XSSFCell headerCell7 = (XSSFCell)headerRow.CreateCell(7);
headerCell7.SetCellValue("單據狀態");
headerCell7.CellStyle = HeaderStyle;
IntRow++;
IRow headerContentRow = (XSSFRow)sheet.CreateRow(IntRow);
headerContentRow.CreateCell(2).SetCellValue(RG01List[i].OutDate);
headerContentRow.CreateCell(3).SetCellValue(RG01List[i].OutTime);
headerContentRow.CreateCell(4).SetCellValue(RG01List[i].GoOutTime);
headerContentRow.CreateCell(5).SetCellValue(RG01List[i].CustomerName);
headerContentRow.CreateCell(6).SetCellValue(RG01List[i].Location);
headerContentRow.CreateCell(7).SetCellValue(RG01List[i].Status);
IntRow++;
//＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊單頭資料
//單身資料＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
IRow bodyRow = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell bodyCell4 = (XSSFCell)bodyRow.CreateCell(4);
bodyCell4.SetCellValue("項 次");
bodyCell4.CellStyle = BodyStyle;
XSSFCell bodyCell5 = (XSSFCell)bodyRow.CreateCell(5);
bodyCell5.SetCellValue("系統判定");
bodyCell5.CellStyle = BodyStyle;
XSSFCell bodyCell6 = (XSSFCell)bodyRow.CreateCell(6);
bodyCell6.SetCellValue("單據狀態");
bodyCell6.CellStyle = BodyStyle;
XSSFCell bodyCell7 = (XSSFCell)bodyRow.CreateCell(7);
bodyCell7.SetCellValue("外出日期");
bodyCell7.CellStyle = BodyStyle;
XSSFCell bodyCell8 = (XSSFCell)bodyRow.CreateCell(8);
bodyCell8.SetCellValue("外出時間");
bodyCell8.CellStyle = BodyStyle;
XSSFCell bodyCell9 = (XSSFCell)bodyRow.CreateCell(9);
bodyCell9.SetCellValue("會議時間");
bodyCell9.CellStyle = BodyStyle;
XSSFCell bodyCell10 = (XSSFCell)bodyRow.CreateCell(10);
bodyCell10.SetCellValue("客戶名稱");
bodyCell10.CellStyle = BodyStyle;
XSSFCell bodyCell11 = (XSSFCell)bodyRow.CreateCell(11);
bodyCell11.SetCellValue("地 點");
bodyCell11.CellStyle = BodyStyle;
XSSFCell bodyCell12 = (XSSFCell)bodyRow.CreateCell(12);
bodyCell12.SetCellValue("編輯時間");
bodyCell12.CellStyle = BodyStyle;
XSSFCell bodyCell13 = (XSSFCell)bodyRow.CreateCell(13);
bodyCell13.SetCellValue("編 輯 者");
bodyCell13.CellStyle = BodyStyle;
IntRow++;
for (int j = 0; j < RG01List[i].RG01Dtl.Count; j++)
{
IRow ItemRow = (XSSFRow)sheet.CreateRow(IntRow);
ItemRow.CreateCell(4).SetCellValue((j + 1).ToString());
ItemRow.CreateCell(5).SetCellValue(RG01List[i].RG01Dtl[j].Alerm);
ItemRow.CreateCell(6).SetCellValue(RG01List[i].RG01Dtl[j].Status);
ItemRow.CreateCell(7).SetCellValue(RG01List[i].RG01Dtl[j].OutDate);
ItemRow.CreateCell(8).SetCellValue(RG01List[i].RG01Dtl[j].OutTime);
ItemRow.CreateCell(9).SetCellValue(RG01List[i].RG01Dtl[j].GoOutTime);
ItemRow.CreateCell(10).SetCellValue(RG01List[i].RG01Dtl[j].CustomerName);
ItemRow.CreateCell(11).SetCellValue(RG01List[i].RG01Dtl[j].Location);
ItemRow.CreateCell(12).SetCellValue(RG01List[i].RG01Dtl[j].UpdateDate + " " + RG01List[i].RG01Dtl[j].UpdateTime);
ItemRow.CreateCell(13).SetCellValue(RG01List[i].RG01Dtl[j].RecordManName);
IntRow++;
}
//＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊單身資料
//換行!!
IRow SpaceDataRow = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell tmpCell = (XSSFCell)SpaceDataRow.CreateCell(0);
IntRow++;
}
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
sheet = null;
workbook = null;
return(ms);
}
/// <summary>
/// 对行赋值
/// </summary>
/// <param name="row">行对象</param>
/// <param name="item">数据对象</param>
private void SetNoTemplateDataRowValue(HSSFWorkbook workBook, IRow row, T item)
{
ICell cell;
ReportFormModel ledgerTableModel = item as ReportFormModel;
cell = row.CreateCell(0);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Dw);
cell = row.CreateCell(1);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Qsrq);
cell = row.CreateCell(2);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Jzrq);
cell = row.CreateCell(3);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Blfs);
cell = row.CreateCell(4);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Esffs);
cell = row.CreateCell(5);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Ysffs);
cell = row.CreateCell(6);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Zyfe);
cell = row.CreateCell(7);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Fpfs);
cell = row.CreateCell(8);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Qsje);
cell = row.CreateCell(9);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Qsfs_1);
cell = row.CreateCell(10);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Qsfs_1_5);
cell = row.CreateCell(11);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Qsfs_3);
cell = row.CreateCell(12);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Yysje);
cell = row.CreateCell(13);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Yysfs);
cell = row.CreateCell(14);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Cjje);
cell = row.CreateCell(15);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Jyfje);
cell = row.CreateCell(16);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Jyfjje);
cell = row.CreateCell(17);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Gsje);
cell = row.CreateCell(18);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Gsfs);
cell = row.CreateCell(19);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Gsfs_1);
cell = row.CreateCell(20);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Gsfs_20);
cell = row.CreateCell(21);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Mgsfs);
cell = row.CreateCell(22);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Tdzzsje);
cell = row.CreateCell(23);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Tdzzsfs);
cell = row.CreateCell(24);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Yhsje);
cell = row.CreateCell(25);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Yhsfs);
cell = row.CreateCell(26);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Cqjmse);
cell = row.CreateCell(27);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Cqjmfs);
cell = row.CreateCell(28);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Gfjmse);
cell = row.CreateCell(29);
cell.CellStyle = GetCellStyle("BorderStyle");
cell.SetCellValue(ledgerTableModel.Gfjmfs);
}
/// <summary>
/// 輸出員工出勤補登記錄表Excel
/// </summary>
/// <param name="SourceTable"></param>
/// <returns></returns>
public Stream RA02ToExcel(ReportFormModel ReportFrom)
{
List <RA02Model> RG02List = new List <RA02Model>();
string SQL = " SELECT * FROM vAttendanceCheckIn WHERE 1=1";
if (ReportFrom.Company != "")
{
SQL += " AND Company = @Company ";
}
if (ReportFrom.EmpNo != "")
{
SQL += " AND EmployeeNo = @EmployeeNo ";
}
if (ReportFrom.DepartMentNo != "")
{
SQL += " AND DepartMentNo = @DepartMentNo ";
}
SQL += " AND CardDate BETWEEN @StartDate AND @EndDate AND Status = @Status";
SQL += " Order by EmployeeNo,CardDate,CardTime ";
DataTable dt = DbAccess.ExecuteDataTable(SQL,
new DbParameter[] {
DataAccess.CreateParameter("Company", DbType.String, ReportFrom.Company.ToString()),
DataAccess.CreateParameter("EmployeeNo", DbType.String, ReportFrom.EmpNo.ToString()),
DataAccess.CreateParameter("DepartMentNo", DbType.String, ReportFrom.DepartMentNo.ToString()),
DataAccess.CreateParameter("StartDate", DbType.String, ReportFrom.StartDate.ToString()),
DataAccess.CreateParameter("EndDate", DbType.String, ReportFrom.EndDate.ToString()),
DataAccess.CreateParameter("Status", DbType.String, ReportFrom.EmpStatus.ToString())
}
);
IWorkbook workbook = new XSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = (XSSFSheet)workbook.CreateSheet();
IRow headerRow = (XSSFRow)sheet.CreateRow(0);
//定義輸出欄位
List <DataTableDisplay> DisplayList = new List <DataTableDisplay>();
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "EmployeeNo",
DisplayName = "員工編號"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "DepartMentName",
DisplayName = "部門"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "EmployeeName",
DisplayName = "姓名"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "EmployeeEName",
DisplayName = "英文名"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "CompanyName",
DisplayName = "公司別"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "CardDate",
DisplayName = "打卡日期"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "CardTime",
DisplayName = "打卡時間"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "CardType",
DisplayName = "打卡類別"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "CheckInDescription",
DisplayName = "補登原因"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "CheckInEmployeeName",
DisplayName = "補登人"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "CheckInDate",
DisplayName = "補登日期"
});
DisplayList.Add(new DataTableDisplay()
{
ColumnName = "CheckInTime",
DisplayName = "補登時間"
});
for (int i = 0; i < DisplayList.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(DisplayList[i].DisplayName);
}
int IntRow = 1;
if (dt.Rows.Count > 0)
{
string BrEmpNo = dt.Rows[0]["EmployeeNo"].ToString();
string Test = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
if (BrEmpNo != dt.Rows[i]["EmployeeNo"].ToString())
{
//換行!!
IRow tmpDataRow = (XSSFRow)sheet.CreateRow(IntRow);
XSSFCell tmpCell = (XSSFCell)tmpDataRow.CreateCell(0);
IntRow++;
BrEmpNo = dt.Rows[i]["EmployeeNo"].ToString();
}
IRow dataRow = (XSSFRow)sheet.CreateRow(IntRow);
for (int j = 0; j < DisplayList.Count; j++)
{
try
{
Test = dt.Rows[i][DisplayList[j].ColumnName].ToString();
}
catch
{
//查無此欄位 則空著!!
XSSFCell CellR = (XSSFCell)dataRow.CreateCell(j);
CellR.SetCellValue("");
continue;
}
XSSFCell Cell = (XSSFCell)dataRow.CreateCell(j);
Cell.SetCellValue(dt.Rows[i][DisplayList[j].ColumnName].ToString());
}
IntRow++;
}
}
workbook.Write(ms);
ms.Flush();
sheet = null;
headerRow = null;
workbook = null;
return(ms);
}
