VB.NET ออกรายงาน Excel แบบรวดเร็ว

โดยปกติเมื่อเราพัฒนาโปรแกรมทางธุรกิจ ที่หนีไม่พ้นก็คือต้องออกรายงานครับ และโดยส่วนใหญ่เรามักจะใช้ Crystal Report เพราะสะดวก ง่าย และรวดเร็วครับ ที่สำคัญเรายังสามารถ Export ข้อมูลออกเป็น Excel, Word หรือ PDF ก็ยังได้

แต่ถ้าเราไม่สามารถใช้ Crystal Report ได้ละ เช่นลูกค้าไม่มี License เป็นต้น ทางเลือกหนึ่งก็คือออกรายงานเป็น Excel ครับ ถ้าลูกค้ามี Licese ของ Microsoft Excel อยู่แล้วก็ไม่ยาก หรือเราอาจใช้ OWC (Office Web Componet) แทนก็ได้ ซึ่ง OWC สามารถดาวน์โหลดได้จากเวบของ Microsoft ครับ (ปัจจุบันผมใช้ OWC11) ซึ่งสามารถออกรายงาน รวมถึงสร้างกราฟได้ด้วย

แต่ข้อเสียของการใช้ Excel หรือ OWC คือ ช้าครับ แถมกิน CPU สูงมากโดยเฉพาะถ้าต้องวนลูปเพื่อใส่ข้อมูล วันนี้ผมจะนำเสนออีกวิธีในการออกรายงาน Excel ครับ นั่นคือเรานำข้อมูลที่ต้องการไปใส่ใน delimited file ก่อนเช่น .csv (CSV file), .txt (Tab delimited file), .prn (Spaced delimited, Formatted Text) หรือ .txt ที่เรากำหนด delimeter เอง เช่น ; หรือ : เป็นต้นครับ จากนั้นค่อยใช้ Excel หรือ OWC เปิดไฟล์พวกนี้ขึ้นมาเพื่อทำการตกแต่งรายงานให้สวยงาม เช่น ใส่ Header, กำหนดขนาด หรือ layout, ตีเส้นตาราง เป็นต้นครับ

ทีนี้มาลองดูการออกรายงานด้วย Tab Delimited ละกันครับ ผมเริ่มจากสร้าง Console Application มาตัวหนึ่ง แล้วก็ Add Reference ของ Microsoft Excel 12.0 Object Library ครับ



จากนั้นก็สร้างข้อมูลสำหรับทดสอบ โดยสร้าง Employee Class ขึนมา และ EmployeeManager Class สำหรับ สร้าง List ของ Employee ขนาด 100,000 ข้อมูลครับ

Public Class Employee
    Public EmployeeID As String
    Public FirstName As String
    Public LastName As String
    Public BirthDay As Date
    Public Salary As Double
End Class

Public Class EmployeeManager
    Public Shared Function GetEmployees() As IList(Of Employee)
        Dim employees As New List(Of Employee)
        Dim emp As Employee = Nothing
        For iRecord As Integer = 0 To 100000
            emp = New Employee With {.EmployeeID = iRecord, .FirstName = "Test" &  iRecord, .LastName = "LName", .BirthDay = DateSerial(1990, 1, 1), .Salary = 2000.0 + CDbl(iRecord)}
            employees.Add(emp)
        Next
        Return employees
    End Function
End Class

ทีนี้ก็มาเขียนโค้ดสำหรับการทดสอบกันครับ

Module Module1
    Const EU_DATE_FORMAT As String = "dd-MM-yyyy hh:mm:ss"
    Dim employees As IList(Of Employee)
    Dim startDate As Date = Now
    Dim endDate As Date = Nothing

    Private Sub DecorateExcel(ByVal xlsBook As Microsoft.Office.Interop.Excel.Workbook)

        Dim xlsSheet As Microsoft.Office.Interop.Excel.Worksheet = Nothing
        xlsSheet = xlsBook.ActiveSheet
        xlsSheet.Range("A:E").EntireColumn.AutoFit()

        With xlsSheet.Range("A1").Font
            .Bold = True
            .Size = "14"
        End With

        With xlsSheet.Range("A2").Font
            .Bold = True
            .Size = "12"
        End With

        With xlsSheet.Range("A3:E3")
            .Interior.Color = System.Drawing.Color.Silver.ToArgb
            .Font.Bold = True
        End With

        xlsSheet.Range("E:E").NumberFormat = "#,##0.00"

        Dim r As Microsoft.Office.Interop.Excel.Range
        r = xlsSheet.Range("A3:E20004")

        With r.Borders
            .LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
            .Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin
            .ColorIndex = 0
        End With

    End Sub

    Private Sub ExportByTAB()
        startDate = Now
        Console.WriteLine("Export by Tab delimited")
        Console.WriteLine("Start at " & startDate.ToString(EU_DATE_FORMAT))

        Dim _fileNameTAB As String = "C:\test.txt"
        Dim _fileName As String = "c:\testTAB.xlsx"

        Dim fs As System.IO.FileStream = System.IO.File.Create(_fileNameTAB)
        Dim writer As New System.IO.StreamWriter(fs)

        writer.WriteLine("List of Employee")
        writer.WriteLine("As of " & Now.ToString("dd MMM yyyy"))
        writer.WriteLine("Employee ID" & vbTab & "First Name" & vbTab & "Last Name" & vbTab & "Birth Date" & vbTab & "Salary")

        For Each emp As Employee In employees
            writer.WriteLine(String.Concat(emp.EmployeeID, vbTab, emp.FirstName, vbTab, emp.LastName, vbTab, emp.BirthDay.ToString("yyyy-MM-dd"), vbTab, emp.Salary))
        Next

        writer.Close()
        writer.Dispose()
        fs.Close()
        fs.Dispose()

        ' Open txt file with Excel for decoration
        Dim xlsAPP As New Microsoft.Office.Interop.Excel.Application
        Dim xlsBook As Microsoft.Office.Interop.Excel.Workbook
        xlsAPP.Visible = False
        xlsBook = xlsAPP.Workbooks.Open(_fileNameTAB)
        DecorateExcel(xlsBook)

        ' Save as Excel2007
        If System.IO.File.Exists(_fileName) Then System.IO.File.Delete(_fileName)
        xlsBook.SaveAs(_fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook)
        xlsBook.Close(SaveChanges:=False)

        xlsAPP.Quit()

        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsAPP)
        GC.SuppressFinalize(xlsAPP)
        GC.Collect()
        xlsBook = Nothing
        xlsAPP = Nothing

        System.IO.File.Delete(_fileNameTAB)

        endDate = Now
        Console.WriteLine("Finish at " & endDate.ToString(EU_DATE_FORMAT))
        Console.WriteLine("Total " & (endDate - startDate).TotalMilliseconds & " milliseconds")
        Console.WriteLine("***********************")

    End Sub

    Sub Main()

        employees = EmployeeManager.GetEmployees()
        Console.WriteLine("Testing for " & employees.Count - 1 & " records.")
        Console.WriteLine("")
        ExportByTAB()
        Console.WriteLine("Finish test.")
        Console.ReadLine()

    End Sub

End Module

ลองรันผลทดสอบดูครับ เครื่องผม Pentium 4 2.66GHz RAM 2 GB ข้อมูลขนาด 100,000 เรคคอร์ดใช้เวลา 7 วินาทีกว่าๆ



ข้อมูลขนาด 5,000 เรคอร์ด ใช้เวลา 2.1 วินาที


ลองดูผลลัพธ์ครับ


เรียบร้อยครับ ทีนี้ถ้าเราอยากจัด Layout สวยๆ เราสามารถเปิด Excel แล้วสั่งบันทึก Macro แล้วค่อยเอา source code ที่ได้ไปใส่เพิ่มใน DecorateExcel Function ครับ

ปล.
1. ผมลอง export โดยใช้ CSV ก็ได้ความเร็วไม่ต่างกันมากครับ
2. ผมลองวนลูปเก็บข้อมูลเข้า StringBuilder ก่อน แล้วค่อยเขียนไฟล์ ปรากฏว่าใช้เวลามากกว่าเปิดไฟล์แล้ววนลูปเขียนเข้าไปตรงๆเลยครับ