CODEWITHSUNDEEP
c#excelformatalignment
kschieck
kschieck

Reputation: 1447

Cell Style Alignment on a Range

I'm having a problem formatting 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?

Upvotes: 40

Views: 158933

Answers (9)

Abdul Khaliq
Abdul Khaliq

Reputation: 2285

In my case, it works fine..

>  worksheet.Range("A4:D4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

Upvotes: 0

Ionut Radu
Ionut Radu

Reputation: 29

Manikandan's answer is good. For SpreadSheetGear users (a C# framework for interacting easier with spreadsheets) try this:

workbook.Worksheets[0].Cells["B1:B4"].HorizontalAlignment = HAlign.Center;. This will align all the cells in your sheet from B1 to B4 (column 2- row 1 through 4).

Upvotes: -1

NoWar
NoWar

Reputation: 37633

Something that works for me. Enjoy.

Excel.Application excelApplication =  new Excel.Application()  // start excel and turn off msg boxes
{
     DisplayAlerts = false,
     Visible = false
};

Excel.Workbook workBook = excelApplication.Workbooks.Open(targetFile);
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];

var rDT = workSheet.Range(workSheet.Cells[monthYearNameRow, monthYearNameCol], workSheet.Cells[monthYearNameRow, maxTableColumnIndex]);
rDT.Merge();
rDT.Value = monthName + " " + year;
var reportDateRowStyle = workBook.Styles.Add("ReportDateRowStyle");
reportDateRowStyle.HorizontalAlignment = XlHAlign.xlHAlignCenter;
reportDateRowStyle.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
reportDateRowStyle.Font.Bold = true;
reportDateRowStyle.Font.Size = 14;
rDT.Style = reportDateRowStyle;

Upvotes: 0

Sheep_Sleep
Sheep_Sleep

Reputation: 31

Don't use "Style:

worksheet.Cells[y,x].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

Upvotes: 3

Reginardo Tribuzi Lula Júnior
Reginardo Tribuzi Lula Júnior

Reputation: 83

  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.

Upvotes: 2

horgh
horgh

Reputation: 18534

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;

Upvotes: 1

crackhaus
crackhaus

Reputation: 1196

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;

Upvotes: 8

Manikandan
Manikandan

Reputation: 401

This works good

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

Upvotes: 40

DGH
DGH

Reputation: 11539

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.

Upvotes: 32

Related Questions