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
{
}
}