Databar formatting in Excel using Actxserver

2 次查看（过去 30 天）
Johannes Baumgarten
编辑： 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!

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

采纳的回答

Sarabjit Kheberi
Sarabjit Kheberi 2018-10-10
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 个评论
Johannes Baumgarten
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.

请先登录，再进行评论。

更多回答（1 个）

Johannes Baumgarten
Johannes Baumgarten 2018-10-22
编辑：Johannes Baumgarten 2018-10-22
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';
Once you know how to do it, it gets quite self explanatory.

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by