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、附件
表格样式