In practical applications, you often encounter situations where Excel is uploaded or downloaded, such as importing data, downloading statistics, etc. To address this problem, I wrote a simple upload download example based on SpringMVC, where Excel's processing uses Apache's POI component.
The main dependencies are as follows:
<dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.4</version> </dependency> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.1</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-web</artifactId> <version>4.0.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>4.0.0.RELEASE</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.10.1</version> </dependency>
Related processing categories:
(I) Controller class
package com.research.spring.controller; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.steretype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.multipart.MultipartFile; import org.springframework.web.servlet.ModelAndView; import com.research.spring.model.UserInfo; import com.research.spring.view.ExcelView; @Controller @RequestMapping("/file") public class FileController { /** * Excel file upload processing* @param file * @return */ @RequestMapping("/upload") public ModelAndView uploadExcel(@RequestParam("file") MultipartFile file){ List<UserInfo> list = new ArrayList<UserInfo>(); //This is only processed with files with file names including "user", and the template uses the download template if( file.getOriginalFilename().contains("user") ){ try { Workbook wb = new HSSFWorkbook(file.getInputStream()); Sheet sheet = wb.getSheetAt(0); for( int i = 1; i <= sheet.getLastRowNum(); i++ ){ Row row = sheet.getRow(i); UserInfo info = new UserInfo(); info.setUserName(row.getCell(0).getStringCellValue()); info.setPassword(row.getCell(1).getStringCellValue()); list.add(info); } } catch (IOException e) { e.printStackTrace(); } } ModelAndView mav = new ModelAndView("content"); mav.addObject("content",list.toString()); return mav; } /** * Excel file download processing*/ @RequestMapping("/download") public ModelAndView downloadExcel(){ List<UserInfo> list = new ArrayList<UserInfo>(); UserInfo userInfo = new UserInfo(); userInfo.setPassword("0000"); userInfo.setUserName("sdfas"); list.add(userInfo); list.add(userInfo); list.add(userInfo); list.add(userInfo); Map<String,List<UserInfo>> map = new HashMap<String, List<UserInfo>>(); map.put("infoList", list); ExcelView ve = new ExcelView(); return new ModelAndView(ve,map); } }(II) Entity Class
package com.research.spring.model; public class UserInfo { private String userName; private String password; public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } @Override public String toString() { return "UserInfo [userName=" + userName + ", password=" + password + "]"; } }(III) View Class
This class is used when downloading, and when rendering pages in Spring, you use a custom View class to perform Excel-related processing.
package com.research.spring.view; import java.io.OutputStream; import java.net.URLEncoder; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; 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.springframework.web.servlet.view.document.AbstractExcelView; import com.research.spring.model.UserInfo; /** * Download Excel view* * @author wdmcygah * */ public class ExcelView extends AbstractExcelView { @Override protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { @SuppressWarnings("unchecked") List<UserInfo> list = (List<UserInfo>) model.get("infoList"); if (list != null && list.size() != 0) { int len = list.size(); Sheet sheet = workbook.createSheet(); // The first line of text description Row row = sheet.createRow(0); Cell cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellValue("Username"); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellValue("Password"); //The following is the specific content for (int i = 0; i < len; i++) { row = sheet.createRow(i + 1); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellValue(list.get(i).getUserName()); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellValue(list.get(i).getPassword()); } } response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); //The file name is encoded here to ensure that the Chinese characters are displayed normally during download String fileName = URLEncoder.encode("User.xls", "utf-8"); //Content-disposition property is set to download response.setHeader("Content-disposition", "attachment;filename=" + fileName); OutputStream os = response.getOutputStream(); workbook.write(os); os.flush(); os.close(); } }(IV) Main configuration files
When uploading files, you need to configure the MultipartResolver class in the configuration file. After configuration, Spring will automatically pass the file into a MultipartFile object, and then the corresponding processing can be performed. For example, see Controller class.
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/mvc" xmlns:util="http://www.springframework.org/schema/util" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd"> <context:component-scan base-package="com.research" /> <bean> <property name="viewClass" value="org.springframework.web.servlet.view.JstlView" /> <property name="prefix" value="/WEB-INF/" /> <property name="suffix" value=".jsp" /> </bean> <!-- Upload file parser configuration--> <bean id="multipartResolver"> <property name="defaultEncoding" value="UTF-8"></property> <!-- Upload file size limit, unit is bytes--> <property name="maxUploadSize" value="5242880000000"></property> <!-- Temporary path to upload file, it will be automatically deleted after uploading--> <property name="uploadTempDir" value="upload/temp"></property> </bean> </beans>
(V) Test page
<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> </head> <body> <h3>Test the download Excel function</h3> <form action="file/download.htm" enctype="multipart/form-data" method="post"> <input type="submit" value="Download Excel"></input> </form> <h3>Test the upload Excel function</h3> <form action="file/upload.htm" enctype="multipart/form-data" method="post"> <input type="file" name="file"></input> <input type="submit" value="upload Excel"></input> </form> </body> </html>
If you want to see the complete source code, you can check it in my Github repository. Among them, uploading files only processes files that meet the download template. To process other files, you need to implement it yourself. The code test passed without error.
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.