private void UC_ThuGocLai_Load(object sender, EventArgs e) { repositoryItemComboBox1.Items.Clear(); foreach (var i in _ciftk) { repositoryItemComboBox1.Items.Add(string.Format(@"{0:###-##-##-######-#}", i.ACCTNO)); } repositoryItemComboBox1.Items.Add(@"Tiền Mặt"); foreach (var i in _tp) { var tk = _ciftk.FirstOrDefault(c => c.DDCTYP == i.tiente); string ptra = ""; if (tk != null) { ptra = string.Format(@"{0:###-##-##-######-#}", tk.ACCTNO); } else { ptra = @"Tiền Mặt"; } _inthulai.Add(new ThuGocLai() { Cif = i.socif, TenKh = i.khachhang, LaiCongDon = i.laicongdon ?? 0, LaiTraCham = i.laitracham ?? 0, SoTienPhaiTra = (i.laicongdon ?? 0) + (i.laitracham ?? 0), PhuongThucTra = ptra, tkvay = string.Format(@"{0:###-##-##-######-#}", i.taikhoan), GhiChu = @"Ngày tính lãi " + i.denngay.Value.ToShortDateString(), LoaiTien = i.tiente, DuNo = i.duno ?? 0 }); } GC_TD_Pyctl_ThuGocLai.DataSource = new BindingSource(_inthulai, ""); GV_TD_Pyctl_ThuGocLai.OptionsView.BestFitMaxRowCount = -1; GV_TD_Pyctl_ThuGocLai.BestFitColumns(); }
private void bt_td_pyctl_thugoclai_xuatexcel_Click(object sender, EventArgs e) { var pathToFile = Path.Combine(_clickOnceLocation, @"thugoclai.xlsx"); SaveFileDialog SaveFileExcel = new SaveFileDialog(); SaveFileExcel.FileName = "thugoclai_"; SaveFileExcel.Filter = @"Excel File (*.xlsx)|*.xlsx"; if (SaveFileExcel.ShowDialog() == DialogResult.OK) { var temp = new FileInfo(pathToFile); using (var pck = new ExcelPackage(temp)) { ExcelWorksheet wsList = pck.Workbook.Worksheets[1]; const int startRow = 13; int row = startRow; var loaitien = "VND"; for (int i = 0; i < GV_TD_Pyctl_ThuGocLai.DataRowCount; i++) { wsList.InsertRow(row, 1); // if (GV_TD_Pyctl_ThuGocLai.GetRowCellValue(i, "LoaiTien").ToString() != loaitien) { //// wsList.InsertRow(row, 1); // wsList.Cells[row, 1].Value = a; // wsList.Cells[row, 2].Value = "T?ng c?ng " + GV_TD_Pyctl_ThuGocLai.GetRowCellValue(i, "LoaiTien").ToString(); // wsList.Cells[row, 2].Style.Font.Bold = true; // // wsList.Cells["A" + startRow + ":B" + row].Merge = true; // //wsList.Cells[row, 5].Formula = string.Format("Sum(C{0}:D{0})", row - 1); // //wsList.Cells[row, 3].Formula = string.Format("Sum(C{0}:C{1})", row - 1, startRow - 1 +a); // a++; // } wsList.Cells[row, 2].Value = GV_TD_Pyctl_ThuGocLai.GetRowCellValue(i, "tkvay").ToString(); wsList.Cells[row, 3].Value = Convert.ToDecimal(GV_TD_Pyctl_ThuGocLai.GetRowCellValue(i, "DuNo")); wsList.Cells[row, 3].Style.Numberformat.Format = "#,##0.00;-#,##0.00"; wsList.Cells[row, 4].Value = Convert.ToDecimal(GV_TD_Pyctl_ThuGocLai.GetRowCellValue(i, "LaiCongDon")); wsList.Cells[row, 4].Style.Numberformat.Format = "#,##0.00;-#,##0.00"; wsList.Cells[row, 5].Value = GV_TD_Pyctl_ThuGocLai.GetRowCellValue(i, "LaiTraCham"); wsList.Cells[row, 5].Style.Numberformat.Format = "#,##0.00;-#,##0.00"; wsList.Cells[row, 7].Value = GV_TD_Pyctl_ThuGocLai.GetRowCellValue(i, "PhuongThucTra").ToString(); wsList.Cells[row, 8].Value = GV_TD_Pyctl_ThuGocLai.GetRowCellValue(i, "GhiChu").ToString(); wsList.Cells[row, 9].Value = GV_TD_Pyctl_ThuGocLai.GetRowCellValue(i, "LoaiTien").ToString(); wsList.Cells[row, 6].Formula = string.Format("Sum(C{0}:E{0})", row); wsList.Cells[row, 6].Style.Numberformat.Format = "#,##0.00;-#,##0.00"; loaitien = GV_TD_Pyctl_ThuGocLai.GetRowCellValue(i, "LoaiTien").ToString(); row++; } string local = "A" + startRow + ":I" + row; var cell = wsList.Cells[local]; var border = cell.Style.Border; border.BorderAround(ExcelBorderStyle.Thin); border.Bottom.Style = ExcelBorderStyle.Thin; border.Top.Style = ExcelBorderStyle.Thin; border.Left.Style = ExcelBorderStyle.Thin; border.Right.Style = ExcelBorderStyle.Thin; wsList.Cells["A10"].Value = wsList.Cells["A10"].Value + " " + GV_TD_Pyctl_ThuGocLai.GetRowCellValue(1, "Cif"); wsList.Cells["A9"].Value = wsList.Cells["A9"].Value + " " + GV_TD_Pyctl_ThuGocLai.GetRowCellValue(1, "TenKh"); wsList.Cells["F3"].Value = string.Format(@"Ngày {0} tháng {1} nam {2}", DateTime.Now.Day, DateTime.Now.Month, DateTime.Now.Year); wsList.Cells["A7"].Value = wsList.Cells["A7"].Value + " " + _phongthu; wsList = pck.Workbook.Worksheets[2]; wsList.Cells["A1"].LoadFromCollection(_tp, true); wsList.Column(2).Style.Numberformat.Format = "dd/MM/yyyy"; wsList.Column(10).Style.Numberformat.Format = "dd/MM/yyyy"; wsList.Column(11).Style.Numberformat.Format = "dd/MM/yyyy"; var fi = new FileInfo(SaveFileExcel.FileName); if (fi.Exists) { fi.Delete(); } pck.SaveAs(fi); } insoden.MainForm.OpenExplorer(SaveFileExcel.FileName); } }