asp.net mvc 上传excel文件并读取excle内容转成DataTable(Spire.Office.3.6.0)

CSDN的编辑器乱码了！请点击这里看不乱码的：

https://blog.csdn.net/djk8888/article/details/80938469

本文配套源码下载：https://download.csdn.net/download/djk8888/10502601

一个需求：将一个Excel文件中的数据导入到数据库中去。

思路：上传一个excel文件，读取该excel文件中数据，转成DataTable(或List)，循环insert到数据库中去

截图如下：

前端用到：jQuery-File-Upload-8.8.5    后台用到：Spire.Office.3.6.0

html代码:

@{
    ViewBag.Title = "djk8888";
}
<script src="~/jQuery-File-Upload-8.8.5/js/jquery.min.js"></script>
<script src="~/jQuery-File-Upload-8.8.5/js/vendor/jquery.ui.widget.js"></script>
<script src="~/jQuery-File-Upload-8.8.5/js/jquery.iframe-transport.js"></script>
<script src="~/jQuery-File-Upload-8.8.5/js/jquery.fileupload.js"></script>
<script src="~/jQuery-File-Upload-8.8.5/js/jquery.fileupload-process.js"></script>
<script src="~/jQuery-File-Upload-8.8.5/js/jquery.fileupload-validate.js"></script>
<link href="~/jQuery-File-Upload-8.8.5/css/jquery.fileupload-ui.css" rel="stylesheet" />
<style type="text/css">
    .bar {
        height: 18px;
        background: green;
    }
</style>
<script>
    $(function () {
        $('#fileupload').fileupload({
            url: "/Home/UploadFiles",
            dataType: 'json',
            replaceFileInput: false,//显示已选择文件文件名(必须)
            progressall: function (e, data) {
                var progress = parseInt(data.loaded / data.total * 100, 10);
                $('#progress .bar').css('width', progress + '%');
            },
            add: function (e, data) {
                //判断文件类型，此例限制为：Excel文件
                var fileType = data.files[0].name.split('.').pop();
                var acceptFileTypes = /xls|xlsx$/i;
                if (!acceptFileTypes.test(fileType)) {
                    $("#startBtn").hide();//隐藏上传按钮
                    alert("不支持的文件类型，仅支持EXCEL文件");
                    return;
                }
                //判断文件大小，此例限制为：1mb：
                var size = data.files[0].size;
                size = (size / 1024).toFixed(2);//文件大小单位kb
                var maxFileSize = 1024;//1mb=1024kb
                if (size > maxFileSize) {
                    $("#startBtn").hide();//隐藏上传按钮
                    alert("文件大小：" + size + "KB,超过最大限制：" + maxFileSize + "KB");
                    return;
                }
                $("#startBtn").show();//显示上传按钮
                //点击上传按钮开始上传
                data.context = $('#startBtn').click(function () {
                    data.context = $('<p/>').text('Uploading...').replaceAll($(this));
                    data.submit();
                });
            },
            done: function (e, data) {
                data.context = $('<p/>').text("文件上传成功!").replaceAll($(this));
                
                $("#tab1").empty();
                $('#tab1').append("<tr><th>进度</th><th>课程</th><th>说明</th></tr>");
                $.ajax({
                    type: "GET",
                    url: "/Home/ReadFile",
                    data: {},
                    dataType: "json",
                    success: function (result) {                     
                        var html = '';
                        for (var i = 0; i < result.length; i++) {
                            html += '<tr>';
                            html += '<td>' + result[i].no + '</td>';
                            html += '<td>' + result[i].title  + '</td>';
                            html += '<td>' + result[i].note  + '</td>';
                            html += '</tr>';
                        }              
                        $('#tab1').append(html);
                    }
                });

            },
            change: function (e, data) {
                if (data.files.length > 1) {
                    $("#startBtn").hide();//隐藏上传按钮
                    alert("只能选择1个文件");
                    return false;
                }
            },
            drop: function (e, data) {
                if (data.files.length > 1) {
                    $("#startBtn").hide();//隐藏上传按钮
                    alert("只能选择1个文件");
                    return false;
                }
            },
        });
    });

</script>
<h2>用jQuery-File-Upload上传Excel文件(ASP.NET MVC)</h2>
<input id="fileupload" type="file" name="multiFiles" multiple />
<button id="startBtn" style="display:none;">上传</button>
<div id="progress">
    <div class="bar" style="width: 0%;"></div>
</div>
<br />
<table border="1" id="tab1"></table>

mvc代码：

using Spire.Xls;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Web;
using System.Web.Mvc;

namespace workbook.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }
        /// <summary>
        /// 上传excel文件
        /// </summary>
        [HttpPost]
        public ActionResult UploadFiles()
        {
            bool b = false;
            var statuses = new List<ViewDataUploadFilesResult>();
            var headers = Request.Headers;
            if (string.IsNullOrEmpty(headers["X-File-Name"]))
            {
                b = UploadWholeFile(Request, statuses);//上传文件
            }
            if (b)//上传成功
            {
                JsonResult result = Json(statuses);
                result.ContentType = "text/plain";
                return result;
            }
            var r = new List<ViewDataUploadFilesResult>();
            return Json(r);//上传失败
        }
        private string StorageRoot
        {
            get { return Path.Combine(Server.MapPath("~/Files")); }//文件上传的路径：根目录下的/Files 文件夹
        }
        private bool UploadWholeFile(HttpRequestBase request, List<ViewDataUploadFilesResult> statuses)
        {
            try
            {
                var file = request.Files[0];//文件源
                var fullPath = Path.Combine(StorageRoot, Path.GetFileName(file.FileName));//服务器文件完整路径
                file.SaveAs(fullPath);//复制文件

                Workbook workbook = new Workbook();
                workbook.LoadFromFile(fullPath);
                //读取第一个sheet
                Worksheet worksheet = workbook.Worksheets[0];
                var dt = worksheet.ExportDataTable();

                //至此：已经获取上传的Excel文件中的内容并转成DataTable，即可进行写入(导入)数据库的操作(略)     
                Session["Upload"] = dt;//存到Session,用于展示上传的excel文件中的内容

                statuses.Add(new ViewDataUploadFilesResult()
                {
                    name = file.FileName,
                    size = file.ContentLength,
                    type = file.ContentType,
                });

                return true;
            }
            catch
            {
                return false;
            }
        }
        public class ViewDataUploadFilesResult
        {
            public string name { get; set; }
            public int size { get; set; }
            public string type { get; set; }
            public string url { get; set; }
            public string delete_url { get; set; }
            public string thumbnail_url { get; set; }
            public string delete_type { get; set; }
        }
        /// <summary>
        /// 展示上传的excel文件中的内容
        /// </summary>
        public ActionResult ReadFile()
        {
            DataTable dt = Session["Upload"] as DataTable;
            string json = "";
            json += "[";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                json += "{";
                json += "\"no\":" + "\"" + dt.Rows[i]["no"].ToString() + "\"" + ",";
                json += "\"title\":" + "\"" + dt.Rows[i]["title"].ToString() + "\"" + ",";
                json += "\"note\":" + "\"" + dt.Rows[i]["note"].ToString() + "\"";
                json += "}";
                json += ",";
            }
            json = json.Substring(0, json.Length - 1);
            json += "]";
            return Content(json);
        }
    }
}

本文配套源码下载： https://download.csdn.net/download/djk8888/10502601

上传文件相关资料：https://blog.csdn.net/djk8888/article/details/78835131