void XuatBBDSNVVang(ExcelPackage p)
{
#region //Create a sheet
p.Workbook.Worksheets.Add("DSNV Vang");
var ws = p.Workbook.Worksheets[2];
ws.Name = "DSNV Vang"; //Setting Sheet's name
ws.Cells.Style.Font.Size = 10; //Default font size for whole sheet
ws.Cells.Style.Font.Name = "Times News Roman"; //Default Font name for whole sheet
#endregion
var sR = 1;
XL2.VeLogo("bbdiemdanhvang", ws);
XL2.GhiThongTinTongcty(ws, "", 1, 4, 12, 1, 9, 12, 4, 9);
int sttnv = 0, startColws = 1;
#region 1.tieu de bang cham cong tu ngay den ngay 2. bỏ 1 dòng trống
sR = sR + 7; // sR+1 là dòng trống
XL.FormatCells(ws.Cells[sR, 6], "Danh sách nhân viên vắng ngày " + dtpNgay.Value.ToString("dd/MM/yyyy"),
size: 14, hAlign: ExcelHorizontalAlignment.CenterContinuous, bold: true, VeBorder: false);
#endregion
sR++;
sR++;
#region write stt +0
ws.Column(startColws).Width = (int)Z.STT;
XL.FormatCells(ws.Cells[sR, startColws], "STT", bold: true);
startColws++;
#endregion
#region write ho ten +1
ws.Column(startColws).Width = (int)Z.TEN;
XL.FormatCells(ws.Cells[sR, startColws], "Họ tên", bold: true);
startColws++;
#endregion
#region write manv +2
ws.Column(startColws).Width = (int)Z.MANV;
XL.FormatCells(ws.Cells[sR, startColws], "Mã NV", bold: true);
startColws++;
#endregion
#region write trạng thái +3
ws.Column(startColws).Width = (int)Z.TRANGTHAI;
XL.FormatCells(ws.Cells[sR, startColws], "Trạng thái", bold: true);
startColws++;
#endregion
#region write ca +4
ws.Column(startColws).Width = (int)Z.CA;
XL.FormatCells(ws.Cells[sR, startColws], "Ca", bold: true);
startColws++;
#endregion
//startCol = 4
sR++;
var pb_c2 = (from item in m_DSNV
select new { item.PBCap2.ID, item.PBCap2.TenPhongBan }).Distinct().ToList();
try {
foreach (var pb_i in pb_c2)
{
#region //write ten phong ban, tang 1 dong
XL.FormatCells(ws.Cells[sR, 1], pb_i.TenPhongBan, bold: true, VeBorder: false, hAlign: ExcelHorizontalAlignment.Left);
#endregion
sR++; //chỉ số dòng mới, dòn
var pb_c1 = (from item in m_DSNV
where item.PBCap2.ID == pb_i.ID
select new { item.PBCap1.ID, item.PBCap1.TenPhongBan }).Distinct().ToList();
foreach (var pb in pb_c1)
{
#region //write ten pb cap 1 tang 1 dong
XL.FormatCells(ws.Cells[sR, 1], pb.TenPhongBan, bold: true, VeBorder: false, hAlign: ExcelHorizontalAlignment.Left);
sR++;
#endregion
var dsnv = (from c in m_DSNV
where c.PBCap1.ID == pb.ID
select c).ToList();
foreach (var nhanvien in dsnv)
{
// duyet tung nv, nhớ tăng sR
startColws = 1; //reset startcol =1 mỗi lần ghi nv mới
var ngayCong = nhanvien.DSNgayCong[2];
if (ngayCong.DSVaoRa.Count > 0)
{
continue;
}
#region stt,hoten,macc
sttnv++;
XL.FormatCells(ws.Cells[sR, startColws + 0], sttnv);
XL.FormatCells(ws.Cells[sR, startColws + 1], nhanvien.TenNV, hAlign: ExcelHorizontalAlignment.Left); //Ten
XL.FormatCells(ws.Cells[sR, startColws + 2], nhanvien.MaNV, hAlign: ExcelHorizontalAlignment.Left); //MaNV
startColws = startColws + 3; // + 4 là 4 cột, nếu thêm 1 cột thì +5, +6
#endregion
var xCol = startColws;
#region trạng thái
XL.FormatCells(ws.Cells[sR, xCol], "Vắng");
xCol++;
#endregion
#region ca
XL.FormatCells(ws.Cells[sR, xCol], ngayCong.Absents_Code()); //
xCol++;
#endregion
sR++;
}
}
}
sR += 2;
} catch (Exception exception) {
throw exception;
}
}
void XuatBBDSNVDLV(ExcelPackage p)
{
#region //Create a sheet
p.Workbook.Worksheets.Add("DSNV Dang Lam Viec");
var ws = p.Workbook.Worksheets[1];
ws.Name = "DSNV Dang Lam Viec"; //Setting Sheet's name
ws.Cells.Style.Font.Size = 10; //Default font size for whole sheet
ws.Cells.Style.Font.Name = "Times News Roman"; //Default Font name for whole sheet
#endregion
var sR = 1;
XL2.VeLogo("bbdiemdanhdlv", ws);
XL2.GhiThongTinTongcty(ws, "", 1, 4, 12, 1, 9, 12, 4, 9);
int sttnv = 0, startColws = 1;
#region 1.tieu de bang cham cong tu ngay den ngay 2. bỏ 1 dòng trống
sR = sR + 7; // sR+1 là dòng trống
XL.FormatCells(ws.Cells[sR, 6], "Danh sách nhân viên đang làm việc ngày " + dtpNgay.Value.ToString("dd/MM/yyyy"),
size: 14, hAlign: ExcelHorizontalAlignment.CenterContinuous, bold: true, VeBorder: false);
#endregion
sR++;
sR++;
#region write stt +0
ws.Column(startColws).Width = (int)Z.STT;
XL.FormatCells(ws.Cells[sR, startColws], "STT", bold: true);
startColws++;
#endregion
#region write ho ten +1
ws.Column(startColws).Width = (int)Z.TEN;
XL.FormatCells(ws.Cells[sR, startColws], "Họ tên", bold: true);
startColws++;
#endregion
#region write manv +2
ws.Column(startColws).Width = (int)Z.MANV;
XL.FormatCells(ws.Cells[sR, startColws], "Mã NV", bold: true);
startColws++;
#endregion
#region write trạng thái +3
ws.Column(startColws).Width = (int)Z.TRANGTHAI;
XL.FormatCells(ws.Cells[sR, startColws], "Trạng thái", bold: true);
startColws++;
#endregion
#region write ca +4
ws.Column(startColws).Width = (int)Z.CA;
XL.FormatCells(ws.Cells[sR, startColws], "Ca", bold: true);
startColws++;
#endregion
#region write vào 1 +5
ws.Column(startColws).Width = (int)Z.VAO1;
XL.FormatCells(ws.Cells[sR, startColws], "Vào 1", bold: true);
ws.Cells[sR, startColws].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
startColws++;
#endregion
#region write raa 1 +6
ws.Column(startColws).Width = (int)Z.RAA1;
XL.FormatCells(ws.Cells[sR, startColws], "Ra 1", bold: true);
ws.Cells[sR, startColws].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
startColws++;
#endregion
#region write vào 2 +7
ws.Column(startColws).Width = (int)Z.VAO2;
XL.FormatCells(ws.Cells[sR, startColws], "Vào 2", bold: true);
ws.Cells[sR, startColws].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
startColws++;
#endregion
#region write raa 2 +8
ws.Column(startColws).Width = (int)Z.RAA2;
XL.FormatCells(ws.Cells[sR, startColws], "Ra 2", bold: true);
ws.Cells[sR, startColws].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
startColws++;
#endregion
#region write vào 3 +9
ws.Column(startColws).Width = (int)Z.VAO3;
XL.FormatCells(ws.Cells[sR, startColws], "Vào 3", bold: true);
ws.Cells[sR, startColws].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
startColws++;
#endregion
#region write raa 3 +10
ws.Column(startColws).Width = (int)Z.RAA3;
XL.FormatCells(ws.Cells[sR, startColws], "Ra 3", bold: true);
ws.Cells[sR, startColws].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
startColws++;
#endregion
sR++;
var pb_c2 = (from item in m_DSNV
select new { item.PBCap2.ID, item.PBCap2.TenPhongBan }).Distinct().ToList();
try {
foreach (var pb_i in pb_c2)
{
#region //write ten phong ban, tang 1 dong
XL.FormatCells(ws.Cells[sR, 1], pb_i.TenPhongBan, bold: true, VeBorder: false, hAlign: ExcelHorizontalAlignment.Left);
#endregion
sR++; //chỉ số dòng mới, dòn
var pb_c1 = (from item in m_DSNV
where item.PBCap2.ID == pb_i.ID
select new { item.PBCap1.ID, item.PBCap1.TenPhongBan }).Distinct().ToList();
foreach (var pb in pb_c1)
{
#region //write ten pb cap 1 tang 1 dong
XL.FormatCells(ws.Cells[sR, 1], pb.TenPhongBan, bold: true, VeBorder: false, hAlign: ExcelHorizontalAlignment.Left);
sR++;
#endregion
var dsnv = (from c in m_DSNV
where c.PBCap1.ID == pb.ID
select c).ToList();
foreach (var nhanvien in dsnv)
{
// duyet tung nv, nhớ tăng sR
startColws = 1; //reset startcol =1 mỗi lần ghi nv mới
var ngayCong = nhanvien.DSNgayCong[2];
if (ngayCong.DSVaoRa.Count == 0 || ngayCong.DSVaoRa[ngayCong.DSVaoRa.Count - 1].Raa != null)
{
continue;
}
#region stt,hoten,macc
sttnv++;
XL.FormatCells(ws.Cells[sR, startColws + 0], sttnv);
XL.FormatCells(ws.Cells[sR, startColws + 1], nhanvien.TenNV, hAlign: ExcelHorizontalAlignment.Left); //Ten
XL.FormatCells(ws.Cells[sR, startColws + 2], nhanvien.MaNV, hAlign: ExcelHorizontalAlignment.Left); //MaNV
startColws = startColws + 3; // + 4 là 4 cột, nếu thêm 1 cột thì +5, +6
#endregion
var xCol = startColws;
#region trạng thái
XL.FormatCells(ws.Cells[sR, xCol], "Đang làm việc");
xCol++;
#endregion
#region ca
XL.FormatCells(ws.Cells[sR, xCol], ngayCong.CIOs_Absents_Code_Comp()); //
xCol++;
#endregion
#region vào ra 1-->3
for (int x = 0; x < ngayCong.DSVaoRa.Count || x < 3; x++)
{
if (x < 3 && x >= ngayCong.DSVaoRa.Count)
{
XL.FormatCells(ws.Cells[sR, xCol], null);
xCol++;
XL.FormatCells(ws.Cells[sR, xCol], null);
xCol++;
}
else
{
XL.FormatCells(ws.Cells[sR, xCol], ngayCong.DSVaoRa[x].Vao != null ? ngayCong.DSVaoRa[x].Vao.Time : (object)null, numberFormat: "H:mm d/M");
xCol++;
XL.FormatCells(ws.Cells[sR, xCol], ngayCong.DSVaoRa[x].Raa != null ? ngayCong.DSVaoRa[x].Raa.Time : (object)null, numberFormat: "H:mm d/M");
xCol++;
}
}
#endregion
sR++;
}
}
}
sR += 2;
} catch (Exception exception) {
throw exception;
}
}