Problems Read / Write Excel File In Java Using Apache POI

Question

Hello guys i am a newbie on java poi library and i was trying all my luck to learn this library but still no luck

I would like to have this output

Excel1.xls has this data

ZIP CODE | PLACE | DATE
211

and I want to copy the all the first row data.

ZIP CODE | PLACE | DATE

and place it to another sheet

This is the code that i have made

public static void main(String[] args) throws IOException{

    try {
        FileInputStream file = new FileInputStream(new File("d:\\input.xls"));

        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheetAt(0);
        HSSFSheet zip1 = workbook.createSheet("ZIP CODE 1");


        for(Row row : sheet){
            int i=0;

            for(Cell cell : row){

                cell.setCellType(Cell.CELL_TYPE_STRING);
                System.out.print(cell.getStringCellValue() + "\t");
                String a = cell.getStringCellValue();

                cell = zip1.createRow(i).createCell(i);

                i++;
                cell.setCellValue(a);
             }
             break;

         }

        file.close();
        FileOutputStream outFile =new FileOutputStream(new File("d:\\output.xls"));
        workbook.write(outFile);
        outFile.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Sully · Accepted Answer · 2014-06-15 10:56:02Z

3

Issues found

For .xlsx files -> HSSFWorkbook should be XSSFWorkbook
Loop. You do not want to loop every row you only want the first one. Just loop the columns
Do not create a row everytime you want to write to a cell. Create a new row only once.

Working Example:

try {
    FileInputStream file = new FileInputStream(new File(
            "C:\\path\\Book1.xlsx"));

    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFSheet zip1 = workbook.createSheet("ZIP CODE 1");

    Row readFirstRow = sheet.getRow(0);
    Row writeFirstRow = zip1.createRow(0);

    for (Cell cell : readFirstRow) {

        cell.setCellType(Cell.CELL_TYPE_STRING);
        String a = cell.getStringCellValue();

        cell = writeFirstRow.createCell(cell.getColumnIndex());
        cell.setCellValue(a);
    }

    file.close();
    FileOutputStream outFile = new FileOutputStream(new File(
            "C:\\path\\BookOut.xlsx"));
    workbook.write(outFile);
    outFile.close();

} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}

edited Jun 15, 2014 at 10:56

answered Jun 15, 2014 at 9:38

Sully

15k5 gold badges56 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gy30n_W00 Over a year ago

Thank you hitham !:D its look so easy on your codes ^_^ i hope i can be good on programming just like you thank you again ^_^

Ran Adler · Accepted Answer · 2014-12-23 08:23:12Z

0

Try to use Xcelite
https://github.com/eBay/xcelite#writing

Write:

public class User { 

  @Column (name="Firstname")
  private String firstName;

  @Column (name="Lastname")
  private String lastName;

  @Column
  private long id; 

  @Column
  private Date birthDate; 
}

Xcelite xcelite = new Xcelite();    
XceliteSheet sheet = xcelite.createSheet("users");
SheetWriter<User> writer = sheet.getBeanWriter(User.class);
List<User> users = new ArrayList<User>();
// ...fill up users
writer.write(users); 
xcelite.write(new File("users_doc.xlsx"));

answered Dec 23, 2014 at 8:23

Ran Adler

3,75732 silver badges28 bronze badges

Collectives™ on Stack Overflow

Problems Read / Write Excel File In Java Using Apache POI

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related