0

I have an Excel 2016 Add-in and I would like to set the format on a couple of columns to Date (mm-dd-yyyy format) and center a couple of columns. How can I do this in Excel 2016 JavaScript model?

Also, I understand that the office model for 2016 does not allow you to change column width. Does anybody have a work around for this?

Thanks, George

gihuey
  • 1
  • 3
  • `Columns(1).ColumnWidth=30` seems to work OK using VBA. – Ron Rosenfeld Mar 20 '16 at 18:00
  • True, but as far as I can see / understand, is that the Excel 2016 object model does not support ColumnWidth. My code looks like this: var column = recordSheet.getCell(1, 2).getEntireColumn(); column.ColumnWidth = 80; // does nothing but ColumnWidth is undefined. For that matter, I would think that I should be able to do something like this: var column = recordSheet.getEntireColumn(2); but getEntireColumn does not exist for worksheets. I also understand that Excel 2016 only supports javascript not VBA. Thanks, George – gihuey Mar 22 '16 at 01:17
  • Excel 2016 does support VBA. The code I posted is VBA. But I cannot help you with JavaScript. – Ron Rosenfeld Mar 22 '16 at 01:44
  • I don't see columnwidth in the JS / Excel documentation. But does this not work? [How to control Excel column size in Javascript](http://stackoverflow.com/questions/23975873/how-to-control-excel-column-size-in-javascript) – Ron Rosenfeld Mar 22 '16 at 10:36
  • Hi Ron, I was so hopeful with that last link, but it does not work. The syntax seems to be totally different (for example, instead of Cells you have to do getRange. But once I get the cell range, where is no method ColumnWidth. Thanks, George – gihuey Mar 22 '16 at 13:35
  • You need someone versed in JS to help. I am surprised you cannot get it working in JS since it clearly works in VBA with Excel 2016 – Ron Rosenfeld Mar 22 '16 at 19:45

1 Answers1

0

A function that sets a column width, date format and centers the values in Typescript:

columnFormatExampleFunction(ctx: Excel.RequestContext):void {
    var sheet: Excel.Worksheet = ctx.workbook.worksheets.getActiveWorksheet();
    var range: Excel.Range = sheet.getRange("B:B");
    range.numberFormat = [["mm-dd-yyyy"]];
    range.format.columnWidth = 150;
    range.format.horizontalAlignment = "Center";
}
Danny122
  • 101
  • 3