private void LoadData() { // do du lieu vao combobox cmbHe.Properties.DataSource = LoadHe(); cmbTrinhDo.Properties.DataSource = LoadTrinhDo(); cmbKhoaHoc.Properties.DataSource = LoadKhoaHoc(); cmbNganh.Properties.DataSource = LoadNganh(); dtMonKy = MonHocTrongKy(0); Lib.clsDataTableHelper cls = new Lib.clsDataTableHelper(); cmbMon.Properties.DataSource = cls.SelectDistinct(dtMonKy, new string[] { "IDDM_MonHoc", "TenMonHoc", "MaMonHoc" }); }
/// <summary> /// Gán các cột được fix với các giá trị về tên lớp và số học sinh của lớp đó /// </summary> private void GanCotFix() { Lib.clsDataTableHelper cls = new Lib.clsDataTableHelper(); DataTable dtLop = cls.SelectDistinct(dtKeHoach, new string[] { "DM_LopID", "TenLop", "SoSinhVien" }); DataRow[] arrDr; fg.Cols[0].TextAlign = TextAlignEnum.LeftCenter; for (int i = RowBegin; i < fg.Rows.Count; i++) { arrDr = dtLop.Select("DM_LopID = " + fg[i, "IDDM_Lop"]); fg[i, 0] = arrDr[0]["TenLop"].ToString(); fg[i, 1] = EncodeString(arrDr[0]["SoSinhVien"].ToString(), i); } }
public dlgThemMonHoc_LopTach(string mXL_LopTachGopIDs, int mIDDM_Lop, int mIDXL_MonHocTrongKy, string mIDXL_MonHocTrongKys, bool mCheckTach_Gop, string mIDDM_Lops, int mSoLop) { InitializeComponent(); oBXL_GiaoVien_MonHoc = new cBXL_GiaoVien_MonHoc(); oBXL_LopTachGop_MonHoc = new cBXL_LopTachGop_MonHoc(); oBXL_LopTachGop = new cBXL_LopTachGop(); pXL_GiaoVien_MonHocInfo = new XL_GiaoVien_MonHocInfo(); pXL_LopTachGop_MonHocInfo = new XL_LopTachGop_MonHocInfo(); CheckTach_Gop = mCheckTach_Gop; XL_LopTachGopIDs = mXL_LopTachGopIDs; IDXL_MonHocTrongKy = mIDXL_MonHocTrongKy; IDXL_MonHocTrongKys = mIDXL_MonHocTrongKys; // them mon hoc - lop tach if (CheckTach_Gop == false) { dtMonHoc = MonHocTrongKyGetAll(mIDDM_Lop); IDDM_Lop = mIDDM_Lop; } else { // them mon hoc - lop gop dtMonHocAll = MonHocTrongKyGetByLopGop(mIDDM_Lops, mSoLop); Lib.clsDataTableHelper cls = new Lib.clsDataTableHelper(); dtMonHoc = cls.SelectDistinct(dtMonHocAll, new string[] { "IDDM_MonHoc", "TenMonHoc", "MaMonHoc", "IDNS_GiaoVien", "IDDM_PhongHoc", "Cahocs", "Chon" }); IDDM_Lops = mIDDM_Lops; char[] mChar = { ',' }; ArrayLop = IDDM_Lops.Split(mChar); IDDM_Lop = int.Parse(XL_LopTachGopIDs.Substring(0, XL_LopTachGopIDs.IndexOf(","))); SoLop = mSoLop; } dtMonHoc.AcceptChanges(); repositoryPhongHoc.DataSource = LoadPhongHoc(); this.Tag = ""; }
private void XuatChiTietGioGiangRaExcel(DataTable dtChiTiet, string FileExcel) { CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!"); this.Cursor = System.Windows.Forms.Cursors.WaitCursor; int DongBatDau = 7, DongKetThuc = DongBatDau, STT = 1; double SoTietTC = 0, SoTietCD = 0, TongThucHien = 0, DinhMuc = 0; bool IsDaThemDong = false; Excel.Range cel; Excel.ApplicationClass excel = new Excel.ApplicationClass(); try { excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false); DataTable dtDinhMuc = (new cBGG_DinhMucGioDay()).GetByIDNS_DonVi((int)cmbDonVi.EditValue, Program.IDNamHoc, Program.HocKy); // Ten don vi excel.Cells[4, 1] = cmbDonVi.Text.ToUpper(); // Năm học, học kỳ excel.Cells[5, 1] = "HỌC KỲ " + Program.HocKy.ToString() + " NĂM HỌC " + Program.NamHoc; Lib.clsDataTableHelper clsDt = new Lib.clsDataTableHelper(); DataTable dtGV = clsDt.SelectDistinct(dtChiTiet, new string[] { "Ten", "HoTen", "NS_GiaoVienID" }, new string[] { "KhoaDonVi" }); DataView dv = new DataView(dtGV); dv.Sort = "KhoaDonVi DESC, Ten, HoTen"; dtGV = dv.ToTable(); DataRow[] arrDr; string IDNS_GiaoVien = dtGV.Rows[0]["NS_GiaoVienID"].ToString(); arrDr = dtDinhMuc.Select("IDNS_GiaoVien = " + dtGV.Rows[0]["NS_GiaoVienID"]); DinhMuc = (arrDr.Length <= 0 ? 0 : double.Parse(arrDr[0]["SoGioDinhMuc"].ToString())); // Them dong moi cel = (Excel.Range)(excel.Cells[DongBatDau + 1, 1]); cel.EntireRow.Insert(Excel.XlDirection.xlUp, null); IsDaThemDong = true; excel.Cells[DongKetThuc, 1] = STT.ToString(); STT++; excel.Cells[DongKetThuc, 2] = dtGV.Rows[0]["HoTen"].ToString(); foreach (DataRow dr in dtGV.Rows) { if (IDNS_GiaoVien != dr["NS_GiaoVienID"].ToString()) { // Gan gia tri cho row dau tien cua giao vien excel.Cells[DongBatDau, 12] = SoTietTC; excel.Cells[DongBatDau, 13] = SoTietCD; excel.Cells[DongBatDau, 14] = DinhMuc; excel.Cells[DongBatDau, 15] = TongThucHien; if (DinhMuc - TongThucHien < 0) { excel.Cells[DongBatDau, 16] = TongThucHien - DinhMuc; } else { excel.Cells[DongBatDau, 17] = DinhMuc - TongThucHien; } // Merge va border cac dong trong cac cot cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongKetThuc - 1, 1]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; cel.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; cel = excel.get_Range(excel.Cells[DongBatDau, 2], excel.Cells[DongKetThuc - 1, 2]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; cel.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; cel = excel.get_Range(excel.Cells[DongBatDau, 2], excel.Cells[DongKetThuc - 1, 2]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; cel.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; for (int j = 12; j <= 17; j++) { cel = excel.get_Range(excel.Cells[DongBatDau, j], excel.Cells[DongKetThuc - 1, j]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; cel.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; } // Them dong moi cho giao vien moi cel = (Excel.Range)(excel.Cells[DongKetThuc + 1, 1]); cel.EntireRow.Insert(Excel.XlDirection.xlUp, null); IsDaThemDong = true; IDNS_GiaoVien = dr["NS_GiaoVienID"].ToString(); DongBatDau = DongKetThuc; SoTietTC = 0; SoTietCD = 0; TongThucHien = 0; arrDr = dtDinhMuc.Select("IDNS_GiaoVien = " + IDNS_GiaoVien); DinhMuc = (arrDr.Length <= 0 ? 0 : double.Parse(arrDr[0]["SoGioDinhMuc"].ToString())); excel.Cells[DongKetThuc, 1] = STT.ToString(); STT++; excel.Cells[DongKetThuc, 2] = dr["HoTen"].ToString(); } arrDr = dtChiTiet.Select("NS_GiaoVienID = " + IDNS_GiaoVien + " And wType = 'GD'"); if (arrDr.Length > 0) { for (int j = 0; j < arrDr.Length; j++) { if (!IsDaThemDong) { cel = (Excel.Range)(excel.Cells[DongKetThuc + 1, 1]); cel.EntireRow.Insert(Excel.XlDirection.xlUp, null); } excel.Cells[DongKetThuc, 3] = arrDr[j]["TenMonHoc"]; excel.Cells[DongKetThuc, 4] = arrDr[j]["TenLop"] + " (" + arrDr[j]["SoSinhVien"] + ")"; if (double.Parse("0" + arrDr[j]["LyThuyet"]) > 0) { excel.Cells[DongKetThuc, 5] = arrDr[j]["LyThuyet"]; ThuocTrinhDo(arrDr[j]["IDDM_TrinhDo"].ToString(), double.Parse(arrDr[j]["LyThuyet"].ToString()), ref SoTietCD, ref SoTietTC); } cel = (Excel.Range)(excel.Cells[DongKetThuc, 6]); if (double.Parse("0" + arrDr[j]["LyThuyetQuyChuan"]) > 0) { excel.Cells[DongKetThuc, 6] = arrDr[j]["LyThuyetQuyChuan"]; cel.NumberFormat = "#,##0.00"; } cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; if (double.Parse("0" + arrDr[j]["ThucHanh"]) > 0) { excel.Cells[DongKetThuc, 7] = arrDr[j]["ThucHanh"]; ThuocTrinhDo(arrDr[j]["IDDM_TrinhDo"].ToString(), double.Parse(arrDr[j]["ThucHanh"].ToString()) * SoTietQuyDoiThucHanhTrongTuan / SoGioThucHanhTrongTuan, ref SoTietCD, ref SoTietTC); excel.Cells[DongKetThuc, 8] = arrDr[j]["SoNhom"]; } cel = (Excel.Range)(excel.Cells[DongKetThuc, 9]); if (double.Parse("0" + arrDr[j]["ThucHanhQuyChuan"]) > 0) { excel.Cells[DongKetThuc, 9] = arrDr[j]["ThucHanhQuyChuan"]; cel.NumberFormat = "#,##0.00"; } cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; cel = (Excel.Range)(excel.Cells[DongKetThuc, 10]); if (double.Parse("0" + arrDr[j]["HeSoLopDong"]) > 0) { excel.Cells[DongKetThuc, 10] = arrDr[j]["HeSoLopDong"]; cel.NumberFormat = "#,##0.00"; } cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; cel = (Excel.Range)(excel.Cells[DongKetThuc, 11]); if (double.Parse("0" + arrDr[j]["GioQuyChuan"]) > 0) { excel.Cells[DongKetThuc, 11] = arrDr[j]["GioQuyChuan"]; cel.NumberFormat = "#,##0.00"; } cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; TongThucHien += double.Parse(arrDr[j]["GioQuyChuan"].ToString()); IsDaThemDong = false; DongKetThuc++; } } arrDr = dtChiTiet.Select("NS_GiaoVienID = " + IDNS_GiaoVien + " And wType = 'CVK'"); if (arrDr.Length > 0) { for (int j = 0; j < arrDr.Length; j++) { if (!IsDaThemDong) { cel = (Excel.Range)(excel.Cells[DongKetThuc + 1, 1]); cel.EntireRow.Insert(Excel.XlDirection.xlUp, null); } excel.Cells[DongKetThuc, 3] = arrDr[j]["TenMonHoc"]; excel.Cells[DongKetThuc, 4] = arrDr[j]["GhiChu"]; cel = (Excel.Range)(excel.Cells[DongKetThuc, 11]); excel.Cells[DongKetThuc, 11] = arrDr[j]["GioQuyChuan"]; cel.NumberFormat = "#,##0.00"; cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; SoTietCD += double.Parse(arrDr[j]["GioQuyChuan"].ToString()); TongThucHien += double.Parse(arrDr[j]["GioQuyChuan"].ToString()); IsDaThemDong = false; DongKetThuc++; } } } // Gan gia tri cho row dau tien cua giao vien excel.Cells[DongBatDau, 12] = SoTietTC; excel.Cells[DongBatDau, 13] = SoTietCD; excel.Cells[DongBatDau, 14] = DinhMuc; excel.Cells[DongBatDau, 15] = TongThucHien; if (DinhMuc - TongThucHien < 0) { excel.Cells[DongBatDau, 16] = TongThucHien - DinhMuc; } else { excel.Cells[DongBatDau, 17] = DinhMuc - TongThucHien; } // Merge va border cac dong trong cac cot cel = excel.get_Range(excel.Cells[DongBatDau, 1], excel.Cells[DongKetThuc - 1, 1]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; cel.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; cel = excel.get_Range(excel.Cells[DongBatDau, 2], excel.Cells[DongKetThuc - 1, 2]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; cel.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; cel = excel.get_Range(excel.Cells[DongBatDau, 2], excel.Cells[DongKetThuc - 1, 2]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; cel.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; for (int j = 12; j <= 17; j++) { cel = excel.get_Range(excel.Cells[DongBatDau, j], excel.Cells[DongKetThuc - 1, j]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; cel.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; } cel = excel.get_Range(excel.Cells[7, 1], excel.Cells[DongKetThuc - 1, 17]); cel.Borders.Value = 1; excel.Application.Workbooks[1].Save(); excel.Visible = true; //Process.Start(FileExcel); } catch (Exception e) { excel.Application.Workbooks[1].Save(); this.Cursor = System.Windows.Forms.Cursors.Default; excel.Application.Workbooks.Close(); excel.Application.Quit(); excel.Quit(); ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng các file Excel trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message); CloseWaitDialog(); this.Cursor = System.Windows.Forms.Cursors.Default; return; } finally { CloseWaitDialog(); this.Cursor = System.Windows.Forms.Cursors.Default; } }
private void XuatDanhSachDuThiRaExcel(DataTable dtChiTiet, string FileExcel) { CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!"); this.Cursor = System.Windows.Forms.Cursors.WaitCursor; int DongBatDau = 6, i = 0; Excel.Range cel; Excel.ApplicationClass excel = new Excel.ApplicationClass(); try { excel.Application.Workbooks.Open(FileExcel, true, false, true, "", "", true, true, true, true, true, true, true, true, false); DataTable dtDinhMuc = (new cBGG_DinhMucGioDay()).GetByIDNS_DonVi((int)cmbDonVi.EditValue, Program.IDNamHoc, Program.HocKy); // Ten don vi excel.Cells[4, 1] = cmbDonVi.Text.ToUpper(); // Năm học, học kỳ excel.Cells[5, 1] = "HỌC KỲ " + Program.HocKy.ToString() + " NĂM HỌC " + Program.NamHoc; Lib.clsDataTableHelper clsDt = new Lib.clsDataTableHelper(); DataTable dtGV = clsDt.SelectDistinct(dtChiTiet, new string[] { "Ten", "HoTen", "NS_GiaoVienID" }); DataRow[] arrDr; double SoGioThucHien = 0; foreach (DataRow dr in dtGV.Rows) { SoGioThucHien = 0; cel = (Excel.Range)(excel.Cells[DongBatDau + i + 1, 1]); cel.EntireRow.Insert(Excel.XlDirection.xlUp, null); i++; cel = (Excel.Range)(excel.Cells[DongBatDau + i + 1, 1]); cel.EntireRow.Insert(Excel.XlDirection.xlUp, null); excel.Cells[DongBatDau + i, 1] = "Giảng viên: " + dr["HoTen"]; cel = excel.get_Range(excel.Cells[DongBatDau + i, 1], excel.Cells[DongBatDau + i, 5]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; i++; arrDr = dtChiTiet.Select("NS_GiaoVienID = " + dr["NS_GiaoVienID"] + " And wType = 'CVK'"); if (arrDr.Length > 0) { // Thêm dòng công việc thực hiện cel = (Excel.Range)(excel.Cells[DongBatDau + i + 1, 1]); cel.EntireRow.Insert(Excel.XlDirection.xlUp, null); excel.Cells[DongBatDau + i, 2] = "'- Công việc thực hiện"; cel = excel.get_Range(excel.Cells[DongBatDau + i, 2], excel.Cells[DongBatDau + i, 5]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; cel.Font.Bold = false; i++; // Thêm phần tiêu đề bảng cel = (Excel.Range)(excel.Cells[DongBatDau + i + 1, 1]); cel.EntireRow.Insert(Excel.XlDirection.xlUp, null); excel.Cells[DongBatDau + i, 1] = "TT"; excel.Cells[DongBatDau + i, 2] = "Tên công việc"; cel = excel.get_Range(excel.Cells[DongBatDau + i, 2], excel.Cells[DongBatDau + i, 4]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.Cells[DongBatDau + i, 5] = "Số giờ"; cel = excel.get_Range(excel.Cells[DongBatDau + i, 5], excel.Cells[DongBatDau + i, 6]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.Cells[DongBatDau + i, 7] = "Hệ số"; cel = excel.get_Range(excel.Cells[DongBatDau + i, 7], excel.Cells[DongBatDau + i, 8]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.Cells[DongBatDau + i, 9] = "Giờ quy chuẩn"; cel = (Excel.Range)(excel.Cells[DongBatDau + i, 9]); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.Cells[DongBatDau + i, 10] = "Ghi chú"; cel = excel.get_Range(excel.Cells[DongBatDau + i, 10], excel.Cells[DongBatDau + i, 11]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; cel = excel.get_Range(excel.Cells[DongBatDau + i, 1], excel.Cells[DongBatDau + i, 11]); cel.Font.Bold = true; cel.RowHeight = 35.25; cel.Borders.Value = 1; i++; for (int j = 0; j < arrDr.Length; j++) { cel = (Excel.Range)(excel.Cells[DongBatDau + j + i + 1, 1]); cel.EntireRow.Insert(Excel.XlDirection.xlUp, null); excel.Cells[DongBatDau + i + j, 1] = j + 1; excel.Cells[DongBatDau + i + j, 2] = arrDr[j]["TenMonHoc"]; cel = excel.get_Range(excel.Cells[DongBatDau + i + j, 2], excel.Cells[DongBatDau + i + j, 4]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; excel.Cells[DongBatDau + i + j, 5] = arrDr[j]["TongTiet"]; cel = excel.get_Range(excel.Cells[DongBatDau + i + j, 5], excel.Cells[DongBatDau + i + j, 6]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.Cells[DongBatDau + i + j, 7] = arrDr[j]["HeSo"]; cel = excel.get_Range(excel.Cells[DongBatDau + i + j, 7], excel.Cells[DongBatDau + i + j, 8]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; cel.NumberFormat = "#,##0.00"; excel.Cells[DongBatDau + i + j, 9] = arrDr[j]["GioQuyChuan"]; cel = (Excel.Range)(excel.Cells[DongBatDau + i + j, 9]); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; cel.NumberFormat = "#,##0.00"; SoGioThucHien += double.Parse(arrDr[j]["GioQuyChuan"].ToString()); excel.Cells[DongBatDau + i + j, 10] = arrDr[j]["GhiChu"]; cel = excel.get_Range(excel.Cells[DongBatDau + i + j, 10], excel.Cells[DongBatDau + i + j, 11]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; cel = excel.get_Range(excel.Cells[DongBatDau + i + j, 1], excel.Cells[DongBatDau + i + j, 11]); cel.Borders.Value = 1; cel.Font.Bold = false; i++; } } arrDr = dtChiTiet.Select("NS_GiaoVienID = " + dr["NS_GiaoVienID"] + " And wType = 'GD'"); if (arrDr.Length > 0) { // Thêm dòng giảng dạy cel = (Excel.Range)(excel.Cells[DongBatDau + i + 1, 1]); cel.EntireRow.Insert(Excel.XlDirection.xlUp, null); excel.Cells[DongBatDau + i, 2] = "'- Giảng dạy"; cel = excel.get_Range(excel.Cells[DongBatDau + i, 2], excel.Cells[DongBatDau + i, 5]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; cel.Font.Bold = false; i++; // Thêm phần tiêu đề bảng cel = (Excel.Range)(excel.Cells[DongBatDau + i + 1, 1]); cel.EntireRow.Insert(Excel.XlDirection.xlUp, null); excel.Cells[DongBatDau + i, 1] = "TT"; excel.Cells[DongBatDau + i, 2] = "Môn học"; cel = excel.get_Range(excel.Cells[DongBatDau + i, 2], excel.Cells[DongBatDau + i, 3]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.Cells[DongBatDau + i, 4] = "Lớp"; cel = (Excel.Range)(excel.Cells[DongBatDau + i, 4]); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.Cells[DongBatDau + i, 5] = "LT"; cel = (Excel.Range)(excel.Cells[DongBatDau + i, 5]); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.Cells[DongBatDau + i, 6] = "LT chuẩn"; cel = (Excel.Range)(excel.Cells[DongBatDau + i, 6]); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.Cells[DongBatDau + i, 7] = "TH"; cel = (Excel.Range)(excel.Cells[DongBatDau + i, 7]); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.Cells[DongBatDau + i, 8] = "TH chuẩn"; cel = (Excel.Range)(excel.Cells[DongBatDau + i, 8]); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.Cells[DongBatDau + i, 9] = "Giờ quy chuẩn"; cel = (Excel.Range)(excel.Cells[DongBatDau + i, 9]); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; excel.Cells[DongBatDau + i, 10] = "Ghi chú"; cel = excel.get_Range(excel.Cells[DongBatDau + i, 10], excel.Cells[DongBatDau + i, 11]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; cel = excel.get_Range(excel.Cells[DongBatDau + i, 1], excel.Cells[DongBatDau + i, 11]); cel.Font.Bold = true; cel.RowHeight = 35.25; cel.Borders.Value = 1; i++; for (int j = 0; j < arrDr.Length; j++) { cel = (Excel.Range)(excel.Cells[DongBatDau + i + 1, 1]); cel.EntireRow.Insert(Excel.XlDirection.xlUp, null); excel.Cells[DongBatDau + i, 1] = j + 1; excel.Cells[DongBatDau + i, 2] = arrDr[j]["TenMonHoc"]; cel = excel.get_Range(excel.Cells[DongBatDau + i, 2], excel.Cells[DongBatDau + i, 3]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; excel.Cells[DongBatDau + i, 4] = arrDr[j]["TenLop"] + " (" + arrDr[j]["SoSinhVien"] + ")"; cel = (Excel.Range)(excel.Cells[DongBatDau + i, 4]); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; if (double.Parse("0" + arrDr[j]["LyThuyet"]) > 0) { excel.Cells[DongBatDau + i, 5] = arrDr[j]["LyThuyet"]; } cel = (Excel.Range)(excel.Cells[DongBatDau + i, 5]); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; cel = (Excel.Range)(excel.Cells[DongBatDau + i, 6]); if (double.Parse("0" + arrDr[j]["LyThuyetQuyChuan"]) > 0) { excel.Cells[DongBatDau + i, 6] = arrDr[j]["LyThuyetQuyChuan"]; cel.NumberFormat = "#,##0.00"; } cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; if (double.Parse("0" + arrDr[j]["ThucHanh"]) > 0) { excel.Cells[DongBatDau + i, 7] = arrDr[j]["ThucHanh"]; } cel = (Excel.Range)(excel.Cells[DongBatDau + i, 7]); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; cel = (Excel.Range)(excel.Cells[DongBatDau + i, 8]); if (double.Parse("0" + arrDr[j]["ThucHanhQuyChuan"]) > 0) { excel.Cells[DongBatDau + i, 8] = arrDr[j]["ThucHanhQuyChuan"]; cel.NumberFormat = "#,##0.00"; } cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; cel = (Excel.Range)(excel.Cells[DongBatDau + i, 9]); if (double.Parse("0" + arrDr[j]["GioQuyChuan"]) > 0) { excel.Cells[DongBatDau + i, 9] = arrDr[j]["GioQuyChuan"]; cel.NumberFormat = "#,##0.00"; } cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; SoGioThucHien += double.Parse(arrDr[j]["GioQuyChuan"].ToString()); excel.Cells[DongBatDau + i, 10] = arrDr[j]["GhiChu"]; cel = excel.get_Range(excel.Cells[DongBatDau + i, 10], excel.Cells[DongBatDau + i, 11]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; cel = excel.get_Range(excel.Cells[DongBatDau + i, 1], excel.Cells[DongBatDau + i, 11]); cel.Borders.Value = 1; cel.Font.Bold = false; i++; } } // Thêm dòng thừa thiếu giờ đối với giáo viên arrDr = dtDinhMuc.Select("IDNS_GiaoVien = " + dr["NS_GiaoVienID"]); string str = "Định mức: " + (arrDr.Length > 0 ? arrDr[0]["SoGioDinhMuc"] : "0") + ";"; str += " Thực hiện: " + SoGioThucHien.ToString() + ";"; SoGioThucHien = SoGioThucHien - (arrDr.Length > 0 ? double.Parse(arrDr[0]["SoGioDinhMuc"].ToString()) : 0); str += (SoGioThucHien >= 0 ? " Thừa: " + Math.Abs(SoGioThucHien).ToString() : " Thiếu: " + Math.Abs(SoGioThucHien).ToString()); cel = (Excel.Range)(excel.Cells[DongBatDau + i + 1, 1]); cel.EntireRow.Insert(Excel.XlDirection.xlUp, null); excel.Cells[DongBatDau + i, 2] = str; cel = excel.get_Range(excel.Cells[DongBatDau + i, 2], excel.Cells[DongBatDau + i, 11]); cel.Merge(null); cel.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; cel.Font.Bold = true; i++; } excel.Application.Workbooks[1].Save(); excel.Visible = true; //Process.Start(FileExcel); } catch (Exception e) { excel.Application.Workbooks[1].Save(); this.Cursor = System.Windows.Forms.Cursors.Default; excel.Application.Workbooks.Close(); excel.Application.Quit(); excel.Quit(); ThongBaoLoi("Xuất dữ liệu không thành công! Hãy đóng các file Excel trước khi xuất dữ liệu. Thông báo lỗi: " + e.Message); CloseWaitDialog(); this.Cursor = System.Windows.Forms.Cursors.Default; return; } finally { CloseWaitDialog(); this.Cursor = System.Windows.Forms.Cursors.Default; } }
private void btnXuatChiTiet_Click(object sender, EventArgs e) { if (dtSinhVien != null && dtSinhVien.Rows.Count > 0) { CreateWaitDialog("Đang xuất dữ liệu ra file Excel", "Xin vui lòng chờ!"); #region Xử lý table Lib.clsDataTableHelper clsTable = new Lib.clsDataTableHelper(); DataTable dtMonHoc = clsTable.SelectDistinct(dtChiTiet, new string[] { "SapXep", "XL_MonHocTrongKyID", "MaMonHoc", "TenMonHoc", "SoHocTrinh" }); foreach (DataRow dr in dtMonHoc.Rows) { if (!dtSinhVien.Columns.Contains("DT_" + dr["XL_MonHocTrongKyID"])) { dtSinhVien.Columns.Add("DT_" + dr["XL_MonHocTrongKyID"], typeof(double)); dtSinhVien.Columns.Add("DTK_" + dr["XL_MonHocTrongKyID"], typeof(double)); } } DataRow[] arrDr; foreach (DataRow dr in dtSinhVien.Rows) { arrDr = dtChiTiet.Select("SV_SinhVienID = " + dr["SV_SinhVienID"]); foreach (DataRow _dr in arrDr) { dr["DT_" + _dr["XL_MonHocTrongKyID"]] = _dr["DiemThi"]; dr["DTK_" + _dr["XL_MonHocTrongKyID"]] = _dr["DiemTongKetMon"]; } } #endregion #region Chuẩn bị tệp excel để ghi dữ liệu Workbook exBook = new Workbook(); exBook.Open(Application.StartupPath + "\\Template\\DanhSachXetHocBongChiTiet.xls", FileFormatType.Excel2003); _exSheet = exBook.Worksheets[0]; _range = _exSheet.Cells; #endregion SaveFileDialog sv = new SaveFileDialog(); sv.FileName = "DanhSachXetHocBongChiTiet -Lop" + cmbLop.Text.ToUpper() + "- Ky" + Program.HocKy.ToString() + " -Nam " + Program.NamHoc + ".xls"; sv.Filter = "Excel file (*.xls)|*.xls"; sv.Title = "Xuất dữ liệu"; if (sv.ShowDialog() == DialogResult.OK) { #region Đổ dữ liệu vào báo cáo int DongBatDau = 9, DongFieldName = 5, CotBatDau = 9; _range["B5"].PutValue("Xuất sắc:" + string.Format("{0:N2}%", float.Parse("0" + (float.Parse("0" + xuatsac) * 100 / dtChiTiet.Rows.Count))) + ". Giỏi:" + string.Format("{0:N2}%", float.Parse("0" + (float.Parse("0" + gioi) * 100 / dtChiTiet.Rows.Count).ToString())) + ". Khá:" + string.Format("{0:N2}%", float.Parse("0" + (float.Parse("0" + kha) * 100 / dtChiTiet.Rows.Count).ToString())) + ". TB Khá:" + string.Format("{0:N2}%", float.Parse("0" + (float.Parse("0" + TBKha) * 100 / dtChiTiet.Rows.Count).ToString())) + ". Trung bình:" + string.Format("{0:N2}%", float.Parse("0" + (float.Parse("0" + trungbinh) * 100 / dtChiTiet.Rows.Count))) + ". Kém:" + string.Format("{0:N2}%", float.Parse("0" + (float.Parse("0" + Kem) * 100 / dtChiTiet.Rows.Count).ToString()))); // Thay thế dữ liệu trên file Dictionary <string, string> DuLieuThayThe = GetDuLieuThayTheBaoCao(); DuLieuThayThe.Add("HocKy", Program.HocKy.ToString()); DuLieuThayThe.Add("NamHoc", Program.NamHoc); if (cmbLop.Text != "") { DuLieuThayThe.Add("LopHoc", "LỚP " + cmbLop.Text.ToUpper()); } else { DuLieuThayThe.Add("LopHoc", " "); } foreach (KeyValuePair <string, string> pair in DuLieuThayThe) { _exSheet.Replace(pair.Key, pair.Value); } Range rngCopy, rngPaste; // Thêm tiêu đề cho các cột điểm môn học //rngCopy = _exSheet.Cells.CreateRange(DongFieldName + 1, CotBatDau, 3, 2); //rngPaste = _exSheet.Cells.CreateRange(DongFieldName + 1, CotBatDau, 3, 2); if (cmbLop.Text != "") { } // Set tiều đề cho môn cuối cùng trước, sau đó insert ngược _range[DongFieldName, CotBatDau].PutValue("DT_" + dtMonHoc.Rows[dtMonHoc.Rows.Count - 1]["XL_MonHocTrongKyID"]); _range[DongFieldName, CotBatDau + 1].PutValue("DTK_" + dtMonHoc.Rows[dtMonHoc.Rows.Count - 1]["XL_MonHocTrongKyID"]); _range[DongFieldName + 1, CotBatDau].PutValue(dtMonHoc.Rows[dtMonHoc.Rows.Count - 1]["TenMonHoc"]); _exSheet.Cells.Merge(DongFieldName + 1, CotBatDau, 1, 2); _range[DongFieldName + 2, CotBatDau].PutValue((dtMonHoc.Rows.Count).ToString()); _exSheet.Cells.Merge(DongFieldName + 2, CotBatDau, 1, 2); int stt = 1; for (int i = dtMonHoc.Rows.Count - 2; i >= 0; i--) { _exSheet.Cells.InsertColumn(CotBatDau); _exSheet.Cells.InsertColumn(CotBatDau); //rngPaste.Copy(rngCopy); //rngPaste.CopyStyle(rngCopy); _range[DongFieldName, CotBatDau].PutValue("DT_" + dtMonHoc.Rows[i]["XL_MonHocTrongKyID"]); _range[DongFieldName, CotBatDau + 1].PutValue("DTK_" + dtMonHoc.Rows[i]["XL_MonHocTrongKyID"]); _range[DongFieldName + 1, CotBatDau].PutValue(dtMonHoc.Rows[i]["TenMonHoc"]); _exSheet.Cells.Merge(DongFieldName + 1, CotBatDau, 1, 2); _range[DongFieldName + 2, CotBatDau].PutValue((i + 1).ToString()); _exSheet.Cells.Merge(DongFieldName + 2, CotBatDau, 1, 2); _range[DongFieldName + 3, CotBatDau].PutValue("Điểm thi"); _range[DongFieldName + 3, CotBatDau + 1].PutValue("ĐTK"); stt++; } _exSheet.Cells.DeleteColumn(CotBatDau - 1); // Thêm các dòng và đưa style vào các dòng được thêm rngCopy = _exSheet.Cells.CreateRange(DongBatDau + 1, 0, 1, 100); int _count = dtSinhVien.Rows.Count, k, DongHienTai = DongBatDau; if (_count > 3) { _range.InsertRows(DongBatDau + 1, _count - 3); } else { _range.DeleteRows(DongBatDau + 1, 3 - _count); } for (int i = 1; i < _count - 1; i++) { rngPaste = _exSheet.Cells.CreateRange(DongBatDau + i, 0, 1, 100); rngPaste.Copy(rngCopy); rngPaste.CopyStyle(rngCopy); _exSheet.Cells.SetRowHeight(DongBatDau + i, rngCopy.RowHeight); } string _FieldName = ""; for (int i = 0; i < _count; i++) { k = 0; _FieldName = ("" + _exSheet.Cells[DongFieldName, 0].Value).Trim(); while (_FieldName != "") { if (dtSinhVien.Columns.Contains(_FieldName) || _FieldName.ToUpper() == "STT") { if (_FieldName.ToUpper() == "STT") { _range[DongHienTai, k].PutValue(i + 1); } else if (_FieldName.IndexOf("=") >= 0) { _range[DongHienTai, k].Formula = _FieldName.Replace("[i]", (DongBatDau + i + 1).ToString()).Replace("'", ""); } else { _range[DongHienTai, k].PutValue(dtSinhVien.Rows[i][_FieldName]); } } k++; _FieldName = ("" + _exSheet.Cells[DongFieldName, k].Value).Trim(); } _exSheet.AutoFitRow(DongHienTai); DongHienTai++; } // Xóa dòng field name _exSheet.Cells.DeleteRow(DongFieldName); #endregion #region Lưu và mở file excel #region Lưu và mở file excel string strTenFileExcelMoi = sv.FileName; exBook.Save(strTenFileExcelMoi, FileFormatType.Excel2003); CloseWaitDialog(); ThongBao("Xuất dữ liệu thành công."); try { Process.Start(strTenFileExcelMoi); } catch (Exception ex) { ThongBaoLoi("Đã có lỗi: " + ex.Message); } #endregion //if (!Directory.Exists(Program.DuongDanThuMucBaoCao)) // Directory.CreateDirectory(Program.DuongDanThuMucBaoCao); //string strTenFileExcelMoi = Program.DuongDanThuMucBaoCao + "\\" + string.Format("BaoCaoXetHocBongChiTiet_{0}.xls", DateTime.Now.ToString("yyyyMMdd")); //exBook.Save(strTenFileExcelMoi, FileFormatType.Excel2003); //CloseWaitDialog(); #endregion } else { ThongBao("Không có dữ liệu để xuất Excel."); } } }
private void GetKeHoachThucHanhLop() { dtToanTruong = new cBDM_Lop().GetKeHoachToanTruong(Program.IDNamHoc, Program.NamHoc, new DM_LopInfo()); dtKeHoach = oBXL_KeHoachThucHanh.GetByNamHoc(Program.IDNamHoc, Program.HocKy); DataTable dtChiTiet = oBXL_KeHoachThucHanhChiTiet.GetByNamHocHocKy(Program.IDNamHoc, Program.HocKy); DataTable dtLop = clsdt.SelectDistinct(dtKeHoach, new string[] { "IDDM_He", "IDDM_TrinhDo", "TenKhoaHoc", "IDDM_Lop", "TenLop", "SoSinhVien" }); string[] arrCaHoc = new string[] { "Sáng", "Chiều", "Tối" }; if (fg.Rows.Count > RowBegin + 1) { fg.Select(RowBegin, 4); fg.Rows.RemoveRange(RowBegin + 1, fg.Rows.Count - RowBegin - 1); } int row = RowBegin, rowDangXet = RowBegin, colDangXet = ColBegin, CaHocDangXet, ToDangXet; foreach (DataRow dr in dtLop.Rows) { // Hiển thị kế hoạch lớp fg.Rows.Add(1); row++; // Gán các giá trị cho cell fix fg.Rows[row].Height = RowHeight; fg[row, 0] = ""; fg[row, 1] = "Kế hoạch lớp"; fg[row, 2] = "Kế hoạch lớp"; fg[row, 3] = "Kế hoạch lớp"; fg[row, 4] = int.Parse(dr["IDDM_Lop"].ToString()); fg.Rows[row].AllowMerging = true; CellRange rg = fg.GetCellRange(row, 0, row, fg.Cols.Fixed - 1); rg.Style = fg.Styles["MyCellStyleLop"]; FillKeHoachLop(row, int.Parse(dr["IDDM_Lop"].ToString())); foreach (string CaHoc in arrCaHoc) { for (int i = 1; i <= 2; i++) { // Thêm 1 row vào grid và tăng giá trị của row hiện tại lên 1 fg.Rows.Add(1); row++; // Gán các giá trị cho cell fix fg.Rows[row].Height = RowHeight; fg[row, 0] = dr["TenLop"].ToString(); fg[row, 1] = int.Parse(dr["SoSinhVien"].ToString()); fg[row, 2] = CaHoc; fg[row, 3] = EncodeString(i.ToString(), row); fg[row, 4] = int.Parse(dr["IDDM_Lop"].ToString()); fg.Rows[row].AllowMerging = true; } } // Thực hiện tìm các kế hoạch chi tiết thực hành của lớp đang xét để fill lên grid DataView dv = new DataView(dtChiTiet); dv.RowFilter = "IDDM_Lop = " + dr["IDDM_Lop"]; if (dv.Count > 0) { CaHocDangXet = -1; ToDangXet = -1; foreach (DataRowView drv in dv) { if (int.Parse(drv["CaHoc"].ToString()) != CaHocDangXet || int.Parse(drv["ToThucHanh"].ToString()) != ToDangXet) { CaHocDangXet = int.Parse(drv["CaHoc"].ToString()); ToDangXet = int.Parse(drv["ToThucHanh"].ToString()); colDangXet = ColBegin; if (CaHocDangXet == 0) { if (ToDangXet != 2) { rowDangXet = row - 5; } else { rowDangXet = row - 4; } } else if (CaHocDangXet == 1) { if (ToDangXet != 2) { rowDangXet = row - 3; } else { rowDangXet = row - 2; } } else { if (ToDangXet != 2) { rowDangXet = row - 1; } else { rowDangXet = row; } } } while (colDangXet < fg.Cols.Count) { if (StringToDate(fg[6, colDangXet].ToString().Substring(2)).Date == ((DateTime)drv["NgayThucHanh"]).Date) { break; } colDangXet++; } if (ToDangXet == 0) { SetCellKeHoach(rowDangXet, colDangXet, int.Parse(drv["XL_KeHoachThucHanhChiTietID"].ToString()), int.Parse(drv["IDXL_KeHoachThucHanh"].ToString()), int.Parse(drv["IDDM_MonHoc"].ToString()), int.Parse(drv["IDXL_KeHoachThucHanhKyHieu"].ToString()), drv["KyHieu"].ToString(), int.Parse("0" + drv["IDNS_GiaoVien"]), "" + drv["TenGiaoVien"], "" + drv["TenVietTat"], int.Parse("0" + drv["IDDM_PhongHoc"]), "" + drv["TenPhongHoc"], (CA_HOC)drv["CaHoc"], ToDangXet, (DateTime)drv["NgayThucHanh"]); SetCellKeHoach(rowDangXet + 1, colDangXet, int.Parse(drv["XL_KeHoachThucHanhChiTietID"].ToString()), int.Parse(drv["IDXL_KeHoachThucHanh"].ToString()), int.Parse(drv["IDDM_MonHoc"].ToString()), int.Parse(drv["IDXL_KeHoachThucHanhKyHieu"].ToString()), drv["KyHieu"].ToString(), int.Parse("0" + drv["IDNS_GiaoVien"]), "" + drv["TenGiaoVien"], "" + drv["TenVietTat"], int.Parse("0" + drv["IDDM_PhongHoc"]), "" + drv["TenPhongHoc"], (CA_HOC)drv["CaHoc"], ToDangXet, (DateTime)drv["NgayThucHanh"]); } else { SetCellKeHoach(rowDangXet, colDangXet, int.Parse(drv["XL_KeHoachThucHanhChiTietID"].ToString()), int.Parse(drv["IDXL_KeHoachThucHanh"].ToString()), int.Parse(drv["IDDM_MonHoc"].ToString()), int.Parse(drv["IDXL_KeHoachThucHanhKyHieu"].ToString()), drv["KyHieu"].ToString(), int.Parse("0" + drv["IDNS_GiaoVien"]), "" + drv["TenGiaoVien"], "" + drv["TenVietTat"], int.Parse("0" + drv["IDDM_PhongHoc"]), "" + drv["TenPhongHoc"], (CA_HOC)drv["CaHoc"], ToDangXet, (DateTime)drv["NgayThucHanh"]); } } } } for (int i = ColBegin; i < fg.Cols.Count; i++) { fg.Cols[i].AllowMerging = true; } SetGridCaption("LOP"); }