private void gridView1_CustomUnboundColumnData(object sender, DevExpress.XtraGrid.Views.Base.CustomColumnDataEventArgs e) { studentReceivableDAO dt = new studentReceivableDAO(); PreferredDAO dc = new PreferredDAO(); int rowindex = e.ListSourceRowIndex; decimal a = 0; if (e.Column.FieldName != "miengiam") { return; } int perferredID = dt.lookforPreferredID(ClassStudentDAO.StudentID); if (perferredID == null) { e.Value = 0; } List <string> b = new List <string>(); string mg = gridView1.GetListSourceRowCellValue(rowindex, "PreferredID").ToString(); decimal f = Convert.ToDecimal(gridView1.GetListSourceRowCellValue(rowindex, "TotalPriceDetail")); for (int i = 0; i < (mg.Length - 1); i += 2) { string c = mg.Substring(i, 1); b.Add(c); } if (b.Count == 0) { e.Value = f; } else { foreach (var i in b) { if (int.Parse(i) == perferredID) { float pr = dc.lookPreferredPercent(int.Parse(i)); a = f - ((f * (decimal)pr) / 100); e.Value = a; break; } e.Value = f; } } }
private void export() { try { #region ===========khởi tạo excel===== //khởi tạo excell Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); //khởi tạo workbook Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); //khởi tọa worksheet Microsoft.Office.Interop.Excel._Worksheet worksheet = null; worksheet = workbook.Sheets["Sheet1"]; worksheet = workbook.ActiveSheet; worksheet.Name = "Danh sách hoc sinh"; app.Visible = true;//cho hiển thị excel #endregion ===========khởi tạo excel========== #region ===========đổ dữ liệu vào sheet====== worksheet.Cells[1, 1] = "SỞ GIÁO DỤC VÀ ĐÀO TẠO HÀ NỘI"; worksheet.Cells[2, 1] = "TRƯỜNG MẦM NON HOA LINH"; worksheet.Cells[4, 1] = "DANH SÁCH HỌC SINH"; worksheet.Cells[5, 1] = "Năm học:" + cbbNamhoc.Text; worksheet.Cells[6, 1] = "Khối:" + cbbKhoihoc.Text; worksheet.Cells[7, 1] = "Lớp:" + cbbLophoc.Text; worksheet.Cells[9, 1] = "STT"; worksheet.Cells[9, 2] = "Mã học sinh"; worksheet.Cells[9, 3] = "Họ"; worksheet.Cells[9, 4] = "Tên"; worksheet.Cells[9, 5] = "Ngày sinh"; worksheet.Cells[9, 6] = "Giới tính"; worksheet.Cells[9, 7] = "Địa chỉ"; worksheet.Cells[9, 8] = "Tình trạng"; worksheet.Cells[9, 9] = "Tổng thu"; //duyệt dết các dòng trong trong gridcontrol for (int i = 0; i < gridView1.RowCount; i++) { #region ==== tinh tien hoc tung hoc sinh====== ReceivableDetail_StudentDAO dt = new ReceivableDetail_StudentDAO(); ReceivableDetailDAO dc = new ReceivableDetailDAO(); List <ReceivableDetail_Student> a = new List <ReceivableDetail_Student>(); List <ReceivableDetail> b = new List <ReceivableDetail>(); decimal tong = 0; a = dt.ListReceivableDetail_Student((int)gridView1.GetRowCellValue(i, gridView1.Columns["StudentID"])); foreach (var j in a) { ReceivableDetail c = new ReceivableDetail(); c = dc.ReceivableDetaiByStudenID(j.ReceivableDetailID, (int)cbbDotthu.SelectedValue); if (c != null) { tong += (decimal)c.TotalPriceDetail; b.Add(c); } } foreach (var j in b) { string mg = j.PreferredID; List <string> b1 = new List <string>(); int perferredID = (int)gridView1.GetRowCellValue(i, gridView1.Columns["PreferredID"]); for (int sj = 0; sj < (mg.Length - 1); sj += 2) { string c = mg.Substring(sj, 1); b1.Add(c); } if (b1.Count == 0) { worksheet.Cells[10 + i, 9] = tong; } else { foreach (var k in b1) { if (int.Parse(k) == perferredID) { PreferredDAO dv = new PreferredDAO(); float pr = dv.lookPreferredPercent(perferredID); tong = tong - (((decimal)j.TotalPriceDetail * (decimal)pr) / 100); worksheet.Cells[10 + i, 9] = tong; break; } worksheet.Cells[10 + i, 9] = tong; } } } // grDanhsachkhoanthu.DataSource = b; #endregion ==== tinh tien hoc tung hoc sinh==== #region ---- thong tin hoc sinh---- worksheet.Cells[10 + i, 1] = i + 1; worksheet.Cells[10 + i, 2] = gridView1.GetRowCellValue(i, gridView1.Columns["StudentCode"]); worksheet.Cells[10 + i, 3] = gridView1.GetRowCellValue(i, gridView1.Columns["FirstName"]); worksheet.Cells[10 + i, 4] = gridView1.GetRowCellValue(i, gridView1.Columns["LastName"]); worksheet.Cells[10 + i, 5] = gridView1.GetRowCellValue(i, gridView1.Columns["Birthday"]); if ((bool)gridView1.GetRowCellValue(i, gridView1.Columns["Gender"]) == true) { worksheet.Cells[10 + i, 6] = "Nam"; } else { worksheet.Cells[10 + i, 6] = "Nữ"; } //worksheet.Cells[10 + i, 6] = gridView1.GetRowCellValue(i, gridView1.Columns["Gender"]); worksheet.Cells[10 + i, 7] = gridView1.GetRowCellValue(i, gridView1.Columns["AdressDetail"]); worksheet.Cells[10 + i, 8] = gridView1.GetRowCellValue(i, gridView1.Columns["tinhtrang"]); #endregion ---thong tin hoc sinh------ } int dongData = gridView1.RowCount; worksheet.Cells[dongData + 13, 8] = "Hà Nội, ngày tháng năm . "; worksheet.Cells[dongData + 14, 8] = "HIỆU TRƯỞNG. "; #endregion ============đổ dữ liệu vào sheet======= #region =====căn chỉnh====== //định dạng trang worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait; // Giấy dọc worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4; // Loại giấy A4 worksheet.PageSetup.LeftMargin = 0; //can le trai worksheet.PageSetup.TopMargin = 0; worksheet.PageSetup.RightMargin = 0; worksheet.PageSetup.BottomMargin = 0; worksheet.PageSetup.CenterHorizontally = true; //can giua theo chieu ngang //dinh dang cot worksheet.Range["A1"].ColumnWidth = 3; worksheet.Range["B1"].ColumnWidth = 13; worksheet.Range["C1"].ColumnWidth = 13; worksheet.Range["D1"].ColumnWidth = 8; worksheet.Range["E1"].ColumnWidth = 13; worksheet.Range["F1"].ColumnWidth = 10; worksheet.Range["G1"].ColumnWidth = 28; worksheet.Range["H1"].ColumnWidth = 14; worksheet.Range["I1"].ColumnWidth = 11; worksheet.Range["J1"].ColumnWidth = 14; worksheet.Range["K1"].ColumnWidth = 11; //dinh dang font chu worksheet.Range["A1", "K100"].Font.Name = "Times New Roman"; worksheet.Range["A1", "K100"].Font.Size = 10; // size cho font chữ worksheet.Range["A4", "K4"].Font.Size = 12; // Size tiêu đề lớn hơn chút worksheet.Range["A1", "K2"].Font.Size = 16; // Size tiêu đề lớn hơn chút worksheet.Range["A5", "K7"].Font.Size = 12; worksheet.Range["A1", "I1"].MergeCells = true; // Nhập dòng tiêu đề worksheet.Range["A2", "I2"].MergeCells = true; worksheet.Range["A4", "I4"].MergeCells = true; worksheet.Range["A5", "I5"].MergeCells = true; worksheet.Range["A6", "I6"].MergeCells = true; worksheet.Range["A7", "I7"].MergeCells = true; worksheet.Range["A1", "K7"].Font.Bold = true; //to dam tieu de worksheet.Range["A9", "K9"].Font.Bold = true; //to dam ten cot worksheet.Range["A9", "I" + (dongData + 9)].Borders.LineStyle = 1; //ke vien bang worksheet.Range["A3", "A9"].HorizontalAlignment = 3; // Căn giữa tiêu đề bảng worksheet.Range["A8", "K8"].HorizontalAlignment = 3; // Tiêu đề cột bảng căn giữa worksheet.Range["A4", "K4"].HorizontalAlignment = 3; worksheet.Range["A5", "K5"].HorizontalAlignment = 3; worksheet.Range["A6", "K6"].HorizontalAlignment = 3; #endregion ====căn chỉnh===== } catch { } }