Java 工具库 txt 解析并存储到数据库

/**

• Created by IntelliJ IDEA.
• @Author jocker zhu
• @Date 2024-04-22 13:52
  **/

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;

public class TxtExcelReaderER {
static final String JDBC_DRIVER = “com.mysql.cj.jdbc.Driver”;
static final String DB_URL = “jdbc:mysql://127.0.0.1:3306/apolloSystem”;
static final String USER = “gokinApollo”;
static final String PASS = “****”;

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        int i = 1;
        String excelFilePath = "E:/GokinWork/一键结薪/YBJJ/0206.xlsx"; // 修改为你的 Excel 文件路径
        String filePath = "E:/GokinWork/一键结薪/XNJJ/20250416EJJJB.txt";

        Class.forName(JDBC_DRIVER);
        conn = DriverManager.getConnection(DB_URL, USER, PASS);
        pstmt = conn.prepareStatement("INSERT INTO apollo_history_jj_e (user_num, user_name, work_weight, everyone_work_weight, work_technology, create_time) VALUES (?, ?, ?, ?, ?, ?)");

        try (BufferedReader br = new BufferedReader(new FileReader(filePath))) {
            String line;
            while ((line = br.readLine()) != null) {
                // 打印原始行内容
                //System.out.println("原始行: " + line);

                // 跳过空行
                if (line.trim().isEmpty()) continue;

                // 使用正则表达式按一个或多个连续空白字符分割
                String[] parts = line.trim().split("\\s+");

                // 验证数据完整性（假设每行应有5个字段，按需调整）
                if (parts.length < 5) {
                    System.err.println("数据格式错误，缺失字段: " + line);
                    continue;
                }

                try {
                    // 解析字段（按实际文件结构调整顺序和类型）
                    String id = parts[0];
                    String name = parts[1];
                    String department = parts[2];
                    String salary = parts[3];
                    String date = parts[4];
                    String date1 = parts[5];
                    String date2 = parts[6];
                    String date3 = parts[7];

                    // 假设 date1 的格式为 "yyyy-MM-dd"，date2 的格式为 "HH:mm"
                    String combinedDateTime = String.format("%s %s:00", date1, date2);

                    // Step 1: 解析日期 (自动补零)
                    DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("yyyy/M/d");
                    LocalDate date0 = LocalDate.parse(date1, dateFormatter);  // 解析为 LocalDate

                    // Step 2: 合并时间并转换为 24 小时制
                    String fullTimeStr = date2 + " " + date3;                // 组合为 "9:30 PM"
                    DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("h:mm a");
                    LocalTime time = LocalTime.parse(fullTimeStr, timeFormatter);  // 解析为 LocalTime

                    // Step 3: 合并日期和时间
                    LocalDateTime dateTime = LocalDateTime.of(date0, time);

                    // Step 4: 格式化为目标字符串
                    DateTimeFormatter targetFormatter = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");
                    String result = dateTime.format(targetFormatter);
                    //System.out.println("--------"+result);

					// 定义时间格式解析器（注意模式匹配）
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

                    // 将字符串解析为 java.util.Date
                    java.util.Date utilDate = sdf.parse(result);

                    // 使用示例
                    //System.out.println("解析后的时间：" + utilDate);

                    Timestamp sqlTimestamp = new Timestamp(utilDate.getTime()); // 将 java.util.Date 转换为 java.sql.Timestamp
                    // 转为Timestamp（兼容java.sql.Timestamp）
                    //Timestamp timestamp = Timestamp.valueOf(localDateTime);
                    pstmt.setString(1, id); // user_num
                    pstmt.setString(2, name); // user_name
                    pstmt.setString(3, department); // work_weight
                    pstmt.setString(4, salary); // everyone_work_weight
                    pstmt.setString(5, date); // work_technology

                    pstmt.setTimestamp(6, sqlTimestamp); // 将 java.sql.Timestamp 对象传递给 PreparedStatement
                    pstmt.executeUpdate();
                    System.out.println(id +"    "+  name +"    "+ result + "数据插入成功！");
                } catch (NumberFormatException e) {
                    System.err.println("数值转换失败: " + line);
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
            System.err.println("文件读取失败: " + e.getMessage());
            e.printStackTrace();
        }

//以下部分为表格上传
// try (FileInputStream inputStream = new FileInputStream(excelFilePath);
// Workbook workbook = new XSSFWorkbook(inputStream)) {
// Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
// for (Row row : sheet) {
// Cell firstCell = row.getCell(0); // 获取第一列的单元格
// if (firstCell != null) {
// // 获取每行的数据并插入到数据库中
// pstmt.setString(1, getStringBeforeDecimal(getCellValue(row.getCell(0)))); // user_num
// pstmt.setString(2, getCellValue(row.getCell(1))); // user_name
// pstmt.setDouble(3, Double.parseDouble(getCellValue(row.getCell(2)))); // work_weight
// pstmt.setDouble(4, Double.parseDouble(getCellValue(row.getCell(3)))); // everyone_work_weight
// pstmt.setString(5, getCellValue(row.getCell(4))); // work_technology
// java.util.Date utilDate = row.getCell(5).getDateCellValue(); // 获取 java.util.Date 对象
// Timestamp sqlTimestamp = new Timestamp(utilDate.getTime()); // 将 java.util.Date 转换为 java.sql.Timestamp
// pstmt.setTimestamp(6, sqlTimestamp); // 将 java.sql.Timestamp 对象传递给 PreparedStatement
// pstmt.executeUpdate();
// System.out.println(getStringBeforeDecimal(getCellValue(row.getCell(0))) + getCellValue(row.getCell(1)) + i++ + “数据插入成功！”);
// }
// }
// } catch (IOException e) {
// e.printStackTrace();
// }
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) pstmt.close();
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}

public static String getStringBeforeDecimal(String str) {
    int decimalIndex = str.indexOf('.');
    if (decimalIndex != -1) { // 如果找到了小数点
        return String.format("%06d", Integer.parseInt(str.substring(0, decimalIndex)));
    } else {
        return String.format("%06d", Integer.parseInt(str));
    }
}

private static String getCellValue(Cell cell) {
    if (cell == null) {
        return null; // 如果单元格为空，返回 null
    }
    switch (cell.getCellType()) {
        case STRING:
            return cell.getStringCellValue();
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return String.valueOf(cell.getDateCellValue());
            } else {
                return String.valueOf(cell.getNumericCellValue());
            }
        case BLANK:
            return "";
        default:
            return null;
    }
}

}