Set text alignment to center in Excel document using OpenXML with C #
I have a document that my asp.net page creates and I need to align the text of certain columns in the center. I opened the document after manually placing the columns in the OpenXML SDK, but the reflected code did not achieve the desired result.
This is how I set the custom widths of these columns, and I would like to add to this function (method, whatevs) the ability to center the text:
private static Column CreateColumnData(UInt32 StartColumnIndex, UInt32 EndColumnIndex, double ColumnWidth)
{
Column column;
column = new Column();
column.Min = StartColumnIndex;
column.Max = EndColumnIndex;
column.Width = ColumnWidth;
column.CustomWidth = true;
//the SDK says to add this next line to center the text but it doesn't work
column.Style = (UInt32Value)6U;
return column;
}
I'm open to another way, but I think the solution should be very simple, I just can't get it. If anyone can help, that would be great.
NOTE. Please be aware that I am using OpenXML and not using Microsoft.Office.Interop.Excel
I think the problem is that you are trying to create a column when it is individual cells that need to be formatted to use a specific horizontal alignment. p>
I looked around and found the following MSDN documentation:
http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.cellformat_properties.aspx
http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.alignment.aspx
I also found some sample code here (although I haven't tested it myself):
http://blogs.msdn.com/b/chrisquon/archive/2009/11/30/stylizing-your-excel-worksheets-with-open-xml-2-0.aspx
I use Interop most of the time on my own and know that I was creating cells, not columns or rows, when I wrote my tables.
You should be able to create one style and just apply it to the cells as you create them.
The following approach works fine for me
//using OfficeOpenXml;
//using OfficeOpenXml.Style;
workSheet.Cells[rowIndex, 22].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
LITERATURE
- Required basic formatting using the open XML SDK
- Coloring cells in excel sheet using openXML in c #
- Cell Styles in an OpenXML Spreadsheet (SpreadsheetML)
you can try this to have a merged cell, edit height and columns and align horizontally and vertically center
var worksheet = wb.Worksheets.Add("Overzicht");
// Adding text
worksheet.Cell("B2").Value = "Overzicht activiteit";
var rngMainTitle = worksheet.Range("B2:E3");
rngMainTitle.FirstCell().Style
.Font.SetBold()
.Fill.SetBackgroundColor(XLColor.CornflowerBlue)
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
//Merge title cells
rngMainTitle.FirstRow().Merge();
worksheet.Column(2).Width = 31;
worksheet.Column(3).Width = 18;
worksheet.Column(4).Width = 18;
worksheet.Column(5).Width = 18;
worksheet.Row(2).Height = 25;
Try
workSheet_range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;