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 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 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();
}
