Sign in to follow this  
Followers 0
water

Color management for Excel Charts

7 posts in this topic

#1 ·  Posted (edited)

Is anyone familiar with the color management of Excel with charts? Is there any good reading you can recommend?

MSDN just gives a listing of all objects, methods and properties but no explanation how they are related and how to use them?

Properties like Color, ColorIndex, SchemeColor, ObjectThemeColor, ThemeColor confuse me a bit.

Any hint is greatly appreciated.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites



#3 ·  Posted (edited)

Taietel, thanks for the reply.

The pdf is a quite good reading and gives a lot of information what color to use for which purpose.

Where I need information is how to use the COM objects, methods and properties.

Color: lets you specify the color as red, green, blue values

ColorIndex: lets you specify the color as a number from 0-56 of the current color palette.

SchemeColor: When I want to color a fill I have to use SchemeColor (values from 0-56) but it seems to use a different color palette because I get different colors compared to ColorIndex.

So I need a good reading explaining how to use the different methods and properties. It should explain Excel 2007 and Excel 2010.

Thanks

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Thanks a lot! Exactly what I need to start. Will keep me busy for quite some time.

Do you know of something similar for Excel 2007 and later? I know that Microsoft made big changes to the charting engine after Excel 2003. Charts created in Excel 2003 or earlier look completely different in Excel 2007 (in respect of colors). Themes were added so there are a lot of possibilities in the newer versions of Excel.

The findings will go into our ExcelChart UDF. As soon as we understand how colors are handled by Excel 2007 and Excel 2010 we will release the first alpha of our UDF.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Thanks, I will give it a try tomorrow.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to write a value into an excel column.
      I know where it starts from, but i don't know what the end is, last non-empty cell.
      How can i get the number of last non-empty cell?
      Thanks in advance.
      Regards 
    • AndyS19
      By AndyS19
      I have a listview with 3 columns and I want to set the 3rd column's color based on the item's 'Param' value, but even though I set up for the 3rd column, the entire row always gets the color.
      My test code is below, but here is the pertinent code:
      Func WM_NOTIFY($hWnd, $iMsg, $iwParam, $ilParam) #forceref $hWnd, $iMsg, $iwParam, $ilParam Local $hWndFrom, $iCode, $tNMHDR $tNMHDR = DllStructCreate($tagNMHDR, $ilParam) $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom")) $iCode = DllStructGetData($tNMHDR, "Code") Switch $hWndFrom Case $hListView Switch $iCode Case $NM_CUSTOMDRAW Local $iDrawStage, $tCustDraw $tCustDraw = DllStructCreate($tagNMLVCUSTOMDRAW, $ilParam) $iDrawStage = DllStructGetData($tCustDraw, 'dwDrawStage') Switch $iDrawStage Case BitOR(0, $CDDS_ITEMPREPAINT) ;, $CDDS_SUBITEM) setItemColor($tCustDraw, $hWndFrom) Return $CDRF_NOTIFYSUBITEMDRAW EndSwitch EndSwitch EndSwitch Return $GUI_RUNDEFMSG EndFunc ;==>WM_NOTIFY Func setItemColor($tCustDraw, $hWnd) Local $iIndex, $color, $iSubitem $iIndex = DllStructGetData($tCustDraw, 'dwItemSpec') ; item number $color = _GUICtrlListView_GetItemParam($hWnd, $iIndex) $iSubitem = DllStructGetData($tCustDraw, 'iSubItem') ; Here, I want to only change the color of the 3rd col, and not the 1st and 2nd cols DllStructSetData($tCustDraw, 'iSubItem', 2) ; always use the 3rd column (the 'sample' col) logmsg("+++: setItemColor($tCustDraw, 0x" & Hex($hWnd) & ") entered ... " _ & ", $iIndex = " & $iIndex & ", $iSubItem = " & $iSubitem & ", $color = " & Hex($color)) DllStructSetData($tCustDraw, 'clrText', $color) ; set the Text color DllStructSetData($tCustDraw, 'clrTextBk', bitnot($color)) ; set the background color EndFunc ;==>setItemColor Func logmsg($msg, $lnum = @ScriptLineNumber) ConsoleWrite("+++:" & $lnum & ": " & $msg & @CRLF) EndFunc ;==>logmsg
      Here is the full test code:
      #include-once ;#cs #NoTrayIcon #AutoIt3Wrapper_UseUpx=n #AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 #AutoIt3Wrapper_UseX64=N Opt("GUICloseOnESC", 1) ; ESC closes GUI? (0 = no, 1 = yes) Opt("GUIOnEventMode", 1) ; Change to OnEvent mode Opt('MustDeclareVars', 1) OnAutoItExitRegister("_test_exit") Opt("GUIEventOptions", 1) ;0=default, 1=just notification, 2=GuiCtrlRead tab index Opt("WinTitleMatchMode", -2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase #include <GuiListBox.au3> #include <ListViewConstants.au3> #include <GuiListView.au3> #include <WindowsConstants.au3> #include <GUIConstantsEx.au3> #include <ColorConstants.au3> Global $__MCP_obj, $__MCP_GUI_hWnd, $idListView, $hListView Global $hMainWin test() Exit Func test() $hMainWin = GUICreate("Test window", 120, 100, @DesktopWidth / 2, -1) GUICtrlCreateButton("Hwnd", 10, 10, 40, 20) GUICtrlSetOnEvent(-1, "handle_1stTEST_btn") GUICtrlCreateButton("ID", 50, 10, 40, 20) GUICtrlSetOnEvent(-1, "handle_2ndTEST_btn") GUISetOnEvent($GUI_EVENT_CLOSE, "_test_exit") GUISetState(@SW_SHOW) While (1) Sleep(17) WEnd EndFunc ;==>test Func _myColorPicker($obj) logmsg("+++: $obj = 0x" & Hex($obj) & @CRLF) Local $flags = 0 Local $ww, $wh, $x, $y, $w, $h $__MCP_obj = $obj $ww = 350 $wh = 500 $x = 200 $y = 150 $__MCP_GUI_hWnd = GUICreate("myColorPicker", $ww, $wh, $x, $y, $flags) $x = 10 $y = 10 $w = 150 $h = 20 GUICtrlCreateButton("Set caller's color", $x, $y, $w, $h) GUICtrlSetOnEvent(-1, "__MCP_handle_SetColor_btn") $y += $h + 5 $w = $ww - 25 $h = $wh - 100 $idListView = GUICtrlCreateListView("", $x, $y, $w, $h, -1, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT)) $hListView = GUICtrlGetHandle($idListView) $x = $ww / 2 - 50 $y = $wh - 60 $w = 100 $h = 25 GUICtrlCreateButton("Quit Popup", $x, $y, $w, $h) GUICtrlSetOnEvent(-1, "__MCP_handle_QUIT_btn") GUICtrlSetFont($idListView, 8.5, -1, -1, "courier new") _GUICtrlListView_AddColumn($idListView, "Name", 100) _GUICtrlListView_AddColumn($idListView, "Value", 100) _GUICtrlListView_AddColumn($idListView, "Sample", 100) _GUICtrlListView_SetColumnWidth($idListView, 0, 100) _GUICtrlListView_SetColumnWidth($idListView, 1, 80) logmsg("+++: $idListView = " & $idListView) logmsg("+++: $hListView = 0x" & Hex($hListView)) __MCP_populateListView($hListView) GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY") GUISetState(@SW_SHOW, $__MCP_GUI_hWnd) EndFunc ;==>_myColorPicker Func WM_NOTIFY($hWnd, $iMsg, $iwParam, $ilParam) #forceref $hWnd, $iMsg, $iwParam, $ilParam Local $hWndFrom, $iCode, $tNMHDR $tNMHDR = DllStructCreate($tagNMHDR, $ilParam) $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom")) $iCode = DllStructGetData($tNMHDR, "Code") Switch $hWndFrom Case $hListView Switch $iCode Case $NM_CUSTOMDRAW Local $iDrawStage, $tCustDraw $tCustDraw = DllStructCreate($tagNMLVCUSTOMDRAW, $ilParam) $iDrawStage = DllStructGetData($tCustDraw, 'dwDrawStage') Switch $iDrawStage Case BitOR(0, $CDDS_ITEMPREPAINT) ;, $CDDS_SUBITEM) setItemColor($tCustDraw, $hWndFrom) Return $CDRF_NOTIFYSUBITEMDRAW EndSwitch EndSwitch EndSwitch Return $GUI_RUNDEFMSG EndFunc ;==>WM_NOTIFY Func setItemColor($tCustDraw, $hWnd) Local $iIndex, $color, $iSubitem $iIndex = DllStructGetData($tCustDraw, 'dwItemSpec') ; item number $color = _GUICtrlListView_GetItemParam($hWnd, $iIndex) $iSubitem = DllStructGetData($tCustDraw, 'iSubItem') ; Here, I want to only change the color of the 3rd col, and not the 1st and 2nd cols DllStructSetData($tCustDraw, 'iSubItem', 2) ; always use the 3rd column (the 'sample' col) logmsg("+++: setItemColor($tCustDraw, 0x" & Hex($hWnd) & ") entered ... " _ & ", $iIndex = " & $iIndex & ", $iSubItem = " & $iSubitem & ", $color = " & Hex($color)) DllStructSetData($tCustDraw, 'clrText', $color) ; set the Text color DllStructSetData($tCustDraw, 'clrTextBk', bitnot($color)) ; set the background color EndFunc ;==>setItemColor Func logmsg($msg, $lnum = @ScriptLineNumber) ConsoleWrite("+++:" & $lnum & ": " & $msg & @CRLF) EndFunc ;==>logmsg Func __MCP_exit() GUIDelete($__MCP_GUI_hWnd) EndFunc ;==>__MCP_exit Func __MCP_handle_SetColor_btn() If (IsHWnd($__MCP_obj)) Then GUISetBkColor($COLOR_BLUE, $__MCP_obj) Else GUICtrlSetBkColor($__MCP_obj, $COLOR_BLUE) EndIf EndFunc ;==>__MCP_handle_SetColor_btn Func __MCP_populateListView($hWnd) Local $text, $parts, $iIndex Local Static $aItems[] = [ _ "BLACK | " & $CLR_BLACK & "|" & "", _ "BLUE | " & $CLR_BLUE & "|" & "", _ "RED | " & $CLR_RED & "|" & "", _ "WHITE | " & $CLR_WHITE & "|" & "", _ "YELLOW | " & $CLR_YELLOW & "|" & "" _ ] _GUICtrlListView_BeginUpdate($hWnd) _GUICtrlListView_DeleteAllItems($hWnd) For $ndx = 0 To UBound($aItems) - 1 $text = $aItems[$ndx] $parts = StringSplit($text, "|", 2) $parts[0] = StringStripWS($parts[0], 3) $parts[2] = StringStripWS($parts[2], 3) $iIndex = _GUICtrlListView_AddItem($hWnd, $parts[0], -1, $parts[1]) _GUICtrlListView_AddSubItem($hWnd, $iIndex, Hex($parts[1]), 1) _GUICtrlListView_AddSubItem($hWnd, $iIndex, $parts[0], 2) Next _GUICtrlListView_EndUpdate($hWnd) EndFunc ;==>__MCP_populateListView Func __MCP_handle_QUIT_btn() GUIDelete($__MCP_GUI_hWnd) EndFunc ;==>__MCP_handle_QUIT_btn Func handle_1stTEST_btn() _myColorPicker($hMainWin) EndFunc ;==>handle_1stTEST_btn Func handle_2ndTEST_btn() _myColorPicker(@GUI_CtrlId) EndFunc ;==>handle_2ndTEST_btn Func _test_exit() Exit EndFunc ;==>_test_exit  
    • Nareshm
      By Nareshm
      Hi All,
      I have excel file like this
      and i want to cut cell/text from excel to other software.

       
      I have to cut the cell of B column one by one and past into other software
      If Winexists("No Data Found")
      then restore cuted cell and goto next/down side cell
      How to do it ?
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.