Excel upload EPPlus bulk upload read excel to datatable dynamically

 

Excel upload EPPlus bulk upload read excel to datatable dynamically

public Result CrudUploadExcel()

{

    Result er = new Result();

    try

    {

        var httpRequest = HttpContext.Current.Request;

        if (httpRequest.Files.Count > 0)

        {

            var postedFile = httpRequest.Files[0];

            DataTable dtExcel = new DataTable();

            AMAT.Utilities.ExcelComponent.ExcelReader oExcelReader = new AMAT.Utilities.ExcelComponent.ExcelReader(postedFile.InputStream, "Master");

            //FileInfo fileInfo = new FileInfo("C:\\Users\\Downloads\\ashwini20k.xlsx");

            using (ExcelPackage p = new ExcelPackage(postedFile.InputStream))

            {

                //AMAT.Utilities.ExcelComponent.ExcelReader oExcelReader1 = new AMAT.Utilities.ExcelComponent.ExcelReader(p.Stream, "Master");

                //dtExcel = ReadExcelToTable();

                ExcelWorksheet worksheet = p.Workbook.Worksheets[1];

                int noOfCol = worksheet.Dimension.End.Column;  //get Column Count

                int noOfRow = worksheet.Dimension.End.Row;     //get row count

                int rowIndex = 1;

                for (int c = 1; c <= noOfCol; c++)

                {

                    dtExcel.Columns.Add(worksheet.Cells[rowIndex, c].Text);

                }

                rowIndex = 2;

                for (int r = rowIndex; r <= noOfRow; r++)

                {

                    DataRow dr = dtExcel.NewRow();

                    for (int c = 1; c <= noOfCol; c++)

                    {

                        dr[c - 1] = worksheet.Cells[r, c].Value;

                    }

                    dtExcel.Rows.Add(dr);

                }

            }

            if (oExcelReader.CurrentSheet == null)

            {

                er.IsSuccess = Constants.FAIL;

                er.Message = "Incorrect File.";

            }

 

                }

            }

            catch (Exception ex)

            {

                Common.WriteToLog("API", "UploadExcel()", ex);

                er.IsSuccess = Constants.FAIL;

                er.Message = ex.Message;

            }

            return er;

        }

========-=-=-=-=-= -=-=- -=-=- -=-=- -=-=- -=-=- -=-=- -=-=- -=-=- -=-=- -=-=- -=-=-

public Result CommonCrudUploadExcel()

{

    Result er = new Result();

    try

    {

        var httpRequest = HttpContext.Current.Request;

        if (httpRequest.Files.Count > 0)

        {

            var postedFile = httpRequest.Files[0];

            DataTable dtExcel = new DataTable();

            AMAT.Utilities.ExcelComponent.ExcelReader oExcelReader = new AMAT.Utilities.ExcelComponent.ExcelReader(postedFile.InputStream, "Master");

            using (ExcelPackage p = new ExcelPackage(postedFile.InputStream))

            {

                ExcelWorksheet worksheet = p.Workbook.Worksheets[1];

                int noOfCol = worksheet.Dimension.End.Column;

                int noOfRow = worksheet.Dimension.End.Row;

                oExcelReader.ReadDocument(noOfCol, ref dtExcel);

            }

            if (oExcelReader.CurrentSheet == null)

            {

                er.IsSuccess = Constants.FAIL;

                er.Message = "Incorrect File.";

                return er;

            }

            string user = httpRequest.Params["user"];

            string table = httpRequest.Params["table"];

 

            if (dtExcel.Rows.Count > 20000)

            {

                er.IsSuccess = Constants.FAIL;

                er.Message = "Upload limit 20,000.";

                return er;

            }

   }

            }

            catch (Exception ex)

            {

                Common.WriteToLog("API", "UploadExcel()", ex);

                er.IsSuccess = Constants.FAIL;

                er.Message = ex.Message;

            }

            return er;

        }

Python > Django

 

SQL Server connection:

srv='DRIVER={ODBC Driver 17 for SQL Server};SERVER=dca-dev-XXX;DATABASE=XXXDATABASE;uid=xxxUIDxxx;PWD=XXXXXX'

We can check ODBC driver: Start Menu => ODBC Data Source (32bit)

apexcharts implementaion, vue-apexcharts in VUE

 

apexcharts implementaion, vue-apexcharts in VUE

helper link: https://apexcharts.com/docs/vue-charts/

Step 1.

npm install --save apexcharts npm install --save vue-apexcharts

Step 2. Main.JS insert below code.

import Vue from 'vue';

import App from './App.vue';

import router from './router';

import store from './store';

import 'bootstrap'

//import 'bootstrap/dist/css/bootstrap.min.css'

import VueApexCharts from "vue-apexcharts";//*******1 apex added line in exising

//import 'jquery'

import { BootstrapVue, IconsPlugin } from 'bootstrap-vue';

import 'bootstrap/dist/css/bootstrap.css';

import 'bootstrap-vue/dist/bootstrap-vue.css';

Vue.config.productionTip = false

//Install BootstrapVue

Vue.use(BootstrapVue)

// Optionally install the BootstrapVue icon components plugin

Vue.use(IconsPlugin)

//Vue.use(bootstrap)

Vue.use(VueApexCharts);//*******2 apex added line in exising

new Vue({

  router,

  store,

  render: h => h(App)

}).$mount('#app')

Step 3.

Create a vue page Apex.vue.

<template>

  <div>

    <VueApexCharts

      width="500"

      type="bar"

      :options="options"

      :series="series"

    ></VueApexCharts>

  </div>

</template>

<script>

import VueApexCharts from "vue-apexcharts";

export default {

  name: "Apex",

  components: {

    VueApexCharts,

  },

  props: {},

  data: function () {

    return {

      srchV: "",

      Vendor: [],

      mdlPart: "",

      mdlVendor: "",

      mdlStatus: "All",

      options: {

        chart: {

          id: "vuechart-example",

          toolbar: {

            show: false,

          },

        },

        xaxis: {

          categories: [1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998],

        },

      },

      series: [

        {

          name: "series-1",

          data: [30, 40, 45, 50, 49, 60, 70, 90],

        },

      ],

    };

  },

  methods: {},

  watch: {},

  mounted: function () {},

};

</script>

<style>

</style>

Simple table with 3 column fixed sticky applied in a pv

 

<div class="tbl-container apvTable">

      <table class="custashGrid width100p">

        <tr>

          <th style="width:150px">MAT</th>

          <th style="width:80px">LO</th>

          <th style="width:180px">LO TYPE</th>

          <th>MIN</th>

          <th>MAX</th>

          <th>12M FINAL FCST</th>

          <th>12M STAT FCST</th>

          <th>12M USAGE</th>

          <th>USABLE OH</th>

          <th>WOH</th>

          <th>BOH</th>

          <th>OPEN PO</th>

          <th>BASE OPEN PO</th>

          <th>W PO</th>

          <th>SO</th>

          <th>OPEN SO</th>

          <th>W SO</th>

          <th>FILL%</th>

          <th>OH </th>

          <th>DOI</th>

          <th> FCST</th>

          <th>FCST</th>

          <th>FLEX TSL</th>

          <th>INFLEX TSL</th>

          <th>ZERO BIN </th>

          <th>BIN AGE</th>

          <th>TSL SINCE</th>

          <th>LWS BOX</th>

          <th>BOX</th>

          <th>EPFCST</th>

          <th>FPM ANNUAL FCST</th>

          <th>POINTS</th>

          <th>RATING</th>

          <th>ETA KPI LWS</th>

          <th>ETA KPI STP</th>

        </tr>

        <tr v-for="(x, index) in items1" v-bind:key="index">

          <td>{{ x.MAT }}</td>

          <td>{{ x.LOC }}</td>

          <td>{{ x.LOCT }}</td>

          <td>{{ x.MIN }}</td>

          <td>{{ x.MAX }}</td>

          <td>{{ x.FCST }}</td>

          <td>{{ x.StFcst }}</td>

          <td>{{ x.USAGE }}</td>

          <td>{{ x.OH }}</td>

          <td>{{ x.WOH }}</td>

          <td>{{ x.BOH }}</td>

          <td>{{ x.OPEN_PO }}</td>

          <td>{{ x.BsOPo }}</td>

          <td>{{ x.WOPo }}</td>

          <td>{{ x.OPEN_SO }}</td>

          <td>{{ x.BsOSo }}</td>

          <td>{{ x.WOSo }}</td>

          <td>{{ x.LcFilPrc }}</td>

          <td>{{ x.LcOhDoi }}</td>

          <td>{{ x.LcTslDoi }}</td>

          <td>{{ x.FlFcst }}</td>

          <td>{{ x.InfFcst }}</td>

          <td>{{ x.FlTsl }}</td>

          <td>{{ x.InfTsl }}</td>

          <td>{{ x.ZoSn }}</td>

          <td>{{ x.ZoAg }}</td>

          <td>{{ x.TslS }}</td>

          <td>{{ x.LWS_BOX }}</td>

          <td>{{ x.STP_BOX }}</td>

          <td>{{ x.EPFCST }}</td>

          <td>{{ x.FpmAnFc }}</td>

          <td>{{ x.RskP }}</td>

          <td>{{ x.RskR }}</td>

          <td>{{ x.EtaLws }}</td>

          <td>{{ x.EtaStp }}</td>

        </tr>

      </table>

    </div>

CSS:

.tbl-container {

    height: 200px;

    width: 100%;

    overflow: scroll;

    margin: 0 auto;

}

.custashGrid {

    table-layout: fixed;

}

.custashGrid th,

.custashGrid td {

    text-wrap: nowrap;

}

.custashGrid th,

.custashGrid td {

    text-wrap: nowrap;

    width: 130px;

}

.custashGrid th {

    position: sticky !important;

    top: 0px;

    z-index: 3;

    color: #ffffff;

    background-color: #1f4e77;

    padding: 2px;

}

.custashGrid tr:nth-child(even) td:nth-child(1) {

    position: sticky;

    top: 0;

    left: 0;

    z-index: 3;

    background-color: #e0eaf5;

}

.custashGrid tr:nth-child(odd) td:nth-child(1) {

    position: sticky;

    top: 0;

    left: 0;

    z-index: 3;

    background-color: #fff ;

}

.custashGrid tr:nth-child(even) td:nth-child(2) {

    position: sticky;

    top: 0;

    left: 150px;

    z-index: 3;

    background-color: #e0eaf5;

}

.custashGrid tr:nth-child(odd) td:nth-child(2) {

    position: sticky;

    top: 0;

    left: 150px;

    z-index: 3;

    background-color: #fff;

}

.custashGrid tr:nth-child(even) td:nth-child(3) {

    position: sticky;

    top: 0;

    left: 230px;

    z-index: 3;

    background-color: #e0eaf5;

}

.custashGrid tr:nth-child(odd) td:nth-child(3) {

    position: sticky;

    top: 0;

    left: 230px;

    z-index: 3;

    background-color: #fff;

}

.custashGrid th:nth-child(1){

    position: sticky;

    top: 0;

    left: 0;

    z-index:4;

}

.custashGrid th:nth-child(2) {

    position: sticky;

    top: 0;

    left: 150px;

    z-index:4;

}

.custashGrid th:nth-child(3) {

    position: sticky;

    top: 0;

    left: 230px;

    z-index:4;

}