• C# 实现xlsx文件导入


    1、前台页面

    <!DOCTYPE html>
    <html lang="en">
    
    <head>
        <meta charset="UTF-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Document</title>
    </head>
    <style>
        #loading {
            position: fixed;
            top: 50%;
            transform: translate(-50%, -50%);
            left: 50%;
            opacity: .8;
            -ms-flex-pack: center !important;
            justify-content: center !important;
            display: none;
        }
    
        .spinner {
            display: inline-block;
            width: 2rem;
            height: 2rem;
            vertical-align: text-bottom;
            border: 0.25em solid currentColor;
            border-right-color: transparent;
            border-radius: 50%;
            -webkit-animation: spinner .75s linear infinite;
            animation: spinner .75s linear infinite;
        }
    
        @keyframes spinner {
            to {
                transform: rotate(360deg);
            }
        }
    </style>
    
    <body>
        <button type="button" onclick="$('#btn').click()">upload</button>
        <input type="file" style="display: none;" id="btn" />
        <div id="loading">
            <div class="spinner">
                <span></span>
            </div>
        </div>
    </body>
    <script src="./jquery-3.3.1.min.js"></script>
    <script>
        $('#btn').change(function (e) {
            let formData = new FormData();
            formData.append('files', e.target.files[0]);
            formData.append('userId', 'xiaoming');
            $.ajax({
                type: 'POST',
                url: 'http://localhost:3000/api/upload',
                cache: false,
                data: formData,
                contentType: false, // 不设置数据类型
                processData: false, // 将数据转换成对象,不对数据做处理
                beforeSend: function (result) {
                    // 数据加载前,显示loading    
                    if (result.readyState == 0) {
                        $("body").attr("disabled", true);
                        $("body").css("opacity", '.5');
                        $("#loading").show();
                    }
                },
                success: function (result) {
                    console.log(result.Message);
                },
                complete: function (result) {
                    // 数据加载后,隐藏loading 
                    if (result.status == 200) {
                        $("body").attr("disabled", false);
                        $("body").css("opacity", '1');
                        $("#loading").hide();
                    }
                    $('#btn').val(''); // 文件导入完成后就清空
                },
                error: function (error) {
                    console.log(error)
                }
            })
        })
    </script>
    
    </html>
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90

    2、后台接口设计

    using Ascentn.Workflow.Base;
    using Medalsoft.Workflow.Resource.CommonUtil;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Web;
    using System.Web.Http;
    
    namespace Medalsoft.Workflow.Resource.Controllers
    {
        [RoutePrefix("api/upload")]
        public class UploadController : ApiController
        {
            string connstr = AgilePointUtil.CreateSQLConnectInstance(); //:获取数据库连接字符串
            /// <summary>
            /// 导入xlsx文件
            /// </summary>
            /// <returns></returns>
            [Route("ImportFile"), HttpPost]
            public ResultData ImportFile()
            {
                using (var w = new LimitedConcurrency())
                {
                    string fileName = "";
                    Stream fileStream = null;
                    var request = System.Web.HttpContext.Current.Request;
                    var formData = request.Form;
                    string userName = formData["userId"];
                    string clearOriginalTableSql = $@"TRUNCATE TABLE UserInfo";
                    string nowTime = DateTime.Now.ToString("yyyyMMddHHmmss"); // 获取当前时间
                    string createBackupTableSql = $@"SELECT * INTO UserInfobackup{nowTime} FROM UserInfo";
                    HttpFileCollection files = HttpContext.Current.Request.Files;
    
                    SqlHelper.ExecteNonQuery(connstr, CommandType.Text, createBackupTableSql, null); // 根据时间创建备份表
                    SqlHelper.ExecteNonQuery(connstr, CommandType.Text, clearOriginalTableSql, null); // 清空原数据表
    
                    SqlConnection myConnection = new SqlConnection(connstr);
                    myConnection.Open();
                    //启动一个事务
                    SqlTransaction myTrans = myConnection.BeginTransaction();
                    //为事务创建一个命令
                    SqlCommand myCommand = new SqlCommand();
                    myCommand.Connection = myConnection;
                    myCommand.Transaction = myTrans;
    
                    // 判断上传的是否是xlsx文件
                    foreach (string key in files.AllKeys)
                    {
                        HttpPostedFile file = files[key];
                        if (string.IsNullOrEmpty(file.FileName) == false)
                        {
                            fileName = file.FileName;
                            fileStream = file.InputStream;
                        }
                        else
                        {
                            return new ResultData { IsError = true, Message = "未获取到Excel数据" };
                        }
                    }
                    if (Path.GetExtension(fileName).ToLower() != ".xls" && Path.GetExtension(fileName).ToLower() != ".xlsx")
                    {
                        return new ResultData { IsError = true, Message = "请上传excel文件" };
                    }
    
                    // 读取文件
                    try
                    {
                        // 获取导入的xlsx文件数据
                        DataTable data = ExcelHelper.ImportStream(fileStream, fileName);
    
                        foreach (DataRow itemRows in data.Rows)
                        {
                            // string description = itemRows.ItemArray[1].ToString();
                            // string description = itemRows["UserDescription"].ToString();
                            string description = HandleString(itemRows["UserDescription"].ToString());
                            string userSex = itemRows["UserSex"].ToString();
                            string userAge = itemRows["UserAge"].ToString();
                            if (StringIsEmpty(userName))
                            {
                                return new ResultData { IsError = true, Message = "UserName不能为空" };
                            }
                            if (StringIsEmpty(description))
                            {
                                return new ResultData { IsError = true, Message = "UserDescription不能为空" };
                            }
                            if (StringIsEmpty(userSex))
                            {
                                return new ResultData { IsError = true, Message = "UserSex不能为空" };
                            }
                            if (!IsNumber(userAge))
                            {
                                return new ResultData { IsError = true, Message = "UserAge不是数字类型" };
                            }
                            string sql = $@"INSERT INTO UserInfo(
                                                        [ID],
                                                        [CREATED_DATE],
                                                        [UserName],
                                                        [UserSex],
                                                        [UserAge]
                                                        [UserDescription] VALUES (
                                                        NEWID(),
                                                        GETDATE(),
                                                        '{userName}',
                                                        '{userSex}',
                                                        '{userAge}', 
                                                        '{description}')";
    
                            myCommand.CommandText = sql;
                            myCommand.ExecuteNonQuery();
                            // SqlHelper.ExecteNonQuery(connstr, CommandType.Text, sql, null);
    
                        }
    
                        myTrans.Commit();
                        return new ResultData { IsError = false, Message = "数据导入成功" };
                    }
                    catch (Exception ex)
                    {
                        myTrans.Rollback();  //遇到错误回滚
                        throw ex;
                    }
    
                }
    
            }
    
            // 判断字符串
            public  bool StringIsEmpty(string str)
            {
                if (string.IsNullOrEmpty(str)) // 是空的返回true否则返回false
                {
                    return true;
                }
                return false;
            }
    
            // 判断数字
            public  bool IsNumber(string number)  // 是数字返回true否则返回false
            {
                decimal num = 0;
                bool n = decimal.TryParse(number, out num);
                if (!string.IsNullOrEmpty(number) && n)
                {
                    return true;
                }
                return false;
            }
    
            public string HandleString(string content)
            {
                if (content.Contains("'"))
                {
                    content = content.Replace("'", "''");
                }
                return content;
            }
    
    
            public class ResultData
            {
                public bool IsError { get; set; }
                public string Message { get; set; }
            }
        }
    }
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
  • 相关阅读:
    软件工程笔记001
    python爬虫入门(四)爬取猫眼电影排行(使用requests库和正则表达式)
    HanLP — Aho-Corasick DoubleArrayTire 算法 ACDAT - 基于双数组字典树的AC自动机
    数据结构与算法训练:第十二弹
    应用系统设计:预约挂号平台,B2C平台设计
    利用shp文件构建mask【MATLAB和ARCGIS】两种方法
    【mia】3 本地rtc发布/推流:frame分发
    Android触摸屏TP crah 日志 addr2line
    mmsegmentation 训练自己的数据集
    应用第三方ByteTrack实现目标跟踪
  • 原文地址:https://blog.csdn.net/likepoems/article/details/125373080