html文件代码如下:
<!doctype html> <html> <head> <meta charset="utf-8"> <title></title> <link href="${base}/financing/css/index.css" rel="stylesheet" type="text/css"> <script type="text/javascript" src="${base}/financing/js/jquery.min.js"></script> <script type="text/javascript" src="${base}/financing/js/common.js"></script> <script type="text/javascript" src="${base}/financing/js/search.js"></script> <script type="text/javascript" src="${base}/financing/js/product.js"></script> <script type="text/javascript" src="${base}/financing/js/index.js"></script> </head> <body> <form method="POST" enctype="multipart/form-data" id="form" action="${base}/upfile/poistudy.action"> <input type="file" id="upfile" name="upfile"> <input type="button" name="checkSAPmain" id="checkSAPmain" value="导入数据" onclick="submit();" Class="listLink" > </form> </body> <script> function submit(){ var form = document.getElementById("form"); form.submit(); } </script> </html>
控制类(Controller文件poistudyAction):
package cn.fulong.cgn.web.action.poistudy; import java.io.IOException; import java.io.InputStream; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpSession; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.multipart.MultipartFile; import org.springframework.web.multipart.MultipartHttpServletRequest; import cn.fulong.common.web.action.BaseAction; import cn.fulong.entity.InfoVo; import cn.fulong.entity.SysUser; import cn.fulong.util.ImportExcelUtil; @Controller @RequestMapping("/upfile") public class poistudyAction extends BaseAction { /** * 解析excel * @throws Exception */ @RequestMapping("/kkkk") public String kkk(Model model,HttpServletRequest request) throws Exception{ System.out.println("123456789"); return "/poistudy/upfile"; } @RequestMapping(value="/poistudy",method={RequestMethod.GET,RequestMethod.POST}) public String top(Model model,HttpServletRequest request) throws Exception{ MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; System.out.println("通过传统方式form表单提交方式导入excel文件！"); 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); InfoVo vo = new InfoVo(); vo.setCode(String.valueOf(lo.get(0))); vo.setName(String.valueOf(lo.get(1))); vo.setDate(String.valueOf(lo.get(2))); vo.setMoney(String.valueOf(lo.get(3))); System.out.println("打印信息-->机构:"+vo.getCode()+" 名称："+vo.getName()+" 时间："+vo.getDate()+" 资产："+vo.getMoney()); } return "/poistudy/upfile"; } }
实体类(InfoVo):
package cn.fulong.entity; public class InfoVo { private String code; private String name; private String date; private String money; public String getCode() { return code; } public void setCode(String code) { this.code = code; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getDate() { return date; } public void setDate(String date) { this.date = date; } public String getMoney() { return money; } public void setMoney(String money) { this.money = money; } }
工具类(ImportExcelUtil)
package cn.fulong.util; 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中的所有行 System.out.println(sheet.getFirstRowNum()); System.out.println(sheet.getLastRowNum()); 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); } } // work.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; } }
使用的是Spring MVC 但是其他的MVC框架和Spring MVC差不多的.
POI所用的jar包:http://pan.baidu.com/s/1eRYC6o6