it经验啦

  1. 首页 > it经验 > >

layui 上传excel，数据插入到数据库

Excel

前端代码:
Title

请选择您要上传的Excel文件
实体类:
package com.pojo;/** * 试题表 * */public class Topic {private Integer id;private String title;private String answerA;private String answerB;private String answerC;private String answerD;private int isMore;private String answer;private int sid;private int grade;public Topic() {}public Topic(String title, String answerA, String answerB, String answerC, String answerD, int isMore, String answer, int sid, int grade) {this.title = title;this.answerA = answerA;this.answerB = answerB;this.answerC = answerC;this.answerD = answerD;this.isMore = isMore;this.answer = answer;this.sid = sid;this.grade = grade;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}public String getAnswerA() {return answerA;}public void setAnswerA(String answerA) {this.answerA = answerA;}public String getAnswerB() {return answerB;}public void setAnswerB(String answerB) {this.answerB = answerB;}public String getAnswerC() {return answerC;}public void setAnswerC(String answerC) {this.answerC = answerC;}public String getAnswerD() {return answerD;}public void setAnswerD(String answerD) {this.answerD = answerD;}public int getIsMore() {return isMore;}public void setIsMore(int isMore) {this.isMore = isMore;}public String getAnswer() {return answer;}public void setAnswer(String answer) {this.answer = answer;}public int getSid() {return sid;}public void setSid(int sid) {this.sid = sid;}public int getGrade() {return grade;}public void setGrade(int grade) {this.grade = grade;}}
【layui 上传excel，数据插入到数据库】mapper 接口
package com.mapper;import com.pojo.Topic;import org.apache.ibatis.annotations.Insert;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import java.util.List;public interface TopicMapper {@Select("select * from topic")public ListgetAll();@Insert("")public boolean AddTopic(@Param("list") List list);}
service 接口
package com.service;import com.pojo.Topic;import org.apache.ibatis.annotations.Insert;import org.apache.ibatis.annotations.Select;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.util.List;public interface TopicService {public List getAll();public boolean AddTopic(List list);String UploadExcel(HttpServletRequest request, HttpServletResponse response);}
PowerServiceImpl 实现类
@Servicepublic class TopicServiceImpl implements TopicService {@Autowiredprivate TopicMapper mapper;@Overridepublic List getAll() {return mapper.getAll();}@Overridepublic boolean AddTopic(List list) {return mapper.AddTopic(list);}@Overridepublic String UploadExcel(HttpServletRequest request, HttpServletResponse response) {CommonsMultipartResolver cmr = new CommonsMultipartResolver(request.getServletContext());cmr.setDefaultEncoding("utf-8");cmr.setMaxInMemorySize(40960);cmr.setMaxUploadSize(10485760000L);MultipartFile file=null;if (cmr.isMultipart(request)) {MultipartHttpServletRequest multipartRequest = cmr.resolveMultipart(request);file = multipartRequest.getFile("file");// 与页面input的name相同}if(file.isEmpty()){try {throw new Exception("文件不存在！");} catch (Exception e) {e.printStackTrace();}}InputStream in =null;try {in = file.getInputStream();} catch (IOException e) {e.printStackTrace();}List> listob = null;try {listob = new ExcelUtils().getBankListByExcel(in,file.getOriginalFilename());} catch (Exception e) {e.printStackTrace();}Listlist=new ArrayList<>();for (int i = 0; i < listob.size(); i) {List lo = listob.get(i);Topic vo = new Topic();vo.setTitle(String.valueOf(String.valueOf(lo.get(0))));vo.setAnswerA(String.valueOf(lo.get(1)));vo.setAnswerB(String.valueOf(lo.get(2)));vo.setAnswerC(String.valueOf(lo.get(3)));vo.setAnswerD(String.valueOf(lo.get(4)));vo.setIsMore(Integer.valueOf(String.valueOf(lo.get(5))));vo.setAnswer(String.valueOf(lo.get(6)));vo.setIsMore(Integer.valueOf(String.valueOf(lo.get(7))));vo.setIsMore(Integer.valueOf(String.valueOf(lo.get(8))));list.add(vo);}mapper.AddTopic(list);return "ok";}}Controller 层
@Controllerpublic class TopicController {@Autowiredprivate TopicService service;@GetMapping("/topicAll")public String TopicAddAll(){return "views/topicAll.htm";}@RequestMapping(valuehttps://www.itzhengshu.com/excel/= "https://www.itzhengshu.com/upload",method = {RequestMethod.GET,RequestMethod.POST})@ResponseBodypublic Object upload(HttpServletRequest request, HttpServletResponse response) throws InvocationTargetException, IntrospectionException, IllegalAccessException, IOException, InvalidFormatException {String s=service.ajaxUploadExcel(request,response);if(s.equals("ok"))return JsonRes.res();elsereturn JsonRes.error("导入excel失败！");}}
工具类：
package com.util;import java.io.IOException;import java.io.InputStream;import java.math.BigDecimal;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;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;public class ExcelUtils {private final static String excel2003L =".xls";//2003- 版本的excelprivate final static String excel2007U =".xlsx";//2007版本的excel/*** 描述：获取IO流中的数据 ， 组装成List>对象* @param in,fileName* @return* @throws IOException*/publicList> getBankListByExcel(InputStream in,String fileName) throws Exception{List> list = null;//创建Excel工作薄Workbook work = this.getWorkbook(in,fileName);if(null == work){throw new Exception("创建Excel工作薄为空！");}Sheet sheet = null;//页数Row row = null;//行数Cell cell = null;//列数list = new ArrayList>();//遍历Excel中所有的sheetfor (int i = 0; i < work.getNumberOfSheets(); i) {sheet = work.getSheetAt(i);if(sheet==null){continue;}//遍历当前sheet中的所有行for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j) {row = sheet.getRow(j);if(row==null||row.getFirstCellNum()==j){continue;}//遍历所有的列List li = new ArrayList();for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y) {cell = row.getCell(y);li.add(this.getValue(cell));}list.add(li);}}return list;}/*** 描述：根据文件后缀，自适应上传文件的版本* @param inStr,fileName* @return* @throws Exception*/publicWorkbook getWorkbook(InputStream inStr,String fileName) throws Exception{Workbook wb = null;String fileType = fileName.substring(fileName.lastIndexOf("."));if(excel2003L.equals(fileType)){wb = new HSSFWorkbook(inStr);//2003-}else if(excel2007U.equals(fileType)){wb = new XSSFWorkbook(inStr);//2007}else{throw new Exception("解析的文件格式有误！");}return wb;}/*** 描述：对表格中数值进行格式化* @param cell* @return*///解决excel类型问题，获得数值publicString getValue(Cell cell) {String valuehttps://www.itzhengshu.com/excel/= "";if(null==cell){return value;}switch (cell.getCellType()) {//数值型case Cell.CELL_TYPE_NUMERIC:if (HSSFDateUtil.isCellDateFormatted(cell)) {//如果是date类型则 ，获取该cell的date值Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");value = https://www.itzhengshu.com/excel/format.format(date);;}else {// 纯数字BigDecimal big=new BigDecimal(cell.getNumericCellValue());value = big.toString();//解决1234.0去掉后面的.0if(null!=value&&!"".equals(value.trim())){String[] item = value.split("[.]");if(1pom.xml
org.apache.poipoi3.8-beta3jarcompileorg.apache.poipoi-ooxml3.9jarcommons-fileuploadcommons-fileupload1.4commons-iocommons-io2.6
数据：
title
answerA
answerB
answerC
answerD
isMore
answer
sid
grade
以下选项中符合Python语言变量命名规则的是（ ）
x y z
5_five
_3a
&
0
C
2
2
下列表达式的值为True的是（ ）
4 3>2 5
3>4>2
’a’<’b’
3<1 and 5>2
0
C
2
2
下列选项中运行后输出 3 5 的是（ ）
Print(3 5)
print(‘3’ ’5’)
print(3’ ’5)
print(‘3’ ’ ’ ’5’)
0
D
2
2


相关经验推荐

上一篇：word里如何输入阻力比符号 比号在word里怎么打出来

下一篇：余世维经典珍藏集第五篇｜六大沟通类型，你打算怎么说？