文件的上传下载(Excel文件的上传解析入库)

文件的上传下载(Excel文件的上传解析入库)

• 总结
• 文件下载: 先用流读文件, 再将读到的文件,通过response返回给客户端,其中读一次通常读1024个字节,写的时候, 从第0个字节, 写1024个
• 文件上传: springboot 通常使用MultipartFile 来接收客户端上传的文件, 上传一般的文件, 一般是将文件写到服务器上, 如果上传Excel通常是解析Excel,将文件的中内容入库. 都没什么难的, 这里只是备份代码而已, 方便后用.

Excel文件的上传解析 Contorller,下面的下载模板, 是取文件夹中的第一个模板作为下载的

package com.basetnt.shop.controller.store;

import com.basetnt.shop.domain.vo.Message;
import com.basetnt.shop.service.store.ResolveExcelService;
import io.swagger.annotations.Api;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;

/**
 * @Desc
 * @Date 2019/4/26 15:09
 * @Author cui_yl
 */
@Api(tags = "ExcelController", description = "导入题")
@RestController
@RequestMapping("/excel")
public class ExcelController {
    private static final Logger logger = LoggerFactory.getLogger(VipBackstageController.class);

    @Value("${file.upload.path}")
    private String uploadLocation;

    @Resource(name = "resolveExcelServiceImpl")
    private ResolveExcelService resolveExcelService;

    /**
     * 文件上传
     */
    @PostMapping(value = "/upload/{vipId}")
    public Object resolveExcel(@RequestParam("file") MultipartFile file, @PathVariable("vipId") Integer vipId) {
        try {
            Object result = resolveExcelService.resolveExcel(file, vipId);
            return new Message().ok(200, "解析成功");
        } catch (Exception e) {
            e.printStackTrace();
            return new Message().error(500, "导入失败:"+e.getMessage());
        }
    }

    @GetMapping("/download/template")
    public void downloadFile(HttpServletResponse response) {
        File directory = new File(uploadLocation);
        File[] files = directory.listFiles();
        //设置文件路径
        File file = files[0];
        if (file.exists()) {
            response.setContentType("application/force-download");// 设置强制下载不打开
            response.addHeader("Content-Disposition", "attachment;fileName=" + file.getName());// 设置文件名
            byte[] buffer = new byte[1024];
            FileInputStream fis = null;
            BufferedInputStream bis = null;
            try {
                fis = new FileInputStream(file);
                bis = new BufferedInputStream(fis);
                OutputStream os = response.getOutputStream();
                int i = bis.read(buffer);
                while (i != -1) {
                    os.write(buffer, 0, i);
                    i = bis.read(buffer);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (bis != null) {
                    try {
                        bis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                if (fis != null) {
                    try {
                        fis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

}

• Configuration

package com.basetnt.shop.config.uploadFile;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.MultipartConfigFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;

import javax.servlet.MultipartConfigElement;

/**
 * @Desc
 * @Date 2019/4/26 11:50
 * @Author cui_yl
 */
@Configuration
public class UploadFileConfiguration extends WebMvcConfigurerAdapter {

    @Value("${file.upload.path}")
    private String uploadLocation;

    @Bean
    public MultipartConfigElement multipartConfigElement() {
        MultipartConfigFactory factory = new MultipartConfigFactory();
        // 设置文件大小限制 ,超出设置页面会抛出异常信息，
        // 这样在文件上传的地方就需要进行异常信息的处理了;
        factory.setMaxFileSize("128MB"); // KB,MB
        /// 设置总上传数据总大小
        factory.setMaxRequestSize("256MB");
        //设置文件路径
        factory.setLocation(uploadLocation);
        return factory.createMultipartConfig();
    }

}

• Service

package com.basetnt.shop.service.store;

import com.basetnt.shop.domain.bo.store.QuestionInfo;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

/**
 * @Desc
 * @Date 2019/4/26 15:12
 * @Author cui_yl
 */
public interface ResolveExcelService {

    List<QuestionInfo> resolveExcel(MultipartFile file, Integer vipId) throws Exception;
}

• ServiceImpl

package com.basetnt.shop.service.store.impl;

import com.alibaba.fastjson.JSONObject;
import com.basetnt.shop.controller.store.WcpHomeController;
import com.basetnt.shop.dao.store.QuestionBasicMapper;
import com.basetnt.shop.dao.store.QuestionInfoMapper;
import com.basetnt.shop.domain.bo.store.QuestionBasic;
import com.basetnt.shop.domain.bo.store.QuestionInfo;
import com.basetnt.shop.form.AnswerTemplate;
import com.basetnt.shop.service.store.ResolveExcelService;
import com.basetnt.shop.util.DateUtil;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.util.ArrayList;
import java.util.List;

/**
 * @Desc
 * @Date 2019/4/26 15:13
 * @Author cui_yl
 */
@Service("resolveExcelServiceImpl")
public class ResolveExcelServiceImpl implements ResolveExcelService {
    /**
     *打印日志
     */
    private static final Logger logger = LoggerFactory.getLogger(WcpHomeController.class);

    /**
     * 注册url
     */
    private static final String SUFFIX_2003 = ".xls";
    private static final String SUFFIX_2007 = ".xlsx";

    @Autowired
    private QuestionBasicMapper questionBasicMapper;
    @Autowired
    private QuestionInfoMapper questionInfoMapper;


    @Transactional(isolation = Isolation.DEFAULT, propagation = Propagation.REQUIRED, rollbackFor=Exception.class)
    @Override
    public List<QuestionInfo> resolveExcel(MultipartFile file, Integer vipId) throws Exception {
        List<QuestionInfo> list = new ArrayList<>();
        Integer date = DateUtil.getIntTypeDate();

        if (file == null) {
            throw new RuntimeException("文件不能为空");
        }
        String originalFilename = file.getOriginalFilename(); //获取文件的名字
        if(StringUtils.isEmpty(originalFilename)){
            throw new RuntimeException("文件名不能为空");
        }
        Workbook workbook = null;
        if (originalFilename.endsWith(SUFFIX_2003)) {
            workbook = new HSSFWorkbook(file.getInputStream());
        } else if (originalFilename.endsWith(SUFFIX_2007)) {
            workbook = new XSSFWorkbook(file.getInputStream());
        }else {
            throw new RuntimeException("请上传.xls或者.xlsx格式的Excel");
        }

        //获得所有的题
        Integer num = 0;
        //获取所有的工作表的的数量
        int numOfSheet = workbook.getNumberOfSheets();
        QuestionBasic questionBasic =null;
        //遍历这个这些表
        for (int i = 0; i < numOfSheet; i++) {
            //获取一个sheet也就是一个工作簿
            Sheet sheet = workbook.getSheetAt(i);
            int lastRowNum = sheet.getLastRowNum();
            /*
             * 第0行 heading
             * 第1行 标题
             * 第2行 数据
             * 需要拿到 0行的heading, 第2行的一行数据, 封装record
             * 从3行开始 循环遍历 封装数据
             */
            if (0 == i){
                //获得文件标题
                String heading ="";
                Row frow = sheet.getRow(0);
                if (frow.getCell(1) != null) {
                    frow.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    heading = frow.getCell(1).getStringCellValue();
                }
                questionBasic = new QuestionBasic();
                questionBasic.setVipId(vipId);
                questionBasic.setHeading(heading);
                questionBasic.setCreateTime(date);
                questionBasicMapper.insertSelective(questionBasic);
            }

            num += lastRowNum;
            for (int j = 2; j <= lastRowNum; j++) {
                Row row = sheet.getRow(j);
                if(null == row){
                    continue;
                }
                AnswerTemplate record = new AnswerTemplate();

                if (row.getCell(0) != null) {
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(0).getStringCellValue();
                    record.setTitle(cellValue);
                }
                if (row.getCell(1) != null) {
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(1).getStringCellValue();
                    record.setA(cellValue);
                }
                if (row.getCell(2) != null) {
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(2).getStringCellValue();
                    record.setB(cellValue);
                }
                if (row.getCell(3) != null) {
                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(3).getStringCellValue();
                    record.setC(cellValue);
                }
                if (row.getCell(4) != null) {
                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(4).getStringCellValue();
                    record.setD(cellValue);
                }
                if (row.getCell(5) != null) {
                    row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = row.getCell(5).getStringCellValue();
                    record.setCorrect(cellValue);
                }

                QuestionInfo entity = new QuestionInfo();
                entity.setQuestionBasicId(questionBasic.getId());
                entity.setAnswer(JSONObject.toJSON(record).toString());
                entity.setQuestionBasicId(questionBasic.getId());
                entity.setCreateTime(date);
                entity.setUpdateTime(date);
                entity.setTitle(record.getTitle());
                list.add(entity);
            }
        }
        if(CollectionUtils.isEmpty(list)){
           return null;
        }
        if (list.size() <= 300){
            questionInfoMapper.batchInsertSelective(list);
            list.clear();
        }else{
            int index =0;
            List<QuestionInfo> arrays = new ArrayList<>();
            for (QuestionInfo tmp : list) {
                index++;
                arrays.add(tmp);
                if (index >= 300){
                    questionInfoMapper.batchInsertSelective(list);
                    arrays.clear();
                }
            }
            if (arrays.size() >0){
                questionInfoMapper.batchInsertSelective(list);
                arrays.clear();
            }
        }
        //更新题目数
        QuestionBasic tmp = new QuestionBasic();
        tmp.setId(questionBasic.getId());
        tmp.setNum(num);
        questionBasicMapper.updateByPrimaryKeySelective(tmp);
        return list;
    }
}

• Entity

package com.basetnt.shop.form;

import com.google.gson.annotations.Expose;
import com.google.gson.annotations.SerializedName;
import lombok.Data;

/**
 * @Desc
 * @Date 2019/4/26 11:43
 * @Author cui_yl
 */
@Data
public class AnswerTemplate {

    @SerializedName("title")
    @Expose
    private String title;

    @SerializedName("a")
    @Expose
    private String a;

    @SerializedName("b")
    @Expose
    private String b;

    @SerializedName("c")
    @Expose
    private String c;

    @SerializedName("d")
    @Expose
    private String d;

    @SerializedName("correct")
    @Expose
    private String correct;

}