แต่ถ้าเราไม่สามารถใช้ 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 ข้อมูลครับ
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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 |
ทีนี้ก็มาเขียนโค้ดสำหรับการทดสอบกันครับ
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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 ก่อน แล้วค่อยเขียนไฟล์ ปรากฏว่าใช้เวลามากกว่าเปิดไฟล์แล้ววนลูปเขียนเข้าไปตรงๆเลยครับ