Overwriting an excel file using Java POI

Question

I am new to Java POI and i am trying to overwrite an excel file using Java POI.Let me make it clear, i don't want to open a new .xls file every time time i build the code however the code i wrote does it that way.The purpose for this is to, i will build the chart on excel and read the values for the chart from the database and write it to the excel file by using Java POI.Here is my code:

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet firstSheet = workbook.createSheet("oldu");
HSSFSheet secondSheet = workbook.createSheet("oldu2");

HSSFRow rowA = firstSheet.createRow(6);
HSSFCell cellA = rowA.createCell(3);
cellA.setCellValue(new HSSFRichTextString("100"));
cellA.setCellValue(100);

HSSFRow rowB = secondSheet.createRow(0);
HSSFCell cellB = rowB.createCell(0);
cellB.setCellValue(new HSSFRichTextString("200"));

FileOutputStream fos = null;
try {
    fos = new FileOutputStream(new File("CreateExcelDemo.xls"));
    workbook.write(fos);
} catch (IOException e) {
    e.printStackTrace();
} finally {
    if (fos != null) {
        try {
            fos.flush();
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

So you want to open an existing excel file and modify/add more data to it? — ChadNC, Jun 13 '12 at 11:24
I think, it already overwrites the excel file. What is wrong with this one? — small_ticket, Jun 13 '12 at 11:25
Yes ChadNC.For this one,i put the chart on it and close then try to give a cell a value it opens new page i guess because there is no chart in the file that Java produces. — user1431829, Jun 13 '12 at 11:28
So your problem is you don't know how to open a workbook with POI? I think you either need HSSFWorkbook's constructor that accepts an InputStream, or WorkbookFactory. — Rup, Jun 13 '12 at 11:32
You can also find some questions in here like http://stackoverflow.com/questions/521274/edit-existing-excel-files-using-jxl-api-apache-poi — small_ticket, Jun 13 '12 at 11:35

score 1 · Accepted Answer · edited Jun 13 '12 at 11:36

1

Always use the same filename and when you run the program it will overwrite the file.

EDIT If you want to modify an existing excel file then have a look HERE and scroll down to the section on "Reading or Modifying an existing file".

edited Jun 13 '12 at 11:36

Rup

33,765
9
83
112

answered Jun 13 '12 at 11:28

ChadNC

2,528
4
25
39

It doesnt do that.As i mentioned before when i try to do that, it doesnt show the chart that i created manually before editing it with java – user1431829 Jun 13 '12 at 11:29
2

then your question is incorrect since overwriting the file is what you asked and overwriting the file is what your code does. What you want is to modify and existing excel form not overwrite one. – ChadNC Jun 13 '12 at 11:31

score 0 · Answer 2 · answered Jun 13 '12 at 11:41

The problem is Apache POI doesn't support all features of Excel file format, including charts. So can not generate a chart with POI and when opening an existing Excel file with POI and modifying it, some of the current "objects" in the Excel file could be lost, as POI can't handle it and when writing, writes only the new information generated and existing one that can handle.

This is assumed by Apache as one of the flaws of POI.

We done similar processing of existing Excel file, filling new data onto it, but the existing Excel file contains only formatting styles and they are preserved using POI, but I think charts are very problematic. Trying ti filling data to update an existing chart is not possible with POI.

Since the chart was created manually, not using POI, you can modify the chart data using HSSF and write it to a new spreadsheet using POI and POI will attempt to keep existing records intact. Doing so could be problematic but it is possible. — ChadNC, Jun 13 '12 at 11:52
But the problem is that i cant see the chart that i created manually after i built java — user1431829, Jun 13 '12 at 12:13

score 0 · Answer 3 · edited Mar 19 '18 at 08:09

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.util.List;

public class WriteExcel {
    public static void writeFile1(List dataList , String path , int columnCount,int forwardIndex)  throws InvalidFormatException, IOException {

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

            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = 1;

            for ( int i = 0+forwardIndex ; i < dataList.size(); i++) {
                Row row = sheet.createRow(++rowCount);
              //  int columnCount = 0;

                Cell cell = row.createCell(columnCount);
                cell.setCellValue((String) dataList.get(i));
            }

            inputStream.close();

            FileOutputStream outputStream = new FileOutputStream(path);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();

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

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.util.List;

public class WriteExcel {
    public static void writeFile1(List dataList , String path , int columnCount,int forwardIndex)  throws InvalidFormatException, IOException {

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

            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = 1;

            for ( int i = 0+forwardIndex ; i < dataList.size(); i++) {
                Row row = sheet.createRow(++rowCount);
              //  int columnCount = 0;

                Cell cell = row.createCell(columnCount);
                cell.setCellValue((String) dataList.get(i));
            }

            inputStream.close();

            FileOutputStream outputStream = new FileOutputStream(path);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();

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

score 0 · Answer 4 · answered Mar 19 '18 at 07:23

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

public class UserInput {

    public static Map getUserInput() throws InvalidFormatException, IOException {
        String userName = System.getProperty("user.name");
        String path = "";
        int indexofColumn = 10;
        Map inputFromExcel = new HashMap();
        InputStream inp = new FileInputStream(path);
        int ctr = 4;
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0); // Mention Sheet no. which you want to read
        Row row = null;
        Cell cell = null;
            try{
                row = sheet.getRow(ctr);
                cell = row.getCell(indexofColumn); //Mention column no. which you want to read
                inputFromExcel.put("NBKID",cell.toString()) ;
                row = sheet.getRow(ctr+1);
                cell = row.getCell(indexofColumn);
                inputFromExcel.put("Password", cell.toString());
                row = sheet.getRow(ctr+2);
                cell = row.getCell(indexofColumn);
                inputFromExcel.put("NBKIDEmail",cell.toString());
                row = sheet.getRow(ctr+3);
                cell = row.getCell(indexofColumn);
                inputFromExcel.put("sourceExcel" ,cell.toString());

            } catch(Exception e) {
            }

        return inputFromExcel;
    }

}

Try to explain your answer, why you suggest this code? – Mohammad Kanan Mar 19 '18 at 07:46 — Mohammad Kanan, Mar 19 '18 at 07:46

score 0 · Answer 5 · answered Mar 19 '18 at 07:28

import java.util;

public class Main {
    public static void main(String[] args) {
        List<String> partyIdList = new ArrayList<String>();
        List<String> phNoList = new ArrayList<String>();
        String userName = System.getProperty("user.name");
        String path = "";
            try {
                Map data = new HashMap(UserInput.getUserInput());
                partyIdList = ReadExcel.getContentFromExcelSheet(data.get("sourceExcel").toString(),0);
                phNoList = ReadExcel.getContentFromExcelSheet(data.get("sourceExcel").toString(),1);
                WriteExcel.writeFile1(partyIdList, path,0,1);
                WriteExcel.writeFile1(phNoList,path,1,0);

            } catch (Exception e) {
                throw new RuntimeException("Error while read excel Sheet" + e);
            }

    }
}

score -1 · Answer 6 · answered Mar 19 '18 at 09:22

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

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;

public class ReadExcel {
    public static List getContentFromExcelSheet(String path ,int indexofColumn) throws InvalidFormatException, IOException {
        //System.out.println(path);
        List<String> ItemList = new ArrayList();
        InputStream inp = new FileInputStream(path);
        int ctr = 0;
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0); // Mention Sheet no. which you want to read
        Row row = null;
        Cell cell = null;
        boolean isNull = false;
        do{
            try{
                row = sheet.getRow(ctr);
                cell = row.getCell(indexofColumn); //Mention column no. which you want to read
                ItemList.add(cell.toString());
                //   System.out.println(cell.toString());
                ctr++;
            } catch(Exception e) {
                isNull = true;
            }

        }while(isNull!=true);
        inp.close();

        return ItemList;
    }
}

Overwriting an excel file using Java POI

6 Answers6