asked on

How to export data from excel in sequence using HSSF

I am using Jakarta HSSF to export data from excel to java, it exports it fine but not in sequence

for eg if in the excel the columns are

first_name last_name add1 add2 city state zip

it will give the values in this order

last_name add1 first_name add2 state city zip

The function which I use to transfer data from excel is as follows

I was wondering if I can use a hashtable and assign each value to the column number, please let me know how to do that.

Any help will be greatly appreciated

--------------------------------------------------------------------------------------------------------------------------

public static String xlsTextStripper(FileInputStream fis) {

String content = null;

try {

StringBuffer sb = new StringBuffer();

HSSFWorkbook workbook = new HSSFWorkbook(fis);

int numOfSheets = workbook.getNumberOfSheets();

for (int i = 0; i < numOfSheets; i++) {

HSSFSheet sheet = workbook.getSheetAt(i);

Iterator rowIterator = sheet.rowIterator();

while (rowIterator.hasNext()) {

HSSFRow row = (HSSFRow) rowIterator.next();

Iterator cellIterator = row.cellIterator();

while (cellIterator.hasNext()) {

HSSFCell cell = (HSSFCell) cellIterator.next();

String cellStringValue = null;

if (cell.getCellType() == 4) {

boolean booleanValue = cell.getBooleanCellValue();

cellStringValue = Boolean.toString(booleanValue);

} else if (cell.getCellType() == 0) {

double doubleValue = cell.getNumericCellValue();

cellStringValue = Double.toString(doubleValue);

} else if (cell.getCellType() == 1) {

cellStringValue = cell.getStringCellValue();

}

if (cellStringValue != null) {

sb.append(cellStringValue);

sb.append("\t");

}

}

sb.append("\n");

}

}

content = sb.toString();

} catch (Exception e) {

e.printStackTrace();

}

return content;

}

---------------------------------------------------------------------------------------------------------------------------------

Mick Barry

try:

for (short i=row.getFirstCellNum(); i<=row.getLastCellNum(); i++) {
HSSFCell cell = row.getCell(i);

royalcyber

ASKER

it gives a null pointer exception

royalcyber

ASKER

my new code

public static ArrayList xlsTextStripper(FileInputStream fis) {

ArrayList record = new ArrayList();
ArrayList document = new ArrayList();
System.out.println(" I am in the xlsTextStripper method");
try {

HSSFWorkbook workbook = new HSSFWorkbook(fis);

int numOfSheets = workbook.getNumberOfSheets();

for (int i = 0; i < numOfSheets; i++) {

HSSFSheet sheet = workbook.getSheetAt(i);

Iterator rowIterator = sheet.rowIterator();

while (rowIterator.hasNext()) {

HSSFRow row = (HSSFRow) rowIterator.next();

Iterator cellIterator = row.cellIterator();

for (short input =row.getFirstCellNum(); input <=row.getLastCellNum(); input++) {
HSSFCell cell = row.getCell(input) == null? :row.getCell(input);
String cellStringValue = null;
if (cell.getCellType() == 4) {

boolean booleanValue = cell.getBooleanCellValue();

cellStringValue = Boolean.toString(booleanValue);

} else if (cell.getCellType() == 0) {

double doubleValue = cell.getNumericCellValue();

cellStringValue = Double.toString(doubleValue);

} else if (cell.getCellType() == 1) {

cellStringValue = cell.getStringCellValue();

}

System.out.println(cellStringValue);
}

document.add(record);

}

}

} catch (Exception e) {

e.printStackTrace();

}

return document;

}

Mick Barry

try:

for (short input =row.getFirstCellNum(); input <=row.getLastCellNum(); input++) {
HSSFCell cell = row.getCell(input);
if (cell!=null) {
String cellStringValue = null;
if (cell.getCellType() == 4) {

boolean booleanValue = cell.getBooleanCellValue();

cellStringValue = Boolean.toString(booleanValue);

} else if (cell.getCellType() == 0) {

double doubleValue = cell.getNumericCellValue();

cellStringValue = Double.toString(doubleValue);

} else if (cell.getCellType() == 1) {

cellStringValue = cell.getStringCellValue();

}

System.out.println(cellStringValue);
}
}

royalcyber

ASKER

objects thanks it worked

But now when I print the values in the java program it prints it three times. Would you know by any chance why?

document contains record arraylist
and record arraylist contains String of data

here is my code

ArrayList document = ExportUtil.xlsTextStripper(fs);
Iterator document_iterator = document.iterator();

if(document.size() > 0){
while(document_iterator.hasNext()){
ArrayList record = (ArrayList)document_iterator.next();
Iterator record_iterator = record.iterator();
if(record.size() > 0){

while(record_iterator.hasNext()){

String value = (String)record_iterator.next();
System.out.println("The value is " + value);
}
System.out.println("--------------------------------------------");
}

}
}

Nguyen Huu Phuoc

Hi royalcyber!
Because of my timzone, I can't continue stay with u last night. I gave u solution in this thread
https://www.experts-exchange.com/questions/21859847/how-to-read-excel-file-from-a-java-application-using-HSSF-POI.html
Why don't you read it?

Nguyen Huu Phuoc

You put document.add(record) wrong place.
Yours:
document.add(record);
}
}
} catch (Exception e) {
It should be:
}
}
document.add(record);
} catch (Exception e) {
It is fun for me when I ind that your test xls document has two sheets and every sheets has tow columns.
Phuoc H. Nguyen

Nguyen Huu Phuoc

I think you should change here:
for (short input =row.getFirstCellNum(); input <=row.getLastCellNum(); input++) {
into:
for (short input =row.getFirstCellNum(); input < row.getLastCellNum(); input++) {
if you use "<=" instead of "<" you will get a null cell when input is equal to row.getLastCellNum()
Phuoc H. Nguyen

Mick Barry

output the contents of Document and see whats in it:

System.out.println(document);

royalcyber

ASKER

Thank You guys for all your help

I was actually trying few things and what I found that when there are two other empty sheets in the workbook, it adds everything three times
Would you guys, how can I make sure that it doesn't go in the for statement if the sheet is blank

Here is my code
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

public static ArrayList xlsTextStripper(FileInputStream fis) {

ArrayList record = new ArrayList();
ArrayList document = new ArrayList();
System.out.println(" I am in the xlsTextStripper method");
try {

HSSFWorkbook workbook = new HSSFWorkbook(fis);

int numOfSheets = workbook.getNumberOfSheets();

for (int i = 0; i < numOfSheets; i++) {

HSSFSheet sheet = workbook.getSheetAt(0);

Iterator rowIterator = sheet.rowIterator();

while (rowIterator.hasNext()) {

HSSFRow row = (HSSFRow) rowIterator.next();

for (short input = row.getFirstCellNum();
input <= (row.getLastCellNum() - 1); input++) {
HSSFCell cell = row.getCell(input);
String cellStringValue = null;
if (cell != null) {

if (cell.getCellType() == 4) {

boolean booleanValue = cell.getBooleanCellValue();

cellStringValue = Boolean.toString(booleanValue);

} else if (cell.getCellType() == 0) {

double doubleValue = cell.getNumericCellValue();

cellStringValue = Double.toString(doubleValue);

} else if (cell.getCellType() == 1) {

cellStringValue = cell.getStringCellValue();
}

} else {
cellStringValue = "Empty";
}
record.add(cellStringValue);
System.out.println(cellStringValue);
}
System.out.println("-------------------------------------------");

}
document.add(record);

}

}

catch (Exception e) {

e.printStackTrace();

}

return document;

}

Nguyen Huu Phuoc

I think you should add the following statement:
before:
document.add(record);
it should be:
document.add(record);
record.clear();//restart this arraylist!

ASKER CERTIFIED SOLUTION

Nguyen Huu Phuoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

royalcyber

ASKER

phuonch

When I try that it actually clears the record from the docment also. When I print the document it shows nothing.

One more thing which i found is that when I print he ArrayLsit document, which contains ArrayList record. The record arrayList contains all the records or rows of a sheet. But what I need is that record should contain cells or columns of one row and document should contain the records of the entire worksheet.

I tried but I couldn't catch this problem. I add the the cells to the record inside the for loop where it iterates over each column and add the record to the document inside the while loop where it iterates over the rows

Please help

royalcyber

ASKER

Phuonch

Thank you very much for your help, It works perfectly fine

But I was just wandering if you can let me know why I was getting duplicates and didi the clne method slved it. I would really appreciate that.

Also there is another one small problem. The date is written in the excel sheet in this format 4/19/2006
Now when I transfer the data it actually diivides the number and brings it as a value. Would you know how can I take the date as String r date/time field

Thanks

Nguyen Huu Phuoc

I can explain the reason is that the arraylist is linked list (just personal view). So when you use:
document.add(record)=>it add record into this linked list.
record.clear()=> Remove all objcets in the record, so it remove also in document.
When you use record.clone() it shallow copy record into new object and add into document.
>>Also there is another one small problem. The date is written in the excel sheet in this format 4/19/2006
>>Now when I transfer the data it actually diivides the number and brings it as a value. Would you know
>>how can I take the date as String r date/time field
http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html
Using java.text.SimpleDateFormat.
SimpleDateFormat dateformat = new SimpleDateFormat("dd/MM/yy");
get Date/time: String datetimestring = dateformat.format (cell.getDateCellValue());
set Date/time: cell.setCellValue(dateformat.parse("27/11/2006"));

Mick Barry

why was my earlier comment that answered the question asked ignored?