1

I have an excel file with DDE links to external sources. I am trying to read those values into my java application.

I am using Apache POI library to read the excel. When trying, I get the following error:

Exception in thread "main" java.lang.IllegalStateException: Cannot get a numeric value from a text cell
at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:643)
at org.apache.poi.hssf.usermodel.HSSFCell.getNumericCellValue(HSSFCell.java:668)
at BasketExcelRead.run(BasketExcelRead.java:40)
at Tester.main(Tester.java:7)

My code is

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

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class BasketExcelRead implements Runnable {

    public void run() {

        String inputFile = "someexcel.xls";

        List<String> code = new ArrayList<String>();
        List<Double> bid = new ArrayList<Double>();
        List<Double> share = new ArrayList<Double>();

        try {

            FileInputStream file = new FileInputStream(new File(inputFile));

            HSSFWorkbook workbook = new HSSFWorkbook(file);

            HSSFSheet sheet = workbook.getSheetAt(0);

            int ss = sheet.getPhysicalNumberOfRows();

            for (int i = 0; i < ss; i++) {

                HSSFRow row = sheet.getRow(i);
                HSSFCell cellCode = row.getCell(0);
                HSSFCell cellBid = row.getCell(7);
                HSSFCell cellShare = row.getCell(9);

                code.add(cellCode.getStringCellValue());
                bid.add(cellBid.getNumericCellValue());
                share.add(cellShare.getNumericCellValue());

            }

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            System.out.println("done reading");
        }


        for (int i = 0; i < 30; i++) {
            System.out.println(code.get(i)+" "+bid.get(i)+" "+share.get(i));
        }

    }
}

The cell that text/numeric value conflict happens at bid.add(cellBid.getNumericCellValue()); This cellBid is the DDE link within the excel file which shows some numeric values

EmreN
  • 11
  • 3
  • are you sure you got a numeric text in that cell? I recommend surrounding with try catch and see the values. – İsmet Alkan Dec 16 '14 at 09:25
  • 1
    You can find solution here- http://stackoverflow.com/questions/24529486/exception-in-thread-main-java-lang-illegalstateexception-cannot-get-a-text-va – SagarVimal Dec 16 '14 at 09:35
  • 'DDE' (dynamic data exchange) and 'from an Excel file' appear to be mutually exclusive. – user207421 Dec 16 '14 at 09:35
  • Thank you IsThatSo, try catch helped to extract some values but not the desired ones. When using try catch it gets the values previously saved when closing the excel. I think with try catch is looks into the xml format of excel to find th DDEs previously saved values. It does not however give the spot values which I am trying to achieve. Also I get a nullpointerexception with the try catch – EmreN Dec 16 '14 at 09:39

0 Answers0