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);
}
}
