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