CODEWITHSUNDEEP
javaapache-poi
Rajeev
Rajeev

Reputation: 559

How to open and save excel file in java

I have one excel file (test.xlsx) which contain a column, say id, in which I have applied a formula and when I open it manually, value of all ids changes automatically, because of it when I close it, it ask for save the changes.

same I have tried with java code as given below

public static void main(String[] args) {

        String str1 = "c:/file/test.xlsx";
        try {


            //open file
            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(str1)));
            System.out.println(wb.getSheetAt(0).getRow(1).getCell(0).getDateCellValue().toString());

            //save file
            FileOutputStream out = new FileOutputStream(str1);
            wb.write(out);
            out.close();

            wb = new XSSFWorkbook(new FileInputStream(new File(str1)));
            System.out.println(wb.getSheetAt(0).getRow(1).getCell(0).getDateCellValue().toString());

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

    }




output:
Thu Aug 04 01:08:34 IST 2016
Thu Aug 04 01:08:34 IST 2016

after executing above code I am not getting any exception

I have verified the result by reading file with help of apache POI.

I want, after saving the file with code new ids should be in Id column as it happening in case of manually open and saving file.

For me it is not working with code.

please suggest

If you are not able to understand the problem then create file like below and open it and close it manually, after that try with code, you will understand the things

enter image description here

Upvotes: 1

Views: 22630

Answers (1)

Rajeev
Rajeev

Reputation: 559

I have found the solution, in above code I have to add only one line, please check below

public static void main(String[] args) {

        String str1 = "c:/file/test.xlsx";
        try {


            //open file
            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(str1)));
            System.out.println(wb.getSheetAt(0).getRow(1).getCell(0).getDateCellValue().toString());

            //FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

            XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

            //save file
            FileOutputStream out = new FileOutputStream(str1);
            wb.write(out);
            out.close();

            wb = new XSSFWorkbook(new FileInputStream(new File(str1)));
            System.out.println(wb.getSheetAt(0).getRow(1).getCell(0).getDateCellValue().toString());

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

    }

Upvotes: 3

Related Questions