1. Concept introduction
ApachePOI is an open source library of the Apache Software Foundation. POI provides APIs to Java programs to read and write Microsoft Office format archives.
2. Function-related code
1. Environment description: JDK1.7+tomcat7+spring
2. Configuration file configuration
Add POI dependencies required in pom file
<!-- Add POI support --> <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 configuration file upload
<bean id="multipartResolver" > <!-- Default encoding--> <property name="defaultEncoding" value="utf-8" /> <!-- Maximum file size value--> <property name="maxUploadSize" value="10485760000" /> <!-- Maximum value in memory--> <property name="maxInMemorySize" value="40960" /> </bean>
3. Related tool classes and code writing
Excel parsing tool class (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- Version of excel private final static String excel2007U =".xlsx"; //2007+ version of excel /** * Description: Get the data in the IO stream and assemble it into a List<List<Object>> object* @param in,fileName * @return * @throws IOException */ public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{ List<List<Object>> list = null; //Create Excel workbook work = this.getWorkbook(in,fileName); if(null == work){ throw new Exception("Create Excel worksheet as empty!"); } Sheet sheet = null; Row row = null; Cell cell = null; list = new ArrayList<List<Object>>(); //Tranquility all sheets in Excel for (int i = 0; i < work.getNumberOfSheets(); i++) { sheet = work.getSheetAt(i); if(sheet==null){continue;} //Tranquility all rows in the current 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;// } // traverse all columns 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; } /** * Description: According to the file suffix, the version of the file uploaded adaptively* @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("The parsed file format is incorrect!"); } return wb; } /** * Description: Format the values in the table* @param cell * @return */ public Object getCellValue(Cell cell){ Object value = null; DecimalFormat df = new DecimalFormat("0"); //Format number String character SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //Date format DecimalFormat df2 = new DecimalFormat("0"); //Format number 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; } /** * Get the content of the merged cell* @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; } /** * Determine whether it is a merged cell* @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; }} Request controller (process page excel import request)
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 { /** * Description: Import excel file through traditional form submission * @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("File does not exist!"); } in = file.getInputStream(); listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename()); in.close(); //The corresponding service method can be called here to save the data to the database. Now only the data is output 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("Print Information-->Institution:"+vo.getCode()+" Name: "+vo.getName()+" Time: "+vo.getDate()+" Asset: "+vo.getMoney()); } return "result"; } Front-end code
The front-end uses the file upload component fileinput of bootstrap, and needs to introduce fileinput.css, fileinput.js, zh.js, bootstrap.css, bootstrap.js, jquery.min.js
<body><h4>Attendance information entry</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 object, save the corresponding object of Excel data
package com.poiexcel.vo; //Convert each row of Excel into an object 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; } }3. Effect display
1. Page display
2. Print backend information
4. Summary
This example only outputs the imported data in the console and does not persist. If you want to persist, just call the service layer at the comment location
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.