因为业务需要用到导入数据的功能,所以自己也研究了下,记录下来
先看下maven包
<!--poi--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency> <!--上传下载的包--> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.1</version> </dependency> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.4</version> </dependency>
前端的index.jsp 先下载excel模板再填写内容后导入mysql(大致是这个流程)
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta http-equiv="pragma" content="no-cache" /> <base target="_self"> <title>文件上传</title> </head> <body> <a href="test/download.do?fileName=test.xls">下载Exel模板</a> <form method="post" action="test/import.do" enctype="multipart/form-data"> <input type="file" name="files" value="file"> <input type="submit" value="确定"> </form> </body> </html>
mapper的sql语句(我用的测试表里面只有这三个字段)
<!--Excel批量导入--> <insert id="insertInfoBatch" parameterType="java.util.List"> insert into my_test(id,name,endTime) VALUES <foreach collection="list" item="item" index="index" separator=","> (#{item.id},#{item.name},#{item.endTime}) </foreach> </insert>
这是service层方法(注:这里的PageData等同于map)
//excel批量导入数据 public void importExcelInfo(InputStream in, MultipartFile file) throws Exception { List<List<Object>> listob = ExcelUtil.getBankListByExcel(in,file.getOriginalFilename()); List<PageData> salaryList = new ArrayList<>(); //遍历listob数据,把数据放到List中 for (int i = 0; i < listob.size(); i++) { List<Object> ob = listob.get(i); PageData pd=new PageData(); //设置编号 pd.put("id",String.valueOf(ob.get(1))); //通过遍历实现把每一列封装成一个model中,再把所有的model用List集合装载 pd.put("name",String.valueOf(ob.get(2))); pd.put("endTime",String.valueOf(ob.get(3))); //object类型转Double类型 salaryList.add(pd); } //批量插入 dao.batchSave("TestMapper.insertInfoBatch",salaryList); }
因为我用的是SqlSessionTemplate进行增删查改,没有用实体类的方式(框架为ssm)
dao
package com.pskj.JXGSLZ.dao; import java.util.List; public interface Dao { /** * 保存对象 * * @param str * @param obj * @return * @throws Exception */ Object save(String str, Object obj) throws Exception; /** * 批量保存 * @param str * @param objs * @return * @throws Exception */ Object batchSave(String str, List objs)throws Exception; /** * 修改对象 * * @param str * @param obj * @return * @throws Exception */ Object update(String str, Object obj) throws Exception; /** * 删除对象 * * @param str * @param obj * @return * @throws Exception */ Object delete(String str, Object obj) throws Exception; /** * 查找对象 * * @param str * @param obj * @return * @throws Exception */ Object findForObject(String str, Object obj) throws Exception; /** * 查找对象 * * @param str * @param obj * @return * @throws Exception */ Object findForList(String str, Object obj) throws Exception; }
DaoImpl(dao的实现类)
package com.pskj.JXGSLZ.dao.daoImpl; import com.pskj.JXGSLZ.dao.Dao; import org.mybatis.spring.SqlSessionTemplate; import org.springframework.stereotype.Repository; import javax.annotation.Resource; import java.util.List; @Repository("daoImpl") public class DaoImpl implements Dao { @Resource(name="sqlSessionTemplate") private SqlSessionTemplate sqlSessionTemplate; /** * 保存对象 * * @param str * @param obj * @return * @throws Exception */ public Object save(String str, Object obj){ return sqlSessionTemplate.insert(str, obj); } /** * 批量保存 * @param str * @param objs * @return * @throws Exception */ public Object batchSave(String str, List objs ){ return sqlSessionTemplate.insert(str, objs); } /** * 批量保存 * @param str * @param objs * @return * @throws Exception */ // public void batchSave(String str, List objs ){ // SqlSessionFactory sqlSessionFactory=sqlSessionTemplate.getSqlSessionFactory(); // //批量执行器 // SqlSession sqlSession=sqlSessionFactory.openSession(ExecutorType.BATCH,false); // try{ // if(objs!=null){ // for(int i=0,len=objs.size();i<len;i++){ // sqlSession.insert(str,objs.get(i)); // } // sqlSession.flushStatements(); // sqlSession.commit(); // sqlSession.clearCache(); // // } // }catch (Exception e){ // e.printStackTrace(); // }finally { // sqlSession.close(); // } // } /** * 修改对象 * * @param str * @param obj * @return * @throws Exception */ @Override public Object update(String str, Object obj){ return sqlSessionTemplate.update(str,obj); } /** * 删除对象 * * @param str * @param obj * @return * @throws Exception */ public Object delete(String str, Object obj){ return sqlSessionTemplate.delete(str, obj); } /** * 查找对象 * * @param str * @param obj * @return * @throws Exception */ @Override public Object findForObject(String str, Object obj){ return sqlSessionTemplate.selectOne(str, obj); } /** * 查找对象 * * @param str * @param obj * @return * @throws Exception */ @Override public Object findForList(String str, Object obj){ return sqlSessionTemplate.selectList(str, obj); } }
操作excel需要的操作类
ExcelUtil
package com.pskj.JXGSLZ.utils.Excel; 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; import java.io.InputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; /** * @date 2018/10/11 * 导出excel */ public class ExcelUtil { private final static String excel2003L =".xls"; //2003- 版本的excel private final static String excel2007U =".xlsx"; //2007+ 版本的excel /** * Excel导入 */ public static List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception { List<List<Object>> list = null; //创建Excel工作薄 Workbook work = getWorkbook(in,fileName); if(null == work){ throw new Exception("创建Excel工作薄为空!"); } Sheet sheet = null; Row row = null; Cell cell = null; list = new ArrayList<List<Object>>(); //遍历Excel中所有的sheet for (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<Object> li = new ArrayList<Object>(); for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) { cell = row.getCell(y); li.add(getCellValue(cell)); } list.add(li); } } return list; } /** * 描述:根据文件后缀,自适应上传文件的版本 */ public static Workbook 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; } /** * 描述:对表格中数值进行格式化 */ public static Object getCellValue(Cell cell){ Object value = null; DecimalFormat df = new DecimalFormat("0"); //格式化字符类型的数字 SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化 DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字 switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: value = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC: if("General".equals(cell.getCellStyle().getDataFormatString())){ value = df.format(cell.getNumericCellValue()); }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){ value = sdf.format(cell.getDateCellValue()); }else{ value = df2.format(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: value = ""; break; default: break; } return value; } }
再就是controller层(注testService是我自己写的service)
//Excel导入数据 @RequestMapping("import") public void impotr(HttpServletRequest request) { Map map=new HashMap(); //获取上传的文件 MultipartHttpServletRequest multipart = (MultipartHttpServletRequest) request; MultipartFile file = multipart.getFile("files"); try{ InputStream in = file.getInputStream(); //数据导入 testService.importExcelInfo(in,file); in.close();//关闭流 map.put("success",true); }catch(Exception e){ e.printStackTrace(); map.put("success",false); } JsonUtils.writeJson(map, this.getRequest(), this.getResponse()); }
先看看测试表数据
启动项目后的index.jsp页面
点击下载Excel模板得到如下(这是事先制作好的模板)
添加数据进去上传文件
再刷新测试表就可以看到数据了