public static void ExportDataWithSaveDialog(ref UltraGrid ultraGrid1, string strFileName)
{
try
{
if (ultraGrid1.Rows.Count == 0)
{
return;
}
if (strFileName.Length == 0)
{
strFileName = "未命名";
}
SaveFileDialog dlg = new SaveFileDialog();
dlg.Title = "保存";
dlg.OverwritePrompt = true;
dlg.Filter = "Excel文件(*.xls)|*.xls";
dlg.AddExtension = true;
dlg.FileName = strFileName;
string strCaption = strFileName;
if (dlg.ShowDialog() == DialogResult.OK)
{
strFileName = dlg.FileName;
Workbook wb = new Workbook();
Worksheet ws2 = wb.Worksheets.Add("Sheet1");
WorksheetMergedCellsRegion region = null;
ws2.Rows[0].Cells[0].Value = strCaption;
ws2.Rows[0].Cells[0].CellFormat.Alignment = Infragistics.Excel.HorizontalCellAlignment.Center;
ws2.Rows[0].Cells[0].CellFormat.VerticalAlignment = Infragistics.Excel.VerticalCellAlignment.Center;
ws2.Rows[0].Cells[0].CellFormat.WrapText = Infragistics.Excel.ExcelDefaultableBoolean.True;
ws2.Rows[0].Cells[0].CellFormat.Font.Name = "宋体";
ws2.Rows[0].Cells[0].CellFormat.Font.Height = 350;
ws2.Rows[0].Cells[0].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
ws2.Rows[0].Height = 880;
int iMaxY = 0;
int iMaxHeight = 0;
int iMaxWidth = 0;
for (int i = 0; i < ultraGrid1.DisplayLayout.Bands[0].Columns.Count; i++)
{
if (!ultraGrid1.DisplayLayout.Bands[0].Columns[i].Hidden && ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.LabelPosition != LabelPosition.None)
{
try
{
ws2.Rows[1 + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginY].Cells[ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginX].Value = ultraGrid1.DisplayLayout.Bands[0].Columns[i].Header.Caption;
ws2.Rows[1 + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginY].Cells[ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginX].CellFormat.Alignment = Infragistics.Excel.HorizontalCellAlignment.Center;
ws2.Rows[1 + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginY].Cells[ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginX].CellFormat.VerticalAlignment = Infragistics.Excel.VerticalCellAlignment.Center;
ws2.Rows[1 + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginY].Cells[ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginX].CellFormat.WrapText = Infragistics.Excel.ExcelDefaultableBoolean.True;
ws2.Rows[1 + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginY].Cells[ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginX].CellFormat.Font.Bold = Infragistics.Excel.ExcelDefaultableBoolean.True;
ws2.Rows[1 + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginY].Cells[ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginX].CellFormat.WrapText = Infragistics.Excel.ExcelDefaultableBoolean.True;
ws2.Rows[1 + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginY].Cells[ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginX].CellFormat.Font.Name = "宋体";
region = ws2.MergedCellsRegions.Add(1 + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginY,
ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginX,
1 + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginY +
ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.SpanY - 1,
ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginX +
ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.SpanX - 1);
if (ws2.Rows[1 + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginY].Height != 500)
{
ws2.Rows[1 + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginY].Height = 500;
}
if (ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.SpanY > iMaxHeight)
{
iMaxHeight = ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.SpanY;
}
if (ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginY > iMaxY)
{
iMaxY = ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginY;
iMaxHeight = iMaxY + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.SpanY;
}
if (ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.SpanX + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginX > iMaxWidth)
{
iMaxWidth = ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.SpanX + ultraGrid1.DisplayLayout.Bands[0].Columns[i].RowLayoutColumnInfo.OriginX;
}
}
catch (Exception ex) { string str = ex.Message; }
}
}
region = ws2.MergedCellsRegions.Add(0, 0, 0, iMaxWidth - 1);
decimal dValue = 0.0M;
bool bDecimal = false;
for (int i = 0; i < ultraGrid1.Rows.Count; i++)
{
for (int j = 0; j < ultraGrid1.DisplayLayout.Bands[0].Columns.Count; j++)
{
try
{
if (!ultraGrid1.DisplayLayout.Bands[0].Columns[j].Hidden && ultraGrid1.DisplayLayout.Bands[0].Columns[j].RowLayoutColumnInfo.LabelPosition != LabelPosition.LabelOnly)
{
if (decimal.TryParse(Convert.ToString(ultraGrid1.Rows[i].Cells[j].Text), out dValue))
{
if (dValue == 0 || dValue < 1 || !Convert.ToString(ultraGrid1.Rows[i].Cells[j].Text).StartsWith("0"))
{
bDecimal = true;
}
else
{
bDecimal = false;
}
}
else
{
bDecimal = false;
}
if (bDecimal)
{
ws2.Rows[1 + iMaxHeight + i].Cells[ultraGrid1.DisplayLayout.Bands[0].Columns[j].RowLayoutColumnInfo.OriginX].Value = dValue;
}
else
{
ws2.Rows[1 + iMaxHeight + i].Cells[ultraGrid1.DisplayLayout.Bands[0].Columns[j].RowLayoutColumnInfo.OriginX].Value = ultraGrid1.Rows[i].Cells[j].Text;
}
}
}
catch { }
}
}
BIFF8Writer.WriteWorkbookToFile(wb, strFileName);
if (File.Exists(strFileName) &&
MessageBox.Show("数据导出成功！\r\n需要打开所导出文件吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
ProcessStartInfo p = new ProcessStartInfo(strFileName);
p.WorkingDirectory = Path.GetDirectoryName(strFileName);
Process.Start(p);
}
}
}
catch { }
}
public static void WriteToDisk(Workbook theWorkBook, string theFile)
{
BIFF8Writer.WriteWorkbookToFile(theWorkBook, theFile);
}
public static void XuatExcelTKTruyCapBaiViet(string tungay, string denngay, List <Article> DisplayArticleList, HttpContext context, bool response)
{
DateTime now = DateTime.Now;
string nameExcel = "Thống kê số lượng truy cập bài viết";
string str = string.Format("_{0}_{1}_{2}_{3}_{4}_{5}", now.Day, now.Month, now.Year, now.Hour, now.Minute, now.Millisecond);
str = "TKTruyCapBaiViet" + str + ".xls";
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets.Add(nameWorksheet);
workbook.ActiveWorksheet = workbook.Worksheets[nameWorksheet];
int colums = 4;//
int rowStart = 1;
sheet.Rows[rowStart].Cells[1].Value = "THỐNG KÊ SỐ LƯỢNG TRUY CẬP BÀI VIẾT";//
sheet.MergedCellsRegions.Add(rowStart, 0, rowStart, colums - 1);
sheet.Rows[rowStart].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowStart].Cells[1].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
sheet.Rows[rowStart].Cells[1].CellFormat.Font.Height = 12 * 20;
sheet.Rows[rowStart].Height = 17 * 20;
if (tungay != "" && denngay != "")
{
DateTime fromDate = DateTime.Parse(tungay, new System.Globalization.DateTimeFormatInfo {
ShortDatePattern = "dd/MM/yyyy"
});
DateTime toDate = DateTime.Parse(denngay, new System.Globalization.DateTimeFormatInfo {
ShortDatePattern = "dd/MM/yyyy"
});
sheet.Rows[rowStart + 1].Cells[1].Value = "(Từ ngày: " + string.Format("{0: dd/MM/yyyy}", fromDate) + " đến ngày: " + string.Format("{0:dd/MM/yyyy}", toDate) + ")";
}
sheet.MergedCellsRegions.Add(rowStart + 1, 1, rowStart + 1, colums - 2);
sheet.Rows[rowStart + 1].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowStart + 1].Cells[1].CellFormat.Font.Bold = ExcelDefaultableBoolean.False;
sheet.Rows[rowStart + 1].Cells[1].CellFormat.Font.Height = 12 * 20;
sheet.Rows[rowStart + 1].Height = 17 * 20;
//định dạng tiêu đề table
//0 stt |1 Tiêu đề|2 Ngày đăng |3 Lượng truy cập
sheet.Columns[0].Width = 5 * 256;
sheet.Columns[1].Width = 100 * 256;
sheet.Columns[2].Width = 12 * 256;
sheet.Columns[3].Width = 10 * 256;
sheet.Rows[rowStart + 2].Cells[0].Value = "STT";
sheet.Rows[rowStart + 2].Cells[1].Value = "Tiêu đề";
sheet.Rows[rowStart + 2].Cells[2].Value = "Ngày đăng";
sheet.Rows[rowStart + 2].Cells[3].Value = "Lượng truy cập";
for (int i = 0; i < colums; i++)
{
SetCellFormatHeader(sheet, rowStart + 2, i);
}
int indexRow = rowStart + 2;
int stt = 0;
foreach (Article item in DisplayArticleList)
{
indexRow++;
stt++;
sheet.Rows[indexRow].Cells[0].Value = stt;
sheet.Rows[indexRow].Cells[1].Value = item.Title;
sheet.Rows[indexRow].Cells[2].Value = string.Format("{0: dd/MM/yyyy}", item.CreatedDate);
sheet.Rows[indexRow].Cells[3].Value = item.ViewTime;
for (int i = 0; i < colums; i++)
{
SetCellFormat(sheet, indexRow, i);
}
}
string filename = System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["TempExcel"]);
// Tạo folder
if (Directory.Exists(filename) == false)
{
Directory.CreateDirectory(filename);
}
filename = filename + "/" + str;
BIFF8Writer.WriteWorkbookToFile(workbook, filename);
if (response)
{
BinaryReader reader = new BinaryReader(new FileStream(filename, FileMode.Open));
context.Response.Clear();
context.Response.AddHeader("content-disposition", "attachment; filename=" + str);
context.Response.BinaryWrite(reader.ReadBytes((int)(new FileInfo(filename)).Length));
reader.Close();
context.Response.Flush();
}
}
public void BangChamCongChiTietExportToExcelProcess(int thang, int nam, Guid?bophanId, Guid?idNhanVien, HttpContext context)
{
//if (bophanId == Guid.Empty && idNhanVien != Guid.Empty)
//{
// ApplicationUser applicationUser = AuthenticationHelper.GetUserById(new Guid(HttpContext.Current.User.Identity.GetUserId()), HttpContext.Current.User.Identity.Name);
// bophanId = Guid.Parse(applicationUser.DepartmentId);
//}
List <DTO_QuanLyChamCong_GioVaoRa> result = new List <DTO_QuanLyChamCong_GioVaoRa>();
CC_ChamCongTheoNgay_Factory factory = new CC_ChamCongTheoNgay_Factory();
//if (webGroupId.ToUpper() == "53D57298-1933-4E4B-B4C8-98AFED036E21")
// result = factory.QuanLyChamCong_ThongTinChamCongThang_Cua1NhanVien(thang, nam, idNhanVien);
//else
// result = factory.QuanLyChamCong_ThongTinChamCongThang(thang, nam, bophanId, maNhanSu, idLoaiNhanSu);
if (bophanId == Guid.Empty)
{
bophanId = null;
}
if (idNhanVien == Guid.Empty)
{
idNhanVien = null;
}
List <spd_WebChamCong_InBangChiTietChamCong_Result> result1 = factory.Context.spd_WebChamCong_InBangChiTietChamCong(thang, nam, bophanId, idNhanVien).ToList();
List <DTO_NgayChamCong> listNgay = factory.GetList_NgayTrongKyChamCong(thang, nam).ToList();
foreach (spd_WebChamCong_InBangChiTietChamCong_Result r in result1)
{
int index = result.FindIndex(item => item.NhanVienID == r.IDNhanVien);
if (index < 0)
{
DTO_QuanLyChamCong_GioVaoRa a = new DTO_QuanLyChamCong_GioVaoRa();
a.NhanVienID = r.IDNhanVien;
a.MaNhanSu = r.IDNhanSu_ChamCong;
a.HoTen = r.HoTen;
a.BoPhan = r.TenBoPhan;
a.ChiTietVaoRa = new List <DTO_QuanLyChamCong_GioVaoRaTungCa>();
DTO_QuanLyChamCong_GioVaoRaTungCa b = new DTO_QuanLyChamCong_GioVaoRaTungCa();
b.Ngay = r.Ngay;
b.GioQuet = r.GioQuet;
b.SoLanQuet = r.SoLanQuet;
a.ChiTietVaoRa.Add(b);
result.Add(a);
}
else
{
foreach (DTO_QuanLyChamCong_GioVaoRa aa in result)
{
if (aa.NhanVienID == r.IDNhanVien)
{
DTO_QuanLyChamCong_GioVaoRaTungCa b = new DTO_QuanLyChamCong_GioVaoRaTungCa();
b.Ngay = r.Ngay;
b.GioQuet = r.GioQuet;
b.SoLanQuet = r.SoLanQuet;
aa.ChiTietVaoRa.Add(b);
}
}
}
}
BoPhan_Factory bpfac = new BoPhan_Factory();
HoSo_Factory hsfac = new HoSo_Factory();
BoPhan DonVi = bpfac.GetByID(bophanId.HasValue ? bophanId.Value : Guid.Empty);
DateTime now = DateTime.Now;
string str = string.Format("{0}_{1}_{2}_{3}_{4}_{5}", now.Day, now.Month, now.Year, now.Hour, now.Minute, now.Millisecond);
str = str + ".xls";
Workbook workbook = new Workbook();
workbook.Styles.NormalStyle.StyleFormat.Font.Name = "Times New Roman";
//Size 11
workbook.Styles.NormalStyle.StyleFormat.Font.Height = 220;
Worksheet sheet = workbook.Worksheets.Add("ChiTiet");
workbook.WindowOptions.SelectedWorksheet = workbook.Worksheets["ChiTiet"];
sheet.PrintOptions.PaperSize = PaperSize.A3;
sheet.Columns[0].Width = 4000;
sheet.Columns[1].Width = 4000;
//làm cho đẹp
sheet.Columns[5].Width = 3000;
sheet.Columns[6].Width = 3000;
//Margin 1 cm
sheet.PrintOptions.LeftMargin = 0.385;
sheet.PrintOptions.RightMargin = 0.385;
sheet.PrintOptions.BottomMargin = 0.77;
sheet.PrintOptions.TopMargin = 0.77;
sheet.PrintOptions.HeaderMargin = 0;
sheet.PrintOptions.FooterMargin = 0;
sheet.PrintOptions.Orientation = Orientation.Landscape;
//sheet.Columns[0].Width =1500;
//sheet.Columns[0].CellFormat.Alignment = HorizontalCellAlignment.Center;
//sheet.Columns[1].Width = 7000;
//for (int i=2;i<= songay + 1;i++)
//{
// sheet.Columns[i].Width = 900;
// sheet.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
//}
//for (int i = songay+2; i <= songay + 6; i++)
//{
// sheet.Columns[i].Width = 2800;
// sheet.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
//}
int rowIndex = 0;
//WorksheetMergedCellsRegion merged2 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 11);
//merged2.Value = "TRƯỜNG ĐẠI HỌC NGÂN HÀNG";
//merged2.CellFormat.Alignment = HorizontalCellAlignment.Center;
//merged2.CellFormat.Font.Name = "Times New Roman";
//merged2.CellFormat.Font.Height = 240;
//merged2.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
//rowIndex++;
//WorksheetMergedCellsRegion merged4 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 11);
//merged4.Value = "THÀNH PHỐ HỒ CHÍ MINH";
//merged4.CellFormat.Alignment = HorizontalCellAlignment.Center;
//merged4.CellFormat.Font.Name = "Times New Roman";
//merged4.CellFormat.Font.Height = 240;
//merged4.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
//rowIndex++;
WorksheetMergedCellsRegion merged = sheet.MergedCellsRegions.Add(0, 0, 1, 6);
merged.Value = "BẢNG CHẤM CÔNG THÁNG " + thang + "/" + nam;
merged.CellFormat.Alignment = HorizontalCellAlignment.Center;
merged.CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
merged.CellFormat.Font.Name = "Times New Roman";
merged.CellFormat.Font.Height = 400;
merged.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
merged.CellFormat.WrapText = ExcelDefaultableBoolean.True;
rowIndex++;
rowIndex++;
#region TargetGroup
foreach (DTO_QuanLyChamCong_GioVaoRa ccThang in result)
{
//WorksheetMergedCellsRegion mergedHoTen = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 2);
//mergedHoTen.Value = "Mã nhân viên: " + ccThang.MaNhanSu + " Tên nhân viên: " + ccThang.HoTen + " Bộ phận: " + ccThang.BoPhan;
//SetRegionBorder(mergedHoTen, false, false, true, true);
sheet.Rows[rowIndex].Cells[0].Value = "Mã nhân viên: " + ccThang.MaNhanSu + " Tên nhân viên: " + ccThang.HoTen + " Bộ phận: " + ccThang.BoPhan;
sheet.Rows[rowIndex].Cells[0].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
rowIndex++;
WorksheetMergedCellsRegion mergedNgay = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex + 1, 0);
mergedNgay.Value = "Ngày";
SetRegionBorder(mergedNgay, true, false, true, true);
WorksheetMergedCellsRegion mergedThu = sheet.MergedCellsRegions.Add(rowIndex, 1, rowIndex + 1, 1);
mergedThu.Value = "Thứ";
SetRegionBorder(mergedThu, true, false, true, true);
int maxTimes = 1;
foreach (DTO_QuanLyChamCong_GioVaoRaTungCa cc in ccThang.ChiTietVaoRa)
{
int times = cc.SoLanQuet.HasValue ? cc.SoLanQuet.Value : 1;
if (times > maxTimes)
{
maxTimes = times;
}
}
for (int i = 1; i <= maxTimes; i++)
{
WorksheetMergedCellsRegion mergedGioQuet = sheet.MergedCellsRegions.Add(rowIndex, 1 + i, rowIndex + 1, 1 + i);
mergedGioQuet.Value = "Lần " + i;
SetRegionBorder(mergedGioQuet, true, false, true, true);
sheet.Columns[i + 1].Width = 3000;
}
//WorksheetMergedCellsRegion merged1 = sheet.MergedCellsRegions.Add(rowIndex, 2, rowIndex, 3);
//merged1.Value = "1";
//SetRegionBorder(merged1, true, false, true, true);
//WorksheetMergedCellsRegion merged2 = sheet.MergedCellsRegions.Add(rowIndex, 4, rowIndex, 5);
//merged2.Value = "2";
//SetRegionBorder(merged2, true, false, true, true);
//WorksheetMergedCellsRegion merged3 = sheet.MergedCellsRegions.Add(rowIndex, 6, rowIndex, 7);
//merged3.Value = "3";
//SetRegionBorder(merged3, true, false, true, true);
SetCellFormat(sheet, rowIndex, 0, maxTimes + 1, true, false, false, true, true);
//rowIndex++;
//for (int i = 2; i < 8; i++)
//{
// if (i % 2 == 0)
// sheet.Rows[rowIndex].Cells[i].Value = "Vào";
// else sheet.Rows[rowIndex].Cells[i].Value = "Ra";
// CellBorder(sheet, rowIndex, i);
//}
//SetCellFormat(sheet, rowIndex, 2, 7, true, false, true, true, true);
rowIndex += 2;
foreach (DTO_QuanLyChamCong_GioVaoRaTungCa cc in ccThang.ChiTietVaoRa)
{
sheet.Rows[rowIndex].Cells[0].Value = cc.Ngay;
foreach (DTO_NgayChamCong date in listNgay)
{
if (date.Ngay == (cc.Ngay.HasValue ? cc.Ngay.Value.Day : 0))
{
sheet.Rows[rowIndex].Cells[1].Value = date.Thu;
}
}
List <string> gioQuetList = XuLyChuoi(cc.GioQuet);
int columnIndex = 2;
if (gioQuetList != null)
{
foreach (string gioQuet in gioQuetList)
{
sheet.Rows[rowIndex].Cells[columnIndex].Value = gioQuet;
columnIndex++;
}
}
SetCellFormat(sheet, rowIndex, 0, maxTimes + 1, false, true, false, true, true);
rowIndex++;
}
rowIndex++;
sheet.Rows[rowIndex].Cells[1].Value = "Ký tên";
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Center;
rowIndex += 3;
//HoSo leader = hsfac.GetDepartmentLeaderByStaffID(ccThang.NhanVienID.HasValue ? ccThang.NhanVienID.Value : Guid.Empty);
//if (leader != null)
//{
// sheet.Rows[rowIndex].Cells[2].Value = leader.HoTen;
// sheet.Rows[rowIndex].Cells[2].CellFormat.Alignment = HorizontalCellAlignment.Center;
// sheet.Rows[rowIndex].Cells[2].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
//}
sheet.Rows[rowIndex].Cells[1].Value = ccThang.HoTen;
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[1].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
rowIndex += 3;
}
rowIndex++;
rowIndex++;
#endregion
string filename = "/Temp/Excel/1.xls";
BIFF8Writer.WriteWorkbookToFile(workbook, context.Server.MapPath(filename));
BinaryReader reader = new BinaryReader(new FileStream(context.Server.MapPath(filename), FileMode.Open));
context.Response.Clear();
context.Response.AddHeader("content-disposition", "attachment; filename=" + str);
context.Response.BinaryWrite(reader.ReadBytes((int)(new FileInfo(context.Server.MapPath(filename))).Length));
reader.Close();
context.Response.Flush();
}
public void StaffSyntheticEvaluationToExcelProcess(Guid evaluationId, Guid departmentId, string type, HttpContext context)
{
//type=1: phân loại NV,GV
//type=2: phân loại trưởng đơn vị
List <ABC_EvaluationBoardStaffDTO> detailResult = new List <ABC_EvaluationBoardStaffDTO>();
ABC_EvaluationBoardApiController controller = new ABC_EvaluationBoardApiController();
if (type == "1")
{
detailResult = controller.GetListStaffSyntheticEvaluationExcel(evaluationId, departmentId, false);
}
else if (type == "2")
{
detailResult = controller.GetListDepartmentLeaderSyntheticEvaluationExcel(evaluationId);
}
ABC_EvaluationBoard eb = controller.GetObj(evaluationId);
DepartmentDTO dept = controller.GetDepartment(departmentId);
DateTime now = DateTime.Now;
string checkStatus = context.Request.Params["type"];
string str = "";
if (type == "1")
{
str = string.Format("{0}_{1}_{2}_{3}_{4}_{5}_{6}", dept.Name, now.Day, now.Month, now.Year, now.Hour, now.Minute, now.Millisecond);
}
else if (type == "2")
{
str = string.Format("{0}_{1}_{2}_{3}_{4}_{5}_{6}", "Đánh giá phân loại Trưởng đơn vị", now.Day, now.Month, now.Year, now.Hour, now.Minute, now.Millisecond);
}
str = str + ".xls";
str = RemoveWhitespace(str);
Workbook workbook = new Workbook();
workbook.Styles.NormalStyle.StyleFormat.Font.Name = "Times New Roman";
//Size 11
workbook.Styles.NormalStyle.StyleFormat.Font.Height = 240;
workbook.Styles.NormalStyle.StyleFormat.Font.Bold = ExcelDefaultableBoolean.True;
workbook.Styles.NormalStyle.StyleFormat.Alignment = HorizontalCellAlignment.Center;
workbook.Styles.NormalStyle.StyleFormat.VerticalAlignment = VerticalCellAlignment.Center;
workbook.Styles.NormalStyle.StyleFormat.WrapText = ExcelDefaultableBoolean.True;
Worksheet sheet = workbook.Worksheets.Add("DanhGia");
workbook.WindowOptions.SelectedWorksheet = workbook.Worksheets["DanhGia"];
sheet.PrintOptions.PaperSize = PaperSize.A4;
//Margin 1 cm
sheet.PrintOptions.LeftMargin = 0.57;
sheet.PrintOptions.RightMargin = 0.38;
sheet.PrintOptions.BottomMargin = 0.38;
sheet.PrintOptions.TopMargin = 0.38;
sheet.PrintOptions.HeaderMargin = 0;
sheet.PrintOptions.FooterMargin = 0;
sheet.PrintOptions.Orientation = Orientation.Portrait;
sheet.Columns[0].Width = 1000;
sheet.Columns[1].Width = 6000;
sheet.Columns[2].Width = 4000;
sheet.Columns[3].Width = 3000;
sheet.Columns[4].Width = 2500;
sheet.Columns[5].Width = 2500;
sheet.Columns[6].Width = 3000;
int rowIndex = 0;
WorksheetMergedCellsRegion merged2 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 2);
merged2.Value = "NGÂN HÀNG NHÀ NƯỚC VIỆT NAM";
WorksheetMergedCellsRegion merged3 = sheet.MergedCellsRegions.Add(rowIndex, 3, rowIndex, 6);
merged3.Value = "CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM";
rowIndex++;
WorksheetMergedCellsRegion merged4 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 2);
merged4.Value = "TRƯỜNG ĐẠI HỌC NGÂN HÀNG";
WorksheetMergedCellsRegion merged5 = sheet.MergedCellsRegions.Add(rowIndex, 3, rowIndex, 6);
merged5.Value = "Độc lập - Tự do - Hạnh phúc";
rowIndex++;
WorksheetMergedCellsRegion merged6 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 2);
merged6.Value = "TP. HỒ CHÍ MINH";
WorksheetMergedCellsRegion merged7 = sheet.MergedCellsRegions.Add(rowIndex, 3, rowIndex, 6);
merged7.Value = "-----------------------------------";
rowIndex++;
WorksheetMergedCellsRegion merged8 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 2);
merged8.Value = "------------------------------------------";
rowIndex++;
rowIndex++;
WorksheetMergedCellsRegion merged9 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 6);
if (type == "1")
{
merged9.Value = "PHIẾU TỔNG HỢP ĐÁNH GIÁ VÀ PHÂN LOẠI CÔNG CHỨC, VIÊN CHỨC";
}
else if (type == "2")
{
merged9.Value = "PHIẾU TỔNG HỢP ĐÁNH GIÁ VÀ PHÂN LOẠI CÁC TRƯỞNG ĐƠN VỊ";
}
rowIndex++;
WorksheetMergedCellsRegion merged10 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 6);
if (type == "1")
{
if (eb.EvaluationBoardType == 3)
{
merged10.Value = "VÀ NGƯỜI LAO ĐỘNG THÁNG " + eb.Month.ToString() + "/NĂM " + eb.Year.ToString();
}
else if (eb.EvaluationBoardType == 2)
{
merged10.Value = "VÀ NGƯỜI LAO ĐỘNG 06 THÁNG/NĂM " + eb.Year.ToString();
}
else if (eb.EvaluationBoardType == 1)
{
merged10.Value = "VÀ NGƯỜI LAO ĐỘNG NĂM " + eb.Year.ToString();
}
}
else if (type == "2")
{
if (eb.EvaluationBoardType == 3)
{
merged10.Value = "THÁNG " + eb.Month.ToString() + "/NĂM " + eb.Year.ToString();
}
else if (eb.EvaluationBoardType == 2)
{
merged10.Value = "06 THÁNG/NĂM " + eb.Year.ToString();
}
else if (eb.EvaluationBoardType == 1)
{
merged10.Value = "NĂM " + eb.Year.ToString();
}
}
rowIndex++;
WorksheetMergedCellsRegion merged11 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 6);
if (type == "1")
{
merged11.Value = "Đơn vị: " + dept.Name;
}
rowIndex++;
rowIndex++;
WorksheetMergedCellsRegion merged12 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex + 2, 0);
merged12.Value = "TT";
SetBorder(merged12);
WorksheetMergedCellsRegion merged13 = sheet.MergedCellsRegions.Add(rowIndex, 1, rowIndex + 2, 1);
merged13.Value = "Họ và tên";
SetBorder(merged13);
WorksheetMergedCellsRegion merged14 = sheet.MergedCellsRegions.Add(rowIndex, 2, rowIndex + 2, 2);
if (type == "1")
{
merged14.Value = "Chức vụ/vị trí công việc";
}
else if (type == "2")
{
merged14.Value = "Chức vụ/Đơn vị công tác";
}
SetBorder(merged14);
WorksheetMergedCellsRegion merged15 = sheet.MergedCellsRegions.Add(rowIndex, 3, rowIndex + 2, 3);
if (eb.EvaluationBoardType == 3)
{
merged15.Value = "Số điểm tự xếp loại";
}
else if (eb.EvaluationBoardType == 2)
{
merged15.Value = "Số điểm BQ của 6 tháng";
}
else if (eb.EvaluationBoardType == 1)
{
merged15.Value = "Số điểm BQ của năm";
}
SetBorder(merged15);
WorksheetMergedCellsRegion merged16 = sheet.MergedCellsRegions.Add(rowIndex, 4, rowIndex, 6);
if (type == "1")
{
merged16.Value = "Trưởng đơn vị phân loại";
}
else if (type == "2" || eb.EvaluationBoardType == 2 || eb.EvaluationBoardType == 1)
{
merged16.Value = "Hiệu trưởng phân loại";
}
SetBorder(merged16);
WorksheetMergedCellsRegion merged17 = sheet.MergedCellsRegions.Add(rowIndex + 1, 4, rowIndex + 2, 4);
merged17.Value = "Số điểm";
SetBorder(merged17);
WorksheetMergedCellsRegion merged18 = sheet.MergedCellsRegions.Add(rowIndex + 1, 5, rowIndex + 2, 5);
merged18.Value = "Loại (A.B.C.D)";
SetBorder(merged18);
WorksheetMergedCellsRegion merged19 = sheet.MergedCellsRegions.Add(rowIndex + 1, 6, rowIndex + 2, 6);
merged19.Value = "Ghi chú";
SetBorder(merged19);
rowIndex += 3;
//Lấy STT
int index = 1;
foreach (ABC_EvaluationBoardStaffDTO r in detailResult)
{
sheet.Rows[rowIndex].Cells[0].Value = index;
sheet.Rows[rowIndex].Cells[0].CellFormat.Font.Bold = ExcelDefaultableBoolean.False;
CellBorder(sheet, rowIndex, 0);
sheet.Rows[rowIndex].Cells[1].Value = r.StaffName;
sheet.Rows[rowIndex].Cells[1].CellFormat.Font.Bold = ExcelDefaultableBoolean.False;
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Left;
CellBorder(sheet, rowIndex, 1);
sheet.Rows[rowIndex].Cells[2].Value = r.PositionName;
sheet.Rows[rowIndex].Cells[2].CellFormat.Font.Bold = ExcelDefaultableBoolean.False;
sheet.Rows[rowIndex].Cells[2].CellFormat.Alignment = HorizontalCellAlignment.Left;
CellBorder(sheet, rowIndex, 2);
sheet.Rows[rowIndex].Cells[3].Value = r.StaffRecord;
sheet.Rows[rowIndex].Cells[3].CellFormat.Font.Bold = ExcelDefaultableBoolean.False;
CellBorder(sheet, rowIndex, 3);
sheet.Rows[rowIndex].Cells[4].Value = r.Record;
sheet.Rows[rowIndex].Cells[4].CellFormat.Font.Bold = ExcelDefaultableBoolean.False;
CellBorder(sheet, rowIndex, 4);
//Nếu hội đồng đã sửa thì ưu tiên
if (r.ClassificationThird != null && r.ClassificationThird != "")
{
sheet.Rows[rowIndex].Cells[5].Value = r.ClassificationThird;
}
//Nếu hội đồng chưa sửa thì ưu tiên trưởng đv sửa
else if (r.ClassificationSecond != null && r.ClassificationSecond != "")
{
sheet.Rows[rowIndex].Cells[5].Value = r.ClassificationSecond;
}
else
{
sheet.Rows[rowIndex].Cells[5].Value = r.Classification;
}
sheet.Rows[rowIndex].Cells[5].CellFormat.Font.Bold = ExcelDefaultableBoolean.False;
CellBorder(sheet, rowIndex, 5);
if (r.NoteThird != null && r.NoteThird != "")
{
sheet.Rows[rowIndex].Cells[6].Value = r.NoteThird;
}
else if (r.NoteSecond != null && r.NoteSecond != "")
{
sheet.Rows[rowIndex].Cells[6].Value = r.NoteSecond;
}
else
{
sheet.Rows[rowIndex].Cells[6].Value = "";
}
sheet.Rows[rowIndex].Cells[6].CellFormat.Font.Bold = ExcelDefaultableBoolean.False;
CellBorder(sheet, rowIndex, 6);
rowIndex++;
index++;
}
rowIndex += 2;
WorksheetMergedCellsRegion merged20 = sheet.MergedCellsRegions.Add(rowIndex, 3, rowIndex, 6);
//merged20.Value = "TP. Hồ Chí Minh, ngày "+now.Day +" tháng "+ now.Month +" năm " + now.Year;
merged20.Value = "TP. Hồ Chí Minh, ngày .... tháng .... năm 20...";
merged20.CellFormat.Font.Italic = ExcelDefaultableBoolean.True;
rowIndex++;
if (eb.EvaluationBoardType == 3)
{
WorksheetMergedCellsRegion merged21 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 2);
merged21.Value = "Thường trực Hội đồng TĐKT";
WorksheetMergedCellsRegion merged22 = sheet.MergedCellsRegions.Add(rowIndex, 3, rowIndex, 6);
if (type == "1")
{
merged22.Value = "Trưởng đơn vị";
}
else if (type == "2")
{
merged22.Value = "HIỆU TRƯỞNG";
}
rowIndex++;
WorksheetMergedCellsRegion merged23 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 2);
merged23.Value = "Phòng Tổ chức cán bộ";
WorksheetMergedCellsRegion merged24 = sheet.MergedCellsRegions.Add(rowIndex, 3, rowIndex, 6);
merged24.Value = "";
rowIndex++;
WorksheetMergedCellsRegion merged25 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 2);
if (type == "1")
{
merged25.Value = "Ký nhận, ngày .... tháng .... năm 20...";
}
merged25.CellFormat.Font.Italic = ExcelDefaultableBoolean.True;
merged25.CellFormat.Font.Bold = ExcelDefaultableBoolean.False;
WorksheetMergedCellsRegion merged26 = sheet.MergedCellsRegions.Add(rowIndex, 3, rowIndex, 6);
if (type == "1")
{
merged26.Value = "(Ký và ghi họ và tên)";
}
merged25.CellFormat.Font.Italic = ExcelDefaultableBoolean.True;
merged25.CellFormat.Font.Bold = ExcelDefaultableBoolean.False;
rowIndex++;
}
else if (eb.EvaluationBoardType == 2 || eb.EvaluationBoardType == 1)
{
rowIndex++;
WorksheetMergedCellsRegion merged21 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 1);
merged21.Value = "Trưởng đơn vị";
WorksheetMergedCellsRegion merged22 = sheet.MergedCellsRegions.Add(rowIndex, 2, rowIndex, 4);
merged22.Value = "Trưởng phòng P.TCCB";
WorksheetMergedCellsRegion merged23 = sheet.MergedCellsRegions.Add(rowIndex, 5, rowIndex, 6);
merged23.Value = "Hiệu trưởng";
}
string filename = "/Temp/Excel/1.xls";
rowIndex++;
BIFF8Writer.WriteWorkbookToFile(workbook, context.Server.MapPath(filename));
BinaryReader reader = new BinaryReader(new FileStream(context.Server.MapPath(filename), FileMode.Open));
context.Response.Clear();
context.Response.AddHeader("content-disposition", "attachment; filename=" + str);
context.Response.BinaryWrite(reader.ReadBytes((int)(new FileInfo(context.Server.MapPath(filename))).Length));
reader.Close();
context.Response.Flush();
}
public void BangVanTayVaoRaExportToExcelProcess(int thang, int nam, Guid bophanId, Guid idNhanVien, HttpContext context)
{
//if (bophanId == Guid.Empty && idNhanVien != Guid.Empty)
//{
// ApplicationUser applicationUser = AuthenticationHelper.GetUserById(new Guid(HttpContext.Current.User.Identity.GetUserId()), HttpContext.Current.User.Identity.Name);
// bophanId = Guid.Parse(applicationUser.DepartmentId);
//}
IQueryable <DTO_QuanLyChamCong_ThongTinChamCongThang> result = null;
CC_ChamCongTheoNgay_Factory factory = new CC_ChamCongTheoNgay_Factory();
result = factory.QuanLyChamCong_VanTayVaoRa(thang, nam, bophanId, idNhanVien);
BoPhan_Factory bpfac = new BoPhan_Factory();
BoPhan DonVi = bpfac.GetByID(bophanId);
List <DTO_NgayChamCong> listNgay = factory.GetList_NgayTrongKyChamCong(thang, nam).ToList();
int songay = listNgay.Count();
DateTime now = DateTime.Now;
string checkStatus = context.Request.Params["type"];
string str = string.Format("{0}_{1}_{2}_{3}_{4}_{5}", now.Day, now.Month, now.Year, now.Hour, now.Minute, now.Millisecond);
str = str + ".xls";
Workbook workbook = new Workbook();
workbook.Styles.NormalStyle.StyleFormat.Font.Name = "Times New Roman";
//Size 11
workbook.Styles.NormalStyle.StyleFormat.Font.Height = 220;
Worksheet sheet = workbook.Worksheets.Add("BangChamCong");
workbook.WindowOptions.SelectedWorksheet = workbook.Worksheets["BangChamCong"];
sheet.PrintOptions.PaperSize = PaperSize.A3;
//Margin 1 cm
sheet.PrintOptions.LeftMargin = 0.385;
sheet.PrintOptions.RightMargin = 0.385;
sheet.PrintOptions.BottomMargin = 0.77;
sheet.PrintOptions.TopMargin = 0.77;
sheet.PrintOptions.HeaderMargin = 0;
sheet.PrintOptions.FooterMargin = 0;
sheet.PrintOptions.Orientation = Orientation.Landscape;
sheet.Columns[0].Width = 1500;
sheet.Columns[0].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Columns[1].Width = 1500;
sheet.Columns[1].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Columns[2].Width = 7000;
//ngày trong tháng
for (int i = 3; i <= songay + 2; i++)
{
sheet.Columns[i].Width = 1500;
sheet.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
}
//phần tổng hợp
for (int i = songay + 3; i <= songay + 6; i++)
{
sheet.Columns[i].Width = 1500;
sheet.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
}
int rowIndex = 0;
//WorksheetMergedCellsRegion merged1 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 11);
//merged1.Value = "BỘ GIÁO DỤC VÀ ĐÀO TẠO";
//merged1.CellFormat.Alignment = HorizontalCellAlignment.Center;
//merged1.CellFormat.Font.Name = "Times New Roman";
//merged1.CellFormat.Font.Height = 240;
//merged1.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
//rowIndex++;
WorksheetMergedCellsRegion merged2 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 7);
merged2.Value = "TRƯỜNG ĐẠI HỌC NGÂN HÀNG TP.HCM";
merged2.CellFormat.Alignment = HorizontalCellAlignment.Center;
merged2.CellFormat.Font.Name = "Times New Roman";
merged2.CellFormat.Font.Height = 240;
merged2.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
rowIndex++;
WorksheetMergedCellsRegion merged4 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 7);
//merged4.Value = "Phòng Thanh Tra";
merged4.Value = DonVi.TenBoPhan.ToUpper();
merged4.CellFormat.Alignment = HorizontalCellAlignment.Center;
merged4.CellFormat.Font.Name = "Times New Roman";
merged4.CellFormat.Font.Height = 240;
merged4.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
rowIndex++;
WorksheetMergedCellsRegion merged3 = sheet.MergedCellsRegions.Add(0, 8, 2, songay + 6);
merged3.Value = "BÁO CÁO TÌNH HÌNH THỰC HIỆN CHẤM VÂN TAY VÀO/RA NƠI LÀM VIỆC";
//Khoảng trắng để wrap text
merged3.Value += " THÁNG " + thang + "/" + nam;
//merged3.Value = "BẢNG CHẤM CÔNG THÁNG " + thang + "/" + nam;
merged3.CellFormat.Alignment = HorizontalCellAlignment.Center;
merged3.CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
merged3.CellFormat.Font.Name = "Times New Roman";
merged3.CellFormat.Font.Height = 300;
merged3.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
merged3.CellFormat.WrapText = ExcelDefaultableBoolean.True;
rowIndex++;
//WorksheetMergedCellsRegion merged5 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, songay+6);
//merged5.Value = "ĐƠN VỊ: " +DonVi.TenBoPhan.ToUpper();
//merged5.CellFormat.Alignment = HorizontalCellAlignment.Center;
//merged5.CellFormat.Font.Name = "Times New Roman";
//merged5.CellFormat.Font.Height = 260;
//merged5.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
//rowIndex++;
rowIndex++;
#region TargetGroup
//BackgroundColor(sheet, rowIndex, 0, songay + 6, Color.LightGray);
//BackgroundColor(sheet, rowIndex + 2, 0, songay + 6, Color.LightGray);
WorksheetMergedCellsRegion mergedStt = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex + 2, 0);
mergedStt.Value = "STT";
SetRegionBorder(mergedStt, true, false, true, true);
mergedStt.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion mergedMaSo = sheet.MergedCellsRegions.Add(rowIndex, 1, rowIndex + 2, 1);
mergedMaSo.Value = "Mã số";
SetRegionBorder(mergedMaSo, true, false, true, true);
mergedMaSo.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion mergedHoTen = sheet.MergedCellsRegions.Add(rowIndex, 2, rowIndex + 2, 2);
mergedHoTen.Value = "Họ tên";
SetRegionBorder(mergedHoTen, true, false, true, true);
mergedHoTen.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion mergedNgayTrongThang = sheet.MergedCellsRegions.Add(rowIndex, 3, rowIndex + 1, songay + 2);
mergedNgayTrongThang.Value = "CHI TIẾT";
SetRegionBorder(mergedNgayTrongThang, true, false, true, true);
mergedNgayTrongThang.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion mergedTongHop = sheet.MergedCellsRegions.Add(rowIndex, songay + 3, rowIndex + 1, songay + 6);
mergedTongHop.Value = "TỔNG HỢP";
SetRegionBorder(mergedTongHop, true, false, true, true);
mergedTongHop.CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowIndex + 2].Cells[songay + 3].Value = "M";
sheet.Rows[rowIndex + 2].Cells[songay + 3].CellFormat.Alignment = HorizontalCellAlignment.Center;
CellBorder(sheet, rowIndex + 2, songay + 3);
sheet.Rows[rowIndex + 2].Cells[songay + 4].Value = "S";
sheet.Rows[rowIndex + 2].Cells[songay + 4].CellFormat.Alignment = HorizontalCellAlignment.Center;
CellBorder(sheet, rowIndex + 2, songay + 4);
sheet.Rows[rowIndex + 2].Cells[songay + 5].Value = "T";
sheet.Rows[rowIndex + 2].Cells[songay + 5].CellFormat.Alignment = HorizontalCellAlignment.Center;
CellBorder(sheet, rowIndex + 2, songay + 5);
sheet.Rows[rowIndex + 2].Cells[songay + 6].Value = "K";
sheet.Rows[rowIndex + 2].Cells[songay + 6].CellFormat.Alignment = HorizontalCellAlignment.Center;
CellBorder(sheet, rowIndex + 2, songay + 6);
int index = 3;
rowIndex += 2;
foreach (DTO_NgayChamCong date in listNgay)
{
sheet.Rows[rowIndex].Cells[index].Value = date.Ngay;
//if (date.T7CN)
// BackgroundColor(sheet, rowIndex, index, index, Color.LightGray);
index++;
}
SetCellFormat(sheet, rowIndex, 3, songay + 2, true, false, false, true, true);
//SetCellFormat(sheet, rowIndex+1, 2, 1 + songay, true, false, false,false, true);
rowIndex++;
//tính tổng cộng
int sumM = 0;
int sumS = 0;
int sumT = 0;
int sumK = 0;
//Lấy STT
index = 1;
foreach (DTO_QuanLyChamCong_ThongTinChamCongThang ccThang in result)
{
sheet.Rows[rowIndex].Cells[0].Value = index;
sheet.Rows[rowIndex].Cells[0].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[0].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
CellBorder(sheet, rowIndex, 0);
sheet.Rows[rowIndex].Cells[1].Value = ccThang.MaNhanSu;
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[1].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
CellBorder(sheet, rowIndex, 1);
sheet.Rows[rowIndex].Cells[2].Value = ccThang.HoTen;
sheet.Rows[rowIndex].Cells[2].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
CellBorder(sheet, rowIndex, 2);
int idx = 3;
SetCellFormat(sheet, rowIndex, idx, songay + 6, false, false, false, true, true);
//tính tổng hợp
int totalM = 0;
int totalS = 0;
int totalT = 0;
int totalK = 0;
foreach (DTO_NgayChamCong date in listNgay)
{
//sheet.Rows[rowIndex].Cells[idx].Value = XuLyChuoi(cc.MaHinhThucNghi);
sheet.Rows[rowIndex].Cells[idx].CellFormat.Alignment = HorizontalCellAlignment.Left;
//CellBorder(sheet, rowIndex, idx);
foreach (DTO_QuanLyChamCong_ChamCongNgay cc in ccThang.ChiTietChamCong)
{
if (date.Ngay == cc.Ngay.Day)
{
sheet.Rows[rowIndex].Cells[idx].Value = XuLyChuoi(cc.MaHinhThucNghi);
sheet.Rows[rowIndex].Cells[idx].CellFormat.Font.Height = 180;
if (date.T7CN)
{
BackgroundColor(sheet, rowIndex, idx, idx, Color.LightGray);
}
if (cc.MaHinhThucNghi == "K")
{
totalK++;
}
if (cc.MaHinhThucNghi.Contains("KV") || cc.MaHinhThucNghi.Contains("KR"))
{
totalT++;
}
if (cc.MaHinhThucNghi.Contains("M"))
{
totalM += int.Parse(GetThoiGianVaoMuon(cc.MaHinhThucNghi));
}
if (cc.MaHinhThucNghi.Contains("S"))
{
totalS += int.Parse(GetThoiGianRaSom(cc.MaHinhThucNghi));
}
}
}
idx++;
}
sheet.Rows[rowIndex].Cells[songay + 3].Value = totalM;
CellBorder(sheet, rowIndex, songay + 3);
sheet.Rows[rowIndex].Cells[songay + 4].Value = totalS;
CellBorder(sheet, rowIndex, songay + 4);
sheet.Rows[rowIndex].Cells[songay + 5].Value = totalT;
CellBorder(sheet, rowIndex, songay + 5);
sheet.Rows[rowIndex].Cells[songay + 6].Value = totalK;
CellBorder(sheet, rowIndex, songay + 6);
sumM += totalM;
sumS += totalS;
sumT += totalT;
sumK += totalK;
rowIndex++;
index++;
}
WorksheetMergedCellsRegion mergedTongCong = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, songay + 2);
mergedTongCong.Value = "Tổng cộng";
SetRegionBorder(mergedTongCong, false, false, true, true);
mergedTongCong.CellFormat.Alignment = HorizontalCellAlignment.Right;
sheet.Rows[rowIndex].Cells[songay + 3].Value = sumM;
sheet.Rows[rowIndex].Cells[songay + 4].Value = sumS;
sheet.Rows[rowIndex].Cells[songay + 5].Value = sumT;
sheet.Rows[rowIndex].Cells[songay + 6].Value = sumK;
CellBorder(sheet, rowIndex, songay + 3);
CellBorder(sheet, rowIndex, songay + 4);
CellBorder(sheet, rowIndex, songay + 5);
CellBorder(sheet, rowIndex, songay + 6);
sheet.Rows[rowIndex].Cells[songay + 3].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
sheet.Rows[rowIndex].Cells[songay + 4].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
sheet.Rows[rowIndex].Cells[songay + 5].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
sheet.Rows[rowIndex].Cells[songay + 6].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
rowIndex++;
sheet.Rows[rowIndex].Cells[0].Value = "1. Ghi chú phần CHI TIẾT: ";
sheet.Rows[rowIndex].Cells[0].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[0].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
//DateTime cur = DateTime.Now;
//sheet.Rows[rowIndex].Cells[35].Value = "Ngày " + cur.Day.ToString() +" tháng " + cur.Month.ToString() + " năm " + cur.Year.ToString();
//sheet.Rows[rowIndex].Cells[35].CellFormat.Alignment = HorizontalCellAlignment.Center;
//sheet.Rows[rowIndex].Cells[35].CellFormat.Font.Bold = ExcelDefaultableBoolean.False;
//sheet.Rows[rowIndex].Cells[35].CellFormat.Font.Italic = ExcelDefaultableBoolean.True;
rowIndex++;
sheet.Rows[rowIndex].Cells[1].Value = "(1) M: Thời gian (số phút) chấm vân tay đầu giờ làm việc muộn so với 7g30p;";
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].CellFormat.WrapText = ExcelDefaultableBoolean.False;
rowIndex++;
sheet.Rows[rowIndex].Cells[1].Value = "(2) S: Thời gian (số phút) chấm vân tay cuối giờ làm việc sớm so với 17g00;";
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].CellFormat.WrapText = ExcelDefaultableBoolean.False;
rowIndex++;
sheet.Rows[rowIndex].Cells[1].Value = "(3) KV: Không chấm vân tay đầu giờ làm việc (7g30p) trong ngày;";
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].CellFormat.WrapText = ExcelDefaultableBoolean.False;
rowIndex++;
sheet.Rows[rowIndex].Cells[1].Value = "(4) KR: Không chấm vân tay cuối giờ làm việc (17g00) trong ngày;";
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].CellFormat.WrapText = ExcelDefaultableBoolean.False;
rowIndex++;
sheet.Rows[rowIndex].Cells[1].Value = "(5) K: Không chấm vân tay cả đầu giờ và cuối giờ làm việc trong ngày";
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].CellFormat.WrapText = ExcelDefaultableBoolean.False;
rowIndex++;
sheet.Rows[rowIndex].Cells[1].Value = "(6) Trường hợp nhân viên có chấm vân tay trong ngày nghỉ thì báo cáo chỉ thể hiện giờ chấm vân tay vào/ra";
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].CellFormat.WrapText = ExcelDefaultableBoolean.False;
rowIndex++;
sheet.Rows[rowIndex].Cells[0].Value = "2. Ghi chú phần TỔNG HỢP: ";
sheet.Rows[rowIndex].Cells[0].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[0].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
rowIndex++;
sheet.Rows[rowIndex].Cells[1].Value = "(1) M: Tổng thời gian (số phút) chấm vân tay đầu giờ làm việc muộn trong tháng;";
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].CellFormat.WrapText = ExcelDefaultableBoolean.False;
rowIndex++;
sheet.Rows[rowIndex].Cells[1].Value = "(2) S: Tổng thời gian (số phút) chấm vân tay cuối giờ làm việc sớm trong tháng;";
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].CellFormat.WrapText = ExcelDefaultableBoolean.False;
rowIndex++;
sheet.Rows[rowIndex].Cells[1].Value = "(3) T: Tổng số ngày thiếu chấm vân tay đầu giờ hoặc cuối giờ làm việc trong tháng;";
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].CellFormat.WrapText = ExcelDefaultableBoolean.False;
rowIndex++;
sheet.Rows[rowIndex].Cells[1].Value = "(4) K: Tổng số ngày không chấm vân tay cả đầu giờ và cuối giờ làm việc trong tháng";
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].CellFormat.WrapText = ExcelDefaultableBoolean.False;
rowIndex++;
DateTime cur = DateTime.Now;
sheet.Rows[rowIndex].Cells[21].Value = "Ngày " + cur.Day.ToString() + " tháng " + cur.Month.ToString() + " năm " + cur.Year.ToString();
sheet.Rows[rowIndex].Cells[21].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[21].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
rowIndex++;
sheet.Rows[rowIndex].Cells[4].Value = "Lập bảng";
sheet.Rows[rowIndex].Cells[4].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[4].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
sheet.Rows[rowIndex].Cells[21].Value = DonVi.TenBoPhan;
sheet.Rows[rowIndex].Cells[21].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[21].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
#endregion
string filename = "/Temp/Excel/1.xls";
BIFF8Writer.WriteWorkbookToFile(workbook, context.Server.MapPath(filename));
BinaryReader reader = new BinaryReader(new FileStream(context.Server.MapPath(filename), FileMode.Open));
context.Response.Clear();
context.Response.AddHeader("content-disposition", "attachment; filename=" + str);
context.Response.BinaryWrite(reader.ReadBytes((int)(new FileInfo(context.Server.MapPath(filename))).Length));
reader.Close();
context.Response.Flush();
}
public void ChiTietChamCongExportToExcelProcess(int thang, int nam, Guid bophanId, HttpContext context)
{
List <DTO_BangChiTietChamCongThang> result = null;
ChiTietChamCongNhanVien_Factory factory = new ChiTietChamCongNhanVien_Factory();
KyTinhLuong_Factory kyFact = new KyTinhLuong_Factory();
Guid kyTinhLuong = kyFact.GetIdByThangNam_GCRecordIsNull(thang, nam);
try
{
result = factory.Context.spd_Report_CacThongTinLamCoSoTinhTraLuong(kyTinhLuong).Map <DTO_BangChiTietChamCongThang>().ToList();
}
catch (Exception) { }
DateTime now = DateTime.Now;
string checkStatus = context.Request.Params["type"];
string str = string.Format("{0}_{1}_{2}_{3}_{4}_{5}", now.Day, now.Month, now.Year, now.Hour, now.Minute, now.Millisecond);
str = str + ".xls";
Workbook workbook = new Workbook();
workbook.Styles.NormalStyle.StyleFormat.Font.Name = "Times New Roman";
//Size 11
workbook.Styles.NormalStyle.StyleFormat.Font.Height = 220;
Worksheet sheet = workbook.Worksheets.Add("BangChamCong");
workbook.WindowOptions.SelectedWorksheet = workbook.Worksheets["BangChamCong"];
sheet.PrintOptions.PaperSize = PaperSize.A4;
//Margin 1 cm
sheet.PrintOptions.LeftMargin = 0.385;
sheet.PrintOptions.RightMargin = 0.385;
sheet.PrintOptions.BottomMargin = 0.77;
sheet.PrintOptions.TopMargin = 0.77;
sheet.PrintOptions.HeaderMargin = 0;
sheet.PrintOptions.FooterMargin = 0;
sheet.PrintOptions.Orientation = Orientation.Landscape;
sheet.Columns[0].Width = 1500;
sheet.Columns[0].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Columns[1].Width = 6500;
sheet.Columns[2].Width = 6500;
for (int i = 3; i < 10; i++)
{
sheet.Columns[i].Width = 2700;
}
for (int i = 10; i < 18; i++)
{
sheet.Columns[i].Width = 3500;
}
int rowIndex = 0;
//WorksheetMergedCellsRegion merged1 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 5);
//merged1.Value = "BỘ GIÁO DỤC VÀ ĐÀO TẠO";
//merged1.CellFormat.Alignment = HorizontalCellAlignment.Center;
//merged1.CellFormat.Font.Name = "Times New Roman";
//merged1.CellFormat.Font.Height = 220;
//merged1.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
//rowIndex++;
WorksheetMergedCellsRegion merged2 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 5);
merged2.Value = "TRƯỜNG ĐẠI HỌC NGÂN HÀNG";
merged2.CellFormat.Alignment = HorizontalCellAlignment.Center;
merged2.CellFormat.Font.Name = "Times New Roman";
merged2.CellFormat.Font.Height = 220;
merged2.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
rowIndex++;
WorksheetMergedCellsRegion merged4 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 5);
merged4.Value = "THÀNH PHỐ HỒ CHÍ MINH";
merged4.CellFormat.Alignment = HorizontalCellAlignment.Center;
merged4.CellFormat.Font.Name = "Times New Roman";
merged4.CellFormat.Font.Height = 220;
merged4.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
rowIndex++;
WorksheetMergedCellsRegion merged3 = sheet.MergedCellsRegions.Add(0, 6, 2, 17);
merged3.Value = "BẢNG CHẤM CÔNG THÁNG " + thang + "/" + nam;
merged3.CellFormat.Alignment = HorizontalCellAlignment.Center;
merged3.CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
merged3.CellFormat.Font.Name = "Times New Roman";
merged3.CellFormat.Font.Height = 260;
merged3.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
merged3.CellFormat.WrapText = ExcelDefaultableBoolean.True;
rowIndex++;
#region TargetGroup
BackgroundColor(sheet, rowIndex, 0, 17, Color.LightGray);
BackgroundColor(sheet, rowIndex + 1, 0, 17, Color.LightGray);
for (int a = 0; a < 11; a++)
{
sheet.Rows[rowIndex].Cells[a].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[a].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[a].CellFormat.WrapText = ExcelDefaultableBoolean.True;
sheet.Rows[rowIndex].Cells[a].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
CellBorder(sheet, rowIndex, a);
}
WorksheetMergedCellsRegion mergedStt = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex + 1, 0);
mergedStt.Value = "STT";
SetRegionBorder(mergedStt, true, false, true, true);
mergedStt.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion mergedHoTen = sheet.MergedCellsRegions.Add(rowIndex, 1, rowIndex + 1, 1);
mergedHoTen.Value = "Họ tên";
SetRegionBorder(mergedHoTen, true, false, true, true);
mergedHoTen.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion mergedDonVi = sheet.MergedCellsRegions.Add(rowIndex, 2, rowIndex + 1, 2);
mergedDonVi.Value = "Đơn vị";
SetRegionBorder(mergedDonVi, true, false, true, true);
mergedDonVi.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion merged5 = sheet.MergedCellsRegions.Add(rowIndex, 10, rowIndex + 1, 10);
merged5.Value = "Tổng ngày công làm việc thực tế";
SetRegionBorder(merged5, true, false, true, true);
merged5.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion merged6 = sheet.MergedCellsRegions.Add(rowIndex, 11, rowIndex + 1, 11);
merged6.Value = "Tổng ngày công hưởng lương";
SetRegionBorder(merged6, true, false, true, true);
merged6.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion merged7 = sheet.MergedCellsRegions.Add(rowIndex, 12, rowIndex + 1, 12);
merged7.Value = "Tổng ngày đi học ĐT, BD";
SetRegionBorder(merged7, true, false, true, true);
merged7.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion merged8 = sheet.MergedCellsRegions.Add(rowIndex, 13, rowIndex + 1, 13);
merged8.Value = "Tổng ngày công không hưởng lương";
SetRegionBorder(merged8, true, false, true, true);
merged8.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion merged9 = sheet.MergedCellsRegions.Add(rowIndex, 14, rowIndex + 1, 14);
merged9.Value = "Tổng ngày công hưởng BHXH";
SetRegionBorder(merged9, true, false, true, true);
merged9.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion merged10 = sheet.MergedCellsRegions.Add(rowIndex, 15, rowIndex + 1, 15);
merged10.Value = "Xếp loại lao động";
SetRegionBorder(merged10, true, false, true, true);
merged10.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion merged11 = sheet.MergedCellsRegions.Add(rowIndex, 16, rowIndex + 1, 16);
merged11.Value = "Lý do xếp loại B";
SetRegionBorder(merged11, true, false, true, true);
merged11.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion merged12 = sheet.MergedCellsRegions.Add(rowIndex, 17, rowIndex + 1, 17);
merged12.Value = "Ghi chú";
SetRegionBorder(merged12, true, false, true, true);
merged12.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion merged13 = sheet.MergedCellsRegions.Add(rowIndex, 3, rowIndex, 9);
merged13.Value = "Số ngày công giảm";
SetRegionBorder(merged13, true, false, true, true);
merged13.CellFormat.Alignment = HorizontalCellAlignment.Center;
rowIndex++;
sheet.Rows[rowIndex].Cells[3].Value = "Nghỉ phép";
sheet.Rows[rowIndex].Cells[4].Value = "Nghỉ đám cưới";
sheet.Rows[rowIndex].Cells[5].Value = "Nghỉ đám tang";
sheet.Rows[rowIndex].Cells[6].Value = "Nghỉ ốm";
sheet.Rows[rowIndex].Cells[7].Value = "Nghỉ thai sản";
sheet.Rows[rowIndex].Cells[8].Value = "Nghỉ việc riêng";
sheet.Rows[rowIndex].Cells[9].Value = "Nghỉ khác";
for (int a = 3; a < 10; a++)
{
sheet.Rows[rowIndex].Cells[a].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[a].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[a].CellFormat.WrapText = ExcelDefaultableBoolean.True;
CellBorder(sheet, rowIndex, a);
}
rowIndex++;
//Lấy STT
int index = 1;
foreach (DTO_BangChiTietChamCongThang ccThang in result)
{
for (int a = 3; a < 18; a++)
{
sheet.Rows[rowIndex].Cells[a].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[a].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[a].CellFormat.WrapText = ExcelDefaultableBoolean.True;
CellBorder(sheet, rowIndex, a);
}
sheet.Rows[rowIndex].Cells[0].Value = ccThang.STT;
sheet.Rows[rowIndex].Cells[0].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[0].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
CellBorder(sheet, rowIndex, 0);
sheet.Rows[rowIndex].Cells[1].Value = ccThang.HoTen;
sheet.Rows[rowIndex].Cells[1].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[1].CellFormat.WrapText = ExcelDefaultableBoolean.True;
CellBorder(sheet, rowIndex, 1);
sheet.Rows[rowIndex].Cells[2].Value = ccThang.TenBoPhan;
sheet.Rows[rowIndex].Cells[2].CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[2].CellFormat.WrapText = ExcelDefaultableBoolean.True;
CellBorder(sheet, rowIndex, 2);
sheet.Rows[rowIndex].Cells[3].Value = ccThang.TongNghiPhep;
sheet.Rows[rowIndex].Cells[4].Value = ccThang.TongNghiDamCuoi;
sheet.Rows[rowIndex].Cells[5].Value = ccThang.TongNghiDamTang;
sheet.Rows[rowIndex].Cells[6].Value = ccThang.TongNghiOm;
sheet.Rows[rowIndex].Cells[7].Value = ccThang.TongNghiThaiSan;
sheet.Rows[rowIndex].Cells[8].Value = ccThang.TongNghiViecRieng;
sheet.Rows[rowIndex].Cells[9].Value = ccThang.TongNghiKhac;
sheet.Rows[rowIndex].Cells[10].Value = ccThang.TongNgayCongLamViec;
sheet.Rows[rowIndex].Cells[11].Value = ccThang.TongNgayCongHuongLuong;
sheet.Rows[rowIndex].Cells[12].Value = ccThang.TongDiHoc;
sheet.Rows[rowIndex].Cells[13].Value = ccThang.TongKhongLuong;
sheet.Rows[rowIndex].Cells[14].Value = ccThang.TongBHXH;
sheet.Rows[rowIndex].Cells[15].Value = ccThang.XepLoai;
sheet.Rows[rowIndex].Cells[17].Value = ccThang.DienGiai;
rowIndex++;
index++;
}
#endregion
string filename = "/Temp/Excel/1.xls";
BIFF8Writer.WriteWorkbookToFile(workbook, context.Server.MapPath(filename));
BinaryReader reader = new BinaryReader(new FileStream(context.Server.MapPath(filename), FileMode.Open));
context.Response.Clear();
context.Response.AddHeader("content-disposition", "attachment; filename=" + str);
context.Response.BinaryWrite(reader.ReadBytes((int)(new FileInfo(context.Server.MapPath(filename))).Length));
reader.Close();
context.Response.Flush();
}
public void BangChamCongExportToExcelProcess(int thang, int nam, Guid bophanId, Guid idNhanVien, string webGroupId, string maNhanSu, Guid idLoaiNhanSu, HttpContext context)
{
if (bophanId == Guid.Empty && idNhanVien != Guid.Empty)
{
ApplicationUser applicationUser = AuthenticationHelper.GetUserById(new Guid(HttpContext.Current.User.Identity.GetUserId()), HttpContext.Current.User.Identity.Name);
bophanId = Guid.Parse(applicationUser.DepartmentId);
}
IQueryable <DTO_QuanLyChamCong_ThongTinChamCongThang> result = null;
CC_ChamCongTheoNgay_Factory factory = new CC_ChamCongTheoNgay_Factory();
if (webGroupId.ToUpper() == "53D57298-1933-4E4B-B4C8-98AFED036E21")
{
result = factory.QuanLyChamCong_ThongTinChamCongThang_Cua1NhanVien(thang, nam, idNhanVien);
}
else
{
result = factory.QuanLyChamCong_ThongTinChamCongThang(thang, nam, bophanId, maNhanSu, idLoaiNhanSu);
}
BoPhan_Factory bpfac = new BoPhan_Factory();
BoPhan DonVi = bpfac.GetByID(bophanId);
List <DTO_NgayChamCong> listNgay = factory.GetList_NgayTrongKyChamCong(thang, nam).ToList();
int songay = listNgay.Count();
DateTime now = DateTime.Now;
string checkStatus = context.Request.Params["type"];
string str = string.Format("{0}_{1}_{2}_{3}_{4}_{5}", now.Day, now.Month, now.Year, now.Hour, now.Minute, now.Millisecond);
str = str + ".xls";
Workbook workbook = new Workbook();
workbook.Styles.NormalStyle.StyleFormat.Font.Name = "Times New Roman";
//Size 11
workbook.Styles.NormalStyle.StyleFormat.Font.Height = 220;
Worksheet sheet = workbook.Worksheets.Add("BangChamCong");
workbook.WindowOptions.SelectedWorksheet = workbook.Worksheets["BangChamCong"];
sheet.PrintOptions.PaperSize = PaperSize.A3;
//Margin 1 cm
sheet.PrintOptions.LeftMargin = 0.385;
sheet.PrintOptions.RightMargin = 0.385;
sheet.PrintOptions.BottomMargin = 0.77;
sheet.PrintOptions.TopMargin = 0.77;
sheet.PrintOptions.HeaderMargin = 0;
sheet.PrintOptions.FooterMargin = 0;
sheet.PrintOptions.Orientation = Orientation.Landscape;
sheet.Columns[0].Width = 1500;
sheet.Columns[0].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Columns[1].Width = 7000;
for (int i = 2; i <= songay + 1; i++)
{
sheet.Columns[i].Width = 900;
sheet.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
}
for (int i = songay + 2; i <= songay + 6; i++)
{
sheet.Columns[i].Width = 2800;
sheet.Columns[i].CellFormat.Alignment = HorizontalCellAlignment.Center;
}
int rowIndex = 0;
//WorksheetMergedCellsRegion merged1 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 11);
//merged1.Value = "BỘ GIÁO DỤC VÀ ĐÀO TẠO";
//merged1.CellFormat.Alignment = HorizontalCellAlignment.Center;
//merged1.CellFormat.Font.Name = "Times New Roman";
//merged1.CellFormat.Font.Height = 240;
//merged1.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
//rowIndex++;
WorksheetMergedCellsRegion merged2 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 11);
merged2.Value = "TRƯỜNG ĐẠI HỌC NGÂN HÀNG";
merged2.CellFormat.Alignment = HorizontalCellAlignment.Center;
merged2.CellFormat.Font.Name = "Times New Roman";
merged2.CellFormat.Font.Height = 240;
merged2.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
rowIndex++;
WorksheetMergedCellsRegion merged4 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, 11);
merged4.Value = "THÀNH PHỐ HỒ CHÍ MINH";
merged4.CellFormat.Alignment = HorizontalCellAlignment.Center;
merged4.CellFormat.Font.Name = "Times New Roman";
merged4.CellFormat.Font.Height = 240;
merged4.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
rowIndex++;
WorksheetMergedCellsRegion merged3 = sheet.MergedCellsRegions.Add(0, 12, 2, songay + 6);
//merged3.Value = "BẢNG CHẤM CÔNG VÀ PHÂN LOẠI LAO ĐỘNG";
////Khoảng trắng để wrap text
//merged3.Value+= " THÁNG " + thang + "/" + nam;
merged3.Value = "BẢNG CHẤM CÔNG THÁNG " + thang + "/" + nam;
merged3.CellFormat.Alignment = HorizontalCellAlignment.Center;
merged3.CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
merged3.CellFormat.Font.Name = "Times New Roman";
merged3.CellFormat.Font.Height = 300;
merged3.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
merged3.CellFormat.WrapText = ExcelDefaultableBoolean.True;
rowIndex++;
WorksheetMergedCellsRegion merged5 = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex, songay + 6);
merged5.Value = "ĐƠN VỊ: " + DonVi.TenBoPhan.ToUpper();
merged5.CellFormat.Alignment = HorizontalCellAlignment.Center;
merged5.CellFormat.Font.Name = "Times New Roman";
merged5.CellFormat.Font.Height = 260;
merged5.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
rowIndex++;
rowIndex++;
#region TargetGroup
BackgroundColor(sheet, rowIndex, 0, songay + 6, Color.LightGray);
BackgroundColor(sheet, rowIndex + 1, 2, songay + 1, Color.LightGray);
BackgroundColor(sheet, rowIndex + 2, 0, songay + 1, Color.LightGray);
WorksheetMergedCellsRegion mergedStt = sheet.MergedCellsRegions.Add(rowIndex, 0, rowIndex + 2, 0);
mergedStt.Value = "STT";
SetRegionBorder(mergedStt, true, false, true, true);
mergedStt.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion mergedHoTen = sheet.MergedCellsRegions.Add(rowIndex, 1, rowIndex + 2, 1);
mergedHoTen.Value = "Họ tên";
SetRegionBorder(mergedHoTen, true, false, true, true);
mergedHoTen.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion mergedNgayTrongThang = sheet.MergedCellsRegions.Add(rowIndex, 2, rowIndex, songay + 1);
mergedNgayTrongThang.Value = "Ngày trong tháng";
SetRegionBorder(mergedNgayTrongThang, true, false, true, true);
mergedNgayTrongThang.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion mergedNgayCong = sheet.MergedCellsRegions.Add(rowIndex, songay + 2, rowIndex + 2, songay + 2);
mergedNgayCong.Value = "Số ngày công";
SetRegionBorder(mergedNgayCong, true, false, true, true);
mergedNgayCong.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion mergedHuongLuong = sheet.MergedCellsRegions.Add(rowIndex, songay + 3, rowIndex + 2, songay + 3);
mergedHuongLuong.Value = "Nghỉ có phép";
SetRegionBorder(mergedHuongLuong, true, false, true, true);
mergedHuongLuong.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion mergedDiHoc = sheet.MergedCellsRegions.Add(rowIndex, songay + 4, rowIndex + 2, songay + 4);
mergedDiHoc.Value = "Nghỉ trừ lương";
SetRegionBorder(mergedDiHoc, true, false, true, true);
mergedDiHoc.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion mergedKhongLuong = sheet.MergedCellsRegions.Add(rowIndex, songay + 5, rowIndex + 2, songay + 5);
mergedKhongLuong.Value = "Nghỉ chế độ ốm đau";
SetRegionBorder(mergedKhongLuong, true, false, true, true);
mergedKhongLuong.CellFormat.Alignment = HorizontalCellAlignment.Center;
WorksheetMergedCellsRegion mergedBHXH = sheet.MergedCellsRegions.Add(rowIndex, songay + 6, rowIndex + 2, songay + 6);
mergedBHXH.Value = "Nghỉ chế độ thai sản";
SetRegionBorder(mergedBHXH, true, false, true, true);
mergedBHXH.CellFormat.Alignment = HorizontalCellAlignment.Center;
int index = 2;
rowIndex++;
foreach (DTO_NgayChamCong date in listNgay)
{
sheet.Rows[rowIndex].Cells[index].Value = date.Ngay;
sheet.Rows[rowIndex + 1].Cells[index].Value = date.Thu;
index++;
}
SetCellFormat(sheet, rowIndex, 2, 1 + songay, true, false, false, true, false);
SetCellFormat(sheet, rowIndex + 1, 2, 1 + songay, true, false, false, false, true);
rowIndex++;
rowIndex++;
//Lấy STT
index = 1;
foreach (DTO_QuanLyChamCong_ThongTinChamCongThang ccThang in result)
{
sheet.Rows[rowIndex].Cells[0].Value = index;
sheet.Rows[rowIndex].Cells[0].CellFormat.Alignment = HorizontalCellAlignment.Center;
CellBorder(sheet, rowIndex, 0);
sheet.Rows[rowIndex].Cells[1].Value = ccThang.HoTen;
CellBorder(sheet, rowIndex, 1);
int idx = 2;
foreach (DTO_QuanLyChamCong_ChamCongNgay cc in ccThang.ChiTietChamCong)
{
sheet.Rows[rowIndex].Cells[idx].Value = cc.MaHinhThucNghi;
CellBorder(sheet, rowIndex, idx);
idx++;
}
sheet.Rows[rowIndex].Cells[idx].Value = ccThang.NgayCong;
CellBorder(sheet, rowIndex, idx);
sheet.Rows[rowIndex].Cells[idx + 1].Value = ccThang.NghiPhep;
CellBorder(sheet, rowIndex, idx + 1);
sheet.Rows[rowIndex].Cells[idx + 2].Value = ccThang.NghiTruLuong;
CellBorder(sheet, rowIndex, idx + 2);
sheet.Rows[rowIndex].Cells[idx + 3].Value = ccThang.NghiOmDau;
CellBorder(sheet, rowIndex, idx + 3);
sheet.Rows[rowIndex].Cells[idx + 4].Value = ccThang.NghiThaiSan;
CellBorder(sheet, rowIndex, idx + 4);
rowIndex++;
index++;
}
rowIndex++;
sheet.Rows[rowIndex].Cells[0].Value = "KÝ HIỆU CHẤM CÔNG: ";
sheet.Rows[rowIndex].Cells[0].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[0].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
DateTime cur = DateTime.Now;
sheet.Rows[rowIndex].Cells[35].Value = "Ngày " + cur.Day.ToString() + " tháng " + cur.Month.ToString() + " năm " + cur.Year.ToString();
sheet.Rows[rowIndex].Cells[35].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[35].CellFormat.Font.Bold = ExcelDefaultableBoolean.False;
sheet.Rows[rowIndex].Cells[35].CellFormat.Font.Italic = ExcelDefaultableBoolean.True;
rowIndex++;
sheet.Rows[rowIndex].Cells[0].Value = "+";
sheet.Rows[rowIndex].Cells[0].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].Value = "Đi làm, hội họp cả ngày";
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].CellFormat.WrapText = ExcelDefaultableBoolean.False;
sheet.Rows[rowIndex].Cells[15].Value = "Người chấm công";
sheet.Rows[rowIndex].Cells[15].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[15].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
sheet.Rows[rowIndex].Cells[27].Value = "Trưởng Đơn vị";
sheet.Rows[rowIndex].Cells[27].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[27].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
sheet.Rows[rowIndex].Cells[35].Value = "Phòng Tổ chức - Cán bộ";
sheet.Rows[rowIndex].Cells[35].CellFormat.Alignment = HorizontalCellAlignment.Center;
sheet.Rows[rowIndex].Cells[35].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
rowIndex++;
HinhThucNghi_Factory factoryHTN = new HinhThucNghi_Factory();
List <DTO_HinhThucNghi> listHinhThucNghi = factoryHTN.GetListForUpdate().Map <DTO_HinhThucNghi>().ToList();
int a = Convert.ToInt32(listHinhThucNghi.Count() / 2);
int b = 1;
int rowIndex2 = rowIndex - 1;
int rowIndex3 = rowIndex - 1;
foreach (DTO_HinhThucNghi htn in listHinhThucNghi)
{
if (b <= a)
{
sheet.Rows[rowIndex].Cells[0].Value = htn.KyHieu;
sheet.Rows[rowIndex].Cells[0].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].Value = htn.TenHinhThucNghi;
sheet.Rows[rowIndex].Cells[1].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex].Cells[1].CellFormat.WrapText = ExcelDefaultableBoolean.False;
rowIndex++;
b++;
}
else
{
sheet.Rows[rowIndex2].Cells[4].Value = htn.KyHieu;
sheet.Rows[rowIndex2].Cells[4].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex2].Cells[6].Value = htn.TenHinhThucNghi;
sheet.Rows[rowIndex2].Cells[6].CellFormat.Alignment = HorizontalCellAlignment.Left;
sheet.Rows[rowIndex2].Cells[6].CellFormat.WrapText = ExcelDefaultableBoolean.False;
rowIndex2++;
b++;
}
}
#endregion
string filename = "/Temp/Excel/1.xls";
BIFF8Writer.WriteWorkbookToFile(workbook, context.Server.MapPath(filename));
BinaryReader reader = new BinaryReader(new FileStream(context.Server.MapPath(filename), FileMode.Open));
context.Response.Clear();
context.Response.AddHeader("content-disposition", "attachment; filename=" + str);
context.Response.BinaryWrite(reader.ReadBytes((int)(new FileInfo(context.Server.MapPath(filename))).Length));
reader.Close();
context.Response.Flush();
}