Here is a example for exporting the content of a excel file to txt file.
The values that are exported are calculated/evaluated, so when a concatenation value like '=A1&B1' is used for a cell it will export the evaluated text.
In case the are references to excel files with data in it like [testdata.xls]testdata'!$B$3 it will also be used for evaluation.
For this Apache POI - the Java API for Microsoft Documents is used to open the files and read its values.
The test data
Suppose we creating a test script for logging in to an application.
The data will be like username and pass word, the Excel sheet look like this:
A B
name value
username maikel
password secret
Safe the file as 'data.xls'
First we set up simple testcase which will start the application and login.
The content of the Excelsheet:
A B C D
action field data toExport
click login click(login; )
type user maikel type(user;maikel)
type pass secret type(pass;secret)
click login click(login; )
validate message logged in validat(message;logged in)
Set the worksheet name to 'Testcase' so that we can refer to it later.
Safe the file as 'ftg_1.xls'
Note that:
-The value of C3 is a reference to the data.xls!B2
-The value of C4 is a reference to the data.xls!B3
-The value of D2 is a concatenation of A2, B2 and C2
What can do is to extract the values from column D and put them in a text file.
After setting the example.properties file we start the export by typing in the command line:
java Main
The command line output will be looking like this:
Start search in directory:./test/ExcelSheet with prefix filter:ftg_
>>Processing file:ftg_1.xls
>>Skipping file:testdata.xls
>>StartExport file=ftg_1.xls
>>Outputtext:
click(login; )
type(user;maikel)
type(pass;secret)
>>Finished searching
The output file './TestOutput/ftg_1.txt' is created and contains only the values of third column D.
PART 2 Java implementation
As mentioned poi is used for reading the excel file.
The main function is opening the file and reading column D, store it in a string buffer.
And write is to the out put file:
private static void exportFile(String filename) {
System.out.println( ">>StartExport file=" + filename);
HSSFWorkbook wb = openSample(filename);
System.out.println(">>numer of sheets=" + wb.getNumberOfSheets());
String searchSheet=excelProps.getProperty("sheetname", "Testcase");
HSSFSheet s = wb.getSheet(searchSheet);
if (s==null){
System.out.println(">>Not found searchSheet name:" + searchSheet);
return;
}
int lastrownum=s.getLastRowNum();
System.out.println( ">>Last rownumber=" + lastrownum);
HSSFRow row = null;
Cell cell = null;
StringBuffer sb=new StringBuffer();
for (int i=0;i<lastrownum;i++){
row = s.getRow(i);
if (row!=null){
cell = row.getCell(Integer.parseInt(excelProps.getProperty("colomnnumber", "3")));
if (cell!=null){
try{
sb.append(cell.getStringCellValue()+"\n");
} catch (RuntimeException e){
sb.append( "#Poi Extract Excel error: "+ e.getMessage()+" in row=" + (i +1) +"\n");
}
}
}
}
//
//Echo to standard output
//
System.out.println(">>Outputtext:"+sb.toString());
}
It seems to work with the picture template on blogspot.nl blogger.com. I had some troubles with the dynamic view, in which it did not seem to work.
Bash
#!/bin/bash
# Fibonacci numbers
# Writes an infinite series to stdout, one entry per line
function fib() {
local a=1
local b=1
while true ; do
echo $a
local tmp=$a
a=$(( $a + $b ))
b=$tmp
done
}
# output the 10th element of the series and halt
fib | head -10 | tail -1
Bash w/ language specified
#!/bin/bash
# Fibonacci numbers
# Writes an infinite series to stdout, one entry per line
function fib() {
local a=1
local b=1
while true ; do
echo $a
local tmp=$a
a=$(( $a + $b ))
b=$tmp
done
}
# output the 10th element of the series and halt
fib | /usr/bin/*head -10 | tail -1
C
#include <stdio.h>
/* the n-th fibonacci number.
*/
unsigned int fib(unsigned int n) {
unsigned int a = 1, b = 1;
unsigned int tmp;
while (--n >= 0) {
tmp = a;
a += b;
b = tmp;
}
return a;
}
main() {
printf("%u", fib(10));
}
C w/ language specified
#include <stdio.h>
/* the nth fibonacci number. */
uint32 fib(unsigned int n) {
uint32 a = 1, b = 1;
uint32 tmp;
while (--n >= 0) {
tmp = a;
a += b;
b = tmp;
}
return a;
}
void main() {
size_t size = sizeof(wchar_t);
ASSERT_EQ(size, 1);
printf("%u", fib(10));
}
#define ZERO 0 /* a
multiline comment */
C++
#include <iostream>
using namespace std;
//! fibonacci numbers with gratuitous use of templates.
//! \param n an index into the fibonacci series
//! \param fib0 element 0 of the series
//! \return the nth element of the fibonacci series
template <class T>
T fib(unsigned int n, const T& fib0) {
T a(fib0), b(fib0);
for (; n; --n) {
T tmp(a);
a += b;
b = tmp;
}
return a;
}
int main(int argc, char **argv) {
cout << fib(10, 1U);
}
C++ w/ language specified
#include <iostream>
using namespace std;
//! fibonacci numbers with gratuitous use of templates.
//! \param n an index into the fibonacci series
//! \param fib0 element 0 of the series
//! \return the nth element of the fibonacci series
template <class T>
T fib(int n, const T& fib0) {
T a(fib0), b(fib0);
while (--n >= 0) {
T tmp(a);
a += b;
b = tmp;
}
return a;
}
int main(int argc, char **argv) {
cout << fib(10, 1U);
}
Java
package foo;
import java.util.Iterator;
/**
* the fibonacci series implemented as an Iterable.
*/
public final class Fibonacci implements Iterable<Integer> {
/** the next and previous members of the series. */
private int a = 1, b = 1;
@Override
public Iterator<Integer> iterator() {
return new Iterator<Integer>() {
/** the series is infinite. */
public boolean hasNext() { return true; }
public Integer next() {
int tmp = a;
a += b;
b = tmp;
return a;
}
public void remove() { throw new UnsupportedOperationException(); }
};
}
/**
* the n<sup>th</sup> element of the given series.
* @throws NoSuchElementException if there are less than n elements in the
* given Iterable's {@link Iterable#iterator iterator}.
*/
public static <T>
T nth(int n, Iterable<T> iterable) {
Iterator<? extends T> it = iterable.iterator();
while (--n > 0) {
it.next();
}
return it.next();
}
public static void main(String[] args) {
System.out.print(nth(10, new Fibonacci()));
}
}
Java w/ language specified(first line shown is line 12)
package foo;
import java.util.Iterator;
/**
* the fibonacci series implemented as an Iterable.
*/
public final class Fibonacci implements Iterable<Integer> {
/** the next and previous members of the series. */
private int a = 1, b = 1;
@Override
public Iterator<Integer> iterator() {
return new Iterator<Integer>() {
/** the series is infinite. */
public boolean hasNext() { return true; }
public Integer next() {
int tmp = a;
a += b;
b = tmp;
return a;
}
public void remove() { throw new UnsupportedOperationException(); }
};
}
/**
* the n<sup>th</sup> element of the given series.
* @throws NoSuchElementException if there are less than n elements in the
* given Iterable's {@link Iterable#iterator iterator}.
*/
public static <T>
T nth(int n, Iterable<T> iterable) {
Iterator<? extends T> in = iterable.iterator();
while (--n > 0) {
in.next();
}
return in.next();
}
public static void main(String[] args) {
System.out.print(nth(10, new Fibonacci()));
}
}
# not a java comment
# not keywords: static_cast and namespace
Javascript
/**
* nth element in the fibonacci series.
* @param n >= 0
* @return the nth element, >= 0.
*/
function fib(n) {
var a = 1, b = 1;
var tmp;
while (--n >= 0) {
tmp = a;
a += b;
b = tmp;
}
return a;
}
document.write(fib(10));
Issue 12 - Javascript Regular Expressions
/foo/; // a slash starting a line treated as a regexp beginning
"foo".match(/fo+$/);
// this line comment not treated as a regular expressions
"foo /bar/".test(/"baz"/); // test string and regexp boundaries
var division = /\b\d+\/\d+/g; // test char sets and escaping of specials
var allSpecials = /([^\(\)\[\]\{\}\-\?\+\*\.\^\$\/]+)\\/;
var slashInCharset = /[^/]/g, notCloseSq = /[^\]]/;
// test that slash used in numeric context treated as an operator
1 / 2;
1. / x;
x / y;
(x) / y;
1 /* foo */ / 2;
1 /* foo *// 2;
1/2;
1./x;
x/y;
(x)/y;
// test split over two lines. line comment should not fool it
1//
/2;
x++/y;
x--/y;
x[y] / z;
f() / n;
// test that slash after non postfix operator is start of regexp
log('matches = ' + /foo/.test(foo));
// test keyword preceders
return /a regexp/;
division = notreturn / not_a_regexp / 2; // keyword suffix does not match
// & not used as prefix operator in javascript but this should still work
&/foo/;
extends = /extends/;
Issue 12 - Javascript Regular Expressions w/ language specified
/foo/; // a slash starting a line treated as a regexp beginning
"foo".match(/fo+$/);
// this line comment not treated as a regular expressions
"foo /bar/".test(/"baz"/); // test string and regexp boundaries
var division = /\b\d+\/\d+/g; // test char sets and escaping of specials
var allSpecials = /([^\(\)\[\]\{\}\-\?\+\*\.\^\$\/]+)\\/;
var slashInCharset = /[^/]/g, notCloseSq = /[^\]]/;
// test that slash used in numeric context treated as an operator
1 / 2;
1. / x;
x / y;
(x) / y;
1 /* foo */ / 2;
1 /* foo *// 2;
1/2;
1./x;
x/y;
(x)/y;
// test split over two lines. line comment should not fool it
1//
/2;
x++/y;
x--/y;
x[y] / z;
f() / n;
// test that slash after non postfix operator is start of regexp
log('matches = ' + /foo/.test(foo));
// test keyword preceders
return /a regexp/;
division = notreturn / not_a_regexp / 2; // keyword suffix does not match
// & not used as prefix operator in javascript but this should still work
&/foo/;
extends = /extends/;
Coffee
class Animal
constructor: (@name) ->
move: (meters, loc) ->
alert @name + " moved " + meters + "m."
travel: (path...) ->
for place in path
@move place.distance, place.location
class Horse extends Animal
###
@param name Horse name
@param jumper Jumping ability
###
constructor: (name, jumper) ->
super name
@capable = jumper
step: ->
alert '''
Step,
step...
'''
jump: ->
@capable
move: (meters, where) ->
switch where
when "ground"
@step()
super meters
when "hurdle"
super meters if @jump()
# Create horse
tom = new Horse "Tommy", yes
street =
location: "ground"
distance: 12
car =
location: "hurdle"
distance: 2
###
Tell him to travel:
1. through the street
2. over the car
###
tom.travel street, car
Perl
#!/usr/bin/perl
use strict;
use integer;
# the nth element of the fibonacci series
# param n - an int >= 0
# return an int >= 0
sub fib($) {
my $n = shift, $a = 1, $b = 1;
($a, $b) = ($a + $b, $a) until (--$n < 0);
return $a;
}
print fib(10);
Python
#!/usr/bin/python2.4
def fib():
'''
a generator that produces the elements of the fibonacci series
'''
a = 1
b = 1
while True:
a, b = a + b, a
yield a
def nth(series, n):
'''
returns the nth element of a series,
consuming the earlier elements of the series
'''
for x in series:
n = n - 1
if n <= 0: return x
print nth(fib(), 10)
Python w/ language specified
#!/usr/bin/python2.4
def fib():
'''
a generator that produces the fibonacci series's elements
'''
a = 1
b = 1
while True:
a, b = a + b, a
yield a
def nth(series, n):
'''
returns the nth element of a series,
consuming the series' earlier elements.
'''
for x in series:
n -= 1
if n <= 0: return x
print nth(fib(), 10)
/* not a comment and not keywords: null char true */
SQL w/ language specified
/* A multi-line
* comment */
'Another string /* Isn\'t a comment',
"A string */"
-- A line comment
SELECT * FROM users WHERE id IN (1, 2.0, +30e-1);
-- keywords are case-insensitive.
-- Note: user-table is a single identifier, not a pair of keywords
select * from user-table where id in (x, y, z);
<html>
<head>
<title>Fibonacci number</title>
<style><!-- BODY { text-decoration: blink } --></style>
<script src="foo.js"></script>
<script src="bar.js"></script>
</head>
<body>
<noscript>
<dl>
<dt>Fibonacci numbers</dt>
<dd>1</dd>
<dd>1</dd>
<dd>2</dd>
<dd>3</dd>
<dd>5</dd>
<dd>8</dd>
…
</dl>
</noscript>
<script type="text/javascript"><!--
function fib(n) {
var a = 1, b = 1;
var tmp;
while (--n >= 0) {
tmp = a;
a += b;
b = tmp;
}
return a;
}
document.writeln(fib(10));
// -->
</script>
</body>
</html>
HTML w/ language specified
Fibonacci Numbers
<noscript>
<dl style="list-style: disc">
<dt>Fibonacci numbers</dt>
<dd>1</dd>
<dd>1</dd>
<dd>2</dd>
<dd>3</dd>
<dd>5</dd>
<dd>8</dd>
…
</dl>
</noscript>
<script type="text/javascript"><!--
function fib(n) {
var a = 1, b = 1;
var tmp;
while (--n >= 0) {
tmp = a;
a += b;
b = tmp;
}
return a;
}
document.writeln(fib(10));
// -->
</script>
HTML using XMP
Fibonacci number
XHTML
<xhtml>
<head>
<title>Fibonacci number</title>
</head>
<body onload="alert(fib(10))">
<script type="text/javascript"><![CDATA[
function fib(n) {
var a = 1, b = 1;
var tmp;
while (--n >= 0) {
tmp = a;
a += b;
b = tmp;
}
return a;
}
]]>
</script>
</body>
</xhtml>
PHP
<html>
<head>
<title><?= 'Fibonacci numbers' ?></title>
<?php
// PHP has a plethora of comment types
/* What is a
"plethora"? */
function fib($n) {
# I don't know.
$a = 1;
$b = 1;
while (--$n >= 0) {
echo "$a\n";
$tmp = $a;
$a += $b;
$b = $tmp;
}
}
?>
</head>
<body>
<?= fib(10) ?>
</body>
</html>
XSL (Issue 19)
<!-- Test elements and attributes with namespaces -->
<xsl:stylesheet xml:lang="en">
<xsl:template match=".">
<xsl:text>Hello World</xsl:text>
</xsl:template>
</xsl:stylesheet>
Whitespace
Misc
// ends with line comment token
//
User submitted testcase for Bug 4
Javascript Snippets wrapped in HTML SCRIPT tags hides/destroys inner content
<script type="text/javascript">
var savedTarget=null; // The target layer (effectively vidPane)
var orgCursor=null; // The original mouse style so we can restore it
var dragOK=false; // True if we're allowed to move the element under mouse
var dragXoffset=0; // How much we've moved the element on the horozontal
var dragYoffset=0; // How much we've moved the element on the verticle
vidPaneID = document.getElementById('vidPane'); // Our movable layer
vidPaneID.style.top='75px'; // Starting location horozontal
vidPaneID.style.left='75px'; // Starting location verticle
<script>
The fact that the script tag was not closed properly was causing
PR_splitSourceNodes to end without emitting the script contents.
Bug 8 - tabs mangled
If tabs are used to indent code inside <pre> IE6 and 7 won't honor them
after the script runs.
Code indented with tabs will be shown aligned to the left margin instead of
the proper indenting shown in Firefox.
I'm using Revision 20 of prettify.js, IE 6.0.29.00 in English and IE
7.0.5730.11 in Spanish.
oneTwothree Four five |
Six seven Eight nine Ten |
eleven Twelve thirteen Fourteen fifteen |
Bug 14a - does not recognize <br> as newline
//comment int main(int argc, char **argv)
{}
Bug 14b - comments not ignored
<!-- There's an HTML comment in my comment -->
<p>And another one inside the end tag</p>
Bug 20 - missing blank lines
<html>
<head>
Bug 21 - code doesn't copy and paste well in IE
<html>
<head>
<title>Test</title>
</head>
</html>
To test this bug, disable overriding of _pr_isIE6 in test_base.js
by putting #testcopypaste on the end of the URL and reloading the
page, then copy and paste the above into Notepad.
Bug 22 - Line numbers and other non-code spans in code
01: // This is a line of code
02: /* Multiline comments can
03: * span over and around
04: * line markers
And can even be interruptedby inline code annotations05: */
06: class MyClass extends Foo {
07: public static void main(String... argv) {
08: System.out.print("Hello World");
09: }
10: }
Bug 24 - Lua Syntax Highlighting
os=require("os")
math=require("math")
-- Examples from the language reference
a = 'alo\n123"'
a = "alo\n123\""
a = '\97lo\10\04923"'
a = [[alo
123"]]
a = [==[
alo
123"]==]
3 3.0 3.1416 314.16e-2 0.31416E1 0xff 0x56
-- Some comments that demonstrate long brackets
double_quoted = "Not a long bracket [=["
--[=[ quoting out
[[ foo ]]
[==[does not end comment either]==]
]=]
past_end_of_comment
--]=]
-- Example code courtesy Joseph Harmbruster
#
do
local function ssgeneral(t, n, before)
for _, h in ipairs(incs) do
for i = h + 1, n do
local v = t[i]
for j = i - h, 1, -h do
local testval = t[j]
if not before(v, testval) then break end
t[i] = testval; i = j
end
t[i] = v
end
end
return t
end
function shellsort(t, before, n)
n = n or #t
if not before or before == "<" then return ssup(t, n)
elseif before == ">" then return ssdown(t, n)
else return ssgeneral(t, n, before)
end
end
return shellsort
end
Bug 27 - VBScript w/ language specified
Imports System
Class [class]
Shared Sub [shared](ByVal [boolean] As Boolean)
If [boolean] Then
Console.WriteLine("true")
Else
Console.WriteLine("false")
End If
End Sub
End Class
Module [module]
Sub Main()
[class].[shared](True)
' This prints out: ".
Console.WriteLine("""")
' This prints out: a"b.
Console.WriteLine("a""b")
' This prints out: a.
Console.WriteLine("a"c)
' This prints out: ".
Console.WriteLine(""""c)
End Sub
End Module
Dim d As Date
d = # 8/23/1970 3:45:39AM #
d = # 8/23/1970 #
d = # 3:45:39AM #
d = # 3:45:39 #
d = # 13:45:39 #
d = # 13:45:39PM #
Dim n As Float
n = (0.0, .99F, 1.0E-2D, 1.0E+3D, .5E4, 1E3R, 4D)
Dim i As Integer
i = (0, 123, 45L, &HA0I, &O177S)
Bug 30 - Haskell w/ language specified
-- A comment
Not(--"a comment")
Also.not(--(A.comment))
module Foo(bar) where
import Blah
import BlahBlah(blah)
import Monads(Exception(..), FIO(..),unFIO,handle,runFIO,fixFIO,fio,
write,writeln,HasNext(..),HasOutput(..))
{- nested comments
- don't work {-yet-} -}
instance Thingy Foo where
a = b
data Foo :: (* -> * -> *) -> * > * -> * where
Nil :: Foo a b c
Cons :: a b c -> Foo abc -> Foo a b c
str = "Foo\\Bar"
char = 'x'
Not.A.Char = 'too long' -- Don't barf. Show that 't is a lexical error.
(ident, ident', Fo''o.b'ar)
(0, 12, 0x45, 0xA7, 0o177, 0O377, 0.1, 1.0, 1e3, 0.5E-3, 1.0E+45)
Bug 33 - OCaml and F#
(*
* Print the 10th fibonacci number
*)
//// A line comment
"A string";;
(0, 125, 0xa0, -1.0, 1e6, 1.2e-3);; // number literals
#if fibby
let
rec fib = function (0, a, _) -> a
| (n, a, b) -> fib(n - 1, a + b, a)
in
print_int(fib(10, 1, 1));;
#endif
let zed = 'z'
let f' x' = x' + 1
Still TODO: handle nested (* (* comments *) *) properly.
<!--
@charset('UTF-8');
/** A url that is not quoted. */
@import(url(/more-styles.css));
HTML { content-before: 'hello\20'; content-after: 'w\6f rld';
-moz-spiff: inherit !important }
/* Test units on numbers. */
BODY { margin-bottom: 4px; margin-left: 3in; margin-bottom: 0; margin-top: 5% }
/** Test number literals and quoted values. */
TABLE.foo TR.bar A#visited { color: #001123; font-family: "monospace" }
/** bolder is not a name, so should be plain. !IMPORTANT is a keyword
* regardless of case.
*/
blink { text-decoration: BLINK !IMPORTANT; font-weight: bolder }
-->
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Placemark>
<name>Simple placemark</name>
<description Lang="en">Attached to the ground. Intelligently places itself
at the height of the underlying terrain.</description>
<Point>
<coordinates>-122.0822035425683,37.42228990140251,0</coordinates>
</Point>
</Placemark>
</kml>
Issue 93 -- C# verbatim strings
// The normal string syntax
string a = "C:\\";
// is equivalent to a verbatim string
string b = @"C:\";
VHDL mode
library ieee;
use ieee.std_logic_1164.all;
use ieee.numeric_std.all;
-- A line comment
entity foo_entity is
generic (-- comment after punc
a : natural := 42;
x : real := 16#ab.cd#-3
);
port (
clk_i : in std_logic;
b_i : in natural range 0 to 100;
c_o : out std_logic_vector(5 downto 0);
\a "name"\ : out integer -- extended identifier
);
end entity foo_entity;
architecture foo_architecture of foo_entity is
signal bar_s : std_logic_vector(2 downto 0);
begin
bar_s <= b"101";
dummy_p : process (clk_i)
begin
if b_i = 1 then
c_o <= (others => '0');
elsif rising_edge(clk_i) then
c_o <= "1011" & bar_s(1 downto 0);
end if;
end process dummy_p;
end architecture foo_architecture;
echo "Make a choice"
read -p "Whats your name?" -a NAME
Read a string value, wait for 10 seconds.
echo "Is your name $NAME?"
read -t 10 -p "Choice 1 YES, 2 NO:" -a YESNO
check if the YESNO value is set.
if [[ -z $YESNO ]]; then
echo "YESNO not set exit"
exit 0
fi
Check the of YESNO
if [ $YESNO -eq 1 ]; then
echo "Yes: $YESNO"
elif [ $YESNO -eq 2 ] ; then
echo "No: $YESNO"
else
echo "Unkown choice: $YESNO"
fi
Whole script:
#!/bin/bash
#
#
echo "Make a choice"
read -p "Whats your name?" -a NAME
echo "Is your name $NAME?"
read -t 10 -p "Choice 1 YES, 2 NO:" -a YESNO
if [[ -z $YESNO ]]; then
echo "YESNO not set exit"
exit 0
fi
if [ $YESNO -eq 1 ]; then
echo "Yes: $YESNO"
elif [ $YESNO -eq 2 ] ; then
echo "No: $YESNO"
else
echo "Unkown choice: $YESNO"
fi
$ for xx in {1..4}; do echo "Hello world $xx"; done
Hello world 1
Hello world 2
Hello world 3
Hello world 4
For loop form 101 down to 95.
$ for xx in {101..95}; do echo "Hello world $xx"; done
Hello world 101
Hello world 100
Hello world 99
Hello world 98
Hello world 97
Hello world 96
Hello world 95
For with steps
$ for((i=1;i<=10;i+=2)); do echo "Welcome $i times"; done
Welcome 1 times
Welcome 3 times
Welcome 5 times
Welcome 7 times
Welcome 9 times
Work with files
$ for i in *; do echo $i; done
Applications
Desktop
Documents
Downloads
with filter
$ for i in ./*.xls; do echo $i; done
./2012.02.sysgarden.xls
./2012.03.sysgarden.xls
./2012.04.sysgarden.xls