CODEWITHSUNDEEP
excelf#excel-interop
Soldalma
Soldalma

Reputation: 4758

How does one set column width with Excel Interop?

I succeeded in writing to an Excel range, setting font color, etc. using the Microsoft.Office.Interop.Excel library. One thing I was not able to do: setting column width. Could not find F#-specific documentation and adapting what I found for C# does not seem to work. For example, this code has no effect:

worksheet.Columns.AutoFit()

and this line of code does not even compile:

worksheet.Columns.[1].Columnwidth <- 15.0

Any suggestions?

Upvotes: 1

Views: 910

Answers (1)

Gene Belitski
Gene Belitski

Reputation: 10350

To begin with, working with Excel from F# is much easier with the help of ExcelProvider.

However, given you realize the intricacies of dealing with COM from F#, operating with bare Excel is not something too complicated.

As you did not provide enough details for pointing out what exactly is wrong with your own attempts, here is a self-contained snippet demonstrating the manipulation with a worksheet column width, including visual demo:

#r "Microsoft.Office.Interop.Excel"
open Microsoft.Office.Interop.Excel
open System
let ex = new ApplicationClass(Visible=true)
let exO = ex.Workbooks.Add()
let exOWs = exO.Worksheets.[1] :?> Worksheet
exOWs.Name <- "TestSheet"
let col1 = exOWs.Columns.[1] :?> Range
let oldW = col1.ColumnWidth |> unbox
printfn "Current width of column 1 is %f" oldW
col1.ColumnWidth <- 15.0
let newW = col1.ColumnWidth |> unbox
printfn "New width of column 1 is %f" newW
printfn "Press any key to exit"
Console.ReadLine() |> ignore
exO.Close(false, false, Type.Missing)
ex.Quit()

Running the above fsx script with FSI on a box having Excel installed should produce the output similar to one below:

--> Referenced 'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Visual Studio Tools for Office\PIA\Office15\Microsoft.Office.Interop.Excel.dll'

Current width of column 1 is 8.430000
New width of column 1 is 15.000000
Press any key to exit

Before stopping the script you may want to locate the opened Excel application window and observe that the first column width has really changed.

UPDATE: Covering Autofit part of the original question: the important tidbit to recognize is that fitting of range's cell widths and heights may be done only after the range is filled. Applying Autofit() to an empty range is of no use, while for an already filled range it works as expected.

Let's fill the first 4 columns of our sample worksheet's row 1 with strings of different length and then apply Autofit by placing the following after the line showing newW value:

let rowContents = "Our widths are flexible".Split(' ') in
    (exOWs.Rows.[1] :?> Range).Resize(ColumnSize=rowContents.Length).Value2 <- rowContents
exOWs.Columns.AutoFit() |> ignore

and observe the visual effect: enter image description here

Upvotes: 2

Related Questions