一.概念介紹
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層即可
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持武林網。