java.lang.IllegalStateException: Cannot get a text value from a numeric formula cell

Question

I am using POIFSFileSystem and HSSFWorkbook to read my excel and upload it to my db.i have declared all the var in my pgm as string and also i have formatted my excel cells to text..Even then I am getting "java.lang.IllegalStateException: Cannot get a text value from a numeric formula cell". The cell type is showing as numeric but I formatted the cells to text.Any solution guys??

here is my code:

/*
 * FarmerQueryMigration.java
 * Copyright (c) 2014-2015, SourceTrace Systems, All Rights Reserved.
 *
 * This software is the confidential and proprietary information of SourceTrace Systems
 * ("Confidential Information"). You shall not disclose such Confidential Information and shall use
 * it only in accordance with the terms of the license agreement you entered into with
 * SourceTrace Systems.
 */
package com.ese.view.profile;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.ese.util.DateUtil;
import com.ese.util.ObjectUtil;
import com.ese.util.StringUtil;

public class FarmerQueryMigration {

    private static final SimpleDateFormat fileNameDateFormat = new SimpleDateFormat("dd-MM-yyyy");

    /**
     * The main method.
     * @param args the arguments
     * @throws IOException Signals that an I/O exception has occurred.
     */
    @SuppressWarnings({ "deprecation", "deprecation" })
    public static void main(String args[]) throws IOException {


        String fileName = "E:\\viji\\proj docs\\aditi upload files\\Bajolga Provisional Approved Farmers List.XLS";
        FileInputStream myInput = new FileInputStream(fileName);
        FileOutputStream myOutput = new FileOutputStream(
                "E:\\viji\\proj docs\\aditi upload files\\FarmerInsertQuery_"
                + fileNameDateFormat.format(new Date()) + ".sql");
        ByteArrayOutputStream baos = new ByteArrayOutputStream();

        POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
        HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
        HSSFSheet mySheet = myWorkBook.getSheetAt(1);

        StringBuilder sb = new StringBuilder();
        String initialQuery = "INSERT INTO FARMER VALUES(null,\"";
        String accountInsertQuery = "INSERT INTO ESE_ACCOUNT VALUES (NULL,\"22";
        String contractQuery = "INSERT INTO CONTRACT VALUES (NULL,\"";
        String cardInsertQuery = "INSERT INTO ESE_CARD VALUES (NULL,\"12";
        String contarctPricePatternQuery = "INSERT INTO CONTRACT_PRICEPATTERN_MAP VALUES ('";
        String nullString = null;
        int farmerSeq = 100;
        int accountSeq = 103;
        int contractSeq = 103;
        int contractPriceSeq = 103;
        int cardSeq = 103;

        int i = 3;
        int rowCount = mySheet.getLastRowNum();

        /*  String test = null;
        String test1 = null;
        while (i <=0) {

            HSSFRow myRow = mySheet.getRow(i);  


         // if(i==rowCount){
              System.out.println("----------ROW_NO:" + i + "-----------");
                String firstName = !StringUtil.isEmpty(myRow.getCell(0).getStringCellValue()) ? String
                       .valueOf((int) myRow.getCell(0).getNumericCellValue()) : nullString;

                test = !ObjectUtil.isEmpty(myRow.getCell(1)) ? String
                        .valueOf((int) myRow.getCell(1).getNumericCellValue()) : nullString;

                test1 = !ObjectUtil.isEmpty(myRow.getCell(2)) ? String
                                .valueOf((int) myRow.getCell(2).getNumericCellValue()) : nullString;
            //}

            System.out.println("---------" +  String
                    .valueOf(myRow.getCell(0).getStringCellValue()) + "-----------");

            if(!ObjectUtil.isEmpty(myRow.getCell(1))&&!ObjectUtil.isEmpty(myRow.getCell(2))){
              System.out.println(myRow.getCell(1).getStringCellValue());
            System.out.println(myRow.getCell(2).getStringCellValue());
      }
      else{
          System.out.println("----------Else" + i + "-----------");
      }

            //System.out.println("----------ROW_NO:" + i + "-----------");
         i++;   
        }
       */
        String customerProjectQuery="INSERT INTO `customer_project` VALUES ('22', '00320002', 'Chitradurga', '1', '1', '1', '1', 'Chitradurga', '0', '0', null, '1', '1', '32', '5', '0', '20150210184539');";

        sb.append(customerProjectQuery+"\n");
        try {

            while (i <= rowCount) {

                HSSFRow myRow = mySheet.getRow(i);
                String farmerId = getExact(String.valueOf(farmerSeq++), 6);
                String farmerCode = null;//myRow.getCell(0).getStringCellValue().trim();
                String firstName = myRow.getCell(1).getStringCellValue().trim();
                String lastName= null;
                String fatherName =myRow.getCell(2).getStringCellValue().trim();

                String gender = String.valueOf(myRow.getCell(3).getStringCellValue().trim()).toUpperCase();

                Date dob =null;

               // Date doj = !StringUtil.isEmpty(myRow.getCell(4).getStringCellValue().trim()) ? myRow.getCell(4) .getDateCellValue() : null;

                String dojString = "2015-05-21";
               /* if (!StringUtil.isEmpty(myRow.getCell(5).getStringCellValue().trim())) {
                   // dojString = DateUtil.convertDateToString(doj, DateUtil.TXN_DATE_TIME);
                }*/


                /* Date dob = !StringUtil.isEmpty(myRow.getCell(3)) ? myRow.getCell(3)
                        .getDateCellValue() : null;

                String dobString = null;
               if (!StringUtil.isEmpty(dob)) {
                    dobString = DateUtil.convertDateToString(dob, DateUtil.TXN_DATE_TIME);
                }*/


               String noOfFamilyMembers = null;

               if(!ObjectUtil.isEmpty(myRow.getCell(29))){
                   noOfFamilyMembers=  myRow.getCell(29).getStringCellValue();
               }

               String imgInfo=null;

                String address = !StringUtil.isEmpty(myRow.getCell(7)) ? myRow.getCell(7)
                        .getStringCellValue().trim() : null;                       


                String cityName = myRow.getCell(11).getStringCellValue().trim();
                String villageName = myRow.getCell(13).getStringCellValue().trim();

                String gramPanchayatName = myRow.getCell(14).getStringCellValue().trim();                

                String pincode = null;
                if(!ObjectUtil.isEmpty(myRow.getCell(27))){
                    pincode=  myRow.getCell(27).getStringCellValue();
                }
                String postOffice = null;//myRow.getCell(18).getStringCellValue().trim();
                String phoneNumber = null;
                String mobileNumber= "";
                if(!ObjectUtil.isEmpty(myRow.getCell(28))){
                    mobileNumber=  myRow.getCell(28).getStringCellValue();
                }

                String eMail = myRow.getCell(17).getStringCellValue();
                long revisionNumber=DateUtil.getRevisionNumber();
                String latitude=null;
                String longtitude=null;
                String photoCaptTime=null;

                String status="1";

                String samithiName=myRow.getCell(26).getStringCellValue().trim();
                String projectSubGroupName=myRow.getCell(26).getStringCellValue().trim();

                String cetificationStandard="5";
                String cetificationLevel="0";
                String cetificationType="2";

                String martialStatus = "0";//myRow.getCell(19).getStringCellValue().trim();

                String education = myRow.getCell(20).getStringCellValue().trim();
                if(education.equalsIgnoreCase("No")){
                    education="0";
                }else if(education.equalsIgnoreCase("2th") || education.equalsIgnoreCase("3rd")|| education.equalsIgnoreCase("3th")||education.equalsIgnoreCase("4th")||education.equalsIgnoreCase("5th")){
                    education="1";
                }else if(education.equalsIgnoreCase("6th") || education.equalsIgnoreCase("7th")||education.equalsIgnoreCase("8th")||education.equalsIgnoreCase("5th")){
                    education="2";
                }
                else if(education.equalsIgnoreCase("SSLC") || education.equalsIgnoreCase("PUC")||education.equalsIgnoreCase("P U C")){
                    education="3";
                }

                String childCount =myRow.getCell(30).getStringCellValue();  
                if(childCount.equalsIgnoreCase("-")){
                    childCount="0";
                }
                String childCount1 = "0";
                String childCount2 = "0";
                String inspectionType="0";
                String icsStatus="3";

                String CustomerProjectName=  myRow.getCell(15).getStringCellValue().trim();  
                String farmerEconomyId = null;
                String statusCode="0";
                String statusMsg="SUCCCESS";

                sb.append(initialQuery);
                sb.append(farmerId);
                sb.append("\",");
                sb.append(farmerCode+",");                               
                sb.append("'"+firstName+"',");
                sb.append(lastName+",");
                sb.append("\""+fatherName+"\",");  
                sb.append("\""+gender+"\",");
                sb.append("NULL,"+"'");
                sb.append(dojString+"','");
                sb.append(noOfFamilyMembers+"',");
                sb.append("NULL,"+"\"");
                sb.append(address+"\",");
                sb.append("(SELECT ID FROM CITY WHERE NAME=\"" + cityName + "\"),");
                sb.append("(SELECT V.ID FROM VILLAGE V INNER JOIN CITY C ON V.CITY_ID=C.ID WHERE V.NAME=\""
                        + villageName + "\" AND C.NAME=\"" + cityName + "\"),");
                sb.append("(SELECT GP.ID FROM GRAM_PANCHAYAT GP INNER JOIN VILLAGE V ON GP.VILLAGE_ID=V.ID WHERE GP.NAME=\""
                        + gramPanchayatName + "\" AND V.NAME=\"" + villageName + "\"),");



                sb.append("\""+pincode+"\",NULL,NULL,'");
                sb.append(mobileNumber+"',\"");
                sb.append(eMail+"\",'"+revisionNumber+"',NULL,NULL,NULL,'1'");
                String samithiIdQuery ="(SELECT WVM.WAREHOUSE_ID FROM WAREHOUSE_VILLAGE_MAP WVM  INNER JOIN WAREHOUSE W ON W.ID=WVM.WAREHOUSE_ID" +
                " WHERE WVM.VILLAGE_ID=(SELECT V.ID FROM VILLAGE V INNER JOIN CITY C ON V.CITY_ID=C.ID WHERE V.NAME=\""
                        + villageName + "\" AND C.NAME=\"" + cityName + "\") AND W.REF_WAREHOUSE_ID IS NOT NULL)";
                 sb.append(","+samithiIdQuery+",");  
                 sb.append(""+samithiIdQuery+",");     
                 sb.append("'"+cetificationStandard+"',");
                 sb.append("'"+cetificationLevel+"',");
                 sb.append("'"+cetificationType+"',");
                 sb.append("'"+martialStatus+"',");
                 sb.append("'"+education+"',");
                 sb.append("'"+childCount+"',");
                 sb.append("'"+childCount1+"',");
                 sb.append("'"+childCount2+"',");
                 sb.append("'"+inspectionType+"',");
                 sb.append("'"+icsStatus+"',");
                 sb.append("(SELECT ID FROM CUSTOMER_PROJECT WHERE NAME_OF_PROJECT=\"Chitradurga\"),");                 
                 sb.append(farmerEconomyId+",");
                 sb.append("'"+statusCode+"',");
                 sb.append("'"+statusMsg+"');\n");

                sb.append(accountInsertQuery);
                String accountNo = getExact(String.valueOf(accountSeq++), 10);
                sb.append(accountNo);
                sb.append("\",'SB','3',CURDATE(),'1',NOW(),NOW(),\"");
                sb.append(farmerId);
                sb.append("\",0.00,0.00);\n");




                sb.append(contractQuery);
                String contractNo = getExact(String.valueOf(contractSeq++), 6);
                sb.append(contractNo);
                sb.append("\",(SELECT ID FROM FARMER WHERE FARMER_ID=\"");
                sb.append(farmerId);
                sb.append("\"),null,1,null,(SELECT ID FROM ESE_ACCOUNT WHERE PROFILE_ID=\"");
                sb.append(farmerId);
                sb.append("\"),0.000,0.00,1);\n");

                sb.append(contarctPricePatternQuery);
                sb.append(contractPriceSeq+"',"+"1);\n");
                sb.append(contarctPricePatternQuery);
                sb.append(contractPriceSeq+"',"+"2);\n");
                sb.append(contarctPricePatternQuery);
                sb.append(contractPriceSeq+"',"+"3);\n");
                sb.append(contarctPricePatternQuery);
                sb.append(contractPriceSeq+"',"+"4);\n");

                contractPriceSeq++;

                sb.append(cardInsertQuery);
                String cardNo = getExact(String.valueOf(cardSeq++), 10);
                sb.append(cardNo);
                sb.append("\",NULL,'2',CURDATE(),'0',NOW(),NOW(),\"");
                sb.append(farmerId);
                sb.append("\",'0');\n\n");

                System.out.println("----------ROW_NO:" + i + "-----------");
                i++;

            }

            sb.append("UPDATE ESE_SEQ SET SEQ_VAL=");
            sb.append(contractSeq - 1);
            sb.append(" WHERE SEQ_KEY='CONTRACT_NO_SEQ';\n");

            sb.append("UPDATE ESE_SEQ SET SEQ_VAL=");            
            sb.append(accountSeq - 1);
            sb.append(" WHERE SEQ_KEY='FARMER_ACCOUNT_NO_SEQ';\n");

            sb.append("UPDATE ESE_SEQ SET SEQ_VAL=");
            sb.append(cardSeq - 1);
            sb.append(" WHERE SEQ_KEY='FARMER_CARD_ID_SEQ';\n");

            sb.append("UPDATE FARMER_ID_SEQ SET WEB_SEQ=");
            sb.append(farmerSeq - 1);
            sb.append(";\n");

            baos.write(sb.toString().getBytes());
            myOutput.write(baos.toByteArray());
            System.out.println("------Query Generated Successfully-------");
        } catch (Exception e) {
            System.out.println("Exception:" + e);
            System.out.println("----------ROW_NO:" + i + "-----------" );
            e.printStackTrace();
        }


    }

    /**
     * Gets the exact.
     * @param value the value
     * @param count the count
     * @return the exact
     */
    public static String getExact(String value, int count) {

        return ((value.length() > count) ? (value.substring(0, count)) : (getEmptyLength(count
                - value.length()))
                + value);
    }

    /**
     * Gets the empty length.
     * @param count the count
     * @return the empty length
     */
    public static String getEmptyLength(int count) {

        StringBuffer data = new StringBuffer();
        for (int i = 0; i < count; i++)
            data.append("0");
        return data.toString();
    }
}

Hi dont know about reading, But the cell causing this exception, must be calculating some value using some other cells in the Excel doc. Assume there are cells c1, c2 and c3 lets say, c1 and c2 holds some value and c3 holds value that is dependent on c1, c2. c3 = c1 + c2. Something like this. — Prashant
– Prashant, Commented May 21, 2015 at 10:09

evepokua · Accepted Answer · 2018-03-22 22:38:29Z

0

All numeric values/data in the excel sheet must have a leading single quote: '

For example :

'07543234337

'0051698435638

'10005

answered Mar 22, 2018 at 22:38

evepokua

511 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Felix · Accepted Answer · 2019-03-15 20:19:22Z

0

switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        cell.getBooleanCellValue() 
        break;
    case Cell.CELL_TYPE_NUMERIC:
        cell.getNumericCellValue()
        break;
    case Cell.CELL_TYPE_STRING:
        cell.getStringCellValue()
        break;
    case Cell.CELL_TYPE_BLANK:  
        break;
    default:
        cell.toString()
}

try above one

edited Mar 15, 2019 at 20:19

Felix

7271 gold badge14 silver badges29 bronze badges

answered May 21, 2015 at 11:24

Rathakrishnan Duraimoni

1719 bronze badges

Collectives™ on Stack Overflow

java.lang.IllegalStateException: Cannot get a text value from a numeric formula cell

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related