excel文件上传与excel模板的下载

这里是比较浅显的excel的上传下载  我用的是jeesite框架

模板的话是直接放在项目中

上图就是做好的excel模板

以下是下载模板的代码

 @RequestMapping(value = "getScheduleTemplate")
    public void getScheduleTemplate(HttpServletRequest request,HttpServletResponse response){
        try {
            //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
            response.setContentType("multipart/form-data");
            //2.设置文件头：最后一个参数是设置下载文件名
            response.setHeader("Content-disposition", "attachment; filename=\"" +encodeChineseDownloadFileName(request, "行政历模板"+".xlsx") +"\"");
            //通过文件路径获得File对象

            File file = new File(Global.getUserfilesBaseDir() + File.separator + "scheduleTemplate.xlsx");

            FileInputStream fileInputStream = new FileInputStream(file);
            //3.通过response获取OutputStream对象(out)
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());

            int flg = 0;
            byte[] buffer = new byte[2048];
            while ((flg = fileInputStream.read(buffer)) != -1){
                outputStream.write(buffer,0,flg);
            }
            fileInputStream.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 设置文件名
     * @param request
     * @param pFileName
     * @return
     * @throws UnsupportedEncodingException
     */
    public static String encodeChineseDownloadFileName(HttpServletRequest request, String pFileName)
            throws UnsupportedEncodingException {

        String filename = null;
        String agent = request.getHeader("USER-AGENT");
        if (null != agent) {
            if (-1 != agent.indexOf("Firefox")) {
                //Firefox
                filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8")))) + "?=";
            } else if (-1 != agent.indexOf("Chrome")) {
                //Chrome
                filename = new String(pFileName.getBytes(), "ISO8859-1");
            } else {
                //IE7+
                filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
                filename = StringUtils.replace(filename, "+", "%20");//替换空格
            }
        } else {
            filename = pFileName;
        }

        return filename;
    }

以下是上传保存的代码

 @RequestMapping(value = "inputSchedule")
    public String inputSchedule(MultipartFile file, RedirectAttributes redirectAttributes,String togo){
        try {
            User userId = UserUtils.getUser();
            String originFileName = file.getOriginalFilename();
        if ((!originFileName.equals("行政历模板.xlsx"))) {
            addMessage(redirectAttributes, "请导入正确的模板文件。");

            if(StringUtils.isNotBlank(togo)){
                return "redirect:" + Global.getAdminPath() + "重定向位置";
            }
            return "redirect:" + Global.getAdminPath() + "重定向位置";
        }

        int successNum = 0;
        int failureNum = 0;
        String failureMsg = new String();
        String failureMsg2 = new String();
        List<String> errorList = new ArrayList<String>();
        POIUtils utils = new POIUtils();
        InputStream input = null;
        List<List<Object>> lists = null;
        List<Schedule> scheduleList = new ArrayList<>();
        if (file.isEmpty()) {
            addMessage(redirectAttributes,"文件不存在！");
        } else {
            if (errorList.size() == 0) {
                String fileName = file.getOriginalFilename();
                    input = file.getInputStream();
                lists = utils.getBankListByExcel(input, fileName);
                input.close();
                for (int i = 1; i < lists.size(); i++) {
                    List<Object> list = lists.get(i);
                    if(StringUtils.isBlank(String.valueOf(list.get(1)))){
                         break;
                    }
                    Schedule schedule = new Schedule(); //所需要保存的实体类
                    User user = new User();
                    user.setId(userId.getId());
                    schedule.setUser(user);
                    schedule.setContent(String.valueOf(list.get(1)));
                    schedule.setDate(DateUtils.parseDate(list.get(0)));
                    schedule.setRemind(0);
                    schedule.setRepeatNum(0);
                    schedule.setCreateBy(userId);
                    schedule.setCreateDate(new Date());
                    schedule.setUpdateBy(userId);
                    schedule.setUpdateDate(new Date());
                    schedule.setType("1");
                    scheduleList.add(schedule);
                    successNum ++;
                }
                scheduleService.saveScheduleList(scheduleList);
            }
        }
            addMessage(redirectAttributes, "已成功导入 "+successNum+" 条记录,失败 "+failureNum+" 条记录"+failureMsg+" "+failureMsg2);
        } catch (Exception e) {
            e.printStackTrace();
            addMessage(redirectAttributes, "导入失败！失败信息：" + e.getMessage());
        }
        if(StringUtils.isNotBlank(togo)){
            return "redirect:" + Global.getAdminPath() + "重定向位置";
        }
        return "redirect:" + Global.getAdminPath() + "重定向位置";

    }

package com.thinkgem.jeesite.modules.schedule.utils;

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.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * excel文件上传Util
 *
 * @author Justin
 */
public class POIUtils {

    private final static String Excel_2003 = ".xls"; //2003 版本的excel
    private final static String Excel_2007 = ".xlsx"; //2007 版本的excel

    /**
     * @param in
     * @param fileName
     * @param columNum 自定义列数
     * @return
     */
    public List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception {
        List<List<Object>> list = null;

        //创建Excel工作簿
        Workbook work = this.getWorkbook(in, fileName);
        if (work == null) {
            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中的所有行
            //int totalRow = sheet.getPhysicalNumberOfRows();//如果excel有格式，这种方式取值不准确
            int totalRow = sheet.getPhysicalNumberOfRows();
            for (int j = sheet.getFirstRowNum(); j < totalRow; j++) {
                row = sheet.getRow(j);
                if (row != null && !"".equals(row)) {
                    //获取第一个单元格的数据是否存在
                    Cell fristCell = row.getCell(0);
                    if (fristCell != null) {
                        //遍历所有的列
                        List<Object> li = new ArrayList<Object>();
                        //int totalColum = row.getLastCellNum();
                        for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                            cell = row.getCell(y);
                            String callCal = this.getCellValue(cell) + "";
                            li.add(callCal);
                        }
                        list.add(li);
                    }
                }

            }
        }
        in.close();
        return list;
    }

    /**
     * 描述：根据文件后缀，自动适应上传文件的版本
     *
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook work = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (Excel_2003.equals(fileType)) {
            work = new HSSFWorkbook(inStr);//2003 版本的excel
        } else if (Excel_2007.equals(fileType)) {
            work = new XSSFWorkbook(inStr);//2007 版本的excel
        } else {
            throw new Exception("解析文件格式有误！");
        }
        return work;
    }

    /**
     * 描述：对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
    public Object getCellValue(Cell cell) {
        Object value = null;
        DecimalFormat df1 = new DecimalFormat("0");//格式化number，string字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");//日期格式化
        DecimalFormat df2 = new DecimalFormat("0.00");//格式化数字
        if (cell != null && !"".equals(cell)) {
            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 = df1.format(cell.getNumericCellValue());
                    } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                        value = sdf.format(cell.getDateCellValue());
                    } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        value = sdf.format(date);
                    } 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;
    }

    public String getFormat(String str) {
        if (str.equals("null")) {
            str = "";
            return str;
        } else {
            return str;
        }
    }

    public Integer getFormats(Integer str) {
        if (str == null) {
            str = 0;
            return str;
        } else {
            return str;
        }
    }
}

上面这段代码是上传保存用到的工具

 

<div id="importBox" class="hide" >
    <form id="importForm" action="后台方法路径" method="post" enctype="multipart/form-data"
          class="form-search" style="padding-left:100px;text-align:center;height: 150px" onsubmit="loading('正在导入，请稍等...');">
        <div style="padding-top:20px;text-align:center;">
            <input id="uploadFile" name="file" type="file" style="width:300px;padding-left: 50px;"/>
        </div>
        <div style="padding-top:50px;text-align:center;">
            <input id="btnImportSubmit" class="btn btn-primary" type="submit" value="   导    入   "/>
            <a class="btn btn-primary"  href="后台方法路径">下载模板</a>
        </div>
    </form>
</div>

$("#btnImport").click(function(){
            $.jBox($("#importBox").html(), {title:"导入数据", buttons:{"关闭":true},
                bottomText:"导入文件不能超过5M，仅允许导入“xls”或“xlsx”格式文件！"});
        });


//这段代码要放在页面加载完成时间中

后两段代码为前端的简单实现