maven环境下的ssm框架上传excel 案例
1、maven环境下,pom.xml的配置:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency>
2、Spring配置文件spring-servlet.xml:
<!-- 支持上传文件 --> <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> <property name="defaultEncoding" value="utf-8"></property> <property name="maxUploadSize" value="10485760000"></property> <property name="maxInMemorySize" value="40960"></property> </bean>
3、ExcelUtil
package com.hyc.www.utils; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelUtil { public static List<String> getDataByExcel(InputStream is) throws IOException { List<String> result = new ArrayList<String>(); //创建Excel,读取文件内容 XSSFWorkbook workbook = null; //获取第一个工作表 XSSFSheet sheet = null; try { workbook = new XSSFWorkbook(is); //获取第一个工作表 sheet = workbook.getSheetAt(0); //获取sheet中第一行行号 int firstRowNum = sheet.getFirstRowNum(); //获取sheet中最后一行行号 int lastRowNum = sheet.getLastRowNum(); //循环插入数据 for(int i=firstRowNum+1;i<=lastRowNum;i++){ XSSFRow row = sheet.getRow(i); XSSFCell goodsNumCell = row.getCell(0);// if(goodsNumCell!=null){ String goodsNum = goodsNumCell.getStringCellValue(); System.out.println("-------------------goodsNum:"+goodsNum); result.add(goodsNum); } } } catch (Exception e) { e.printStackTrace(); } finally { if(workbook!=null) { workbook.close(); } } return result; } }
4、Controller:
@RequestMapping(value="/uploadExcel",method=RequestMethod.POST,produces = "text/html;charset=UTF-8") @ResponseBody public String uploadExcel(@RequestParam("file") CommonsMultipartFile file,HttpServletRequest request) { String inId = request.getParameter("inId"); System.out.println("-------------------inId:"+inId); String skuCode = request.getParameter("skuCode"); System.out.println("-------------------skuCode:"+skuCode); File fo = new File(file.getOriginalFilename()); try { FileUtils.copyInputStreamToFile(file.getInputStream(),fo); List<String> result = ExcelUtil.getDataByExcel(FileUtils.openInputStream(fo)); return "success"; } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); return "fail"; } }
5、前端页面:
使用jquery.form.js 插件异步上传文件:
<form id="formDemo"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal"><span aria-hidden="true">×</span><span class="sr-only">Close</span></button> <h4 class="modal-title">导入产品序列号</h4> </div> <div class="modal-body"> <div class="row"> <div class="form-group" style="text-align: center;"> <label title="选择文件" for="inputImage" class="btn btn-primary" > <input type="file" accept="excel/*" name="file" id="inputImage" > </label> </div> </div> </div> <div class="modal-footer"> <button type="button" class="btn btn-white" data-dismiss="modal">关闭</button> <button type="button" class="btn btn-primary" id="importBtnSave">上传</button> </div> </form>
//上传导入文件 $("#importBtnSave").click(function(){ let selectedRows = $("#editStockInDetailTab").bootstrapTable('getSelections'); if (selectedRows.length <= 0) { alert('请选择要导入的数据'); return; } if (selectedRows.length > 1) { alert('一次只能选择一行导入'); return; } let skuCode = selectedRows[0].skuCode; let inId = $("#editInId").val(); $("#formDemo").ajaxSubmit({ url: baseUrl + '/Inventory/repair/StockIn/uploadExcel?inId='+inId+'&skuCode='+skuCode, type: "post", /*设置表单以post方法提交*/ dataType: "json", /*设置返回值类型为文本*/ success: function (data) { alert(data); console.info(data); }, error: function (error) { alert(error); console.info(error); } }); });