spring boot整合poi实现excel文件导入导出实战

今天科比离去，今天肺炎病毒持续肆虐。。。

意识到生命的脆弱，今天我继续前行，比以往更加坚定和紧迫，这辈子不活好自己就算白来一趟。

1.项目介绍

最近帮朋友做了一个小工具，就是实现：上传一个excel文件，将数据导入mysql表中，再调用存储过程进行转换计算，最后再将处理结果导出成excel。中间用到的一些知识点记录下来和朋友分享。

2.效果图

3.pom整合

<!--解析excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>

        <!--处理2007 excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>

4.工具类

package com.laoxu.test.helloweb.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

/**
 * @Description:
 * @Author laoxu
 * @Date 2020/1/24 16:00
 **/
public class ExcelUtil {
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
    /**
     * 获取并解析excel文件，返回一个二维集合
     * @param file 上传的文件
     * @return 二维集合（第一重集合为行，第二重集合为列，每一行包含该行的列集合，列集合包含该行的全部单元格的值）
     */
    public static ArrayList<ArrayList<String>> analysis(MultipartFile file) {
        ArrayList<ArrayList<String>> result = new ArrayList<>();
        //获取文件名称
        String fileName = file.getOriginalFilename();
        System.out.println(fileName);

        try {
            //获取输入流
            InputStream in = file.getInputStream();
            //判断excel版本
            Workbook workbook = null;
            if (judegExcelEdition(fileName)) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }

            //获取第一张工作表
            Sheet sheet = workbook.getSheetAt(0);
            //从第二行开始获取
            Row row=null;
            for (int i=1; i<sheet.getPhysicalNumberOfRows();i++) {
                row = sheet.getRow(i);
                //循环获取每一列
                ArrayList<String> list = new ArrayList<>();
                Cell cell = null;
               for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                   // logger.info(i+":"+j);
                   cell = row.getCell(j);
                   if(cell==null){
                       cell = row.createCell(j);
                   }
                   cell.setCellType(CellType.STRING);

                   list.add(cell.getStringCellValue());
                }
                //将装有每一列的集合装入大集合
                result.add(list);

                //关闭资源
                workbook.close();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            System.out.println("===================未找到文件======================");
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("===================上传失败======================");
        }
        return result;
    }
    /**
     * 判断上传的excel文件版本（xls为2003，xlsx为2017）
     * @param fileName 文件路径
     * @return excel2007及以上版本返回true，excel2007以下版本返回false
     */
    private static boolean judegExcelEdition(String fileName){
        if (fileName.matches("^.+\\.(?i)(xls)$")){
            return false;
        }else {
            return true;
        }
    }
}

5.controller

package com.laoxu.test.helloweb.controller;

import com.laoxu.test.helloweb.entity.WorkDt;
import com.laoxu.test.helloweb.service.WorkDtService;
import com.laoxu.test.helloweb.util.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.*;

/**
 * @Description: excel上传
 * @Author laoxu
 * @Date 2020/1/24 16:25
 **/
@Controller
@RequestMapping("/api/excel/")
public class ExcelUploadController {
    @Autowired
    WorkDtService workDtService;

    //接受文件上传
    @RequestMapping("/upload")
    @ResponseBody
    public Map<String,Object> uploadFile(MultipartFile file, HttpServletResponse response) {
        Map<String,Object> map = new HashMap<>(16);
        //解析excel文件
        List<ArrayList<String>> row = ExcelUtil.analysis(file);

        if(row.size()>0){
            //1.清空表
            workDtService.removeAll();
            //2.插入数据
            WorkDt entity = null;
            int size=0;
            for (int i = 0;i<row.size();i++){
                entity = new WorkDt();
                List<String> cell = row.get(i);
                size = cell.size();
                // 不足32个的 补全
                for (int j = size; j < 32; j++) {
                    cell.add("");
                }

                entity.setSeqno(Integer.valueOf(cell.get(0)));
                entity.setD1(cell.get(1));
                entity.setD2(cell.get(2));
                entity.setD3(cell.get(3));
                entity.setD4(cell.get(4));
                entity.setD5(cell.get(5));
                entity.setD6(cell.get(6));
                entity.setD7(cell.get(7));
                entity.setD8(cell.get(8));
                ...

                workDtService.add(entity);


              /*  for (int j = 0;j<cell.size();j++){
                    System.out.print(cell.get(j)+" ");
                }
                System.out.println();*/
            }
        }


        //3.调用存储过程
        Map<String,String> param = new HashMap<>();
        param.put("year","2019");
        param.put("month","12");

        workDtService.updateWorkDt(param);


        Map<String,Object> result = new HashMap<>();
        result.put("code",0);
        result.put("msg","");

        return result;
    }

    /**
     *  导出
     * @param response
     * @throws Exception
     */
    @GetMapping("/export")
    public void export(HttpServletResponse response)  throws Exception{
        // 创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建sheet
        HSSFSheet sheet = workbook.createSheet("sheet1");

//        String id = UUID.randomUUID().toString();
        String fileName = "计算结果.xls";// 设置要导出的文件的名字

        // 获取数据集合
        List<WorkDt> list = workDtService.getAll();
        // 在表中存放查询到的数据放入对应的列
        HSSFRow row = null;
        WorkDt entity=null;
        for (int i = 0; i < list.size(); i++) {
            entity = list.get(i);
            row = sheet.createRow(i);
            row.createCell(0).setCellValue(entity.getSeqno());
            row.createCell(1,CellType.STRING).setCellValue(entity.getD1());
            row.createCell(2,CellType.STRING).setCellValue(entity.getH1());
            row.createCell(3).setCellValue(entity.getD2());
            row.createCell(4).setCellValue(entity.getH2());
            row.createCell(5).setCellValue(entity.getD3());
            row.createCell(6).setCellValue(entity.getH3());
            row.createCell(7,CellType.STRING).setCellValue(entity.getD4());
            row.createCell(8,CellType.STRING).setCellValue(entity.getH4());
            row.createCell(9).setCellValue(entity.getD5());
            row.createCell(10).setCellValue(entity.getH5());
            row.createCell(11,CellType.STRING).setCellValue(entity.getD6());
            row.createCell(12,CellType.STRING).setCellValue(entity.getH6());
            ...
        }

        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition",
                "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
        response.flushBuffer();

        workbook.write(response.getOutputStream());
    }
}