最稳的pk10计划iphone 北京pk10计划手机软件 北京pk10数字的规律 超神手机版pk10软件 pk10北京赛车9码技巧 pk10四期倍投计划表 pk10极速赛车论坛 北京赛车冠军怎样选5码 北京赛车系统下载安装 pk10教程视频 北京pk10选号公式 北京赛车pk10赚钱技巧 北京赛车怎么提升概率 pk10技巧北京快三 北京pk10大小计划
VB.net 2010 視頻教程 VB.net 2010 視頻教程 VB.net 2010 視頻教程
SQL Server 2008 視頻教程 c#入門經典教程 Visual Basic從門到精通視頻教程
當前位置:
首頁 > 編程開發 > C#編程 >
  • C#教程之webapi 導入excel處理數據

  • 2019-08-16 20:27 來源:未知

參考資料

        https://blog.csdn.net/pan_junbiao/article/details/82935992

     https://www.cnblogs.com/dansediao/p/5482467.html

     https://www.cnblogs.com/shiyh/p/7478241.html

excel轉成datatable工具類(ExcelHelp)

 

復制代碼
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace ELearning.Common.Extensions
{
    public static class ExcelHelp
    {
        /// <summary>
        /// excel文件流轉化成datatable
        /// </summary>
        public static DataTable ExcelToTableForXLSX(Stream fileStream, bool haveNote = false)
        {
            var dt = new DataTable();
            using (var fs = fileStream)
            {
                var xssfworkbook = new XSSFWorkbook(fs);
                var sheet = xssfworkbook.GetSheetAt(0);
                //表頭  判斷是否包含備注
                var firstRowNum = sheet.FirstRowNum;
                if (haveNote)
                {
                    firstRowNum += 1;
                }
                var header = sheet.GetRow(firstRowNum);
                var columns = new List<int>();
                for (var i = 0; i < header.LastCellNum; i++)
                {
                    var obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        //continue;
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //數據
                for (var i = firstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var dr = dt.NewRow();
                    var hasValue = false;
                    if (sheet.GetRow(i) == null)
                    {
                        continue;
                    }
                    foreach (var j in columns)
                    {
                        var cell = sheet.GetRow(i).GetCell(j);
                        if (cell != null && cell.CellType == CellType.Numeric)
                        {
                            //NPOI中數字和日期都是NUMERIC類型的,這里對其進行判斷是否是日期類型
                            if (DateUtil.IsCellDateFormatted(cell)) //日期類型
                            {
                                dr[j] = cell.DateCellValue;
                            }
                            else //其他數字類型
                            {
                                dr[j] = cell.NumericCellValue;
                            }
                        }
                        else
                        {
                            dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
                        }
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 獲取單元格類型(xlsx)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLSX(XSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {

                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }

  #region  轉化實體為dataTable

        /// <summary>
        /// Convert a List{T} to a DataTable.
        /// </summary>
        public static DataTable ToDataTable<T>(this List<T> items)
        {
            var tb = new DataTable(typeof(T).Name);

            PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (PropertyInfo prop in props)
            {
                Type t = GetCoreType(prop.PropertyType);
                tb.Columns.Add(prop.Name, t);
            }

            foreach (T item in items)
            {
                var values = new object[props.Length];

                for (int i = 0; i < props.Length; i++)
                {
                    values[i] = props[i].GetValue(item, null);
                }

                tb.Rows.Add(values);
            }

            return tb;
        }

        /// <summary>
        /// Return underlying type if type is Nullable otherwise return the type
        /// </summary>
        public static Type GetCoreType(Type t)
        {
            if (t != null && IsNullable(t))
            {
                if (!t.IsValueType)
                {
                    return t;
                }
                else
                {
                    return Nullable.GetUnderlyingType(t);
                }
            }
            else
            {
                return t;
            }
        }

        /// <summary>
        /// Determine of specified type is nullable
        /// </summary>
        public static bool IsNullable(Type t)
        {
            return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
        }

        #endregion

        #region datatable to list

        /// <summary>
        /// DataTable轉成List
        /// </summary>
        public static List<T> ToDataList<T>(this DataTable dt)
        {
            var list = new List<T>();
            var plist = new List<PropertyInfo>(typeof(T).GetProperties());
            foreach (DataRow item in dt.Rows)
            {
                var s = Activator.CreateInstance<T>();
                for (var i = 0; i < dt.Columns.Count; i++)
                {
                    var info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
                    if (info != null)
                    {
                        try
                        {
                            if (!Convert.IsDBNull(item[i]))
                            {
                                object v = null;
                                if (info.PropertyType.ToString().Contains("System.Nullable"))
                                {
                                    v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
                                }
                                else
                                {
                                    v = Convert.ChangeType(item[i], info.PropertyType);
                                }
                                info.SetValue(s, v, null);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("字段[" + info.Name + "]轉換出錯," + ex.Message);
                        }
                    }
                }
                list.Add(s);
            }
            return list;
        }
        #endregion
     }
}
復制代碼

 

SqlBulkCopyHelper工具類

復制代碼
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace ELearning.Common.Helpers
{
    public class SqlBulkCopyHelper
    {
        public static void SaveTable(DataTable dtTable)
        {
            var connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
            var sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction) {BulkCopyTimeout = 5000};
            try
            {
                sbc.DestinationTableName = dtTable.TableName;
                sbc.WriteToServer(dtTable);
            }
            catch (Exception ex)
            {
                //處理異常
            }
            finally
            {
                //sqlcmd.Clone();
                //srcConnection.Close();
                //desConnection.Close();
            }
        }
    }
}
復制代碼

對應excel實體類:

復制代碼
    /// <summary>
    /// 導入用戶視圖模型
    /// </summary>
    public class InsertAdminUsersViewModel
    {
        public string 性別 { set; get; }
        public string 出生日期 { set; get; }
        public string 身份證號 { set; get; }
        public string 經銷商名稱 { set; get; }
        public string 經銷商崗位 { set; get; }
        public string 更新時間 { set; get; }
    }
復制代碼

導入信息接口(InsertAdminUsers):

復制代碼
        /// <summary>
        /// 導入用戶
        /// </summary>
        [HttpPost, Route("api/user/InsertAdminUsers")]
        [AllowAnonymous]
        public object InsertAdminUsers()
        {
            var filelist = HttpContext.Current.Request.Files;
            var users = new List<InsertAdminUsersViewModel>();
            if (filelist.Count > 0)
            {
                for (var i = 0; i < filelist.Count; i++)
                {
                    var file = filelist[i];
                    var dataTable = ExcelHelp.ExcelToTableForXLSX(file.InputStream);//excel轉成datatable
                    users = dataTable.ToDataList<InsertAdminUsersViewModel>();//datatable轉成list
                }
            }
            var succe = new List<ESysUser>();
            var faile = new List<ESysUser>();
            var names = userService.FindList(u => !u.IsDelete).Select(u => u.LoginName).ToList();
    //數據list轉成數據庫實體對應的list
            foreach (var u in users)
            {
                if (string.IsNullOrEmpty(u.狀態) || !u.狀態.Equals("1"))
                    continue;
                var s = new ESysUser
                {
                    CreateTime = DateTime.Now,
                    Birthday = DateTime.ParseExact(u.出生日期, "yyyyMMdd", CultureInfo.CurrentCulture),
                    Email = string.Empty,
                    IsDelete = false,
                    ModifyTime = DateTime.ParseExact(u.更新時間, "yyyyMMddHHmmssfff", CultureInfo.CurrentCulture),
                    UserID = GuidUtil.NewSequentialId(),
                    UserName = u.職員名稱,
                    UserType = "JXS",
                    Unumber = u.職員代碼,
                    AgentJobName = u.經銷商崗位,
                    AgentName = u.經銷商名稱.
                    CardNo = u.身份證號
                };
                if (!string.IsNullOrEmpty(s.CardNo) && s.CardNo.Length > 14)
                {
                    var str = s.CardNo.Substring(6, 8);
                    try
                    {
                        s.Birthday = DateTime.ParseExact(str, "yyyyMMdd", CultureInfo.CurrentCulture);
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e);
                    }

                }
                var t = names.Where(f => f == s.LoginName);
                var p1 = succe.Where(o => o.LoginName == s.LoginName);
                if (t.Any() || p1.Any())
                {
                    s.Remark = "登錄名重復";
                    faile.Add(s);
                }
                else
                {
                    succe.Add(s);
                }
            }
            var dt = succe.ToDataTable();//轉成 SqlBulkCopy所需要的類型:datatable
            if (string.IsNullOrEmpty(dt.TableName))
                dt.TableName = "ESysUser";
            var r = succe.Count;
            SqlBulkCopyHelper.SaveTable(dt);//批量插入
            var list = new { succeed = succe.Take(100).ToList(), failed = faile.Take(100).ToList() }; //數據太多的話,瀏覽器會崩潰
            return OK(list);
        }
復制代碼

基本上就是這些了。

pk10赛车冠军技巧
最稳的pk10计划iphone 北京pk10计划手机软件 北京pk10数字的规律 超神手机版pk10软件 pk10北京赛车9码技巧 pk10四期倍投计划表 pk10极速赛车论坛 北京赛车冠军怎样选5码 北京赛车系统下载安装 pk10教程视频 北京pk10选号公式 北京赛车pk10赚钱技巧 北京赛车怎么提升概率 pk10技巧北京快三 北京pk10大小计划
真人捕鱼官方网下载 安徽快3遗漏表 黑龙江36选7开奖结果查询 彩票站2016年好赚钱嘛 怎么才能下载黑龙江十一选五 单机麻将(全集) 3d100% 绝杀三码的方法 最新金蟾捕鱼游戏 3d中奖藏宝图彩票官网