[VISUAL BASIC] Instructions for exporting the database to a formatted excel file

Hôm nay, mình xin hướng các bạn xuất file excel có định dạng từ cơ sở dữ liệu. vd: kẻ ô viền, chọn font chữ đậm, canh lề trái phải, canh độ rộng và độ cao của từng cell, chèn hình ảnh vào file excel.

[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