Script Scraper

Pull Data from the Internet into Excel Spreadsheets.

Population Example

This example illustrates how simple it can be to create an Excel spreadsheet using JavaScript.


We will hard code the data directly into the JavaScript, the next example baseball will show how to download data from the Internet.
The Excel sheet that results from this example is shown below.

Two arrays, usa and europe, containing hashtables are used to store the population sizes of a few major US and Eurpoean cities.

var usa = [{City: "New York",     State: "New York",      Population: 8175133},
           {City: "Los Angeles",  State: "California",    Population: 3792621},
           {City: "Chicago",      State: "Illinois",      Population: 2695598},
           {City: "Houston",      State: "Texas",         Population: 2099451},
           {City: "Philadelphia", State: "Pennsylvania",  Population: 1526006},
           {City: "Phoenix",      State: "Arizona",       Population: 1445632},
           {City: "San Antonio",  State: "Texas",         Population: 1327407}]

europe =  [{City: "London",     Country: "United Kingdom",Population: 7429200},
           {City: "Berlin",     Country: "Germany",       Population: 3387828},
           {City: "Madrid",     Country: "Spain",         Population: 3228319},
           {City: "Rome",       Country: "Italy",         Population: 2553873},
           {City: "Paris",      Country: "France",        Population: 2181374},
           {City: "Hamburg",    Country: "Germany",       Population: 1734830},
           {City: "Budapest",   Country: "Hungary",       Population: 1695814}]
The data in the two hashtables is loeded into Excel as two separate worksheets.

Two ExcelWorkSheet objects are needed, the first argument to the ExcelWorkSheet constructor will be the name of the worksheet which can be seen at the bottom in Microsoft Excel. The second is a list of labels that appear in the sheet. In this case just one label is used for each sheet to describe the information.

var usa_sheet  = new ExcelWorkSheet("US Cities", ["Population of Major US Cities."]);
var euro_sheet = new ExcelWorkSheet("European Cities", ["Population of Major European Cities."]);
The blank sheets are then populated by using the addRows method.
usa_sheet.addRows(usa,     ["City", "State",   "Population"]);
euro_sheet.addRows(europe, ["City", "Country", "Population"]);
A ExcelSpreadSheet object will contain the ExcelWorkSheets. A spreadsheets always have at least one worksheet and therefore the constructor for ExcelSpreadSheet takes the first argument of a ExcelWorkSheet. The second argument the name of a template, the template determines the format of the ouput.
var excel = new ExcelSpreadSheet(usa_sheet, "cities_template.xls");
Finally the second worksheet, the european one, is added to the spreadsheet. The spreadsheet is saved and a message is logged to the console.
excel.addWorkSheet(euro_sheet);
excel.saveAs("cities.xls");
out.println("Created cities.xls");