Databar formatting in Excel using Actxserver

Question

Johannes Baumgarten 2018-10-4

1
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/422178-databar-formatting-in-excel-using-actxserver

编辑： Johannes Baumgarten 2018-10-22

Im using actxserver to write data to an excel sheet and to format it. I managed to color cells and to add frames to the cells, but I am stuck at adding databars.

The corresponding VBA code would be:

   Range("D7:D217").Select
      Selection.FormatConditions.AddDatabar
      Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1)
          .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
          .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
      End With
      With Selection.FormatConditions(1).BarColor
          .Color = 13012579
          .TintAndShade = 0
      End With
      Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
      Selection.FormatConditions(1).Direction = xlContext
      Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
      Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
      Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
      With Selection.FormatConditions(1).AxisColor
          .Color = 0
          .TintAndShade = 0
      End With
      With Selection.FormatConditions(1).NegativeBarFormat.Color
          .Color = 255
          .TintAndShade = 0
      End With

How can I change the code to make it work in Matlab? Any advice, also a link to general advice on how to use actxserver with Excel (i.e. what changes to the VBA code need to be done) is appreciated. Thanks!

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Sarabjit Kheberi 2018-10-10

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/422178-databar-formatting-in-excel-using-actxserver#answer_340785

Since you have a working VBA script, you can create a macro with your code. You can then run an excel macro from MATLAB using the instructions in the below MATLAB answers page:

https://www.mathworks.com/matlabcentral/answers/100938-how-can-i-run-an-excel-macro-from-matlab

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Johannes Baumgarten 2018-10-11

Thank you for your answer! I found my way around it by formatting an empty excel sheet and copying it into the used one every time the program is used. But your way would work as well especially with more complicated topics.

请先登录，再进行评论。

Answer 2

Johannes Baumgarten 2018-10-22

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/422178-databar-formatting-in-excel-using-actxserver#answer_342630

编辑：Johannes Baumgarten 2018-10-22

在 MATLAB Online 中打开

If someone ever gets the same problem, I found the solution myself, using the invoke command. Excel is openend in Actxserver.

     dummy_range = 'A1:A100'; 
     Excel.Range(dummy_range).Select;
     Databar = invoke(Excel.Selection.FormatCondition, 'AddDatabar');
     Databar.BarFillType = 'xlDataBarFillSolid';
     Databar.AxisPosition = 'xlDataBarAxisAutomatic';
     Databar.BarColor.ThemeColor =  5;
     Databar.NegativeBarFormat.ColorType = 'xlDataBarSameAsPositive';