To automate Excel sheet processing, Xill has all basic functionality built in. You can program Xill robots to read cells out of sheets, edit them or build new Excel files.
Available functions
Omitting some details about the possible parameters, the following is what you can do with the Excel package.
- createWorkbook(filepath)
- loadWorkbook(filepath)
These first two return a workbook variable.
- createSheet(workbook,sheetname)
- loadSheet(workbook,sheetname)
These return a sheet variable. createSheet(..) adds a sheet to the workbook in internal memory.
- setCell(sheet,column,row,value)
This edits the specified cell in the sheet in internal memory.
- getCell(sheet,column,row)
This returns the value from the specified cell.
- save(workbook,path)
This saves the Excel file at the specified path.
You can find extra details about the parameters in the built-in help section of Xill IDE.
Create your first Excel robot
Let's take a "Hello World" approach for this example and let a Xill robot create a very simple Excel file and read it back from disk. This demonstrates almost all available Excel functions in the package.
Copy the code below to a new robot:
use System; use Excel; //set this path to your choice var filePath = "D:\\temp\\test.xlsx"; //create a workbook with a sheet, and then fill a cell var myFirstWorkbook = Excel.createWorkbook(filePath); var myFirstSheet = Excel.createSheet(myFirstWorkbook,"Hi"); Excel.setCell(myFirstSheet,"A",1,"Hello World!"); Excel.setCell(myFirstSheet,"A",2,"Hi Earth!"); //now save the sheet System.print(Excel.save(myFirstWorkbook)); //to demonstrate the reading functions, let's read the written cell back from disk and log the contents var workBookFromDisk = Excel.loadWorkbook(filePath); var sheetFromDisk = Excel.loadSheet(workBookFromDisk,0); var upperLeftCell = Excel.getCell(sheetFromDisk,"A",1); System.print(upperLeftCell);
When you've created the test.xlsx file and run this code you will see the following messages being logged in the console:
Using the debug panel
If you haven't done this yet, try out the debugger. It's very simple, but extremely helpful. Just put a breakpoint on the last code line and press play. Now you can see the Excel-related variables you're working with. Note that the sheets are displayed as an Object type. The values in that list are merely metadata about the Excel variables. The sheet itself is only accessible through the Excel package, until you save it as a file.
Usage of Excel functionality in the real world
Editing or reading some cells individually like in the example should often be done manually, because writing a robot for it will probably take more time. Real-world automation will usually involve loops (foreach or while) to 'walk through' the rows and maybe the columns, sheets or even different Excel files too. It can also be a good idea to extend the built-in Excel functionality a bit, by writing routines like getColumnNumber(columnName) and/or insertRecord(recordObject).
Whether those are the functions you need, depends on your coding style. Often, you'll probably use a database for internal content processing. For educational purposes, we'll keep databases and every non-excel function except basic ones out of this tutorial. That doesn't mean that the assignment is going to be child's play though. At least you should be able to work with collections first.
Assignment 1
Imagine a fictional business. They have employees with different interesting jobs, working at two different locations. There's a company-wide, simple employee administration, made in Excel. Download the file here. (Or see the attached item below).
As the company's growing bigger, the list is getting longer and more complex. Supervisors are losing overview, while only a select group of employees is relevant to them. They would like to have simpler sheets with only the names and phone numbers of employees with a specific job title at a specific location.
The assignment is to make those sheets, as new Excel workbooks, for every job title/location combination.
You should not assume that you know up-front which locations and job titles there are, as this might change in the future. This probably sounds a bit easier than it is: Just take all rows from the original sheet and distribute them over new sheets by job title/location combination, right? Not really, because you don't know how many Excel files you need until you've read the complete sheet and even then, you can't build these new files at the same time, because you can't assign variables with a dynamic name.
So you should pick a job title/location combination somehow, build and save the sheet and then proceed to the next combination (reusing variable names in a loop). If you think you might be able to do this without any more help, please do so and share your solution! Otherwise, read on and fill in our robot skeleton.
Robot template
Copy and paste this code to a new robot; you can double click the code to select all and do Ctrl-c and Ctrl-v to the new file.
//This example robot reads the accompanying excel file with basic employee info,
//and splits it to new Excel files with one job title/location per file.
//
// Marijn van der Zaag, 2015
use System;
use Excel;
use Collection;
// === SETUP === //
var originalSheetPath = "D:\\Projects\\Xill developers platform\\tutorial Excel\\";
var excelFileName = "employees info.xlsx";
var firstRow = 2;
// === MAIN === //
var originalExcelFile = Excel.loadWorkbook(originalSheetPath :: excelFileName);
var originalSheet = Excel.loadSheet(originalExcelFile,0);
var columnNamePositionList = buildColumnNamePositionList(originalSheet);
splitSheet(originalSheet,columnNamePositionList,originalSheetPath);
System.print("All done! Check the new Excel files at " :: originalSheetPath);
// === ROUTINES === //
//This is the root routine, where the magic starts.
//The sheet will be split into several excel files.
//Which row is put into which new excel file, depends on job title/location.
//The amount of resulting Excel files is not fixed, but depends on the available combinations
function splitSheet(originalSheet,columnNamePositionList,originalSheetPath) {
var copiedCombinations = {}; //this is to keep track of what type of lines is already done
var nextCombination = findNextCombination(originalSheet,columnNamePositionList,copiedCombinations);
while(nextCombination!=null) {
//this loop will continue until no new combination of job title+location can be found anymore
var newExcelFile = makeNewExcelFile(originalSheet,nextCombination,columnNamePositionList);
Excel.save(newExcelFile,originalSheetPath :: "employees info - " :: nextCombination.location :: " - " :: nextCombination.jobTitle :: ".xlsx");
copiedCombinations[] = nextCombination.jobTitle :: nextCombination.location;
nextCombination = findNextCombination(originalSheet,columnNamePositionList,copiedCombinations);
}
}
//walk through original sheet, pick out all rows that have the current combination of job title and location,
//and put them in a new workbook/sheet. Returns the workbook
function makeNewExcelFile(originalSheet,combination,columnNamePositionList) {
var newWorkbook = Excel.createWorkbook("temp");
var newSheet = Excel.createSheet(newWorkbook,"Employees");
//set the headers of the new sheet columns to "Name" and "Number"
/*TO DO*/
//insert all relevant rows
var originalRow = firstRow;
var newRow = firstRow;
while(originalRow <= originalSheet.rows) {
/*TO DO*/
originalRow += 1;
}
return newWorkbook;
}
//returns next combination of jobTitle and location (in an object!) that wasn't processed before.
//returns null if there's no unprocessed combination
function findNextCombination(sheet,columnNamePositionList,copiedCombinations) {
/*TO DO*/
//Hint: if(!Collection.contains(copiedCombinations,jobTitle :: location)) {
}
//Build a list with column names and the position in the sheet
//so When columnnames are mixed up, the robot will still work
function buildColumnNamePositionList(sheet) {
var columnNamePositionList = {};
var i = 1;
while (i <= sheet.columns) {
var header = Excel.getCell(sheet,i,1);
columnNamePositionList[header] = i;
i = i + 1;
}
return(columnNamePositionList);
}At the bottom of this article you will find the file Excel splitting assignment - full solution.sbot Like the name says, you should only use this in case you get stuck and want to see the solution. If you have some programming experience and have read the Collection tutorial, you should be able to complete the assignment though. If not, we'd really like to know, so please tell us where you got stuck.
Step by step
First, the used packages are defined.
The 'setup' section
The parameters that might need to be changed for another run ('setttings') are defined at the start of the robot.
The 'main' section
- line 16/17: should be trivial. If not, restart reading of this article.
- line 18: This is one of the ways to extend the basic Excel functionality. It is not mandatory, but enables you to use getcell() and setcell() with a column name instead of a hardcoded column number/letter. The routine will be explained amongst the other routines this example solution uses.
- line 19: Everything from reading the original sheet until saving the new Excel files happens in the routine called here.
- line 20: It's always good to have a little proof that the robot ran till the end.
Routine: buildColumnNamePositionList(sheet)
You'll get this one for free for a kickstart. It's all in the given skeleton. In the original sheet, the 'phone' column is column "E". This means that after this routine has been called, you can use columnNamePositionList["phone"] instead of hardcoding "E" in functions setcell() and getcell(). To see what exactly the routine returns, put a breakpoint on line 19 and press play.
Routine: splitSheet(originalSheet,columnNamePositionList,originalSheetPath)
This could be called the 'root' routine. We put this completely in the template robot as well, because as a Xill Excel package novice you should practice the details first.
Routine: findNextCombination(originalSheet,columnNamePositionList,copiedCombinations)
This should return an object in the form of:
{"jobTitle" : "nail clipper","location" : "Fantasia"}.
Walk through all rows until you find a combination that has not been processed yet. If there is none, return null (or don't return anything, which has the same effect).
It is assumed that you use copiedCombinations as a flat list of strings of the form [job title][location], i.e. 'nail clipperFantasia'.
Routine: makeNewExcelFile(originalSheet,nextCombination,columnNamePositionList)
Here's a rough setup of the routine already. After the creation of the workbook and sheet, you should:
- fill the header cells of the new sheet with the colomn names "Name" and "Number"
- code the already prepared while loop. It has a counter for the old sheet and one for the new sheet.
- for every row that has the right job title ánd location: get the full name and phone number from the old sheet and insert them in the new sheet
