royalcyber
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(FileInputS tream 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(booleanVa lue);
} else if (cell.getCellType() == 0) {
double doubleValue = cell.getNumericCellValue() ;
cellStringValue = Double.toString(doubleValu e);
} 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;
}
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
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(FileInputS
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(booleanVa
} else if (cell.getCellType() == 0) {
double doubleValue = cell.getNumericCellValue()
cellStringValue = Double.toString(doubleValu
} 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;
}
--------------------------
ASKER
it gives a null pointer exception
ASKER
my new code
public static ArrayList xlsTextStripper(FileInputS tream 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(booleanVa lue);
} else if (cell.getCellType() == 0) {
double doubleValue = cell.getNumericCellValue() ;
cellStringValue = Double.toString(doubleValu e);
} else if (cell.getCellType() == 1) {
cellStringValue = cell.getStringCellValue();
}
System.out.println(cellStr ingValue);
}
document.add(record);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return document;
}
public static ArrayList xlsTextStripper(FileInputS
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(booleanVa
} else if (cell.getCellType() == 0) {
double doubleValue = cell.getNumericCellValue()
cellStringValue = Double.toString(doubleValu
} else if (cell.getCellType() == 1) {
cellStringValue = cell.getStringCellValue();
}
System.out.println(cellStr
}
document.add(record);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return document;
}
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(booleanVa lue);
} else if (cell.getCellType() == 0) {
double doubleValue = cell.getNumericCellValue() ;
cellStringValue = Double.toString(doubleValu e);
} else if (cell.getCellType() == 1) {
cellStringValue = cell.getStringCellValue();
}
System.out.println(cellStr ingValue);
}
}
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(booleanVa
} else if (cell.getCellType() == 0) {
double doubleValue = cell.getNumericCellValue()
cellStringValue = Double.toString(doubleValu
} else if (cell.getCellType() == 1) {
cellStringValue = cell.getStringCellValue();
}
System.out.println(cellStr
}
}
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.ha sNext()){
ArrayList record = (ArrayList)document_iterat or.next();
Iterator record_iterator = record.iterator();
if(record.size() > 0){
while(record_iterator.hasN ext()){
String value = (String)record_iterator.ne xt();
System.out.println("The value is " + value);
}
System.out.println("------ ---------- ---------- ---------- --------") ;
}
}
}
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
Iterator document_iterator = document.iterator();
if(document.size() > 0){
while(document_iterator.ha
ArrayList record = (ArrayList)document_iterat
Iterator record_iterator = record.iterator();
if(record.size() > 0){
while(record_iterator.hasN
String value = (String)record_iterator.ne
System.out.println("The value is " + value);
}
System.out.println("------
}
}
}
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?
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?
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
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
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
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
output the contents of Document and see whats in it:
System.out.println(documen t);
System.out.println(documen
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(FileInputS tream 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(booleanVa lue);
} else if (cell.getCellType() == 0) {
double doubleValue = cell.getNumericCellValue() ;
cellStringValue = Double.toString(doubleValu e);
} else if (cell.getCellType() == 1) {
cellStringValue = cell.getStringCellValue();
}
} else {
cellStringValue = "Empty";
}
record.add(cellStringValue );
System.out.println(cellStr ingValue);
}
System.out.println("------ ---------- ---------- ---------- -------");
}
document.add(record);
}
}
catch (Exception e) {
e.printStackTrace();
}
return document;
}
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(FileInputS
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(booleanVa
} else if (cell.getCellType() == 0) {
double doubleValue = cell.getNumericCellValue()
cellStringValue = Double.toString(doubleValu
} else if (cell.getCellType() == 1) {
cellStringValue = cell.getStringCellValue();
}
} else {
cellStringValue = "Empty";
}
record.add(cellStringValue
System.out.println(cellStr
}
System.out.println("------
}
document.add(record);
}
}
catch (Exception e) {
e.printStackTrace();
}
return document;
}
I think you should add the following statement:
before:
document.add(record);
it should be:
document.add(record);
record.clear();//restart this arraylist!
before:
document.add(record);
it should be:
document.add(record);
record.clear();//restart this arraylist!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
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(dateform at.parse(" 27/11/2006 "));
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(dateform
why was my earlier comment that answered the question asked ignored?
for (short i=row.getFirstCellNum(); i<=row.getLastCellNum(); i++) {
HSSFCell cell = row.getCell(i);