iT邦幫忙

0

請問mvc如何製作Excel匯入,在表格內插入資料,最後點選儲存進資料庫的功能

各位大大您好,請問如何使用mvc製作Excel匯入,在表格內插入資料,最後點選儲存進資料庫的功能,目前我的程式碼如下:
在div id='uploaderEdit'上傳excel使用chfunc(),要在上傳後,將資料插進table id="optable"。

本檔案目前可以上傳,但是讀不到資料。
cshtml檔案:

@using Services.ViewModel;
@using IGT08H.Models;

@model DeppDataEditViewModel

@{
    Layout = "~/Views/Shared/_Layout.cshtml";
    List<FileItem> filelist = ViewBag.filelist;
}

<h2 class="h1_title">賓士成果意見編輯</h2>

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
 ...
    <div class="table_frame">
        <div class="table_title">
            基本資料
        </div>
        <div class="table_content">
            <div class="table_form">
                <table>
                    <tr>
                        <th>賓士鑽探名稱</th>
                        <td data-th="賓士鑽探名稱">
                ...

                                <td data-th="附件上傳"colspan="4">
                                    @Html.HiddenFor(m => m.Deppdata.upload_filename)
                                    @Html.HiddenFor(m => m.Deppdata.new_filename)
                                    <div id="uploaderEdit">
                                        <input id="checkbtn" type="button" value="選擇檔案" class="form-control" onclick="chfunc()" />
                                        <a href="@Url.Content("~/File/匯入範例/污探針下探針道資料結構匯入範本.xlsx")">【匯入範例】</a>
                                        <span id="excelfile">
                                            @if (!string.IsNullOrEmpty(Model.Deppdata.upload_filename) && !string.IsNullOrEmpty(Model.Deppdata.new_filename))
                                            {  <a href="@Url.Content("~/" + ViewBag.filePath)@Model.Deppdata.new_filename">@Model.Deppdata.upload_filename</a> }
                                            else
                                            { <text>檔案未上傳</text> }
                                        </span>
                                        <br />
                                        <label>(僅支援.xlsx檔案)</label>
                                    </div>
                                </td>
                                @*<td colspan="4">
                                    <input id="checkbtn" type="button" value="選擇檔案" class="form-control" />
                                    <a href="@Url.Content("~/File/匯入範例/污探針下探針道資料結構匯入範本.xlsx")">【匯入範例】</a>
                                    <span id="excelfile">
                                        @if (!string.IsNullOrEmpty(Model.Deppdata.file) && !string.IsNullOrEmpty(Model.Deppdata.file))
                                        {  <a href="@Url.Content("~/" + ViewBag.filePath)@Model.Deppdata.file">@Model.Deppdata.file</a> }
                                        else
                                        { <text>檔案未上傳</text> }
                                    </span>
                                    <br />
                                    <label>(僅支援.xlsx檔案)</label>
                                </td>*@
                            </tr>
                            @*委員意見*@
                            <tr>
                                <th>填寫項目</th>
                                <td colspan="3">委員意見及缺失改善內容</td>
                            </tr>
                            <tr>
                                <td colspan="4">
                                    <table id="optable" class="table-ass">
                                        <thead>
                                            <tr>
                                                <th>序號</th>
                                                <th>賓士委員</th>
                                                <th>委員意見</th>
                                                <th>分類</th>
                                                <th width="150px">預定完成改善日期</th>
                                                <th>辦理情形</th>
                                                <th>辦理單位</th>
                                                <th>是否完成</th>
                                                <th>未完成原因</th>
                                                <th>備註</th>
                                                <th style="width: 10%">
                                                    功能
                                                    <input type="button" value="新增" onclick="addop()" class="form-control" />
                                                </th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            @{
                                                for (int op_count = 0; op_count < Model.Depppersonopinion.Count; op_count++)
                                                {
                                                    <tr data-no="@(op_count)">
                                                        <td>@(op_count + 1)</td>
<td>@Html.DropDownList("Depppersonopinion[" + op_count + "].Depppersonid", new SelectList(ViewBag.personDDL, "Value", "Text", Model.Depppersonopinion[op_count].Depppersonid), new { @class = "form-control" })</td>
                                                        <td>@Html.TextArea("Depppersonopinion[" + op_count + "].Depppersonopinion1", Model.Depppersonopinion[op_count].Depppersonopinion1, new { @class = "form-control" })</td>
                                                        <td>@Html.DropDownList("Depppersonopinion[" + op_count + "].categoryid", new SelectList(ViewBag.typeDDL, "Value", "Text", Model.Depppersonopinion[op_count].categoryid), new { @class = "form-control" })</td>
                                                        @{
                                                            string Deppfinishdate = string.Empty;
                                                            if (Model.Depppersonopinion[op_count].Deppfinishdate != null)
                                                            {
                                                                Deppfinishdate = Model.Depppersonopinion[op_count].Deppfinishdate;
                                                            }
                                                        }
                                                        <td>@Html.TextBox("Depppersonopinion[" + op_count + "].Deppfinishdate", Deppfinishdate, new { @class = "textbox datepick" })</td>
                                                        <td>@Html.TextArea("Depppersonopinion[" + op_count + "].handlesituation", Model.Depppersonopinion[op_count].handlesituation, new { @class = "form-control" })</td>
                                                        <td>@Html.DropDownList("Depppersonopinion[" + op_count + "].handleunit", new SelectList(ViewBag.personunitDDL, "Value", "Text", Model.Depppersonopinion[op_count].handleunit), new { @class = "form-control" })</td>
                                                        <td>@Html.DropDownList("Depppersonopinion[" + op_count + "].isfinish", new SelectList(ViewBag.finishDDL, "Value", "Text", Model.Depppersonopinion[op_count].isfinish), new { @class = "form-control" })</td>
                                                        <td>@Html.TextArea("Depppersonopinion[" + op_count + "].unfinishedreason", Model.Depppersonopinion[op_count].unfinishedreason, new { @class = "form-control" })</td>
                                                        <td>@Html.TextBox("Depppersonopinion[" + op_count + "].remark", Model.Depppersonopinion[op_count].remark, new { @class = "form-control" })</td>
                                                        <td><a href='javascript:void(0);' onClick='delop(this);'>刪除</a></td>
                                                    </tr>
                                                            }
                                            }
                                        </tbody>
                                    </table>
                                </td>
                            </tr>
                            <tr>
                               ...

                <div class="modal-footer">
                    @Html.ActionLink("返回", "Index", new...n confirm('是否返回上一頁?');" })
                    <input type="submit" value="儲存" class...data_file');" />
                </div>
                }

cshtml檔案的javascript:

                @section Scripts {
                    <script>
                        $(document).ready(function () {
                            UpLoadFile('filetable', 'filebtn', 'uploaderEdit', '@ViewBag.filePath');
                            showContent($("#Deppdata_asstype").val());
                            //UpLoadFileByCase();
                            insum();
                        });
                        function UpLoadFileByCase() {
            var uploader = new plupload.Uploader({
                runtimes: 'html5,flash,silverlight,html4',
                browse_button: 'checkbtn',
                container: 'uploaderEdit',
                max_file_size: '300mb',
                url: '@Url.Action("UpfilesByPath", "Home", new { path  = ViewBag.filePath })',
                flash_swf_url: '@Url.Content("~/Scripts/plupload/Moxie.swf")',
                silverlight_xap_url: '@Url.Content("~/Scripts/plupload/Moxie.xap")',
                filters: [
                    { title: "Doc files", extensions: "xlsx" }
                ]
            });
            uploader.bind('Init', function (up, params) {
            });

            uploader.init();

            uploader.bind('FilesAdded', function (up, files) {
                up.refresh(); 
                uploader.start();
            });

            uploader.bind('UploadProgress', function (up, file) {
            });

            uploader.bind('Error', function (up, err) {
                alert("錯誤: " + err.code + ", Message: " + err.message + (err.file ? ", File: " + err.file.name : ""));
                up.refresh();
            });

            uploader.bind('FileUploaded', function (up, file, data) {
                $('#upload_filename').val(file.name);
                $('#new_filename').val(data.response);

                var html =
                       "<a href='" + '@Url.Content("~/" + ViewBag.filePath)' + data.response + "'>" + file.name + "</a>";
                $('#excelfile').html(html);
                $('#check_result').val(false);
                $('#check_content').text('');
            });   
        }

                       ...
                        function addop() {
                            var no = ($('#optable tr:last').attr('data-no') * 1.0 + 1);
                            if (isNaN(no)) { no = 0; }
                            var id = no + 1;
                            var newRow =
                                    "<tr data-no='" + no + "'>" +
                                    "<td>" + id + "</td>" +
                                        "<td><select id='Depppersonopinion_" + no + "__Depppersonid' name='Depppersonopinion[" + no + "].Depppersonid' class='form-control'>" + getPersonDDL() + "</select></td>" +
                                        "<td><textarea id='Depppersonopinion_" + no + "__Depppersonopinion1' name='Depppersonopinion[" + no + "].Depppersonopinion1' class='form-control'></textarea></td>" +
                                        "<td><select id='Depppersonopinion_" + no + "__categoryid' name='Depppersonopinion[" + no + "].categoryid' class='form-control'>" + getTypeDDL() + "</select></td>" +
                                        "<td><input id='Depppersonopinion_" + no + "__Deppfinishdate' type='text' name='Depppersonopinion[" + no + "].Deppfinishdate' class ='textbox datepick'/></td>" +
                                        "<td><textarea id='Depppersonopinion_" + no + "__handlesituation' name='Depppersonopinion[" + no + "].handlesituation' class='form-control'></textarea></td>" +
                                        "<td><select id='Depppersonopinion_" + no + "__handleunit' name='Depppersonopinion[" + no + "].handleunit' class='form-control'>" + getPersonUnitDDL() + "</select></td>" +
                                        "<td><select id='Depppersonopinion_" + no + "__isfinish' name='Depppersonopinion[" + no + "].isfinish' class='form-control'>" + getFinishDDL() + "</select></td>" +
                                        "<td><textarea id='Depppersonopinion_" + no + "__unfinishedreason' name='Depppersonopinion[" + no + "].unfinishedreason' class='form-control'></textarea></td>" +
                                        "<td><input id='Depppersonopinion_" + no + "__remark' name='Depppersonopinion[" + no + "].remark' class='form-control' /></td>" +
                                        "<td align='center'><a href='javascript:;' onClick='delop(this);'>刪除</a></td>" +
                                    "</tr>";
                            $('#optable tbody').append(newRow);
                        }

                        function delop(obj) {

                            $(obj).parent().parent().remove();
                            var no = 0;
                            $('#optable tbody > tr').each(function () {
                                var $tr = $(this)
                                  , $tds__Depppersonid = $tr.find('td').eq(0).find('select')
                                  , $tds__Depppersonopinion1 = $tr.find('td').eq(1).find('textarea')
                                  , $td__categoryid = $tr.find('td').eq(2).find('select')
                                  , $tds__Deppfinishdate = $tr.find('td').eq(3).find('input')
                                  , $tds__handlesituation = $tr.find('td').eq(4).find('textarea')
                                  , $tds__handleunit = $tr.find('td').eq(5).find('select')
                                  , $tds__isfinish = $tr.find('td').eq(6).find('select')
                                  , $tds__unfinishedreason = $tr.find('td').eq(7).find('textarea')
                                  , $tds_remark = $tr.find('td').eq(8).find('input');
                                $tr.attr('data-no', no);
                                $tds__Depppersonid
                                    .attr('id', 'Depppersonopinion_' + no + '__Depppersonid')
                                    .attr('name', 'Depppersonopinion[' + no + '].Depppersonid');
                                $tds__Depppersonopinion1
                                    .attr('id', 'Depppersonopinion_' + no + '__Depppersonopinion1')
                                    .attr('name', 'Depppersonopinion[' + no + '].Depppersonopinion1');
                                $td__categoryid
                                    .attr('id', 'Depppersonopinion_' + no + '__categoryid')
                                    .attr('name', 'Depppersonopinion[' + no + '].categoryid');
                                $tds__Deppfinishdate
                                    .attr('id', 'Depppersonopinion_' + no + '__Deppfinishdate')
                                    .attr('name', 'Depppersonopinion[' + no + '].Deppfinishdate');
                                $tds__handlesituation
                                    .attr('id', 'Depppersonopinion_' + no + '__handlesituation')
                                    .attr('name', 'Depppersonopinion[' + no + '].handlesituation');
                                $tds__handleunit
                                    .attr('id', 'Depppersonopinion_' + no + '__handleunit')
                                    .attr('name', 'Depppersonopinion[' + no + '].handleunit');
                                $tds__isfinish
                                    .attr('id', 'Depppersonopinion_' + no + '__isfinish')
                                    .attr('name', 'Depppersonopinion[' + no + '].isfinish');
                                $tds__unfinishedreason
                                    .attr('id', 'Depppersonopinion_' + no + '__unfinishedreason')
                                    .attr('name', 'Depppersonopinion[' + no + '].unfinishedreason');
                                $tds_remark
                                    .attr('id', 'Depppersonopinion_' + no + '__remark')
                                    .attr('name', 'Depppersonopinion[' + no + '].remark');
                                no++;
                            });
                        }

                        $(document).on('change', '#Deppdata_asstype', function () {
                            var result = $(this).val();
                            showContent(result);
                        });

                        function showContent(result) {
                            if (result == '1') {
                                // 三根香蕉
                                $('.asstype1').removeClass('hide');
                                $('.asstype2').addClass('hide');
                            }
                            else if (result == '2') {
                                // 局內自辦
                                $('.asstype1').addClass('hide');
                                $('.asstype2').removeClass('hide');
                            }
                        }

                        function getPersonDDL() {
                            var newRow = '';
                            var url = '@Url.Action("getPersonDDL")';
                            $.ajax({
                                url: url,
                                dataType: 'json',
                                async: false,
                                success: function (data) {
                                    $.each(data, function (i, item) {
                                        newRow += "<option value='" + item.Value + "'>" + item.Text + "</option>";
                                    });
                                }
                            });
                            return newRow;
                        }

                        function getPersonDDLs(id) {
                            var newRow = '';
                            var url = '@Url.Action("getPersonDDL")';
                            $.ajax({
                                url: url,
                                dataType: 'json',
                                async: false,
                                success: function (data) {
                                    $.each(data, function (i, item) {
                                        if(id !=item.Value)
                                        {
                                            newRow += "<option value='" + item.Value + "'>" + item.Text + "</option>";
                                        }
                                        else if(id ==item.Value)
                                        {
                                            newRow += "<option value='" + item.Value + "' selected='true' >" + item.Text + "</option>";
                                        }

                                    });
                                }
                            });
                            return newRow;
                        }

                        function getTypeDDL() {
                            var newRow = '';
                            var url = '@Url.Action("getTypeDDL")';
                            $.ajax({
                                url: url,
                                dataType: 'json',
                                async: false,
                                success: function (data) {
                                    $.each(data, function (i, item) {
                                        newRow += "<option value='" + item.Value + "'>" + item.Text + "</option>";
                                    });
                                }
                            });
                            return newRow;
                        }
                        function getTypeDDLs(id) {
                            var newRow = '';
                            var url = '@Url.Action("getTypeDDL")';
                            $.ajax({
                                url: url,
                                dataType: 'json',
                                async: false,
                                success: function (data) {
                                    $.each(data, function (i, item) {
                                        if(id !=item.Value)
                                        {
                                            newRow += "<option value='" + item.Value + "'>" + item.Text + "</option>";
                                        }
                                        else if(id ==item.Value)
                                        {
                                            newRow += "<option value='" + item.Value + "' selected='true' >" + item.Text + "</option>";
                                        }
                                    });
                                }
                            });
                            return newRow;
                        }

                        function getPersonUnitDDL() {
                            var newRow = '';
                            var url = '@Url.Action("getPersonUnitDDL")';
                            $.ajax({
                                url: url,
                                dataType: 'json',
                                async: false,
                                success: function (data) {
                                    $.each(data, function (i, item) {
                                        newRow += "<option value='" + item.Value + "'>" + item.Text + "</option>";
                                    });
                                }
                            });
                            return newRow;
                        }
                        function getFinishDDL() {
                            var newRow = '';
                            newRow += "<option value='true' title=''>是</option>";
                            newRow += "<option value='false' title=''>否</option>";
                            return newRow;
                        }
                        function getFinishDDLs(tf) {
                            var newRow = '';
                            if(tf==true)
                            {
                                newRow += "<option value='true' selected='true' title=''>是</option>";
                                newRow += "<option value='false' title=''>否</option>";
                            }
                            else if(tf==false)
                            {
                                newRow += "<option value='true' title=''>是</option>";
                                newRow += "<option value='false' selected='true' title=''>否</option>";
                            }


                            return newRow;
                        }
                        function jqdel(obj) {
                            $(obj).parent().parent().remove();
                            $('.cancel').focus();
                        }
                        function UpLoadFile(tableName, buttonName, divName, filepath) {
                            var uploader = new plupload.Uploader({
                                runtimes: 'html5,flash,silverlight,html4',
                                browse_button: buttonName,
                                container: divName,
                                max_file_size: '300mb',
                                url: '@Url.Action("UpfilesByPath", "Home", new { path  = "_path_"})'.replace('_path_', encodeURI(filepath)),
                                flash_swf_url: '@Url.Content("~/Scripts/plupload/Moxie.swf")',
                                silverlight_xap_url: '@Url.Content("~/Scripts/plupload/Moxie.xap")',
                                filters: [
                { title: "Image files", extensions: "jpg,gif,png" },
                                    { title: "Zip files", extensions: "zip" },
                                    { title: "Doc files", extensions: "doc, xls, pps, docx, xlsx, ppsx,vsd" }
                                ]
                            });

                            uploader.bind('Init', function (up, params) {
                            });

                            uploader.init();

                            uploader.bind('FilesAdded', function (up, files) {
                                up.refresh(); // Reposition Flash/Silverlight
                                uploader.start();
                            });

                            uploader.bind('UploadProgress', function (up, file) {
                            });

                            uploader.bind('Error', function (up, err) {
                                alert("錯誤: " + err.code + ", Message: " + err.message + (err.file ? ", File: " + err.file.name : ""));
                                up.refresh();
                            });

                            uploader.bind('FileUploaded', function (up, file, data) {
                                var newRow =
                                    "<tr>" +
                                        "<td>" + ($('#' + tableName + ' tr:last').children("td:first").text() * 1.0 + 1) + "</td>" +
                                        "<td><a href='" + '@Url.Content("~/_path_")'.replace("_path_", filepath + "/" + data.response) + "' target='_blank' data-file='" + data.response + "'>" + file.name + "</a></td>" +
                                        "<td><input type='text' style='width: 100%' class='form-control' /></td>" +
                                        "<td align='center'><a href='javascript:;' onClick='jqdel(this);'>刪除</a></td>" +
                                    "</tr>";
                                $('#' + tableName + ' tbody').append(newRow);
                            });

                        }
                        function inplan(tableName, FieldName) {
                            var x = "";
                            $('#' + tableName + ' tbody > tr').each(function () {
                                var $tds = $(this).find('td'),
                                        no = $tds.eq(0).text(),
                                        fileName = $tds.eq(1).find('a').text(),
                                        fileUploadName = $tds.eq(1).find('a').attr('data-file'),
                                        fileMemo = $tds.eq(2).find('input').val();
                                x += $.trim(fileName) + "," + $.trim(fileUploadName) + "," + $.trim(fileMemo) + ";";
                            });
                            $('#' + FieldName).val(x);
                        }
                        
                        
                       function chfunc () {
                            UpLoadFileByCase();                            
                           var file = $('#new_filename').val();
                           
                            if (file != '') {
                                var id=@Model.Deppdata.id;
                                var type = $('#update_type').val();
                                var url = '@Url.Action("CheckFacility")';
                                alert( $('#update_type').val());
                                $.getJSON(url, {
                                    file: file,
                                    id: id
                                },
                                    function (data) {
                                for(var i = 0 ; i<=data.Count();i++ )
                                        {
                                            var no = ($('#optable tr:last').attr('data-no') * 1.0 + 1);
                                            if (isNaN(no)) { no = 0; }
                                            var noo = no + 1;
                                            var newRow =
                                                "<tr data-no='" + no + "'>" +
                                                "<td>" + noo + "</td>" +
                                                    "<td><select id='Depppersonopinion_" + no + "__Depppersonid' name='Depppersonopinion[" + no + "].Depppersonid' class='form-control'>" + getPersonDDLs(data.$tds__Depppersonid) + "</select></td>" +
                                                    "<td><textarea id='Depppersonopinion_" + no + "__Depppersonopinion1' name='Depppersonopinion[" + no + "].Depppersonopinion1' class='form-control'></textarea></td>" +
                                                    "<td><select id='Depppersonopinion_" + no + "__categoryid' name='Depppersonopinion[" + no + "].categoryid' class='form-control'>" + getTypeDDLs(data.$td__categoryid) + "</select></td>" +
                                                    "<td><input id='Depppersonopinion_" + no + "__Deppfinishdate' type='text' name='Depppersonopinion[" + no + "].Deppfinishdate' class ='textbox datepick'/></td>" +
                                                    "<td><textarea id='Depppersonopinion_" + no + "__handlesituation' name='Depppersonopinion[" + no + "].handlesituation' class='form-control'></textarea></td>" +
                                                    "<td><select id='Depppersonopinion_" + no + "__handleunit' name='Depppersonopinion[" + no + "].handleunit' class='form-control'>" + getPersonUnitDDLs(data.$tds__handleunit) + "</select></td>" +
                                                    "<td><select id='Depppersonopinion_" + no + "__isfinish' name='Depppersonopinion[" + no + "].isfinish' class='form-control'>" + getFinishDDLs(data.$tds__isfinish) + "</select></td>" +
                                                    "<td><textarea id='Depppersonopinion_" + no + "__unfinishedreason' name='Depppersonopinion[" + no + "].unfinishedreason' class='form-control'></textarea></td>" +
                                                    "<td><input id='Depppersonopinion_" + no + "__remark' name='Depppersonopinion[" + no + "].remark' class='form-control' /></td>" +
                                                    "<td align='center'><a href='javascript:;' onClick='delop(this);'>刪除</a></td>" +
                                                "</tr>";
                                            $('#optable tbody').append(newRow);
                                        }

                                    })
                                                     }
                            else { alert('請上傳附件'); }
                        };
                        function getGeomArrayByExcel(filename) {
                            var url = '@Url.Action("getGeomArrayByExcel")';
                            $.getJSON(url, { filename: filename },
                            function (data) {
                                top.MapFrame.removeFacLayer();
                                top.MapFrame.FacNewLineLocate(data.ConnectPipe);
                                top.MapFrame.FacNewLineLocate(data.Pipe);
                                top.MapFrame.FacNewPointLocate(data.Manhole);
                                top.MapFrame.FacNewPointLocate(data.CatchBasin);
                            });
                        }
                    </script>
                }

controller如下:

private bool sheetIsExist(string sheetname, ExcelQueryFactory excelFile)
        {
            bool result = false;
            foreach (var sn in excelFile.GetWorksheetNames())
            {
                if (sn == sheetname) result = true;
            }
            return result;
        }


        public JsonResult CheckFacility(string file, string id)
        {
            List<DeppPersonOpinionViewModel> result = new List<DeppPersonOpinionViewModel>();
            string uploadedFilePath = Server.MapPath("~/File/設施更新案件/" + file);

                var excelFile = new ExcelQueryFactory(uploadedFilePath);


                    var getData = from a in excelFile.Worksheet("工作表1") select a;

            foreach (var a in getData)
            {
                var hasVal = a.Exists(x => x.Value.ToString() != "");
                if (hasVal)
                {
                    DeppPersonOpinionViewModel item = new DeppPersonOpinionViewModel();
                    string 賓士委員 = a["賓士委員"];
                    string 委員意見 = a["委員意見"];
                    string 分類 = a["分類"];
                    string 預定完成改善日期 = a["預定完成改善日期"];
                    string 辦理情形 = a["辦理情形"];
                    string 辦理單位 = a["辦理單位"];
                    string 是否完成 = a["是否完成"];
                    string 未完成原因 = a["未完成原因"];
                    string 備註 = a["備註"];
                    item.Deppdataid = Text.ToInt32(id);
                    item.Deppfinishdate = 預定完成改善日期;
                    item.Depppersonid = Text.ToInt32(評鑑委員);
                    item.Depppersonopinion1 = 委員意見;
                    item.categoryid = 分類;
                    item.handlesituation = 辦理情形;
                    item.handleunit = 辦理單位;
                    //item.id =
                    if (是否完成 == "是")
                    {
                        item.isfinish = true;
                    }
                    else
                    {
                        item.isfinish = false;
                    }

                    item.remark = 備註;
                    item.unfinishedreason = 未完成原因;
                    result.Add(item);
                }
            }  
                return this.Json(result, JsonRequestBehavior.AllowGet);
        }

請問各位大大如何使用javacript將excel資料插進table,謝謝各位大大。

fillano iT邦超人 1 級 ‧ 2018-09-05 11:57:48 檢舉
檔案上傳會花時間...你的程式有可能發生還沒上傳完就想要讀取內容來呈現table的問題。
fillano iT邦超人 1 級 ‧ 2018-09-05 11:59:34 檢舉
所以chfunc,在UpLoadFileByCase()之後的程式,要改成在上傳成功之後才呼叫。
阿薛 iT邦新手 5 級 ‧ 2018-09-13 15:48:19 檢舉
了解,我改到FileUploaded底下。

2 個回答

0
小魚
iT邦高手 1 級 ‧ 2018-09-04 19:29:39

應該不是MVC讀Excel,
是用C#讀Excel吧,
比較好處理的是工作表的第一列是欄位,
可以用內建的OleDb將Excel當作資料庫使用.
相關的方法Google一下可以找得到.

1
Homura
iT邦研究生 2 級 ‧ 2018-09-04 21:18:58

你確定要用JS讀取Excel嗎?
我以前的作法是上傳成功後你會傳回後端
然後後端先copy一份(直接讀也行)在網站的資料夾裡
再用C#讀出Excel(此時可以用C#做檢查格式)
然後組好Html Table丟回前端
使用者看完確定要後再匯入

JS有沒有這種套件我倒是沒試過
不過最後都是需要後端再檢查一遍
不如直接給後端檢查就好了
C#的Excel套件很多

  • olddb
  • EPPlus
  • NPOI

我比較推NPOI
Nuget就能安裝了
參考教學

阿薛 iT邦新手 5 級 ‧ 2018-09-13 15:52:53 檢舉

已解決,謝謝各位大大。
解答如下:

        function UpLoadFileByCase(tableName, buttonName, divName, filepath) {
            var uploader = new plupload.Uploader({
                //用来指定上传方式
                runtimes: 'html5,flash,silverlight,html4',
                //触发文件选择对话框的DOM元素
                browse_button: buttonName,
                //用来指定Plupload所创建的html结构的父容器
                container: divName,
                //用来限定上传文件的大小
                max_file_size: '300mb',
                //服务器端接收和处理上传文件的脚本地址
                url: '@Url.Action("Upfiles", "Home", new { path  = "_path_"})'.replace('_path_', encodeURI(filepath)),
                //flash上传组件的url地址
                flash_swf_url: '@Url.Content("~/Scripts/plupload/Moxie.swf")',
                //silverlight上传组件的url地址
                silverlight_xap_url: '@Url.Content("~/Scripts/plupload/Moxie.xap")',
                //可以使用该参数来限制上传文件的类型
                filters: [
                    { title: "Doc files", extensions: "xlsx" }
                ]
            });
            //当Plupload初始化完成后触发
            uploader.bind('Init', function (up, params) {
            });
            //初始化Plupload实例
            uploader.init();
            //当文件添加到上传队列后触发
            uploader.bind('FilesAdded', function (up, files) {
                up.refresh();
                uploader.start();
            });
            //会在文件上传过程中不断触发,可以用此事件来显示上传进度
            uploader.bind('UploadProgress', function (up, file) {
            });
            //当发生错误时触发
            uploader.bind('Error', function (up, err) {
                alert("錯誤: " + err.code + ", Message: " + err.message + (err.file ? ", File: " + err.file.name : ""));
                up.refresh();
            });
            //当队列中的某一个文件上传完成后触发
            uploader.bind('FileUploaded', function (up, file, data) {
                $('#excelfile').text(file.name);
                var name=file.name;
                document.getElementById('excelfile').href = '@Url.Content("~/_path_")'.replace("_path_", filepath + "/" + data.response);
                document.getElementById('excelfile').target = "_blank";

                var file = data.response;
                if (file != '') {
                    var url = '@Url.Action("ccheckFa")';
                    $.getJSON(url, {
                        file: filepath + "/" + file
                    },
                     function (datat) {
                         var no = ($('#optable tr:last').attr('data-no') * 1.0 + 1);
                         if (isNaN(no)) { no = 0; };
                         var noo = no + 1;
                         for (var i in datat) {

                             var newRow =
                                  "<tr data-no='" + no + "'>" +
                                  "<td>" + noo + "</td>" +
                                      "<td><select id='Depppersonopinion_" + no + "__Depppersonid' name='Depppersonopinion[" + no + "].Depppersonid' class='form-control'>" + getPersonDDLs(datat[i].Depppersonid) + "</select></td>" +
                                      "<td><textarea id='Depppersonopinion_" + no + "__Depppersonopinion1' name='Depppersonopinion[" + no + "].Depppersonopinion1' class='form-control'>" + datat[i].Depppersonopinion1 + "</textarea></td>" +
                                      "<td><select id='Depppersonopinion_" + no + "__categoryid' name='Depppersonopinion[" + no + "].categoryid' class='form-control'>" + getTypeDDLs(datat[i].categoryid) + "</select></td>" +
                                      "<td><input id='Depppersonopinion_" + no + "__Deppfinishdate' type='text' name='Depppersonopinion[" + no + "].Deppfinishdate' class ='textbox datepick k-textbox' value='" + datat[i].Deppfinishdate + "'  data-role='maskedtextbox' autocomplete='off'/></td>" +
                                      "<td><textarea id='Depppersonopinion_" + no + "__handlesituation' name='Depppersonopinion[" + no + "].handlesituation' class='form-control'>" + datat[i].handlesituation + "</textarea></td>" +
                                      "<td><select id='Depppersonopinion_" + no + "__handleunit' name='Depppersonopinion[" + no + "].handleunit' class='form-control'>" + getPersonUnitDDLs(datat[i].handleunit) + "</select></td>" +
                                      "<td><select id='Depppersonopinion_" + no + "__isfinish' name='Depppersonopinion[" + no + "].isfinish' class='form-control'>" + getFinishDDLs(datat[i].isfinish) + "</select></td>" +
                                      "<td><textarea id='Depppersonopinion_" + no + "__unfinishedreason' name='Depppersonopinion[" + no + "].unfinishedreason' class='form-control'>" + datat[i].unfinishedreason + "</textarea></td>" +
                                      "<td><input id='Depppersonopinion_" + no + "__remark' name='Depppersonopinion[" + no + "].remark' class='form-control' value='" + datat[i].remark + "'/></td>" +
                                      "<td align='center'><a href='javascript:;' onClick='delop(this);'>刪除</a></td>" +
                                  "</tr>";
                             $('#optable tbody').append(newRow);
                             no = no + 1;
                             noo = noo + 1;
                         };

                         var newRow2 =
                           "<tr>" +
                               "<td>" + ($('#' + 'filetable' + ' tr:last').children("td:first").text() * 1.0 + 1) + "</td>" +
                               "<td><a href='" + '@Url.Content("~/_path_")'.replace("_path_", filepath + "/" + data.response) + "' target='_blank' data-file='" + data.response + "'>" + name + "</a></td>" +
                               "<td><input type='text' style='width: 100%' class='form-control' /></td>" +
                               "<td align='center'><a href='javascript:;' onClick='jqdel(this);'>刪除</a></td>" +
                           "</tr>";
                         $('#filetable tbody').append(newRow2);
                         /////新增button重新loading datepick.js
                         $(".datepick").datepicker({
                             yearSuffix: "", //將年改為空白
                             changeYear: true, //手動修改年
                             changeMonth: true, //手動修改月
                             showWeek: true, //顯示第幾周
                             firstDay: 1, //0為星期天
                             showOtherMonths: true, //在本月中顯示其他月份
                             selectOtherMonths: true, //可以在本月中選擇其他月份
                             showButtonPanel: false, //顯示bottom bar
                             closeText: '清除', //將離開改為清除

                             dateFormat: "yy/mm/dd",
                             onSelect: function (dateText, inst) {
                                 var dateFormate = inst.settings.dateFormat == null ? "yy/mm/dd" : inst.settings.dateFormat; //取出格式文字
                                 var reM = /m+/g;
                                 var reD = /d+/g;
                                 var objDate = {
                                     y: inst.selectedYear - 1911 < 0 ? inst.selectedYear : inst.selectedYear - 1911,
                                     m: String(inst.selectedMonth).length != 1 ? inst.selectedMonth + 1 : "0" + String(inst.selectedMonth + 1),
                                     d: String(inst.selectedDay).length != 1 ? inst.selectedDay : "0" + String(inst.selectedDay)
                                 };

                                 if (objDate.m == "010") {

                                     objDate.m = "10";

                                 }

                                 $.each(objDate, function (k, v) {
                                     var re = new RegExp(k + "+");
                                     dateFormate = dateFormate.replace(re, v);
                                 });
                                 inst.input.val(dateFormate);
                             }
                         });
                     }
                     )
                    .error(function () { alert("error"); });
                };
            });

        }
Homura iT邦研究生 2 級 ‧ 2018-09-13 16:36:12 檢舉

解決就好@@

我要發表回答

立即登入回答