private void Load_DataGridViewChitiet() { string sql; sql = "SELECT SoDDH, a.MaNoiThat, b.TenNoiThat, a.SoLuong, b.DonGiaBan, a.GiamGia, a.ThanhTien FROM ChiTietDonDatHang AS a, DMNoiThat AS b WHERE a.MaNoiThat=b.MaNoiThat"; tblCTHDB = DAO.GetDataToTable(sql); DataGridViewChitiet.DataSource = tblCTHDB; }
private void btnInBaoCao_Click(object sender, EventArgs e) { // Khởi động chương trình Excel COMExcel.Application exApp = new COMExcel.Application(); COMExcel.Workbook exBook; //Trong 1 chương trình Excel có nhiều Workbook COMExcel.Worksheet exSheet; //Trong 1 Workbook có nhiều Worksheet COMExcel.Range exRange; string sql; int hang = 0, cot = 0; DataTable tblThongtinBC, 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 = "Cửa hàng nội thất"; exRange.Range["A2:B2"].MergeCells = true; exRange.Range["A2:B2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A2:B2"].Value = "Hai Bà Trưng - Hà Nội"; exRange.Range["A3:B3"].MergeCells = true; exRange.Range["A3:B3"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A3:B3"].Value = "Điện thoại: (04)37562222"; 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 = "SẢN PHẨM ĐÃ BÁN ĐƯỢC"; // Biểu diễn thông tin chung của báo cáo sql = "SELECT b.Tenkhach, b.Diachi, b.Dienthoai FROM DonDatHang AS a, KhachHang AS b WHERE a.MaKhach = N'" + cmbMaKH.SelectedValue.ToString() + "' AND a.MaKhach = b.MaKhach"; tblThongtinBC = DAO.GetDataToTable(sql); exRange.Range["B6:C9"].Font.Size = 12; exRange.Range["B6:C9"].Font.Name = "Times new roman"; exRange.Range["B6:B6"].Value = "Khách hàng:"; exRange.Range["C6:E6"].MergeCells = true; exRange.Range["C6:E6"].Value = tblThongtinBC.Rows[0][0].ToString(); exRange.Range["B7:B7"].Value = "Địa chỉ:"; exRange.Range["C7:E7"].MergeCells = true; exRange.Range["C7:E7"].Value = tblThongtinBC.Rows[0][1].ToString(); exRange.Range["B8:B8"].Value = "Điện thoại:"; exRange.Range["C8:C8"].MergeCells = true; exRange.Range["C8:C8"].Value = tblThongtinBC.Rows[0][2].ToString(); sql = "select b.MaNoiThat,TenNoiThat,Datepart(mm,NgayGiao),b.SoLuong from DMNoiThat as a inner join " + "ChiTietDonDatHang as b on a.MaNoiThat=b.MaNoiThat inner join DonDatHang as c on b.SoDDH=c.SoDDH where MaKHach=N'" + cmbMaKH.SelectedValue + "'AND datepart(mm,NgayGiao)='" + txtThang.Text + "'"; tblThongtinHang = DAO.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ã nội thất"; exRange.Range["C11:C11"].Value = "Tên nội thất"; exRange.Range["D11:D11"].Value = "Tháng giao"; exRange.Range["E11:E11"].Value = "Số lượng"; //exRange.Range["F11:F11"].Value = "Thành tiền"; 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.Range[" C16: E16 "].MergeCells = true; exRange.Range[" C16: E16 "].Font.Italic = true; exRange.Range[" C16: E16 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exSheet.Name = " Hóa Đơn Bán"; exApp.Visible = true; int day = DateTime.Now.Day; int month = DateTime.Now.Month; int year = DateTime.Now.Year; exRange.Range[" C16: E16 "].Value = " Hà Nội, ngày " + day + " tháng " + month + "năm " + year; exRange.Range[" C17: E17 "].MergeCells = true; exRange.Range[" C17: E17 "].Font.Italic = true; exRange.Range[" C17: E17 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" C17: E17 "].Value = " Nhân viên lập báo cáo "; exRange.Range[" C20: E20 "].MergeCells = true; exRange.Range[" C20: E20 "].Font.Italic = true; exRange.Range[" C20: E20 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" C20: E20 "].Value = " (Kí và ghi rõ họ tên)"; exSheet.Name = " Danh sách sản phẩm "; exApp.Visible = true; }
private void btnIn_Click(object sender, EventArgs e) { // Khởi động chương trình Excel COMExcel.Application exApp = new COMExcel.Application(); COMExcel.Workbook exBook; //Trong 1 chương trình Excel có nhiều Workbook COMExcel.Worksheet exSheet; //Trong 1 Workbook có nhiều Worksheet 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 = "Cửa hàng nội thất"; exRange.Range["A2:B2"].MergeCells = true; exRange.Range["A2:B2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A2:B2"].Value = "Hai Bà Trưng - Hà Nội"; exRange.Range["A3:B3"].MergeCells = true; exRange.Range["A3:B3"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A3:B3"].Value = "Điện thoại: (04)37562222"; 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 = "HÓA ĐƠN NHẬP"; // Biểu diễn thông tin chung của hóa đơn bán sql = "SELECT a.SoHDN, a.NgayNhap, b.TenNCC, b.Dienthoai, b.Diachi, a.Tongtien, c.TenNV FROM HoaDonNhap AS a, NhaCungCap AS b, NhanVien AS c WHERE a.SoHDN = N'" + txtSoHDN.Text + "' AND a.MaNCC = b.MaNCC AND a.MaNV = c.MaNV"; tblThongtinHD = DAO.GetDataToTable(sql); exRange.Range["B6:C9"].Font.Size = 12; exRange.Range["B6:C9"].Font.Name = "Times new roman"; exRange.Range["B6:B6"].Value = "Mã hóa đơn:"; exRange.Range["C6:E6"].MergeCells = true; exRange.Range["C6:E6"].Value = tblThongtinHD.Rows[0][0].ToString(); exRange.Range["B7:B7"].Value = "Nhà cung cấp:"; exRange.Range["C7:E7"].MergeCells = true; exRange.Range["C7:E7"].Value = tblThongtinHD.Rows[0][2].ToString(); exRange.Range["B8:B8"].Value = "Địa chỉ:"; exRange.Range["C8:E8"].MergeCells = true; exRange.Range["C8:E8"].Value = tblThongtinHD.Rows[0][4].ToString(); exRange.Range["B9:B9"].Value = "Điện thoại:"; exRange.Range["C9:C9"].MergeCells = true; exRange.Range["C9:C9"].Value = tblThongtinHD.Rows[0][3].ToString(); //Lấy thông tin các mặt hàng sql = "SELECT b.TenNoiThat, a.SoLuong, b.DonGiaNhap, a.GiamGia, a.ThanhTien " + "FROM ChiTietHoaDonNhap AS a , DMNoiThat AS b WHERE a.SoHDN = N'" + txtSoHDN.Text + "' AND a.MaNoiThat = b.MaNoiThat"; tblThongtinHang = DAO.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 = "Tên hàng"; exRange.Range["C11:C11"].Value = "Số lượng"; exRange.Range["D11:D11"].Value = "Đơn giá"; exRange.Range["E11:E11"].Value = "Giảm giá"; exRange.Range["F11:F11"].Value = "Thành tiền"; 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(); } } sql = "select sum(TongTien) from HoaDonNhap WHERE SoHDN=N'" + txtSoHDN.Text + "'"; DataTable tblBC; tblBC = DAO.GetDataToTable(sql); exRange = exSheet.Cells[cot][hang + 14]; exRange.Font.Bold = true; exRange.Value2 = "Tổng tiền:"; exRange = exSheet.Cells[cot + 1][hang + 14]; exRange.Font.Bold = true; exRange.Value2 = tblBC.Rows[0][0].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.Range[" A1: F1"].Value = "Bằng chữ: " + DAO.ChuyenSoSangChu(Double.Parse(tblBC.Rows[0][0].ToString())); sql = "Select TenNV from NhanVien as a inner join HoaDonNhap as b on a.MaNV=b.MaNV where SoHDN=N'" + txtSoHDN.Text.Trim() + "'"; DataTable tblBC1; tblBC1 = DAO.GetDataToTable(sql); exRange = exSheet.Cells[cot][hang + 21]; exRange.Font.Bold = true; //exRange.Value2 = tblBC1; exRange = exSheet.Cells[cot + 0][hang + 21]; exRange.Font.Bold = true; exRange.Value2 = tblBC1.Rows[0][0].ToString(); exRange = exSheet.Cells[1][hang + 22]; //Ô A1 exRange = exSheet.Cells[4][hang + 17]; exRange.Range[" A1: C1 "].MergeCells = true; exRange.Range[" A1: C1 "].Font.Italic = true; exRange.Range[" A1: C1 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exSheet.Name = " Hóa Đơn Nhập"; exApp.Visible = true; int day = DateTime.Now.Day; int month = DateTime.Now.Month; int year = DateTime.Now.Year; exRange.Range[" A1: C1 "].Value = " Hà Nội, ngày " + day + " tháng " + month + "năm " + year; exRange.Range[" A2: C2 "].MergeCells = true; exRange.Range[" A2: C2 "].Font.Italic = true; exRange.Range[" A2: C2 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" A2: C2 "].Value = " Nhân viên nhập hàng "; exRange.Range[" A6: C6 "].MergeCells = true; exRange.Range[" A6: C6 "].Font.Italic = true; exRange.Range[" A6: C6 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" A6: C6 "].Value = " (Kí tên)"; }
private void btnInBaoCao_Click(object sender, EventArgs e) { COMExcel.Application exApp = new COMExcel.Application(); COMExcel.Workbook exBook; COMExcel.Worksheet exSheet; COMExcel.Range exRange; string sql; int hang = 0, cot = 0; DataTable 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; 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 = " Cửa hàng nội thất"; exRange.Range[" A2: B2 "].MergeCells = true; exRange.Range[" A2: B2 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" A2: B2"].Value = " Hai Bà Trưng - Hà Nội "; exRange.Range[" A3: B3 "].MergeCells = true; exRange.Range[" A3: B3 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" A3: B3 "].Value = " Điện thoại: (023)3756222 "; exRange.Range[" D2: F2 "].Font.Size = 16; exRange.Range[" D2: F2 "].Font.Name = " Times new roman"; exRange.Range[" D2: F2 "].Font.Bold = true; exRange.Range[" D2: F2 "].Font.ColorIndex = 3; //Màu đỏ exRange.Range[" D2: F2 "].MergeCells = true; exRange.Range[" D2: F2 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" D2: F2 "].Value = " Danh Sách Hóa Đơn Nhập"; DataTable tblThongtinNV; sql = "SELECT b.TenNV, b.Diachi, b.Dienthoai FROM HoaDonNhap AS a, NhanVien AS b WHERE a.MaNV = N'" + cmbMaNV.SelectedValue.ToString() + "' AND a.MaNV = b.MaNV"; tblThongtinNV = DAO.GetDataToTable(sql); exRange.Range["B6:C9"].Font.Size = 12; exRange.Range["B6:C9"].Font.Name = "Times new roman"; exRange.Range["B6:B6"].Value = "Nhân viên:"; exRange.Range["C6:E6"].MergeCells = true; exRange.Range["C6:E6"].Value = tblThongtinNV.Rows[0][0].ToString(); exRange.Range["B7:B7"].Value = "Địa chỉ:"; exRange.Range["C7:E7"].MergeCells = true; exRange.Range["C7:E7"].Value = tblThongtinNV.Rows[0][1].ToString(); exRange.Range["B8:B8"].Value = "Điện thoại:"; exRange.Range["C8:C8"].MergeCells = true; exRange.Range["C8:C8"].Value = tblThongtinNV.Rows[0][2].ToString(); //Lấy thông tin các mặt hàng sql = "Select b.SoHDN,TenNoiThat,b.SoLuong,(Datepart(QUARTER, NgayNhap)),GiamGia,DonGiaNhap,ThanhTien from DMNoiThat as a " + "inner join ChiTietHoaDonNhap as b on a.MaNoiThat=b.MaNoiThat inner join HoaDonNhap as c on b.SoHDN=c.SoHDN " + "inner join NhanVien as d on d.MaNV=c.MaNV where c.MaNV=N'" + cmbMaNV.SelectedValue + "'AND (Datepart(QUARTER, NgayNhap))='" + txtQuy.Text + "'"; tblThongTinHang = DAO.GetDataToTable(sql); //Tạo dòng tiêu đề bảng exRange.Range[" A11: H11 "].Font.Bold = true; exRange.Range[" A11: H11 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" C11: H11 "].ColumnWidth = 12; exRange.Range[" A11: A11 "].Value = " STT "; exRange.Range[" B11: B11 "].Value = " Mã hóa đơn "; exRange.Range[" C11: C11 "].Value = " Tên hàng "; exRange.Range[" D11: D11 "].Value = " Số lượng "; exRange.Range[" E11: E11 "].Value = " Qúy nhập "; exRange.Range[" F11: F11 "].Value = " Giảm Giá "; exRange.Range[" G11: G11 "].Value = " Đơn Giá "; exRange.Range[" H11: H11 "].Value = " Thành tiền "; 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(); } exSheet.Cells[2][hang + 12] = "'" + tblThongTinHang.Rows[hang][0].ToString(); } sql = "select sum(TongTien) from HoaDonNhap where MaNV=N'" + cmbMaNV.Text + "'"; DataTable tblBC; tblBC = DAO.GetDataToTable(sql); exRange = exSheet.Cells[cot][hang + 17]; exRange.Font.Bold = true; exRange.Value2 = "Tổng tiền:"; exRange = exSheet.Cells[cot + 1][hang + 17]; exRange.Font.Bold = true; exRange.Value2 = tblBC.Rows[0][0].ToString(); exRange = exSheet.Cells[1][hang + 18]; //Ô A1 exRange.Range[" C1: H1 "].MergeCells = true; exRange.Range[" C1: H1 "].Font.Bold = true; exRange.Range[" C1: H1 "].Font.Italic = true; exRange.Range[" C1: H1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignRight; exRange.Range[" C1: H1"].Value = "Bằng chữ: " + DAO.ChuyenSoSangChu(Double.Parse(tblBC.Rows[0][0].ToString())); exRange = exSheet.Cells[4][hang + 20]; exRange.Range[" C1: E1 "].MergeCells = true; exRange.Range[" C1: E1 "].Font.Italic = true; exRange.Range[" C1: E1 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; int day = DateTime.Now.Day; int month = DateTime.Now.Month; int year = DateTime.Now.Year; exRange.Range[" C1: E1 "].Value = " Hà Nội, ngày " + day + " tháng " + month + "năm " + year; exRange.Range[" C2: E2 "].MergeCells = true; exRange.Range[" C2: E2 "].Font.Italic = true; exRange.Range[" C2: E2 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" C2: E2 "].Value = " Nhân viên lập báo cáo "; exRange.Range[" C3: E3 "].MergeCells = true; exRange.Range[" C3: E3 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" C3: E3 "].Value = " (Kí, Ghi rõ họ tên)"; exSheet.Name = " Hóa Đơn Nhập"; exApp.Visible = true; }
private void btnInBaoCao_Click(object sender, EventArgs e) { COMExcel.Application exApp = new COMExcel.Application(); COMExcel.Workbook exBook; COMExcel.Worksheet exSheet; COMExcel.Range exRange; string sql; int hang = 0, cot = 0; DataTable tblBCDSHDB; 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; 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 = " Cửa hàng nội thất"; exRange.Range[" A2: B2 "].MergeCells = true; exRange.Range[" A2: B2 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" A2: B2"].Value = " Hai Bà Trưng - Hà Nội "; exRange.Range[" A3: B3 "].MergeCells = true; exRange.Range[" A3: B3 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" A3: B3 "].Value = " Điện thoại: (023)3756222 "; 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 = " Danh Sách Hóa Đơn Bán"; DataTable tblThongtinHang; sql = "SELECT a.MaNoiThat,TenNoiThat FROM ChiTietDonDatHang as a inner join DMNoiThat as b on a.MaNoiThat=b.MaNoiThat WHERE a.MaNoiThat=N'" + cmbMaNoiThat.SelectedValue + "'"; tblThongtinHang = DAO.GetDataToTable(sql); exRange.Range["B6:C9"].Font.Size = 12; exRange.Range["B6:C9"].Font.Name = "Times new roman"; exRange.Range["B6:B6"].Value = "Mã hàng:"; exRange.Range["C6:E6"].MergeCells = true; exRange.Range["C6:E6"].Value = tblThongtinHang.Rows[0][0].ToString(); exRange.Range["B7:B7"].Value = "Tên hàng"; exRange.Range["C7:E7"].MergeCells = true; exRange.Range["C7:E7"].Value = tblThongtinHang.Rows[0][1].ToString(); //Lấy thông tin các mặt hàng sql = " Select b.SoDDH,b.SoLuong,DonGiaBan,GiamGia,ThanhTien from" + " DMNoiThat as a inner join ChiTietDonDatHang as b on a.MaNoiThat=b.MaNoiThat inner join" + " DonDatHang as c on b.SoDDH=c.SoDDH where b.MaNoiThat=N'" + cmbMaNoiThat.SelectedValue + "'"; tblBCDSHDB = DAO.GetDataToTable(sql); //Tạo dòng tiêu đề bảng exRange.Range[" A11: H11 "].Font.Bold = true; exRange.Range[" A11: H11 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" C11: H11 "].ColumnWidth = 12; exRange.Range[" A11: A11 "].Value = " STT "; exRange.Range[" B11: B11 "].Value = " Mã hóa đơn "; exRange.Range[" C11: C11 "].Value = " Số lượng "; exRange.Range[" D11: D11 "].Value = " Đơn giá "; exRange.Range[" E11: E11 "].Value = " Giảm giá "; exRange.Range[" F11: F11 "].Value = " Thành tiền "; for (hang = 0; hang <= tblBCDSHDB.Rows.Count - 1; hang++) { exSheet.Cells[1][hang + 12] = hang + 1; for (cot = 0; cot <= tblBCDSHDB.Columns.Count - 1; cot++) { exSheet.Cells[cot + 2][hang + 12] = tblBCDSHDB.Rows[hang][cot].ToString(); } exSheet.Cells[2][hang + 12] = "'" + tblBCDSHDB.Rows[hang][0].ToString(); } sql = "select sum(TongTien) from DonDatHang as a inner join ChiTietDonDatHang as b on a.SoDDH=b.SoDDH where MaNoiThat='" + cmbMaNoiThat.Text + "'"; DataTable tblBC; tblBC = DAO.GetDataToTable(sql); exRange = exSheet.Cells[cot][hang + 14]; exRange.Font.Bold = true; exRange.Value2 = "Tổng tiền:"; exRange = exSheet.Cells[cot + 1][hang + 14]; exRange.Font.Bold = true; exRange.Value2 = tblBC.Rows[0][0].ToString(); exRange = exSheet.Cells[1][hang + 15]; //Ô A1 exRange.Range[" C1: H1 "].MergeCells = true; exRange.Range[" C1: H1 "].Font.Bold = true; exRange.Range[" C1: H1 "].Font.Italic = true; exRange.Range[" C1: H1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignRight; exRange.Range[" C1: H1"].Value = "Bằng chữ: " + DAO.ChuyenSoSangChu(Double.Parse(tblBC.Rows[0][0].ToString())); exRange = exSheet.Cells[4][hang + 16]; exRange.Range[" C1: E1 "].MergeCells = true; exRange.Range[" C1: E1 "].Font.Italic = true; exRange.Range[" C1: E1 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; int day = DateTime.Now.Day; int month = DateTime.Now.Month; int year = DateTime.Now.Year; exRange.Range[" C1: E1 "].Value = " Hà Nội, ngày " + day + " tháng " + month + "năm " + year; exRange.Range[" C2: E2 "].MergeCells = true; exRange.Range[" C2: E2 "].Font.Italic = true; exRange.Range[" C2: E2 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" C2: E2 "].Value = " Nhân viên lập báo cáo "; exRange.Range[" C3: E3 "].MergeCells = true; exRange.Range[" C3: E3 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" C3: E3 "].Value = " (Kí, Ghi rõ họ tên)"; exSheet.Name = " Hóa Đơn Bán"; exApp.Visible = true; }
private void btnInBaoCao_Click(object sender, EventArgs e) { // Khởi động chương trình Excel COMExcel.Application exApp = new COMExcel.Application(); COMExcel.Workbook exBook; //Trong 1 chương trình Excel có nhiều Workbook COMExcel.Worksheet exSheet; //Trong 1 Workbook có nhiều Worksheet COMExcel.Range exRange; string sql; int hang = 0, cot = 0; DataTable tblThongtinNCC; 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 = "Cửa hàng quản lý nội thất"; exRange.Range["A2:B2"].MergeCells = true; exRange.Range["A2:B2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A2:B2"].Value = "Hai Bà Trưng - Hà Nội"; exRange.Range["A3:B3"].MergeCells = true; exRange.Range["A3:B3"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["A3:B3"].Value = "Điện thoại: (04)37562222"; exRange.Range["C2:F2"].Font.Size = 16; exRange.Range["C2:F2"].Font.Name = "Times new roman"; exRange.Range["C2:F2"].Font.Bold = true; exRange.Range["C2:F2"].Font.ColorIndex = 3; //Màu đỏ exRange.Range["C2:F2"].MergeCells = true; exRange.Range["C2:F2"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["C2:F2"].Value = "HỌ TÊN NHÀ CUNG CẤP"; DataTable tblThongtinHang; sql = "SELECT a.MaNoiThat,TenNoiThat,Datepart(mm,NgayNhap) FROM ChiTietHoaDonNhap as a inner join DMNoiThat as b on a.MaNoiThat=b.MaNoiThat inner join HoaDonNhap as c on a.SoHDN=c.SoHDN WHERE a.MaNoiThat=N'" + cmbMaNoiThat.SelectedValue + "' AND Datepart(mm,NgayNhap)='" + txtThang.Text + "'"; tblThongtinHang = DAO.GetDataToTable(sql); exRange.Range["B6:C9"].Font.Size = 12; exRange.Range["B6:C9"].Font.Name = "Times new roman"; exRange.Range["B6:B6"].Value = "Mã hàng:"; exRange.Range["C6:E6"].MergeCells = true; exRange.Range["C6:E6"].Value = tblThongtinHang.Rows[0][0].ToString(); exRange.Range["B7:B7"].Value = "Tên hàng"; exRange.Range["C7:E7"].MergeCells = true; exRange.Range["C7:E7"].Value = tblThongtinHang.Rows[0][1].ToString(); exRange.Range["B8:B8"].Value = "Tháng nhập"; exRange.Range["C8:C8"].MergeCells = true; exRange.Range["C8:C8"].Value = tblThongtinHang.Rows[0][2].ToString(); // Biểu diễn thông tin chung của hóa đơn bán sql = "Select a.MaNCC,TenNCC,DiaChi,DienThoai from NhaCungCap as a inner join HoaDonNhap as b on a.MaNCC=b.MaNCC" + " inner join ChiTietHoaDonNhap as c on b.SoHDN=c.SoHDN inner join DMNoiThat as d on c.MaNoiThat=d.MaNoiThat where d.MaNoiThat=N'" + cmbMaNoiThat.SelectedValue + "'AND datepart(mm,(NgayNhap))='" + txtThang.Text + "'"; tblThongtinNCC = DAO.GetDataToTable(sql); //Tạo dòng tiêu đề bảng exRange.Range["A11:G11"].Font.Bold = true; exRange.Range["A11:G11"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range["C11:F11"].ColumnWidth = 12; exRange.Range["A11:A11"].Value = "STT"; exRange.Range["B11:B11"].Value = "Mã NCC"; exRange.Range["C11:C11"].Value = "Tên NCC"; exRange.Range["D11:D11"].Value = "Địa chỉ"; exRange.Range["E11:E11"].Value = "Điện thoại"; for (hang = 0; hang <= tblThongtinNCC.Rows.Count - 1; hang++) { exSheet.Cells[1][hang + 12] = hang + 1; for (cot = 0; cot <= tblThongtinNCC.Columns.Count - 1; cot++) { exSheet.Cells[cot + 2][hang + 12] = tblThongtinNCC.Rows[hang][cot].ToString(); } exSheet.Cells[2][hang + 12] = "'" + tblThongtinNCC.Rows[hang][0].ToString(); } exRange = exSheet.Cells[4][hang + 15]; //Ô A1 exRange.Range["A1:C1"].MergeCells = true; exRange.Range["A1:C1"].Font.Italic = true; exRange.Range["A1:C1"].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; int day = DateTime.Now.Day; int month = DateTime.Now.Month; int year = DateTime.Now.Year; exRange.Range[" A1: C1 "].Value = " Hà Nội, ngày " + day + " tháng " + month + "năm " + year; exRange.Range[" A2: C2 "].MergeCells = true; exRange.Range[" A2: C2 "].Font.Italic = true; exRange.Range[" A2: C2 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" A2: C2 "].Value = " Nhân viên lập báo cáo "; exRange.Range[" A3: C3 "].MergeCells = true; exRange.Range[" A3: C3 "].HorizontalAlignment = COMExcel.XlHAlign.xlHAlignCenter; exRange.Range[" A3: C3 "].Value = " (Kí, Ghi rõ họ tên)"; exSheet.Name = " Danh sách nhà cung cấp "; exApp.Visible = true; }