Качих файла на Excel в mysql 5.7. Подробностите се качват гладко, с изключение на времето. Не знам как да получа времева стойност от листа на Excel.
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 |
|__________________________|
Ако използвам метода по-долу
String punchin= row.getCell(0).getStringCellValue(); грешката ще възникне.
Ако използвам метода по-долу
int punchin = (int) row.getCell(0).getNumericCellValue(); не е възникнала грешка, но стойностите съдържат нула.
**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);
}
}
}
Описание на таблицата
