Excel的导入V2优化版

有些时候文件上传这一步骤由前端来处理，只将上传后的 URL 传输给后端（可以参考上一文中的图片上传功能），也就是导入请求中并不会直接处理 MultipartFile 对象，而是通过文件 URL 将其下载，之后进行文件流处理，具体过程如下：

点击 “ 导入V2 ” 按钮，与 V1 按钮不同，此时出现的不是文件选择框，而是一个上传文件的弹框，点击弹框里的 “ 上传 Excel 文件 ” 按钮才会出现文件选择框，选择正确的文件后点击确定，请求完成后同 V1 版本的导入功能效果一样。

前端实现

HTML页面

HTML模态框(这里用的是Custombox)

导入的JS

// 用户导入V2function importV2() {    var modal = new Custombox.modal({        content: {            effect: 'fadein',            target: '#importV2Modal'        }    });    modal.open();}

new AjaxUpload('#uploadExcelV2', {        action: 'upload/file',        name: 'file',        autoSubmit: 'true',        responseType: 'json',        onSubmit: function (file, extension) {            if (!(extension && /^(xlsx)$/.test(extension.toLowerCase()))) {                alert('只支持xlsx格式的文件！', {                    icon: "error",                });                return false;            }        },        onComplete: function (file, r) {            if (r.resultCode == 200) {                $("#fileUrl").val(r.data);                $("#uploadExcelV2").attr('class','btn-info');                alert("上传Excel成功，请点击确认添加数据");                return false;            } else {                alert(r.message);            }        }    });});

这里还是通过上传获取文件的url再通过流处理转换成File类型并把对应的Excel下载到服务器，其他的和V1版本是一样的

当点击确定导入的时候，如果之前上传没错，再访问一次后台

$("#importV2Button").click(function () {    var fileUrl = $("#fileUrl").val();    $.ajax({        type: 'post',        dataType: 'json',        url: 'users/importV2?fileUrl=' + fileUrl,        contentType:'application/json',        success:function (result) {                if (result.resultCode==200){                    closeModal();                    reload();                    alert("成功导入"+result.data+"条记录！");                }else {                    closeModal();                    alert(result.message);                };        },        error:function () {            reset();            alert("操作失败！");        }    });});

后端逻辑

控制层

package com.ssm.demo.controller;import com.ssm.demo.common.Result;import com.ssm.demo.common.ResultGenerator;import com.ssm.demo.controller.enums.UploadFileTypeEnum;import com.ssm.demo.utils.FileUtil;import org.apache.commons.io.FileUtils;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.RequestParam;import org.springframework.web.bind.annotation.ResponseBody;import org.springframework.web.multipart.MultipartFile;import javax.servlet.ServletContext;import javax.servlet.http.HttpServletRequest;import java.io.File;import java.io.IOException;import java.text.SimpleDateFormat;import java.util.Date;import java.util.Random;import static com.ssm.demo.common.Constants.FILE_PRE_URL;/** * Created by 13 on 2017/7/17. */@Controller@RequestMapping("/upload")public class UploadFileController {    /**     * 通用 文件上传接口(可以上传图片、视频、excel等文件，具体格式可在UploadFileTypeEnum中进行配置)     *     * @return     */    @RequestMapping(value = "/file", method = RequestMethod.POST)    @ResponseBody    public Result uploadFile(HttpServletRequest request, @RequestParam("file") MultipartFile file) {        ServletContext sc = request.getSession().getServletContext();        String type = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1, file.getOriginalFilename().length());        String fileName = null;        UploadFileTypeEnum uploadFileTypeEnum = UploadFileTypeEnum.getFileEnumByType(type);        if (uploadFileTypeEnum == UploadFileTypeEnum.ERROR_TYPE) {            //格式错误则不允许上传，直接返回错误提示            return ResultGenerator.genFailResult("请检查文件格式！");        } else {            //生成文件名称通用方法            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd_HHmmss");            Random r = new Random();            StringBuilder tempName = new StringBuilder();            tempName.append(sdf.format(new Date())).append(r.nextInt(100)).append(".").append(type);            fileName = tempName.toString();        }        try {            String dir = sc.getRealPath("/upload");            FileUtils.writeByteArrayToFile(new File(dir, fileName), file.getBytes());        } catch (IOException e) {            //文件上传异常            return ResultGenerator.genFailResult("文件上传失败！");        }        Result result = ResultGenerator.genSuccessResult();        //返回文件的全路径        StringBuilder fileUrl = new StringBuilder();        fileUrl.append(FILE_PRE_URL).append("/upload/").append(fileName);        result.setData(fileUrl.toString());        return result;    }    /**     * @param chunks 当前所传文件的分片总数     * @param chunk  当前所传文件的当前分片数     * @return     * @Description: 大文件上传前分片检查     * @author: 13     */    @ResponseBody    @RequestMapping(value = "/checkChunk")    public Result checkChunk(HttpServletRequest request, String guid, Integer chunks, Integer chunk, String fileName) {        try {            String uploadDir = FileUtil.getRealPath(request);            String ext = fileName.substring(fileName.lastIndexOf("."));            // 判断文件是否分块            if (chunks != null && chunk != null) {                //文件路径                StringBuilder tempFileName = new StringBuilder();                tempFileName.append(uploadDir).append(File.separator).append("temp").append(File.separator).append(guid).append(File.separator).append(chunk).append(ext);                File tempFile = new File(tempFileName.toString());                //是否已存在分片,如果已存在分片则返回SUCCESS结果                if (tempFile.exists()) {                    return ResultGenerator.genSuccessResult("分片已经存在！跳过此分片！");                }            }        } catch (Exception ex) {            ex.printStackTrace();            return ResultGenerator.genFailResult("error");        }        return ResultGenerator.genNullResult("不存在分片");    }    /**     * @param chunks 当前所传文件的分片总数     * @param chunk  当前所传文件的当前分片数     * @return     * @Description: 大文件分片上传     * @author: 13     */    @ResponseBody    @RequestMapping(value = "/files")    public Result upload(HttpServletRequest request, String guid, Integer chunks, Integer chunk, String name, MultipartFile file) {        String filePath = null;        //上传存储路径        String uploadDir = FileUtil.getRealPath(request);        //后缀名        String ext = name.substring(name.lastIndexOf("."));        StringBuilder tempFileName = new StringBuilder();        //等价于 uploadDir + "\\temp\\" + guid + "\\" + chunk + ext        tempFileName.append(uploadDir).append(File.separator).append("temp").append(File.separator).append(guid).append(File.separator).append(chunk).append(ext);        File tempFile = new File(tempFileName.toString());        // 判断文件是否分块        if (chunks != null && chunk != null) {            //根据guid 创建一个临时的文件夹            if (!tempFile.exists()) {                tempFile.mkdirs();            }            try {                //保存每一个分片                file.transferTo(tempFile);            } catch (Exception e) {                e.printStackTrace();            }            //如果当前是最后一个分片，则合并所有文件            if (chunk == (chunks - 1)) {                StringBuilder tempFileFolder = new StringBuilder();                //等价于 uploadDir + "\\temp\\" + guid + File.separator                tempFileFolder.append(uploadDir).append(File.separator).append("temp").append(File.separator).append(guid).append(File.separator);                String newFileName = FileUtil.mergeFile(chunks, ext, tempFileFolder.toString(), request);                filePath = "upload/chunked/" + newFileName;            }        } else {            //不用分片的文件存储到files文件夹中            StringBuilder destPath = new StringBuilder();            destPath.append(uploadDir).append(File.separator).append("files").append(File.separator);            String newName = System.currentTimeMillis() + ext;// 文件新名称            try {                FileUtil.saveFile(destPath.toString(), newName, file);            } catch (IOException e) {                e.printStackTrace();            }            filePath = "upload/files/" + newName;        }        Result result = ResultGenerator.genSuccessResult();        result.setData(filePath);        return result;    }}

FileUtil工具类

package com.ssm.demo.utils;import org.apache.commons.io.FileUtils;import org.springframework.web.multipart.MultipartFile;import javax.servlet.ServletContext;import javax.servlet.http.HttpServletRequest;import java.io.*;import java.net.URL;import java.net.URLConnection;import java.util.UUID;public class FileUtil {    /**     * 转换MultipartFile对象为java.io.File类型     *     * @param multipartFile     * @return     */    public static File convertMultipartFileToFile(MultipartFile multipartFile) {        File result = null;        try {            /**             * UUID.randomUUID().toString()是javaJDK提供的一个自动生成主键的方法。             * UUID(Universally Unique Identifier)全局唯一标识符,是指在一台机器上生成的数字，             * 它保证对在同一时空中的所有机器都是唯一的，是由一个十六位的数字组成,表现出来的形式。             * 由以下几部分的组合：当前日期和时间(UUID的第一个部分与时间有关，如果你在生成一个UUID之后，             * 过几秒又生成一个UUID，则第一个部分不同，其余相同)，时钟序列，             * 全局唯一的IEEE机器识别号（如果有网卡，从网卡获得，没有网卡以其他方式获得），             * UUID的唯一缺陷在于生成的结果串会比较长。             *             *             * File.createTempFile和File.createNewFile()的区别：             *  后者只是创建文件，而前者可以给文件名加前缀和后缀             */            //这里对生成的文件名加了UUID随机生成的前缀,后缀是null            result = File.createTempFile(UUID.randomUUID().toString(), null);            multipartFile.transferTo(result);            result.deleteOnExit();        } catch (Exception e) {            e.printStackTrace();        }        return result;    }    /**     * 根据url获取文件对象     *     * @param fileUrl     * @return     */    public static File downloadFile(String fileUrl) {        File result = null;        try {            result = File.createTempFile(UUID.randomUUID().toString(), null);            URL url = new URL(fileUrl);            URLConnection connection = url.openConnection();            connection.setConnectTimeout(3000);            BufferedInputStream bis = new BufferedInputStream(connection.getInputStream());            BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(result));            byte[] car = new byte[1024];            int l = 0;            while ((l = bis.read(car)) != -1) {                bos.write(car, 0, l);            }            bis.close();            bos.close();        } catch (Exception e) {            e.printStackTrace();        }        return result;    }    /**     * @param request     * @return     */    public static String getRealPath(HttpServletRequest request) {        ServletContext sc = request.getSession().getServletContext();        String uploadDir = sc.getRealPath("/upload");        return uploadDir;    }    public static boolean saveFile(String savePath, String fileFullName, MultipartFile file) throws IOException {        File uploadFile = new File(savePath + fileFullName);        FileUtils.writeByteArrayToFile(new File(savePath, fileFullName), file.getBytes());        return uploadFile.exists();    }    public static String mergeFile(int chunksNumber, String ext, String uploadFolderPath,                                   HttpServletRequest request) {        //合并分片流        String mergePath = uploadFolderPath;        String destPath = getRealPath(request);// 文件路径        String newName = System.currentTimeMillis() + ext;// 文件新名称        SequenceInputStream s;        InputStream s1;        try {            s1 = new FileInputStream(mergePath + 0 + ext);            String tempFilePath;            InputStream s2 = new FileInputStream(mergePath + 1 + ext);            s = new SequenceInputStream(s1, s2);            for (int i = 2; i < chunksNumber; i++) {                tempFilePath = mergePath + i + ext;                InputStream s3 = new FileInputStream(tempFilePath);                s = new SequenceInputStream(s, s3);            }            //分片文件存储到/upload/chunked目录下            StringBuilder filePath = new StringBuilder();            filePath.append(destPath).append(File.separator).append("chunked").append(File.separator);            saveStreamToFile(s, filePath.toString(), newName);            // 删除保存分块文件的文件夹            deleteFolder(mergePath);        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (Exception e) {            e.printStackTrace();        }        return newName;    }    private static boolean deleteFolder(String mergePath) {        File dir = new File(mergePath);        File[] files = dir.listFiles();        if (files != null) {            for (File file : files) {                try {                    file.delete();                } catch (Exception e) {                    e.printStackTrace();                }            }        }        return dir.delete();    }    private static void saveStreamToFile(SequenceInputStream inputStream, String filePath, String newName)            throws Exception {        File fileDirectory = new File(filePath);        synchronized (fileDirectory) {            if (!fileDirectory.exists()) {                if (!fileDirectory.mkdir()) {                    throw new Exception("文件夹创建失败,路径为：" + fileDirectory);                }            }            if (!fileDirectory.exists()) {                if (!fileDirectory.mkdir()) {                    throw new Exception("文件夹创建失败,路径为：" + fileDirectory);                }            }        }        OutputStream outputStream = new FileOutputStream(filePath + newName);        byte[] buffer = new byte[1024];        int len = 0;        try {            while ((len = inputStream.read(buffer)) != -1) {                outputStream.write(buffer, 0, len);                outputStream.flush();            }        } catch (Exception e) {            e.printStackTrace();            throw e;        } finally {            outputStream.close();            inputStream.close();        }    }}

其他的和V1版本是一样的

mapper


     
                update tb_admin_user set is_deleted = 1           where id in           
      
                         #{id}

效果展示

Excel的导出实现

前端实现

点击的时候会直接访问到Controller层来获取下载

后端逻辑

/**     * 导出功能     */    @RequestMapping(value = "/export", method = RequestMethod.GET)    public void exportUsers(HttpServletRequest request, HttpServletResponse response) {        List
     
       userList = adminUserService.getUsersForExport();        //单元格表头        String[] excelHeader = {"用户id", "用户名", "账号状态", "添加时间"};        //字段名称        String[] fileds = {"userId", "userName", "status", "createTime"};        //单元格宽度内容格式        int[] formats = {4, 2, 1, 1};        //单元格宽度        int[] widths = {256 * 14, 512 * 14, 256 * 14, 512 * 14};        try {            List
      
       
        > excelData = new ArrayList
        
         
          >();            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");            if (CollectionUtils.isNotEmpty(userList)) {                for (AdminUser user : userList) {                    Map
          
            map = new HashMap<>(); map.put("userId", user.getId()); map.put("userName", user.getUserName()); map.put("status", user.getIsDeleted() == 0 ? "正常账号" : "废弃账号"); map.put("createTime", formatter.format(user.getCreateTime())); excelData.add(map); } } String excelName = "用户数据_" + System.currentTimeMillis(); PoiUtil.exportFile(excelName, excelHeader, fileds, formats, widths, excelData, request, response); } catch (Exception e) { e.printStackTrace(); } }

注：这里的一些工具类在上一篇写过了

转载于:https://www.cnblogs.com/xiaowangtongxue/p/10747393.html

你可能感兴趣的文章