private void ExportExcelCC()
{
string filePath = Server.MapPath("/AppFile/Excel/ChamCongSimax.xls");
HSSFWorkbook wb = ExcelNpoi.ReadExcelToHSSFWorkBook(filePath); //tao excel ten ban excel duoc dua vao
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0); //tao sheet moi tu excel duoc chon sheet
#region Create font word
HSSFCellStyle xWhiteCN = (HSSFCellStyle)wb.CreateCellStyle();
var setStyleCellWhiteCN = xsSetStyle(xWhiteCN, NPOI.HSSF.Util.HSSFColor.White.Index, NPOI.SS.UserModel.BorderStyle.Dotted);
HSSFCellStyle xYellow = (HSSFCellStyle)wb.CreateCellStyle();
var setStyleCellYellow = xsSetStyle(xYellow, NPOI.HSSF.Util.HSSFColor.Yellow.Index, NPOI.SS.UserModel.BorderStyle.Dotted);
HSSFCellStyle xBlue = (HSSFCellStyle)wb.CreateCellStyle();
var setStyleCellBlue = xsSetStyle(xBlue, NPOI.HSSF.Util.HSSFColor.Aqua.Index, NPOI.SS.UserModel.BorderStyle.Dotted);
//Merge cell
#endregion
List <KETQUACHAMCONGEntities> listKetQuaChamCong = dapCHAMCONG.ListChamCong();
List <KETQUACHAMCONGEntities> listDayOfMonth = dapCHAMCONG.ListNgay();
List <KETQUATINHEntities> listKetQuaTinh = dapCHAMCONG.listKetQua();
List <DANGKYONSITEEtities> listOnsite = dapDANGKYONSITE.ListOnsite();
using (FileStream excel = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
#region Replace tiêu đề tháng
var date = sheet.GetRow(1).GetCell(0);
date.SetCellValue("Từ ngày " + (listKetQuaChamCong[0].NGAY).ToString("dd/MM/yyyy") + " đến ngày " + (listKetQuaChamCong[30].NGAY).ToString("dd/MM/yyyy"));
#endregion
#region Replace thứ va ngay trong tháng
for (var i = 4; i < 35; i++)
{
var rowNgay = sheet.GetRow(5).GetCell(i);
rowNgay.SetCellValue(listKetQuaChamCong[i - 4].DAY);
var rowThu = sheet.GetRow(6).GetCell(i);
rowThu.SetCellValue(listKetQuaChamCong[i - 4].THU);
}
#endregion
#region Đổ dữ liệu ra sheet
int rowCopy = 7;
int rowIndex = 9;
int rowIndexCopy = 11;
int stt = 0;
string userID = listKetQuaChamCong[0].USERID; //Lấy Id đầu tiên làm chuẩn để so sánh với các Id khác
IRow worksheetRow = sheet.GetRow(rowIndex);
TimeSpan dateMorning = TimeSpan.Parse("08:00"); //Hours by default
TimeSpan dateAfternoon = TimeSpan.Parse("17:30"); //Timeout by default
//dump ChamCong the data in excel
for (int i = 0; i < listKetQuaChamCong.Count;)
{
//Nếu trùng id thì chỉ cho đổi theo chiều ngang
if (userID != null && userID == listKetQuaChamCong[i].USERID)
{
worksheetRow.GetCell(0).SetCellValue(stt + 1);
worksheetRow.GetCell(1).SetCellValue("");
MergeCell(sheet, rowIndex, 1);
worksheetRow.GetCell(2).SetCellValue(String.IsNullOrEmpty(listKetQuaChamCong[i].FULLNAME) ? "" : listKetQuaChamCong[i].FULLNAME);
MergeCell(sheet, rowIndex, 2);
//Dump ChamCong
for (int j = 4; j < listDayOfMonth.Count + 4; j++)
{
if (listKetQuaChamCong[i].NGAY == listDayOfMonth[j - 4].NGAY)
{
TimeSpan indexGioDen = TimeSpan.Parse(String.IsNullOrEmpty(listKetQuaChamCong[i].GIODEN) ? "00:00" : listKetQuaChamCong[i].GIODEN);
TimeSpan indexGioRa = TimeSpan.Parse(String.IsNullOrEmpty(listKetQuaChamCong[i].GIORA) ? "18:00" : listKetQuaChamCong[i].GIORA);
int resultGioDen = TimeSpan.Compare(indexGioDen, dateMorning);
int resultGioRa = TimeSpan.Compare(indexGioRa, dateAfternoon);
if (resultGioDen > 0 || resultGioRa < 0)
{
if (listKetQuaChamCong[i].THU == "T7")
{
if (resultGioDen > 0)
{
//In ra giờ đến
worksheetRow.GetCell(j).SetCellValue(string.IsNullOrEmpty(listKetQuaChamCong[i].GIODEN) ? "" : listKetQuaChamCong[i].GIODEN);
//worksheetRow.GetCell(j).CellStyle = xsStyleOrange;
worksheetRow.GetCell(j).CellStyle = setStyleCellYellow;
IRow rowIndexGioRa = sheet.GetRow(rowIndex + 1);
//In ra giờ về
rowIndexGioRa.GetCell(j).SetCellValue(string.IsNullOrEmpty(listKetQuaChamCong[i].GIORA) ? "" : listKetQuaChamCong[i].GIORA);
rowIndexGioRa.GetCell(j).CellStyle = setStyleCellYellow;
i++;
}
else
{
//In ra giờ đến
worksheetRow.GetCell(j).SetCellValue(string.IsNullOrEmpty(listKetQuaChamCong[i].GIODEN) ? "" : listKetQuaChamCong[i].GIODEN);
IRow rowIndexGioRa = sheet.GetRow(rowIndex + 1);
//In ra giờ về
rowIndexGioRa.GetCell(j).SetCellValue(string.IsNullOrEmpty(listKetQuaChamCong[i].GIORA) ? "" : listKetQuaChamCong[i].GIORA);
i++;
}
}
else
{
//In ra giờ đến
worksheetRow.GetCell(j).SetCellValue(string.IsNullOrEmpty(listKetQuaChamCong[i].GIODEN) ? "" : listKetQuaChamCong[i].GIODEN);
worksheetRow.GetCell(j).CellStyle = setStyleCellYellow;
IRow rowIndexGioRa = sheet.GetRow(rowIndex + 1);
//In ra giờ về
rowIndexGioRa.GetCell(j).SetCellValue(string.IsNullOrEmpty(listKetQuaChamCong[i].GIORA) ? "" : listKetQuaChamCong[i].GIORA);
rowIndexGioRa.GetCell(j).CellStyle = setStyleCellYellow;
i++;
}
}
else
{
//In ra giờ đến
worksheetRow.GetCell(j).SetCellValue(string.IsNullOrEmpty(listKetQuaChamCong[i].GIODEN) ? "" : listKetQuaChamCong[i].GIODEN);
IRow rowIndexGioRa = sheet.GetRow(rowIndex + 1);
//In ra giờ về
rowIndexGioRa.GetCell(j).SetCellValue(string.IsNullOrEmpty(listKetQuaChamCong[i].GIORA) ? "" : listKetQuaChamCong[i].GIORA);
if (listKetQuaChamCong[i].GIODEN == null && listKetQuaChamCong[i].GIORA == null)
{
worksheetRow.GetCell(j).SetCellValue("V");
MergeCell(sheet, rowIndex, j);
}
if (listKetQuaChamCong[i].THU == "CN")
{
worksheetRow.GetCell(j).CellStyle = setStyleCellWhiteCN;
worksheetRow.GetCell(j).SetCellValue(string.IsNullOrEmpty(listKetQuaChamCong[i].GIODEN) ? "" : listKetQuaChamCong[i].GIODEN);
rowIndexGioRa.GetCell(j).SetCellValue(string.IsNullOrEmpty(listKetQuaChamCong[i].GIORA) ? "" : listKetQuaChamCong[i].GIORA);
}
i++;
}
}
else
{
continue;
}
}
//Dump Sosite
for (int n = 0; n < listOnsite.Count; n++)
{
if (userID == listOnsite[n].UserId)
{
try
{
for (int j = 4; j < listDayOfMonth.Count + 4; j++)
{
if (listDayOfMonth[j - 4].THU == "CN")
{
worksheetRow.GetCell(j).CellStyle = setStyleCellWhiteCN;
worksheetRow.GetCell(j).SetCellValue("");
IRow rowIndexGioRa = sheet.GetRow(rowIndex + 1);
rowIndexGioRa.GetCell(j).SetCellValue("");
}
else
{
if (listDayOfMonth[j - 4].NGAY <= listOnsite[n].DenNgay && listDayOfMonth[j - 4].NGAY >= listOnsite[n].TuNgay)
{
if (listOnsite[n].TuBuoi == "1" || listOnsite[n].DenBuoi == "1")
{
worksheetRow.GetCell(j).SetCellValue("O");
worksheetRow.GetCell(j).CellStyle = setStyleCellBlue;
}
if (listOnsite[n].TuBuoi == "2" || listOnsite[n].DenBuoi == "2")
{
IRow rowIndexGioRa = sheet.GetRow(rowIndex + 1);
rowIndexGioRa.GetCell(j).SetCellValue("O");
rowIndexGioRa.GetCell(j).CellStyle = setStyleCellBlue;
}
if (listDayOfMonth[j - 4].NGAY <listOnsite[n].DenNgay && listDayOfMonth[j - 4].NGAY> listOnsite[n].TuNgay)
{
worksheetRow.GetCell(j).SetCellValue("O");
worksheetRow.GetCell(j).CellStyle = setStyleCellBlue;
MergeCell(sheet, rowIndex, j);
}
}
else
{
continue;
}
}
}
}
catch
{
}
}
else
{
continue;
}
}
//Dump KetQua
for (int k = 0; k < listKetQuaTinh.Count;)
{
if (userID == listKetQuaTinh[k].Userid)
{
for (int m = 35; m < 51;)
{
worksheetRow.GetCell(m).SetCellValue(string.IsNullOrEmpty(listKetQuaTinh[k].NgayCongChuan) ? "0" : listKetQuaTinh[k].NgayCongChuan);
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue(string.IsNullOrEmpty(listKetQuaTinh[k].NgayCongNT) ? "0" : listKetQuaTinh[k].NgayCongNT);
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue("0");
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue(string.IsNullOrEmpty(listKetQuaTinh[k].GioCongNT) ? "0" : listKetQuaTinh[k].GioCongNT);
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue("0");
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue(string.IsNullOrEmpty(listKetQuaTinh[k].LanVaoTre) ? "0" : listKetQuaTinh[k].LanVaoTre);
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue(string.IsNullOrEmpty(listKetQuaTinh[k].PhutVaoTre) ? "0" : listKetQuaTinh[k].PhutVaoTre);
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue(string.IsNullOrEmpty(listKetQuaTinh[k].LanRaSom) ? "0" : listKetQuaTinh[k].LanRaSom);
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue(string.IsNullOrEmpty(listKetQuaTinh[k].PhutRaSom) ? "0" : listKetQuaTinh[k].PhutRaSom);
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue(string.IsNullOrEmpty(listKetQuaTinh[k].GioTangCa) ? "0" : listKetQuaTinh[k].GioTangCa);
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue("0");
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue("");
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue(string.IsNullOrEmpty(listKetQuaTinh[k].VangKp) ? "0" : listKetQuaTinh[k].VangKp);
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue("");
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue("");
MergeCell(sheet, rowIndex, m);
m++;
worksheetRow.GetCell(m).SetCellValue("0");
MergeCell(sheet, rowIndex, m);
m++;
}
}
k++;
}
}
//Khác id xuất dữ liệu xuống dòng tiếp theo
else
{
MergeCell(sheet, rowIndexCopy, 1);
MergeCell(sheet, rowIndexCopy, 2);
stt++;
worksheetRow = sheet.GetRow(rowIndexCopy);
sheet.CopyRow(rowCopy, rowIndexCopy);
sheet.CopyRow(rowCopy + 1, rowIndexCopy + 1);
rowIndexCopy = rowIndexCopy + 2;
rowIndex = rowIndex + 2;
userID = listKetQuaChamCong[i].USERID;
}
}
#region Delete first row
var rowDelete1 = sheet.GetRow(rowCopy);
var rowDelete2 = sheet.GetRow(rowCopy + 1);
if (rowDelete1 != null)
{
sheet.RemoveRow(rowDelete1);
sheet.RemoveRow(rowDelete2);
sheet.ShiftRows(rowCopy + 1, sheet.LastRowNum, -1);
sheet.ShiftRows(rowCopy + 1, sheet.LastRowNum, -1);
}
#endregion
#endregion
#region ExportExcel
MemoryStream exportData = new MemoryStream();
wb.Write(exportData);
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-disposition", "attachment; filename=ChamCong2020.xls");
Response.Clear();
Response.BinaryWrite(exportData.GetBuffer());
Response.End();
#endregion
}
}
public IRow CopyRowTo(int targetIndex)
{
return(sheet.CopyRow(RowNum, targetIndex));
}
public static EmailExtend.FileAttachment OptInDailyReport(string startTime, string endTime)
{
using (FileStream fs = new FileStream(EmailExtend.MapPath("/ReportMuban/optindailyemail.xls"), FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
var dailyInfo = DbFunction.OptInDailyReportEmail(startTime, endTime);
var datenow = Convert.ToDateTime(startTime);
HSSFSheet sheetDaily = (HSSFSheet)hssfworkbook.GetSheetAt(0);
hssfworkbook.SetSheetName(0, datenow.ToString("MMddyyyy"));
sheetDaily.GetRow(0).GetCell(0).SetCellValue(SystemConfig.HotelName);
sheetDaily.GetRow(0).GetCell(9).SetCellValue(datenow.Year);
sheetDaily.GetRow(1).GetCell(7).SetCellValue(ExcelExtend.DatetTimeFormate(datenow));
if (dailyInfo != null)
{
sheetDaily.GetRow(4).GetCell(9).SetCellValue(dailyInfo.Daily.Start_DOD);
sheetDaily.GetRow(5).GetCell(9).SetCellValue(dailyInfo.Daily.Start_Stayover);
sheetDaily.GetRow(6).GetCell(12).SetCellValue(dailyInfo.Daily.Start_NSS);
sheetDaily.GetRow(7).GetCell(12).SetCellValue(dailyInfo.Daily.Start_MSS);
sheetDaily.GetRow(8).GetCell(12).SetCellValue(dailyInfo.Daily.Start_OCI_OptOut);
sheetDaily.GetRow(9).GetCell(9).SetCellValue(dailyInfo.Daily.StartOfTheDayTotal);
sheetDaily.GetRow(11).GetCell(9).SetCellValue(dailyInfo.Daily.Start_Discrepancies);
sheetDaily.GetRow(14).GetCell(9).SetCellValue(dailyInfo.Daily.End_CheckOut);
sheetDaily.GetRow(15).GetCell(9).SetCellValue(dailyInfo.Daily.End_Stayover);
sheetDaily.GetRow(16).GetCell(9).SetCellValue(dailyInfo.Daily.End_MandatoryClean);
sheetDaily.GetRow(18).GetCell(9).SetCellValue(dailyInfo.Daily.EndOfTheDayTotal);
sheetDaily.GetRow(21).GetCell(9).SetCellValue(dailyInfo.Daily.ExtraRoomsCount);
sheetDaily.GetRow(39).GetCell(9).SetCellValue(dailyInfo.Daily.ChangesStayoverProgramCount);
var copyRow = 2;
var startRow = 3;
HSSFSheet sheetDetails = (HSSFSheet)hssfworkbook.GetSheetAt(1);
var DetailsList = dailyInfo.RoomList.Where(x => x.TaskStatus == "S").OrderBy(x => x.NodeName).ToList();
DetailsList.ForEach(d =>
{
var EndOfDay = dailyInfo.RoomList.Where(x => x.CreateTime == d.CreateTime && x.NodeName == d.NodeName && x.TaskStatus == "E").FirstOrDefault();
sheetDetails.CopyRow(copyRow, startRow);
HSSFRow hRow = (HSSFRow)sheetDetails.GetRow(startRow);
hRow.GetCell(0).SetCellValue(d.CreateTime.ToString("dd/MM/yyyy"));
hRow.GetCell(1).SetCellValue(d.NodeName);
hRow.GetCell(2).SetCellValue(d.RoomStatus);
hRow.GetCell(3).SetCellValue(d.ReservStatus);
hRow.GetCell(4).SetCellValue(d.IsOptIn == 1 ? "Yes" : "No");
hRow.GetCell(5).SetCellValue(d.IsGreenProgram == 1 ? "Yes" : "No");
if (EndOfDay != null)
{
hRow.GetCell(6).SetCellValue(EndOfDay.RoomStatus);
hRow.GetCell(7).SetCellValue(EndOfDay.ReservStatus);
hRow.GetCell(8).SetCellValue(EndOfDay.IsOptIn == 1 ? "Yes" : "No");
hRow.GetCell(9).SetCellValue(EndOfDay.IsGreenProgram == 1 ? "Yes" : "No");
}
startRow++;
});
sheetDetails.RemoveRowAt(copyRow);
}
var fileName = SystemConfig.HotelName + " Opt In Daily Report_" + ExcelExtend.DatetTimeFormate(datenow) + ".xls";
MemoryStream ms = new MemoryStream();
hssfworkbook.Write(ms);
return(new EmailExtend.FileAttachment()
{
FileContent = ms.ToArray(),
FileName = fileName
});
//var attachmentsList = new List<EmailExtend.FileAttachment>() {new EmailExtend.FileAttachment
//{
// FileContent = ms.ToArray(),
// FileName = fileName
//} };
//EmailExtend.SendEmail(SendTo, string.Format("Opt In Daily Report {0} ", ExcelExtend.DatetTimeFormate(datenow)), "Test Opt In Daily Report", attachmentsList);
}
}