Cell Style Alignment on a range

Question

I'm having a problem fromatting cells in an excel sheet. For some reason my code seems to be changing the style of all cells when I just want to change the style of a few specified, or a specified range.

Here's some of the code that I am using:

app = new Microsoft.Office.Interop.Excel.Application();
workbook = app.Workbooks.Add(1);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];

//Change all cells' alignment to center
worksheet.Cells.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

//But then this line changes every cell style back to left alignment
worksheet.Cells[y + 1, x + 2].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

Why would it change the style of multiple cells when I set it to just work on one? Is it not supposed to work how I want it to? Is there another way of doing this?

Solution 1

Based on this comment from the OP, "I found the problem. apparentlyworksheet.Cells[y + 1, x + 1].HorizontalAlignment", I believe the real explanation is that all the cells start off sharing the same Style object. So if you change that style object, it changes all the cells that use it. But if you just change the cell's alignment property directly, only that cell is affected.

Solution 2

This works good

worksheet.get_Range("A1","A14").Cells.HorizontalAlignment = 
                 Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

Solution 3

Maybe declaring a range might workout better for you.

// fill in the starting and ending range programmatically this is just an example. 
string startRange = "A1";
string endRange = "A1";
Excel.Range currentRange = (Excel.Range)excelWorksheet.get_Range(startRange , endRange );
currentRange.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

Solution 4

Modifying styles directly in range or cells did not work for me. But the idea to:

1. create a separate style
2. apply all the necessary style property values
3. set the style's name to the Style property of the range

, given in MSDN How to: Programmatically Apply Styles to Ranges in Workbooks did the job.

For example:

var range = worksheet.Range[string.Format("A{0}:C{0}", rowIndex++)];
range.Merge();
range.Value = "some value";

var style = workbook.AddStyle();
style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

range.Style = style.Name;

Solution 5

  ExcelApp.Sheets[1].Range[ExcelApp.Sheets[1].Cells[1, 1], ExcelApp.Sheets[1].Cells[70, 15]].Cells.HorizontalAlignment =
                 Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

This works fine for me.

View more solutions