springMVC(5)---导入excel文件数据到数据库
springMVC(5)---导入excel文件数据到数据库
上一篇文章写了从数据库导出数据到excel文件，这篇文章悄悄相反，写的是导入excel文件数据到数据库。上一篇链接：springMVC(4)---生成excel文件并导出
直接进入步骤讲解：
第一步、导入jar包
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency>
第二步，创建Model对象
我没有把数据放到数据库中，而是导入到实体中，然后在后台打印
public class Family { //家庭编号 private String jtbh; //姓名 private String xm; //行业 private String hy; //备注 private String bz; /* * 提供set和get，toString方法 */ }
第三步.导入excel界面 leadingexcel.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <html> <head> <script type="text/javascript" src="../js/jquery-1.7.1.js"></script> <script type="text/javascript" src="../js/jquery.form.js"></script> <script type="text/javascript"> /* ajax 方式上传文件操作 */ $(document).ready(function(){ $("#btn").click(function(){ if(checkData()){ $('#form1').ajaxSubmit({ url:'uploadExcel/ajax', dataType: 'text', success: resutlMsg, error: errorMsg }); function resutlMsg(msg){ alert(msg); $("#upfile").val(""); } function errorMsg(){ alert("导入excel出错！"); } } }); }); //JS校验form表单信息 function checkData(){ var fileDir = $("#upfile").val(); var suffix = fileDir.substr(fileDir.lastIndexOf(".")); if("" == fileDir){ alert("选择需要导入的Excel文件！"); return false; } if(".xls" != suffix && ".xlsx" != suffix ){ alert("选择Excel格式的文件导入！"); return false; } return true; } </script> </head> <body> <form method="POST" enctype="multipart/form-data" id="form1" action="uploadExcel/form"> <label>上传文件: </label> <input id="upfile" type="file" name="upfile"><br> <br> <input type="submit" value="表单提交" onclick="return checkData()"> <input type="button" value="ajax提交" id="btn" name="btn" > </form> </body> </html>
先讲下，我这src引用路径的时候发现，怎么也引用不到，找了好久才发现，我在springmvc中没有配置静态文件
springmvc.xml
<!-- 静态资源访问 --> <mvc:default-servlet-handler/> <!-- 当我仅配置上面的时候又发现src是引用到了，但是我的RequestMapping映射却变成请求不到了，所以下面的也一定要加上 --> <mvc:annotation-driven></mvc:annotation-driven>
第四步、LeadingExcelController.java
import java.io.InputStream; import java.io.PrintWriter; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.multipart.MultipartFile; import org.springframework.web.multipart.MultipartHttpServletRequest; import com.ssm.model.Family; import com.ssm.service.impl.ImportExcelUtil; @Controller @RequestMapping("/jsp/uploadExcel") public class LeadingExcelController { @RequestMapping("/form") public String form(HttpServletRequest request)throws Exception{ MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; InputStream in =null; List<List<Object>> listob = null; MultipartFile file = multipartRequest.getFile("upfile"); if(file.isEmpty()){ throw new Exception("文件不存在！"); } in = file.getInputStream(); listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename()); in.close(); //该处可调用service相应方法进行数据保存到数据库中，现只对数据输出 for (int i = 0; i < listob.size(); i++) { List<Object> lo = listob.get(i); Family family = new Family(); family.setJtbh(String.valueOf(lo.get(0))); family.setXm(String.valueOf(lo.get(1))); family.setHy(String.valueOf(lo.get(2))); family.setBz(String.valueOf(lo.get(3))); System.out.println("打印信息-->"+family.toString()); } return null; } @RequestMapping(value="/ajax") public void ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response) throws Exception { MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; InputStream in =null; List<List<Object>> listob = null; MultipartFile file = multipartRequest.getFile("upfile"); if(file.isEmpty()){ throw new Exception("文件不存在！"); } in = file.getInputStream(); listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename()); //该处可调用service相应方法进行数据保存到数据库中，现只对数据输出 for (int i = 0; i < listob.size(); i++) { List<Object> lo = listob.get(i); Family family = new Family(); family.setJtbh(String.valueOf(lo.get(0))); family.setXm(String.valueOf(lo.get(1))); family.setHy(String.valueOf(lo.get(2))); family.setBz(String.valueOf(lo.get(3))); System.out.println("打印信息-->"+family.toString()); } PrintWriter out = null; response.setCharacterEncoding("utf-8"); //防止ajax接受到的中文信息乱码 out = response.getWriter(); out.print("文件导入成功！"); out.flush(); out.close(); } }
第五步、ImportExcelUtil.java 报表导入实现层
import java.io.IOException; import java.io.InputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; 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 ImportExcelUtil { private final static String excel2003L =".xls"; //2003- 版本的excel private final static String excel2007U =".xlsx"; //2007+ 版本的excel /** * 描述：获取IO流中的数据，组装成List<List<Object>>对象 * @param in,fileName * @return * @throws IOException */ public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{ List<List<Object>> 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<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(this.getCellValue(cell)); } list.add(li); } } in.close(); return list; } /** * 描述：根据文件后缀，自适应上传文件的版本 * @param inStr,fileName * @return * @throws Exception */ public 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; } /** * 描述：对表格中数值进行格式化 * @param cell * @return */ public Object getCellValue(Cell cell){ Object value = null; DecimalFormat df = new DecimalFormat("0"); //格式化number String字符 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; } }
第六步：最终效果，当我点击通过表单和ajax提交时
(1)先展示上传的xls文件内容
(2)最终控制台打出：
(3)通过ajax导入成功，前端也会提示
完美！
想的太多，做的太少，中间的落差就是烦恼，要么去做，要么别想 少尉【12】
