java实现表格数据导入数据库

java实现表格数据导入数据库

1、pom.xml配置，需要先引用poi的jar包

        <dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.14</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.14</version>
		</dependency>

2、在数据库中创建题目表(以oracle数据库为例)

名称	类型	注释
ID	number(10)	id
TOPICNAME	varchar(300)	题目
A	number(10)	选项A
B	number(10)	选项B
C	number(10)	选项C
D	number(10)	选项D
ANSWERS	number(10)	正确答案

3、创建实体类

4、导入须知

前端需要先把表格传到服务器上，再给后端返回一个文件在服务器中的路径，后端接收这个路径把表格数据保存到数据库中。

5、主要代码

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import xyj.eentity.Xcx_TestTopic;

public void ExperTopic(String filePath) {		
			File file = null;
			FileInputStream fs = null;
			List<Xcx_TestTopic> list = new ArrayList<Xcx_TestTopic>();
			try {
				file = new File(filePath);
				fs = new FileInputStream(file);
			} catch (Exception e) {
				throw new RuntimeException("读取文件失败");
			}
			String fileNameAll = file.getName();
			String[] split = fileNameAll.split("\\.");
			if (split.length != 2) {
				throw new RuntimeException("文件名格式错误");
			}
			String formateStr = split[1];
			// 读取文件
			if ("xls".equals(formateStr)) {
				// 是 xls 文件 用HSS
				try {
					HSSFWorkbook wb = new HSSFWorkbook(fs);
					HSSFSheet sheet = wb.getSheetAt(0);
					for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
						HSSFRow hssfRow = sheet.getRow(rowNum);
						if (hssfRow == null) {
							continue;
						}
						Xcx_TestTopic testTopic = new Xcx_TestTopic();
						// 题目
						HSSFCell cell = hssfRow.getCell(1);
						if (cell == null) {
							continue;
						}
						String topicName = cell.getStringCellValue();
						if (StringUtils.isBlank(topicName)) {
							// 如果题目为空 跳过这一行
							throw new RuntimeException("文件中第"+(rowNum-1)+"行题目为空");
						}
						testTopic.setTopicName(topicName);
						//A
						cell = hssfRow.getCell(2);
						if (cell == null) {
							continue;
						}
						String A = cell.getStringCellValue();
						if (StringUtils.isBlank(A)) {
							throw new RuntimeException("文件中第"+(rowNum-1)+"行选项A为空");
						}
						testTopic.setA(A);
						
						//B
						cell = hssfRow.getCell(3);
						if (cell == null) {
							continue;
						}
						String B = cell.getStringCellValue();
						if (StringUtils.isBlank(B)) {
							throw new RuntimeException("文件中第"+(rowNum-1)+"行选项B为空");
						}
						testTopic.setB(B);
						
						//C
						cell = hssfRow.getCell(4);
						if (cell == null) {
							continue;
						}
						String C = cell.getStringCellValue();
						if (StringUtils.isBlank(C)) {
							throw new RuntimeException("文件中第"+(rowNum-1)+"行选项C为空");
						}
						testTopic.setC(C);
						
						//D
						cell = hssfRow.getCell(5);
						if (cell == null) {
							continue;
						}
						String D = cell.getStringCellValue();
						if (StringUtils.isBlank(D)) {
							throw new RuntimeException("文件中第"+(rowNum-1)+"行选项D为空");
						}
						testTopic.setD(D);
						
						//正确答案
						cell = hssfRow.getCell(6);
						if (cell == null) {
							continue;
						}
						//如果该列数据是纯数字，则用这个方法
						Double answers = cell.getNumericCellValue();
						if (answers == null) {
							throw new RuntimeException("文件中第"+(rowNum-1)+"行正确答案为空");
						}
						Long longValue = answers.longValue();
						testTopic.setAnswers(longValue.toString());				
						xcx_TestTopicDao.saveTestTopic(testTopic);
					}
				} catch (IOException e) {
					e.printStackTrace();
				} finally {
					try {
						fs.close();
					} catch (IOException e) {
						throw new RuntimeException("导入异常");
					}
					file.delete();
				}
				
			} else if ("xlsx".equals(formateStr)) {
				// 如果是 xlsx 用XSS
				try {
					XSSFWorkbook xwb = new XSSFWorkbook(fs);
					XSSFSheet sheet = xwb.getSheetAt(0);
					for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
						XSSFRow xssfRow = sheet.getRow(rowNum);
						if (xssfRow == null) {
							continue;
						}
						//创建实体类对象
						Xcx_TestTopic testTopic = new Xcx_TestTopic();
						XSSFCell cell = xssfRow.getCell(1);
						if (cell == null) {
							continue;
						}
						String topicName = cell.getStringCellValue();
						if (StringUtils.isBlank(topicName)) {
							// 如果题目为空 跳过这一行
							throw new RuntimeException("文件中第"+(rowNum-1)+"行题目为空");
						}
						testTopic.setTopicName(topicName);;
						//A
						cell = xssfRow.getCell(2);
						if (cell == null) {
							continue;
						}
						String A = cell.getStringCellValue();
						if (StringUtils.isBlank(A)) {
							throw new RuntimeException("文件中第"+(rowNum-1)+"行选项A为空");
						}
						testTopic.setA(A);
						
						//B
						cell = xssfRow.getCell(3);
						if (cell == null) {
							continue;
						}
						String B = cell.getStringCellValue();
						if (StringUtils.isBlank(B)) {
							throw new RuntimeException("文件中第"+(rowNum-1)+"行选项B为空");
						}
						testTopic.setB(B);
						
						//C
						cell = xssfRow.getCell(4);
						if (cell == null) {
							continue;
						}
						String C = cell.getStringCellValue();
						if (StringUtils.isBlank(C)) {
							throw new RuntimeException("文件中第"+(rowNum-1)+"行选项C为空");
						}
						testTopic.setC(C);
						
						//D
						cell = xssfRow.getCell(5);
						if (cell == null) {
							continue;
						}
						String D = cell.getStringCellValue();
						if (StringUtils.isBlank(D)) {
							throw new RuntimeException("文件中第"+(rowNum-1)+"行选项D为空");
						}
						testTopic.setD(D);
						
						//正确答案
						cell = xssfRow.getCell(6);
						if (cell == null) {
							continue;
						}
						Double answers = cell.getNumericCellValue();
						if (answers == null) {
							throw new RuntimeException("文件中第"+(rowNum-1)+"行正确答案为空");
						}
						Long longValue = answers.longValue();
						testTopic.setAnswers(longValue+"");
						
						xcx_TestTopicDao.saveTestTopic(testTopic);
					}
				} catch (Exception e) {
					throw new RuntimeException(e.getMessage());
				} finally {
					try {
						fs.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
					file.delete();
				}
			} else {
				throw new RuntimeException("不支持格式");
			}
		}else{
			throw new RuntimeException("考试已经开始/结束，无法上传试题");
		}
	}

6、测试

页面：

数据库：

7、附件

表格样式

8、补充

如遇到表格上传类型转换问题可以看看我的补充文章：https://blog.csdn.net/qq_42382972/article/details/86132227