How can I get my wrapped column headings to horizontally align?

Question

I'm creating a "column header row" on an Excel spreadsheet that ends up looking, in part, like this:

Note how the column headings that span multiple "lines" are not aligned horizontally within their cell. The last "line" seems well-centered, but those before/above it are off-center. e.g., with "Member Item Code", "Member" is way too far to the left, and "Item" is too far to the left also (but less so), while "Code" looks perfect. But my code is not really perfect, apparently.

I am applying horizontal alignment of "center" to these cells:

var unitHeaderCell = (Excel.Range)_xlSheet.Cells[rowToPopulate, DETAIL_UNIT_COL];
unitHeaderCell.Value2 = "Unit";

var custNumHeaderCell = (Excel.Range)_xlSheet.Cells[rowToPopulate, DETAIL_CUSTNUM_COL];
custNumHeaderCell.Value2 = String.Format("Customer{0}Number", Environment.NewLine);
custNumHeaderCell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
custNumHeaderCell.WrapText = true;

var custNameHeaderCell = (Excel.Range)_xlSheet.Cells[rowToPopulate, DETAIL_CUSTNAME_COL];
custNameHeaderCell.Value2 = String.Format("Customer{0}Name", Environment.NewLine);
custNameHeaderCell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
custNameHeaderCell.WrapText = true;

var memberItemCodeHeaderCell = (Excel.Range)_xlSheet.Cells[rowToPopulate, DETAIL_MEMBERITEMCODE_COL];
memberItemCodeHeaderCell.Value2 = String.Format("Member{0}Item{0}Code", Environment.NewLine);
memberItemCodeHeaderCell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
memberItemCodeHeaderCell.WrapText = true;
memberItemCodeHeaderCell.ColumnWidth = DETAIL_MEMBERITEMCODE_COL_WIDTH;

var qtyHeaderCell = (Excel.Range)_xlSheet.Cells[rowToPopulate, DETAIL_QTY_COL];
qtyHeaderCell.Value2 = "Qty";

var bidPriceHeaderCell = (Excel.Range)_xlSheet.Cells[rowToPopulate, DETAIL_BIDPRICE_COL];
bidPriceHeaderCell.Value2 = "Bid Price";

What must I do to get all of these words to center (not just the last one)?

Answer 1

You don't need to add Environment.NewLine between words, you can simply use space between words and wrap the content of column, and based on column width, it will show in multiple lines.

In the below code, I set all cells in the range to wrap text and to be vertically and horizontally centered.

Also just for test I set the column width of 5th column.

//Using
using XL = Microsoft.Office.Interop.Excel;

//Code
XL.Application application = new XL.Application();
application.Visible = true;
var book = application.Workbooks.Add();
var sheet = book.Worksheets[1];

sheet.Cells[1, 1].Value2 = "Unit";
sheet.Cells[1, 2].Value2 = "Customer Number";
sheet.Cells[1, 3].Value2 = "Customer Name";
sheet.Cells[1, 4].Value2 = "Member Item Code";
sheet.Cells[1, 5].Value2 = "Qty";
sheet.Cells[1, 5].ColumnWidth = 30;
sheet.Cells[1, 6].Value2 = "Bad Price";

var range = sheet.Range("A1", "F1");
range.VerticalAlignment = XL.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = XL.XlHAlign.xlHAlignCenter;
range.WrapText = true;

And here is the result with default font settings:

And this is the result using customized font settings:

range.Font.Name = "Tahoma";
range.Font.Bold = true;
range.Font.Size = 9;