T
Techno_Dex
For anyone out there looking to use OWC11 with C# (or VB for that matter) in
..NET 2.0, here are some pointers and hints that I have discovered the hard
way using the PivotTable and Chart Controls. Note: this is not using VSTO
but rather referencing the OWC Interop and dropping the controls onto a
WinForm.
* OWC Colors are very screwy using .NET. The color properties expect and
object and that object better be an int. I .Net there are a few properties
on the Color class that will spit out an int value. The one you want is
Color.FromArgb(). The problem is .NET colors are in RGB format and return a
value between -16777216 (Black) and -1 (White). The OWC control expects an
int between 0 (Black) and 16777215 (White). This means you must shift your
int value from the FromtArgb() method into the positive realm by adding
16777216 to it. Now here is where the really screwy thing is, the OWC
control expects colors in BGR format (not RGB). This mean you must break
your color down into the Red, Green, Blue components and flip the Red and
Blue pieces inside the FromArgb method. The output should look something
like .BackColor = Color.FromArgb(Color.Orange.B, Color.Orange.G,
Color.Orange.R) + 16777216
* During development you might find things don't behave the way you expect.
This could possibly be due to the COM interop. If you suspect this to be
the problem, then create a new variable of the specified type and assign it
then use that variable to assign parameters you desire.
* When setting colors in a PivotTable, try to get to the PivotField
variable then set the properties from this (i.e.
pfPivotField.GroupedBackColor = 20000, pfPivotField.SubtotalLabelBackColor =
10000)
* There are multiple ways to get to certain properties, some are a real
pain and some are very easy. If you find yourself writing a recursive
function to get a nested columns or rows in a PivotTable, then look for an
easier way
* When working with Chart Controls, there are a lot of ReadOnly properties
but you will not find this out until Runtime. Some of the Properties appear
intuitive but in reality they are not. Take ChChart.Title.Interior.Color.
This looks like a property that you can set. You would be wrong. The way
to set this is by using a method ChChart.Title.Interior.SetSolid(100000).
When using the Chart Control look for methods to set values instead of
assigning properties.
* The above comment doesn't hold true for Fonts. You can set the
Font.Color property. so ChChart.Title.Font.Color = 0 is correct. I would
suggest you look for a method first then try setting a property. The
problem with this is that usually the Method is a level deeper in the Object
Model than the property.
* The Chart Control apparently only supports 10 different colors when
graphing and will reuse the first 10 colors set. I also wrote a function
trying to set colors with different Alpha values to make the color more
transparent but this doesn't work in the OWC.
* In both the PivotTable and Chart Control, try checking the properties and
counts before setting values otherwise you will be catching COMExceptions
all over the place and they don't help half the time.
* When Coloring charts and Pivot Tables, you should listen to the
DataChanged event that way you have access to all of the Columns and Rows
that the user has added/deleted
Hope this helps someone out there as documentation for OWC sucks.
..NET 2.0, here are some pointers and hints that I have discovered the hard
way using the PivotTable and Chart Controls. Note: this is not using VSTO
but rather referencing the OWC Interop and dropping the controls onto a
WinForm.
* OWC Colors are very screwy using .NET. The color properties expect and
object and that object better be an int. I .Net there are a few properties
on the Color class that will spit out an int value. The one you want is
Color.FromArgb(). The problem is .NET colors are in RGB format and return a
value between -16777216 (Black) and -1 (White). The OWC control expects an
int between 0 (Black) and 16777215 (White). This means you must shift your
int value from the FromtArgb() method into the positive realm by adding
16777216 to it. Now here is where the really screwy thing is, the OWC
control expects colors in BGR format (not RGB). This mean you must break
your color down into the Red, Green, Blue components and flip the Red and
Blue pieces inside the FromArgb method. The output should look something
like .BackColor = Color.FromArgb(Color.Orange.B, Color.Orange.G,
Color.Orange.R) + 16777216
* During development you might find things don't behave the way you expect.
This could possibly be due to the COM interop. If you suspect this to be
the problem, then create a new variable of the specified type and assign it
then use that variable to assign parameters you desire.
* When setting colors in a PivotTable, try to get to the PivotField
variable then set the properties from this (i.e.
pfPivotField.GroupedBackColor = 20000, pfPivotField.SubtotalLabelBackColor =
10000)
* There are multiple ways to get to certain properties, some are a real
pain and some are very easy. If you find yourself writing a recursive
function to get a nested columns or rows in a PivotTable, then look for an
easier way
* When working with Chart Controls, there are a lot of ReadOnly properties
but you will not find this out until Runtime. Some of the Properties appear
intuitive but in reality they are not. Take ChChart.Title.Interior.Color.
This looks like a property that you can set. You would be wrong. The way
to set this is by using a method ChChart.Title.Interior.SetSolid(100000).
When using the Chart Control look for methods to set values instead of
assigning properties.
* The above comment doesn't hold true for Fonts. You can set the
Font.Color property. so ChChart.Title.Font.Color = 0 is correct. I would
suggest you look for a method first then try setting a property. The
problem with this is that usually the Method is a level deeper in the Object
Model than the property.
* The Chart Control apparently only supports 10 different colors when
graphing and will reuse the first 10 colors set. I also wrote a function
trying to set colors with different Alpha values to make the color more
transparent but this doesn't work in the OWC.
* In both the PivotTable and Chart Control, try checking the properties and
counts before setting values otherwise you will be catching COMExceptions
all over the place and they don't help half the time.
* When Coloring charts and Pivot Tables, you should listen to the
DataChanged event that way you have access to all of the Columns and Rows
that the user has added/deleted
Hope this helps someone out there as documentation for OWC sucks.