java实现Excel导入数据库，并返回导入信息
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了java实现Excel导入数据库，并返回导入信息，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含23189字，纯文字阅读大概需要34分钟。
内容图文
工具类：
工具类中的内容可以根据自己的情况进行修改
package com.tphy.nursing.util;
import com.tphy.nursing.exam.bean.TestList;
import com.tphy.nursing.personinfo.bean.RYXX;
import java.util.HashMap;
import java.util.Map;
/**
* @author lbz
* @version 1.0
* @date 2020/4/24 11:31
* @description：
*/
public class ExcelResult {
public Map<String,Object> map=new HashMap<String, Object>();
public ExcelResult put(String str, Object obj){
this.map.put(str,obj);
return this;
}
//上传成功信息
public static ExcelResult success(int row, int successNumber, TestList testList){
ExcelResult entity=new ExcelResult();
entity.map.put("statusCode",200);
entity.map.put("message","响应成功");
entity.map.put("row",row);
entity.map.put("successNumber","共"+successNumber+"条数据导入成功");
entity.map.put("testList",testList);
return entity;
}
//上传失败信息
public static ExcelResult error(int row, int failNumber, TestList testList){
ExcelResult entity=new ExcelResult();
entity.map.put("statusCode",500);
entity.map.put("message","响应失败");
entity.map.put("row",row);
entity.map.put("failNumber","共"+failNumber+"条数据导入失败");
entity.map.put("testList",testList);
return entity;
}
public static ExcelResult errorMessage(){
ExcelResult entity=new ExcelResult();
entity.map.put("statusCode",500);
entity.map.put("message","响应失败");
return entity;
}
//上传内容有误
public static ExcelResult errorMessage(String errorMessage,int row, TestList testList){
ExcelResult entity=new ExcelResult();
entity.map.put("statusCode",500);
entity.map.put("message","响应失败");
entity.map.put("errorMessage",errorMessage);
entity.map.put("row",row);
entity.map.put("testList",testList);
return entity;
}
//上传成功信息(人员列表)
public static ExcelResult successRylb(int row, int successNumber, RYXX ryxx){
ExcelResult entity=new ExcelResult();
entity.map.put("statusCode",200);
entity.map.put("message","响应成功");
entity.map.put("row",row);
entity.map.put("successNumber","共"+successNumber+"条数据导入成功");
entity.map.put("ryxx",ryxx);
return entity;
}
//上传失败信息(人员列表)
public static ExcelResult errorRylb(int row, int failNumber, RYXX ryxx){
ExcelResult entity=new ExcelResult();
entity.map.put("statusCode",500);
entity.map.put("message","响应失败");
entity.map.put("row",row);
entity.map.put("failNumber","共"+failNumber+"条数据导入失败");
entity.map.put("ryxx",ryxx);
return entity;
}
//上传内容有误(人员列表)
public static ExcelResult errorMessageRylb(String errorMessage,int row, RYXX ryxx){
ExcelResult entity=new ExcelResult();
entity.map.put("statusCode",500);
entity.map.put("message","响应失败");
entity.map.put("errorMessage",errorMessage);
entity.map.put("row",row);
entity.map.put("ryxx",ryxx);
return entity;
}
}
Controller类：
Excel2003版本(.xls)的和2007版本(.xlsx)的写法不同，具体在创建文档的方法不同
package com.tphy.nursing.exam.controller;
import com.alibaba.fastjson.JSON;
import com.tphy.nursing.common.bean.Data;
import com.tphy.nursing.exam.bean.*;
import com.tphy.nursing.exam.mapper.STKMMapper;
import com.tphy.nursing.exam.mapper.STLXMapper;
import com.tphy.nursing.exam.mapper.TestListMapper;
import com.tphy.nursing.login.bean.Login;
import com.tphy.nursing.util.*;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;
import java.io.File;
import java.io.FileInputStream;
import java.util.List;
/**
* @author ：lqw
* @date ：Created in 2020/3/16 15:28
* @description：试题导入
* @modified By：
* @version: 1
*/
@Controller
@RequestMapping("/excel")
public class ExcelController {
@Autowired
private STLXMapper stlxMapper;
@Autowired
private STKMMapper stkmMapper;
@Autowired
private GetProcedure getProcedure;
@Autowired
private TestListMapper testListMapper;
/**
* create by: lbz
* description: excel试题导入.xls
* create time: 2020/3/16 15:29
*
* @param :
* @return * @return : null
*/
@RequestMapping(value = "/import_2003")
@ResponseBody
public ArrayList<ExcelResult> import_2003(@RequestParam("file") MultipartFile file,
HttpServletRequest request, HttpServletResponse response) {
HttpSession session = request.getSession();
Login info = (Login)session.getAttribute("info");
try {
ArrayList<ExcelResult> excelResults = new ArrayList<>();
// @RequestParam("file") MultipartFile file 是用来接收前端传递过来的文件
// 1.创建workbook对象，读取整个文档
InputStream inputStream = file.getInputStream();
POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
HSSFWorkbook wb = new HSSFWorkbook(poifsFileSystem);
// 2.读取页脚sheet
HSSFSheet sheetAt = wb.getSheetAt(0);
// 3.循环读取某一行
HSSFSheet sheet = wb.getSheetAt(0);
System.out.println("sheet对象：" + sheet);
//获取最后一行的num，即总行数。此处从0开始计数
int maxRow = sheet.getLastRowNum();
System.out.println("总行数为：" + maxRow);
List<TestList> testLists = new ArrayList<>();
int successNumber = 0; //成功条数
int failNumber = 0; //失败条数
int flag = 1; //标识符
//从第三行开始添加
for (int row = 2; row <= maxRow; row++) {
flag = 1; //定义初始标识符，如果为1则excel内容无错，进行插入，如果为0则excel内容有误，将相应的错误信息存到工具类中返回
//获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
int maxRol = sheet.getRow(row).getLastCellNum();
System.out.println("总列数为："+maxRol);
System.out.println("--------第" + row + "行的数据如下--------");
TestList tl = new TestList();
tl.setCjsj(new Date());
tl.setStdm(getProcedure.getEightCode("nms_code_stdm_id"));
tl.setTplj(null);
tl.setSxh(Integer.valueOf(getProcedure.getEightCode("nms_code_stdm_sxh")));
tl.setXgsj(new Date());
tl.setCtr(info.getUsername());
for (int rol = 0; rol < maxRol; rol++){
if(rol==0){
String stmc = (sheet.getRow(row).getCell(rol)+"").equals("") ? " ":sheet.getRow(row).getCell(rol)+""; //判断试题名称是否为空
String pydm = (sheet.getRow(row).getCell(rol)+"").equals("") ? " ":sheet.getRow(row).getCell(rol)+""; //判断拼音代码是否为空
tl.setStmc(stmc);
tl.setPydm(pydm);
}
if(rol ==1){
System.out.println("显示标志："+sheet.getRow(row).getCell(rol)+"");
//显示标志为空
if((sheet.getRow(row).getCell(rol)) == null){
excelResults.add( ExcelResult.errorMessage("显示标志为空",row,tl));
flag = 0;
}else{
if((sheet.getRow(row).getCell(rol)+"").equals("1.0")){ //将1.0变为1插入数据库
tl.setXsbz("1");
}
if((sheet.getRow(row).getCell(rol)+"").equals("0.0")){ //将0.0变为1插入数据库
tl.setXsbz("0");
}
// tl.setXsbz(sheet.getRow(row).getCell(rol)+"");
}
}
if(rol ==2){
STLXExample example = new STLXExample();
STLXExample.Criteria criteria = example.createCriteria();
criteria.andLxmcLike(sheet.getRow(row).getCell(rol)+"");
List<STLX> stlxes = stlxMapper.selectByExample(example);
System.out.println("stlxes:"+stlxes);
if(stlxes.size() == 0){
excelResults.add( ExcelResult.errorMessage("试题类型有误",row,tl));
flag = 0;
}else{
tl.setStlx(stlxes.get(0).getLxdm());
}
}
if(rol ==3){
System.out.println("试题题干：" + sheet.getRow(row).getCell(rol));
//试题题干为空
if((sheet.getRow(row).getCell(rol)+"").equals("") || (sheet.getRow(row).getCell(rol)) == null){
excelResults.add( ExcelResult.errorMessage("试题题干为空",row,tl));
flag = 0;
}else{
tl.setSttg(sheet.getRow(row).getCell(rol)+"");
}
}
if(rol ==4){
System.out.println("试题类型：" + tl.getStlx());
//单选和多选的试题答案只能是大写英文
if(tl.getStlx() == "" || tl.getStlx() == null){
flag = 0;
}else if(tl.getStlx().equals("01") || tl.getStlx().equals("02")){
int length = (sheet.getRow(row).getCell(rol)+"").split(",").length;
for(int i = 0; i < length; i ++){
//将试题答案替换为字符串并进行判断
if(!((sheet.getRow(row).getCell(rol)+"").replaceAll(",","").charAt(i) >= 'A' && (sheet.getRow(row).getCell(rol)+"").replaceAll(",","").charAt(i) <= 'Z')){
excelResults.add( ExcelResult.errorMessage("单选或多选题答案只能为英文字母大写",row,tl));
flag = 0;
}
}
if(flag == 1){
tl.setStda(sheet.getRow(row).getCell(rol)+"");
}
}else if(tl.getStlx().equals("03")){ //判断题
System.out.println("判断答案：" + (sheet.getRow(row).getCell(rol)+""));
if(!((sheet.getRow(row).getCell(rol)+"").equals("1.0") || (sheet.getRow(row).getCell(rol)+"").equals("0.0"))){
excelResults.add( ExcelResult.errorMessage("判断题答案只能为1(正确)或0(错误)",row,tl));
flag = 0;
}else{
if((sheet.getRow(row).getCell(rol)+"").equals("1.0")){ //将1.0变为1插入数据库
tl.setStda("1");
}
if((sheet.getRow(row).getCell(rol)+"").equals("0.0")){ //将0.0变为1插入数据库
tl.setStda("0");
}
}
}else{ //填空或主管
//试题答案为空
if((sheet.getRow(row).getCell(rol)+"").equals("")){
excelResults.add( ExcelResult.errorMessage("试题答案为空",row,tl));
flag = 0;
}else{
tl.setStda(sheet.getRow(row).getCell(rol)+"");
}
}
}
if(rol ==5){
STKMExample example = new STKMExample();
STKMExample.Criteria criteria = example.createCriteria();
criteria.andMcLike(sheet.getRow(row).getCell(rol)+"");
List<STKM> stkms = stkmMapper.selectByExample(example);
System.out.println("stkms:"+stkms);
if(stkms.size() == 0){
excelResults.add( ExcelResult.errorMessage("试题科目有误",row,tl));
flag = 0;
}else{
tl.setStkm(stkms.get(0).getDm());
}
// tl.setStkm(stkms.get(0).getDm());
}
}
System.out.println("&&&&&&&&&&:" + tl); //每一条数据具体内容
if(flag == 1){
int i = testListMapper.insert(tl);
if(i>0){
successNumber ++;
excelResults.add(ExcelResult.success(row,successNumber,tl));
}else{
failNumber ++;
excelResults.add(ExcelResult.error(row,failNumber,tl));
}
}
}
return excelResults;
} catch (Exception e) {
System.out.println("异常捕获");
ArrayList<ExcelResult> excelResults = new ArrayList<>();
e.printStackTrace();
excelResults.add(ExcelResult.errorMessage());
return excelResults;
}
}
@RequestMapping("/import_2007")
@ResponseBody
public ArrayList<ExcelResult> import_2007(@RequestParam("file") MultipartFile file,
HttpServletRequest request, HttpServletResponse response){
HttpSession session = request.getSession();
Login info = (Login)session.getAttribute("info");
try {
ArrayList<ExcelResult> excelResults = new ArrayList<>();
InputStream inputStream = file.getInputStream();
//创建工作簿
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
System.out.println("xssfWorkbook对象：" + xssfWorkbook);
//读取第一个工作表
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
System.out.println("sheet对象：" + sheet);
//获取最后一行的num，即总行数。此处从0开始计数
int maxRow = sheet.getLastRowNum();
List<TestList> testLists = new ArrayList<>();
System.out.println("总行数为：" + maxRow);
int successNumber = 0; //成功条数
int failNumber = 0; //失败条数
int flag = 1; //标识符
//从第3行开始插入
for (int row = 2; row <= maxRow; row++) {
/*
* 代码同上
*/
}
System.out.println("&&&&&&&&&&:" + tl); //每一条数据具体内容
if(flag == 1){
int i = testListMapper.insert(tl);
if(i>0){
successNumber ++;
excelResults.add(ExcelResult.success(row,successNumber,tl));
}else{
failNumber ++;
excelResults.add(ExcelResult.error(row,failNumber,tl));
}
}
}
return excelResults;
} catch (Exception e) {
System.out.println("异常捕获");
ArrayList<ExcelResult> excelResults = new ArrayList<>();
e.printStackTrace();
excelResults.add(ExcelResult.errorMessage());
return excelResults;
}
}
}
前台代码：
使用了layui，相应插件自行去官网下载
<!--上传按钮-->
<a style="background-color: #1890FF;color: white;float: right;margin-right: 25px;margin-top: 10px;height:35px;" class="btn btn-sm btn-space" data-toggle="modal" data-target="#uploadModal" > <img style="vertical-align: text-bottom;" src="../Images/upload.png" height="16" width="15"/> <font class="dbt_style" style="font-size: 16px">上传试题</font> </a>
<!--上传弹窗-->
<div class="modal fade" id="uploadModal" tabindex="-1" role="dialog" aria-labelledby="ModalLabel1" aria-hidden="true">
<div class="modal-dialog" role="document">
<div class="modal-content" style="height: 200px;">
<div class="modal-header">
<div style="color: #333333;font-size: 18px;font-weight: bold;float: left;margin-left: 30px">上传试题</div>
<button onclick="closeWindow()" style="float: right;border: 0px;margin-left: 30px"><img src="../Images/chahao.png" style="background-color:#FFFFFF;display: block;" height="14" width="14"/></button>
</div>
<div style="height: 84px;border-bottom: #dfdfdf solid 1px;margin-top: 30px">
<span style="margin-left: 10px;color:red;font-weight: bold;text-align: right">☆</span>
<font class="tc_bt"> 上传位置选择：</font>
<input id="articleImageFile" name="excelFile" type="file" accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" class="form-control" style="width: 300px; display: inline;" />
<input type="button" id="saveZipButton" class="btn" style="width: 90px;height:35px;margin-right: 40px;font-size: 16px;background-color: #1890ff;color: #ffffff;float: right;font-family: STXihei;" value="上传">
</div>
</div>
</div>
</di<!--上传结果弹窗-->
<div id="uploadResult" style="display: none;font-size: 16px;padding: 0" >
<div id="UploadSuccess" style="min-height: 90px;margin-left: 50px">
<!--拼接上传成功信息-->
</div>
<div style="margin-top: 20px">
<span style="margin-left: 50px;font-family:STXihei;font-weight: bold ;color: #333333;">上传错误</span><br />
</div>
<div id="UploadFail" style="margin-top: 10px;margin-left: 50px;min-height: 50px">
<!--拼接上传失败信息-->
</div>
<div style="min-height: 20px">
</div>
<!--文件的上传-->
$("#saveZipButton").on('click', function(){
var formData = new FormData();
var name = $("#articleImageFile").val();
var index = name.lastIndexOf(".");
var suffix = name.substring(index).toLowerCase();
formData.append("file",$("#articleImageFile")[0].files[0]);
formData.append("name",name);//这个地方可以传递多个参数
if(".xlsx" == suffix){
$("#uploadModal").modal('hide');
$.ajax({
url : "/excel/import_2007",
type : 'POST',
async : false,
data : formData,
// 告诉jQuery不要去处理发送的数据
processData : false,
// 告诉jQuery不要去设置Content-Type请求头
contentType : false,
success : function(res) {
//清空模态窗内容
$("#UploadSuccess").html("");
$("#UploadFail").html("");
//console.log(res);
if (res.map.statusCode==500){
layer.alert(res.message)
}else {
for (var index in res) {
if(res[index].map.statusCode == 200){ //上传正确信息
if(res[index].map.testList.stlx == "01"){
var html = "<div>\n" +
"<img src=\"../Images/true.png\" width=\"20px\" height=\"20px\"> <span>单选题:</span> <span>"+ res[index].map.testList.stmc +"</span><br>\n" +
"</div>"
}
if(res[index].map.testList.stlx == "02"){
var html = "<div>\n" +
"<img src=\"../Images/true.png\" width=\"20px\" height=\"20px\"> <span>多选题:</span> <span>"+ res[index].map.testList.stmc +"</span><br>\n" +
"</div>"
}
if(res[index].map.testList.stlx == "03"){
var html = "<div>\n" +
"<img src=\"../Images/true.png\" width=\"20px\" height=\"20px\"> <span>判断题:</span> <span>"+ res[index].map.testList.sttg +"</span><br>\n" +
"</div>"
}
if(res[index].map.testList.stlx == "04"){
var html = "<div>\n" +
"<img src=\"../Images/true.png\" width=\"20px\" height=\"20px\"> <span>填空题:</span> <span>"+ res[index].map.testList.sttg +"</span><br>\n" +
"</div>"
}
if(res[index].map.testList.stlx == "05"){
var html = "<div>\n" +
"<img src=\"../Images/true.png\" width=\"20px\" height=\"20px\"> <span>主观题:</span> <span>"+ res[index].map.testList.sttg +"</span><br>\n" +
"</div>"
}
$("#UploadSuccess").append(html);
}
if(res[index].map.statusCode == 500){ //上传错误信息
var rownuber = parseInt(res[index].map.row) + 1;
if(res[index].map.testList.stlx == "01"){
var html = " <div>\n" +
"<img src=\"../Images/false.png\" width=\"17px\" height=\"17px\"> <span style=\"color: red\">单选题(第"+ rownuber +"行):</span> <span>"+ res[index].map.errorMessage +"</span><br>\n" +
"</div>"
}else if(res[index].map.testList.stlx == "02"){
var html = " <div>\n" +
"<img src=\"../Images/false.png\" width=\"17px\" height=\"17px\"> <span style=\"color: red\">多选题(第"+ rownuber +"行):</span> <span>"+ res[index].map.errorMessage +"</span><br>\n" +
"</div>"
}else if(res[index].map.testList.stlx == "03"){
var html = " <div>\n" +
"<img src=\"../Images/false.png\" width=\"17px\" height=\"17px\"> <span style=\"color: red\">判断题(第"+ rownuber +"行):</span> <span>"+ res[index].map.errorMessage +"</span><br>\n" +
"</div>"
}else if(res[index].map.testList.stlx == "04"){
var html = " <div>\n" +
"<img src=\"../Images/false.png\" width=\"17px\" height=\"17px\"> <span style=\"color: red\">填空题(第"+ rownuber +"行):</span> <span>"+ res[index].map.errorMessage +"</span><br>\n" +
"</div>"
}else if(res[index].map.testList.stlx == "05"){
var html = " <div>\n" +
"<img src=\"../Images/false.png\" width=\"17px\" height=\"17px\"> <span style=\"color: red\">主观题(第"+ rownuber +"行):</span> <span>"+ res[index].map.errorMessage +"</span><br>\n" +
"</div>"
}else{
var html = " <div>\n" +
"<img src=\"../Images/false.png\" width=\"17px\" height=\"17px\"> <span style=\"color: red\">未知题(第"+ rownuber +"行):</span> <span>"+ res[index].map.errorMessage +"</span><br>\n" +
"</div>"
}
$("#UploadFail").append(html);
}
}
parent.layer.msg('正在校验文件内容，请稍候...', {shade: 0.3},function () {
layer.open({
type: 1,
title: ["上传结果", "color:#333333"], //不显示标题栏
closeBtn: false,
area: '600px;',
shade: 0.3,
id: 'LAY_layuipro', //设定一个id，防止重复弹出
btn: ['确认'],
btnAlign: 'a',
moveType: 1, //拖拽模式，0或者1
content: $("#uploadResult"),
yes: function(index,layero) {
$("#articleImageFile").val(""); //清空选择文件内容
search(pageNum,pageSize);
layer.close(index);
}
});
})
//错误信息窗结束
}
},
error:function (msg) {
layer.msg("程序发生错误，请联系技术人员！");
}
});
}else if(".xls" == suffix){
$("#uploadModal").modal('hide');
$.ajax({
url : "/excel/import_2003",
type : 'POST',
async : false,
dataType: "json",
data : formData,
// 告诉jQuery不要去处理发送的数据
processData : false,
// 告诉jQuery不要去设置Content-Type请求头
contentType : false,
success : function(res) {
//layer.msg('数据加载中，请稍后...',{shade: 0.3},function () {
//清空模态窗内容
$("#UploadSuccess").html("");
$("#UploadFail").html("");
//console.log(res);
if (res.map.statusCode==500){
layer.alert(res.message)
}else {
for (var index in res) {
<!--内容同上->
}
},
error:function (msg) {
layer.msg("程序发生错误，请联系技术人员！");
}
});
}else{
if($("#articleImageFile").val() == null || $("#articleImageFile").val() == "" ){
layer.alert("请选择模板文件！！！");
}else{
layer.alert("文件格式不对，请选择xls或xlsx格式！");
}
}
});
内容总结
以上是互联网集市为您收集整理的java实现Excel导入数据库，并返回导入信息全部内容，希望文章能够帮你解决java实现Excel导入数据库，并返回导入信息所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】