Set text align to center in an Excel document using OpenXML with C#

Question

I have a document that my asp.net page is creating and I need to align the text of certain columns to center. I have opened the document after manually center the columns in openXML SDK but the code that is reflected does not achieve the desired outcome.

This is how I am setting the custom widths of these columns and I would like to add to this function (method, whatevs) the capability 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 that the solution has got to be very simple I just can't seem to get it. If anyone can help that would be great.

NOTE: Please keep in mind that I am using OpenXML and will not be using Microsoft.Office.Interop.Excel

Columbary

Apparently it's not that simple. I wasn't able to change my method to incorporate text alignment. I had to go through StyleSheets. It's pretty long so I will probably delete this question unless someone just wants to see my solution. – Columbary

Answer 1

I think the issue is that you are trying to style the column, when it is individual cells that need to be formatted to use a specific horizontal alignment.

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 a code example here (Though I didn't test it myself):

http://blogs.msdn.com/b/chrisquon/archive/2009/11/30/stylizing-your-excel-worksheets-with-open-xml-2-0.aspx

I use the Interop most of the time myself, and know that I styled cells and not columns or rows when I wrote up my spreadsheets.

You should be able to create a single style and just apply it a bunch of times to cells as you create them.

Skintight

This is the solution that I went with. I was hoping that there was a way to style the columns but I don't think you can. I know you're right about being able to style just the cells and I think that might be the absolute only way. – Columbary

Yeah, I didn't notice your comment on your post until after I posted mine. Thanks. – Skintight

This one helped me a lot. Thanks – Vacationist

Answer 2

Following approach works fine for me

//using OfficeOpenXml;
//using OfficeOpenXml.Style;

workSheet.Cells[rowIndex, 22].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

REFERENCES

1. Required basic formatting using open XML SDK
2. Coloring cells in excel sheet using openXML in C#
3. Cell styles in OpenXML spreadsheet (SpreadsheetML)

Carcinomatosis

Answer 3

you can try this to have a merged cell, edit height and columns and align center horizontally and vertically

 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;

Pulmotor

Answer 4

You can just select your cell and set text align:

ws.Cell("A1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

ws is your worksheet:

 XLWorkbook workbook = new XLWorkbook();
        var ws = workbook.Worksheets.Add("Text align");

Rooky

Answer 5

In case you are looking how to aligne text in excel cell, you need to apply the alignement on the cellFormat objects

 CellFormat cellFormat = new CellFormat() { NumberFormatId = 0, FontId = 0, 
 FillId = 0, BorderId = 0, ApplyAlignment = true }; 
 Alignment alignment = new Alignment();
 alignment.Vertical = VerticalAlignmentValues.Center;
 alignment.Horizontal = HorizontalAlignmentValues.Center;
 CellFormat.Alignment = alignment;

you can read more in microsoft documentation

https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.cellformat.alignment?view=openxml-2.8.1

Tombac

Answer 6

Try this

workSheet_range.HorizontalAlignment =
     Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

Angell

I don't know if it works or not; but, the questioner already told that he was not using Interop Excel! – Opacity