C# 实现xlsx文件导入

最新推荐文章于 2024-08-14 11:34:36 发布

likepoems

最新推荐文章于 2024-08-14 11:34:36 发布

阅读量820

点赞数

分类专栏： C# 文章标签： c# javascript html

本文链接：https://blog.csdn.net/likepoems/article/details/125373080

版权

C# 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

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>

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; }
        }
    }
}