[已解决]csv檔案轉換圖表速度很慢

ipmitool · 发表于 2018-7-5 13:43:32

本帖最后由 ipmitool 于 2018-7-26 09:40 编辑

請問各位大大, 如何讓圖表顯示速度加快啊? 只讀100筆資料就要40秒了, 如果要讀一萬筆, 可能要花十幾分鐘..
程式碼及csv檔案如附件, 請參閱~

[mw_shl_code=applescript,true]#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[100]
Global $arValues[100]

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, [Optional]
;                $sTitle2 - Subtitle of the graph, [Optional]
;                $iX - left margin, [Optional]
;                $iY - top margin, [Optional]
;                $iW - width of the graph, [Optional]
;                $iH - height of the graph, [Optional]
;
; 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[UBound($arNames)]
   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[1]) Then
      $Canvas = _CreateBarCanvas($iX+15, $iY+15, $iW-110, $iH-60)
      GUICtrlCreateGroup("Legend", $Canvas[0]+$Canvas[2]+10, $Canvas[1]-5, ($iW-$Canvas[2])/2-10, $Canvas[3]+$Canvas[4]+5)
      For $i=0 To UBound($arNames)-1
         GUICtrlCreateLabel($arNames[$i],$Canvas[0]+$Canvas[2]+20, $Canvas[1]+10+(($Canvas[3]+$Canvas[4]-15)/UBound($arNames))*$i,($iW-$Canvas[2])/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[2]/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]-0.5*$Canvas[4], $Canvas[1]+0.5*$Canvas[4], $Canvas[2], $Canvas[3],0)
   For $i=0 To UBound($arValues)-1
      For $j=$Canvas[4]/2.5 To 0 Step -0.5
         GUICtrlSetGraphic(-1, $GUI_GR_COLOR, $arColours[$i]-0x333333, $arColours[$i])
         GUICtrlSetGraphic(-1, $GUI_GR_RECT, 5+($Canvas[2]/UBound($arValues))*$i+$j, -2+$Canvas[3]-$j, 0.5*($Canvas[2]/UBound($arValues)), -($Canvas[3]/_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[0]+($Canvas[2]/UBound($arValues))*$i, $iH-$Canvas[1]+$Canvas[4]-10-(($Canvas[3]/_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[3] Step $Canvas[3]/5
      GUICtrlCreateLabel(($i/$Canvas[3])*_RoundUp($max),$Canvas[0]-65,$Canvas[1]+$Canvas[3]+$Canvas[4]-$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[/mw_shl_code]

kk_lee69 · 发表于 2018-7-6 01:04:57

本帖最后由 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 這樣要挑選應該快很多

kk_lee69 · 发表于 2018-7-6 02:10:28

找到最佳解了秒殺

來去睡覺!!

ipmitool · 发表于 2018-7-6 07:43:35

太帥了大大, 可是如果500筆圖表就會看不清楚, 這個有辦法處理嗎?

ipmitool · 发表于 2018-7-6 07:46:34

太帥了大大, 可是如果500筆圖表就會看不清楚, 這個有辦法處理嗎?

chzj589 · 发表于 2018-7-17 13:21:05

本帖最后由 chzj589 于 2018-7-17 13:23 编辑

ipmitool 发表于 2018-7-6 07:46
太帥了大大, 可是如果500筆圖表就會看不清楚, 這個有辦法處理嗎?

控件_CreateBarChart的宽度不够。
解决方法：
增加_CreateBarChart的宽度，添加水平滚动条

resethdd · 发表于 2018-7-18 20:54:22

好厉害

298311657 · 发表于 2018-7-30 21:06:39

这种展示数据的方式有意义吗？完全无法阅读。个人感觉做成分页模式会比较好，每页展示有限的数据便于阅读比较，如需快速查阅特定数据，可以增加搜索模式

		自动登录	找回密码
密码			加入

[系统综合] [已解决]csv檔案轉換圖表速度很慢

本帖子中包含更多资源

本帖子中包含更多资源

点评

本帖子中包含更多资源

本帖子中包含更多资源

本帖子中包含更多资源

点评