Java. HSSF. Apache-poi. How to modificate code

Question

My data is stored in a format(look down): [-] means a blank cell, on the right may be only 10 columns, after the space. Something like this:
[string0] [-] [string1] [string2] [string3] .. [string10] [-]

How to change this code for:

1) obtain only [string0]

2) obtain only [string1] [string2] [string3] .. [string10] [-]

    try {

    FileInputStream file = new FileInputStream("C:\\Users\\student3\\"+sfilename+".xls");

    //Get the workbook instance for XLS file
    HSSFWorkbook workbook = new HSSFWorkbook(file);

    //Get first sheet from the workbook
    HSSFSheet sheet = workbook.getSheetAt(0);

    //Iterate through each rows from first sheet
    Iterator<Row> rowIterator = sheet.iterator();
    while(rowIterator.hasNext()) {
        Row row = rowIterator.next();

        //For each row, iterate through each columns
        Iterator<Cell> cellIterator = row.cellIterator();
        while(cellIterator.hasNext()) {

            Cell cell = cellIterator.next();

            switch(cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    list1.add(cell.getStringCellValue());
                    break;
            }
        }
        System.out.println("");
    }
    file.close();
    FileOutputStream out =
            new FileOutputStream("C:\\Users\\student3\\"+sfilename+".xls");
    workbook.write(out);
    out.close();

I don't know how to stop Iterator. He absorbs all..

Sankumarsingh · Accepted Answer · 2013-08-07T08:25:50.057

1

If I am clear you just want to filter your first column string and rest seperately.

Why not you just use a simple counter for this:

 while(rowIterator.hasNext()) {
    Row row = rowIterator.next();
    String RowContent = null;
    Iterator<Cell> cellIterator = row.cellIterator();
    while(cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        RowContent=RowContent+cell.toString();
    }
    //Code for saving RowContent or printing or whatever you want for text in complete row
}

RowContent will give concatenation of each cells of a single row in each iteration.

edited Aug 07 '13 at 08:25

answered Aug 06 '13 at 16:53

Sankumarsingh

9,889
11
50
74

do you know how to implement concat columns into row and then convert row into string for each row – Eldar Nezametdinov Aug 07 '13 at 07:36
@EldarNezametdinov: I have updated the code. At the end of each row iterator, `Rowcontent` will give you complete row content after concatenation. Is this satisfying your requirement? – Sankumarsingh Aug 07 '13 at 08:27
You already done enough) I rewrote "while" in "the for statement" and now it works great, unfortunately i still don't know how to concat strings in cycles. I will try your RowContent) Thank you a lot! – Eldar Nezametdinov Aug 07 '13 at 08:33

score 0 · Answer 2 · answered Aug 06 '13 at 14:14

Like you did in the switch block with "break". But what i think you want is this:

Iterator<Cell> cellIterator = row.cellIterator();
boolean stop = false;
while(cellIterator.hasNext()) {

  Cell cell = cellIterator.next();

  switch(cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
      System.out.print(cell.getStringCellValue() + "\t\t");
      list1.add(cell.getStringCellValue());
      stop = true;
      break;
  }

  if (stop) {
    break;
  }
}

This would stop the while loop when you found a string cell and will then operate on the next row. Make any possible condition you need to break the while loop. For example collect string columns and when you found the desired set stop to true, to get to the next row.

Be carefully with the POI HSSF stuff when working with huge Excel sheets. I have a running desktop app using POI, which handles sheets with several thousands of rows. This sheets could not access by the normal API of POI. In this case i used a trick by defining xlsx as default format and extracting the xml portion i need to work with. — Rene M., Aug 06 '13 at 14:18

axiopisty · Answer 3 · 2013-08-07T06:28:29.160

External Link: Busy Developers' Guide to HSSF and XSSF Features

Here is an example that should work.

Maven Dependencies:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

Code:

import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

public class StackOverflowQuestion18095443 {

    public static void main(String[] args) {
        if(args.length != 1) {
            System.out.println("Please specify the file name as a parameter");
            System.exit(-1);
        }
        String sfilename = args[0];
        File file = new File("C:\\Users\\student3\\" + sfilename + ".xls");
        read(file);
    }

    public static void read(File file) {
        try (InputStream in = new FileInputStream(file)) {
            HSSFDataFormatter formatter = new HSSFDataFormatter();
            Workbook workbook = WorkbookFactory.create(in);
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                StringBuilder rowText = new StringBuilder();
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    String cellAsStringValue = formatter.formatCellValue(cell);
                    rowText.append(cellAsStringValue).append(" ");
                }
                System.out.println(rowText.toString().trim());
            }
        } catch (InvalidFormatException | IOException e) {
            e.printStackTrace();
        }
    }
}

As for terminating the iteration you can conditionally break from the loop. Or, you could also just not use an iterator. Notice that you can obtain a Cell from a Row using a named reference (this allows you to refer to a cell by name, such as "A2", just like you would in Excel) or simply by the column index within the row.

Could you rewrite it with "the for statement" . Start below while (rowIterator.hasNext()) {. And. How do I obtain full string, for EACH row fullString = [-] [string1] [string2] [string3] .. [string10] [-]. Using StringBuilder? Or how? — Eldar Nezametdinov, Aug 07 '13 at 07:50

Java. HSSF. Apache-poi. How to modificate code

3 Answers3

Linked

Related