SpringMVC上传下载Exacel

最新推荐文章于 2021-07-02 09:44:28 发布

爱谈

最新推荐文章于 2021-07-02 09:44:28 发布

阅读量467

点赞数

文章标签： spring mvc excel

本文链接：https://blog.csdn.net/u012682006/article/details/55255057

版权

首先需要先配置好框架
导入需要的jar包poi-3.11-20141221.jar
创建Excle处理类
controller层调用方法

SpringMVC上传下载Exacel

先写一个ExcelView，当做工具类使用

package com.ssm.commons.tools;

import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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;

public class ExcelView extends AbstractExcelView{
    @Override  
    protected void buildExcelDocument(Map<String, Object> model,  
            HSSFWorkbook workbook, HttpServletRequest request,  
            HttpServletResponse response) throws Exception {  
        @SuppressWarnings("unchecked")  
        List<Map<Object, Object>> dataList = (List<Map<Object, Object>>) model.get("dataList");
        @SuppressWarnings("unchecked")
        List<String> titlesList = (List<String>) model.get("titleList");
        if (dataList != null && dataList.size() != 0) {  
            int len = dataList.size();
            // 创建工作表
            Sheet sheet = workbook.createSheet();  
            // 第一行文字说明 ,创建表头   Row表示创建行
            Row row = sheet.createRow(0);  
            Cell cell = row.createCell(0);  
            for(int i=0;i<titlesList.size();i++){
                cell = row.createCell(i, Cell.CELL_TYPE_STRING);
                cell.setCellValue(titlesList.get(i));
            }
            //下面是具体数据内容  
            for (int i = 0; i < len; i++) {  
                //创建新的一行
                row = sheet.createRow(i + 1);
                Map<Object, Object> dataMap = dataList.get(i);
                int j = 0;
                for (Object value : dataMap.values()) {  
                    cell = row.createCell(j, Cell.CELL_TYPE_STRING);
                    cell.setCellValue(value+"");
                    j++;
                } 
            }
        }  

        response.setContentType("application/vnd.ms-excel");  
        response.setCharacterEncoding("utf-8");  
        //这里对文件名进行编码，保证下载时汉字显示正常  
        String fileName = URLEncoder.encode(model.get("fileName")+".xls", "utf-8");  
        //Content-disposition属性设置成以附件方式进行下载  
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);  
        OutputStream os = response.getOutputStream();  
        workbook.write(os);  
        os.flush();  
        os.close();  
    }

    public Object getValues(Cell cell){
        Object ret = null;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                ret = "";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                ret = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                ret = null;
                break;
            case Cell.CELL_TYPE_FORMULA: 
                try {   
                     if (HSSFDateUtil.isCellDateFormatted(cell)) {  
                        Date date = cell.getDateCellValue();  
                        ret = simpleDateFormat.format(date); 
                        break;  
                     } else {  
                         ret = String.valueOf(cell.getNumericCellValue());  
                     }  
                 } catch (IllegalStateException e) {  
                     ret = String.valueOf(cell.getRichStringCellValue());  
                 }  
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) { 
                    Date date = cell.getDateCellValue();
                    ret = simpleDateFormat.format(date); 
                    break;  
                } else { 
                    DecimalFormat df = new DecimalFormat("#");
                    ret = df.format(cell.getNumericCellValue());
                    break;  
                }
            case Cell.CELL_TYPE_STRING:
                ret = cell.getRichStringCellValue().getString();
                break;
            default:
                ret = null;
        }
        return ret;
    }

    //判断是不是double
    public boolean isDouble(String str){ 
        try{   
            Double dCheckValue = Double.parseDouble(str);  
            if(dCheckValue instanceof Double == false)  {  
                return false;  
            }  
        }catch(NumberFormatException e){    
            return false;
        }
        return true;
    }

    //2.0转换为2
    public String DoubleToInt(String str){
        if(str.split("\\.").length>1){
            str = str.split("\\.")[0];
        }
        return str;
    }

}

User实体类

package com.ssm.commons.sqlmap;

public class User {

    private int userId;
    private String username;
    private String password;
    private int gender;
    private String usernum;
    private String userphone;

    public int getUserId() {
        return userId;
    }

    public void setUserId(int userId) {
        this.userId = userId;
    }

    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;
    }

    public String getUsernum() {
        return usernum;
    }

    public void setUsernum(String usernum) {
        this.usernum = usernum;
    }

    public String getUserphone() {
        return userphone;
    }

    public void setUserphone(String userphone) {
        this.userphone = userphone;
    }

    public int getGender() {
        return gender;
    }

    public void setGender(int gender) {
        this.gender = gender;
    }

}

controller层调用工具类

在调用前需要先在SpringMVC中配置一下上传和下载

    <!-- 上传文件解析器配置 -->
    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <property name="defaultEncoding" value="UTF-8"/>
        <!-- 上传文件的大小限制 ，单位是字节--> 
        <property name="maxUploadSize" value="200000"/>
        <!-- 上传文件的临时路径，上传完成后会自动删除 -->
        <property name="uploadTempDir" value="upload/temp"/>
    </bean>

package com.ssm.excel.action;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
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.stereotype.Controller;
import org.springframework.util.StringUtils;
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.ssm.commons.sqlmap.User;
import com.ssm.commons.tools.ExcelView;

@Controller
@RequestMapping(value="/file")
public class ExcelAction {

    /** 
     * Excel文件上传处理 
     * @param file 
     * @return 
     */  
    @RequestMapping("/upload")  
    public ModelAndView uploadExcel(@RequestParam("file") MultipartFile file){ 

        List<User> list = new ArrayList<User>();  
        if(file != null && StringUtils.hasText(file.getOriginalFilename())){  
            try {  
                @SuppressWarnings("resource")
                Workbook wb = new HSSFWorkbook(file.getInputStream());  
                Sheet sheet = wb.getSheetAt(0);
                ExcelView ev = new ExcelView();
                for( int i = 1; i <= sheet.getLastRowNum(); i++ ){  
                    Row row = sheet.getRow(i);  
                    User user = new User();
                    user.setUsername((String)ev.getValues(row.getCell(0)));
                    String password = row.getCell(1).toString();
                    if(ev.isDouble(password)){
                        password = ev.DoubleToInt(password);
                    }
                    user.setPassword(password); 
                    String sex = ev.getValues(row.getCell(2)).toString();
                    if(ev.isDouble(sex)){
                        sex = ev.DoubleToInt(sex);
                    }
                    user.setGender(Integer.parseInt(sex));
                    user.setUsernum((String)ev.getValues(row.getCell(3)));
                    user.setUserphone(ev.getValues(row.getCell(4)).toString());
                    list.add(user);
                }  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }

        ModelAndView mav = new ModelAndView("fail");
        if(list!=null && list.size()>0){
            mav = new ModelAndView("content");  
            mav.addObject("content",list);  
        }
        return mav; 
    }  

    /** 
     * Excel文件下载处理 
     */  
    @RequestMapping("/download")  
    public ModelAndView downloanExcel(){  

        List<String> titleList = new ArrayList<String>();
        titleList.add("用户名");
        titleList.add("密码");
        titleList.add("性别");
        titleList.add("学号");
        titleList.add("电话");

        List<Object> dataList = new ArrayList<Object>();
        Map<Object, Object> userMap = new LinkedHashMap<Object, Object>();
        userMap.put("userName", "zel");
        userMap.put("Password", "1111");
        userMap.put("Gender", 1);
        userMap.put("userNum", "2012****4125");
        userMap.put("userPhone", "156****9970");
        dataList.add(userMap);  
        dataList.add(userMap);  
        dataList.add(userMap);  
        dataList.add(userMap);  

        Map<String,Object> exacelMap = new HashMap<String, Object>();
        exacelMap.put("fileName", "fiel");
        exacelMap.put("dataList", dataList);
        exacelMap.put("titleList", titleList); 
        ExcelView view = new ExcelView();  
        return new ModelAndView(view,exacelMap);  
    }  

}

上传成功后数据显示

显示页面位置

content.jsp源码，这时使用jstl标签需要导入jstl-1.2.jar这个jar包

<%@page import="java.util.ArrayList,java.util.List"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>上传成功</title>
</head>
<body>
    <p>上传用户信息显示(jstl)</p> 
    <c:forEach var="user" items="${content}"> 
        <table>
            <tr>
                <td>${user.username}</td>
                <td>${user.password}</td>
                <td>
                    <c:if test="${user.gender == 1}">
                        <c:out value="男"></c:out>
                    </c:if>
                    <c:if test="${user.gender == 0}">
                        <c:out value="女"></c:out>
                    </c:if>
                </td>
                <td>${user.usernum}</td>
                <td>${user.userphone}</td>
            </tr>
        </table>
    </c:forEach>    
</body>
</html>