Mailing-List: contact user-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <user@poi.apache.org>
Date: Mon, 10 Jul 2017 13:23:58 -0700 (MST)
From: Hehabr <hehabr@web.de>
To: user@poi.apache.org
Message-ID: <1499718238841-5728177.post@n5.nabble.com>
In-Reply-To: <B082877D-1345-4F50-A8F8-6D814732D9A5@gmail.com>
References: <1499614367054-5728112.post@n5.nabble.com> <CAM+TppLpjG1ad_kH_jHEdmHBQL5cLta9mraDNr=kbsMdg8ZS3w@mail.gmail.com> <D003196C-C07E-497A-9EAB-6319FA177ADE@gmail.com> <B082877D-1345-4F50-A8F8-6D814732D9A5@gmail.com>
Subject: Re: Apache POI : Problem with Excel updating
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
archived-at: Mon, 10 Jul 2017 20:48:44 -0000

package poi.service;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;

public class POITestEva {

    private String excelFilePath = "C:/Test/1.xlsm";
    private FileInputStream inputStream;
    private XSSFWorkbook workbook;

    public static void main(String[] args) {
        POITestEva pOITestEva = new POITestEva();
        pOITestEva.updateCell(3.0);
        System.out.println("D5 = " + pOITestEva.readCellTest("D5"));		//
Line 23
    }


    public void updateCell(Double newData) {
        try {
            File excel = new File(excelFilePath);
            inputStream = new FileInputStream(excel);
            workbook = new XSSFWorkbook(inputStream);
            workbook.setForceFormulaRecalculation(true);

            Cell cell = getCell(1, "C8");
            if (cell != null) {
                cell.setCellValue(newData);
            }

           
workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
            OutputStream output = new FileOutputStream(excel);
            workbook.write(output);
            output.flush();
            output.close();
            workbook.close();
            inputStream.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }


    private Cell getCell(int sheetNr, String cellId) {
        CellReference ref = new CellReference(cellId);
        return getCell(sheetNr, ref.getCol(), ref.getRow());
    }

    private Cell getCell(int sheetNr, int col, int row) {
        XSSFSheet sheet = workbook.getSheetAt(sheetNr);
        if (sheet.getRow(row) != null
                && sheet.getRow(row).getCell(col) != null
                && !(sheet.getRow(row).getCell(col).getCellType() ==
Cell.CELL_TYPE_BLANK)) {
            return sheet.getRow(row).getCell(col);
        }
        return null;
    }


    public Double readCellTest(String cellId) {
        try {
            File excel = new File(excelFilePath);
            inputStream = new FileInputStream(excel);
            workbook = new XSSFWorkbook(inputStream);
            Double result = ( (Double) (readCell(cellId)) );			// Line 74
            if (workbook != null) {
                workbook.close();
            }
            if (inputStream != null) {
                workbook.close();
            }
            return result;
        }
        catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    private Object readCell(String cellId) {
        Cell cell = getCell(1, cellId);
        return handleCell(cell.getCellType(), cell);					// Line 91
    }


    @SuppressWarnings("deprecation")
    private Object handleCell(int type, Cell cell) {
        switch (type) {
            case XSSFCell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case XSSFCell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue();
            case XSSFCell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();
            case XSSFCell.CELL_TYPE_BLANK:
                return null;
            case XSSFCell.CELL_TYPE_ERROR:
                return null;
            case XSSFCell.CELL_TYPE_FORMULA:
                return cell.getNumericCellValue();					// Line 109
            default:
                return null;
        }
    }

}

-------------------------------------------------------------------------

"C:\Program Files\Java\jdk1.8.0_51\bin\java" -Didea.launcher.port=... 
...java.lang.IllegalStateException: Cannot get a NUMERIC value from a STRING
cell
D5 = null
	at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1050)
	at
org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:310)
	at quicc.headliner.service.POITestEva.handleCell(POITestEva.java:109)
	at quicc.headliner.service.POITestEva.readCell(POITestEva.java:91)
	at quicc.headliner.service.POITestEva.readCellTest(POITestEva.java:74)
	at quicc.headliner.service.POITestEva.main(POITestEva.java:23)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)

Process finished with exit code 0


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Problem-with-Excel-updating-tp5728112p5728177.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org