CODEWITHSUNDEEP
javaexcel
viderSelf
viderSelf

Reputation: 41

While writing to excel file it loses some data. Java

I am creating XLSX file and adding to cells some values, it writes that everything is OK, but when i read the file back or just open the file. I do not see all the data there.

File myfile = new File("hello.xlsx");
Workbook workBook = new XSSFWorkbook();
Sheet sheet = workBook.createSheet("sheet");
String[] tokens= {"A1", "B5", "C7", "J1", "K15", "Z20"};

    for (String tok: tokens) {
        CellReference ref = new CellReference(tok);
        Row row = sheet.createRow(ref.getRow());
        Cell cell = row.createCell((short) ref.getCol());
        cell.setCellValue("blabla");
        System.out.println(ref);
    }

workBook.write(new FileOutputStream(myfile));
workBook.close();

System.out.println();

FileInputStream fileInputStream = new FileInputStream(myfile);
Workbook workBook2 = new XSSFWorkbook(fileInputStream);
Sheet worksheet = workBook2.getSheet("sheet");

    for(String tok: tokens) {
        CellReference ref= new CellReference(tok);
        Row row = worksheet.getRow(ref.getRow());
        Cell cell = row.getCell(ref.getCol());
        if(cell != null){
           System.out.println(ref);
        }
    }

But when i read them back, i get only few of them. Why?

org.apache.poi.ss.util.CellReference [A1]
org.apache.poi.ss.util.CellReference [B5]
org.apache.poi.ss.util.CellReference [C7]
org.apache.poi.ss.util.CellReference [J1]
org.apache.poi.ss.util.CellReference [K15]
org.apache.poi.ss.util.CellReference [Z20]

org.apache.poi.ss.util.CellReference [B5]
org.apache.poi.ss.util.CellReference [C7]
org.apache.poi.ss.util.CellReference [J1]
org.apache.poi.ss.util.CellReference [K15]
org.apache.poi.ss.util.CellReference [Z20]

Upvotes: 0

Views: 443

Answers (2)

Axel Richter
Axel Richter

Reputation: 61915

You are creating the Row 1 (index 0) twice:

...
Row row = sheet.createRow(ref.getRow());
...

This will create a new row 0 once if ref refers to A1 and again if ref refers to J1. Both times a new empty row 0 is created. So the first created cell in that row will be lost.

You must check if the row already exists before creating a new one:

...
Row row = sheet.getRow(ref.getRow());
if (row == null) row = sheet.createRow(ref.getRow());
...

Upvotes: 1

nhouser9
nhouser9

Reputation: 6780

See this link:

http://poi.apache.org/apidocs/org/apache/poi/POIXMLDocument.html#close%28%29

From the documentation for write():

"Write out this document to an Outputstream. Note - if the Document was opened from a File rather than an InputStream, you must write out to a different file, overwriting via an OutputStream isn't possible."

Upvotes: 0

Related Questions