private void button1_Click(object sender, EventArgs e)
{
// Khởi động chương trình Excel
COMExcel.Application exApp = new COMExcel.Application();
COMExcel.Workbook exBook;
COMExcel.Worksheet exSheet;
COMExcel.Range exRange;
string sql;
int hang = 0, cot = 0;
DataTable tblThongtinHD, tblThongtinHang;
exBook = exApp.Workbooks.Add(COMExcel.XlWBATemplate.xlWBATWorksheet);
exSheet = exBook.Worksheets[1];
// Định dạng chung
exRange = exSheet.Cells[1, 1];
exRange.Range["A1:B3"].Font.Size = 10;
exRange.Range["A1:B3"].Font.Name = "Times new roman";
exRange.Range["A1:B3"].Font.Bold = true;
exRange.Range["A1:B3"].Font.ColorIndex = 5; //Màu xanh da trời
exRange.Range["A1:A1"].ColumnWidth = 7;
exRange.Range["B1:B1"].ColumnWidth = 15;
exRange.Range["A1:B1"].MergeCells = true;
exRange.Range["A1:B1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A1:B1"].Value = "Thế Giới Siêu Thị";
exRange.Range["A2:B2"].MergeCells = true;
exRange.Range["A2:B2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A2:B2"].Value = "Việt Nam";
exRange.Range["A3:B3"].MergeCells = true;
exRange.Range["A3:B3"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A3:B3"].Value = "Điện thoại: 0972594994";
exRange.Range["C2:E2"].Font.Size = 16;
exRange.Range["C2:E2"].Font.Name = "Times new roman";
exRange.Range["C2:E2"].Font.Bold = true;
exRange.Range["C2:E2"].Font.ColorIndex = 3; //Màu đỏ
exRange.Range["C2:E2"].MergeCells = true;
exRange.Range["C2:E2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["C2:E2"].Value = "BÁO CÁO HÀNG MUA ÍT";
//Lấy thông tin các mặt hàng
sql = @"SELECT top(3) tblHoaDonBan.Makhach, sum(tblChiTietHDB.Soluong) AS Sl FROM tblChiTietHDB INNER JOIN tblHoaDonBan ON tblChiTietHDB.Sohoadonban = tblHoaDonBan.Sohoadonban WHERE tblHoaDonBan.Ngayban BETWEEN '" + d1.Text + "' AND '" + d2.Text + "' group by tblHoaDonBan.Makhach order by sum(tblChiTietHDB.Soluong) ASC";
tblThongtinHang = Data.GetDataToTable(sql);
//Tạo dòng tiêu đề bảng
exRange.Range["A11:F11"].Font.Bold = true;
exRange.Range["A11:F11"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["C11:F11"].ColumnWidth = 12;
exRange.Range["A11:A11"].Value = "STT";
exRange.Range["B11:B11"].Value = "Mã KH";
exRange.Range["C11:C11"].Value = "Số Lượng";
for (hang = 0; hang <= tblThongtinHang.Rows.Count - 1; hang++)
{
//Điền số thứ tự vào cột 1 từ dòng 12
exSheet.Cells[1][hang + 12] = hang + 1;
for (cot = 0; cot <= tblThongtinHang.Columns.Count - 1; cot++)
{
//Điền thông tin hàng từ cột thứ 2, dòng 12
exSheet.Cells[cot + 2][hang + 12] = tblThongtinHang.Rows[hang][cot].ToString();
}
}
exRange = exSheet.Cells[1][hang + 15]; //Ô A1
exRange.Range["A1:F1"].MergeCells = true;
exRange.Range["A1:F1"].Font.Bold = true;
exRange.Range["A1:F1"].Font.Italic = true;
exRange.Range["A1:F1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignRight;
exRange = exSheet.Cells[4][hang + 17]; //Ô A1
exRange.Range["A1:C1"].MergeCells = true;
exRange.Range["A1:C1"].Font.Italic = true;
exRange.Range["A1:C1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
DateTime d = DateTime.Now;
exRange.Range["A1:C1"].Value = "Hà Nội, ngày " + d.Day + " tháng " + d.Month + " năm " + d.Year;
exRange.Range["A2:C2"].MergeCells = true;
exRange.Range["A2:C2"].Font.Italic = true;
exRange.Range["A2:C2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exRange.Range["A6:C6"].MergeCells = true;
exRange.Range["A6:C6"].Font.Italic = true;
exRange.Range["A6:C6"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter;
exSheet.Name = "Báo Cáo";
exApp.Visible = true;
}
