msexceller

I am also having a lot of trouble with excel borders. I am trying to create a new named style based on an existing style. I create a new style first and then copy all the style properties from the existing style and apply them all to the newly created style one by one. While doing that, borders are not getting copied correctly. No matter what I do, the new style get black(automatic colorIndex) borders, even though the old style border colors are gray.

first - Is there an easier way to duplicate an existing style (using C#)
second - are there any tricks related to borders MSDN documentation is not enough.

Please let me know if you would like me to post the code here...
thanks for your help in advance.


Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

msexceller

Came across some other postings and that solved my problem for now. I guess, borders collection in Range object differs than that in Style object and it makes no sense at all!!!!

First, you need to do a cast like this...otherwise excel will mistakenly think that left border is actually top border!

Excel.XlBordersIndex topIndex = (Excel.XlBordersIndex)Excel.Constants.xlTop;
Excel.XlBordersIndex bottomIndex = (Excel.XlBordersIndex)Excel.Constants.xlBottom;
Excel.XlBordersIndex leftIndex = (Excel.XlBordersIndex)Excel.Constants.xlLeft;
Excel.XlBordersIndex rightIndex = (Excel.XlBordersIndex)Excel.Constants.xlRight;

Excel.Border leftBorder = source.Borders[leftIndex];
Excel.Border rightBorder = source.Borders[rightIndex];
Excel.Border topBorder = source.Borders[topIndex];
Excel.Border bottomBorder = source.Borders[bottomIndex];


Another silly thing I noticed is this:

If you apply colorIndex before the linestyle , the result is incorrect. The color will just be plain black (automatic). So, you will need to do it the other way around. - apply linestyle first, then apply colorindex.

I don't see any good reason why the things are as they are. Is there any





Re: Visual Studio Tools for Office Remove borders from Excel charts using C#

Cindy Meister

msexceller wrote:
Came across some other postings and that solved my problem for now. I guess, borders collection in Range object differs than that in Style object and it makes no sense at all!!!!

First, you need to do a cast like this...otherwise excel will mistakenly think that left border is actually top border!

Excel.XlBordersIndex topIndex = (Excel.XlBordersIndex)Excel.Constants.xlTop;
Excel.XlBordersIndex bottomIndex = (Excel.XlBordersIndex)Excel.Constants.xlBottom;
Excel.XlBordersIndex leftIndex = (Excel.XlBordersIndex)Excel.Constants.xlLeft;
Excel.XlBordersIndex rightIndex = (Excel.XlBordersIndex)Excel.Constants.xlRight;

Excel.Border leftBorder = source.Borders[leftIndex];
Excel.Border rightBorder = source.Borders[rightIndex];
Excel.Border topBorder = source.Borders[topIndex];
Excel.Border bottomBorder = source.Borders[bottomIndex];


Another silly thing I noticed is this:

If you apply colorIndex before the linestyle , the result is incorrect. The color will just be plain black (automatic). So, you will need to do it the other way around. - apply linestyle first, then apply colorindex.

I don't see any good reason why the things are as they are. Is there any

The things you're noticing are due to how the Primary Interop Assemblies are "interpreting" the Excel object model. It's important to remember that Office applications are COM, and comparatively old. They don't follow all the rules the .NET Framework uses.

For instance, in the Office world an Enum constant, such as Excel.XlBordersIndex is the equivalent of System.Int32 (a Long Integer in the COM world). In addition, the Excel PIA passes pretty much everything to C# as an object. So in order to create an object of this type, you must use a cast.

Also, in the Office world you cannot apply attributes to something that doesn't yet exist. So, if you apply the color before there's a border, the color assignment is simply lost. The border has to be there, first.

Please note, also, that you'll have a better chance of receiving an answer if you post a new question. If there are things in another post that you feel are relevant to your problem, you can include a link in your post. I'm going to split your messages and this answer off into a separate message thread.