将 Excel 日期数据保存到 MySQL 时，Java 日期格式化程序不起作用

Question

我的目标是将excel中的每一行数据保存到MySQL数据库中。在我的 excel 文件中，日期格式是 dd.MM.yyyy，而 yyyy-MM-dd 是 MySQL 中的日期格式。

这是我的代码:

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();
        }
    }

}

我尝试格式化 excel 单元格的日期但失败了。异常:

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)

我哪部分代码做错了？此外，如果我对日期格式化程序使用注释代码行，则不会显示异常，但它会保存错误的日期。请指导我。提前致谢

Answer 1

获取字符串形式的值

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

根据您的说法，格式是19.06.2014

所以

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

然后将其解析为日期

Date dt = sdf.parse(activityDate);

使用PreparedStatment插入

设置值(第 3 列？)

ps.setDate (3, dt);