C#-Xuất dữ liệu SQL Server ra Excel để báo cáo

Trong quá trình thực hiện phần mềm, vấn đề xuất báo cáo: ra file cứng (giấy, tức là từ phần mềm xuất thẳng dữ liệu ra giấy trong máy in luôn), file mềm (Excel, PDF, Word, HTML, Image…) thì Tui thấy nhu cầu xuất ra file Excel cũng khá phổ biến. Vì vậy Tui làm một số Tips hữu ích không có sắp xếp thứ tự như các bài học giảng giải về 1 công nghệ. Mỗi bài sẽ là 1 Tip hoàn chỉnh.

Cụ Thể trong bài này: Ta có dữ liệu từ SQL Server –> xuất ra Excel để báo cáo:

Hình trên Tui chụp 1 CSDL mẫu tên là “CSDL_MAU”, nó chỉ có 1 bảng duy nhất là “SanPham”, trong bảng này có 4 cột (MaSP, TenSp, DonGiaNhap, DonGiaXuat). Sau đó nhiệm vụ của ta là xuất dữ liệu từ bảng đó ra file Excel gọi lfa file Thống Kê Sản Phẩm. Lưu ý File Excel này khi xuất ta phải định dạng y xì boong vậy luôn nha, có Merge dòng/cột. có màu nền màu chữ, có kẻ khung.

Để làm được bài này thì trước tiên ta phải biết cách kết nối Cơ sở dữ liệu (ta có thể học 2 Khóa độc lập C# nâng cao hoặc LINQ). Trong tip này Tui dùng LINQ.

Đồng thời để kết xuất ra File Excel, ta có rất nhiều thư viện. Tuy nhiên trong Tip này Tui sử dụng thư viện có sẵn của Microsoft khi ta cài đặt công cụ Office, đó là thư viện: Microsoft.Office.Core và Microsoft.Office.Interop.Excel

Ta tiến hành làm nha:

Bước 1: Phải có dữ liệu trước thì mới làm ra cơm phở.

Vụ CSDL các em tự xử nha, dùng version nào cũng được. Tui dùng SQL Server 2017. Để tiết kiệm thời gian cho các Em, Tui để Script của CSDL này, các Em chỉ cần Run Script 1 cái vèo cho lẹ:

Script bấm vào đây mà tải (CSDL_MAU)

Bước 2:

Tạo 1 project C# tên là “TestReportExcel” (dùng Winform hay WPF cũng được, không quan trọng). Ở đây Tui dùng Winform:

Chọn như trên đó, rùi bấm OK:

Bước 3: Kết nối CSDL bằng LINQ

Bấm chuột phải vào Project chọn Add/ New Item:

Chọn LINQ to SQL Classes, mục Name đặt CSDL_MAU.dbml (Lưu ý nếu không thấy LINQ tức là chưa biết cài Visual Studio nha)

Sau đó bấm nút Add:

Tập tin CSDL_MAU.dbml sẽ xuất hiện như trên. Ta bấm vào mục Server Explorer để kết nối CSDL:

Bấm vào nút Connect to Database:

Chọn Microsoft SQL Server rồi bấm OK:

Server Name (1): Chọn Tên Server của bạn lúc cài đặt SQL Server

Authentication (2): Chọn Windows hoặc SQL Mode. Chọn loại nào cũng được. miễn sao kết nối được

Database Name (3): Chọn Tên CSDL mà ta đã tạo CSDL_MAU

OK(4) : bấm OK để truy suất

Ta thấy CSDL_MAU xuất hiện trong Server Explorer. Bấm vào bảng SanPham Túm kéo nó vào phần mềm như hướng dẫn ở trên, ta có kết quả:

Như vậy là đã kết nối xong

Bước 4: Hiển thị dữ liệu lên Giao diện, cụ thể là GridView

Ta kéo thả 2 Button và 1 DataGridView như trên. Coding cho nút tải dữ liệu như sau:

1 2 3 4 5

private void btnTaiDuLieu_Click(object sender, EventArgs e) { CSDL_MAUDataContext context = new CSDL_MAUDataContext(); gvSanPham.DataSource = context.SanPhams.ToList(); }

Vô cùng đơn giản, chỉ cần 2 dòng lên trên thôi ta đã có dữ liệu hiển thị lên Giao diện.

Bước 5: Xuất dữ liệu ra File Excel để báo cáo (Theo đúng định dạng yêu cầu)

Ta tham chiếu thư viện như sau:

Bấm chuột phải vào References/ chọn add Reference…

Ta chọn COM, rồi tick vào 2 thư viện như trên: Microsoft Office 16.0 Object Library,  Microsoft Excel 16.0 Object Library (Dĩ nhiên do Tui dùng Office 2016 nên có cái này, còn bạn sài 2010, 2013… thì nó phải khác nha, thấy số 14, 15 thì chọn. tên na ná như vậy).

BẤM OK, thấy được thư viện tham chiếu như bên dưới là Ngon lành cành đào:

Bây giờ Coding Xuất Excel nha (Lưu ý là khi dữ liệu lớn thì bắt buộc phải viết dạng đa tiến trình, cụ thể là dùng BackgroundWorker để export Step by Step giúp chạy realtime mà không bị Treo phần mềm). Tuy nhiên để đơn giản thì Tui lược bỏ phần đa tiến trình này đi, bạn muốn dùng thì xem Khóa C# nâng cao có hướng dẫn phần Background Worker, hoặc ở một bài khác Tui sẽ ví dụ cách dùng Đa tiến trình để chạy xuất file báo cáo.

Quay lại yêu cầu của bài toán, là sau khi chạy thì kết quả xuất Báo cáo File Excel phải y xì như bên dưới:

Quan sát : Dòng đầu tiền (dòng có số thứ tự là 1. Cột chạy từ A->E : Và Trộn các cột này lại với nhau thành dòng tiêu đề “Thống kê sản phẩm“.

Bên dưới dòng 2 và 3 là Tên Trường (tên cột) để hiển thị thông tin chi tiết. Thì lưu ý  Cột A, B, C là trộn 2 dòng. Còn cột D, E thì dòng bên trên là trộn 2 cột để ra chữ “Giá sản phẩm”, và ngay bên dưới có 2 cột là “Giá bán” và “Giá Xuất”.

==> Đây chính là chỗ khó nhất để xuất file báo cáo này.

Còn tất cả dữ liệu bên dưới ta dùng vòng lặp xuất ra là xong.

sau khi xuất dữ liệu xong thì phải Kẻ bảng cũng như tô màu nền màu chữ nếu có.

Chi tiết Coding cho phần xuất Excel như sau:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using Excel = Microsoft.Office.Interop.Excel; using Microsoft.Office.Interop.Excel;   namespace TestReportExcel { public partial class Form1 : Form { public Form1() { InitializeComponent(); }   private void btnTaiDuLieu_Click(object sender, EventArgs e) { CSDL_MAUDataContext context = new CSDL_MAUDataContext(); gvSanPham.DataSource = context.SanPhams.ToList(); }   private void btnXuatExcel_Click(object sender, EventArgs e) { try { string saveExcelFile = @"f:\excel_report.xlsx";   Excel.Application xlApp = new Excel.Application();   if (xlApp == null) { MessageBox.Show("Lỗi không thể sử dụng được thư viện EXCEL"); return; } xlApp.Visible = false;   object misValue = System.Reflection.Missing.Value;   Workbook wb = xlApp.Workbooks.Add(misValue);   Worksheet ws = (Worksheet)wb.Worksheets[1];   if (ws == null) { MessageBox.Show("Không thể tạo được WorkSheet"); return; } int row = 1; string fontName = "Times New Roman"; int fontSizeTieuDe = 18; int fontSizeTenTruong = 14; int fontSizeNoiDung = 12; //Xuất dòng Tiêu đề của File báo cáo: Lưu ý Range row1_TieuDe_ThongKeSanPham = ws.get_Range("A1", "E1"); row1_TieuDe_ThongKeSanPham.Merge(); row1_TieuDe_ThongKeSanPham.Font.Size = fontSizeTieuDe; row1_TieuDe_ThongKeSanPham.Font.Name = fontName; row1_TieuDe_ThongKeSanPham.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; row1_TieuDe_ThongKeSanPham.Value2 = "Thống kê sản phẩm";   //Tạo Ô Số Thứ Tự (STT) Range row23_STT = ws.get_Range("A2", "A3");//Cột A dòng 2 và dòng 3 row23_STT.Merge(); row23_STT.Font.Size = fontSizeTenTruong; row23_STT.Font.Name = fontName; row23_STT.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; row23_STT.Value2 = "STT";   //Tạo Ô Mã Sản phẩm : Range row23_MaSP = ws.get_Range("B2", "B3");//Cột B dòng 2 và dòng 3 row23_MaSP.Merge(); row23_MaSP.Font.Size = fontSizeTenTruong; row23_MaSP.Font.Name = fontName; row23_MaSP.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; row23_MaSP.Value2 = "Mã Sản Phẩm"; row23_MaSP.ColumnWidth = 20;   //Tạo Ô Tên Sản phẩm : Range row23_TenSP = ws.get_Range("C2", "C3");//Cột C dòng 2 và dòng 3 row23_TenSP.Merge(); row23_TenSP.Font.Size = fontSizeTenTruong; row23_TenSP.Font.Name = fontName; row23_TenSP.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; row23_TenSP.ColumnWidth = 20; row23_TenSP.Value2 = "Tên Sản Phẩm";   //Tạo Ô Giá Sản phẩm : Range row2_GiaSP = ws.get_Range("D2", "E2");//Cột D->E của dòng 2 row2_GiaSP.Merge(); row2_GiaSP.Font.Size = fontSizeTenTruong; row2_GiaSP.Font.Name = fontName; row2_GiaSP.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; row2_GiaSP.Value2 = "Giá Sản Phẩm";   //Tạo Ô Giá Nhập: Range row3_GiaNhap = ws.get_Range("D3", "D3");//Ô D3 row3_GiaNhap.Font.Size = fontSizeTenTruong; row3_GiaNhap.Font.Name = fontName; row3_GiaNhap.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; row3_GiaNhap.Value2 = "Giá Nhập"; row3_GiaNhap.ColumnWidth = 20;   //Tạo Ô Giá Xuất: Range row3_GiaXuat = ws.get_Range("E3", "E3");//Ô E3 row3_GiaXuat.Font.Size = fontSizeTenTruong; row3_GiaXuat.Font.Name = fontName; row3_GiaXuat.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; row3_GiaXuat.Value2 = "Giá Xuất"; row3_GiaXuat.ColumnWidth = 20; //Tô nền vàng các cột tiêu đề: Range row23_CotTieuDe = ws.get_Range("A2", "E3"); //nền vàng row23_CotTieuDe.Interior.Color = ColorTranslator.ToOle(System.Drawing.Color.Yellow); //in đậm row23_CotTieuDe.Font.Bold = true; //chữ đen row23_CotTieuDe.Font.Color = ColorTranslator.ToOle(System.Drawing.Color.Black);   int stt = 0; row = 3;//dữ liệu xuất bắt đầu từ dòng số 4 trong file Excel (khai báo 3 để vào vòng lặp nó ++ thành 4) CSDL_MAUDataContext context = new CSDL_MAUDataContext(); foreach (SanPham sp in context.SanPhams) { stt++; row++; dynamic []arr = { stt,sp.MaSP,sp.TenSP,sp.DonGiaNhap,sp.DonGiaXuat}; Range rowData = ws.get_Range("A"+row, "E"+row);//Lấy dòng thứ row ra để đổ dữ liệu rowData.Font.Size = fontSizeNoiDung; rowData.Font.Name = fontName; rowData.Value2 = arr; } //Kẻ khung toàn bộ BorderAround(ws.get_Range("A2", "E" + row));   //Lưu file excel xuống Ổ cứng wb.SaveAs(saveExcelFile);   //đóng file để hoàn tất quá trình lưu trữ wb.Close(true, misValue, misValue); //thoát và thu hồi bộ nhớ cho COM xlApp.Quit(); releaseObject(ws); releaseObject(wb); releaseObject(xlApp);   //Mở File excel sau khi Xuất thành công System.Diagnostics.Process.Start(saveExcelFile); } catch (Exception ex) { MessageBox.Show(ex.Message); } } //Hàm kẻ khung cho Excel private void BorderAround(Range range) { Borders borders = range.Borders; borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous; borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous; borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous; borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous; borders.Color = Color.Black; borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous; borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous; borders[XlBordersIndex.xlDiagonalUp].LineStyle = XlLineStyle.xlLineStyleNone; borders[XlBordersIndex.xlDiagonalDown].LineStyle = XlLineStyle.xlLineStyleNone; } //Hàm thu hồi bộ nhớ cho COM Excel private static void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { Console.WriteLine(ex.Message); obj = null; } finally { GC.Collect(); } } } }

Chạy lên ta sẽ có kết quả như mong muốn.

Đây là source code đầy đủ: Tải code

Ngoài ra Tui có tạo 1 Facebook Fanpage để hướng dẫn cũng như trả lời các thắc mắc của Sinh viên về các kiến thức lập trình mà Tui đang đảm nhiệm, các bạn có thể Like + Follow để dễ dàng theo dõi các Tip cũng như có thể tham gia thảo luận: https://www.facebook.com/communityuni

Chúc các bạn thành công!