一.概念介绍
ApachePOI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能
二.功能相关代码
1.环境说明：JDK1.7+tomcat7+spring
2.配置文件的配置
pom文件中添加POI所需依赖
<!-- 添加POI支持 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.13</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.13</version> </dependency>
spring-mvc.xml配置文件上传
<bean id="multipartResolver" > <!-- 默认编码 --> <property name="defaultEncoding" value="utf-8" /> <!-- 文件大小最大值 --> <property name="maxUploadSize" value="10485760000" /> <!-- 内存中的最大值 --> <property name="maxInMemorySize" value="40960" /> </bean>
3.相关工具类及代码编写
Excel解析工具类（ImportExcelUtil.java）
package com.jointem.hrm.utils;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.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.IOException;import java.io.InputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;/** * Created by jenking on 2017/9/8. */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.getLastRowNum()); for (int j = sheet.getFirstRowNum(); j <=sheet.getLastRowNum()-11; 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); if(this.isMergedRegion(sheet,j,y)) { li.add(this.getMergedRegionValue(sheet,j,y)); } else { 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"); //格式化数字 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; } /** * 获取合并单元格的内容 * @param sheet * @param row * @param column * @return */ public Object getMergedRegionValue(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress ca = sheet.getMergedRegion(i); int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); int firstRow = ca.getFirstRow(); int lastRow = ca.getLastRow(); if (row >= firstRow && row <= lastRow) { if (column >= firstColumn && column <= lastColumn) { Row fRow = sheet.getRow(firstRow); Cell fCell = fRow.getCell(firstColumn); return this.getCellValue(fCell); } } } return null; } /** * 判断是否是合并单元格 * @param sheet * @param row * @param column * @return */ public boolean isMergedRegion(Sheet sheet,int row ,int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int firstColumn = range.getFirstColumn(); int lastColumn = range.getLastColumn(); int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); if(row >= firstRow && row <= lastRow){ if(column >= firstColumn && column <= lastColumn){ return true; } } } return false; }}请求控制器（处理页面excel导入请求）
package com.poiexcel.control; 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.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.multipart.MultipartFile; import org.springframework.web.multipart.MultipartHttpServletRequest; import com.poiexcel.util.ImportExcelUtil; import com.poiexcel.vo.InfoVo; @Controller @RequestMapping("/uploadExcel/*") public class UploadExcelControl { /** * 描述：通过传统方式form表单提交方式导入excel文件 * @param request * @throws Exception */ @RequestMapping(value="upload.do",method={RequestMethod.GET,RequestMethod.POST}) public String uploadExcel(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); 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 "result"; } 前端代码
前端运用了bootstrap的文件上传组件fileinput,需要引入fileinput.css，fileinput.js，zh.js，bootstrap.css，bootstrap.js,jquery.min.js
<body><h4>考勤信息录入</h4><form method="POST" enctype="multipart/form-data" id="form1" action="${pageContext.request.contextPath }/attendance/uploadExcel"> <input id="file-zh" name="upfile" type="file" ></form></body><script> $('#file-zh').fileinput({ language: 'zh', uploadUrl: '${pageContext.request.contextPath }/attendance/uploadExcel', allowedFileExtensions : ['xls', 'xlsx'] });</script>Vo对象，保存Excel数据对应的对象
package com.poiexcel.vo; //将Excel每一行数值转换为对象 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; } } 三.效果展示
1.页面展示
2.后台信息打印
四.总结
该例子只在控制台对导入的数据进行了输出，并没有进行持久化。如果要持久化，只需在注释的位置调用service层即可
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持武林网。