Click to watch a video tutorial on how to write to an Excel document.

This tutorial video will show you how to make a robot that automatically creates an Excel document based on data extracted from a website. Please feel free to pause the video along the way so you can follow the steps on your own computer.

With Design Studio open, we go into the Examples folder in the default project. Under Robots, we find and open the example robot called TableExtract.robot. This is a robot which extracts person data from a table on the website News Magazine. We can see the table by clicking on the loop step.

Before moving on, make sure you familiarize yourself with this robot. We will modify the robot to write the extracted data into Excel.

We first need to clean up the robot a bit. We start by deleting the two first steps, which are only there to display help about the original robot.

Then, we add a new type to the robot by clicking Add (+) in the Variables view and choosing PersonListExcel in the list of variables. This type contains an Excel attribute, which the robot will write to. Let's call the variable personList and mark it as Global. Having a global variable ensures that we can add content to the spread sheet over multiple iterations of a loop. We click OK.

To modify our Excel variable, we must open it in the page view. We right click personList and choose Insert Step > Open Variable > personList.list. This inserts and executes an Open Variable step in the Robot View and, although the spreadsheet is empty, we can now see the content of our personList variable.

Next, we add a header for each column in the table we are extracting from. To insert content into Excel, we right click a cell and choose Modify > Set > Text. This opens a dialog where we can specify the value to insert. We write "id" and click OK.

Then, we repeat this process for the next three cells which we call "name", "age" and "isMale". It is now time to make the robot insert the extracted data into this spread sheet. We click on the end step of the robot. An error might occur, stating that the Test Tag step stopped execution. This is an intended error. To get around it, we use the lower alternative from the Try Step.

We switch to the Excel window by right clicking it and choosing Set as Current Window. This inserts and executes a Set Current Window step in the Robot View.

Before we can insert new content into the Excel variable, we must extend the sheet with a new row. We select the entire sheet by clicking the upper left corner of the Excel View. Then, we can right click on the sheet and choose Modify > Insert > Rows > Last. A dialog opens, asking how many rows to insert. We only need to insert one row for each iteration, so we press OK. This will insert a new row as the last row in our sheet and mark it as a named range.

We can now select the named range by clicking the second row on the very left side of the Excel View. We then right click the selected row and choose Modify > Set > Content of Row > person. This inserts the data extracted from News Magazine.

Stepping thought the iterations of the loop, we can see how multiple rows of content are added to our Excel variable.

We now have a robot which creates and writes to an Excel variable. To get full usage of our robot, we need to make it save the Excel variable as a document on the hard drive. To this extent, we insert a branch just before our loop step. The lower branch will execute after the loop has finished all iterations.

Stepping into the lower branch, we add a new action step and select for it the Write File action, which can be found under the File System category.

To configure the Write File step, we need to give a file path. I will simply place the file at the root of my C:\ drive and call it simpleExcel.xlsx. You may choose to place it wherever you want. The file content should be our personList variable.

Running the robot in Debug Mode will now write the Excel variable as a document at the specified location. If we open the file, we see that our spread sheet looks as expected.

This was a very simple example of how to write to Excel in a robot. For a more advanced example, take a look at ExcelAdvanced.robot in the examples folder or read about the capabilities of writing to Excel in the documentation in Kapow help.