当前位置: 动力学知识库 > 问答 > 编程问答 >

Excel File uploading Using Java

问题描述:

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

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: