public void NhapDuLieu() { OpenFileDialog openFile = new OpenFileDialog(); openFile.Filter = "Excel file (*.xls; *.xlsx; *.xlsm; *.xlsb)|*.xls"; openFile.ShowDialog(); if (openFile.FileName == String.Empty) return; Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; Excel.Worksheet sheetDocGia, sheetHocSinh, sheetNhanVien, sheetViPham; Excel.Worksheet sheetTuaSach, sheetDauSach, sheetCuonSach; Excel.Worksheet sheetMuonSach; Excel.Worksheet sheetDkChoMuon; Excel.Range rangeDocGia; string str; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(openFile.FileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); sheetDocGia = xlWorkBook.Sheets["Độc giả"] as Excel.Worksheet; sheetHocSinh = xlWorkBook.Sheets["Học sinh"] as Excel.Worksheet; sheetNhanVien = xlWorkBook.Sheets["Nhân viên"] as Excel.Worksheet; sheetViPham = xlWorkBook.Sheets["Vi phạm"] as Excel.Worksheet; sheetTuaSach = xlWorkBook.Sheets["Tựa sách"] as Excel.Worksheet; sheetDauSach = xlWorkBook.Sheets["Đầu sách"] as Excel.Worksheet; sheetCuonSach = xlWorkBook.Sheets["Cuốn sách"] as Excel.Worksheet; sheetMuonSach = xlWorkBook.Sheets["Mượn sách"] as Excel.Worksheet; sheetDkChoMuon = xlWorkBook.Sheets["ĐK chờ mượn"] as Excel.Worksheet; //sheetDocGia = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); /*sheetHocSinh = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2); sheetNhanVien = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(3); sheetViPham = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(4); sheetTuaSach = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(5); sheetDauSach = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(6); sheetCuonSach = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(7); sheetMuonSach = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(8); sheetDkChoMuon = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(9);*/ //range = xlWorkSheet.UsedRange; //for (int rCnt = 1; rCnt <= range.Rows.Count; rCnt++) //{ // for (int cCnt = 1; cCnt <= range.Columns.Count; cCnt++) // { // str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2; // MessageBox.Show(str); // } //} //rangeDocGia = sheetDocGia.UsedRange; //if ((sheetDocGia.Cells[2, 6] as Excel.Range).Value == false) // MessageBox.Show("false"); //MessageBox.Show(Convert.ToString((sheetDocGia.Cells[2,6] as Excel.Range).Value)); DangKyCho dataDkCho = new DangKyCho(); MuonTraSach dataMuonSach = new MuonTraSach(); Sach dataSach = new Sach(); DocGia dataDocGia = new DocGia(); ViPham dataViPham = new ViPham(); dataDkCho.xoaTatCa(); dataMuonSach.xoaTatCa(); dataSach.xoaTatCa(); dataViPham.xoaTatCa(); dataDocGia.xoaTatCa(); //Excel.Range rangeDkChoMuon = sheetDkChoMuon.UsedRange; DOCGIA dg; for (int i = 2; i <= sheetDocGia.UsedRange.Rows.Count; i++) { dg = new DOCGIA(); dg.madocgia = Convert.ToString((sheetDocGia.Cells[i, 1] as Excel.Range).Value); dg.hoten = Convert.ToString((sheetDocGia.Cells[i, 2] as Excel.Range).Value); dg.gioitinh = Convert.ToString((sheetDocGia.Cells[i, 3] as Excel.Range).Value); String date = Convert.ToString((sheetDocGia.Cells[i, 4] as Excel.Range).Value); if (date != null) dg.ngaysinh = DateTime.Parse(date); String date1 = Convert.ToString((sheetDocGia.Cells[i, 5] as Excel.Range).Value); if (date1 != null) dg.ngaylap = DateTime.Parse(date1); dg.tinhtrang = (bool)(sheetDocGia.Cells[i, 6] as Excel.Range).Value; dataDocGia.themDocGia(dg); } HOCSINH hs; for (int i = 2; i <= sheetHocSinh.UsedRange.Rows.Count; i++) { hs = new HOCSINH(); hs.madocgia = Convert.ToString((sheetHocSinh.Cells[i, 1] as Excel.Range).Value); hs.lop = Convert.ToString((sheetHocSinh.Cells[i, 2] as Excel.Range).Value); dataDocGia.themHocSinh(hs); } NHANVIEN nv; for (int i = 2; i <= sheetNhanVien.UsedRange.Rows.Count; i++) { nv = new NHANVIEN(); nv.madocgia = Convert.ToString((sheetNhanVien.Cells[i, 1] as Excel.Range).Value); dataDocGia.themNhanVien(nv); } VIPHAM vp; for (int i = 2; i <= sheetViPham.UsedRange.Rows.Count; i++) { vp = new VIPHAM(); vp.madocgia = Convert.ToString((sheetViPham.Cells[i, 1] as Excel.Range).Value); vp.vipham1 = Convert.ToInt32((sheetViPham.Cells[i, 2] as Excel.Range).Value); String date = Convert.ToString((sheetViPham.Cells[i, 3] as Excel.Range).Value); if (date != null) vp.ngayhethan = DateTime.Parse(date); dataViPham.themViPham(vp); } TUASACH ts; for (int i = 2; i <= sheetTuaSach.UsedRange.Rows.Count; i++) { ts = new TUASACH(); ts.matuasach = Convert.ToString((sheetTuaSach.Cells[i, 1] as Excel.Range).Value); ts.tentuasach = Convert.ToString((sheetTuaSach.Cells[i, 2] as Excel.Range).Value); ts.tacgia = Convert.ToString((sheetTuaSach.Cells[i, 3] as Excel.Range).Value); ts.gioithieu = Convert.ToString((sheetTuaSach.Cells[i, 4] as Excel.Range).Value); dataSach.themTuaSach(ts); } DAUSACH ds; for (int i = 2; i <= sheetDauSach.UsedRange.Rows.Count; i++) { ds = new DAUSACH(); ds.madausach = Convert.ToString((sheetDauSach.Cells[i, 1] as Excel.Range).Value); ds.matuasach = Convert.ToString((sheetDauSach.Cells[i, 2] as Excel.Range).Value); ds.ngonngu = Convert.ToString((sheetDauSach.Cells[i, 3] as Excel.Range).Value); ds.tinhtrang = (bool)(sheetDauSach.Cells[i, 4] as Excel.Range).Value; dataSach.themDauSach(ds); } CUONSACH cs; for (int i = 2; i <= sheetCuonSach.UsedRange.Rows.Count; i++) { cs = new CUONSACH(); cs.macuonsach = Convert.ToString((sheetCuonSach.Cells[i, 1] as Excel.Range).Value); cs.madausach = Convert.ToString((sheetCuonSach.Cells[i, 2] as Excel.Range).Value); cs.tinhtrang = (bool)(sheetCuonSach.Cells[i, 3] as Excel.Range).Value; dataSach.themCuonSach(cs); } DKCHOMUON dk; for (int i = 2; i <= sheetDkChoMuon.UsedRange.Rows.Count; i++) { dk = new DKCHOMUON(); dk.madocgia = Convert.ToString((sheetDkChoMuon.Cells[i, 1] as Excel.Range).Value); dk.madausach = Convert.ToString((sheetDkChoMuon.Cells[i, 2] as Excel.Range).Value); String date = Convert.ToString((sheetDkChoMuon.Cells[i, 3] as Excel.Range).Value); if (date != null) dk.ngaygiodk = DateTime.Parse(date); dk.tinhtrang = (bool)(sheetDkChoMuon.Cells[i, 4] as Excel.Range).Value; dataDkCho.themDangKyCho(dk); } MUONSACH ms; for (int i = 2; i <= sheetMuonSach.UsedRange.Rows.Count; i++) { ms = new MUONSACH(); ms.madocgia = Convert.ToString((sheetMuonSach.Cells[i, 1] as Excel.Range).Value); ms.macuonsach = Convert.ToString((sheetMuonSach.Cells[i, 2] as Excel.Range).Value); String date = Convert.ToString((sheetMuonSach.Cells[i, 3] as Excel.Range).Value); if (date != null) ms.ngaygiomuon = DateTime.Parse(date); String date1 = Convert.ToString((sheetMuonSach.Cells[i, 4] as Excel.Range).Value); if (date1 != null) ms.ngayhethan = DateTime.Parse(date1); String date2 = Convert.ToString((sheetMuonSach.Cells[i, 5] as Excel.Range).Value); if (date2 != null) ms.ngaygiotra = DateTime.Parse(date2); dataMuonSach.themMuonTraSach(ms); } xlWorkBook.Close(true, null, null); xlApp.Quit(); MessageBox.Show("Hoàn thành!"); noticeImport(); //releaseObject(xlWorkSheet); //releaseObject(xlWorkBook); //releaseObject(xlApp); }
//cập nhật bảng vi phạm void updateViPham(string madocgia) { ViPham dataViPham = new ViPham(); ThamSo dataThamSo = new ThamSo(); VIPHAM inf = new VIPHAM(); THAMSO songaykhoathe; THAMSO solantrehen = new THAMSO(); inf.madocgia = madocgia; inf = dataViPham.timViPham(inf); solantrehen = new THAMSO(); solantrehen.tenthamso = "solantrehen"; solantrehen = dataThamSo.timThamSo(solantrehen); inf.vipham1++; if (solantrehen.tinhtrang == true) { if (inf.vipham1 % Convert.ToInt32(solantrehen.giatri) == 0) { khoaThe(madocgia); songaykhoathe = new THAMSO(); songaykhoathe.tenthamso = "songaykhoathe"; songaykhoathe = dataThamSo.timThamSo(songaykhoathe); inf.ngayhethan = DateTime.Now.Date.AddDays(Convert.ToInt32(songaykhoathe.giatri)); } } dataViPham.suaViPham(inf); //updateDocGia(); dgvdocgia.DataSource = dataDocGia.loadDocGia(); dgvhocsinh.DataSource = dataDocGia.loadHocSinh(); dgvnhanvien.DataSource = dataDocGia.loadNhanVien(); dgvvipham.DataSource = dataViPham.loadViPham(); }
private void btndelalldg_Click(object sender, EventArgs e) { try { DialogResult delete = MessageBox.Show("Bạn có thực muốn xóa hết dữ liệu này?", "Exit", MessageBoxButtons.YesNo); if (delete == DialogResult.No) return; dataDocGia.xoaTatCa(); ViPham dataViPham = new ViPham(); dataViPham.xoaTatCa(); updateDgvDocGia(); } catch (SqlException) { MessageBox.Show("Có ràng buộc dữ liệu, không thể xóa!"); } }
//load lại DataGridView vi phạm void updateDgvViPham() { ViPham dataViPham = new ViPham(); dgvvipham.DataSource = dataViPham.loadViPham(); }
//load lại DataGridView độc giả void updateDgvDocGia() { ViPham dataViPham = new ViPham(); dgvdocgia.DataSource = dataDocGia.loadDocGia(); dgvhocsinh.DataSource = dataDocGia.loadHocSinh(); dgvnhanvien.DataSource = dataDocGia.loadNhanVien(); dgvvipham.DataSource = dataViPham.loadViPham(); //dgvdocgia.Columns[0].ValueType = typeof(Int32); //dgvdocgia.Sort(dgvdocgia.Columns[0], System.ComponentModel.ListSortDirection.Ascending); }
//kiểm tra phục hồi thẻ độc giả đã gia hạn void ktPhucHoiThe() { ViPham dataViPham = new ViPham(); ThamSo dataThamSo = new ThamSo(); DataTable dt = dataDocGia.loadDocGia(); THAMSO handungthe = new THAMSO(); handungthe.tenthamso = "handungthe"; handungthe = dataThamSo.timThamSo(handungthe); DateTime ngaydk; DateTime ngayhethan; DOCGIA dg; DataTable vp; for (int i = 0; i < dt.Rows.Count; i++) { vp = dataViPham.timViPham(dt.Rows[i]["Mã độc giả"].ToString()); ngaydk = new DateTime(); ngaydk = (DateTime)dt.Rows[i]["Ngày lập"]; ngayhethan = new DateTime(); ngayhethan = ngaydk.AddMonths(Convert.ToInt32(handungthe.giatri)); if (ngayhethan.Date >= DateTime.Now.Date && (vp.Rows[0]["ngayhethan"].ToString() == String.Empty || (DateTime)vp.Rows[0]["ngayhethan"] < DateTime.Now)) //mở thẻ { //dg = new DOCGIA(); string madocgia = dt.Rows[i]["Mã độc giả"].ToString(); //dg.madocgia = dt.Rows[i]["Mã độc giả"].ToString(); dg = dataDocGia.timDocGia(madocgia); if (dg.tinhtrang != true) { dg.tinhtrang = true; dataDocGia.suaDocGia(dg); } } } }
//kiểm tra phục hồi thẻ độc giả hết hạn phạt void ktHetHanPhat() { ViPham dataViPham = new ViPham(); DataTable dt = dataViPham.loadViPham(); DateTime ngayhethan; DOCGIA dg; VIPHAM vp; for (int i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i]["Ngày hết hạn phạt"].ToString() != String.Empty) { ngayhethan = new DateTime(); ngayhethan = (DateTime)dt.Rows[i]["Ngày hết hạn phạt"]; if (ngayhethan.Date < DateTime.Now.Date) { //dg = new DOCGIA(); string madocgia = dt.Rows[i]["Mã độc giả"].ToString(); //dg.madocgia = dt.Rows[i]["Mã độc giả"].ToString(); dg = dataDocGia.timDocGia(madocgia); dg.tinhtrang = true; dataDocGia.suaDocGia(dg); vp = new VIPHAM(); vp.madocgia = dt.Rows[i]["Mã độc giả"].ToString(); vp = dataViPham.timViPham(vp); vp.ngayhethan = null; dataViPham.suaViPham(vp); } } } }
private void btnxoadg_Click(object sender, EventArgs e) { try { DialogResult delete = MessageBox.Show("Do you really want to delete?", "Exit", MessageBoxButtons.YesNo); if (delete == DialogResult.No) return; if (tcdocgia.SelectedTabIndex == 0)//xóa theo tab độc giả { if (dgvdocgia.SelectedRows.Count == 0) return; string madocgia = dgvdocgia.SelectedRows[0].Cells[0].Value.ToString(); dataDocGia.xoaDocGia(madocgia); dataDocGia.xoaNhanVien(madocgia); dataDocGia.xoaHocSinh(madocgia); ViPham dataViPham = new ViPham(); dataViPham.xoaViPham(madocgia); updateDgvDocGia(); } else if (tcdocgia.SelectedTabIndex == 1)//xóa theo tab học sinh { if (dgvhocsinh.SelectedRows.Count == 0) return; string madocgia = dgvhocsinh.SelectedRows[0].Cells[0].Value.ToString(); dataDocGia.xoaDocGia(madocgia); dataDocGia.xoaHocSinh(madocgia); ViPham dataViPham = new ViPham(); dataViPham.xoaViPham(madocgia); updateDgvDocGia(); } else if (tcdocgia.SelectedTabIndex == 2)//xóa theo tab nhân viên { if (dgvnhanvien.SelectedRows.Count == 0) return; string madocgia = dgvnhanvien.SelectedRows[0].Cells[0].Value.ToString(); dataDocGia.xoaDocGia(madocgia); dataDocGia.xoaNhanVien(madocgia); ViPham dataViPham = new ViPham(); dataViPham.xoaViPham(madocgia); updateDgvDocGia(); } } catch (SqlException) { MessageBox.Show("Có liên kết dữ liệu, không thể xóa!"); } /*catch (System.Data.Entity.Infrastructure.DbUpdateException) { MessageBox.Show("Có liên kết dữ liệu, không thể xóa!"); }*/ }