[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

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;

    }

---------------------------------------------------------------------------------------------------------------------------------
0
royalcyber
Asked:
royalcyber
  • 6
  • 6
  • 4
1 Solution
 
objectsCommented:
try:

for (short i=row.getFirstCellNum(); i<=row.getLastCellNum(); i++) {
   HSSFCell cell = row.getCell(i);
0
 
royalcyberAuthor Commented:
it gives a  null pointer exception

0
 
royalcyberAuthor Commented:
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;

   }
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
objectsCommented:
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);
                  }
            }
0
 
royalcyberAuthor Commented:
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("--------------------------------------------");
                        }
                           
                        }
                    }
0
 
phuocnhCommented:
Hi royalcyber!
Because of my timzone, I can't continue stay with u last night. I gave u solution in this thread
http://www.experts-exchange.com/Programming/Programming_Languages/Java/Q_21859847.html
Why don't you read it?

0
 
phuocnhCommented:
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

0
 
phuocnhCommented:
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
0
 
objectsCommented:
output the contents of Document and see whats in it:

System.out.println(document);
0
 
royalcyberAuthor Commented:
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;

    }
0
 
phuocnhCommented:
I think you should add the following statement:
before:
  document.add(record);
it should be:
 document.add(record);
record.clear();//restart this arraylist!
0
 
phuocnhCommented:
I am sorry I have make a mistake
It should be
document.add(record.clone());
record.clear();
0
 
royalcyberAuthor Commented:
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
0
 
royalcyberAuthor Commented:
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
0
 
phuocnhCommented:
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"));
0
 
objectsCommented:
why was my earlier comment that answered the question asked ignored?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now