extracting data from an excel worksheet 1

[theRook]

Hi. Im trying to build an app that will extract several fields from several Excel worksheets. For example, I may get worksheets from several different companies and I want to extract two fields from each of these worksheets, such as partID and quantity. How would I go about extracting this data using java? I believe I must convert it to a CSV file. If this is the case, how do I go about doing that? I appreciate any help you guys can provide or even point me in the right direction! Thanx.

 

[tlohan]

Hi,

To convert an excel sheet to a csv just do a simple Save As.. when the excel sheet is open.

To extract two fields from the sheet you could do the following:


import java.io.*;

...

try {
BufferedReader fileInput = new BufferedReader(new FileReader(filename));
String line = fileInput.readLine();
String delimiters = ",";
int linecount = 0;
int partidcol = 0;
int quantitycol = 0;
int wordcount = 0;

while (line != null) {
StringTokenizer str = new StringTokenizer (line, delimiters);
wordcount = 0;
// Read first line and find what cols the partid and qty are in
if (linecount == 0) {
while (str.hasMoreTokens()) {
String word = str.nextToken();
if (word.equals("PARTID&quot)
partidcol = wordcount;
if (word.equals("QUANTITY&quot)
quantitycol = wordcount;
wordcount++;
}
}

if (linecount > 0) {
while (str.hasMoreTokens()) {
String word = str.nextToken();
if (wordcount == partidcol)
PartIDArray = word.trim();
if (wordcount == quantitycol )
QuantityArray = word.trim();
wordcount++;
}
}

linecount++;
}
} catch (IOException e) {
//do something
}


Hope this helps.

Cheers.

 

[theRook]

Thanks tlohan. This makes sense.