[已解决]csv檔案轉換圖表速度很慢
本帖最后由 ipmitool 于 2018-7-26 09:40 编辑請問各位大大, 如何讓圖表顯示速度加快啊? 只讀100筆資料就要40秒了, 如果要讀一萬筆, 可能要花十幾分鐘..
程式碼及csv檔案如附件, 請參閱~
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\sdr_list_48hr.csv")
If @error Then
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\sdr_list_48hr.csv'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)
Exit
EndIf
; Read data from a single cell on the active sheet of the specified workbook
;If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Data successfully read." & @CRLF & "Value of cell B2: " & $sResult)
Global $arNames
Global $arValues
Example2()
Func Example2()
Local $gui = GUICreate("Example 2: 3D Bar Graph", 950, 650)
GUISetBkColor(0xffffff)
For $i=0 To 13
Local $sName000 = _Excel_RangeRead($oWorkbook, Default, "A"&$i+2)
$arNames[$i]=$sName000
Next
For $iV=0 To 99
Local $sResult000 = _Excel_RangeRead($oWorkbook, Default, "R"&$iV+2)
$arValues[$iV]=$sResult000
Next
_CreateBarChart("3D Bar Chart","Example 2",20,20,900,600)
GUISetState()
While 1
Sleep(10)
Switch GUIGetMsg()
Case $GUI_EVENT_CLOSE
GUIDelete($gui)
;Example3()
Exit
EndSwitch
WEnd
EndFunc
; #FUNCTION# ====================================================================================================
; Name...........:_CreateBarChart
; Description....:Create 3D bar graph with native AutoIt functions
; Syntax.........:_CreateBarChart($sTitle1="",$sTitle2="",$iX=20,$iY=20,$iW=400,$iH=400)
; Parameters.....:$sTitle1 - Title of the graph,
; $sTitle2 - Subtitle of the graph,
; $iX - left margin,
; $iY - top margin,
; $iW - width of the graph,
; $iH - height of the graph,
;
; Return values..:none
;
; Author.........:Mihai Iancu (taietel at yahoo dot com)
; Remarks........:$array = ["name",value]
; - one with numbers/names (try to uncoment/coment them)
; - one with values
; ===============================================================================================================
Func _CreateBarChart($sTitle1="",$sTitle2="",$iX=20,$iY=20,$iW=400,$iH=400)
Local $max=_ArrayMax($arValues,1);compare numerically
;Local $values = UBound($arValues)
Local $arColours
Local $color
For $i=0 To UBound($arValues)-1
$color = (Random(100, 255, 1) * 0x10000) + (Random(100, 255, 1) * 0x100) + Random(100, 255, 1)
$arColours[$i]=$color
Next
;set default values for the frame
If $iX=-1 Then $iX=20
If $iY=-1 Then $iY=20
If $iW=-1 Then $iW=400
If $iH=-1 Then $iH=400
;create frame for the chart
$grp = GUICtrlCreateGroup("", $iX, $iY, $iW, $iH)
;title
GUICtrlCreateLabel($sTitle1,$iX+15, $iY+10,$iW-30,-1,$SS_CENTER)
GUICtrlSetColor(-1,0x002244)
GUICtrlSetFont(-1, 9, 800, 0, "Arial")
GUICtrlSetBkColor(-1,$GUI_BKCOLOR_TRANSPARENT)
GUICtrlCreateLabel($sTitle2,$iX+15, $iY+25,$iW-30,-1,$SS_CENTER)
GUICtrlSetColor(-1,0x002244)
GUICtrlSetFont(-1, 8, 800, 0, "Arial")
GUICtrlSetBkColor(-1,$GUI_BKCOLOR_TRANSPARENT)
Local $Canvas
If IsString($arNames) Then
$Canvas = _CreateBarCanvas($iX+15, $iY+15, $iW-110, $iH-60)
GUICtrlCreateGroup("Legend", $Canvas+$Canvas+10, $Canvas-5, ($iW-$Canvas)/2-10, $Canvas+$Canvas+5)
For $i=0 To UBound($arNames)-1
GUICtrlCreateLabel($arNames[$i],$Canvas+$Canvas+20, $Canvas+10+(($Canvas+$Canvas-15)/UBound($arNames))*$i,($iW-$Canvas)/2,13,$SS_CENTER)
GUICtrlSetColor(-1,$arColours[$i]-0x444444)
GUICtrlSetFont(-1, 8, 800, 0, "Arial")
GUICtrlSetBkColor(-1,$arColours[$i])
Next
GUICtrlCreateGroup("", -99, -99, 1, 1)
Else
$Canvas = _CreateBarCanvas($iX+15, $iY+15, $iW-50, $iH-60)
For $i=0 To UBound($arNames)-1
GUICtrlCreateLabel($arNames[$i],$iX+52+(($Canvas/UBound($arNames)))*$i+5, $iY+$iH-30)
GUICtrlSetColor(-1,0x990000)
GUICtrlSetFont(-1, 8, 800, 0, "Arial")
GUICtrlSetBkColor(-1,$GUI_BKCOLOR_TRANSPARENT)
Next
EndIf
;draw the bars
GUICtrlCreateGraphic($Canvas-0.5*$Canvas, $Canvas+0.5*$Canvas, $Canvas, $Canvas,0)
For $i=0 To UBound($arValues)-1
For $j=$Canvas/2.5 To 0 Step -0.5
GUICtrlSetGraphic(-1, $GUI_GR_COLOR, $arColours[$i]-0x333333, $arColours[$i])
GUICtrlSetGraphic(-1, $GUI_GR_RECT, 5+($Canvas/UBound($arValues))*$i+$j, -2+$Canvas-$j, 0.5*($Canvas/UBound($arValues)), -($Canvas/_RoundUp($max))*$arValues[$i])
Next
Next
GUICtrlSetGraphic(-1,$GUI_GR_REFRESH)
For $i=0 To UBound($arValues)-1
;values from the top of the bars
GUICtrlCreateLabel($arValues[$i],$Canvas+($Canvas/UBound($arValues))*$i, $iH-$Canvas+$Canvas-10-(($Canvas/_RoundUp($max))*$arValues[$i]))
GUICtrlSetColor(-1,0x002244)
GUICtrlSetFont(-1, 7, 800, 0, "Arial")
GUICtrlSetBkColor(-1,$GUI_BKCOLOR_TRANSPARENT)
Next
;Y Axis
For $i=0 To $Canvas Step $Canvas/5
GUICtrlCreateLabel(($i/$Canvas)*_RoundUp($max),$Canvas-65,$Canvas+$Canvas+$Canvas-$i,30,-1,$SS_RIGHT)
GUICtrlSetColor(-1,0x990000)
GUICtrlSetFont(-1, 8, 800, 0, "Arial")
GUICtrlSetBkColor(-1,$GUI_BKCOLOR_TRANSPARENT)
Next
GUICtrlCreateGroup("", -99, -99, 1, 1)
EndFunc
#Region INTERNAL USE ONLY
Func _RoundUp($m)
Local $rv = Round(Ceiling($m/10)*10,-1)
;ConsoleWrite($rv&@CRLF)
Return $rv
EndFunc
Func _CreateBarCanvas($iX=0, $iY=0, $iW=400, $iH=400, $iDepthCanvas=30, $BgColor=0xEEEEEE)
Local $iXCanvas=$iX+$iDepthCanvas
Local $iYCanvas=$iY+10+2*$iDepthCanvas
Local $iWCanvas=$iW-2*$iDepthCanvas
Local $iHCanvas=$iH-2*$iDepthCanvas
Local $BgColor2 = $BgColor - 0x333333
;create bg for the bars
For $i=0 To $iDepthCanvas; Step 0.5
GUICtrlCreateGraphic($iXCanvas+$i, $iYCanvas-$i, $iWCanvas, $iHCanvas, 0)
GUICtrlSetBkColor(-1, $BgColor)
GUICtrlSetColor(-1, $BgColor2)
Next
GUICtrlSetGraphic(-1, $GUI_GR_MOVE, 0, $iHCanvas)
GUICtrlSetGraphic(-1, $GUI_GR_COLOR, $BgColor)
GUICtrlSetGraphic(-1, $GUI_GR_LINE, -$iDepthCanvas-1, $iHCanvas+$iDepthCanvas+1)
;horizontal grid
For $i=0 To $iHCanvas Step $iHCanvas/5
GUICtrlSetGraphic(-1, $GUI_GR_MOVE, 0, $i)
GUICtrlSetGraphic(-1, $GUI_GR_COLOR, $BgColor2)
GUICtrlSetGraphic(-1, $GUI_GR_LINE, $iWCanvas, $i)
GUICtrlSetGraphic(-1, $GUI_GR_MOVE, 0, $i)
GUICtrlSetGraphic(-1, $GUI_GR_COLOR, $BgColor)
GUICtrlSetGraphic(-1, $GUI_GR_LINE, -$iDepthCanvas, $i+$iDepthCanvas)
Next
Local $Canvas = StringSplit($iXCanvas+$iDepthCanvas &"|"& $iYCanvas-$iDepthCanvas&"|"&$iWCanvas&"|"&$iHCanvas&"|"&$iDepthCanvas,"|",2)
;ConsoleWrite($iXCanvas+$iDepthCanvas &"|"& $iYCanvas-$iDepthCanvas&"|"&$iWCanvas&"|"&$iHCanvas&@CRLF)
Return $Canvas
EndFunc
#EndRegion INTERNAL USE ONLY
本帖最后由 kk_lee69 于 2018-7-6 01:19 编辑
你的問題出在兩個地方
1. CSV 檔案太大 有四萬多筆資料光是開啟 就很累 別說讀取資料了
2. 如果要快可以考慮 把EXCEL 當作資料庫 來處理....或者 使用
$eExcel = _ExcelBookOpen(@ScriptDir & "\sdr_list_48hr.CSV",0,True) ;打開EXCEL表
With $eExcel.ActiveSheet;worksheets("")
$eTempArray=$eExcel.application.intersect(.range("2:101"),.range("R:R")).value ;
EndWith
這個方式 直接 把 EXCEL 裡面某個範圍資料 變成數組
又或者 建立個MDB (ACCESS 的格式 然後 連結好 CSV檔案 使用連結方式 這樣 以後換 CSV 就可以)
使用資料庫方式讀取 MDB這樣要挑選 應該快很多
找到最佳解了秒殺
來去睡覺!!
太帥了大大, 可是如果500筆圖表就會看不清楚, 這個有辦法處理嗎?
太帥了大大, 可是如果500筆圖表就會看不清楚, 這個有辦法處理嗎?
本帖最后由 chzj589 于 2018-7-17 13:23 编辑
ipmitool 发表于 2018-7-6 07:46
太帥了大大, 可是如果500筆圖表就會看不清楚, 這個有辦法處理嗎?
控件_CreateBarChart的宽度不够。
解决方法:
增加_CreateBarChart的宽度,添加水平滚动条
好厉害{:face (316):} 这种展示数据的方式有意义吗?完全无法阅读。个人感觉做成分页模式会比较好,每页展示有限的数据便于阅读比较,如需快速查阅特定数据,可以增加搜索模式
页:
[1]