0

I need to add borders to excel cells in Automatic colour using c# language. Given below is the coding that i use. But it doesn't add any borders to the cell. Can you please let me know what i have done wrong here:

i'm not getting any border designing functionalities when i try to specify border styles to cells: enter image description here

 Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            xlApp.Visible = false;
            xlWorkBook = xlApp.Workbooks.Open(textBox1.Text, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            int i = 0;
            int j = 0;

            for (i = 0; i <= dataGridView1.RowCount - 1; i++)
            {
                for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
                {
                    DataGridViewCell cell = dataGridView1[j, i];
                    xlWorkSheet.Cells["19", "I"] = "Availablility";                         
                    xlWorkSheet.Cells[i + 20, j + 9] = cell.Value;                                                               
                    xlWorkSheet.Cells.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
                }
            }
hanzi_ru
  • 153
  • 5
  • 23
  • I am not sure your Color attribute is ok. Does it work if you delete the last argument? In addition, the doc says "You must specify either ColorIndex or Color, but not both. You can specify either LineStyle or Weight, but not both. If you don't specify either argument, Microsoft Excel uses the default line style and weight" – JohnB Oct 19 '12 at 06:08
  • @John if i remove any argument it throws an error called "No overload for method "BorderAround" takes '3' arguments". I guess that i need to include all the four arguments coz if i remove all it throws an error called "No overload for method "BorderAround" takes '0' arguments" – hanzi_ru Oct 19 '12 at 06:28

4 Answers4

1

try this.Not posting the entire code

Excel.Worksheet oSheet;
Excel.Range oRange;
oRange = oSheet.get_Range("Q3", "Q40");

oRange.Font.Color= System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
oRange.Cells.Borders.Color=System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);

Karthik
  • 2,391
  • 6
  • 34
  • 65
  • hi, thanks for the reply. But it gives a runtime error called "Methods with System.Color parameters or return type cannot be invoked via IDispatch". And im unable to define a range as it varies according to the input excel sheet – hanzi_ru Oct 23 '12 at 04:39
  • @hansikaattanayake see the edit.I think there are ways to pass the range dynamically – Karthik Oct 23 '12 at 05:48
0

I think you need to select cells first. The following code works for me:

using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelInteropDemo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application excel = new Excel.Application();
            excel.Workbooks.Add();
            excel.Visible = true;
            excel.ActiveWorkbook.ActiveSheet.Range("a1").EntireRow.Select();
            excel.Selection.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, 
                Excel.XlColorIndex.xlColorIndexAutomatic, 
                System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(79, 129, 189)));
        }
    }
}

Update 2012-10-23: After some discussion in the comments and the original post has been updated, the problem became all about IntelliSense, instead of the syntax of Excel Interop.

Michael Tsai
  • 661
  • 7
  • 17
  • Do you intend to set all cells' border style, or just the cells that you created in the for loop? If all cells, it should be easy ([see here](http://stackoverflow.com/questions/4850738/how-to-select-the-whole-excel-sheet-in-excel-range-object-of-c)) If you just want to set specific cells, maybe you can try something like this: `xlWorkSheet.Cells[i + 20, j + 9].BorderAround(...);` – Michael Tsai Oct 19 '12 at 09:58
  • Are you sure about that? The following code works on my machine without any problem (I'm using Excel 2010): `xlWorkSheet.Cells[1,1].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(79, 129, 189)));` – Michael Tsai Oct 21 '12 at 12:37
  • I think you can't use xlWorkSheet.Cells.BorderAround because it couldn't determine what cells you are trying to set. You need to explicitly specify a cell or a cells range. – Michael Tsai Oct 22 '12 at 05:39
  • yes. But when i try to specify a cell it do not provide any of the functionalities. I have attached a screenshot – hanzi_ru Oct 22 '12 at 07:11
  • Please ignore that (we are dealing with dynamic call, IntelliSense might not work here). Just type the code in, you will find it works. If you really care about IntelliSense, try this: `(xlWorkSheet.Cells[1,1] as Excel.Range).BorderAround2(....);` – Michael Tsai Oct 22 '12 at 07:49
  • If i manually type, it outputs this error message "'object' does not contain a definition for 'BorderAround' and no extension method 'BorderAround' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)" – hanzi_ru Oct 23 '12 at 03:32
  • That's interesting. The code works both with VS2010 and VS2012 on my machine. Base on the information you provided, which is not much, I have no further idea. – Michael Tsai Oct 23 '12 at 05:55
  • If you would write a simple test program and use the exact code from my answer, and found it still didn't work. You can zip and upload the sample project to somewhere I can download. And I will take a look at it. (and, what's the version of your Excel?) – Michael Tsai Oct 23 '12 at 06:08
  • thanks for the support. I got an answer which works from Karthi. thanks again for your effort – hanzi_ru Oct 23 '12 at 06:10
0

If you are just trying to put a border around the table you've created, once you've built the whole table try:

xlWorksheet.Cells ["19", "I"].CurrentRegion.BorderAround(xlContinuous, xlMedium, xlColorIndexAutomatic);

Your code is currently trying to put a border around the whole worksheet

SWa
  • 4,343
  • 23
  • 40
  • hi, thanks for the reply. i'm not getting the CurrentRegion functionality if i specify a cell. I have attached a screen shot in my query. – hanzi_ru Oct 22 '12 at 07:09
  • yes. then it outputs the given error message "'object' does not contain a definition for 'CurrentRegion' and no extension method 'CurrentRegion' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)" – hanzi_ru Oct 23 '12 at 03:34
0

As you need to add just border so try the following code. it works for me. 

  private void AllBorders(Excel.Borders borders)
    {
           borders.Color = System.Drawing.Color.Black;            
    }

//Call the function now. 

AllBorders(activeWorkSheet.get_Range(Cell1: "A1",  Cell2: "lastcellname").Cells.Borders);

And If you want to set border just LEFT/RIGHT/TOP/Bottom then use the following code. 

borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium;            
            borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.Color.Black;

Set the edges according to your requirement I have enabled the border for RIGHT side only with "xlEdgeRight"

Vivek Arora
  • 1