SpringBoot+Vue之表格的CRUD與匯入匯出

chetwhy發表於2019-04-26
Spring BootVue

SpringBoot+Vue–表格操作

工具環境：jdk1.8，myql5.7，webstorm2018, idea2018，SQLyog，postman

該專案推薦剛入門springboot開發的朋友們練手使用

業務簡介

建立一個頁面，展示玩家資訊，支援玩家的增刪查改，並支援頁面表格的匯入匯出。

目的

梳理業務邏輯和開發流程，理解並獨立開發。步驟儘量簡要

效果預覽

SpringBoot+Vue之表格的CRUD與匯入匯出

開發流程

建立資料庫指令碼

匯入userinfo指令碼資訊

/*Table structure for table `userInfo` */

DROP TABLE IF EXISTS `userInfo`;

CREATE TABLE `userInfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL COMMENT '使用者名稱',
  `gameLevel` enum('黑鐵','黃銅','白銀','黃金','鑽石') DEFAULT NULL,
  `createDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `enabled` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8;

/*Data for the table `userInfo` */

INSERT  INTO `userInfo`(`id`,`username`,`gameLevel`,`createDate`,`enabled`) VALUES 
(1,'張三','鑽石','2018-01-11 21:19:14',1),
(2,'李四',NULL,'2018-01-11 21:19:20',1),
(3,'王五','白銀','2018-01-11 21:35:39',1),
(4,'趙六','黃銅','2018-01-11 22:42:12',0),
(5,'小紅','黑鐵','2018-01-14 16:18:50',1),
(6,'小明','黃金','2018-01-14 16:19:00',1),
(7,'小花','黃銅','2018-01-14 16:19:14',1),
(8,'韓梅梅','白銀','2018-01-14 16:19:24',1);
複製程式碼

效果如下：

SpringBoot+Vue之表格的CRUD與匯入匯出

配置後端SpringBoot專案

1、匯入相關依賴

(pom.xml)

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.0.1</version>
    </dependency>

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.10</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.27</version>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>com.google.code.google-collections</groupId>
        <artifactId>google-collect</artifactId>
        <version>snapshot-20080530</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.0.1</version>
    </dependency>
</dependencies>
複製程式碼

注意

  • idea預設安裝最新的版本依賴，我這裡是mysql5.7，所以特別指定一下版本

  • 可以先定義一個HelloController測試類，確保web專案可以正常啟動。

SpringBoot+Vue之表格的CRUD與匯入匯出

3、配置資料庫連線和網路埠

（application.properties）

server.port=8081
#列印日誌
logging.level.com.chety.sheetdemo.mapper=debug 

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql:///test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=1234
複製程式碼

4、建立實體類

  • 定義實體類User.java和持久層UserMapper介面和xml對映檔案，可以用逆向工程生成。

  • 因為表中有日期的欄位，需要JSON轉換時，會出錯，所以要註解標明內容格式

（User.java）

public class User {
    private Integer id;

    private String nickname;

    private String gamelevel;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "Asia/Shanghai")
    private Date createdate;

    private Boolean enabled;
    
    //getter and setter...
    
}    
複製程式碼

5、啟動類中新增資料庫元件並配置mybatis對映檔案的掃描

（SheetdemoApplication.java）

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
@MapperScan(basePackages = "com.chety.sheetdemo.mapper")
public class SheetdemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(SheetdemoApplication.class, args);
    }

}
複製程式碼

6、配置資料來源路徑(pom.xml)

<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
        </resource>
        <resource>
            <directory>src/main/resources</directory>
        </resource>
    </resources>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
</build>
複製程式碼

為節省篇幅，只講查詢的前後端詳細步驟。目的是理清思路，瞭解業務邏輯，後面的可以舉一反三，也可以參考後面的完整程式碼。

編寫後端介面程式碼

1、定義使用者查詢方法

（UserController.java）

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    UserService userService;

    @GetMapping("/")
    public List<User> getAllUser() {
        return userService.getAllUser();
    }
}    
複製程式碼

2、查詢服務類

(UserService.java)

@Service
public class UserService {
    @Autowired
    UserMapper userMapper;

    public List<User> getAllUser() {
        return userMapper.getAllUser();
    }
}    
複製程式碼

3、定義介面方法

(UserMapper.java)

List<User> getAllUser();
複製程式碼

4、sql語句

（UserMapper.xml）

<select id="getAllUser" resultMap="BaseResultMap">
  select * from userinfo;
</select>
複製程式碼

5、使用postman進行後端測試，get請求http://127.0.0.1:8081/user/。返回一組玩家資訊的JSON陣列

SpringBoot+Vue之表格的CRUD與匯入匯出

前端Vue專案搭建

頁面模板

1、開啟webstorm的terminal視窗，安裝ElementUI和Axios 的依賴

npm i element-ui -S
npm install axios
複製程式碼

2、引入剛下載下的資源

（main.js)

import Vue from 'vue'
import App from './App'
import router from './router'
import ElementUI from 'element-ui';
import 'element-ui/lib/theme-chalk/index.css';

import {postRequest} from "./utils/api";
import {postKeyValueRequest} from "./utils/api";
import {putRequest} from "./utils/api";
import {deleteRequest} from "./utils/api";
import {getRequest} from "./utils/api";
import {uploadFileRequest} from "./utils/api";

Vue.use(ElementUI)

Vue.config.productionTip = false

Vue.prototype.postRequest = postRequest;
Vue.prototype.postKeyValueRequest = postKeyValueRequest;
Vue.prototype.putRequest = putRequest;
Vue.prototype.deleteRequest = deleteRequest;
Vue.prototype.getRequest = getRequest;
Vue.prototype.uploadFileRequest = uploadFileRequest;

/* eslint-disable no-new */
new Vue({
  el: '#app',
  router,
  components: { App },
  template: '<App/>'
})
複製程式碼

3、配置後端訪問的路徑8081

（config\index.js）

// Paths
assetsSubDirectory: 'static',
assetsPublicPath: '/',
proxyTable: {
  '/': {
    target: 'http://localhost:8081',
    changeOrigin: true,
    pathRewrite: {
      '^/': ''
    }
  }
},
複製程式碼

4、匯入一個http請求介面的工具檔案

（src\utils\api.js）

'use strict'
// Template version: 1.3.1
// see http://vuejs-templates.github.io/webpack for documentation.

const path = require('path')

module.exports = {
  dev: {

    // Paths
    assetsSubDirectory: 'static',
    assetsPublicPath: '/',
    proxyTable: {
      '/': {
        target: 'http://localhost:8081',
        changeOrigin: true,
        pathRewrite: {
          '^/': ''
        }
      }
    },

    // Various Dev Server settings
    host: 'localhost', // can be overwritten by process.env.HOST
    port: 8080, // can be overwritten by process.env.PORT, if port is in use, a free one will be determined
    autoOpenBrowser: false,
    errorOverlay: true,
    notifyOnErrors: true,
    poll: false, // https://webpack.js.org/configuration/dev-server/#devserver-watchoptions-

    
    /**
     * Source Maps
     */

    // https://webpack.js.org/configuration/devtool/#development
    devtool: 'cheap-module-eval-source-map',

    // If you have problems debugging vue-files in devtools,
    // set this to false - it *may* help
    // https://vue-loader.vuejs.org/en/options.html#cachebusting
    cacheBusting: true,

    cssSourceMap: true
  },

  build: {
    // Template for index.html
    index: path.resolve(__dirname, '../dist/index.html'),

    // Paths
    assetsRoot: path.resolve(__dirname, '../dist'),
    assetsSubDirectory: 'static',
    assetsPublicPath: '/',

    /**
     * Source Maps
     */

    productionSourceMap: true,
    // https://webpack.js.org/configuration/devtool/#production
    devtool: '#source-map',

    // Gzip off by default as many popular static hosts such as
    // Surge or Netlify already gzip all static assets for you.
    // Before setting to `true`, make sure to:
    // npm install --save-dev compression-webpack-plugin
    productionGzip: false,
    productionGzipExtensions: ['js', 'css'],

    // Run the build command with an extra argument to
    // View the bundle analyzer report after build finishes:
    // `npm run build --report`
    // Set to `true` or `false` to always turn it on or off
    bundleAnalyzerReport: process.env.npm_config_report
  }
}
複製程式碼

5、新建一個展示玩家資訊的元件，在裡面先建立一個表格，表格樣式來自ElementUi官網元件ElementUi

（src/components/User.vue）

搭建表格結構

<template>
  <div style="margin: 20px">
    <div style="display: flex;justify-content: center">
      <h2>玩家資訊表格</h2>
    </div>
    <hr>
    <div style="display: flex;justify-content: center">
      <h2>加入新召喚師</h2>
    </div>
    <hr>
    <div style="margin-top: 10px;">
      <el-table
        :data="userinfo"
        border
        style="width: 100%">
        <el-table-column
          prop="id"
          label="ID"
          width="180">
        </el-table-column>
        <el-table-column
          prop="nickname"
          label="暱稱">
        </el-table-column>
        <el-table-column
          prop="gamelevel"
          label="段位">
        </el-table-column>
        <el-table-column
          prop="createdate"
          label="建立時間">
        </el-table-column>
        <el-table-column
          prop="enabled"
          label="是否可用">
        </el-table-column>
        <el-table-column label="操作">
        </el-table-column>
      </el-table>
    </div>
  </div>
</template>

<script>
  export default {
    name: "User",
    data() {
      return {
        // 存放表格資料
        userinfo: [],
      }
    },
  }
</script>
<style scoped>
</style>
複製程式碼

6、配置玩家資訊的訪問路由

（src/router/index.js）

// ...
import User from '@/components/User'

routes: [{
    path:'/user',
    name:'User',
    component:User,
    hidden:true
  }
   // ...    
]
複製程式碼

7、執行dev，用瀏覽器訪問埠http://localhost:8080/#/user

SpringBoot+Vue之表格的CRUD與匯入匯出

表格完善

1、特殊欄位處理

”段位“單元格中加入插槽，有段位顯示段位名，沒有段位的地方是空，顯示”暫無段位“

<el-table-column
  prop="gamelevel"
  label="段位">
  <template slot-scope="scope">
    <span v-if="scope.row.gamelevel">{{scope.row.gamelevel}}</span>
    <span v-else>暫無段位</span>
  </template>
</el-table-column>
複製程式碼

“是否可用”單元格中加入插槽，可用顯示“是”，否則顯示”否“

<el-table-column
  prop="enabled"
  label="是否可用">
  <template slot-scope="scope">
    <el-tag type="success" v-if="scope.row.enabled">是</el-tag>
    <el-tag type="info" v-else>否</el-tag>
  </template>
</el-table-column>
複製程式碼

“操作”單元格，加入兩個按鈕，編輯和刪除

<el-table-column label="操作">
  <template slot-scope="scope">
    <el-button type="warning">編輯</el-button>
    <el-button type="danger">刪除</el-button>
  </template>
</el-table-column>
複製程式碼

2、建立查詢所有玩家的方法

<script>
  export default {
	// ...
	// 鉤子方法，使頁面重新整理時自動查詢
    mounted(){
      this.initUser();
    },      
    methods:{
      initUser() {
        // http請求，get方式
        this.getRequest("/user/").then(resp => {
          // 請求成功，返回的json資料直接填入userinfo陣列中
          if (resp) {
            this.userinfo=resp;
          }
        })
      }
    }
  }
</script>
複製程式碼

3、再次檢視頁面，已有資料，ok

SpringBoot+Vue之表格的CRUD與匯入匯出

後續

後面的增刪改都很類似。主要是為了給大家理清前後端開發api的思路和步驟

下面是完整的DRUD及匯入匯出檔案的程式碼

1、前端頁面(src/components/User.vue)

由於Vue雙向繫結的特性，在修改資料資料時，重新拷貝定義了一組行資料。

<template>
    <div style="margin: 20px">
      <div>
        <div style="display: flex;justify-content: center">
          <h2>玩家資訊表格</h2>
        </div>
        <hr>
        <span>加入新召喚師：</span>
        <el-input v-model="userselect.nickname" placeholder="請輸入暱稱" style="width: 200px"></el-input>
        <el-select v-model="userselect.gamelevel" placeholder="請選擇段位">
          <el-option
            v-for="item in userselects"
            :key="item.value"
            :label="item.label"
            :value="item.value">
          </el-option>
        </el-select>
        <el-button type="primary" icon="el-icon-circle-plus" @click="addUser">新增</el-button>
        <el-button icon="el-icon-download" type="success" @click="exportFile">匯出</el-button>
        <el-upload
          style="display: inline"
          :show-file-list="false"
          :on-success="onSuccess"
          :on-error="onError"
          :before-upload="beforeUpload"
          action="/user/import">
          <el-button type="info" :disabled="!enabledUploadBtn" :icon="uploadBtnIcon">{{btnText}}</el-button>
        </el-upload>
      </div>
      <hr>
      <div style="margin-top: 10px;">
        <el-table
          :data="userinfo"
          border
          style="width: 100%">
          <el-table-column
            prop="id"
            label="ID"
            width="180">
          </el-table-column>
          <el-table-column
            prop="nickname"
            label="暱稱">
          </el-table-column>
          <el-table-column
            prop="gamelevel"
            label="段位">
            <template slot-scope="scope">
              <span v-if="scope.row.gamelevel">{{scope.row.gamelevel}}</span>
              <span v-else>暫無段位</span>
            </template>
          </el-table-column>
          <el-table-column
            prop="createdate"
            label="建立時間">
          </el-table-column>
          <el-table-column
            prop="enabled"
            label="是否可用">
            <template slot-scope="scope">
              <el-tag type="success" v-if="scope.row.enabled">是</el-tag>
              <el-tag type="info" v-else>否</el-tag>
            </template>
          </el-table-column>
          <el-table-column label="操作">
            <template slot-scope="scope">
              <el-button type="warning" @click="toUpdateUser(scope.row)">編輯</el-button>
              <el-button type="danger" @click="deleteUser(scope.row.id)">刪除</el-button>
            </template>
          </el-table-column>
        </el-table>
      </div>
      <el-dialog
        title="修改"
        :visible.sync="dialogVisible"
        width="30%">
        <div>
          <table>
            <tr>
              <td>暱稱</td>
              <td><el-input v-model="userupdate.nickupdate" placeholder="新暱稱"></el-input></td>
            </tr>
            <tr>
              <td>段位</td>
              <td>
                <el-select v-model="userupdate.levelupdate" placeholder="請輸入段位">
                  <el-option
                    v-for="item in userselects"
                    :key="item.value"
                    :label="item.label"
                    :value="item.value">
                  </el-option>
                </el-select>
              </td>
            </tr>
          </table>
        </div>
        <span slot="footer" class="dialog-footer">
          <el-button @click="dialogVisible = false">取 消</el-button>
          <el-button type="primary" @click="doUpdateUser">確 定</el-button>
        </span>
      </el-dialog>
    </div>
</template>

<script>
    export default {
      name: "User",
      mounted(){
        this.initUser();
      },
      data() {
        return {
          uploadBtnIcon:'el-icon-upload2',
          enabledUploadBtn:true,
          btnText:'匯入',
          dialogVisible: false,
          userinfo: [],
          userselect:{
            nickname:'',
            gamelevel:''
          },
          userupdate:{
            id:'',
            nickupdate:'',
            levelupdate:'',
            createdate:'',
            enabled:''
          },
          userselects:[{
            value:'鑽石',
            label:'鑽石'
          },{
            value:'黃金',
            label:'黃金'
          },{
            value:'白銀',
            label:'白銀'
          },{
            value:'黑鐵',
            label:'黑鐵'
          },{
            value:null,
            label:'暫無段位'
          },]
        }
      },
      methods:{
        onSuccess(response,file,fileList) {
          this.enabledUploadBtn=true;
          this.uploadBtnIcon='el-icon-upload2';
          this.btnText='匯入'
        },
        onError(err, file, fileList) {
          this.enabledUploadBtn=true;
          this.uploadBtnIcon='el-icon-upload2'
          this.btnText='匯入'
        },
        beforeUpload(file) {
          this.enabledUploadBtn=false;
          this.uploadBtnIcon='el-icon-loading';
          this.btnText='正在匯入'
        },
        exportFile() {
          window.open("/user/export", "_parent");
        },
        addUser() {
          this.postRequest("/user/",this.userselect).then(resp => {
            if (resp) {
              this.initUser();
              this.$message({
                type: 'success',
                message: '新增成功!'
              });
            }
          })
        },
        deleteUser(id) {
          this.$confirm('此操作將使玩家永遠離開峽谷, 是否繼續?', '提示', {
            confirmButtonText: '確定',
            cancelButtonText: '取消',
            type: 'warning'
          }).then(() => {
            this.deleteRequest("/user/"+id).then(resp => {
              if (resp) {
                this.$message({
                  type: 'success',
                  message: '刪除成功!'
                });
                this.initUser();
              }
            })
          }).catch(() => {
            this.$message({
              type: 'info',
              message: '已取消刪除'
            });
          });
        },
        toUpdateUser(data) {          
          // alert(JSON.stringify(data));
          this.dialogVisible = true;
          this.userupdate.id=data.id;
          this.userupdate.nickupdate=data.nickname;
          this.userupdate.levelupdate=data.gamelevel;
          this.userupdate.createdate=data.createdate;
          this.userupdate.enabled=data.enabled

        },
        doUpdateUser() {
          this.dialogVisible = false;
          this.userselect.id=this.userupdate.id;
          this.userselect.nickname=this.userupdate.nickupdate;
          this.userselect.gamelevel=this.userupdate.levelupdate;
          this.userselect.createdate=this.userupdate.createdate;
          this.userselect.enabled=this.userupdate.enabled;
          alert(JSON.stringify(this.userselect))
          this.putRequest("/user/",this.userselect).then(resp => {
            if (resp) {
              this.initUser();
              this.$message({
                type: 'success',
                message: '修改成功!'
              });
            }else {
              this.$message({
                type: 'info',
                message: '未知錯誤，更新失敗'
              });
            }
          })
        },
        initUser() {
          this.getRequest("/user/").then(resp => {
            if (resp) {
              this.userinfo=resp;
            }
          })
        }
      }
    }
</script>

<style scoped>

</style>
複製程式碼

2、後端介面

2.1 統一返回資訊類

public class RespEntity {
    // 狀態碼
    private Integer status;
    // 返回資訊
    private String msg;
    // 返回物件，可選
    private Object obj;

    public static RespEntity success(String msg, Object obj) {
        return new RespEntity(200, msg, obj);
    }

    public static RespEntity success(String msg) {
        return new RespEntity(200, msg, null);
    }

    public static RespEntity fail(String msg, Object obj) {
        return new RespEntity(500, msg, obj);
    }

    public static RespEntity fail(String msg) {
        return new RespEntity(500, msg, null);
    }

    private RespEntity(Integer status, String msg, Object obj) {
        this.status = status;
        this.msg = msg;
        this.obj = obj;
    }
    private RespEntity() {   }
    
    // getter and setter...
}    
複製程式碼

2.2 檔案操作的工具類

public class PoiUtils {

    /**
     * 根據集合返回一個http響應體
     * @param allUsers 職稱等級列表
     * @return http響應實體
     */
    public static ResponseEntity<byte[]> exportUserExcel(List<User> allUsers) throws IOException {
        // 建立一個excel檔案
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 設定文件屬性
        workbook.createInformationProperties();
        // 獲取文件的文件摘要資訊
        DocumentSummaryInformation sumInfo = workbook.getDocumentSummaryInformation();
        // 設定摘要資訊
        sumInfo.setCompany("towards");
        sumInfo.setManager("chet");
        sumInfo.setCategory("user information");
        // 建立一個新的單元格樣式並將其新增到工作簿的樣式表中。
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 獲取與給定格式字串匹配的格式索引，自動將“文字”轉換為excel的格式字串來表示文字
        short format = HSSFDataFormat.getBuiltinFormat("m/d/yy");
        // 設定表格中的日期格式,設定資料格式(必須是有效格式)
        cellStyle.setDataFormat(format);

        // 為這個HSSFWorkbook建立一個HSSFSheet，將它新增到工作表中
        HSSFSheet sheet = workbook.createSheet("玩家資訊表");

        // 設定表格列名
        // 在工作表中建立一個新行
        HSSFRow row = sheet.createRow(0);
        // 在行中建立新的單元格,引數為列號
        HSSFCell cell0 = row.createCell(0);
        HSSFCell cell1 = row.createCell(1);
        HSSFCell cell2 = row.createCell(2);
        HSSFCell cell3 = row.createCell(3);
        HSSFCell cell4 = row.createCell(4);

        // 為單元格設定一個字串值
        cell0.setCellValue("編號");
        cell1.setCellValue("暱稱");
        cell2.setCellValue("段位");
        cell3.setCellValue("建立時間");
        cell4.setCellValue("是否可用");

        // 迴圈設定表格中的資料
        for (int i = 0; i < allUsers.size(); i++) {
            // 返回列表中指定位置的元素
            User level = allUsers.get(i);
            HSSFRow r = sheet.createRow(i + 1);
            HSSFCell c0 = r.createCell(0);
            HSSFCell c1 = r.createCell(1);
            HSSFCell c2 = r.createCell(2);
            HSSFCell c3 = r.createCell(3);
            HSSFCell c4 = r.createCell(4);

            c0.setCellValue(level.getId());
            c1.setCellValue(level.getNickname());
            c2.setCellValue(level.getGamelevel()==null?"暫無段位":level.getGamelevel());
            // 設定日期格式
            c3.setCellStyle(cellStyle);
            c3.setCellValue(level.getCreatedate());
            c4.setCellValue(level.getEnabled() ? "是" : "否");
        }
        // 建立一個http請求頭
        HttpHeaders headers = new HttpHeaders();
        // 設定，引數：1.控制方式-內嵌，2.檔名，在瀏覽器需轉換格式
        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);
        // 使用給定的主體、頭和狀態程式碼建立一個新的http實體
        ResponseEntity<byte[]> responseEntity = new ResponseEntity<>(baos.toByteArray(), headers, HttpStatus.CREATED);
        return responseEntity;
    }

    public static List<User> parseFileList(MultipartFile file) throws IOException {
        List<User> Users = 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);
            HSSFCell c0 = row.getCell(0);
            HSSFCell c1 = row.getCell(1);
            HSSFCell c2 = row.getCell(2);
            HSSFCell c3 = row.getCell(3);
            HSSFCell c4 = row.getCell(4);

            double numericCellValue = c0.getNumericCellValue();
            User level = new User();
            level.setId((int)numericCellValue);
            level.setNickname(c1.getStringCellValue());
            level.setGamelevel(c2.getStringCellValue().equals("暫無段位")?null:c2.getStringCellValue());
            level.setCreatedate(c3.getDateCellValue());
            level.setEnabled(c4.getStringCellValue().equals("是"));
            Users.add(level);
        }
        return Users;
    }
}
複製程式碼

2.3 控制層（UserController.java）

/**
 * 玩家資訊表格操作，crud與檔案操作
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    UserService userService;

    /**
     * 查詢所有玩家資訊
     * @return User列表
     */
    @GetMapping("/")
    public List<User> getAllUser() {
        return userService.getAllUser();
    }

    /**
     * 新增使用者
     * @param user
     * @return RespEntity物件
     */
    @PostMapping("/")
    public RespEntity addUser(@RequestBody User user) {
        if (userService.addUser(user) == 1) {
            return RespEntity.success("新增成功");
        }
        return RespEntity.fail("新增失敗");
    }

    /**
     * 根據玩家編號刪除
     * @param id
     * @return RespEntity物件
     */
    @DeleteMapping("/{id}")
    public RespEntity deleteUserById(@PathVariable("id") Integer id) {
        if ((userService.deleteUserById(id)) == 1) {
            return RespEntity.success("刪除成功");
        }
        return RespEntity.fail("刪除失敗");
    }

    /**
     * 更新玩家資訊，只有暱稱和段位可改
     * @param user
     * @return RespEntity物件
     */
    @PutMapping("/")
    public RespEntity updateUser(@RequestBody User user) {
        if ((userService.updateUser(user)) == 1) {
            return RespEntity.success("更新成功");
        }
        return RespEntity.fail("更新失敗");
    }

    /**
     * 匯出excel檔案
     * @return
     * @throws IOException
     */
    @GetMapping("/export")
    public ResponseEntity<byte[]> exportExcel() throws IOException {
        return PoiUtils.exportUserExcel(userService.getAllUser());
    }

    /**
     * 匯入excel檔案
     * @param file
     * @param req
     * @return
     * @throws IOException
     */
    @PostMapping("/import")
    public RespEntity importFile(MultipartFile file, HttpServletRequest req) throws IOException {
        List<User> users = PoiUtils.parseFileList(file);
        System.out.println(users);
        int n = userService.listFileToDb(users);
        System.out.println(n);
        return RespEntity.success("匯入成功");
    }
}
複製程式碼

2.4 服務類(UserService.java)，略。這裡沒有新增判斷邏輯，直接注入dao層呼叫即可。

注：在檔案匯入時，即新增玩家時，不建議使用Java程式碼迴圈insert插入，而是直接使用sql語句批量匯入即可。

SpringBoot+Vue之表格的CRUD與匯入匯出

<insert id="listFileToDb">
  INSERT INTO userinfo(
  nickname,gameLevel,createDate,enabled)
  VALUES
  <foreach collection="users" item="user" separator=",">
    (#{user.nickname},#{user.gamelevel},#{user.createdate},#{user.enabled})
  </foreach>
</insert>
複製程式碼

相關文章