[Visual Basic] Hướng dẫn xuất database ra file excel có định dạng
Bỏ những thuộc tính của ứng dụng excel như: Gridline, Formular (Thanh công thức) ....VIDEO DEMO ỨNG DỤNG
Đầu tiên bạn cần phải import thư viện vào
Dưới đây là source chương trình
- Các bạn cần import thư viện database sql server vào excel
Imports System.Data.SqlClient Imports Excel = Microsoft.Office.Interop.Excel
- Khai báo biến kết nối database và lấy đường dẫn thư mục từ file chạy (debug)
Dim con As New SqlConnection Dim directory As String = My.Application.Info.DirectoryPath
- Tạo hàm kết nối cơ sở dữ liệu
Public Sub taoketnoi() con.ConnectionString = "Data Source=DESKTOP-5ANBA4H;Initial Catalog=HOB;Integrated Security=True" con.Open() End Sub
- Tạo hàm đóng kết nối
Public Sub dongketnoi() con.Close() End Sub
- Tạo hàm lấy dữ liệu từ database, ở đây mình sử dụng datatable
Public Function LayDulieu() As System.Data.DataTable taoketnoi() Dim dt As New System.Data.DataTable Dim da As New SqlDataAdapter da.SelectCommand = New SqlCommand("select manv as [Mã NV], hoten as [Họ và tên],ngaysinh as [Ngày sinh], chucvu as [Chức vụ], tenphongban as [Phòng ban] from view_user where manv>0", con) da.Fill(dt) dongketnoi() Return dt End Function
- Hàm load dữ liệu vào datagridviewv
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click Dim dt As System.Data.DataTable = LayDulieu() DataGridView1.DataSource = dt End Sub
- Tiếp tục là viết nút xử lý database ra file excel có định dạng.
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application() If xlApp Is Nothing Then MessageBox.Show("Excel is not properly installed!!") Return End If Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value Dim chartRange As Excel.Range xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Sheets("sheet1") xlWorkSheet.Shapes.AddPicture(directory & "logo.png", _ Microsoft.Office.Core.MsoTriState.msoFalse, _ Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 79, 59) xlWorkSheet.Range("a2", "f2").Merge() xlWorkSheet.Cells(2, 1) = "DANH SÁCH THÀNH VIÊN LẬP TRÌNH VB.NET" xlWorkSheet.Cells(2, 1).VerticalAlignment = Excel.Constants.xlCenter xlWorkSheet.Cells(2, 1).HorizontalAlignment = Excel.Constants.xlCenter xlWorkSheet.Cells(2, 1).Font.Size = 18 xlWorkSheet.Cells(2, 1).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue) xlWorkSheet.Cells(2, 1).Font.Bold = True Dim dt As System.Data.DataTable = LayDulieu() Dim dc As DataColumn Dim dr As DataRow Dim colIndex As Integer = 1 Dim rowIndex As Integer = 3 Dim stt As Integer = 0 '// tên tiêu đề table xlWorkSheet.Cells(4, 1) = "STT" For Each dc In dt.Columns colIndex = colIndex + 1 xlWorkSheet.Cells(4, colIndex) = dc.ColumnName Next 'export the rows For Each dr In dt.Rows stt = stt + 1 rowIndex = rowIndex + 1 colIndex = 1 For Each dc In dt.Columns colIndex = colIndex + 1 xlWorkSheet.Cells(rowIndex + 1, 1) = "'" & stt & "." If colIndex = 2 Then xlWorkSheet.Cells(rowIndex + 1, colIndex) = "'" & dr(dc.ColumnName) Else xlWorkSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName) End If Next Next xlWorkSheet.Range("a1", "z200").RowHeight = 20 xlWorkSheet.Cells(4, 1).VerticalAlignment = Excel.Constants.xlCenter xlWorkSheet.Cells(4, 1).HorizontalAlignment = Excel.Constants.xlCenter xlWorkSheet.Range("a5", "a200").HorizontalAlignment = Excel.Constants.xlCenter xlWorkSheet.Range("a5", "a200").Font.Bold = True chartRange = xlWorkSheet.Range("a4", "f4") chartRange.EntireColumn.AutoFit() chartRange.Font.Bold = True chartRange.RowHeight = 25 chartRange.Font.Size = 14 xlWorkSheet.Range("a1", "z20").VerticalAlignment = Excel.Constants.xlCenter xlWorkSheet.Range("a1", "z1").HorizontalAlignment = Excel.Constants.xlCenter chartRange.HorizontalAlignment = Excel.Constants.xlCenter chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red) chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow) xlWorkSheet.Range("c4", "c149").Font.Bold = True xlWorkSheet.Range("a4", "f149").Borders.LineStyle = Excel.XlLineStyle.xlContinuous xlWorkSheet.Range("a4", "f149").Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange) xlWorkSheet.Range("a4", "f149").Borders.Weight = 2D xlWorkSheet.Cells.EntireColumn.AutoFit() xlApp.ActiveWindow.DisplayGridlines = False xlApp.ActiveWindow.DisplayFormulas = False xlApp.ActiveWindow.DisplayHeadings = False xlWorkBook.SaveAs(directory & "danhsachthanhvien.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue) xlWorkBook.Close(True, misValue, misValue) xlApp.Quit() releaseObject(xlApp) releaseObject(xlWorkBook) releaseObject(xlWorkSheet) Process.Start(directory & "danhsachthanhvien.xls") End Sub
- Và cuối cùng là hàm thoát đối tượng kết nối với excel
Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub
Chúc mọi người thành công với thủ thuật trên.
Theo LapTrinhVB.Net
Comments
Như video hướng dẫn đó thì file execl đó mặc định hay tuỳ biền file execl khác đc k e?
ReplyDeleteGood code, tks for share
ReplyDelete