使用POI对表格进行逐行逐列读取并写入数据库

最新推荐文章于 2024-03-06 08:31:39 发布
R-sz
最新推荐文章于 2024-03-06 08:31:39 发布
阅读量308
点赞数
文章标签：数据库 python 开发语言 java
本文链接：https://blog.csdn.net/weixin_42759398/article/details/134404775
版权
package com.metaqianfeng;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

public class ExcelToDatabase {

	// 定义变量主要是为了后续写入group_id
	private static HashMap<String, String> nameToGroupIdMap = new HashMap<String, String>() {{
		put("考核1组", "037b3fecf80047c29769abf264f86a14");
		put("考核13组", "2364775b5cf148c5849bf6ba269905a0");
		put("考核2组", "2a69dddc52f344e0bb6eace6a327cd8d");
		put("考核3组", "382ad90e0c8d4f33b833d7e697a9cf28");
		put("考核12组", "5de7c1b46d5540cb85e5a69fc08ec63d");
		put("考核7组", "6045f93b4c5a44268a6c343064c4195d");
		put("考核5组", "6c5bb3cd3cd94e6fbfefded112c1d9c6");
		put("考核6组", "738d9c5198fd48d998e83103f4d21efa");
		put("考核14组", "99b5c9bf3814426c81a5eba20746fc2d");
		put("考核10组", "af7ed97b2f9b4e9c9b988256062307ee");
		put("考核4组", "b2a2cecc45ac449bb4067a6813f909c4");
		put("考核9组", "dbfa1bcbf3744bb09aad29c8f25ccb1e");
		put("考核11组", "f2c57761643646998cf8fe226a8818d3");
		put("考核15组", "f480c4175f69403da7ed200d17998146");
		put("考核8组", "fc3a52b5ee4f4ba38181e0c55975126d");
	}};


	//主要是为了kind的id和name的对应关系,一级菜单只有一种
	private static HashMap<String, String> nameToKindMap = new HashMap<String, String>() {{
		put("舰载飞行潜质专家评定表", "JZ");
		put("军事飞行潜质专家评定表", "JS");
		put("行为观察", "XWGC");
		put("情境考查", "QJKC");
		put("潜质检测", "QZJC");
	}};


	// 主要是为了区分 3-军事飞行潜质评定表和 4-舰载飞行潜质评定表,军事飞行潜质评定表的意志品质和舰载飞行潜质评定表的意志品质不一样
	private static HashMap<String, String> nameToAbilityIdMap3 = new HashMap<String, String>() {{
		put("记忆能力", "0152d549fcb74b8fb7ba09ea8c42eb03");
		put("创造能力", "2105681a308145348ef844089453c115");
		put("思维能力", "3a6b3b7881e0424f8e3c46e0ba0e3820");
		put("应变反应", "44aca75028a14195980c5bb7ce6c19f7");
		put("成就动机", "4536b9ec0e9846fc926fff3098d17e15");//有歧义  到底是字母还是uuid
		// todo
		put("领导能力", "4f79cd4c2542456f8b52c7d85ec1f4ee");
		put("压力应对", "4fb5e3e4930e4c02801800f43b7a53dd");
		put("情绪特点", "5464211d509e44e19fbb7df60ad99ffe");
		put("注意集散", "555aa0c2913d4d7cb97453891583d8c3");
		put("操控能力", "59c69d184bcb4279a0d92f3983ebc662");
		put("性格特征", "5b6cccc21dcb4664b559d8ee6ce80439");
		put("空间定向", "77ce3eaa5b6640c5ac34e255a9e14a97");
		put("感知能力", "7d876306328c4e34bafda2064a1e3b17");
		put("意志品质", "84e1de9c001d4b9cae3b48da11ec0a2c");//3-军事飞行潜质评定表
		put("思想意识", "88637fd449544cebb6f3123909d55ef4");
		put("适应能力", "8a414c69441f44e69d745c98f4f461b6");
		put("精确操控", "8a6c762b69b449b88dbc1bb669a78851");
		put("飞行动机", "9a7656c93c124dd393f8880c57ac5748");
		put("环境适应", "9cb818e0b57641a1b94d68315bff1266");
		put("注意能力", "c290720486ec4958b47efe75788109bd");
		put("判断决策", "e1f06aa6fa444d0aa3bfaeb7043aaf20");
		put("自控能力", "e4f8cc561a044a6e875368ad2b3136db");
		put("情绪调控", "ec7d3cf4a8ba4c708f5d7fb87cd31e61");
		put("反应能力", "fa684d72c8da493cbc918af1a0057457");

		put("综合评定", "FINAL");
		put("意志品质", "YZPZ");
		put("情绪特点", "QXTD");
		put("性格特征", "XGTZ");
		put("飞行动机", "FXDJ");
		put("思想意识", "SXYS");
	}};
	private static HashMap<String, String> nameToAbilityIdMap4 = new HashMap<String, String>() {{
		put("记忆能力", "0152d549fcb74b8fb7ba09ea8c42eb03");
		put("创造能力", "2105681a308145348ef844089453c115");
		put("思维能力", "3a6b3b7881e0424f8e3c46e0ba0e3820");
		put("应变反应", "44aca75028a14195980c5bb7ce6c19f7");
		put("成就动机", "4536b9ec0e9846fc926fff3098d17e15");
		put("领导能力", "4f79cd4c2542456f8b52c7d85ec1f4ee");
		put("压力应对", "4fb5e3e4930e4c02801800f43b7a53dd");
		put("情绪特点", "5464211d509e44e19fbb7df60ad99ffe");
		put("注意集散", "555aa0c2913d4d7cb97453891583d8c3");
		put("操控能力", "59c69d184bcb4279a0d92f3983ebc662");
		put("性格特征", "5b6cccc21dcb4664b559d8ee6ce80439");
		put("空间定向", "77ce3eaa5b6640c5ac34e255a9e14a97");
		put("感知能力", "7d876306328c4e34bafda2064a1e3b17");
		put("意志品质", "804deb737842476b8af43fad97ea940f");//4-舰载飞行潜质评定表
		put("思想意识", "88637fd449544cebb6f3123909d55ef4");
		put("适应能力", "8a414c69441f44e69d745c98f4f461b6");
		put("精确操控", "8a6c762b69b449b88dbc1bb669a78851");
		put("飞行动机", "9a7656c93c124dd393f8880c57ac5748");
		put("环境适应", "9cb818e0b57641a1b94d68315bff1266");
		put("注意能力", "c290720486ec4958b47efe75788109bd");
		put("判断决策", "e1f06aa6fa444d0aa3bfaeb7043aaf20");
		put("自控能力", "e4f8cc561a044a6e875368ad2b3136db");
		put("情绪调控", "ec7d3cf4a8ba4c708f5d7fb87cd31e61");
		put("反应能力", "fa684d72c8da493cbc918af1a0057457");


		put("综合评定", "FINAL");
		put("意志品质", "YZPZ");
		put("情绪特点", "QXTD");
		put("性格特征", "XGTZ");
		put("飞行动机", "FXDJ");
		put("思想意识", "SXYS");

	}};
	//二级标题
	private static HashMap<String, String> nameToItemIdMap = new HashMap<String, String>() {{
		put("图形记忆", "0580ac93920c485a82369701e1989283");
		put("托带球跑", "06897e6f1690492bb6ce905385995418");
		put("心算判断", "09a47937656f42f7a927c0c307bbcea6");
		put("创造能力", "101aa279653e40b28281479f37a87f33");
		put("反应能力", "1e1dc8c04a584c34ada8463e1807a744");
		put("巧跳方格", "210367652d704119a57b34c31b3423cc");
		put("模拟演练", "36d6a231ceb6473ca1829e5ee4ed90d0");
		put("拍球写字", "3838dd1f69474bdda8f88376a559510c");
		put("模仿动作", "51b14f448af54e9aa305207ba91760ce");
		put("前跃滚翻", "53e94796a56d4926a13444219f855612");
		put("团队运球", "58e858cd57e04013acd39203a9a9c324");
		put("反向动作", "60fd401019214784bfdca900dd4ae50b");
		put("原地跳绳", "66936c92099f47b1b9895ce0358c1b60");
		put("领导能力", "66e8df0b95c34c168800722a51329645");
		put("自控能力", "71140112d51b46319dfd4eeff5d3f4f6");
		put("辨别方位", "8654e1e7e68746cd816d00bac9340d47");
		put("思维能力", "8700716a49ee447b9c863a0791799af9");
		put("躲避掷球", "895cfe29fb4e4de48c1f0c2a89a2be61");
		put("接物报数", "a11f86ae9e7844e4979ee9e3a20dd919");
		put("即兴演讲", "b5be3491ac5744fc87c18b1cf12a6905");
		put("跨越障碍", "b701feb4d43f4ac8b564557dbf1df853");
		put("体能测试", "b7a46c28449e4f6b9a0449b61896b635");
		put("跑跳长绳", "bf148c262c494c998c82a0209f8aba30");
		put("接力报数", "c38b5328eb6941aa87e9dd3b613a72c5");
		put("团队讨论", "c73e6fd530444da4905e2dde51ce4446");
		put("适应能力", "ca77d3b9fe1e49bd80a6f1966937c31d");
		put("跑步变向", "d037a20ae15b45fc91b9f863b1b26213");
		put("积木拼图", "d808f8d084d84e0389f226d9577b2944");
		put("仪器操控", "db0c541e2edd489a83e24e627924cbcc");
		put("曲线跟踪", "f43c6e25c65b4e758df0467d1ef1137f");

		put("检测评分", "testScore");
		put("印象评分", "impressionScore");
		put("综合评分", "comprehensiveScore");
		put("综合评定", "FINAL");
	}};


	//这个主要用于，读取到第三排时候，进行查找tiemId和name,前提是  必须把查找的表格的三级菜单（检测评分等等）换成二级菜单再去getKey,本质上是二级菜单匹配表
//	private static HashMap<String, String> nameToItemIdMap3 = new HashMap<String, String>() {{
//		put("检测评分", "testScore");
//		put("印象评分", "impressionScore");
//		put("综合评分", "comprehensiveScore");
//		put("综合评定", "FINAL");
//	}};
	private String batchId = "1234567890";


	//这个通过连接数据库获取
	private static HashMap<String, String> BatchIdMap;
	private static HashMap<String, String> BatchNameMap;

	// 数据库连接信息
	public static final String DB_URL = "jdbc:mysql://81.68.80.37:13306/hjzf?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true";
	// 数据库用户名
	public static final String DB_USERNAME = "root";
	// 数据库密码
	public static final String DB_PASSWORD = "mysql@XH2022";
	// 文件路径
	public static final String FILE_PATH = "C:\\Users\\admin\\Desktop\\读取excel补录数据\\海军招飞-三平台小分补录数据模版.xlsx";


	public ExcelToDatabase() {


	}

	public static void main(String[] args) {

		System.out.println("执行开始时间=" + LocalDateTime.now());

		//先查询batch_id的集合
		BatchIdMap = new HashMap<>();
		// 初始化batchIdMap
		initializeNameToBtchIdMap();

		String filePath = FILE_PATH;
		try {

			String creator = "未知";//定义创建人(专家)  第一次读取没有事件和专家，就很尴尬
			String create_time = "19991111111111";//定义创建时间


			// 读取 Excel 文件
			FileInputStream inputStream = new FileInputStream(filePath);
			Workbook workbook = new XSSFWorkbook(inputStream);

			// 获取第一个工作表
			Sheet sheet = workbook.getSheetAt(0);


			// 数据库连接
			Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

// 创建 PreparedStatement 对象
			PreparedStatement statement = null;
			PreparedStatement scoreStatement = connection.prepareStatement("INSERT INTO t_test_score (name,cert_no,code, batch_id,  batch_name,  kind_id, kind_name, item_id, item_name, ability_id, ability_name, score,group_id, creator,create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)");
			PreparedStatement evaluateStatement = connection.prepareStatement("INSERT INTO t_test_evaluate (name,cert_no,code, batch_id,  batch_name,  kind_id, kind_name, item_id, item_name, ability_id, ability_name, score,group_id, creator,create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)");
			// 遍历工作表中的每一行
			for (int i = 3; i < sheet.getPhysicalNumberOfRows(); i++) {
//			for (int i = 3; i < 4; i++) {
				System.out.println("执行第" + (i + 1) + "行数据");
				Row row = sheet.getRow(i);


				//前五列对于其每一行都是固定的
				String name;
				try {
					name = row.getCell(0).getStringCellValue();
				} catch (Exception e) {
					//没有名字则说明到头了，直接结束
					break;
				}


				Integer cert_no = (int) row.getCell(1).getNumericCellValue();
				Integer code = (int) row.getCell(2).getNumericCellValue();

				String batch_id = "batch_od";//这个表格没有，暂时写死
//				if (row.getCell(3) != null) {
//					batch_id = (int) row.getCell(3).getNumericCellValue();
//				}

				batch_id = BatchIdMap.get(cert_no.toString());
				String batch_name = "批次号未知";//这个表格也没有，也写死
//				if (row.getCell(4)!= null){
//					batch_name = row.getCell(4).getStringCellValue();
//				}

				String group_id = "group_id";
				//通过分组名“考核一组” 获取group_id
				if (row.getCell(4) != null) {
					String group_id_name = row.getCell(4).getStringCellValue();
					group_id = nameToGroupIdMap.get(group_id_name);
				}


				// 逐列读取并设置特定的列参数
				int cellIndex = row.getLastCellNum() - 1;  // 从第六列开始
				while (cellIndex > 4) {
					if ( "军事飞行潜质评定表".equals(getRowColumnValue(sheet, 0, cellIndex)) || "舰载飞行潜质评定表".equals(getRowColumnValue(sheet, 0, cellIndex))) {
						//捕获到后三项目需要转换表
						statement = evaluateStatement;
					} else {
						statement = scoreStatement;
					}


					if (getRowColumnValue(sheet, 1, cellIndex).equals("专家")) {
						if (row.getCell(cellIndex) != null) {
							creator = row.getCell(cellIndex).getStringCellValue();
						}
//						else {
//							creator = (i + 1) + "行" + cellIndex+"列专家为空";
//							System.out.println(creator);
//						}

						cellIndex--;//继续读取前一列数据
						continue;
					}
					if (getRowColumnValue(sheet, 1, cellIndex).equals("日期")) {

						SimpleDateFormat outputFormat = new SimpleDateFormat("yyyyMMddHHmmss"); // 输出日期时间字符串的格式

						String dateTimeStr = "19000101000000";
						if (row.getCell(cellIndex) != null) {
							Date dateCellValue = row.getCell(cellIndex).getDateCellValue();
							try {
								dateTimeStr = outputFormat.format(dateCellValue); // 格式化 Date 对象为日期时间字符串
							} catch (Exception ex) {
								//本质上是空指针异常，也就是读不到时间，就是空串继续往前读取就行，若出现其他异常则是时间格式不对，会在数据库中看到19000101000000，以便后续查找
//								System.out.println("第"+cellIndex+"列有时间转换错误");
							}

						}

						create_time = dateTimeStr;

						cellIndex--;//继续读取前一列数据
						continue;
					}
					BigDecimal score = new BigDecimal("-1"); // 初始化为 null，表示未知或空值
					Cell cell = row.getCell(cellIndex);
					if (cell != null) {//当前格子为纯空白
						score = new BigDecimal(row.getCell(cellIndex).getNumericCellValue());
						if (score.compareTo(new BigDecimal("0")) == 0) {
//							System.out.println("捕获");
							try {

								if (row.getCell(cellIndex).getStringCellValue().equals("")) {
//									System.out.println("与空串匹配到,本质上是表格啥也没有，但是做了颜色标注");
									cellIndex--;//继续读取前一列数据
									continue;
								}
							} catch (Exception ex) {//如果取到对象不为空，也不是空串，那其实就是0，正常走不用管，这段优化下回头
								System.out.println("第" + cellIndex + "列值为0");
							}
						}
					} else {
						cellIndex--;//继续读取前一列数据
						continue;
					}
					// 设置前五列参数,都是固定的，因为每一次循环
					statement.setString(1, name);  // name
					statement.setInt(2, cert_no);  //
					statement.setInt(3, code);  // code
					statement.setString(4, batch_id);  // batch_id 待定
					statement.setString(5, batch_name);  // batch_name 待定


					//获取指定的大标题
					String rowColumnValue = getRowColumnValue(sheet, 0, cellIndex);
					//获取指定的二标题
					String rowColumnValue2 = getRowColumnValue(sheet, 1, cellIndex);
					//获取指定的三标题
					String rowColumnValue3 = getRowColumnValueForTitle(sheet, 2, cellIndex);

					// TODO: 2023-11-14 这里检查下

					// 在二级菜单有但是三级菜单获取不到时，就说明是二级菜单的最后一项，需要把三级菜单的值设置为二级菜单的值，然后将二级菜单设定为“综合评定”
					if ((rowColumnValue3 == null || rowColumnValue3.equals("")) && rowColumnValue2 != null && !rowColumnValue2.equals("")) {

						//只有情景考察记录表才会出现这种情况需要进行特殊处理，因为读取到二级菜单实际上是A表的三级菜单，需要将二级菜单设置为综合评定
						if (rowColumnValue.equals("情境考核记录表")) {
							//这个表的规则属于特殊情况，需要将二级菜单设置为综合评定
							rowColumnValue3 = rowColumnValue2;
						} else {
							//后三张表的二级菜单都是综合评定
							rowColumnValue3 = "综合评定";
						}
					}

					//对后三张表的数据进行特殊处理，因为后三张表的数据是 item和ability是反向的
					if (rowColumnValue.equals("军事飞行潜质评定表") || rowColumnValue.equals("舰载飞行潜质评定表")) {
						//对二三级菜单进行交换
						String temp = rowColumnValue3;
						rowColumnValue3 = rowColumnValue2;
						rowColumnValue2 = temp;
					}

					//对 一级菜单进行匹配处理，在此时，一级菜单无论是哪个表都取其一个map
					String matchingKey = getMatchingKey(nameToKindMap, rowColumnValue);
					String kind_id = "kind_id";
					String kind_name = "kind_name";
					if (matchingKey != null) {
						kind_name = matchingKey;
						kind_id = nameToKindMap.get(matchingKey);
						System.out.println("Matched key: " + kind_name);
						System.out.println("Corresponding value: " + kind_id);
					} else {
						System.out.println("No matching key found for input: " + rowColumnValue);
					}


					//通过map取得item_id值，处理二级菜单
					String item_id = nameToItemIdMap.get(rowColumnValue2);


					//对三级标题进行特殊处理，需要先判断大标题是哪个，是舰载飞行潜质评定表 还是 军事飞行潜质评定表
					String ability_id = "ability_id";
					if ("舰载飞行潜质评定表".equals(rowColumnValue)) {
						ability_id = nameToAbilityIdMap4.get(rowColumnValue3);

					}
//					else if ("军事飞行潜质评定表".equals(rowColumnValue)) {
					else{

						ability_id = nameToAbilityIdMap3.get(rowColumnValue3);

					}


					statement.setString(6, kind_id);  // kind_id
					statement.setString(7, kind_name);  // kind_name
					statement.setString(8, item_id);  // item_id
					statement.setString(9, rowColumnValue2);  // item_name
					statement.setString(10, ability_id);  // ability_id
					statement.setString(11, rowColumnValue3);  // ability_name
					statement.setBigDecimal(12, score);  // score
					statement.setString(13, group_id);//map在initializeNameToGroupIdMap方法里初始化
					statement.setString(14, creator);
					statement.setString(15, create_time);


					// 执行插入操作
					statement.addBatch();

					cellIndex--;//继续读取下一列数据
				}
				// 把两个表的批量执行插入操作
				scoreStatement.executeBatch();
				evaluateStatement.executeBatch();
				System.out.println("执行第" + (i + 1) + "行数据");

			}

			// 关闭资源
			workbook.close();
			inputStream.close();
			connection.close();
			System.out.println("执行完成，结束时间=" + LocalDateTime.now());

		} catch (IOException | SQLException e) {
			e.printStackTrace();
		}
	}


	//输出行和列还有sheet获取指定行列的值，遇到合并单元格的情况，递归取处理，往前找一格
	static String getRowColumnValue(Sheet sheet, int rowIndex, int columnIndex) {
		Row row = sheet.getRow(rowIndex); // 获取指定行
		Cell cell = row.getCell(columnIndex); // 获取指定列的单元格

		if (cell != null && cell.getStringCellValue() != "") {
			return cell.getStringCellValue(); // 假设单元格都是字符串类型数据
			// 处理单元格的值
		} else {
			return getRowColumnValue(sheet, rowIndex, columnIndex - 1);
		}

	}

	//输出行和列还有sheet获取指定行列的值，不能递归去处理，否则往前找一格就出错，因为第三行和第二行合并单元格后，第三行就是空，它也就为空
	static String getRowColumnValueForTitle(Sheet sheet, int rowIndex, int columnIndex) {
		Row row = sheet.getRow(rowIndex); // 获取指定行
		Cell cell = row.getCell(columnIndex); // 获取指定列的单元格

		if (cell != null && cell.getStringCellValue() != "") {
			return cell.getStringCellValue(); // 假设单元格都是字符串类型数据
			// 处理单元格的值
		} else {
			return null;
		}

	}

	//主要是为了匹配大标题，因为表格的标题只与前两个字一致
	private static String getMatchingKey(Map<String, String> map, String input) {
		String inputPrefix = input.length() >= 2 ? input.substring(0, 2) : input;
		for (Map.Entry<String, String> entry : map.entrySet()) {
			String keyPrefix = entry.getKey().length() >= 2 ? entry.getKey().substring(0, 2) : entry.getKey();
			if (inputPrefix.equals(keyPrefix)) {
				return entry.getKey();
			}
		}
		//必须找到匹配，否则报错
		throw new RuntimeException("No matching key found for input: " + input);


	}

	//初始化map,用于匹配batch_od
	private static void initializeNameToBtchIdMap() {
		//这个待定，batch_id后续再处理

		// 连接数据库
		try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);) {
			// 创建 PreparedStatement 对象
			PreparedStatement statement = connection.prepareStatement("SELECT cert_no, batch_id FROM t_test_object");
			// 执行查询
			ResultSet resultSet = statement.executeQuery();
			// 读取查询结果，并存储到 HashMap 中
			while (resultSet.next()) {
				String cert_no = resultSet.getString("cert_no");
				String batch_id = resultSet.getString("batch_id");
				BatchIdMap.put(cert_no, batch_id);
			}


		} catch (SQLException e) {
			e.printStackTrace();
		}


	}


}