SpringBoot + Vue+POI实现导入Excel到数据库与导出数据库数据到Excel表格

SpringBoot + Vue实现导入Excel到数据库与导出数据库数据到Excel表格

一、导入excel表格到数据库

（一）后端实现

1.导入POI依赖

  <!--导入POI依赖，ms office文件生成-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>

2.下载模板

controller层

 /**
     *下载模板
     */
    @GetMapping("/download")
    @ApiOperation(value="文件下载", notes="描述的具体信息可以省略")
    public ResultBody download(HttpServletResponse response) {
        try {
            response.setContentType("application/octet-stream;charset=UTF-8");
            String filename = "attachment;filename=\""
                    + URLEncoder.encode("课题管理模板.xls", "UTF-8") + "\";";
            response.setHeader("Content-disposition", filename);
            HSSFWorkbook wb = new HSSFWorkbook();
            service.download(wb);
            try {
                wb.write(response.getOutputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            return ResultBody.failure(e.getMessage());
        }
        return ResultBody.success();
    }

service层

// 接口：
 /**
     * 文件下载
     */
    public void download(HSSFWorkbook wb);

//实现类
//文件下载
    @Override
    public void download(HSSFWorkbook wb) {
        HSSFSheet sheet = null;
        sheet = wb.createSheet("课题管理模板导入模板");
        HSSFRow row1 = sheet.createRow(0);
        sheet.setColumnWidth(0, (int)35.7*100);
        sheet.setColumnWidth(1, (int)35.7*100);
        sheet.setColumnWidth(2, (int)35.7*100);
        sheet.setColumnWidth(3, (int)35.7*100);
        sheet.setColumnWidth(4, (int)35.7*100);
        sheet.setColumnWidth(5, (int)35.7*100);
        sheet.setColumnWidth(6, (int)55.7*100);
        sheet.setColumnWidth(7, (int)35.7*100);
        sheet.setColumnWidth(8, (int)35.7*100);

        /*
         * 设置表格样式
         */
        HSSFCellStyle style = wb.createCellStyle();
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        HSSFFont font = wb.createFont();
        font.setBoldweight(font.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short)11);
        font.setFontName("宋体");

        //将字体格式设置到HSSFCellStyle上
        style.setFont(font);
        style.setBorderTop(HSSFBorderFormatting.BORDER_THIN);
        style.setBorderBottom(HSSFBorderFormatting.BORDER_THIN);
        style.setBorderLeft(HSSFBorderFormatting.BORDER_THIN);
        style.setBorderRight(HSSFBorderFormatting.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        //设置第一个sheet的标题信息
        HSSFCell pCell = row1.createCell(0);
        pCell.setCellValue(new HSSFRichTextString("课题代号"));
        pCell.setCellStyle(style);

        HSSFCell proCell = row1.createCell(1);
        proCell.setCellValue(new HSSFRichTextString("课题名称"));
        proCell.setCellStyle(style);

        HSSFCell modelCell = row1.createCell(2);
        modelCell.setCellValue(new HSSFRichTextString("课题主管"));
        modelCell.setCellStyle(style);

        HSSFCell partCell = row1.createCell(3);
        partCell.setCellValue(new HSSFRichTextString("所属型号"));
        partCell.setCellStyle(style);

        HSSFCell partNameCell = row1.createCell(4);
        partNameCell.setCellValue(new HSSFRichTextString("所属系统"));
        partNameCell.setCellStyle(style);

        HSSFCell yearCountCell = row1.createCell(5);
        yearCountCell.setCellValue(new HSSFRichTextString("课题描述"));
        yearCountCell.setCellStyle(style);
    }

3.上传文件

controller层

 /**
     *导入
     */
    @PostMapping("/upload")
    public ResultBody importData(MultipartFile file, HttpServletRequest req) throws IOException {
        List<Topic> topics= PoiUtils.parseFile2List(file);
        try{
            service.addTopicList(topics);

        }catch (Exception e){
            e.printStackTrace();
            return ResultBody.failure(e.getMessage());
        }
        return ResultBody.success();
    }

service层

//接口
/**
     * 批量新增
     */
    public Integer addTopicList(List<Topic> topics);

   //实现类
@Override
    public List<Topic> findAll() {
        List<Topic> list = topicRepository.findAll();
        return list;
    }

repository层

    /**
     * 查询所有课题  导出数据
     */
    public List<Topic> findAll();

mapper.xml

 <!--批量新增导入-->
    <insert id="addTopicList" parameterType="com.meritdata.cloud.entity.Topic">
        <selectKey keyProperty="id" resultType="String" order="BEFORE">SELECT left(UUID(),32)</selectKey>
        insert into sfy_topic(id,name,number,manager_id,model_id,system_ids,description,create_time,creator)
        values
        <foreach collection="list" item="topic" separator=",">
            ((SELECT REPLACE(UUID(), '-', '') AS id),#{topic.name},#{topic.number},#{topic.managerId},#{topic.modelId},#{topic.systemIds},#{topic.description},now(),#{topic.creator})
        </foreach>
    </insert>

PoiUtils工具类

package com.meritdata.cloud.comments;

import com.meritdata.cloud.entity.Topic;
import net.sf.jsqlparser.statement.select.Top;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class PoiUtils {

    //这是把数据导出到本地保存为Excel文件的方法
    public static ResponseEntity<byte[]> exportJobLevelExcel(List<Topic> topics) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();//创建一个Excel文件

        //创建Excel文档属性，必不可少。少了的话，getDocumentSummaryInformation()方法就会返回null
        workbook.createInformationProperties();
        DocumentSummaryInformation info = workbook.getDocumentSummaryInformation();
        info.setCompany("sfy.");//设置公司信息
        info.setManager("hjg");//设置管理者
        info.setCategory("课题表");//设置文件名

        //设置文件中的日期格式
        HSSFCellStyle datecellStyle = workbook.createCellStyle();
        datecellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));//这个文件的日期格式和平时的不一样

        //创建表单
        HSSFSheet sheet = workbook.createSheet("课题表");
        HSSFRow r0 = sheet.createRow(0);//创建第一行
        HSSFCell c0 = r0.createCell(0);// 创建列
        HSSFCell c1 = r0.createCell(1);// 创建列
        HSSFCell c2 = r0.createCell(2);// 创建列
        HSSFCell c3 = r0.createCell(3);// 创建列
        HSSFCell c4 = r0.createCell(4);// 创建列
        HSSFCell c5 = r0.createCell(5);// 创建列

        c0.setCellValue("课题代号");
        c1.setCellValue("课题名称");
        c2.setCellValue("课题主管");
        c3.setCellValue("所属型号");
        c4.setCellValue("所属系统");
        c5.setCellValue("课题描述");



        for (int i = 0; i < topics.size(); i++) {
            Topic  topic=topics.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            HSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(topic.getNumber());
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(topic.getName());
            HSSFCell cell2 = row.createCell(2);
            cell2.setCellValue(topic.getManagerId());
            HSSFCell cell3 = row.createCell(3);
            cell3.setCellValue(topic.getModelId());
            HSSFCell cell4 = row.createCell(4);
            cell4.setCellValue(topic.getSystemIds());
            HSSFCell cell5 = row.createCell(5);
            cell5.setCellValue(topic.getDescription());
        }
        HttpHeaders headers = new HttpHeaders();
        headers.setContentDispositionFormData("attachment",
                new String("课题表.xls".getBytes("UTF-8"),"iso-8859-1"));
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        ByteArrayOutputStream baos=new ByteArrayOutputStream();
        workbook.write(baos);

        ResponseEntity<byte[]> entity = new ResponseEntity<>(baos.toByteArray(), headers, HttpStatus.CREATED);

        return entity;
    }

    //这是解析上传的Excel文件为对象集合，从而批量添加数据的方法
    public static List<Topic> parseFile2List(MultipartFile file) throws IOException {
        List<Topic> topics =new ArrayList<>();
        HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
        HSSFSheet sheet = workbook.getSheetAt(0);
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();//获取表单所有的行
        for (int i = 1; i < physicalNumberOfRows; i++) {
            HSSFRow row = sheet.getRow(i);
            Topic topic=new Topic();

            HSSFCell c0 = row.getCell(0);
            if(row.getCell(0)!=null){
                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                topic.setNumber(c0.getStringCellValue());
            }

            HSSFCell c1 = row.getCell(1);
            if(row.getCell(1)!=null){
                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                topic.setName(c1.getStringCellValue());
            }

            HSSFCell c2 = row.getCell(2);
            if(row.getCell(2)!=null){
                row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                topic.setManagerId(c2.getStringCellValue());
            }

            HSSFCell c3 = row.getCell(3);
            if(row.getCell(3)!=null){
                row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                topic.setModelId(c3.getStringCellValue());
            }

            HSSFCell c4 = row.getCell(4);
            if(row.getCell(4)!=null){
                row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                topic.setSystemIds(c4.getStringCellValue());
            }

            HSSFCell c5 = row.getCell(5);
            if(row.getCell(5)!=null){
                row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                topic.setDescription(c5.getStringCellValue());
            }

            topics.add(topic);
        }

        return topics;
    }
}

（二）、前端代码

1.导入功能

    <el-button
            size="small"
            type="primary"
            @click="inputdialogVisible = true"
            >导入课题</el-button
          >
          <!-- </el-upload> -->
          <el-button type="primary" @click="exportTopic">导出课题</el-button>

          <el-dialog
            title="导入课题"
            :visible.sync="inputdialogVisible"
            width="30%"
            :before-close="handleClose"
          >
             <el-button @click="download">下载模板</el-button>  

            <el-upload
              action="http://127.0.0.1:8082/gateway/sfy/api/topic/upload"
              :show-file-list="false"
              :before-upload="beforeUpload"
              :on-success="onSuccess"
              :on-error="onError"
              :disabled="importDisabled"
            >
              <el-button
                size="small"
                :disabled="importDisabled"
                type="success"
                :icon="importDataIcon"
                >{{ importDataText }}
              </el-button>
              <!--<label>未选择任何文件</label> -->
            </el-upload>
            <span slot="footer" class="dialog-footer">
              <el-button @click="inputdialogVisible = false">取 消</el-button>
              <el-button type="primary" @click="upload = false"
                >确 定</el-button
              >
            </span>
          </el-dialog>

2.数据定义

export default {
  data() {
    return {
      //导入按钮的文本
      importDataText: "导入数据",
      //导按钮的图标
      importDataIcon: "el-icon-upload2",
      //导入按钮是否被禁用
      importDisabled: false,
      inputdialogVisible: false,
      		}
      	}
      }

3.方法实现

下载方法

 method(){
 	//下载模板
    download() {
      var _this = this;
      _this.axios["business-manage"]
        .get(this.HOST + "/download")
        // .get("http://127.0.0.1:8082/gateway/sfy/api/topic/download")
        .then(function (res) {
          if (!res) {
            return;
          }
          var blob = new Blob([res], {
            type: "application/vnd.ms-excel;charset=utf-8",
          });
          //创建下载地址以及a标签,并且模拟a标签的点击事件进行下载文件。
          var url = window.URL.createObjectURL(blob);
          var aLink = document.createElement("a");
          aLink.style.display = "none";
          aLink.download = "课题管理模板.xls";
          aLink.href = url;
          document.body.appendChild(aLink);
          aLink.click();
        })
        .catch((error) => {
          throw error;
        });
    },
    }

上传文件（导入）方法

method(){
// 导入文件失败后回调
    onError() {
      this.importDataText = "导入数据";
      this.importDataIcon = "el-icon-upload2";
      this.importDisabled = false;
      // this.initEmps();
      this.$message.success("导入失败！");
    },
    // 导入文件成功后回调
    onSuccess() {
      // 成功后文本修改为原来的导入数据
      // 图标修改
      this.importDataIcon = "el-icon-upload2";
      // 将上传组件改为允许使用
      this.importDisabled = false;
      // 调用刷新数据的方法
      // this.initEmps();
      // message 弹出消息
      this.$message.success("导入成功！");
    },
    // 上传文件调用
    beforeUpload() {
      // 将文本修改为正在导入
      this.importDataText = "正在导入";
      // 修改其图标
      this.importDataIcon = "el-icon-loading";
      // 将其上传组件暂时禁用
      this.importDisabled = true;
    },
    handleClose(done) {
      this.$confirm("确认关闭？")
        .then((_) => {
          done();
        })
        .catch((_) => {});
    },
}

二、导出数据到excel表格

（一）、后端实现

1.controller层 （PoiUtil工具类见上）

    /**
     *导出
     */
    @GetMapping("/export")
    //ResponseEntity里面装了所有响应的数据
    public ResponseEntity<byte[]> exportExcel() throws IOException {
        java.lang.System.out.println(service.findAll());
        return PoiUtils.exportJobLevelExcel(service.findAll());
    }

2.service层

//接口
 /**
     * 响应数据时
     */
    public ResponseEntity<byte[]> exportExcel() throws IOException;
 /**
     * 查询所有
     */
    public List<Topic> findAll();

//实现类
@Override
    public ResponseEntity<byte[]> exportExcel() throws IOException {
        return PoiUtils.exportJobLevelExcel(topicRepository.findAll());
    }
 @Override
    public List<Topic> findAll() {
        List<Topic> list = topicRepository.findAll();
        return list;
    }

3.repository层

 /**
     * 查询所有课题  导出数据
     */
    public List<Topic> findAll();

4.mapper.xml

 <select id="findAll" resultType="com.meritdata.cloud.entity.Topic">
            select t.name,t.number,u.name as managerId,a.name as modelId,s.name as systemIds,t.description
                from sfy_topic t
                inner join sfy_user u on t.manager_id = u.id
                inner join sfy_aircraft_model a on a.id = t.model_id
                inner join sfy_system s on t.system_ids = s.id
    </select>

(二)、前端代码

1.导出功能

  <el-button type="primary" @click="exportTopic">导出课题</el-button>

2.方法实现

 method(){
	// 导出，通过blob
     exportTopic() {
      var _this = this;
      _this.axios["business-manage"]
        .get(this.HOST + "/export")
        // .get("http://127.0.0.1:8082/gateway/sfy/api/topic/export")
        .then(function (res) {
          if (!res) {
            return;
          }
          var blob = new Blob([res], {
            type: "application/vnd.ms-excel;charset=utf-8",
          });
          //创建下载地址以及a标签,并且模拟a标签的点击事件进行下载文件。
          var url = window.URL.createObjectURL(blob);
          var aLink = document.createElement("a");
          aLink.style.display = "none";
          aLink.download = "课题表.xls";
          aLink.href = url;
          document.body.appendChild(aLink);
          aLink.click();
        })
        .catch((error) => {
          throw error;
        });
    },
}