Thứ Tư, 3 tháng 4, 2019

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ẹ:
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!

0 nhận xét:

Đăng nhận xét