Excel File uploading Using Java

Question

I uploaded the excel file in mysql 5.7.The details are uploaded smoothly except time.I dont know how to get time value from excel sheet.

Issue:

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

**upload.xlsx**

___________________________
|Punch In   |  Punch       |
----------------------------
|9:00:27 Am |19:45:57 PM   |
|__________________________|

If i use the below method

String punchin= row.getCell(0).getStringCellValue(); the error will be occured.

If i use the below method

int punchin = (int) row.getCell(0).getNumericCellValue(); no error occured but the values holds zero.

**insert.java**

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
//import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.servlet.ServletException;
import javax.servlet.annotation.MultipartConfig;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.Part;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

@WebServlet("/insert1")
@MultipartConfig(maxFileSize = 1216584) 
public class insert1 extends HttpServlet {
    private static final long serialVersionUID = 1L;
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/rough1","root","root");
            con.setAutoCommit(false);
            PreparedStatement pstm = null ;
            Part filepart=request.getPart("filename");
            InputStream inputstream=null;
            inputstream=filepart.getInputStream();
            XSSFWorkbook wb = new XSSFWorkbook(inputstream);
            XSSFSheet sheet = wb.getSheetAt(0);
            Row row;
            for(int i=1; i<=sheet.getLastRowNum(); i++){
            row = sheet.getRow(i);
            //String punchin= row.getCell(0).getStringCellValue();
            int punchin = (int) row.getCell(0).getNumericCellValue();
            System.out.println(":::::::::::::::::::::::::: "+punchin);
        //  String punchout= row.getCell(1).getStringCellValue();
            int punchout =(int)row.getCell(1).getNumericCellValue();
            System.out.println(":::::::::::::::::::::::::: "+punchout);
//          int duration = (int) row.getCell(5).getNumericCellValue();
    String sql = "INSERT INTO log VALUES(null,'"+punchin+"','"+punchout+"')";
            pstm = (PreparedStatement) con.prepareStatement(sql);
            pstm.execute();
            System.out.println("Import rows "+i);
            }
            con.commit();
            pstm.close();
            con.close();
            inputstream.close();
            System.out.println("Success import excel to mysql table");
            }catch(ClassNotFoundException e){
            System.out.println(e);
            }catch(SQLException ex){
            System.out.println(ex);
            }catch(IOException ioe){
            System.out.println(ioe);
            }
    }

}

Table Desc

Possible duplicate of [Reading time values from spreadsheet using poi api](http://stackoverflow.com/questions/15710888/reading-time-values-from-spreadsheet-using-poi-api) — Erwin Bolwidt, Jan 02 '17 at 06:17
This example give HSSFCell but please consider changing xssf. for docx — KishanCS, Jan 02 '17 at 06:20

score 0 · Answer 1 · answered Jan 02 '17 at 06:13

0

I is clearly mentioned that you are facing data type mismatch Please cast the value you will succeed without errors.

String punchin= String.valueOf(row.getCell(0).getNumericCellValue());

Be Caution about data types with cells.

answered Jan 02 '17 at 06:13

KishanCS

1,357
1
19
38

That gives you a number, not a time value. – Erwin Bolwidt Jan 02 '17 at 06:17
1

I just solved that error but i am no sure what propose it is used . – KishanCS Jan 02 '17 at 06:18
1

Even if deeply described with issue i could have given better solution – KishanCS Jan 02 '17 at 06:19
@KishanCS I just upload employee punchin and punchout time from excel file to database(Mysql).In mysql the punchin and punchout field holds time. – Kanika Jan 02 '17 at 07:03
In mysql the punchin and punchout field holds Time datatype.I tried this String punchin= String.valueOf(row.getCell(0).getNumericCellValue()); but it read like this."0.3753125" – Kanika Jan 02 '17 at 07:06
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect time value: '0.3753125' for column 'punchin' at row 1 – Kanika Jan 02 '17 at 07:07
Please check what is the column type, share desc of your table.. 0.3753125 is decimal use proper data type on column or convert to what data type you want for column to fit – KishanCS Jan 02 '17 at 07:35

Excel File uploading Using Java

1 Answers1