การเขียนโปรแกรม 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

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

100

101

102

103

104

105

106

107

										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 ก่อน แล้วค่อยเขียนไฟล์ ปรากฏว่าใช้เวลามากกว่าเปิดไฟล์แล้ววนลูปเขียนเข้าไปตรงๆเลยครับ

สำนักวิทยบริการและเทคโนโลยีสารสนเทศ (สวส.)

Office of Academic Resources and Information Technology

Office of Academic Resources and Information Technology

การเขียนโปรแกรมVB.NET เพื่อออกรายงานเป็น Excel