Java Date Formatter not working while saving Excel date data into MySQL

Question

My goal is to save every rows of data in excel into MySQL database. In my excel file, the date format is dd.MM.yyyy, whereas yyyy-MM-dd is the date format in MySQL.

Here are my codes:

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReader {

    public static void main(String[] args) throws Exception {

        try {

            Class.forName("com.mysql.jdbc.Driver");
            Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/activity", "root", "password");
            con.setAutoCommit(false);
            PreparedStatement pstm = null ;
            FileInputStream input = new FileInputStream("testing.xlsx");
            //POIFSFileSystem fs = new POIFSFileSystem( input );
            XSSFWorkbook wb = new XSSFWorkbook(input);
            XSSFSheet sheet = wb.getSheetAt(0);
            Row row;
            for(int i=1; i<=sheet.getLastRowNum(); i++){
                row = sheet.getRow(i);
                String activityName= row.getCell(5).getStringCellValue();
                Integer activityAllocation = (int) row.getCell(8).getNumericCellValue();
                //DataFormatter dataFormatter = new DataFormatter();
                //String activityDate = dataFormatter.formatCellValue(row.getCell(10));
                Date activityDate = row.getCell(10).getDateCellValue();                
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                String Date = sdf.format(activityDate);
                String activityPersonInCharge = row.getCell(11).getStringCellValue();

                String sql = "INSERT INTO activity(ACTIVITY_NAME,ACTIVITY_ALLOCATION,ACTIVITY_DATE,ACTIVITY_MADE_BY) VALUES('"+activityName+"','"+activityAllocation +"','"+activityDate +"','"+activityPersonInCharge +"')";
                pstm = (PreparedStatement) con.prepareStatement(sql);
                pstm.execute();
                System.out.println("Import rows "+i);
            }
            con.commit();
            pstm.close();
            con.close();
            input.close();
            System.out.println("Success import excel to mysql table");
        }
        catch (IOException e) {
            e.printStackTrace();
        }
    }

}

I tried to format the date of the excel cell but failed to work. The exception:

Exception in thread "main" java.lang.IllegalStateException: Cannot get a NUMERIC value from a STRING cell
    at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1062)
    at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:307)
    at org.apache.poi.xssf.usermodel.XSSFCell.getDateCellValue(XSSFCell.java:776)
    at ExcelReader.main(ExcelReader.java:35)

Which part of the codes that I did wrongly? Also, if I use the commented line of codes for the date formatter, no exception shown but it save the wrong dates. Please guide me. Thanks in advance

Doing nothing more than reading the exception, you seem to be trying to read a numeric value from a text based cell - have you tried reading it as a String instead (and seeing what it returns)? — MadProgrammer
You should inserting an actual date object into your database, rather than a formatted string ... but you're not using PreparedStatement properly anyway :P — MadProgrammer
@MadProgrammer I tried with String, but it saves the wrong date into MySQL. For instance, in the excel, the date is 19.06.2014 but in MySQL is 2019-06-20. — Brian Cheong
@ScaryWombat line 35 is Date activityDate = row.getCell(10).getDateCellValue(); — Brian Cheong

Scary Wombat Scary Wombat · Accepted Answer · 2017-06-30T02:22:38

Get the value as a String

String activityDate = row.getCell(10).getStringCellValue();

According to you the format is19.06.2014

so

SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");

then parse it into a Date

Date dt = sdf.parse(activityDate);

Use PreparedStatment to insert

Set the value (column 3 ?)

ps.setDate (3, dt);

Java Date Formatter not working while saving Excel date data into MySQL

1 Answers