13.5.1 writing data to Excel

13.5.1 writing data to Excel

The interop assemblies for Excel are standard. NET assemblies that can be referenced using #r directives in F # Interactive. After referencing an assembly, you can use the class to either run Excel as a standalone (visible or invisible) application or to write scripts. Listing 13.18 shows how to start Excel, create a new workbook with a worksheet, and write data to a worksheet.

Listing 13.18 starting Excel creation worksheet (F #)

#r "Office.dll"#r "Microsoft.Office.Interop.Excel.dll"Open System Open Microsoft. Office. Interop. ExcelLet app = new ApplicationClass (Visible = True) [1]let Workbook = App. Workbooks. ADD(xlwbatemplate. Xlwbatworksheet)     [2]let worksheet = (workbook. Worksheets. [1]:?> _worksheet) [3]worksheet. Range("C2"). Value2 <-"1990"| [4]worksheet. Range("C2","E2"). Value2 <-[|"1990"; "2000";  "2005" |] |

In Listing 13.18, we create a new instance of the ApplicationClass class [1], which comes from the Excel namespace, which represents the Excel application. After you run this line of code, a new Excel window should appear. The next line [2] creates the workbook, so after you run it, you should see the usual Excel table. On the next line, we get the object that represents the first worksheet in the workbook (the worksheet appears in the lower-left corner of the application). As you can see, we need to cast this object to the Worksheet class [3], because the Excel programming interface (API) is weakly typed in many places. Once we have this worksheet, we can start writing data to the grid, which can be implemented using the Range indexer and the Value2 property [4]. The type of this property is object, so it can be used in a variety of ways. The first example writes a string value to a column, and the second example populates the range with a. NET array value (three rows in a row). In Figure 13.1, you can see the EXCEL worksheet that you created after you ran your code.

Figure 13.1 Starting an Excel application from F # Interactive and entering data programmatically.

So far, we have created the headers that we want to display, the next step is to populate all the remaining information, and most importantly, the table contains the forest area for different years. Listing 13.19 transforms the data into a two-dimensional array, which is also a valid data source for the Value2 property.

Listing 13.19 exporting data to an EXCEL worksheet (F #)

 LetStatsArray= Stats |>Array. ofSeq  LetNames =Array2D. init statsArray.Length 1( FunIndex_–> [1] LetName_= StatsArray. [Index] | Get the region name in the two-dimensional array name) | LetDataArray=Array2D. init statsArray.Length 3( FunIndex year–> Let _, values = StatsArray. [Index] LetYearValue= values. Year (year)Value/1000000.0) <--display for millions of square kilometres Let EndColumn= String (statsArray.Length+2) worksheet.Range("B3","B"+EndColumn).Value2<-namesVert| Write the data to the worksheet worksheet.Range("C3","E"+EndColumn).Value2<-tableARR|

To write data to an EXCEL worksheet, we can use basic values such as arrays or two-dimensional arrays. One-dimensional arrays can be used to write data rows, as seen in the first example, but if we want to populate matrices or columns with data, we must use a two-dimensional array. In Listing 13.19, we first create a two-dimensional array that holds the region name vertically. To do this, create a simple array that contains the name, and then, using the Array2D.init function, convert it to a two-dimensional array [1]. The first two parameters of the Init function are the width and height of the array, plus a function to generate values for each coordinate. The resulting array contains only one column, so the second coordinate can be omitted during initialization.
The next step is to generate a two-dimensional array with region data. We convert the input sequence to an array, and we use the Array2D.init function to generate a two-dimensional set so that the index can be used. The lambda function, each array cell executes, in the function, first obtains the region information, then finds the value of the specified year, divided by 1 million, making the output easier to read. Finally, we calculate the right range in the EXCEL worksheet (depending on the number of regions) and set the data to the previous example.
Run the code and the data should appear in Excel. Because we're running an F # script, we can work on the worksheet at the same time, adjust the table design you just generated, and the results look similar to Figure 13.2.

Figure 13.2 An Excel table generated by F # script that shows changes in forest coverage over the last 20 years in the world.

Now, with Excel, it's easier to understand the data, and you can go further and create a chart with the data. This can be done manually, but it is easier to generate complete Excel files, including charts, in F #.

13.5.1 writing data to Excel