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