private void btnPhanBoChiTiet_Click(object sender, EventArgs e)
{
if (IDKQHT_ChuongTrinhDaoTao > 0)
{
Lib.clsExportToWord cls = new Lib.clsExportToWord();
DataTable dt = oBXL_MonHocTrongKy.GetMonKyToanKhoaByLop(IDDM_Lop);
if (dt.Rows.Count > 0)
{
Microsoft.Office.Interop.Word.ApplicationClass WordApp;
CreateWaitDialog("Xuất dữ liệu", "Đang xuất dữ liệu ra file");
try
{
WordApp = new Microsoft.Office.Interop.Word.ApplicationClass();
Microsoft.Office.Interop.Word.Document aDoc = null;
cls.InitWord(WordApp, ref aDoc, 13);
DataTable dtNamKy = new Lib.clsDataTableHelper().SelectDistinct(dt, new string[] { "TenNamHoc", "HocKy" });
Dictionary <int, float> colWidth = new Dictionary <int, float>();
colWidth.Add(1, 7);
for (int i = 2; i <= 5; i++)
{
colWidth.Add(i, 2);
}
foreach (DataRow dr in dtNamKy.Rows)
{
DataView dv = new DataView(dt);
dv.RowFilter = "TenNamHoc = '" + dr["TenNamHoc"] + "' And HocKy = " + dr["HocKy"];
cls.AddText(aDoc, "\tCác môn học trong học kỳ " + dr["HocKy"] + " năm học " + dr["TenNamHoc"], 0, 0, Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphLeft);
//cls.AddTable(aDoc, dv.ToTable(), new string[] { "Tên môn học", "Số học trình", "Số tiết", "Lý thuyết", "Thực hành" },
// new string[] { "TenMonHoc", "SoHocTrinh", "SoTiet", "LyThuyet", "ThucHanh" });
cls.AddTable(WordApp, aDoc, dv.ToTable(), new string[] { "Tên môn học", "Số học trình", "Số tiết", "Lý thuyết", "Thực hành" },
new string[] { "TenMonHoc", "SoHocTrinh", "SoTiet", "LyThuyet", "ThucHanh" }, colWidth);
cls.AddText(aDoc, "", 0, 0, Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphLeft);
}
WordApp.Visible = true;
CloseWaitDialog();
}
catch (Exception ex)
{
CloseWaitDialog();
ThongBaoLoi("File word đang được mở. Đề nghị đóng file này trước khi xuất dữ liệu! Thông báo lỗi: " + ex.Message);
return;
}
}
else
{
ThongBao("Lớp này chưa được phân bổ các môn trong kỳ.");
}
}
else
{
ThongBao("Lớp này chưa có chương trình đào tạo");
}
}
public frmKeHoachThucHanh()
{
InitializeComponent();
oBXL_KeHoachThucHanh = new cBXL_KeHoachThucHanh();
oBXL_KeHoachThucHanhChiTiet = new cBXL_KeHoachThucHanhChiTiet();
pXL_KeHoachThucHanhChiTietInfo = new XL_KeHoachThucHanhChiTietInfo();
oBDM_PhongHoc = new cBDM_PhongHoc();
clsdt = new Lib.clsDataTableHelper();
}
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.");
}
}
}
