springboot+vue: 自动生成建表语句

0. 参考

springboot + vue :https://www.bilibili.com/video/BV1BJ411L7NR?p=34&vd_source=824d8f61906b474c0974b8dce18a69fd
Handsontable:https://blog.csdn.net/weixin_44911028/article/details/119673195

Handsontable简单使用：https://www.jianshu.com/p/e27da506426d

Handsontable.git：https://github.com/handsontable/handsontable

前端接收数据：https://blog.csdn.net/NeverFG/article/details/127252859

1. 编写前端

1.1 部署前端环境

• 安装node：https://blog.csdn.net/dkm123456/article/details/121890891

• 安装全局脚手架：npm install -g @vue/cli

1.2 IDEA创建项目

• 创建工程

cd 到创建好的文件夹
vue create 工程名
## 再选择版本
## 再使用IDEA打开项目

• 安装依赖

## 安装element-ui
cd 工程目录 && npm i element-ui -S
npm instal axios
## 下面的都要装 报错就强制安装  --force
"dependencies": {
    "@handsontable/vue": "^12.3.2",
    "": "^1.3.4",
    "core-js": "^3.8.3",
    "element-ui": "^2.15.13",
    "handsontable": "^12.3.2",
    "vue": "^2.6.14",
    "vue-axios": "^3.5.2"
  }

## 其他常用命令
npm run update   ## 安装依赖
npm run serve  ## 启动本地调试
npm run lib   ## 编译打包，生成编译后的目录：es,lib

• main.js全局引入

import Vue from 'vue'
import App from './App.vue'
import Handsontable from 'handsontable'
import { HotTable } from '@handsontable/vue'
import ElementUI from 'element-ui';
import 'element-ui/lib/theme-chalk/index.css';

import axios from 'axios';

Vue.prototype.$axios = axios
axios.defaults.baseURL = "http://localhost:8888"

console.log(`Handsontable: v${Handsontable.version} (${Handsontable.buildDate}) Wrapper: v${HotTable.version} Vue: v${Vue.version}`);

Vue.use(ElementUI);
Vue.config.productionTip = false

new Vue({
  render: h => h(App),
}).$mount('#app')

• 编写app.vue

<template>

  <div id="example">

    <p v-html="textdata"></p>



<!--    <button type="button" onclick="">生成建表语句</button>-->
<!--    <button type="button" @onclick="testRefs()">测试ref用法</button>-->
    <el-container>
      <el-header>
        <!--        <el-button  @click="testRefs()">测试ref用法</el-button>-->
        <el-button  @click="printData5()">输出</el-button>
        <el-button  @click="printData6()">输出</el-button>
<!--        <el-button  @click="printData4()">输出4</el-button>-->
        <!--        <el-button  @click="printData2()">输出2</el-button>-->
        <!--        <el-button  @click="printData3()">输出3</el-button>-->
      </el-header>
    </el-container>

    <hot-table :settings="hotSettings" ref="hotTableComponent">
      <hot-column :width="120" title="A" data="dataColumnProperty"></hot-column>
      <hot-column :width="120" title="B" data="dataColumnProperty1"></hot-column>
      <hot-column :width="120" title="C" data="dataColumnProperty3"></hot-column>
      <hot-column :width="120" title="D" data="dataColumnProperty4"></hot-column>
    </hot-table>
  </div>
</template>



<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/handsontable/dist/handsontable.full.min.js"></script>
<script>
import { HotTable,HotColumn } from '@handsontable/vue';
import 'handsontable/dist/handsontable.full.css';
import './assets/styles.css';
import axios from "axios";

export default {
  name: 'App',
  data: function () {
    return {
      hotSettings: {
        data: [
          ["", "Ford", "Volvo", "Toyota", "Honda"],
          ["2016", 10, 11, 12, 13],
          ["2017", 20, 11, 14, 13],
          ["2018", 30, 15, 12, 13],
        ],
        colHeaders: true,
        rowHeaders: true,
        licenseKey: "non-commercial-and-evaluation",
        height: '100%',
        width: '100%'
      },
      textdata: ""
    };
  },
  methods: {
    /**
     * @description: 测试ref用法
     * @param {*}
     * @return {*}
     */
    testRefs(){
      console.log(111111);
    },
    printData(){
      console.log(this.$refs.hotTableComponent.hotInstance.getData())
    },
    printData2(){
      var $container=$("#App");
      var handsontable=$container.data('handsontable');
      var data=handsontable.getData();
      var json=SerializeJsonToStr(data);
        //多次多行替换//json = json.replace(new RegExp(',{}','gm')  ,"");
        //json = json.replace(new RegExp('"parseDate(','gm')  ,"parseDate(");
        //json = json.replace(new RegExp(')",','gm')  ,"),");
      alert(json);
      var chartData=eval('('+json+')');
      return chartData;
    },
    printData3(){
      console.log(this.dataList)
    },
    printData4(){
      console.log(this.$axios)
      this.$axios.post('http://localhost:9091/auto/getData',this.$refs.hotTableComponent.hotInstance.getData())
          .then(res=>res.config.data)
          .then(res=>{
        console.log('res',res)
            return res
      })
    },
    printData5(){
      this.$axios.post('http://192.168.8.39:9091/auto/getData',this.$refs.hotTableComponent.hotInstance.getData())
          .then(res=>res.data)
          .then(res=>{
            console.log(res)
            this.textdata=res
          })

    },
    printData6(){
      this.$axios.post('http://localhost:9091/auto/getData',this.$refs.hotTableComponent.hotInstance.getData())
          .then(res=>res.data)
          .then(res=>{
            console.log(res)
            this.textdata=res
          })

    },
  },

  components: {
    HotTable,HotColumn
  }
}
</script>

<style>
</style>

2. 编写后段

• 步骤

1. 读取前端数据
2. 处理加工数据
3. 返回给前端

• 后端脚本

package org.example;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;


public class App {
    public static void main(String[] args) {
            jxl.Workbook readwb = null;
            // List<String> list = new ArrayList<String>();
            try {
                // 构建Workbook对象, 只读Workbook对象 直接从本地文件创建Workbook
                readwb = Workbook.getWorkbook(new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\TEST\\student.xls")));
                // Sheet的下标是从0开始 获取第一张Sheet表
                Sheet readsheet = readwb.getSheet(0);
                // 获取Sheet表中所包含的总列数
                int rsColumns = readsheet.getColumns();
                // 获取Sheet表中所包含的总行数
                int rsRows = readsheet.getRows();
                // 获取指定单元格的对象引用
                String[][] arr = (new String[rsColumns][rsRows]);
                String[] arr1 = new String[rsRows+1];

                FileOutputStream fos = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\TEST\\建表语句.txt"));
                OutputStreamWriter dos = new OutputStreamWriter(fos);
                for (int i = 0; i < rsColumns; i++) {
                    for (int j = 0; j < rsRows; j++) {
                        Cell cell = readsheet.getCell(i, j);
                        // System.out.print(cell.getContents() + " ");
                        // list.add(cell.getContents());
                        arr[i][j] = cell.getContents();
                    }
                }
                arr1[0] = "create table if not exits stage." + arr[3][1] + "( \n    ";
                System.out.println(arr1[0]);
                dos.write(arr1[0]);
                for (int i = 1; i < rsRows; i++) {
                    arr1[i] =  arr[0][i]  + " " + arr[2][i] + " comments " + "\"" + arr[1][i] + "\", ";
                    System.out.println(arr1[i] + "    ");
                    dos.write(arr1[i]+ " \n   ");
                }
                arr1[rsRows] = "data_date" + " String" + " comments \"数据日期\" )"
                        + "\n" + "partition by (etl_date string)"
                        + "\n" + "ROW FORMAT DELIMITED"
                        + "\n" + "STORED AS ORC"
                        + "\n" + "TBLPROPERTIES(\"orc.compress\" = \"SNAPPY\");";
                System.out.println(arr1[rsRows]);
                dos.write(arr1[rsRows]);
                dos.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                readwb.close();


            }
        }
}