CODEWITHSUNDEEP
javaexcelapache-poi
user2883071
user2883071

Reputation: 980

Java prints only last cell of data

I am trying to read content from a csv and print it out to an excel file. So far, I have this, however it only prints out the last value of the csv into the first cell of the excel file. I tried using an increment for the column and row, but I get a null pointer exception error.

int i=0;
int j=0;
int k=0;
int row=68;
int column=0;
String [] part={"052","064"};
try{
    //InputStream inp = new FileInputStream();
    //Workbook wb = WorkbookFactory.create(inp);
    Workbook wb = WorkbookFactory.create(new File("30_results_w_graphs.xls"));


    for (k=0; k<part.length; k++)
    {
        Sheet sheet = wb.getSheet(part[k]);
            Cell cell = null;
        Scanner scanner = new Scanner(new File(part[k]+".csv"));
        scanner.useDelimiter(",");


        while(scanner.hasNext()){
            cell=sheet.getRow(row).getCell(column);
            cell.setCellValue(scanner.next());
            //row++;
            //column++;
            }
        FileOutputStream fileOut = new FileOutputStream("30_results_U_w_graphs.xls");
        wb.write(fileOut);
        scanner.close();
    }

}

I believe it is a nested for loop to increment the row and the column, but even in regular increments i get a null pointer exception.

Upvotes: 0

Views: 1748

Answers (2)

Udo Klimaschewski
Udo Klimaschewski

Reputation: 5315

I don't know what exactly you want to do, but this code shows some basic steps in reading a CSV file into an existing Excel file using Apache POI. Existing sheets/rows/cells will not be deleted.

The CSV file looks like this:

12,14,18,21
82,19,18,44
9,4,31,12

Read the file into a sheet named after the file name:

    File excel = new File("/tmp/result.xls");

    //Open existing Excel file:
    Workbook wb = WorkbookFactory.create(excel);

    //Create a new Excel file:
    //Workbook wb = new HSSFWorkbook();

    //Read in CSV
    String name = "test";
    Sheet sheet = wb.getSheet(name);
    if (sheet == null) {
        sheet = wb.createSheet(name);
    }

    int rowCount = 0;
    Scanner scanner = new Scanner(new File("/tmp/" + name + ".csv"));
    while (scanner.hasNextLine()) {
        String[] rowData = scanner.nextLine().split(",");
        for (int col = 0; col < rowData.length; col++) {
            Row row = sheet.getRow(rowCount);
            if (row == null)
                row = sheet.createRow(rowCount);
            Cell cell = row.getCell(col);
            if (cell == null) {
                cell = row.createCell(col);
            }
            cell.setCellValue(rowData[col]);
        }
        rowCount++;
    }

    wb.write(new FileOutputStream(excel));
}

Upvotes: 2

agad
agad

Reputation: 2189

You don't increase row nor column. Use createRow(int row) and createCell(int column) tor creating new cells.

Upvotes: 0

Related Questions