{"id":54613,"date":"2009-01-14T11:55:00","date_gmt":"2009-01-14T11:55:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2009\/01\/14\/hey-scripting-guy-how-do-i-center-contents-of-a-column-in-office-excel\/"},"modified":"2009-01-14T11:55:00","modified_gmt":"2009-01-14T11:55:00","slug":"hey-scripting-guy-how-do-i-center-contents-of-a-column-in-office-excel","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-do-i-center-contents-of-a-column-in-office-excel\/","title":{"rendered":"Hey, Scripting Guy! How Do I Center Contents of a Column in Office Excel?"},"content":{"rendered":"

\"Hey, <\/h2>\n

Hey, Scripting Guy! When I add data to an Office Excel spreadsheet, it always seems to put stuff on the right side of the column. I would like to know how to center the contents of a column. I do not think it should be that hard, but I just cannot figure it out. <\/p>\n

– LK<\/p>\n

\"Spacer\"\"Hey,<\/p>\n

Hi LK,<\/p>\n

Did you try Open Sesame<\/a>? I can certainly sympathize with you. It can be really frustrating trying many different things in a script to see if it will work or not. I still, painfully, remember a situation many years ago, when I was trying to connect to the local WinNT repository on a remote computer, in another domain. There can, of course, be many potential issues from name resolution to security. I tried them all; at least I thought I had. I had been working on this script for more than six hours, and I kept trying and trying to get the thing to work. Finally, I tried \u201cWinNT\u201d with this exact capitalization. I was sure it couldn\u2019t be something so simple, but, capitalization was indeed the problem. It had to be capitalized in exactly this manner. When the script worked, I was not sure if I should be happy that it worked, or sad that I had wasted six hours of my life\u2014and not one reference I consulted during those six hours mentioned this little piece of trivia! Of course, all the examples were capitalized in this manner, but nothing told me that it had to be cased in this manner. This was even more frustrating because most of VBScript is case insensitive. Of course, when I later wrote the Microsoft VBScript Step by Step<\/a> book, I made certain to include a nice big warning to this fact. <\/p>\n\n<\/thead>\n\n\n
\n

The Office Excel automation model is documented here on MSDN<\/a>. Here are previous “Hey, Scripting Guy!” articles<\/a> that illustrate using VBScript to automate Microsoft Excel. The Office space archive<\/a> also has numerous examples of using VBScript with Office Excel. You can also find lots of examples of automating Office Excel in the Script Center Repository<\/a>. Perhaps I also saved the best for last. The Community-Submitted Scripts Center has a ton<\/i> of scripts that automate Office Excel. Here is the direct link to that gold mine of information<\/a>. Look in the Microsoft Office section. If you are new to using Microsoft PowerShell, you can get a jump-start on it with the resources in the Windows PowerShell hub<\/a>.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/div>\n

I am doing something I do not normally like to do: This script is basically useless. It does answer the question and is important from a learning standpoint, but unless your pointy-headed boss<\/a> (PHB) comes floating around and asks you, “Can you write a script that will populate the first row with the letters a, b, and c and center the letter a?” you will have no occasion to use the script. On the other hand, maybe, just maybe (you never know with a PHB). So just in case, here is the script:<\/p>\n

Function NewExcelSheet($sheetName)\n{\n $excel = new-object -comobject excel.application\n $excel.visible = $true\n $workbook = $excel.workbooks.add()\n $workbook.workSheets.item(3).delete()\n $workbook.WorkSheets.item(2).delete()\n $workbook.WorkSheets.item(1).Name = $sheetName\n $sheet = $workbook.WorkSheets.Item($sheetName)\n AddExcelContent\n} #end NewExcelSheet\nFunction AddExcelContent()\n{\n $sheet.cells.item(1,1) = \"A\"\n $sheet.cells.item(1,2) = \"B\"\n $sheet.cells.item(1,3) = \"C\"\n CenterCells\n}\nFunction CenterCells()\n{\n [reflection.assembly]::loadWithPartialname(\"Microsoft.Office.Interop.Excel\") | out-Null\n $xlConstants = \"microsoft.office.interop.excel.Constants\" -as [type]\n $sheet.Cells.item(1,1).HorizontalAlignment = $xlConstants::xlCenter\n}\nNewExcelSheet(\"Test\")\n<\/pre>\n
To begin our script, we create a function called NewExcelSheet<\/b>. The advantage of putting the essential steps for working with Microsoft Excel is that we can easily use this function again in another script. As it is written, very little would need to be changed to enable us to use this function in other scripts. <\/p>\n
The first thing we do when creating a function is use the function<\/b> keyword, followed by the name of the function. We will pass the name of the spreadsheet to the function when we call it, which allows us to change the name of the spreadsheet during the creation. This line of code is seen here:<\/p>\n
Function NewExcelSheet($sheetName)<\/pre>\n
Now we need to create an instance of the Excel.Application<\/b> object. This is the main object we use when working with automating Microsoft Excel. The New-Object<\/b> cmdlet is used to create the object, and we specify the \u2013comobject<\/b> parameter because Excel.Application<\/b> is a com object. We store the resulting application object in the $excel<\/b> variable. After we have created the Excel.Application<\/b>, we make it visible by setting the visible property to true. This is shown here: <\/p>\n
$excel = new-object -comobject excel.application\n $excel.visible = $true\n<\/pre>\n
Next we need to add a workbook to the empty Excel.Application<\/b>. To add a workbook, we use the add<\/b> method from the workbooks<\/b> object. This is illustrated here:<\/p>\n
$workbook = $excel.workbooks.add()<\/pre>\n
We then delete the two extra spreadsheets. This is done by using the worksheets<\/b> property to return a worksheets collection, and using the item<\/b> method to retrieve a specific spreadsheet. We then call the delete<\/b> method from the worksheet<\/b> object, which remotes the extra spreadsheets. This is shown here: <\/p>\n
 $workbook.workSheets.item(3).delete()\n $workbook.WorkSheets.item(2).delete()\n<\/pre>\n
Now we want to rename the first spreadsheet. To do this, we use the item<\/b> method from the worksheets<\/b> object to retrieve the worksheet. We then assign a new value to the name<\/b> property. This is seen here: <\/p>\n
$workbook.WorkSheets.item(1).Name = $sheetName<\/pre>\n
We then store the worksheet<\/b> object in the variable $sheet<\/b>. This time, we use the name of the spreadsheet to retrieve the newly renamed worksheet. After we have stored the worksheet in the $sheet <\/b>variable, we call the AddExcelContent<\/b> function. This can be seen here: <\/p>\n
$sheet = $workbook.WorkSheets.Item($sheetName)\n AddExcelContent\n<\/pre>\n
In the AddExcelContent<\/b> function, we assign three letters to three different cells. The first number represents the row in the spreadsheet, and the second number the column. It is a bit confusing that we use 1,1<\/b> to refer to cell 1a in Excel. But it is not that difficult, so I will not whine about it. To assign a value to the cell, we use the cells<\/b> collection, and the item to grab a specific cell, and then we use the equality<\/b> operator to assign the value to it. We then call the CenterCells<\/b> function. This is seen here, and is not nearly as complicated as it sounds: <\/p>\n
Function AddExcelContent()\n{\n $sheet.cells.item(1,1) = \"A\"\n $sheet.cells.item(1,2) = \"B\"\n $sheet.cells.item(1,3) = \"C\"\n CenterCells\n}\n<\/pre>\n
Now we get to the hard part of the script. The first thing we do in the function is load the Microsoft.Office.Interop.Excel<\/b> assembly. This is because it is not loaded by default. To load the assembly in Windows PowerShell 1.0, we use the static loadWithPartialName<\/b> method from the reflection.assembly<\/b> class. In Windows PowerShell 2.0 (which is in a community technology preview as of this date), you can add this reference by using the add-type<\/b> cmdlet. When the assembly is loaded, it produces feedback on the screen, which is seen here:<\/p>\n
PS C:\\> [reflection.assembly]::loadWithPartialname(\"Microsoft.Office.Interop.Excel\")\nGAC    Version        Location\n---    -------        --------\nTrue   v1.1.4322      C:\\WINDOWS\\assembly\\GAC\\Microsoft.Office.Interop.Excel\\12.0.0.0__71e9bce111e9429c\\Microsoft.Of...\nPS C:\\>\n<\/pre>\n
Because that feedback can be a bit distracting, I send the output to the out-N<\/b>ull<\/b> cmdlet. The out-N<\/b>ull<\/b> cmdlet is a cmdlet with a really bad memory, and therefore it does nothing. You give it something, and it immediately forgets about it (kind of like Dory from Finding Nemo<\/i>). This section of the code can be perused at your leisure: <\/p>\n
Function CenterCells()\n{\n [reflection.assembly]::loadWithPartialname(\"Microsoft.Office.Interop.Excel\") |\n out-Null\n<\/pre>\n
We now create the constant<\/b> type. The VBScript version of this script<\/a> uses what I call a “magic number,” and magic numbers are, in my mind, a big no-no! They are hard to read and extremely error prone. For instance, if I were troubleshooting the script and I come across this line of code, what would I need to do?:<\/p>\n
objWorksheet.Cells(1, 2).HorizontalAlignment = -4108<\/pre>\n
There is absolutely no way I can verify if this is the correct value. Also, what are the other options available to me? It is simply a number, and that is about it. In our current script, we use the actual constant values. <\/p>\n
We need to understand two lines of code. The first we already discussed; we loaded the interop assembly. Now we create the constants and store them in the $xlConstants<\/b> variable. This is actually pretty cool because this pulls the mask off of all those annoying magic numbers you have been seeing around here for the last eight years. If you are like me, you want to know where in the heck those values are documented. They are in fact on MSDN. You can also use Windows PowerShell to find this information on your own, as seen here:<\/p>\n
[reflection.assembly]::loadWithPartialname(\"Microsoft.Office.Interop.Excel\") | out-Null\n[enum]::getNames($xlconstants) |\nForEach-Object { \"$xlconstants:$_\" +  \" \" + $xlConstants::$_.value__ }\n<\/pre>\n
To simplify the troubleshooting of the script, we create the constants as shown here. <\/p>\n
$xlConstants = \"microsoft.office.interop.excel.Constants\" -as [type]<\/pre>\n
We then use this constant to specify the alignment of the cells. We are interested in the horizontal alignment, and we use the HorizontalAlignment<\/b> property and specify the xlcenter<\/b> constant to center the data in the cell. There are other constants we could use for this as well. These are seen here:<\/p>\n\n\n\n\n\n\n\n\n\n
<\/td>\n<\/tr>\n<\/thead>\n
\n
xlCenter<\/p>\n<\/td>\n<\/tr>\n
\n
xlDistributed<\/p>\n<\/td>\n<\/tr>\n
\n
xlJustify<\/p>\n<\/td>\n<\/tr>\n
\n
xlLeft<\/p>\n<\/td>\n<\/tr>\n
\n
xlRight<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
We use the cells<\/b> property from the worksheet<\/b> object to return a cells<\/b> collection. The item<\/b> method returns a specific cell, and then we supply a new value for the HorizontalAlignment<\/b>: <\/p>\n
$sheet.Cells.item(1,1).HorizontalAlignment = $xlConstants::xlCenter<\/pre>\n
We begin the script by calling the NewExcelSheet<\/b> function and passing the name of our spreadsheet. This is the entry point into the script. It is seen here. <\/p>\n
NewExcelSheet(\"Test\")<\/pre>\n
The completed Office Excel workbook is seen here:<\/p>\n
\"Image<\/p>\n
 <\/p>\n
Well, that is about all there is to it. The key was finding out the constant values that allow us to specify where the alignment will be performed. See you tomorrow.<\/p>\n
Ed Wilson and Craig Liebendorfer, Scripting Guys<\/b><\/b><\/span><\/font><\/p>\n
<\/b><\/font><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! When I add data to an Office Excel spreadsheet, it always seems to put stuff on the right side of the column. I would like to know how to center the contents of a column. I do not think it should be that hard, but I just cannot figure it out. – […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,48,49,3,45],"class_list":["post-54613","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! When I add data to an Office Excel spreadsheet, it always seems to put stuff on the right side of the column. I would like to know how to center the contents of a column. I do not think it should be that hard, but I just cannot figure it out. – […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54613","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=54613"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/54613\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=54613"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=54613"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=54613"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}