ZDIRY-TUFWT-EBONM-EYJ00-IDBLANTER.COM
ZDIRY-TUFWT-EBONM-EYJ00

BLANTERWISDOM105

Upload File Excel Menggunakan ASP NET MVC Ke Database SQL Server

4/27/2020

Bertemu lagi dengan Agung Panduan, kali ini kita akan membahas kelanjutan Cara Upload Drag and Drop File Excel Menggunakan Bootstrapdi ASP. MVC. Dimana sebelumnya upload excel tidak disimpan terlebih dahulu ke database namun langsung ditampilkan ke GridView pada ASP NET. Hal itu dilakukan bertujuan agar mempermudah dalam memahami proses upload excel di ASP NET.

Excel-ASP NET-SQLServer

Proses upload excel dari ASP NET MVC ke dalam Database SQL Server pada artikel kali ini. Dilakukan dengan cara merubah file excel yang di upload menjadi sebuah kumpulan value (array) dan akan di simpan di table sementara (table types) pada database. Setelah data tersimpan pada table types tersebut selanjutnya data dipindahkan ke dalam table lainnya dengan menggunakan Stored Procedures. Yang telah anda ketahui Stored Procedures ini merupakan sekumpulan perintah-perintah SQL yang tersimpan dengan nama tertentu dan diproses sebagai sebuah kesatuan atau bisa dikatakan sebuah sub program yang tersimpan di database. Stored Procedures menerima parameter sebagai input dan mengembalikan nilai-nilai dalam bentuk parameter output kepada function pada aplikasi yang memanggilnya.

Adapun proses upload excel yang dimaksud adalah sebagai berikut:

Langkah 1

Buatlah database ‘TEST’ dan eksekusi table, table types, dan stored procedures seperti di bawah ini di Query Database SQL Server,

USE [TEST]
GO
/****** Object:  StoredProcedure [dbo].[SP_InsertUploadExcel]    Script Date: 27/04/2020 14:24:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_InsertUploadExcel]
 -- Add the parameters for the stored procedure here
 @ro_v_err_mesg varchar(2000) OUTPUT,
 @ro_n_return_value INT OUTPUT,
 @ListUploadData [dbo].[CLASSM] readonly

WITH EXEC AS CALLER
AS
BEGIN TRY

        SET NOCOUNT ON;
        
        DECLARE
                --@l_n_process_status                   smallint = 0,
    @l_n_return_value       smallint = 0,
    @l_v_name         varchar(30),
    @l_v_email         varchar(50),
    @l_v_gender         varchar(10),
    @l_v_class         varchar(7),
    @l_count_tbl_classM       int

    set @ro_v_err_mesg=''
    set @ro_n_return_value = 0

    if @ro_n_return_value <> 0
    begin
     -- transaction handled at .net
     --rollback transaction process_tran
     return @ro_n_return_value  
    end

    set @l_count_tbl_classM = (select count(*) from @ListUploadData)
       if @l_count_tbl_classM = 0
    begin
     set @ro_n_return_value = 1
     set @ro_v_err_mesg = 'ERROR: No Data Stored'
    end
    else
    begin
     DECLARE ts CURSOR LOCAL FAST_FORWARD FOR

     SELECT [Name],[Email],[Gender],[ClassM] FROM @ListUploadData
  
     OPEN ts;
        FETCH NEXT FROM ts
      INTO 
      @l_v_name,
      @l_v_email,
      @l_v_gender,
      @l_v_class

     WHILE @@FETCH_STATUS = 0
     BEGIN
       INSERT INTO [dbo].[CLASS] 
       (
        Name,Email, Gender, ClassM
       )
       VALUES
       (
        @l_v_name,@l_v_email,@l_v_gender,@l_v_class
       )
      FETCH NEXT FROM ts
      INTO 
      @l_v_name,
      @l_v_email,
      @l_v_gender,
      @l_v_class  
     END
     CLOSE ts;
     DEALLOCATE ts;
    end   
        RETURN @ro_n_return_value         
END TRY
BEGIN CATCH        
        DECLARE @ErrorMessage NVARCHAR(4000)
        DECLARE @ErrorSeverity INT
        DECLARE @ErrorState INT
        DECLARE @ErrorLine INT
        
        SELECT @ErrorMessage = ERROR_MESSAGE(),
                   @ErrorSeverity = ERROR_SEVERITY(),
                   @ErrorState = ERROR_STATE(),
                   @ErrorLine = ERROR_LINE()

        SET @ro_n_return_value = 2        
        SET @ro_v_err_mesg = 'ERROR: SP_InsertUploadExcel: '+ ', at line = ' +  cast (@ErrorLine as varchar)

        PRINT 'SP_InsertUploadExcel : ' +', at line = ' +  cast (@ErrorLine as varchar)

        RETURN @ro_n_return_value
END CATCH





GO
/****** Object:  Table [dbo].[CLASS]    Script Date: 27/04/2020 14:24:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CLASS](
 [Name] [varchar](30) NOT NULL,
 [Email] [varchar](50) NOT NULL,
 [Gender] [varchar](10) NOT NULL,
 [ClassM] [varchar](7) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

Langkah 2

Tambahkan Class Repositories untuk proses insert ke database, 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using UploadExcel.Models.UploadExcel2;

namespace UploadExcel.Models.UploadExcel2
{
    public class UploadExcel2Repo
    {
        string connectionString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;
        SqlConnection connection;
        SqlCommand command;
        SqlDataReader reader;

        public List<UploadExcel2> getAllData(UploadExcel2 data)
        {
            List<UploadExcel2> UploadClass = new List<UploadExcel2>();

            connection = new SqlConnection(connectionString);
            connection.Open();
            var query = "SELECT [Name],[Email],[Gender],[ClassM] FROM [dbo].[CLASS] Where 1=1" +
                        " AND (NULLIF('" + data.Name + "','') IS NULL OR [Name] like '%" + data.Name + "%')" +
                        " AND (NULLIF('" + data.Gender + "','') IS NULL OR [Gender] like '%" + data.Gender + "%')";
            command = new SqlCommand(query, connection);
            reader = command.ExecuteReader();

            while (reader.Read())
            {
                UploadExcel2 classm = new UploadExcel2()
                {
                    Name = Convert.ToString(reader["Name"]), //Convert.ToInt32(reader["id"]),
                    Gender = Convert.ToString(reader["Gender"]),
                    Email = Convert.ToString(reader["Email"]),
                    ClassM = Convert.ToString(reader["ClassM"]),
                };

                UploadClass.Add(classm);
            }

            reader.Close();
            connection.Close();

            return UploadClass;
        }

        public RepoResult Insert(IList<UploadExcel2> listData)
        {
            SqlTransaction trx = null;
            connection = new SqlConnection(connectionString);
            connection.Open();
            trx = connection.BeginTransaction();

            SqlCommand cmd = new SqlCommand("[dbo].[SP_InsertUploadExcel]", connection, trx);
            
            cmd.CommandType = CommandType.StoredProcedure;

            RepoResult repoResult = new RepoResult();

            try
            {
                var outputErrMesg = new System.Data.SqlClient.SqlParameter("@ro_v_err_mesg", System.Data.SqlDbType.VarChar, 2000);
                outputErrMesg.Direction = System.Data.ParameterDirection.Output;

                var retVal = new System.Data.SqlClient.SqlParameter("@ro_n_return_value", System.Data.SqlDbType.Int);
                retVal.Direction = System.Data.ParameterDirection.Output;
                SqlParameter ListUploadData = CreateSqlParameterTblOfClassUploadData("ListUploadData", listData);

                //cmd.Parameters.AddWithValue("@studentNo", data.Name);
                cmd.Parameters.Add(outputErrMesg);
                cmd.Parameters.Add(retVal);
                cmd.Parameters.Add(ListUploadData);
                //cmd.Parameters.AddWithValue("@listUploadData", listUploadData);

                cmd.ExecuteNonQuery();
                trx.Commit();
                connection.Close();

                repoResult.Result = RepoResult.VALUE_SUCCESS;
                if ((int?)retVal.Value != 0)
                {
                    repoResult.Result = RepoResult.VALUE_ERROR;
                    string errMesg = string.Empty;
                    if (outputErrMesg != null && outputErrMesg.Value != null)
                    {
                        errMesg = outputErrMesg.Value.ToString();   
                    }
                    repoResult.ErrMesgs = new string[1];
                    repoResult.ErrMesgs[0] = errMesg;
                }
                
            }
            catch (SqlException e)
            {
                
            }

            return repoResult;
        }

        protected SqlParameter CreateSqlParameterTblOfClassUploadData(string parameterName, IList<UploadExcel2> listData)
        {
            DataTable table = new DataTable();

            table.Columns.Add("Name", type: typeof(string));
            table.Columns.Add("Email", type: typeof(string));
            table.Columns.Add("Gender", type: typeof(string));
            table.Columns.Add("ClassM", type: typeof(string));

            if (listData != null) {
                foreach (UploadExcel2 data in listData) {
                    DataRow row = table.NewRow();
                    row["Name"] = data.Name;//(data.Name != null ? data.Name : (object)DBNull.Value);
                    row["Email"] = data.Email;//(data.Email != null ? data.Email : (object)DBNull.Value);
                    row["Gender"] = data.Gender;//(data.Gender != null ? data.Gender : (object)DBNull.Value);
                    row["ClassM"] = data.ClassM;//(data.ClassM != null ? data.ClassM : (object)DBNull.Value);
                    table.Rows.Add(row);
                }
            }
            var paramStruct = new System.Data.SqlClient.SqlParameter(parameterName,
               System.Data.SqlDbType.Structured);
            paramStruct.SqlDbType = SqlDbType.Structured; // According to marc_s
            paramStruct.SqlValue = table;
            paramStruct.TypeName = "dbo.CLASSM";
            return paramStruct;
        }
        
    }
}

Langkah 3

Jika anda telah membaca artikel sebelumnya dan anda sudah menambahkan model di bawah ini maka anda tidak perlu menambahkannya ke dua kali. Cukup tambahkan parameter ClassM saja.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace UploadExcel.Models.UploadExcel2
{
    public class UploadExcel2
    {
        public string Name { get; set; }
        public string Gender { get; set; }
        public string Email { get; set; }
        public string ClassM { get; set; }

        public IList<UploadExcel2> listData { get; set; }
    }
}

Langkah 4

Tambahkan ActionResult (Search) di Controller (UploadExcel2) untuk AJAX request. Di sini kita akan membuat daftar Siswa yang disimpan pada model UploadExcel2 dan mengembalikan PartialView (_GridView).

using System;
#region Search
        public ActionResult Search(UploadExcel2 data)//IList<UploadExcel2> listData
        {
            AjaxResult ajaxResult = new AjaxResult();
            try
            {
                IList<UploadExcel2> listData = con.getAllData(data);
                ViewData["ListData"] = listData;
            }
            catch (Exception ex)
            {
                return Json("Error : " + ex.Message, JsonRequestBehavior.AllowGet);
            }
            return PartialView("_GridView", data);
        }
        #endregion

Langkah 5

Tambahkan JsonResult UploadFile untuk mengecek file yang diterima dari POST AJAX. Apakah file tersebut memiliki ekstensi file berjenis (.xls | .xlsx) jika memang file tersebut memang merupakan file excel maka akan dilakukan langkah selanjutnya. (GetDataLocalUploadExcel). Berikut kodenya:


#region Upload File
        public JsonResult UploadFile(HttpPostedFileBase file, string SCR_TR)
        {
            AjaxResult ajaxResult = new AjaxResult();
            RepoResult repoResult = null;
            IList<string> errMesgs = new List<string>();
            IList<UploadExcel2> listData = null;
            
            if (!file.FileName.EndsWith(".xls") && !file.FileName.EndsWith(".xlsx"))
            {
                ajaxResult.Result = ajaxResult.ValueError;
                ajaxResult.ErrMesgs = new string[] { string.Format("{0} = {1}", "Warning", "File extension should be .xls or .xlsx") };
            }
            else
            {
                listData = this.GetDataLocalUploadExcel(file, errMesgs, SCR_TR);
              
                if (errMesgs.Count > 0)
                {
                    ajaxResult.Result = AjaxResult.VALUE_ERROR;
                    ajaxResult.ErrMesgs = errMesgs.ToArray();
                }
                else
                {
                    repoResult = con.Insert(listData);
                    CopyPropertiesRepoToAjaxResult(repoResult, ajaxResult);
                    
                    //ajaxResult.Params = new Object[] { listData };
                    //CopyPropertiesRepoToAjaxResult(repoResult, ajaxResult);
                }
            }
            return Json(ajaxResult);
        }

Langkah 6

Tambahkan Method untuk mengenerate data file excel menjadi sebuah List yang akan di simpan pada Model. Berikut kodenya:


private IList<UploadExcel2> GetDataLocalUploadExcel(HttpPostedFileBase file, IList<string> errMesgs, string ClassM)
        {
            IRow row = null;
            ICell cell = null;
            UploadExcel2 data = null;

            string Name            = null;
            string Gender          = null;
            string Email           = null;

            IList<UploadExcel2> listData = new List<UploadExcel2>();

            IWorkbook hssfwb = null;

            if (file.FileName.EndsWith(".xls"))
            {
                hssfwb = new HSSFWorkbook();

                using (System.IO.Stream file2 = file.InputStream)
                {
                    hssfwb = new HSSFWorkbook(file2);
                }
            }
            if (file.FileName.EndsWith(".xlsx"))
            {
                hssfwb = new XSSFWorkbook();
                using (System.IO.Stream file2 = file.InputStream)
                {
                    hssfwb = new XSSFWorkbook(file2);
                }
            }
            if (hssfwb == null)
            {
                throw new ArgumentNullException("Cannot create Workbook object from excel file" + file.FileName);
            }

            int indexRow = DATA_ROW_INDEX_START;
            bool isAllCellEmpty = true;
            bool isBreak = false;

            ISheet sheet = hssfwb.GetSheetAt(0);
         
            int rowCount = 1;

            for (indexRow = DATA_ROW_INDEX_START; indexRow <= sheet.LastRowNum; indexRow++)
            {
                row = sheet.GetRow(indexRow);

                if (row == null)
                {
                    errMesgs.Add(string.Format("There is an empty data at row {0}, Error Mesg : Data between Row cannot be empty",
                                indexRow + 1));
                    break;
                }

                cell = row.GetCell(0);
                if (cell != null)
                {
                    rowCount++;
                }
            }

            //loop row data [start]
            //for (indexRow = DATA_ROW_INDEX_START; indexRow < rowCount; indexRow++)
            for (indexRow = DATA_ROW_INDEX_START; indexRow < rowCount; indexRow++)
            {
                isAllCellEmpty = true;
                isBreak = false;
                row = sheet.GetRow(indexRow);

                Name = null;
                Gender = null;
                Email = null;

                if (row != null)
                {

                    try
                    {
                        cell = row.GetCell(0);
                        if (cell != null)
                        {
                            if (cell.CellType == CellType.Blank)
                            {
                                errMesgs.Add(string.Format("Name No empty {0} is Incorrect Format", indexRow + 1));
                                // do nothing
                            }
                            else if (cell.CellType == CellType.String)
                            {
                                Name = cell.StringCellValue;
                                isAllCellEmpty = false;
                            }
                            else
                            {
                                errMesgs.Add(string.Format("Name No row {0} is Incorrect Format", indexRow + 1));
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        errMesgs.Add(string.Format("Unable to get value of Name at row {0}, Error Mesg : {1}",
                            indexRow + 1, ex.Message));
                        break;
                    }

                   try
                    {
                        cell = row.GetCell(1);
                        if (cell != null)
                        {
                            if (cell.CellType == CellType.Blank)
                            {
                                errMesgs.Add(string.Format("Gender No empty {0} is Incorrect Format", indexRow + 1));
                                // do nothing
                            }
                            else if (cell.CellType == CellType.String)
                            {
                                Gender = cell.StringCellValue;
                                isAllCellEmpty = false;
                            }
                            else
                            {
                                errMesgs.Add(string.Format("Gender No row {0} is Incorrect Format", indexRow + 1));
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        errMesgs.Add(string.Format("Unable to get value of Gender at row {0}, Error Mesg : {1}",
                            indexRow + 1, ex.Message));
                        break;
                    }

                    try
                    {
                        cell = row.GetCell(2);
                        if (cell != null)
                        {
                            if (cell.CellType == CellType.Blank)
                            {
                                errMesgs.Add(string.Format("Email No empty {0} is Incorrect Format", indexRow + 1));
                                // do nothing
                            }
                            else if (cell.CellType == CellType.String)
                            {
                                Email = cell.StringCellValue;
                                isAllCellEmpty = false;
                            }
                            else
                            {
                                errMesgs.Add(string.Format("Email No row {0} is Incorrect Format", indexRow + 1));
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        errMesgs.Add(string.Format("Unable to get value of Email at row {0}, Error Mesg : {1}",
                            indexRow + 1, ex.Message));
                        break;
                    }

                   

                if (isBreak || isAllCellEmpty) break;

                data = new UploadExcel2();
                //data.PRODUCT_ID = PRODUCT_ID;
                data.Name = Name;              
                data.Email = Email;
                data.Gender = Gender;
                data.ClassM = ClassM;
                listData.Add(data);
                }
            }
            return listData;
        }
        #endregion

Langkah 7

Tambahkan PartialView untuk menampilkan Tabel (_GridView.cshtml) di folder Views / UploadExcel2. Dalam kode berikut ini kami memanggil nilai yang disimpan pada model dan akan ditampilkan pada baris <tr><td>.

@using System;
@using UploadExcel.Models.UploadExcel2
@{
    IList<UploadExcel2> listData = (List<UploadExcel2>)ViewData["ListData"];
}
<div class="container">
   <table class="table">
     <thead>
       <tr class="table-primary">
         <th class="text-center grid-checkbox-col" rowspan="2">
             <input class="grid-checkbox" type="checkbox" id="checkall" />
         </th>
         <th rowspan="1">Name</th>
         <th rowspan="1">Gender</th>
         <th rowspan="1">Email</th>
       </tr>
     </thead>
     <tbody>
       @*<tr>
         <td class="text-center grid-checkbox-col">
             <input name="chkRow" type="checkbox" class="grid-checkbox grid-checkbox-body" 
                 data-ClassId=""  value="Male"/>
         </td>
         <td>Agung Panduan</td>
         <td>Male</td>
         <td>admincool@agungpanduan.com</td>
       </tr>*@  
         
        @if (listData == null || listData.Count == 0)
         {
             <tr>
                 <td colspan="13">No Data Found</td>
             </tr>
         }
         else
         {
             foreach(UploadExcel2 data in listData )
             {    
                 <tr>
                    <td class="text-center grid-checkbox-col">
                        <input name="chkRow" type="checkbox" class="grid-checkbox grid-checkbox-body" 
                            data-ClassId=""  value=""/>
                    </td>
                    <td>@data.Name</td>
                    <td>@data.Gender</td>
                    <td>@data.Email</td>
                </tr>
             }
        }
     </tbody>
   </table>
</div>

Langkah 8

Tambahkan PartialView yang berguna halaman area drag and drop file (UploadPopup.cshtml). Dalam Script di bawah ini kami sertakan Disable Drap and Drop agar memaksa pengguna memilih Select Option terlebih dahulu sebelum melakukan Upload.


<div id="uploadPopup" class="modal fade" data-backdrop="static"> 
    <div id="dialogupload" class="modal-dialog modal-md">
        <div class="modal-content" id="motenuopload">
            <div class="modal-header" id="headerupload">
                <h4 class="modal-title" id="popup-title">Upload</h4>
                <button type="button" class="close" data-dismiss="modal">&times;</button>
            </div>

            <div class="modal-body" id="modyupload">
                <div class="row no-gutter">  
                     <div class="col-md-4 text-right">
                         
                     </div>
                     <label for="cmbClass" class="col-md-2 col-form-label text-right">Class:</label>
                     <div class="col-md-6">
                        <select class="form-control" id="cmbClass">
                           <option selected="selected" value="">--Please Choose--</option>
                           <option value="Class 1">Class 1</option>
                           <option value="Class 2">Class 2</option>
                        </select>
                     </div>
                </div>
                <div class="row no-gutter">
                 <div class="col-md-12">
                  <!-- PAGE CONTENT BEGINS -->
                   <form action="UploadExcel2/UploadFile" class="dropzone" id="dropzone"
                                enctype="multipart/form-data" method="post" target="upload_target" style="height:300px;margin-top:20px">
                    <div class="fallback">
                     <input id="fileUploadFile" name="file" type="file" multiple="" />
                    </div>
                   </form>
                  <!-- PAGE CONTENT ENDS -->
                 </div><!-- /.col -->
                </div><!-- /.row --> 
            </div> 
            <div class="modal-footer" id="footerupload"> 
                <!--<button type="submit" style="width:100px" class="btn btn-sm btn-primary" id="btnUpload" onclick="btnUploadPopUp();">Upload</button>--> 
                <button type="button" style="width:100px" class="btn btn-sm btn-danger" id="btnCancel" data-dismiss="modal">Cancel</button>
            </div>
        </div>
    </div>
</div>

<style type="text/css">
    #headerupload{
  background-color:#e6e6ff;
        -webkit-border-top-left-radius: 6px;
        -webkit-border-top-right-radius: 6px;
        -moz-border-radius-topleft: 6px;
        -moz-border-radius-topright: 6px;
        border-top-left-radius: 6px;
        border-top-right-radius: 6px;
 }
 #motenuopload{
  border-radius: 6px;
 }

    #footerupload{
  -webkit-border-bottom-left-radius: 6px;
        -webkit-border-bottom-right-radius: 6px;
        -moz-border-radius-bottomleft: 6px;
        -moz-border-radius-bottomright: 6px;
        border-bottom-left-radius: 6px;
        border-bottom-right-radius: 6px;
 }

    .rowrowupload.no-gutter {
        margin-left: 0px;
        margin-right: 0;
        margin-top: 0;
        margin-bottom:0;
        width:100%;
    }
    .rowrowupload.no-gutter [class*='col-']:not(:first-child),
    .rowrowupload.no-gutter [class*='col-']:not(:last-child) {
        padding-right: 0px;
        padding-left: 0px;
        padding-top: 0px;
        padding-bottom:0px;
       
     }
    .blockDiv
    {
        height:93%;width:96%;opacity:0.6;background-color:#ffffff;
        position:absolute;
        border: 2px solid black;
        text-align:center;
        padding:100px;
        font-size:large;
        
    }
    .Successupload
    {
        height:100%;width:96%;opacity:1.0;background-color:#ffffff;
        position:absolute;
        border: 2px solid black;
        text-align:center;
        padding:100px;
        font-size:50px;
        color:red;
        background: -webkit-linear-gradient(red,#333);
        -webkit-background-clip:text;
        -webkit-text-fill-color: transparent;
    }
</style>
<!-- inline scripts related to this page -->
<script type="text/javascript">
    var str = "";
    $(document).ready(function () {

        if (str == "") {
            $("#dropzone").prepend($('<div id="blockDiv" class="blockDiv" ><span style="Color:Red;vertical-align: -80px;">Please Choose Class</span></div>'));
        }
    });

    $("#cmbClass").on('change', function () {
        // For multiple choice
        str = $(this).val();

        if (str != "") {
            $(".blockDiv").hide();
        } else {
            $(".blockDiv").show();
        }

    });
    //  var message = "Upload File to FTP Server Error";
    jQuery(function ($) {

        try {

            Dropzone.autoDiscover = false;
            var myDropzone = new Dropzone("#dropzone", {
                paramName: "file", // The name that will be used to transfer the file
                // maxFilesize: 0.5, // MB
                //acceptedFiles: ".csv",
                //accept:function(file, done){
                //    if (str = "") {
                //        
                //    }
                //},
                addRemoveLinks: false,
                dictDefaultMessage:
  '<div class="text-center"><br /> <br /> <br /> <br /> <span style="font-size: 150% !important;"><i class="fa fa-caret-right"></i> Drop files</span> to upload 
  <span style="font-size: 80% !important;">(or click)</span> <br /> 
  <i class="fa fa-upload" aria-hidden="true" style="font-size:50px;color:#4080bf"></i></div>'
 ,
                dictResponseError: 'Error while uploading file!',
                success: function (file, returnResult) {
                    //console.log(returnResult);
                    //alert(returnResult.Result);
                    if (returnResult.Result == "ERROR") {

                        var errorMessage = "Error has occured during Upload checksheet";

                        for (i = 0; i < returnResult.ErrMesgs.length; i++) {
                            errorMessage += "</br>" + returnResult.ErrMesgs[i] + "</br>"
                        }
                        
                        //alert(errorMessage);
                        $.bootstrapGrowl(errorMessage, {
                            type: 'danger',
                            allow_dismiss: true,
                            align: 'center',
                            delay: 10000
                        });
                        //showErrorMesgGrowl(errorMessage);
                        file.previewElement.classList.add("dz-error");
                    }
                    else {
                        if (file.previewElement) {
                            $("#dropzone").prepend($('<div id="Successupload" class="Successupload">SUCCESS UPLOAD</div>'));
                            var h = 0;
                            var k = 0
                            setInterval(function () {
                                h = h + 1;
                                if (h == 3) {
                                    $(".Successupload").hide();
                                    $("#cmbClass").prop('selected', false).find('option:first').prop('selected', true);
                                    $(".blockDiv").show();
                                }

                            }, 1000)
                        }
                    }

                },
                complete: function () {

                },
                error: function (file, errormessage) {
                    if (file.previewElement) {
                        showErrorMesgGrowl(errormessage);
                        file.previewElement.classList.add("dz-error");
                    }
                },
                //change the previewTemplate to use Bootstrap progress bars
                previewTemplate: "<div class="dz-preview dz-file-preview">n  <div class="dz-details">n   " +
                 " <div class="dz-filename"><span data-dz-name></span></div>n  " +
                 " <div class="dz-size" data-dz-size></div>n   " +
                 " <img data-dz-thumbnail />n  " +
                 " </div>n <div class="progress progress-small progress-striped active"> " +
                 " <div class="progress-bar progress-bar-success" data-dz-uploadprogress></div></div>n " +
                 " <div class="dz-success-mark"><span></span></div>n  " +
                //" <div class="dz-error-mark"><span></span></div>n  " +
                 " <div class="dz-error-message"> <span data-dz-errormessage></span></div>n</div>"
            });

            myDropzone.on('sending', function (file, xhr, formData) {
                formData.append('SCR_TR', str);
                //                                formData.append('modelCd', gUploadModelCd);
                //                                formData.append('prodMonth', gUploadProdMonth);
            });

            myDropzone.on("complete", function (file) {
                myDropzone.removeFile(file);
            });

            $(document).one('ajaxloadstart.page', function (e) {
                try {
                    myDropzone.destroy();
                } catch (e) { }
            });

        } catch (e) {
            alert(e);
        }

    });

    function onUploadFileSuccess() {

    }

   
</script>

File yang sudah di upload dan di generate menjadi List Data Model akan di panggil kembali ke AJAX pada Langkah 8 dan POST ke Controller ActionResult Search untuk ditampilkan pada _GridView.cshtml.

Langkah 9

Tambahkan View Page(Index.cshtml) di folder Views / UploadExcel2


@using System;
@using UploadExcel.Models.UploadExcel2;
@Html.Partial("_UploadPopup")
@{
    List<UploadExcel2> listData = (List<UploadExcel2>)ViewData["ListData"]; 
}

<style>
    .text-large {
      text-transform: uppercase;
     background: linear-gradient(to right, #30CFD0 0%, #330867 100%);
     -webkit-background-clip: text;
     -webkit-text-fill-color: transparent;
     font-size: 500%;
      font-family: 'Poppins', sans-serif;
    }
    .row.no-gutter {
        margin-left: 0px;
        margin-right: 0;
        margin-top: 0;
        margin-bottom:0;
        width:100%;
    }
    .row.no-gutter [class*='col-']:not(:first-child),
    .row.no-gutter [class*='col-']:not(:last-child) {
        padding-right: 10px;
        padding-left: 10px;
        padding-top: 10px;
        padding-bottom:10px;
     }
    input[name='need']:disabled {
        background: red !important;
        color:black !important;
    }
   
</style>

<div class="container">
     <div class="row no-gutter">
       <div class="col-md-6" style="padding-top:13px;">
           <div class="row no-gutter"><i class="fa fa-user col-md-3">
                <label for="inputEmail" class="col-md-4 col-form-label text-success" >Name</label></i>
                <div class="col-md-8">
                    <input type="text" class="form-control" id="inputName">
                </div>
           </div>
           <div class="row no-gutter"><i class="fa fa-venus-mars col-md-3">
              <label for="cmbGender" class="col-md-4 col-form-label text-success">Gender</label></i>
              <div class="col-md-8">
                 <select class="form-control"  id="cmbGender">
                    <option selected="selected" value="">--Please Choose--</option>
                    <option value="1">Male</option>
                    <option value="2">Female</option>
                 </select>
              </div>
           </div>
       </div>
       <div class="col-md-6">
          <p class="float-right text-large"><span id="furnace_no">CLASS 1</span></p>
       </div>
   </div>
   <div class="row no-gutter" style="margin-bottom:5px">
       <div class="col-md-12">
           <div class="text-right">
               <button onclick="btnSearch_onClick()" type="button" class="btn btn-sm btn-success">Search</button>
           </div>
       </div>
   </div>
   <div class="row" >
        <div class="bg-primary text-white text-center col-md-12">
   <h4>Type 1</h4>
  </div>
    </div>
   <div class="row no-gutter">
      <div id="divGrid" style="width:100%">
         @Html.Partial("_GridView")
      </div>
   </div>
   <div class="row no-gutter">
       <div class="col-md-12">
           <div class="text-right">
               <button onclick="btnUploadPopUp2()" type="button" class="btn btn-sm btn-success">Upload</button>
               <button id="btnSave" type="button" class="btn btn-sm btn-success">Save</button>
               <button id="btnSkip" type="button" class="btn btn-sm btn-primary">Skip</button>
           </div>
       </div>
   </div>
   <br />
</div>
@section footer{
   <div class="bg-secondary" style="height:100%">
       <div class="footer">
        <div class="container">
            <div class="row">
                <div class="col-md-12 col-12 text-white">
                    <div class="row p-3">
                        <div class="col-md-6 col-12">
                            <p class="m-0">All Right Reserved 2020 - <a href="http://www.agungpanduan.com" target="_blank">agungpanduan.com</a></p>
                        </div>
                        <div class="col-md-6 col-12 footer-icon text-right">
                            <i class="fa fa-twitter"></i>
                            <i class="fa fa-instagram"></i>
                            <i class="fa fa-pinterest"></i>
                            <i class="fa fa-linkedin"></i>
                            <i class="fa fa-youtube"></i>
                            <i class="fa fa-vimeo"></i>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
   </div>
}
 
<script type="text/javascript">
    Dropzone.autoDiscover = false;
    $(document).ready(function () {
        //Dropzone.autoDiscover = true;
        //onSearchCriteria();
        $("#checkall").click(function () {
            $(".grid-checkbox").prop("checked", $("#checkall").is(":checked"));
        });

        $(".grid-checkbox").click(function () {
            $("#checkall").prop("checked", $('.grid-checkbox:not(#checkall)').not(':checked').length == 0);
        });
    });

    function btnUploadPopUp2() {
        
        $("#uploadPopup").modal("show");
        //$("#dropzone").prepend($('<div id="blockDiv" class="blockDiv" ><span style="Color:Red;vertical-align: -80px;">Please Choose Gender</span></div>'));
    }

    function onSearchCriteria(obj) {

        //console.log(obj.listdata);
        var params = new Object();
        var data = new Object();
        params.ClassM = $("#cmbClass").val();
        params.data = obj;
        console.log(obj.listdata);
        $.ajax({
            type: "POST",
            url: "@Url.Content("~/UploadExcel2/Search")",
            contentType: "application/json",
            dataType: 'html',
            data: JSON.stringify(params),
            //async: true,
            success: function (data) {
                $("#divGrid").html(data);
                $("#furnace_no").html($("#cmbClass").val());
            },
            error: function (data) {
                console.log("ERORRRRRRR");
                //console.log(data.responseText);
            }
        });
    }

    function btnSearch_onClick() {
        var page = 1;
        popUpProgressShow();
        $.ajax({
            type: "POST",
            url: "@Url.Content("~/UploadExcel2/Search")",
            data: {
                Name: $("#inputName").val(),
                Gender: $("#cmbGender").val()
            },
            success: function (data) {
                $("#divGrid").html(data);
                popUpProgressHide();
            },
            error: function (data) {
                popUpProgressHide();
            }
        });
    }
</script>

Langkah 10

Tambahkan Commons Model untuk menyimpan hasil JSON AJAX Result, yaitu AjaxResult.cs, BaseResult.cs, dan RepoResult.cs. Codingnya dapat di download pada artikel sebelumnya.

Share This :

0 Comments