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

Answer 1

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.