Posted: February 2nd, 2012 | Author: Christopher Vigliotti | Filed under: Java, O-O | 2 Comments »
If you are new to the strange and wonderful world of Java development, Head First Java is the essential primer.
Do you have a favorite “Intro to Java” or “Intro to Object Oriented Development” resource? Please share in the comments
Posted: January 18th, 2012 | Author: Christopher Vigliotti | Filed under: ColdFusion | 2 Comments »
I’ll be participating on a preview of ColdFusion Zeus tomorrow at noon. See you there.
Posted: January 18th, 2012 | Author: Christopher Vigliotti | Filed under: Selenium | No Comments »
This post is a part of my Learn Selenium series.
Here’s a test case that demonstrates the use of conditional statements and assertions in Selenium RC. This code uses the parent class BigDaddyKane, which is explored in detail in a previous post.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
| // package declaration, imports, etc.
package awesomeTests;
import java.security.SecureRandom;
import java.math.BigInteger;
@SuppressWarnings("deprecation")
// class decaration
public class FancyTests extends BigDaddyKane {
// This is a method for generating a random string.
// You can disregard it for now, just note that we'll be using this later.
public String getRandomString(){
SecureRandom random = new SecureRandom();
return new BigInteger(130, random).toString(32);
}
// Here is our first test case. It verifies that the text
// "Repeated Overflows" appears in the h1 of the main page.
public void testIsBlogTitleCorrect() throws Exception {
// open the root page of the site
selenium.open("/");
// Lastly we use assertEquals(). This test will fail if the text
// "Repeated Overflows" is not present in an h1 element
assertEquals("Repeated Overflows", selenium.getText("css=h1"));
}
/* Here is our second test case. This example demonstrates the use of conditional logic in conjunction with
* the Selenium function isElementPresent().
*/
public void testAddCommentToBlog() throws Exception {
// open the root page of the site
selenium.open("/");
// click on the top-most post and wait for the page to load
selenium.click("xpath=//div[2]/div/h1/a");
selenium.waitForPageToLoad(pageReloadTime);
// using this variable to determine the user's logged in state
boolean userIsLoggedIn = false;
/* since Wordpress will now allow duplicate comments we need to make the comment text unique each time this test is run.
* Note the use of the + symbol when declaring the string. This means that the text to the left of the plus in quotes will be
* appended by the results of the getRandomString() function call.
*/
String comment = "the chicken says: " + getRandomString();
System.out.println(comment);
// are these elements present?
boolean nameFieldExists = selenium.isElementPresent("id=author");
boolean mailFieldExists = selenium.isElementPresent("id=email");
boolean urlFieldExists = selenium.isElementPresent("id=url");
// displays the above boolean values in the console screen (so you can see what's going on)
/*
System.out.println(nameFieldExists);
System.out.println(mailFieldExists);
System.out.println(urlFieldExists);
*/
// note: in cases where the author, email and url fields are not present the user is already logged in
// if the name, email and url fields do not exist, then the user is logged in
if(nameFieldExists == false && mailFieldExists == false && urlFieldExists == false){
userIsLoggedIn = true;
}
// if the user is not logged in, fill in the name, mail and url fields
if(userIsLoggedIn == false){
System.out.println("user is logged in");
selenium.type("id=author", "Paul McCartney");
selenium.type("id=email", "billgates@microsoft.com");
selenium.type("id=url", "http://wikipedia.org");
}else{
System.out.println("user is not logged in");
}
// fill in the comment field and click the submit button
selenium.type("id=comment", comment);
selenium.click("id=submit");
selenium.waitForPageToLoad(pageReloadTime);
// if the comment was added either the text "Your comment is awaiting moderation." or the value of the String 'comment' should be present
boolean expectedResult = false;
if(selenium.isTextPresent("Your comment is awaiting moderation") || selenium.isTextPresent(comment)){
expectedResult = true;
}
// lets display the expected result
System.out.println(expectedResult);
// use assertEquals to conclude this test case. note that we could also use assertTrue(expectedResult) here...
assertEquals(expectedResult, true);
}
} |
Posted: January 12th, 2012 | Author: Christopher Vigliotti | Filed under: Selenium | No Comments »
This post is a part of my Learn Selenium series.

mmm…ice cream
What Type Of Ice Cream Is This??!!
The Wikipedia entry for Selenium defines it as “a portable software testing framework for web applications. Selenium provides a record/playback tool for authoring tests without learning a test scripting language (Selenium IDE). It also provides a test domain-specific language (Selenese) to write tests in a number of popular programming languages, including C#, Java, Groovy, Perl, PHP, Python and Ruby. The tests can then be run against most modern web browsers. Selenium deploys on Windows, Linux, and Macintosh platforms.” So basically, Selenium is a way to write code that performs browser interactions.
Three Delicious Flavors Of Selenium
Here is some (more) handy information that I cut and pasted from Wikipedia…
| Flavor |
Description |
Benefits |
| Selenium IDE
|
Runs as a Firefox extension, can record, edit and debug tests. |
easy to learn
easy to author tests
can export to RC or WebDriver
|
| Selenium RC |
Runs via it’s own server. Tests written in one of many diff. languages. |
works with many languages and browsers
mature and complete API
better JavaScript support
|
| Selenium WebDriver |
Runs via it’s own server. Tests written in one of many diff. languages. |
Native automation faster, less prone to error
headlessHTMLUnit for faster tests
|
Which Should I Choose?
I chose to develop simple test cases in Selenium IDE then export them to Selenium RC for further modification. I chose Selenium RC over Selenium WebDriver because at this point RC offers more features and supports more browsers than WebDriver. It’s important to note that Selenium RC is no longer being developed, and that Selenium WebDriver is the future of the product. Until then I’d stick with Selenium RC.
Ready for more? Check out my post on Selenium Selectors
Posted: January 10th, 2012 | Author: Christopher Vigliotti | Filed under: O-O, Selenium | No Comments »
This post is a part of my Learn Selenium series.
Inheritance
In object-oriented programming, a class can inherit the methods and properties from a parent class. This means that we can add code to a parent class that can be used in one or more child objects. The advantage is that one would only have to write code one time for it to be available in many places.
Lets take a look at the example in Figure 1a:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| package awesomeTests;
import com.thoughtworks.selenium.*;
import java.util.regex.Pattern;
@SuppressWarnings("deprecation")
public class AnAwesomeTest extends SeleneseTestCase {
public void setUp() throws Exception {
setUp("http://code.christophervigliotti.com/", "*chrome");
}
public void testAwesome() throws Exception {
selenium.open("/");
selenium.click("link=Excel Entity Service");
selenium.waitForPageToLoad("30000");
selenium.type("id=comment", "Test comment.");
selenium.click("id=submit");
selenium.waitForPageToLoad("30000");
}
} |
Figure 1a. AnAwesomeTest.java
This is a basic Selenium RC test case. Lets take a closer look at line 8.
8
| public class AnAwesomeTest extends SeleneseTestCase { |
Figure 1b. Line 8 of AnAwesomeTest.java
Translated from Java-speak into English this line reads as follows. “This public class is named ‘AnAwesomeTest’ and is a child of ‘SelesenseTestCase’. AnAwesomeTest.java is a child of parent class SelesenseTestCase.java. This means that the logic found SelesenseTestCase.java and it’s ancestors is available for us to call upon. If you look at the setUp() and testAwesome() methods we are calling methods from the parent class at line 10, as well as on lines 13-18.
Lets take a look at a second Selenium test class…
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| package awesomeTests;
import com.thoughtworks.selenium.*;
import java.util.regex.Pattern;
@SuppressWarnings("deprecation")
public class AnotherTest extends SeleneseTestCase {
public void setUp() throws Exception {
setUp("http://code.christophervigliotti.com/", "*chrome");
}
public void testSomething() throws Exception {
// your test code goes here
}
} |
Figure 2. AnotherTestCase.java
Like AwesomeTestCase, AnotherTestCase is a parent of SeleneseTestCase.
Leveraging A New Parent
Now if we were to create a new parent class for these two classes we would be able to combine some of the redundant logic into one place.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| package awesomeTests;
import com.thoughtworks.selenium.*;
// not being used! import java.util.regex.Pattern;
@SuppressWarnings("deprecation")
public class BigDaddyKane extends SeleneseTestCase {
// properties
public int pageReloadTime = 3000;
public String url = "http://code.christophervigliotti.com/";
public String browser = "*chrome";
// methods
public void setUp() throws Exception {
// was setUp("http://code.christophervigliotti.com/", "*chrome");
setUp(url, browser);
}
} |
Figure 3a. The awesomely named BigDaddyKane.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| package awesomeTests;
@SuppressWarnings("deprecation")
public class AnAwesomeTest extends BigDaddyKane {
public void testAwesome() throws Exception {
selenium.open("/");
selenium.click("link=Excel Entity Service");
// was selenium.waitForPageToLoad("3000");
selenium.waitForPageToLoad(pageReloadTime);
selenium.type("id=comment", "Test comment.");
selenium.click("id=submit");
// was selenium.waitForPageToLoad("3000");
selenium.waitForPageToLoad(pageReloadTime);
}
} |
Figure 3b. Modified AnAwesomeTestCase.java
1
2
3
4
5
6
7
8
| package awesomeTests;
@SuppressWarnings("deprecation")
public class AnotherTest extends BigDaddyKane {
public void testAwesome() throws Exception {
// your test code goes here
}
} |
Figure 3c. Modified AnotherTestCase.java
Since BigDaddyKane contains the same import statements and setUp() method we are able to remove them from AnAwesomeTestCase and AnotherTestCase. Code re-use FTW!
Leveraging Properties
If you take another look at BigDaddyKane.java you’ll notice some other new code.
9
10
11
12
| // properties
public int pageReloadTime = 3000;
public String url = "http://code.christophervigliotti.com/";
public String browser = "*chrome"; |
Figure 4, properties of BigDaddyKane
These are a few properties that I added to the class. The url and string properties are used in line 17 of BigDaddyKane.java. The pageReloadTime property is used on lines 9 and 13 of AnAwesomeTest.java. This modification allows for further code re-use, and specifies a single place where the page reload time, url and browser are defined.
Summary
Parent classes are a great way to help you both pay tribute to golden age rapper Big Daddy Kane and maximize code re-use in your Selenium RC tests.
Ready to dive deeper into the world of Java and Selenium RC? Check out my post on how your code can access test data via an Excel file.
Posted: January 10th, 2012 | Author: Christopher Vigliotti | Filed under: Groovy/Grails, Regular Expressions | 4 Comments »
The other day I had a Groovy/Grails bug fix whose solution was to break large non-space ‘words’ (or rather ‘substrings that do not contain spaces’) into smaller words by inserting spaces every X characters.
My regex is not the best, so after a bit of tinkering I reached out to the ever-helpful community over at Stack Overflow. My question was answered within an hour by Nik. I wrapped the solution in a simple function, and modified it to allow you to specify the character length…
static String breakLongWordsInString(str, charLength = 60) {
def response = (str =~ /(\w{${charLength}})/).replaceAll("\$1\n")
return response
}
And here’s how it was implemented…
<%@ page import="your.package.path.and.ObjectName" %>
${TextUtil.breakLongWordsInString(variableName, 60)}
Thanks Stack Overflow and Nik!
Posted: January 6th, 2012 | Author: Christopher Vigliotti | Filed under: Java, Selenium | 2 Comments »
This post is a part of my Learn Selenium series.
HEY THERE! In order to better understand this post (as well as the rest of the Learn Selenium Series) I suggest that you be familiar with basic Object-Oriented concepts in Java. I suggest picking up the most excellent book “Head First Java” and working through the first five or so chapters. If you have a preferred Intro to Java or Intro to Object Oriented Development resource please share it in the comments of this post.
As I said in the previous post “Selenium IDE is a free and awesome Firefox plugin that records and plays automated tests”. If you want to add conditions, looping and other logic to your tests you will need to move your test from Selenium IDE to either Selenium WebDriver or Selenium RC. At the time of this post Selenium RC is a more stable and robust platform than Selenium WebDriver (link).
Setting Up
In order to complete the tasks in this post you will need to perform these three steps:
- Download and install the free and awesome Eclipse IDE.
- Download and run the Selenium server. I suggest that you copy this file to a easy to access, non-network path. I use C:\selenium. Also note that Selenium Server will only work if you have the Java Development Kit (JDK) installed and running on your machine.
- Create a new Java project in Eclipse, referencing the external JAR from the previous post. Lets call the project ‘Tests Are Awesome’
Do I have JDK Installed?
In a command prompt type the word “java” and hit return. If you receive the following message
“Java” is not recognized as internal or external command, operable program or batch file
then you need to install the JDK, which can be downloaded here.
Start Selenium Server With A Batch File
I like to start and run Selenium Server via a batch file. The following batch file can be created by opening notepad, pasting the three lines of code and saving the file to your Desktop with the name “Start Selenium Server.txt”
1
2
3
| Cd C:\selenium
Java -jar selenium-server-standalone-2.13.0.jar -port 4444
pause |
Potential Issues With Running Selenium Server
If you run into an error where something else is running on port 4444, change line 2 of your batch file to run on a different port. Try port 4448. For more information on troubleshooting this issue click here.
Optional Installs
- jxl.jar – If you want to read data from an Excel file for use in your test scripts download jxl.jar. We’ll explore this more in a future post.
- Tortoise SVN – If you are running through this any my other Selenium tutorials in one of my classes you will be accessing code via Subversion. Please download and install Tortoise SVN.
Exporting A Selenium IDE Test
For this example I’ll use the test from my previous post. It has four steps. Here are the details
The Base URL is http://code.christophervigliotti.com/
1st command
command: open
target: /
value: [blank]
2nd command
command: clickAndWait
target: link=Excel Entity Service
value: [blank]
3rd command
command: type
target: id=comment
value: Test Comment.
4th command
command: clickAndWait
target: id=submit
value: [blank]
With the test case open in Selenium IDE, click File > Export Test Case > JUnit 3 (Remote Control). Lets save the file as AnAwesomeTest.txt. Open the file and you’ll see the following
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| package com.example.tests;
import com.thoughtworks.selenium.*;
import java.util.regex.Pattern;
public class awesome extends SeleneseTestCase {
public void setUp() throws Exception {
setUp("http://code.christophervigliotti.com/", "*chrome");
}
public void testAwesome() throws Exception {
selenium.open("/");
selenium.click("link=Excel Entity Service");
selenium.waitForPageToLoad("30000");
selenium.type("id=comment", "Test comment.");
selenium.click("id=submit");
selenium.waitForPageToLoad("30000");
}
} |
Recreating The Test In Selenium RC
Create a new Java class in your project. Call the file ‘AnAwesomeTest.java’ and specify the package ‘awesomeTests’. Open your new class file and check it out
1
2
3
4
| package awesomeTests;
public class AnAwesomeTest {
} |
Now it’s time to do some minor cut-and-paste surgery. We need to move the import statements and logic from AnAwesomeTest.txt over to AnAwesomeTest.java. We’ll also need to make AnAwesomeTest.java extend SeleneseTestCase. The result of this cutting and pasting is as follows…
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| package awesomeTests; // changed this
import com.thoughtworks.selenium.*;
import java.util.regex.Pattern; // you can delete this line
@SuppressWarnings("deprecation") // I added this to suppress Eclipse warnings
public class AnAwesomeTest extends SeleneseTestCase {
public void setUp() throws Exception {
setUp("http://code.christophervigliotti.com/", "*chrome");
}
public void testAwesome() throws Exception {
selenium.open("/");
selenium.click("link=Excel Entity Service"); // if your code crashes here change this to an XPath selector
selenium.waitForPageToLoad("30000");
selenium.type("id=comment", "Test comment.");
selenium.click("id=submit");
selenium.waitForPageToLoad("30000");
}
} |
Running The Test
Verify that Selenium Server is running on your local machine. Highlight the file AnAwesomeTest.java in Eclipse and press the green play button. Sit back and enjoy the show. The JUnit tab in Eclipse should appear and tell you that your test passed.
What Just Happened?
If your test ran too fast to observe the system interactions you can add commands to slow it down. Lets add a few Thread.sleep() commands…
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| package awesomeTests;
import com.thoughtworks.selenium.*;
@SuppressWarnings("deprecation")
public class AnAwesomeTest extends SeleneseTestCase {
public void setUp() throws Exception {
setUp("http://code.christophervigliotti.com/", "*chrome");
}
public void testAwesome() throws Exception {
selenium.open("/");
selenium.click("link=Excel Entity Service");
Thread.sleep(30000); // add one here
selenium.waitForPageToLoad("30000");
selenium.type("id=comment", "Test comment.");
Thread.sleep(30000); // add one here
selenium.click("id=submit");
selenium.waitForPageToLoad("30000");
Thread.sleep(30000); // add one here
}
} |
Re-run the test using the above code and you can observe each action as it’s happening.
Now What?
Now that your code is in Java/JUnit land you can leverage the power of Java to write your tests. In a follow-up post I’ll demonstrate the hows and whys of adding a parent class to your test cases.
I want more Selenium!!! Check out this post on adding a parent class to your test cases
Posted: January 5th, 2012 | Author: Christopher Vigliotti | Filed under: Selenium | No Comments »
This post is a part of my Learn Selenium series.
Selenium IDE is a free and awesome Firefox plugin that records and plays automated tests. Consider the following test…
The Base URL is http://code.christophervigliotti.com/
1st command
command: open
target: /
value: [blank]
2nd command
command: clickAndWait
target: link=Excel Entity Service
value: [blank]
3rd command
command: type
target: id=comment
value: Test Comment.
4th command
command: clickAndWait
target: id=submit
value: [blank]
The first command opens root page of the site. The second command clicks on the post named ‘Excel Entity Service’ and waits for the next page to load. The third command enters the text ‘Test Comment.’ into the comment box. The fourth command clicks the submit button and waits for the next page to load. This test will continue to work great as long as my goal is to run a test that clicks on the post titled “Excel Entity Service” and adds a comment.
Stop!
Now would be the perfect time to install the awesomely awesome Firefox plugin Firebug and it’s awesome cousin FirePath. You’ll have to install Firebug first, then reboot your browser, then install FirePath and reboot once again. Once you get it installed and have rebooted Firefox you can easily grab the XPath for any element on a web page by performing these steps:
- Open Firebug
- Click on the FirePath tab in Firebug
- Enable “Inspect Element” Ctrl+Shift+C
- Click the desired element
- Grab the XPath from Firebug
Note that FirePath XPath adds an extra . character that will need to be removed before using it in your Selenium IDE scripts.
What If
What if I wanted to write a test that adds a comment to the most recent post? I would need to use a different selector for step two…one that targets the link not by name, but by it’s relative position. Luckily the available selectors for each step are available in Selenium IDE. I highlighted the step in question in Selenium IDE and checked out the available choices in the ‘target’ drop-down. Here are my available choices for step two:
- css=div.post > h1 > a
- //a[contains(text(),'Excel Entity Service')]
- //div[@id='content']/div/h1/a
- //a[@href='http://code.christophervigliotti.com/2012/01/excel-entity-service/']
- //div[2]/div/h1/a
Lets take a closer look at each of these.
1. link=Excel Entity Service
This is the one that we’re presently using. It targets a link named ‘Excel Entity Service’. This is not the droid we’re looking for.
2. css=div.post > h1 > a
This is a css selector. We can tell that because it starts with ‘css=’. This selector targets the ‘a’ that appears after the ‘h1′ that appears in the ‘div’ whose class name is ‘post’. Could this be the solution?
3. //a[contains(text(),'Excel Entity Service')]
This is an XPath selector. XPath is [define here]. This selector targets the ‘a’ that contains the text ‘Excel Entity Service’, which will not work for our ‘what if’ requirement.
4. //div[@id='content']/div/h1/a
This is also an XPath selector. This selector targets the ‘a’ found after the ‘h1′ thats inside of the ‘div’ that’s inside of the ‘div’ whose id is ‘content’. Could this be the solution?
5. //a[@href='http://code.christophervigliotti.com/2012/01/excel-entity-service/']
This is also an XPath Selector. This time we are targeting the ‘a’ whose href is ‘http://code.christophervigliotti.com/2012/01/excel-entity-service/’. This will also not work for us.
6. //div[2]/div/h1/a
Our final choice is yet another XPath Selector. This one targets the ‘a’ after the ‘h1′ after the ‘div’ inside of the second ‘div’. Could this be the solution?
The Finalists
2, 4 and 6 are all possible solutions. The correct answer is “use any one of the finalists”. All three target the link to the first post based on it’s relative position.
Elsewhere
For more info on Selenium selectors check out this colossal chart. You can learn more about XPath here. If you are hankering for even more Selenium then check out my post on Importing A Selenium IDE Test Into RC Land
Posted: January 5th, 2012 | Author: Christopher Vigliotti | Filed under: Java, Selenium | 1 Comment »
This post is a part of my Learn Selenium series.
Requirement
I’m working with a team of testers that are developing Selenium RC scripts (in Java). They needed a quick and easy way to read data from Excel for use in Selenium tests.
Dependencies
This code requires jxl.jar, which can be downloaded here.
Usage
Entities (such as “Users”) can be defined in Excel using (1) the worksheet name as the entity name, (2) the first row of the sheet as the property names and (3) subsequent rows as the values.
So if you wanted to create a list of user accounts to test against a login page, you can create a worksheet named “users”, and specify the “username” and “password” property names in the first row. Once you’ve done that, you may add one or more usernames and passwords, and using ExcelEntityService.java you can easily grab an ArrayList of User entities using the following code…
1
| ArrayList<Hashtable<String, String>> users = excelEntityService.getExcelEntities("users"); |
Once you have your ArrayList of users you can easily loop through it and get each username and password…
2
3
4
5
6
| for(int i = 0; i < users.size(); i++){
String username = users.get(i).get("username");
String password = users.get(i).get("password");
// your fancy Selenium code goes here...
} |
You can also get a specific row by searching using the getExcelEntityBySearch() method, or specify the row number using the getExcelEntityByRowNumber() method.
The Code
If you are new to Java or don’t understand the code below you can simply cut and paste the code into a new class and use it as detailed above. If you have any questions feel free to ask in the comments section below.
Package declaration & imports….
1
2
3
4
5
6
7
8
| package com.vigliotti.selenium;
import java.io.File;
import java.io.IOException;
import java.util.*;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException; |
…class declaration, inputFile property and mutator “setter” method…
9
10
11
12
13
| public class ExcelEntityService {
private String inputFile;
public void setInputFile(String inputFile) {
this.inputFile = inputFile;
} |
Next is the private method getEntityPropertiesArrayList(). This method gets an ArrayList of entities for the specified Excel worksheet name.
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
| private ArrayList getEntityPropertiesArrayList(String sheetName) throws IOException{
ArrayList entityPropertiesArrayList = new ArrayList();
int columnCount = -1;
String propertyName = "";
try{
Sheet sheet = getSheet(sheetName);
columnCount = sheet.getColumns();
for(int i = 0; i < columnCount; i++){
Cell headerCell = sheet.getCell(i, 0);
propertyName = headerCell.getContents();
entityPropertiesArrayList.add(propertyName);
}
} catch (IOException e) {
e.printStackTrace();
}
return entityPropertiesArrayList;
} |
The getExcelEntities() method gets an ArrayList of Hashtables for all entities of the specified worksheet name.
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
| public ArrayList getExcelEntities(String sheetName) throws IOException{
ArrayList> entities = new ArrayList>();
jxl.Sheet sheet = null;
int rowCount = -1;
sheet = getSheet(sheetName);
rowCount = sheet.getRows() - 1;
for(int i = 1; 1 0){
Hashtable entityHashtable = getExcelEntityByRowNumber(sheetName, i);
entities.add(entityHashtable);
}else{
break;
}
}
return entities;
} |
Here is the awesomely awesome getExcelEntityBySearch(), for when you want a Hashtable of a single entity.
34
35
36
37
38
39
40
41
42
43
| public Hashtable getExcelEntityBySearch(String sheetName, String columnName, String columnValue) throws IOException{
System.out.println("ExcelEntityService.getExcelEntityBySearch()");
Hashtable entityHashtable = new Hashtable();
jxl.Sheet sheet = null;
int rowNumber = -1;
sheet = getSheet(sheetName);
rowNumber = sheet.findCell(columnValue).getRow();
entityHashtable = getExcelEntityByRowNumber(sheetName, rowNumber);
return entityHashtable;
} |
And if you know the row number of the entity, you can use this method…
44
45
46
47
48
49
50
51
52
53
54
55
56
| public Hashtable getExcelEntityByRowNumber(String sheetName, int rowNumber) throws IOException{
Hashtable entityHashtable = new Hashtable();
jxl.Sheet sheet = null;
ArrayList propertiesArrayList = new ArrayList();
int columnCount = -1;
propertiesArrayList = getEntityPropertiesArrayList(sheetName);
sheet = getSheet(sheetName);
columnCount = sheet.getColumns();
for(int i = 0; i < columnCount; i++){
entityHashtable.put(propertiesArrayList.get(i), sheet.getCell(i, rowNumber).getContents());
}
return entityHashtable;
} |
This private method interacts with jxl.jar, getting a Sheet object for use in some of the other methods.
57
58
59
60
61
62
63
64
65
66
| private Sheet getSheet(String sheetName) throws IOException{
File excelFile = new File(inputFile);
jxl.Sheet sheet = null;
try {
sheet = Workbook.getWorkbook(excelFile).getSheet(sheetName);
} catch (BiffException e) {
e.printStackTrace();
}
return sheet;
} |
If you cut and paste the code samples and add an extra “}” at the end you will have a working ExcelEntityService.java file.
Posted: January 5th, 2012 | Author: Christopher Vigliotti | Filed under: Java | 3 Comments »
As part of my Geeky Goals for 2012 I’m spending some time solving the problems found at Project Euler.
Here’s the solution for Problem 1…
int total = 0;
for(int i = 1; i < 1000; i++){
if(i % 3 == 0 || i % 5 == 0){
total = total + i;
}
}
System.out.println(total);
…and Problem 2…
int ceiling = 4000000; // four million
int total = 2;
int firstTerm = 1;
int secondTerm = 2;
int thirdTerm = 3;
int newFirstTerm = 0;
int newSecondTerm = 0;
int newThirdTerm = 0;
while (thirdTerm < ceiling) {
if(thirdTerm%2==0){
total = total + thirdTerm;
}
newSecondTerm = thirdTerm; // previous third becomes second
newFirstTerm = secondTerm; // previous second becomes first
newThirdTerm = newFirstTerm + newSecondTerm; // get the new third term
firstTerm = newFirstTerm;
secondTerm = newSecondTerm;
thirdTerm = newThirdTerm;
}
System.out.println(total);