做第三方团购的平台网站,搜狗广告联盟,公司注册资金500万,大兴模版网站开发公司哪家好前段时间做一个小项目#xff0c;为了同时存储多条数据#xff0c;其中有一个功能是解析Excel并把其中的数据存入对应数据库中。花了两天时间#xff0c;不过一天多是因为用了upload关键字作为URL从而导致总报同一个错#xff0c;最后在同学的帮助下顺利解决为了同时存储多条数据其中有一个功能是解析Excel并把其中的数据存入对应数据库中。花了两天时间不过一天多是因为用了upload关键字作为URL从而导致总报同一个错最后在同学的帮助下顺利解决下面我把自己用POI解析的方法总结出来供大家参考(我用的是SpingMVC和hibernate框架)。1.web.xml中的配置文件web.xml中的配置文件就按照这种方式写只需要把application.xml换成你的配置文件名即可org.springframework.web.context.ContextLoaderListenercontextConfigLocationclasspath:application.xml2.application.xml的配置文件(固定写发)在这个配置文件中你还可以规定上传文件的格式以及大小等多种属性限制classorg.springframework.web.multipart.commons.CommonsMultipartResolver3.文件上传的前端HTML注意1.enctypemultipart/form-data 必须写封装表单2.methodpost提交方式必须为post提交3.action${text}/uploadfile uploadfile切记不要写成upload否则你找到世界末日也不会找到哪里有问题(本人因为这个折腾了一天多时间)。请选择正确的excel文件上传支持的excel格式为xls、xlsx、xlsb、xlsm、xlst4.验证上传文件的格式//用于验证文件扩展名的正则表达式function checkSuffix(){var name document.getElementById(txt).value;var strRegex (.xls|.xlsx|.xlsb|.xlsm|.xlst)$;var renew RegExp(strRegex);if (re.test(name.toLowerCase())){alert(上传成功);document.fileupload.submit();} else{alert(文件名不合法);}}5.dao层的接口和实现类package com.gxxy.team1.yyd.dao;public interface IFileUploadDao {public void save(Object o);}package com.gxxy.team1.yyd.dao.impl;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Repository;import com.gxxy.team1.yyd.dao.IFileUploadDao;Repositorypublic class FileUploadDaoImpl implements IFileUploadDao {Autowiredprivate SessionFactory sessionFactory;private Session getSession() {Session session sessionFactory.getCurrentSession();return session;}Overridepublic void save(Object o) {getSession().save(o);}}6.service层的接口和实现类package com.gxxy.team1.yyd.service;import java.util.List;public interface IFileUploadService {public List readExcel(String path);public void save(Object o);}package com.gxxy.team1.yyd.service.impl;import java.io.File;import java.io.FileInputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;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.ss.usermodel.WorkbookFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import com.gxxy.team1.yyd.dao.IFileUploadDao;import com.gxxy.team1.yyd.service.IFileUploadService;Servicepublic class FileUploadServiceImpl implements IFileUploadService {Autowiredprivate IFileUploadDao fileDao;Overridepublic List readExcel(String path) {SimpleDateFormat fmt new SimpleDateFormat(yyyy-MM-dd);List list null;try {//同时支持Excel 2003、2007File excelFile new File(path); //创建文件对象FileInputStream is new FileInputStream(excelFile); //文件流Workbook workbook WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的int sheetCount workbook.getNumberOfSheets(); //Sheet的数量//存储数据容器list new ArrayList();//遍历每个Sheetfor (int s 0; s sheetCount; s) {Sheet sheet workbook.getSheetAt(s);int rowCount sheet.getPhysicalNumberOfRows(); //获取总行数//遍历每一行for (int r 0; r rowCount; r) {Row row sheet.getRow(r);int cellCount row.getPhysicalNumberOfCells(); //获取总列数//用来存储每行数据的容器String[] model new String[cellCount-1];//遍历每一列for (int c 0; c cellCount; c) {Cell cell row.getCell(c);int cellType cell.getCellType();if(c 0) continue;//第一列ID为标志列不解析String cellValue null;switch(cellType) {case Cell.CELL_TYPE_STRING: //文本cellValue cell.getStringCellValue();//model[c-1] cellValue;break;case Cell.CELL_TYPE_NUMERIC: //数字、日期if(DateUtil.isCellDateFormatted(cell)) {cellValue fmt.format(cell.getDateCellValue()); //日期型//model[c-1] cellValue;}else {cellValue String.valueOf(cell.getNumericCellValue()); //数字//model[c-1] cellValue;}break;case Cell.CELL_TYPE_BOOLEAN: //布尔型cellValue String.valueOf(cell.getBooleanCellValue());break;case Cell.CELL_TYPE_BLANK: //空白cellValue cell.getStringCellValue();break;case Cell.CELL_TYPE_ERROR: //错误cellValue 错误;break;case Cell.CELL_TYPE_FORMULA: //公式cellValue 错误;break;default:cellValue 错误;}System.out.print(cellValue );model[c-1] cellValue;}//model放入list容器中list.add(model);System.out.println();}}is.close();}catch (Exception e) {e.printStackTrace();}return list;}Overridepublic void save(Object o) {fileDao.save(o);}}7.controller层实现//文件上传方法RequestMapping(/uploadfile)public String upload(RequestParam(value file, required false) MultipartFile file, HttpServletRequest request, ModelMap model,Model mod) throws Exception {String path request.getSession().getServletContext().getRealPath(upload);System.out.println(文件路径path);String originalFilename file.getOriginalFilename();String type file.getContentType();//originalFilename UUID.randomUUID().toString()originalFilename;System.out.println(目标文件名称originalFilename,目标文件类型type);File targetFile new File(path,originalFilename );if (!targetFile.getParentFile().exists()) {targetFile.getParentFile().mkdirs();}else if (!targetFile.exists()) {targetFile.mkdirs();}// 获得上传文件的文件扩展名String subname originalFilename.substring(originalFilename.lastIndexOf(.)1);System.out.println(文件的扩展名subname);try {file.transferTo(targetFile);} catch (Exception e) {e.printStackTrace();}FileUploadServiceImpl fileUp new FileUploadServiceImpl();String rootpath path File.separator originalFilename;List excellist fileUp.readExcel(rootpath);int len excellist.size();System.out.println(集合的长度为len);for (int i 0; i len; i) {String[] fields excellist.get(i);SimpleDateFormat format new SimpleDateFormat(yyyy-MM-dd);String sampleNo fields[0];Double valueOf Double.valueOf(fields[1]);int sampleType valueOf.intValue(); //double转intString createTime fields[2];Date createTime1 format.parse(createTime);String name fields[3];String pId fields[4];String hospitalName fields[5];String cellPhone fields[6];Sample sample new Sample(sampleNo, sampleType, createTime1, name, pId);Patient patient new Patient(hospitalName, cellPhone);fileService.save(sample);fileService.save(patient);}//model.addAttribute(fileUrl, request.getContextPath()/upload/originalFilename);String username (String) request.getSession().getAttribute(username);List power powerService.power(username);mod.addAttribute(list, power);return redirect:/ yyd;}以上这7个部分就是我实现解析excel文件并存入数据库的全部代码。希望对大家的学习有所帮助也希望大家多多支持脚本之家。
