Excel

Category:
Office Interop
Description: Reading and writing from and to Excel. Please make sure you have Excel 2010 installed on your computer.
Code:
let openExcel() = 

try
// Start Excel, Open a exiting file for input and create a new file output.xlsx
let xlApp = new Excel.ApplicationClass()
let currentPath = System.IO.Path.GetFullPath(@".\Support.ExcelFile.xlsx");
let xlWorkBookInput = xlApp.Workbooks.Open(currentPath)
let xlWorkBookOutput = xlApp.Workbooks.Add()
xlApp.Visible <- true
// Open input's 'Sheet1' and create a new worksheet in output.xlsx
let xlWorkSheetInput = xlWorkBookInput.Worksheets.["Sheet1"] :?> Excel.Worksheet
let xlWorkSheetOutput = xlWorkBookOutput.Worksheets.[1] :?> Excel.Worksheet
xlWorkSheetOutput.Name <- "OutputSheet1"
// Reading\Writing a cell value using cell index
let value1 = xlWorkSheetInput.Cells.[10,5]
xlWorkSheetOutput.Cells.[10,5] <- value1
// Reading\Writing a cell value using range
let value2 = xlWorkSheetInput.Cells.Range("E10","E10").Value2
xlWorkSheetOutput.Cells.Range("E10","E10").Value2 <- value2
// Reading\Writing a row
let row = xlWorkSheetInput.Cells.Rows.[1] :?> Excel.Range
(xlWorkSheetOutput.Cells.Rows.[1] :?> Excel.Range).Value2 <- row.Value2
// Reading\Writing a column
let column1 = xlWorkSheetInput.Cells.Range("A:A")
xlWorkSheetOutput.Cells.Range("A:A").Value2 <- column1.Value2
// Reading\Writing a Range
let inputRange = xlWorkSheetInput.Cells.Range("A1","E10")
for i in 1 .. inputRange.Cells.Rows.Count do
for j in 1 .. inputRange.Cells.Columns.Count do
xlWorkSheetOutput.Cells.[i,j] <- inputRange.[i,j]
//write jagged array
let data = [| [|0 .. 1 .. 2|];
[|0 .. 1 .. 4|];
[|0 .. 1 .. 6|] |]
for i in 1 .. data.Length do
for j in 1 .. data.[i-1].Length do
xlWorkSheetOutput.Cells.[j, i] <- data.[i-1].[j-1]
with _ -> printfn "please check Excel installation"

Execution Result:
 

Last edited Sep 14, 2011 at 3:04 AM by ttliu2000, version 1

Comments

No comments yet.