Hướng dẫn xuất database ra file excel có định dạng

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.

Bỏ những thuộc tính của ứng dụng excel như: Gridline, Formular (Thanh công thức) ....

Dưới đây là video demo của ứ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 datagridview

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 các bạn thành công!

Link download source code