Java - append excel

Question

i need to append excel. I write code:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import javafx.collections.FXCollections;
import javafx.collections.ObservableList;

    public class AppendExcel {



public static void main(String[] args) throws Exception {
    String excelFilePath = "append.xlsx";

    try {
        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
        Workbook workbook = WorkbookFactory.create(inputStream);

        Sheet sheet = workbook.getSheetAt(0);

        DbManagerSQL test = new DbManagerSQL();
        String sql = "select distinct Code,Item,Description from MyTable";

        Statement stmt = test.dbConnect().createStatement();
        ResultSet rs = stmt.executeQuery(sql);


        int rowCount = sheet.getLastRowNum();
        while (rs.next()) {


            Row row = sheet.createRow(++rowCount);

            int columnCount = 0;

            Cell cell = row.createCell(columnCount);
            cell.setCellValue(rowCount);

            cell = row.createCell(++columnCount);
            cell.setCellValue((String) rs.getString(1));
            cell.setCellValue((String) rs.getString(2));
            cell.setCellValue((String) rs.getString(3));

        }

        inputStream.close();

        FileOutputStream outputStream = new FileOutputStream("append.xlsx");
        workbook.write(outputStream);
        // workbook.close();
        outputStream.close();

    } catch (IOException | EncryptedDocumentException | InvalidFormatException ex) {
        ex.printStackTrace();
    }
}

}

My Excel has the column

but when I append Excel I got next

It looks like remember only the last column. I think the prblem is in this line of code

cell = row.createCell(++columnCount);

but i don't now how to add data to get excel like this

Any idea???

Phil · Accepted Answer · 2017-06-06 09:39:08Z

1

Create a cell for every entry:

int columnCount = 0;

Cell cell = row.createCell(columnCount);
cell.setCellValue(rowCount);
columnCount++;

cell = row.createCell(columnCount);
cell.setCellValue((String) rs.getString(1));
columnCount++;

cell = row.createCell(columnCount);
cell.setCellValue((String) rs.getString(2));
columnCount++;

cell = row.createCell(columnCount);
cell.setCellValue((String) rs.getString(3));

answered Jun 6, 2017 at 9:39

Phil

3061 silver badge8 bronze badges

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

Comments

Turtle · Accepted Answer · 2017-06-06 09:36:12Z

0

You increment your columnCount in the while:

int columnCount = 0;
cell = row.createCell(++columnCount);

So you always write in the same cell. You must put the initialization out of the wile.

answered Jun 6, 2017 at 9:36

Turtle

1,68118 silver badges28 bronze badges

Collectives™ on Stack Overflow

Java - append excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related