Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

ExcelChart UDF - Example Scripts

Excel Chart Examples

  • Please log in to reply
14 replies to this topic

#1 water

water

    ?

  • MVPs
  • 15,759 posts

Posted 02 February 2012 - 08:52 AM

In this thread you will find some useful scripts based on the ExcelChart UDF (for download please see my signature).
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki







#2 GreenCan

GreenCan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 325 posts

Posted 03 February 2012 - 08:37 PM

A small example of a Saddle chart (for more information see http://www.horsesaddleshop.com/saddle-seat-size-chart.html :) )
Greencan

PS see also the other published examples in the ExcelChart Download topic

Attached Thumbnails

  • saddle chart.jpg

Attached Files



#3 water

water

    ?

  • MVPs
  • 15,759 posts

Posted 16 February 2012 - 05:33 PM

Version 0.2.2.0 of the UDF has been released.

Have fun testing!

For download please see my signature.
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#4 GreenCan

GreenCan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 325 posts

Posted 17 February 2012 - 11:33 AM

This is an example of a Thermometer Chart created with ExcelChart function.
GreenCan

Attached Thumbnails

  • ThermometerChart1.jpg

Attached Files


Edited by GreenCan, 17 February 2012 - 11:34 AM.


#5 GreenCan

GreenCan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 325 posts

Posted 17 February 2012 - 02:30 PM

Updated version of Gauge Chart (requires ExcelChart v 0.2.2.0)

_XLChart_Example_Gauge Chart.au3
AutoIt         
#include "ExcelChart.au3" #include <Excel.au3> #include <Array.au3> Opt('MustDeclareVars', 1) Global $iXLC_Debug = 0 ; ***************************************************************************** ; Create example environment ; ***************************************************************************** Global $oExcel = _ExcelBookNew(1) If @error <> 0 Then Exit MsgBox(16, "Excel Chart Example Script", "Error " & @error & " returned by _ExcelBookNew!") ; ***************************************************************************** ; Excel Version Check - exit if earlier than Excel 2007 ; ***************************************************************************** If _XLChart_Version($oExcel) < 12 Then Exit MsgBox(16, "Excel Gauge Chart Example Script", "The installed Excel version is not supported by this UDF!" & @CRLF & "Version must be >= 12 (Excel 2007).") #region declare Local $sCell, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $sFormula, $sFunction, _ $iEdgeLeft, $iEdgeTop, $iEdgeBottom, $iEdgeRight, $iEdgeInsideH, $iEdgeInsideV #endregion declare ; New function to be developed $oExcel.ActiveWindow.DisplayGridlines = False ; ***************************************************************************** ; Set title ; ***************************************************************************** $sCell = "A1" _ExcelWriteCell($oExcel, "Gauge Chart Example", $sCell) ; set row height _ExcelRowHeightSet($oExcel, "1:1", 30) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelRowHeightSet' on line " & @ScriptLineNumber) ; set the font _ExcelFontSet($oExcel, $sCell, 1, 1, 1, "Arial Rounded MT Bold") If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSet' on line " & @ScriptLineNumber) ; set the font size _ExcelFontSetSize($oExcel, $sCell, 1, 1, 1, 14) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetSize' on line " & @ScriptLineNumber) ; set font property _ExcelFontSetProperties($oExcel, $sCell, 1, 1, 1, True, True, False) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetProperties' on line " & @ScriptLineNumber) ; set horizontal alignment of title only _ExcelHorizontalAlignSet($oExcel, $sCell, 1, 1, 1, "center") If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelHorizontalAlignSet' on line " & @ScriptLineNumber) ; set vertical alignment _ExcelVerticalAlignSet($oExcel, $sCell, 1, 1, 1 , "center") If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelVerticalAlignSet' on line " & @ScriptLineNumber) ; main  Title color _ExcelFontSetColor($oExcel, $sCell, 1, 1, 1, 255, 0x8D1A1A) ; font color If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetColor' on line " & @ScriptLineNumber) _ExcelCellColorSet($oExcel, $sCell, 1, 1, 1, 255, 0x7DFFFF) ; color the background If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellColorSet' on line " & @ScriptLineNumber) ; Now Merge the main title line $sRangeOrRowStart = 1 $iColStart = 1 $iRowEnd = $sRangeOrRowStart $iColEnd = 10 _ExcelCellMerge($oExcel, True, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellMerge' on line " & @ScriptLineNumber) $sCell = "A2" _ExcelWriteCell($oExcel, "By GreenCan", $sCell) _ExcelFontSetProperties($oExcel, $sCell, 1, 1, 1, False, True, False) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetProperties' on line " & @ScriptLineNumber) _ExcelCellColorSet($oExcel, $sCell, 1, 1, 1, 255, 0x00CEFF) ; color the background If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellColorSet' on line " & @ScriptLineNumber) _ExcelCellMerge($oExcel, True, $sRangeOrRowStart + 1, $iColStart, $iRowEnd + 1, $iColEnd) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellMerge' on line " & @ScriptLineNumber) ; Disable screen updating to enhance performance _XLChart_ScreenUpdateSet($oExcel, 0) ; 2) $sCell = "H18" _ExcelWriteCell($oExcel,  "Press Escape to quit", $sCell) ; set the font size _ExcelFontSetSize($oExcel, $sCell, 1, 1, 1, 14) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetSize' on line " & @ScriptLineNumber) ; set font property _ExcelFontSetProperties($oExcel, $sCell, 1, 1, 1, True, True, False) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetProperties' on line " & @ScriptLineNumber) ; set font color _ExcelFontSetColor($oExcel, $sCell, 1, 1, 1, 255, 0x0000FF) ; font color If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetColor' on line " & @ScriptLineNumber) ; ***************************************************************************** ; Create the data for the Gauge Chart ; ***************************************************************************** ; draw borders $iEdgeLeft = 1 $iEdgeTop = 1 $iEdgeBottom = 1 $iEdgeRight = 1 $iEdgeInsideH = 1 $iEdgeInsideV = 0 _ExcelCreateBorders($oExcel, $xlThin, $sRangeOrRowStart, $iColStart, $iRowEnd + 1, $iColEnd, $iEdgeLeft, $iEdgeTop, $iEdgeBottom, $iEdgeRight, $iEdgeInsideV, $iEdgeInsideH) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCreateBorders' on line " & @ScriptLineNumber) ; set variable Data section $sRangeOrRowStart = 3 $iColStart = 2 $iRowEnd = $sRangeOrRowStart $iColEnd = $iColStart _ExcelWriteCell($oExcel, "Changeable" & @CRLF & "values:", $sRangeOrRowStart, $iColStart + 1) _ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart + 1, $iRowEnd, $iColEnd + 1,"right" ) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelHorizontalAlignSet' on line " & @ScriptLineNumber) _ExcelWriteCell($oExcel, "Gauge Value (0 to 100)", $sRangeOrRowStart + 1, $iColStart) ; _ExcelWriteCell($oExcel, 25, $sRangeOrRowStart + 1, $iColStart + 1) ; removed for the dynamic example _ExcelWriteCell($oExcel, "Red", $sRangeOrRowStart + 3, $iColStart) _ExcelWriteCell($oExcel, 45, $sRangeOrRowStart + 3, $iColStart + 1) _ExcelWriteCell($oExcel, "Yellow", $sRangeOrRowStart + 4, $iColStart) _ExcelWriteCell($oExcel, 55, $sRangeOrRowStart + 4, $iColStart + 1) ; autofit column B _ExcelColWidthSet($oExcel, "B:B", "autofit") If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelColWidthSet' on line " & @ScriptLineNumber) _ExcelColWidthSet($oExcel, "C", 12) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelColWidthSet' on line " & @ScriptLineNumber) ; set background of input fields _ExcelCellColorSet($oExcel, $sRangeOrRowStart + 1, $iColStart + 1, $iRowEnd + 1, $iColEnd + 1, 255, 0x80FF80) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellColorSet' on line " & @ScriptLineNumber) ; color the background _ExcelCellColorSet($oExcel, $sRangeOrRowStart + 3, $iColStart + 1, $iRowEnd + 4, $iColEnd + 1, 255, 0x80FF80) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function ''_ExcelCellColorSet on line " & @ScriptLineNumber) ; draw borders _ExcelCreateBorders($oExcel, $xlThin, $sRangeOrRowStart + 1, $iColStart + 1, $iRowEnd + 1, $iColEnd + 1, $iEdgeLeft, $iEdgeTop, $iEdgeBottom, $iEdgeRight, $iEdgeInsideV, $iEdgeInsideH) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCreateBorders' on line " & @ScriptLineNumber) _ExcelCreateBorders($oExcel, $xlThin, $sRangeOrRowStart + 3, $iColStart + 1, $iRowEnd + 4, $iColEnd + 1, $iEdgeLeft, $iEdgeTop, $iEdgeBottom, $iEdgeRight, $iEdgeInsideV, $iEdgeInsideH) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCreateBorders' on line " & @ScriptLineNumber) ; set Gauge Data $sRangeOrRowStart = 4 $iColStart = 9 $iRowEnd = $sRangeOrRowStart $iColEnd = $iColStart _ExcelWriteCell($oExcel, "Gauge", $sRangeOrRowStart, $iColStart) ; Font and background color _ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, 255, 0xFFFFFF) ; font color If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetColor' on line " & @ScriptLineNumber) _ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, 255, 0x8D1A1A) ; color the background If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellColorSet' on line " & @ScriptLineNumber) ; merge title _ExcelCellMerge($oExcel, True, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd + 1) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellMerge' on line " & @ScriptLineNumber) ; and set alignment _ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, "center") If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelHorizontalAlignSet' on line " & @ScriptLineNumber) _ExcelWriteCell($oExcel, "Start", $sRangeOrRowStart + 1, $iColStart) _ExcelWriteCell($oExcel, "Red", $sRangeOrRowStart + 2, $iColStart) _ExcelWriteCell($oExcel, "Yellow", $sRangeOrRowStart + 3, $iColStart) _ExcelWriteCell($oExcel, "End", $sRangeOrRowStart + 4, $iColStart) _ExcelWriteCell($oExcel, "Blank", $sRangeOrRowStart + 5, $iColStart) _ExcelWriteCell($oExcel, 0, $sRangeOrRowStart + 1, $iColStart + 1) _ExcelWriteFormula($oExcel, "=C6" , "J6") If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelWriteFormula'_ExcelWriteFormula on line " & @ScriptLineNumber) _ExcelWriteFormula($oExcel, "=C7-C6" , "J7") If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelWriteFormula' on line " & @ScriptLineNumber) _ExcelWriteFormula($oExcel, "=100-C7" , "J8") If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelWriteFormula' on line " & @ScriptLineNumber) _ExcelWriteCell($oExcel, 100, $sRangeOrRowStart + 5, $iColStart + 1) ; draw borders $iEdgeInsideV = 1 _ExcelCreateBorders($oExcel, $xlThin, $sRangeOrRowStart, $iColStart, $iRowEnd + 5, $iColEnd + 1, $iEdgeLeft, $iEdgeTop, $iEdgeBottom, $iEdgeRight, $iEdgeInsideV, $iEdgeInsideH) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCreateBorders' on line " & @ScriptLineNumber) ; set Indicator Data $sRangeOrRowStart += 7 $iColStart = 9 $iRowEnd = $sRangeOrRowStart $iColEnd = $iColStart _ExcelWriteCell($oExcel, "Indicator", $sRangeOrRowStart, $iColStart) ; Font and background color _ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, 255, 0xFFFFFF) ; font color If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelFontSetColor' on line " & @ScriptLineNumber) _ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, 255, 0x8D1A1A) ; color the background If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellColorSet' on line " & @ScriptLineNumber) ; merge title _ExcelCellMerge($oExcel, True, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd + 1) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCellMerge' on line " & @ScriptLineNumber) ; and set alignment _ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, "center") If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelHorizontalAlignSet' on line " & @ScriptLineNumber) _ExcelWriteCell($oExcel, "GV", $sRangeOrRowStart + 1, $iColStart) _ExcelWriteCell($oExcel, "Pointer", $sRangeOrRowStart + 2, $iColStart) _ExcelWriteCell($oExcel, "End", $sRangeOrRowStart + 3, $iColStart) _ExcelWriteCell($oExcel,  1, $sRangeOrRowStart + 2, $iColStart + 1) _ExcelWriteFormula($oExcel, "=C4" , "J12") If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelWriteFormula'_ExcelWriteFormula on line " & @ScriptLineNumber) $sFormula = "=200-(J12+J13)" If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_Excel_ColumnNumberToLetter' on line " & @ScriptLineNumber) ConsoleWrite("Translated function: " & $sFormula & @CR ) $sCell = _Excel_ColumnNumberToLetter($iColStart + 1) &  string($sRangeOrRowStart + 3) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_Excel_ColumnNumberToLetter' on line " & @ScriptLineNumber) _ExcelWriteFormula($oExcel, $sFormula , $sCell) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelWriteFormula' on line " & @ScriptLineNumber) ; set borders _ExcelCreateBorders($oExcel, $xlThin, $sRangeOrRowStart, $iColStart, $iRowEnd + 3, $iColEnd + 1, $iEdgeLeft, $iEdgeTop, $iEdgeBottom, $iEdgeRight, $iEdgeInsideV, $iEdgeInsideH) If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelCreateBorders' on line " & @ScriptLineNumber) ; name the sheet _ExcelSheetNameSet($oExcel, "Gauge Chart") If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_ExcelSheetNameSet' on line " & @ScriptLineNumber) ; ***************************************************************************** ; create the Gauge chart ; ***************************************************************************** _XLChart_Gauge($oExcel, 1, "B10:F27", "$J$5:$J$9", "$I$5:$I$9", "$J$12:$J$14", "$I$12:$I$14") If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '_XLChart_Gauge' on line " & @ScriptLineNumber) ; New Excel function to be developed ; protect sheet to avoid formula overwriting ; allow writing in cell C4, C6 and C7 only $oExcel.Activesheet.Range("C4").Locked = False If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '.Activesheet.Range().Locked' on line " & @ScriptLineNumber) $oExcel.Activesheet.Range("C6:C7").Locked = False If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '.Activesheet.Range().Locked' on line " & @ScriptLineNumber) ; allow writing in cell H18 for the displayed message $oExcel.Activesheet.Range("H18").Locked = False If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '.Activesheet.Range().Locked' on line " & @ScriptLineNumber) ; New Excel function to be developed ; now protect the sheet $oExcel.ActiveSheet.Protect() If @error Then MsgBox(64, "Excel Gauge Chart Example", "Error " & @error & " returned by function '.ActiveSheet.Protect()' on line " & @ScriptLineNumber) ; Now enable screen updating again so that we can see the result _XLChart_ScreenUpdateSet($oExcel, 1) ; ***************************************************************************** ; Now demo Gauge ; ***************************************************************************** HotKeySet("{ESC}", "Terminate") Local $iStart = 1, $iEnd While 1 $iEnd = Random(51, 100, 1) For $i = $iStart to $iEnd   _ExcelWriteCell($oExcel, $i, "C4") Next $iStart = Random(0, 50, 1) For $i = $iEnd to $iStart Step -1   _ExcelWriteCell($oExcel, $i, "C4") Next WEnd Exit Func Terminate() _ExcelWriteCell($oExcel,  "", "H18")     Exit EndFunc ;==>Terminate ; =============================================================================================================== ; Name...........: _XLChart_Gauge ; Description....: Create a _XLChart_Gauge chart (dashboard) ; Syntax.........: _XLChart_Gauge($oXLC_Excel, $vXLC_Worksheet, $sXLC_SizeByCells, $GaugeDataRange, $GaugeDataName, $IndicatorDataRange, $IndicatorDataName) ; Parameters ....: $oXLC_Excel     - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ;                 $vXLC_Worksheet   - Worksheet number or name to use for the chart creation (eg. 1) ;                 $sXLC_SizeByCells - The left-hand top and right-hand bottom corner of the chart (eg. "B2:K24"). ;      $GaugeDataRange  - The values range used for the Gauge chart ;      $GaugeDataName  - Header name of the range of the Gauge chart ;      $IndicatorDataRange - The values range used for the Indicator chart ;      $IndicatorDataName - Header name of the range of the Indicator chart ; Return values .: Success - Draws the Gauge chart ;                 Failure - Returns 0 and sets @error: ;                 |1 - $oXLC_Excel is not an object ;                 |2 - $vXLC_Worksheet could not be found ;                 |3 - Doughnut _XLChart_ChartCreate error. See @extended for details ;                 |4 - Doughnut _XLChart_DoughnutGroupSet error. See @extended for details ;                 |5 - Doughnut _XLChart_FillSet error. See @extended for details ;                 |6 - Pie _XLChart_ChartCreate error. See @extended for details ;                 |7 - Pie _XLChart_PieGroupSet error. See @extended for details ;                 |8 - Pie _XLChart_FillSet error. See @extended for details ;                 |9 - Pie _XLChart_LineSet error. See @extended for details ;                 |10 - Pie _XLChart_DatalabelSet error. See @extended for details ;                 |11 - Pie _XLChart_ObjectDelete error. See @extended for details ;                 |12 - Pie DataLabel.Text error. See @extended for details ; Authors........: GreenCan ; Modified ......: ; Remarks .......:  ; Enumeration XlReferenceStyle ;     Global Const $xlA1 = 1 ;     Global Const $xlR1C1 = -4150 ;<== Already defined ; Related .......: ; Link ..........: ; Example .......: Yes ; =============================================================================================================== Func _XLChart_Gauge($oXLC_Excel, $vXLC_Worksheet, $sXLC_SizeByCells, $GaugeDataRange, $GaugeDataName, $IndicatorDataRange, $IndicatorDataName) If Not IsObj($oXLC_Excel) Then Return SetError(1, 0, 0) ; Activate worksheet Local $oXLC_Sheet = $oXLC_Excel.Worksheets($vXLC_Worksheet) If @error Then Return SetError(2, @error, 0) Local $sXLC_SheetName = $oXLC_Excel.Worksheets($vXLC_Worksheet).Name If @error Then Return SetError(2, @error, 0) ; ***************************************************************************** ; 1. Doughnut chart ; ***************************************************************************** Local $Graph_position = "='" & $sXLC_SheetName & "'!" & $sXLC_SizeByCells $GaugeDataRange = "='" & $sXLC_SheetName & "'!" & $GaugeDataRange $GaugeDataName = "='" & $sXLC_SheetName & "'!" & $GaugeDataName Local $oChart1 = _XLChart_ChartCreate($oXLC_Excel, "Gauge Chart", $xlDoughnut, $Graph_position, "", "", $GaugeDataRange, $GaugeDataName, False, "Gauge") If @error Then Return SetError(3, @error, 0) ; Chart rotate 270 degrees _XLChart_DoughnutGroupSet($oChart1.ChartGroups(1), 270) If @error Then Return SetError(4, @error, 0) ; set chart 3 series 1 color and transparency _XLChart_FillSet($oChart1.SeriesCollection(1).Points(5), 0xFFFFFF, Default, False, Default, Default, Default, 1) ; lower half transparent If @error Then Return SetError(5, @error, 0) _XLChart_FillSet($oChart1.SeriesCollection(1).Points(2), 0xFF0000, 0xFFFFFF, False, 4, 1, 0.4) ; red fill gradient If @error Then Return SetError(5, @error, 0) _XLChart_FillSet($oChart1.SeriesCollection(1).Points(3), 0xFFCE00, Default) ; yellow fill If @error Then Return SetError(5, @error, 0) _XLChart_FillSet($oChart1.SeriesCollection(1).Points(4), 0x00FF19); green fill If @error Then Return SetError(5, @error, 0) ; ***************************************************************************** ; 2. Pie chart ; ***************************************************************************** $IndicatorDataRange = "='" & $sXLC_SheetName & "'!" & $IndicatorDataRange $IndicatorDataName = "='" & $sXLC_SheetName & "'!" & $IndicatorDataName Local $oChart2 = _XLChart_ChartCreate($oExcel, "Gauge Chart", $xlPie, $Graph_position, "", "", $IndicatorDataRange, $IndicatorDataName, False, " ") If @error Then Return SetError(6, @error, 0) ; Chart rotate 270 degrees _XLChart_PieGroupSet($oChart2.ChartGroups(1), 270) If @error Then Return SetError(7, @error, 0) ; Pointers _XLChart_FillSet($oChart2.SeriesCollection(1).Points(1), 0xFFFFFF, Default, Default, 3, 1, Default, 1) ; lower half transparent If @error Then Return SetError(8, @error, 0) _XLChart_FillSet($oChart2.SeriesCollection(1).Points(3), 0xFFFFFF, Default, Default, 3, 1, Default, 1) ; lower half transparent If @error Then Return SetError(8, @error, 0) _XLChart_FillSet($oChart2.SeriesCollection(1).Points(2), 0x000037) ; pointer If @error Then Return SetError(8, @error, 0) ; set Border transparent _XLChart_LineSet($oChart2.ChartArea, Default, Default, Default, Default, Default, $xlSheetHidden) If @error Then Return SetError(9, @error, 0) ; Set chart area transparent _XLChart_FillSet($oChart2.ChartArea, 0xCD0000, Default, Default, Default, Default, Default, 1) If @error Then Return SetError(8, @error, 0) ; Set data label for points 2 _XLChart_DatalabelSet($oChart2.SeriesCollection(1), 2, True, Default, Default, Default, Default, $xlLabelPositionOutsideEnd) If @error Then Return SetError(10, @error, 0) ; Delete the datalabels 1 and 3 _XLChart_ObjectDelete($oChart2.SeriesCollection(1).Points(1).DataLabel) If @error Then Return SetError(11, @error, 0) _XLChart_ObjectDelete($oChart2.SeriesCollection(1).Points(3).DataLabel) If @error Then Return SetError(11, @error, 0) ; set the cell value of C4 in the data label point 2 ; Water and I have decided not to make a function of the following line, except is there is a request for it $oChart2.SeriesCollection(1).Points(2).DataLabel.Text = StringTrimRight($IndicatorDataRange,StringLen($IndicatorDataRange) - StringInStr($IndicatorDataRange,":") + 1 ) If @error Then Return SetError(12, @error, 0) Return EndFunc ;==>_XLChart_Gauge #region ExcelEX udf extract ; #FUNCTION# ==================================================================================================== ; Name...........: _Excel_ColumnNumberToLetter ; Description....: Convert column number to column letter. ; Syntax.........: _Excel_ColumnNumberToLetter($iColumnNumber) ; Parameters ....: $iColumnNumber - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; Return values .: Success - Returns the ColumnLetter in the range A to XFD ;                 Failure - Returns 0 and sets @error: ;                 |1 - Specified $iColumnNumber exceeds maximum authorized columns in Excel ; Authors........: GreenCan ; Modified ......: ; Remarks .......: ; Related .......: ; Link ..........: ; Example .......: Yes ; =============================================================================================================== Func _Excel_ColumnNumberToLetter($iColumnNumber) If $iColumnNumber > 16384 Then Return SetError(1, 0, 0) If $iColumnNumber > 702 Then   ; as from AAA   Return Chr( Int( ( Int( Mod( $iColumnNumber - 703, 26 ) /26 )  + Int( ( $iColumnNumber - 703 ) /26 ) ) / 26 ) + 65 ) & _       Chr( Mod( Int( Mod( $iColumnNumber - 703, 26 ) /26 )  + Int( ( $iColumnNumber - 703 ) /26 ) , 26) + 65 ) & _       Chr( Mod( $iColumnNumber - 703, 26 ) + 65 ) ElseIf $iColumnNumber > 26 Then   ; as from AA   Return Chr( Int( ( $iColumnNumber - 1 ) / 26 ) + 64 ) & _       Chr( Mod( $iColumnNumber - 1, 26 ) + 65 ) Else   ; Columns A-Z   Return Chr( $iColumnNumber + 64 ) EndIf EndFunc ;==>_Excel_ColumnNumberToLetter #endregion #region ExcelCom udf extract (FOR THE PURPOSE OF THIS EXAMPLE ONLY) ; #FUNCTION# ==================================================================================================== ; Function:  _ExcelCellColorSet ; Description:    Set the cell interior color value of a range in an Excel object. ; Syntax:          _ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _ ;         $iColorIndex = 1, $hColor = 0x000000) ; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ;    $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1 ;    $iColStart - The starting column for the number format(left) (default=1) ;    $iRowEnd - The ending row for the number format (bottom) (default=1) ;    $iColEnd - The ending column for the number format (right) (default=1) ;    $iColorIndex - The interior color index (default = 1, if > 254 then $hColor is used instead) ;    $hColor - Hex value of color used when colorindex > 254 ; Requirement(s):   None ; Return Value(s):  On Success - Returns 1 ;                  On Failure - Returns 0 and sets @error on errors: ;     @error=1 - Specified object does not exist ;     @error=2 - Starting row or column invalid ;      @extended=0 - Starting row invalid ;      @extended=1 - Starting column invalid ;     @error=3 - Ending row or column invalid ;      @extended=0 - Ending row invalid ;      @extended=1 - Ending column invalid ; Author(s):        SEO <locodarwin at yahoo dot com> ; Note(s):        None ; ; #FUNCTION# ==================================================================================================== Func _ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then   If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)   If $iColStart < 1 Then Return SetError(2, 1, 0)   If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)   If $iColEnd < $iColStart Then Return SetError(3, 1, 0)   If $iColorIndex < 255 Then    $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Interior.ColorIndex = $iColorIndex   Else    $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Interior.Color = $hColor   EndIf Else   If $iColorIndex < 255 Then    $oExcel.Activesheet.Range($sRangeOrRowStart).Interior.ColorIndex = $iColorIndex   Else    $oExcel.Activesheet.Range($sRangeOrRowStart).Interior.Color = $hColor   EndIf EndIf Return 1 EndFunc ;==>_ExcelCellColorSet ; #FUNCTION# ==================================================================================================== ; Function:  _ExcelCellMerge ; Description:    Merge/UnMerge cell(s) in a range. ; Syntax:          _ExcelCellMerge($oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1) ; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ;    $fDoMerge - Flag, True performs a merge on the range, False reverses an existing merge in the range (True or False) (default=False) ;    $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1 ;    $iColStart - The starting column for the number format(left) (default=1) ;    $iRowEnd - The ending row for the number format (bottom) (default=1) ;    $iColEnd - The ending column for the number format (right) (default=1) ; Requirement(s):   None ; Return Value(s):  On Success - Returns 1 ;                  On Failure - Returns 0 and sets @error on errors: ;     @error=1 - Specified object does not exist ;     @error=2 - Starting row or column invalid ;      @extended=0 - Starting row invalid ;      @extended=1 - Starting column invalid ;     @error=3 - Ending row or column invalid ;      @extended=0 - Ending row invalid ;      @extended=1 - Ending column invalid ;     @error=4 - $fDoMerge is not set as True or False ; Author(s):        SEO <locodarwin at yahoo dot com> ; Note(s):        None ; ; #FUNCTION# ==================================================================================================== Func _ExcelCellMerge($oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If $fDoMerge <> False and $fDoMerge <> True Then Return SetError(4, 0, 0) If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then   If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)   If $iColStart < 1 Then Return SetError(2, 1, 0)   If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)   If $iColEnd < $iColStart Then Return SetError(3, 1, 0)   $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).MergeCells = $fDoMerge Else   $oExcel.Activesheet.Range($sRangeOrRowStart).MergeCells = $fDoMerge EndIf Return 1 EndFunc ;==>_ExcelCellMerge ; #FUNCTION# ==================================================================================================== ; Function:  _ExcelColWidthSet ; Description:    Set the column width of the specified column(s). ; Syntax:          _ExcelColWidthSet($oExcel, $vColumn, $vWidth) ; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ;    $sColumn - A valid Excel column, either a number or an A1 string (i.e. 5, or "C" or a range "A:K") ;    $vWidth - The width of the column in points, or the string "autofit" ; Requirement(s):   None ; Return Value(s):  On Success - Returns 1 ;                  On Failure - Returns 0 and sets @error on errors: ;     @error=1 - Specified object does not exist ; Author(s):        SEO <locodarwin at yahoo dot com> ; Note(s):        None ; ; #FUNCTION# ==================================================================================================== Func _ExcelColWidthSet($oExcel, $vColumn, $vWidth) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If $vWidth = "autofit" Then   $oExcel.Activesheet.Columns($vColumn).Autofit Else   $oExcel.Activesheet.Columns($vColumn).ColumnWidth = $vWidth EndIf Return 1 EndFunc ;==>_ExcelColWidthSet ; #FUNCTION# ==================================================================================================== ; Function:  _ExcelCreateBorders ; Description:    Create Borders around a range of cells ; Syntax:          _ExcelCreateBorders($oExcel, $sBorderStyle, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iEdgeLeft = 1, ;          $iEdgeTop = 1, $iEdgeBottom = 1, $iEdgeRight = 1, $iEdgeInsideV = 0, $iEdgeInsideH = 0) ; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ;              $sBorderStyle - The type of border to use,  $xlThick, $xlThin, $xlDouble ;              $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1 ;              $iColStart - The starting column for the number format(left) (default=1) ;              $iRowEnd - The ending row for the number format (bottom) (default=1) ;              $iColEnd - The ending column for the number format (right) (default=1) ;              $iEdgeLeft - Specify if the left edge of the selected cells should have a border (default=1) Yes ;              $iEdgeTop  - Specify if the Top edge of the selected cells should have a border (default=1) Yes ;              $iEdgeBottom - Specify if the Bottom edge of the selected cells should have a border (default=1) Yes ;              $iEdgeRight - Specify if the Right edge of the selected cells should have a border (default=1) Yes ;              $iEdgeInsideV - Specify if the Inside Verticle edges of the selected cells should have a border (default=0) No ;              $iEdgeInsideH - Specify if the Inside Horizontal edges of the selected cells should have a border (default=0) No ; Requirement(s):   None ; Return Value(s):  On Success - Returns 1 ;                  On Failure - Returns 0 and sets @error on errors: ;                 @error=1 - Specified object does not exist ;                 @error=2 - Starting row or column invalid ;                    @extended=0 - Starting row invalid ;                    @extended=1 - Starting column invalid ;                 @error=3 - Ending row or column invalid ;                    @extended=0 - Ending row invalid ;                    @extended=1 - Ending column invalid ; Author(s):        MikeOsdx <Using Generic Excel functions from locodarwin> ; Note(s):        None ; ; #FUNCTION# ==================================================================================================== Func _ExcelCreateBorders($oExcel, $sBorderStyle, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _        $iEdgeLeft = 1, $iEdgeTop = 1, $iEdgeBottom = 1, $iEdgeRight = 1, $iEdgeInsideV = 0, $iEdgeInsideH = 0)     If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)     If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then         If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)         If $iColStart < 1 Then Return SetError(2, 1, 0)         If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)         If $iColEnd < $iColStart Then Return SetError(3, 1, 0)         if $iEdgeLeft = 1 Then             With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeLeft)                 .LineStyle = $xlContinuous                 .ColorIndex = $xlAutomatic                 ;.TintAndShade = 0                 .Weight = $sBorderStyle             EndWith         EndIf         if $iEdgeTop = 1 Then             With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeTop)                 .LineStyle = $xlContinuous                 .ColorIndex = $xlAutomatic                 ;.TintAndShade = 0                 .Weight = $sBorderStyle             EndWith         EndIf         if $iEdgeBottom = 1 Then             With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeBottom)                 .LineStyle = $xlContinuous                 .ColorIndex = $xlAutomatic                 ;.TintAndShade = 0                 .Weight = $sBorderStyle             EndWith         EndIf         if $iEdgeRight = 1 Then             With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeRight)                 .LineStyle = $xlContinuous                 .ColorIndex = $xlAutomatic                 ;.TintAndShade = 0                 .Weight = $sBorderStyle             EndWith         EndIf         if $iEdgeInsideV = 1 Then             With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlInsideVertical)                 .LineStyle = $xlContinuous                 .ColorIndex = $xlAutomatic                 ;.TintAndShade = 0                 .Weight = $sBorderStyle             EndWith         EndIf         if $iEdgeInsideH = 1 Then             With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlInsideHorizontal)                 .LineStyle = $xlContinuous                 .ColorIndex = $xlAutomatic                 ;.TintAndShade = 0                 .Weight = $sBorderStyle             EndWith         EndIf     Else         if $iEdgeLeft = 1 Then             With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeLeft)                 .LineStyle = $xlContinuous                 .ColorIndex = $xlAutomatic                 ;.TintAndShade = 0                 .Weight = $sBorderStyle             EndWith         EndIf         if $iEdgeTop = 1 Then             with $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeTop)                 .LineStyle = $xlContinuous                 .ColorIndex = $xlAutomatic                 ;TintAndShade = 0                 .Weight = $sBorderStyle             EndWith         EndIf         if $iEdgeBottom = 1 Then             With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeBottom)                 .LineStyle = $xlContinuous                 .ColorIndex = $xlAutomatic                 ;.TintAndShade = 0                 .Weight = $sBorderStyle             EndWith         EndIf         if $iEdgeRight = 1 Then             With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeRight)                 .LineStyle = $xlContinuous                 .ColorIndex = $xlAutomatic                 ;.TintAndShade = 0                 .Weight = $sBorderStyle             EndWith         EndIf         if $iEdgeInsideV = 1 Then             With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlInsideVertical)                 .LineStyle = $xlContinuous                 .ColorIndex = $xlAutomatic                 ;.TintAndShade = 0                 .Weight = $sBorderStyle             EndWith         EndIf         if $iEdgeInsideH = 1 Then             With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlInsideHorizontal)                 .LineStyle = $xlContinuous                 .ColorIndex = $xlAutomatic                 ;.TintAndShade = 0                 .Weight = $sBorderStyle             EndWith         EndIf     EndIf     Return 1 EndFunc ; #FUNCTION# ==================================================================================================== ; Function:  _ExcelFontSet ; Description:    Set the font face property of a range in an Excel object. ; Syntax:          _ExcelFontSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sFontName = "Arial") ; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ;    $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1 ;    $iColStart - The starting column for the number format(left) (default=1) ;    $iRowEnd - The ending row for the number format (bottom) (default=1) ;    $iColEnd - The ending column for the number format (right) (default=1) ;    $sFontName - The font name to set the range to (default = "Arial") ; Requirement(s):   None ; Return Value(s):  On Success - Returns 1 ;                  On Failure - Returns 0 and sets @error on errors: ;     @error=1 - Specified object does not exist ;     @error=2 - Starting row or column invalid ;      @extended=0 - Starting row invalid ;      @extended=1 - Starting column invalid ;     @error=3 - Ending row or column invalid ;      @extended=0 - Ending row invalid ;      @extended=1 - Ending column invalid ; Author(s):        SEO <locodarwin at yahoo dot com> ; Note(s):        None ; ; #FUNCTION# ==================================================================================================== Func _ExcelFontSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sFontName = "Arial") If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then   If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)   If $iColStart < 1 Then Return SetError(2, 1, 0)   If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)   If $iColEnd < $iColStart Then Return SetError(3, 1, 0)   $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Font.Name = $sFontName Else   $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Name = $sFontName EndIf Return 1 EndFunc ;==>_ExcelFontSet ; #FUNCTION# ==================================================================================================== ; Function:  _ExcelFontSetSize ; Description:    Set the font size property of a range in an Excel object. ; Syntax:          _ExcelFontSetSize($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iFontSize = 10) ; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ;    $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1 ;    $iColStart - The starting column for the number format(left) (default=1) ;    $iRowEnd - The ending row for the number format (bottom) (default=1) ;    $iColEnd - The ending column for the number format (right) (default=1) ;    $sFontSize - The font size in points to set the range to (default = 10 points) ; Requirement(s):   None ; Return Value(s):  On Success - Returns 1 ;                  On Failure - Returns 0 and sets @error on errors: ;     @error=1 - Specified object does not exist ;     @error=2 - Starting row or column invalid ;      @extended=0 - Starting row invalid ;      @extended=1 - Starting column invalid ;     @error=3 - Ending row or column invalid ;      @extended=0 - Ending row invalid ;      @extended=1 - Ending column invalid ; Author(s):        SEO <locodarwin at yahoo dot com> ; Note(s):        None ; ; #FUNCTION# ==================================================================================================== Func _ExcelFontSetSize($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iFontSize = 10) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then   If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)   If $iColStart < 1 Then Return SetError(2, 1, 0)   If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)   If $iColEnd < $iColStart Then Return SetError(3, 1, 0)   $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Font.Size = $iFontSize Else   $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Size = $iFontSize EndIf Return 1 EndFunc ;==>_ExcelFontSetSize ; #FUNCTION# ==================================================================================================== ; Function:  _ExcelFontSetColor ; Description:    Set the font color value of a range in an Excel object. ; Syntax:          _ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _ ;        $iColorIndex = 1, $hColor = 0x000000) ; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ;    $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1 ;    $iColStart - The starting column for the number format(left) (default=1) ;    $iRowEnd - The ending row for the number format (bottom) (default=1) ;    $iColEnd - The ending column for the number format (right) (default=1) ;    $iColorIndex - The font color index (default = 1, if > 254 then $hColor is used instead) ;    $hColor - Hex value of color used when colorindex > 254 ; Requirement(s):   None ; Return Value(s):  On Success - Returns 1 ;                  On Failure - Returns 0 and sets @error on errors: ;     @error=1 - Specified object does not exist ;     @error=2 - Starting row or column invalid ;      @extended=0 - Starting row invalid ;      @extended=1 - Starting column invalid ;     @error=3 - Ending row or column invalid ;      @extended=0 - Ending row invalid ;      @extended=1 - Ending column invalid ; Author(s):        SEO <locodarwin at yahoo dot com> ; Note(s):        None ; ; #FUNCTION# ==================================================================================================== Func _ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then   If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)   If $iColStart < 1 Then Return SetError(2, 1, 0)   If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)   If $iColEnd < $iColStart Then Return SetError(3, 1, 0)   If $iColorIndex < 255 Then    $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Font.ColorIndex = $iColorIndex   Else    $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Font.Color = $hColor   EndIf Else   If $iColorIndex < 255 Then    $oExcel.Activesheet.Range($sRangeOrRowStart).Font.ColorIndex = $iColorIndex   Else    $oExcel.Activesheet.Range($sRangeOrRowStart).Font.Color = $hColor   EndIf EndIf Return 1 EndFunc ;==>_ExcelFontSetColor ; #FUNCTION# ==================================================================================================== ; Function:  _ExcelRowHeightSet ; Description:    Set the row height of the specified row(s). ; Syntax:          _ExcelRowHeightSet($oExcel, $sRow, $vHeight) ; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ;    $iRow - The integer representation of a valid Excel row (i.e. 45 or a range "1:10") ;    $vHeight - The height of the row in points, or the string "autofit" ; Requirement(s):   None ; Return Value(s):  On Success - Returns 1 ;                  On Failure - Returns 0 and sets @error on errors: ;     @error=1 - Specified object does not exist ; Author(s):        SEO <locodarwin at yahoo dot com> ; Note(s):        None ; ; #FUNCTION# ==================================================================================================== Func _ExcelRowHeightSet($oExcel, $iRow, $vHeight) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If $vHeight = "autofit" Then   $oExcel.Activesheet.Rows($iRow).Autofit Else   $oExcel.Activesheet.Rows($iRow).RowHeight = $vHeight EndIf EndFunc ;==>_ExcelRowHeightSet ; #FUNCTION# ==================================================================================================== ; Function:  _ExcelVerticalAlignSet ; Description:    Set the vertical alignment of each cell in a range. ; Syntax:          _ExcelVerticalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sVertAlign = "bottom") ; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ;    $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1 ;    $iColStart - The starting column for the number format(left) (default=1) ;    $iRowEnd - The ending row for the number format (bottom) (default=1) ;    $iColEnd - The ending column for the number format (right) (default=1) ;    $sVertAlign - Vertical alignment ("top"|"center"|"bottom") (default="bottom") ; Requirement(s):   None ; Return Value(s):  On Success - Returns 1 ;                  On Failure - Returns 0 and sets @error on errors: ;     @error=1 - Specified object does not exist ;     @error=2 - Starting row or column invalid ;      @extended=0 - Starting row invalid ;      @extended=1 - Starting column invalid ;     @error=3 - Ending row or column invalid ;      @extended=0 - Ending row invalid ;      @extended=1 - Ending column invalid ; Author(s):        SEO <locodarwin at yahoo dot com> ; Note(s):        None ; ; #FUNCTION# ==================================================================================================== Func _ExcelVerticalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sVertAlign = "bottom") If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then   If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)   If $iColStart < 1 Then Return SetError(2, 1, 0)   If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)   If $iColEnd < $iColStart Then Return SetError(3, 1, 0)   Switch ($sVertAlign)    Case "bottom"     $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).VerticalAlignment = $xlVAlignBottom    Case "center", "centre"     $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).VerticalAlignment = $xlVAlignCenter    Case "top"     $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).VerticalAlignment = $xlVAlignTop   EndSwitch Else   Switch ($sVertAlign)    Case "bottom"     $oExcel.Activesheet.Range ($sRangeOrRowStart).VerticalAlignment = $xlVAlignBottom    Case "center", "centre"     $oExcel.Activesheet.Range ($sRangeOrRowStart).VerticalAlignment = $xlVAlignCenter    Case "top"     $oExcel.Activesheet.Range ($sRangeOrRowStart).VerticalAlignment = $xlVAlignTop   EndSwitch EndIf Return 1 EndFunc ;==>_ExcelVerticalAlignSet #endregion

Attached Thumbnails

  • GaugeChart.jpg

Edited by GreenCan, 17 February 2012 - 05:47 PM.


#6 GreenCan

GreenCan

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 325 posts

Posted 17 February 2012 - 04:45 PM

This is a very nice example of a Conditional Line Chart based on a formula (original example can be found on the website of Andy Pope http://www.andypope.info/index.htm)

The sheet contains complex formulas and makes extensive use of Excel function translation and Application.International Property of Excel.

The chart displays a line that changes colour depending on whether it is above or below the control line. It is based on a xy-scatter chart and if you Press F9, the graph will refresh with new values (because of the random numbers in column B.

The example should function in any of these 16 languages:
English (is the base script language) , Czech, Danish, Dutch, French, German, Spanish, Finnish, Hungarian, Italian, Norwegian, Polish, Portuguese, Brazialian Portuguese, Russian, Swedish and Turkish
(requires function_language_lookup.cfg in the script folder, included in zip file)

Please let me know if this would not be the case.

GreenCan

Attached Thumbnails

  • ConditionalFormating.jpg

Attached Files



#7 ptrex

ptrex

    Universalist

  • MVPs
  • 2,420 posts

Posted 26 February 2012 - 07:52 PM

This example is a Dashboard I use at work.

GreenCan translated this using the powerfull Excel UDF (thanks to Water).

Thanks a both of you,

Rgds,

ptrex

Attached Thumbnails

  • _XLChart_Example_Dashboard.jpg

Attached Files



#8 water

water

    ?

  • MVPs
  • 15,759 posts

Posted 29 April 2012 - 02:26 PM

Version 0.3.0.0 of the UDF has been released.

Have fun testing!

For download please see my signature.
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#9 water

water

    ?

  • MVPs
  • 15,759 posts

Posted 21 January 2013 - 07:41 AM

Version 0.3.1.1 of the UDF has been released.

Have fun testing!

For download please see my signature.

Edited by water, 21 January 2013 - 03:15 PM.

UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#10 13lack13lade

13lack13lade

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 354 posts

Posted 27 August 2013 - 01:43 AM

I have a question!!!!!

 

Is it possible to re-create these graphs or say a dashboard of graphs from excel without having them displayed in excel?

 

For example i am wanting to make an Autoit GUI where they can load the dashboard which is linked to shared spreadsheets that are constantly updated to give a real-time display of the data however not within excel and rather in the Autoit Window itself... is this possible?

 

*sorry if this is in the wrong section*


Edited by 13lack13lade, 27 August 2013 - 01:44 AM.


#11 water

water

    ?

  • MVPs
  • 15,759 posts

Posted 27 August 2013 - 07:48 AM

Moved to the "General Help & Support" thread.


UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#12 step887

step887

    Wayfarer

  • Active Members
  • Pip
  • 90 posts

Posted 06 December 2013 - 10:43 PM

First off, very nice work on Excel Charts and the provided examples.

 

I often have to parse poolmon logs to find memory leaks and if you have read the logs, they are not easiest to go through.

 

http://support.microsoft.com/kb/177415

 

So I parse the data and use charts to make nice little line charts. See attached file screen shot of top 25 line chart. It also does 25 individual line charts for the top 25..

 

the script can take single or multiple files from command line switch (or drag a file on the icon) or it will pop up file open dialog if there is no command line.

AutoIt         
#include <array.au3> #include <excel.au3> #include <Excelchart.au3> Global $flarr[100][2] = [[0]], $rec[100] = [0], $index, $arr[100] = [0], $count, $bad = True, $file_log, $farr, $x_time, $cmdlineused If $cmdline[0] = 1 And FileExists($cmdlineraw) Then;one file     $file_log = $cmdlineraw ElseIf $cmdline[0] > 1 Then     For $i = 1 To $cmdline[0]         $file_log &= $cmdline[$i] & '|'     Next     $file_log = StringTrimRight($file_log, 1)     $cmdlineused = True Else     $file_log = FileOpenDialog('Choose poolmon log', @ScriptDir, "All Files (*.*)", 1 + 4) EndIf If Not $file_log Then Exit If StringRegExp($file_log, '\|') Then     Local $z = 2, $full     $farr = StringSplit($file_log, '|')     If $cmdlineused Then $z = 1     For $i = $z To $farr[0]         If $cmdlineused Then             $full = $farr[$i]         Else             $full = $farr[1] & '\' & $farr[$i]         EndIf         If FileExists($full) Then             _ArrAdd($flarr, StringTrimLeft(FileGetTime($full, 1, 1), 8) & '|' & $full, 2)         EndIf     Next     ReDim $flarr[$flarr[0][0] + 1][2]     _ArraySort($flarr, 0, 1)     For $i = 1 To $flarr[0][0]         _file_to_arr($flarr[$i][1], $rec)         $x_time &= $flarr[$i][0] & '|'     Next     $x_time = StringTrimRight($x_time, 1)     $x_time = StringSplit($x_time, '|', 2) Else     _file_to_arr($file_log, $rec) EndIf ProgressOn($file_log, "Parsing Log", '', Default, Default, 18) For $i = 1 To $rec[0]     $temp = StringRegExp($rec[$i], '([A-z0-9 _]{4} [A-z]{4})\s+[0-9]+\s+[0-9]+\s+[0-9]+\s+([0-9]+)', 1)     If @error = 0 Then         $out = StringRegExp($index, $temp[0] & '\|(\d*)\|', 1)         If @error = 0 Then             $arr[$out[0]] &= '|' & $temp[1]         Else             $index &= $temp[0] & '|' & $arr[0] + 1 & '|'             _ArrAdd($arr, $temp[0] & '|' & $temp[1])         EndIf     EndIf     ProgressSet(($i / $rec[0]) * 24) Next If $rec[0] = 0 Then     ProgressOff()     MsgBox(16, @ScriptName, 'No Data to import. Clck ok to exit.')     Exit EndIf ProgressSet(($i / $rec[0]) * 24, '', 'Creating Excel file') $rec = 0 Local $oExcel = _ExcelBookNew(0) If @error Then     MsgBox(16, @ScriptName, 'Error: Unable to create the Excel COM object.')     Exit EndIf _ExcelSheetAddNew($oExcel, 'Top 25') _ExcelSheetActivate($oExcel, 2) _ExcelSheetNameSet($oExcel, "Top Charts") _ExcelSheetActivate($oExcel, 3) _ExcelSheetNameSet($oExcel, "Data") Local $top For $i = 1 To $arr[0]     $tarr = StringSplit($arr[$i], '|', 2)     _ExcelWriteArray($oExcel, $i, 3, $tarr)     $num = UBound($tarr)     If $top < $num Then $top = $num     _ExcelWriteFormula($oExcel, '=R' & $i & 'C' & $num + 2 & '-R' & $i & 'C4', $i, 1) ;Uses R1C1 referencing     _ExcelWriteFormula($oExcel, '=STDEV.S(R' & $i & 'C4:R' & $i & 'C' & $num + 2, $i, 2) ;Uses R1C1 referencing     $t = _ExcelReadCell($oExcel, $i, 1)     _ExcelWriteCell($oExcel, $t, $i, 1)     $t = _ExcelReadCell($oExcel, $i, 2)     _ExcelWriteCell($oExcel, $t, $i, 2)     ProgressSet(($i / $arr[0]) * 75 + 24) Next ProgressSet(100, '', 'Creating Charts.') $oExcel.ActiveSheet.UsedRange.Sort($oExcel.ActiveSheet.Range("A1"), 2, $oExcel.ActiveSheet.Range("B1"), Default, 2) _ExcelRowInsert($oExcel, 1) _ExcelWriteCell($oExcel, 'End-Start', 1, 1) _ExcelWriteCell($oExcel, 'Standard Deviation', 1, 2) _ExcelWriteCell($oExcel, 'Tag', 1, 3) If IsArray($x_time) Then     _ExcelWriteArray($oExcel, 1, 4, $x_time) Else     For $i = 4 To $top + 2         _ExcelWriteCell($oExcel, $i - 3, 1, $i)     Next EndIf Local $XValueRange = '=Data!R1C4:R1' & 'C' & $top + 2 Local $asDataRange[26] = [25], $asDataName[26] = [25] For $i = 1 To 25     $asDataRange[$i] = '=Data!R' & $i + 1 & 'C4:R' & $i + 1 & 'C' & $top + 2     $asDataName[$i] = '=Data!C' & $i + 1 Next _ExcelSheetActivate($oExcel, 1) _XLChart_ChartCreate($oExcel, 1, 4, 'A1:S40', 'Top 25', $XValueRange, $asDataRange, $asDataName, True, 'Top 25') Local $r1, $r2, $c = 1 For $i = 1 To $asDataRange[0]     $mod = Mod($i, 4)     Switch $mod         Case 1             $r = 'A' & $c & ':F' & $c + 13         Case 2             $r = 'G' & $c & ':L' & $c + 13         Case 3             $r = 'M' & $c & ':R' & $c + 13         Case 0             $r = 'S' & $c & ':X' & $c + 13             $c += 14     EndSwitch     $out = _XLChart_ChartCreate($oExcel, 2, $xlLineMarkers, $r, $asDataName[$i], $XValueRange, $asDataRange[$i], $asDataName[$i], False, $asDataName[$i]) Next ProgressOff() Local $test = False Do     $fout = FileSaveDialog('Output File,', @ScriptDir, "Excel files (*.xls)")     If FileExists($fout) Then         $mout = MsgBox(36, 'Overwriite', $fout & ' already exist. Overwrite?')         If $mout = 6 Then             $dout = FileDelete($fout)             If $dout = 0 Then                 MsgBox(0, $fout, 'Failed to save ' & $fout & '.' & @CRLF & 'Please close excel or choose a different filename.')             Else                 $test = True             EndIf         EndIf     Else         $test = True     EndIf Until $test = True If $fout <> 1 Then _ExcelBookSaveAs($oExcel, $fout) $oExcel.Application.Visible = 1 Func _file_to_arr($name, ByRef $arr)     Local $file = FileOpen($name, 0)     ; Check if file opened for reading OK     If $file = -1 Then         MsgBox(0, "Error", "Unable to open " & $name)         Exit     EndIf     ; Read in lines of text until the EOF is reached     While 1         Local $line = FileReadLine($file)         If @error = -1 Then ExitLoop         _ArrAdd($rec, $line)     WEnd     FileClose($file) EndFunc   ;==>_file_to_arr Func _ArrAdd(ByRef $array, $data, $dem = False, $delimt = '|')     If $dem Then         Local $t = StringSplit($data, $delimt)         If @error = 0 Then             $array[0][0] += 1             If UBound($array) = $array[0][0] Then ReDim $array[$array[0][0] * 2][UBound($array, 2)]             For $f = 1 To $t[0]                 $array[$array[0][0]][$f - 1] = $t[$f]             Next         EndIf     Else         $array[0] += 1         If UBound($array) = $array[0] Then ReDim $array[$array[0] * 2]         $array[$array[0]] = $data     EndIf EndFunc   ;==>_ArrAdd

Autoit v3.3.8.1

Excel 2010

 

 

 

 

 

Attached Thumbnails

  • TOP25.PNG


#13 water

water

    ?

  • MVPs
  • 15,759 posts

Posted 07 December 2013 - 08:26 AM

Glad you like the UDF :)

Thanks for the example. So people can see how useful the UDF can be to make life easier.

 

N.B.: If performance is an issue with your script I suggest to have a look at my rewrite of the Excel UDF. Reading/writing is much, much faster (20 to 100 times).


UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#14 step887

step887

    Wayfarer

  • Active Members
  • Pip
  • 90 posts

Posted 08 December 2013 - 07:26 PM

So I took your advice and used your excel rewrite

the poolmon file I was using was 10 megs.

The difference was impressive.. 

38.227 secs with your excel UDF

250.344 secs with the built in Excel

 

Thanks,

 

below is the updated code I used.. 

AutoIt         
#include <array.au3> #include <excel.au3> #include <Excel Rewrite.au3> #include <Excelchart.au3> Global $flarr[100][2] = [[0]], $rec[100] = [0], $index, $cindex[100] = [0],$arr[100][10] = [[0]], $count, $bad = True, $file_log, $farr, $x_time, $cmdlineused If $cmdline[0] = 1 And FileExists($cmdlineraw) Then;one file     $file_log = $cmdlineraw ElseIf $cmdline[0] > 1 Then     For $i = 1 To $cmdline[0]         $file_log &= $cmdline[$i] & '|'     Next     $file_log = StringTrimRight($file_log, 1)     $cmdlineused = True Else     $file_log = FileOpenDialog('Choose poolmon log', @ScriptDir, "All Files (*.*)", 1 + 4) EndIf If Not $file_log Then Exit If StringRegExp($file_log, '\|') Then     Local $z = 2, $full     $farr = StringSplit($file_log, '|')     If $cmdlineused Then $z = 1     For $i = $z To $farr[0]         If $cmdlineused Then             $full = $farr[$i]         Else             $full = $farr[1] & '\' & $farr[$i]         EndIf         If FileExists($full) Then             _ArrAdd($flarr, StringTrimLeft(FileGetTime($full, 1, 1), 8) & '|' & $full, 2)         EndIf     Next     ReDim $flarr[$flarr[0][0] + 1][2]     _ArraySort($flarr, 0, 1)     For $i = 1 To $flarr[0][0]         _file_to_arr($flarr[$i][1], $rec)         $x_time &= $flarr[$i][0] & '|'     Next     $x_time = StringTrimRight($x_time, 1)     $x_time = StringSplit($x_time, '|', 2) Else     $rec = FileReadToArray($file_log) EndIf $timer = TimerInit() ProgressOn($file_log, "Parsing Log", '', Default, Default, 18) Local $num = 0, $total = Ubound($rec)-1,$top For $i = 1 To  $total     $temp = StringRegExp($rec[$i], '([A-z0-9 _]{4} [A-z]{4})\s+[0-9]+\s+[0-9]+\s+[0-9]+\s+([0-9]+)', 1)     If @error = 0 Then         $out = StringRegExp($index, $temp[0] & '\|(\d*)\|', 1)         If @error = 0 Then             $cindex[$out[0]] += 1             If $top < $cindex[$out[0]] Then $top = $cindex[$out[0]]             _ArrAdd($arr,$temp[1],Default,$out[0],$cindex[$out[0]])         Else             $index &= $temp[0] & '|' & $arr[0][0] + 1 & '|'             _ArrAdd($cindex, 1)             _ArrAdd($arr, $temp[0] & '|' & $temp[1], '|')         EndIf     EndIf     ProgressSet(($i / $total) * 24) Next ;~ _ArrayDisplay($arr) If $arr[0][0] = 0 Then     ProgressOff()     MsgBox(16, @ScriptName, 'No Data to import. Clck ok to exit.')     Exit EndIf ProgressSet(($i / $total) * 24, '', 'Creating Excel file') $rec = 0 ;~ Local $oExcel = _ExcelBookNew(0) Local $oExcel = _Excel_Open() $oWorkbook = _Excel_BookNew($oExcel,1) If @error Then     MsgBox(16, @ScriptName, 'Error: Unable to create the Excel COM object.')     Exit EndIf ;~ _ExcelSheetActivate($oExcel,1) ;~ _ExcelSheetNameSet($oExcel, 'Top 25') ;~ _ExcelSheetActivate($oExcel, 2) _ExcelSheetNameSet($oExcel, "Top Charts") _ExcelSheetActivate($oExcel, 2) _ExcelSheetNameSet($oExcel, "Data") Local $tarr [1][2] = [[0]] ;~ For $i = 1 To $arr[0] ;~  $tarr = StringSplit($arr[$i], '|', 2)     _Excel_RangeWrite($oWorkbook, 'Data', $arr, "C2")     For $i = 1 to $arr[0][0]         $c = _Excel_ColumnToLetter($cindex[$i]+3)         _Excel_RangeWrite($oWorkbook,'Data','=' & $c & $i & '-D' & $i, 'A' & $i, False)         _Excel_RangeWrite($oWorkbook,'Data','=STDEV.S(D' & $i & ':' &  $c & $i, 'B' & $i, False)         ProgressSet(($i / $arr[0][0]) * 75 + 24)     Next ;~  _ExcelWriteArray($oExcel, $i, 3, $tarr) ;~  $num = UBound($tarr) ;~  If $top < $num Then $top = $num ;~  _ExcelWriteFormula($oExcel, '=R' & $i & 'C' & $num + 2 & '-R' & $i & 'C4', $i, 1) ;Uses R1C1 referencing ;~  _ExcelWriteFormula($oExcel, '=STDEV.S(R' & $i & 'C4:R' & $i & 'C' & $num + 2, $i, 2) ;Uses R1C1 referencing ;~  $t = _ExcelReadCell($oExcel, $i, 1) ;~  _ExcelWriteCell($oExcel, $t, $i, 1) ;~  $t = _ExcelReadCell($oExcel, $i, 2) ;~  _ExcelWriteCell($oExcel, $t, $i, 2) ;~  ProgressSet(($i / $arr[0]) * 75 + 24) ;~ Next ;~ ProgressSet(100, '', 'Creating Charts.') $oExcel.ActiveSheet.UsedRange.Sort($oExcel.ActiveSheet.Range("A1"), 2, $oExcel.ActiveSheet.Range("B1"), Default, 2) _ExcelRowInsert($oExcel, 1) _Excel_RangeWrite($oWorkbook, 'Data','End-Start', 'A1') _Excel_RangeWrite($oWorkbook, 'Data', 'Standard Deviation', 'B1') _Excel_RangeWrite($oWorkbook, 'Data','Tag', 'C1') ;~ _ExcelWriteCell($oExcel, 'Standard Deviation', 1, 2) ;~ _ExcelWriteCell($oExcel, 'Tag', 1, 3) If IsArray($x_time) Then     _Excel_RangeWrite($oWorkbook, 'Data', $x_time, 'D1:' & _Excel_ColumnToLetter($top) & '1') Else     Local $label[1][$top + 3]     For $i = 1 To $top         _ArrAdd($label, $i,Default, 0, $i-1)     Next     _Excel_RangeWrite($oWorkbook, 'Data',$label, 'D1') EndIf Local $XValueRange = '=Data!R1C4:R1' & 'C' & $top + 2 Local $asDataRange[26] = [25], $asDataName[26] = [25] For $i = 1 To 25     $asDataRange[$i] = '=Data!R' & $i + 1 & 'C4:R' & $i + 1 & 'C' & $top + 2     $asDataName[$i] = '=Data!C' & $i + 1 Next _ExcelSheetActivate($oExcel, 2) _XLChart_ChartCreate($oExcel, 1, 4, 0, 'Top 25', $XValueRange, $asDataRange, $asDataName, True, 'Top 25') Local $r1, $r2, $c = 1 For $i = 1 To $asDataRange[0]     $mod = Mod($i, 4)     Switch $mod         Case 1             $r = 'A' & $c & ':F' & $c + 13         Case 2             $r = 'G' & $c & ':L' & $c + 13         Case 3             $r = 'M' & $c & ':R' & $c + 13         Case 0             $r = 'S' & $c & ':X' & $c + 13             $c += 14     EndSwitch     $out = _XLChart_ChartCreate($oExcel, 2, 4, $r, $asDataName[$i], $XValueRange, $asDataRange[$i], $asDataName[$i], False, $asDataName[$i]) Next ProgressOff() MsgBox(0,'',TimerDiff($timer)) Local $test = False Do     $fout = FileSaveDialog('Output File,', @ScriptDir, "Excel files (*.xls)")     If FileExists($fout) Then         $mout = MsgBox(36, 'Overwriite', $fout & ' already exist. Overwrite?')         If $mout = 6 Then             $dout = FileDelete($fout)             If $dout = 0 Then                 MsgBox(0, $fout, 'Failed to save ' & $fout & '.' & @CRLF & 'Please close excel or choose a different filename.')             Else                 $test = True             EndIf         EndIf     Else         $test = True     EndIf Until $test = True If $fout <> 1 Then _ExcelBookSaveAs($oExcel, $fout) $oExcel.Application.Visible = 1 Func _file_to_arr($name, ByRef $arr)     Local $file = FileOpen($name, 0)     ; Check if file opened for reading OK     If $file = -1 Then         MsgBox(0, "Error", "Unable to open " & $name)         Exit     EndIf     ; Read in lines of text until the EOF is reached     While 1         Local $line = FileReadLine($file)         If @error = -1 Then ExitLoop         _ArrAdd($rec, $line)     WEnd     FileClose($file) EndFunc   ;==>_file_to_arr Func _ArrAdd(ByRef $array, $data, $delimt = False, $row = -1, $col = -1)     Select         Case $delimt             Local $t = StringSplit($data, $delimt)             If @error = 0 Then                 $acol = Ubound($array,2)                 $arow = Ubound($array)                 If $t[0] >= $acol Then                     $acol = $t[0] * 2                     ReDim $array [$arow][$acol]                 EndIf                 $array[0][0] += 1                 If $arow = $array[0][0] Then ReDim $array[$arow * 2][$acol]                 For $f = 1 To $t[0]                     $array[$array[0][0]][$f - 1] = $t[$f]                 Next             EndIf         Case $col > -1 And $row > -1             $acol = Ubound($array,2)             $arow = Ubound($array) ;~          ConsoleWrite ($arow & ':' & $row  & @tab & $acol & ':' & $col & @CRLF)             If $col >= $acol Then Redim $array[$arow][$col *2]             If $row >= $arow Then ReDim $array[$row * 2][$acol]             $array[$row][$col] = $data         Case  $delimt = False And $row = -1 And $col = -1             $array[0] += 1             If UBound($array) = $array[0] Then ReDim $array[$array[0] * 2]             $array[$array[0]] = $data         EndSelect EndFunc   ;==>_ArrAdd

Autoit 3.3.9.22



#15 water

water

    ?

  • MVPs
  • 15,759 posts

Posted 08 December 2013 - 07:33 PM

Thanks for the update!


UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki





Also tagged with one or more of these keywords: Excel, Chart, Examples

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users