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?
Modifying styles directly in range or cells did not work for me. But the idea to:
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;
Don't use "Style:
worksheet.Cells[y,x].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
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;
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;
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).
This works good
worksheet.get_Range("A1","A14").Cells.HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
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.
Source: Stackoverflow.com