Getting Excel Border Styles in C#

Question

Excel border styles in C# are well explored across the internet (here on Stack as much as anywhere) but I've been totally unable to find any documentation covering getting style values; every question, page, and PDF I've found only discusses setting.

How does one get style data from the excel borders in a useful form? (Say, String)

The problem, I'm sure, has something to do with the fact that I'm an absolute C# novice. Dynamic types confuse and scare me (I miss Java) and this problem may very well center around that. I'm trying to retrieve various styles from ranges in Excel (the ranges may be cells, rows, or entire tables) and translate them into some other form (for demonstration purposes, I've rewritten my code to write HTML styles inline as a String).

using Excel = Microsoft.Office.Interop.Excel;

public static String rangeStyle2InlineHTMLStyle(Excel.Range range)
{
    String str = "";
    Excel.Style style = range.Style;

    Excel.Border border_top = style.Borders[Excel.XlBordersIndex.xlEdgeTop];
    Excel.Border border_left = style.Borders[Excel.XlBordersIndex.xlEdgeLeft];
    Excel.Border border_right = style.Borders[Excel.XlBordersIndex.xlEdgeRight];
    Excel.Border border_bottom = style.Borders[Excel.XlBordersIndex.xlEdgeBottom];

    Console.WriteLine(border_top.Color.ToString());

    str += "border-top-color:" + color2CSSRGB(border_top.Color) + "; ";
    str += "border-left-color:" + color2CSSRGB(border_left.Color) + "; ";
    str += "border-right-color:" + color2CSSRGB(border_right.Color) + "; ";
    str += "border-bottom-color:" + color2CSSRGB(border_bottom.Color) + "; ";

    return "style='" + str + "'";
}

public static String color2CSSRGB(Object c)
{
    return "rgb(" + c.R + "," + c.G + "," + c.B + ")";
}

Everything underneath Excel.Style seems to be of dynamic type, and I have no clue how to use that. MSDN documentation is wonderfully obtuse: There's no clear indication what the members of the Color property are, or what sort of object type I can expect it to return.

I guess this is intentional, that way any one of a number of different color types can be used and even returned, but it's not immediately useful when all I want to know is what's going on in border x... Then again, it's just as likely that this is all entirely the wrong approach.

Thanks

chancea · Accepted Answer · 2013-07-09T18:49:32.923

1

I will completely redo my answer based on the new information as I misunderstood the question earlier:

Although I do not understand the actual problem as to why you need to convert the styles to strings, still you can make multiple methods that each take in an Excel.Border and then return the corresponding string values you desire

For getting the color

you can have something like

    public static String getBorderColor(Excel.Border border)
    {
        String retval = "";
        retval += " rgb(";
        System.Drawing.Color color = Color.FromArgb((int)border.Color);
        retval += color.R + ",";
        retval += color.G + ",";
        retval += color.B + ")";
        return retval;
    }

For getting the weight since all line styles and weight styles are just basically integers and so they will not convert to strings as you want them. They would appear as their assigned numeric value as such you are going to have to use either if statements or switch case statements and traverse through all the styles... so something like:

    public static String getWeight(Excel.Border border)
    {
        String retval = "weight = ";
        int weight = border.Weight;

        const int xlThick = (int)Excel.XlBorderWeight.xlThick;

        switch (weight)
        {
            case xlThick:
                retval += "thick";
                break;
            //... continue for all border weights
        }

        return retval;

    }

you can repeat that for line styles and such... Hopefully that helps you

edited Jul 09 '13 at 18:49

answered Jul 08 '13 at 16:08

chancea

5,858
3
29
39

This doesn't seem to set the border style, only check if it's equal to another value (which is really just getting the style, I think). I could be misreading, though. Could you explain a little further? – Sandy Gifford Jul 08 '13 at 16:11
Maybe I am not understanding completely what you mean by `border style` This would get you the border around a `range` Are you only looking specifically for the color of the border? – chancea Jul 08 '13 at 16:18
I'm trying to *get* the assorted border styles (color, style, width). For example, if (in the Excel application) I set the color of a cell's border to a full red, I want my function to return a `String`: "rgb(255,0,0)" for that cell (as passed to the function by the "range" parameter). Similarly for `style`, I'd like to be able to set a cell's border to dashed (or whatever) and for my function to return the String "dashed". – Sandy Gifford Jul 08 '13 at 17:01
Sorry: I just realized in my first comment I had accidentally said "set the border style" instead of "get the border style". – Sandy Gifford Jul 08 '13 at 17:02
@SandyGifford I still dont know if I understand your question but I did update my answer – chancea Jul 08 '13 at 19:01
Maybe it's better to stick to colors as an example. Borders are a little easier to get, since there are only a few options, but I can't make a case statement for every single color. (`switch(color){case Color.Red: ... }`). – Sandy Gifford Jul 08 '13 at 19:14
@SandyGifford My answer already contains an example for colors – chancea Jul 09 '13 at 18:16
Holy crap! I missed that in the edit! That's ***EXACTLY*** what I need! Thanks! – Sandy Gifford Jul 09 '13 at 18:42
Do me a favor and edit the answer so I can upvote it. Just put in some formatting or something. – Sandy Gifford Jul 09 '13 at 18:43

Getting Excel Border Styles in C#

1 Answers1