public static void exportDept(String idKH, String path, String dateTo, String dateFrom)
{
KhachHangDto infoKH = new KhachHangDao().getKhachHangById(idKH);
SqlDataReader reader = new OrderDao().getDebtByCustomerWithPay(dateTo, dateFrom, idKH);
try
{
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Chưa cài đặt excel, thao tác bị lỗi", "ERROR");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Open(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location) + "\\Template\\CongNoTemplate.xls", 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);
xlWorkSheet.Cells[8, 1] = "Tên khách hàng : " + infoKH.tenKhachHang;
int i = 0;
decimal sumChietKhau = 0;
decimal sumThanhTien = 0;
int sumSoLuong = 0;
Boolean isPayRecord = false;
while (reader.Read())
{
if (reader.GetString(reader.GetOrdinal("TEN_SAN_PHAM")) == "THANH TOÁN")
{
isPayRecord = true;
}
else
{
isPayRecord = false;
}
var r = xlWorkSheet.get_Range(string.Format("{0}:{0}", 12, Type.Missing));
var range = xlWorkSheet.get_Range(string.Format("{0}:{0}", 11, Type.Missing));
range.Select();
range.Copy();
r.Insert();
xlWorkSheet.Cells[11, 1] = reader.GetDateTime(reader.GetOrdinal("NGAY_GIAO")).Day;
xlWorkSheet.Cells[11, 2] = reader.GetDateTime(reader.GetOrdinal("NGAY_GIAO")).Month;
xlWorkSheet.Cells[11, 3] = reader.GetDateTime(reader.GetOrdinal("NGAY_GIAO")).Year;
xlWorkSheet.Cells[11, 4] = reader.GetString(reader.GetOrdinal("ID"));
xlWorkSheet.Cells[11, 5] = reader.GetString(reader.GetOrdinal("TEN_SAN_PHAM"));
decimal thanhTien = !reader.IsDBNull(reader.GetOrdinal("TONG_TIEN")) ? reader.GetDecimal(reader.GetOrdinal("TONG_TIEN")) : 0;
if (!isPayRecord)
{
xlWorkSheet.Cells[11, 6] = reader.GetString(reader.GetOrdinal("CD_CR"));
xlWorkSheet.Cells[11, 7] = reader.GetString(reader.GetOrdinal("KICH_THUOC"));
xlWorkSheet.Cells[11, 8] = !reader.IsDBNull(reader.GetOrdinal("SO_TRANG")) ? reader.GetInt32(reader.GetOrdinal("SO_TRANG")) : 0;
xlWorkSheet.Cells[11, 9] = reader.GetString(reader.GetOrdinal("LOAI_BIA"));
xlWorkSheet.Cells[11, 10] = reader.GetString(reader.GetOrdinal("LOAI_GIAY"));
int soLuong = !reader.IsDBNull(reader.GetOrdinal("SO_LUONG")) ? reader.GetInt32(reader.GetOrdinal("SO_LUONG")) : 0;
decimal donGia = !reader.IsDBNull(reader.GetOrdinal("DON_GIA")) ? reader.GetDecimal(reader.GetOrdinal("DON_GIA")) : 0;
decimal chietKhau = donGia * (
(!reader.IsDBNull(reader.GetOrdinal("CHIET_KHAU")) ? reader.GetDecimal(reader.GetOrdinal("CHIET_KHAU")) : 0)
) / 100;
xlWorkSheet.Cells[11, 11] = soLuong;
xlWorkSheet.Cells[11, 12] = donGia;
xlWorkSheet.Cells[11, 13] = chietKhau;
sumSoLuong += soLuong;
sumChietKhau += chietKhau;
sumThanhTien += thanhTien;
for (int col = 1; col <= 14; col++)
{
xlWorkSheet.Cells[11, col].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
}
}
else
{
sumThanhTien -= thanhTien;
for (int col = 1; col <= 14; col++)
{
xlWorkSheet.Cells[11, col].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
}
}
xlWorkSheet.Cells[11, 14] = thanhTien;
i++;
}
xlWorkSheet.Cells[13 + i, 11] = sumSoLuong;
xlWorkSheet.Cells[13 + i, 13] = sumChietKhau;
xlWorkSheet.Cells[13 + i, 14] = sumThanhTien;
DateTime now = DateTime.Now;
xlWorkSheet.Cells[15 + i, 13] = "Ngày " + now.Day + " tháng " + now.Month + " năm " + now.Year;
xlWorkBook.CheckCompatibility = false;
String fileName = "CongNo_" + now.ToString("yyyyMMddhhmmss") + ".xls";
xlWorkBook.SaveAs(path + "\\" + fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
reader.Close();
}
}