CODEWITHSUNDEEP
javaexceleclipse
Juke
Juke

Reputation: 1426

Excel Apache POI is printing only last element while performing setCellValue()

I am reading some data from excel and writing it to another sheet in the same file in the form of column and I want the column to be printed in the row. When I am performing iteration createRow().setCellValue() is printing only last element.

package com.editDistance;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadColumnsEditDistance {

	public static void main(String[] args) throws IOException {
		// TODO Auto-generated method stub

		File src = new File("C:\\Users\\xyz\\Desktop\\folder\\file.xlsx");
		FileInputStream file = new FileInputStream(src);
		Workbook workbook = new XSSFWorkbook(file);
		Sheet sheet1 = workbook.getSheetAt(0);
		int rows = sheet1.getPhysicalNumberOfRows();
		workbook.createSheet();
		Sheet sheet2 = workbook.getSheetAt(1);
		for (int Readingrowindex = 1; Readingrowindex < rows; Readingrowindex++) {

			String ah = sheet1.getRow(Readingrowindex).getCell(1).getStringCellValue();
			sheet2.createRow(Readingrowindex).createCell(0).setCellValue(ah);
			sheet2.createRow(0).createCell(Readingrowindex).setCellValue(ah);

		}

		FileOutputStream fout = new FileOutputStream(src);
		workbook.write(fout);
	}
}

enter image description here

Upvotes: 0

Views: 592

Answers (3)

user13758235
user13758235

Reputation: 1

I faced the similar problem while writing to the code (it was printing the last row only).

Here is the solution for my code:

Failed solution (printing only last cell) i.e. "Hello3"

                    sheet.createRow(8).createCell(0).setCellValue("Hello1");
                    sheet.createRow(8).createCell(1).setCellValue("Hello2");
                    sheet.createRow(8).createCell(2).setCellValue("Hello3");

Working solution (Printing all the data) :

        XSSFRow row;
        row = sheet.createRow(8);
        row.createCell(0).setCellValue("India 123");
        row.createCell(1).setCellValue("India 234");
        row.createCell(2).setCellValue("India 3453");

        row = sheet.createRow(9);
        row.createCell(0).setCellValue("Test 123");
        row.createCell(1).setCellValue("Test 234");
        row.createCell(2).setCellValue("Test 3453");

Upvotes: -1

amedv
amedv

Reputation: 388

Do you want to create sheet with the same cells in first column and first row, like a named table? If yes, you need modify your code, because you always rewrite first row, that's why you get only last item. Do this:

    File src = new File("C:\\Users\\xyz\\Desktop\\folder\\file.xlsx");
    FileInputStream file = new FileInputStream(src);
    Workbook workbook = new XSSFWorkbook(file);
    Sheet sheet1 = workbook.getSheetAt(0);
    int rows = sheet1.getPhysicalNumberOfRows();
    workbook.createSheet();
    Sheet sheet2 = workbook.getSheetAt(1);
    Row sheet2FirstRow = sheet2.createRow(0);
    for (int Readingrowindex = 1; Readingrowindex < rows; Readingrowindex++) {

        String ah = sheet1.getRow(Readingrowindex).getCell(1).getStringCellValue();
        sheet2.createRow(Readingrowindex).createCell(0).setCellValue(ah);
        Cell cell = sheet2FirstRow.createCell(Readingrowindex);
        cell.setCellValue((String) ah);

    }

    FileOutputStream fout = new FileOutputStream(src);
    workbook.write(fout);

Upvotes: 1

Justin
Justin

Reputation: 1356

I think it's because you keep creating row 0 over and over again so it's overwriting what you already created.

You probably want to do something like this:

if(sheet2.getRow() == null){
    sheet2.createRow(0).createCell(Readingrowindex).setCellValue(ah);
}else{
    sheet2.getRow(0).createCell(Readingrowindex).setCellValue(ah);
}

Upvotes: 1

Related Questions