ASP.NET 上傳文件導(dǎo)入Excel的示例

更新時(shí)間：2021年04月13日 09:29:22 作者：大穩(wěn)·楊

這篇文章主要介紹了ASP.NET 上傳文件導(dǎo)入Excel的示例，幫助大家更好的理解和學(xué)習(xí)使用.net技術(shù)，感興趣的朋友可以了解下

前言

　　本文對應(yīng)的場景是導(dǎo)入Excel數(shù)據(jù)，Excel對應(yīng)的字段都配置在xml文件中。截圖如下：

代碼實(shí)戰(zhàn)

　　工具類

　　實(shí)體類：XMLReadModel.cs

public class XMLReadModel
    {
        /// <summary>
        /// 導(dǎo)入所需鍵值對
        /// </summary>
        public Hashtable ImportHashtable { set; get; } = new Hashtable();

        /// <summary>
        /// 導(dǎo)出所需鍵值對
        /// </summary>
        public Hashtable ExportHashtable { set; get; } = new Hashtable();
    }

　　工具方法：讀取xml文件內(nèi)容到實(shí)體中。

/// <summary>
        /// 讀取xml文件到hashtable
        /// </summary>
        public static XMLReadModel ReadToHashtable(string path)
        {
            var xr = new XMLReadModel();
            var xmldoc = new XmlDocument();
            xmldoc.Load(path);
            //獲取節(jié)點(diǎn)列表 
            var topM = xmldoc.SelectNodes("http://ColumnName");
            foreach (XmlElement element in topM)
            {
                var enabled = element.Attributes[0].Value;
                if (enabled == "true") //字段啟用
                {
                    var dbProperty = element.GetElementsByTagName("DbProperty")[0].InnerText;
                    var excelProperty = element.GetElementsByTagName("ExcelProperty")[0].InnerText;
                    if (!xr.ImportHashtable.ContainsKey(excelProperty))
                    {
                        xr.ImportHashtable.Add(excelProperty, dbProperty);
                    }
                    if (!xr.ExportHashtable.ContainsKey(dbProperty))
                    {
                        xr.ExportHashtable.Add(dbProperty, excelProperty);
                    }
                }
            }
            return xr;
        }

　　Excel文件內(nèi)容轉(zhuǎn)成datatable方法

/// <summary>
        /// excel文件流轉(zhuǎn)化成datatable
        /// </summary>
        public static DataTable ExcelToTableForXLSX(Stream fileStream, Hashtable ht = null, 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
                    {
                        if (ht != null)
                        {
                            var o = ht[obj.ToString()].ToString();//這里就是根據(jù)xml中讀取的字段對應(yīng)關(guān)系進(jìn)行字段賦值的。
                            dt.Columns.Add(new DataColumn(o));
                        }
                        else
                        {
                            dt.Columns.Add(new DataColumn(obj.ToString()));
                        }
                    }
                    columns.Add(i);
                }
                //數(shù)據(jù)
                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中數(shù)字和日期都是NUMERIC類型的，這里對其進(jìn)行判斷是否是日期類型
                            if (DateUtil.IsCellDateFormatted(cell)) //日期類型
                            {
                                dr[j] = cell.DateCellValue;
                            }
                            else //其他數(shù)字類型
                            {
                                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;
        }

　　獲取Excel單元格值類型，轉(zhuǎn)成C#對應(yīng)的值類型。

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

　　datatable轉(zhuǎn)成list實(shí)體方法

/// <summary>
        /// DataTable轉(zhuǎn)成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
                                {
                                    if (info.PropertyType.Equals(typeof(bool)))
                                    {
                                        var value = item[i].ToString();
                                        if (value.Equals("true", StringComparison.CurrentCultureIgnoreCase) || value.Equals("false", StringComparison.CurrentCultureIgnoreCase))
                                            v = Convert.ChangeType(item[i], info.PropertyType);
                                        else if (value.Equals("1", StringComparison.CurrentCultureIgnoreCase) || value.Equals("0", StringComparison.CurrentCultureIgnoreCase))
                                        {
                                            if (value.Equals("1", StringComparison.CurrentCultureIgnoreCase))
                                                v = true;
                                            else
                                                v = false;
                                        }
                                    }
                                    else
                                    {
                                        v = Convert.ChangeType(item[i], info.PropertyType);
                                    }
                                }

                                info.SetValue(s, v, null);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("字段[" + info.Name + "]轉(zhuǎn)換出錯(cuò)," + ex.Message);
                        }
                    }
                }
                list.Add(s);
            }
            return list;
        }

　　導(dǎo)入Excel方法

[HttpPost, Route("api/Workstage/ImportFile")]
        public object ImportFile()
        {
            var filelist = HttpContext.Current.Request.Files;
            var models = new List<DModel>();
            var path = HttpContext.Current.Server.MapPath("/ImportConfig/ModelConfig.xml");
            var xr = XMLHelper.ReadToHashtable(path);//讀取Excel的字段對應(yīng)關(guān)系，代碼的實(shí)體字段和Excel中的字段對應(yīng)，在后面的Excel的值讀取還有數(shù)據(jù)庫實(shí)體賦值用得到。

            try
            {
                if (filelist.Count > 0)
                {
                    for (var i = 0; i < filelist.Count; i++)
                    {
                        var file = filelist[i];
                        var fileName = file.FileName;
                        var fn = fileName.Split('\\');
                        if (fn.Length > 1)
                        {
                            fileName = fn[fn.Length - 1];
                        }
                        DataTable dataTable = null;
                        var fs = fileName.Split('.');
                        if (fs.Length > 1)
                        {
                            dataTable = ExcelHelp.ExcelToTableForXLSX(file.InputStream, xr.ImportHashtable); //excel轉(zhuǎn)成datatable
                        }
                        models = dataTable.ToDataList<DWorkstage>(); //datatable轉(zhuǎn)成list
                    }
                }
                var succe = new List<DModel>();//需要插入的數(shù)據(jù)列表
                var exportList = new List<DModel>();//需要導(dǎo)出給用戶的失敗數(shù)據(jù)列表
                // 做一些數(shù)據(jù)邏輯處理，把處理好的數(shù)據(jù)加到succe列表中
                if (succe.Any())
                {
                    SqlBulkCopyHelper.BulkInsertData(succe, "DModel");
                }

                var url = string.Empty;
                if (exportList.Any())
                {
                    var extDt = exportList.ToDataTable(xr.ExportHashtable);//把數(shù)據(jù)庫中的字段轉(zhuǎn)成Excel中需要展示的字段，并保存到datatable中。
                    url = SaveFile(extDt, "失敗信息.xlsx");//把datatable保存到本地服務(wù)器或者文件服務(wù)器中，然后把文件下載地址返回給前端。
                }

                var list = new { failed = faile.Take(100).ToList(), failedCount = faile.Count }; //數(shù)據(jù)太多的話，瀏覽器會崩潰
                 var json = new { list, msg = "添加成功", url };
                 return  json;

            }
            catch (Exception ex)
            {
                var json = new { msg = "添加失敗", ex.Message, ex };
                return  json;
            }
        }

具體的xml文件

　　　　具體的節(jié)點(diǎn)可以自己命名。

<?xml version="1.0" encoding="utf-8" ?>
<TableConfig>
  <!--商品名稱-->
  <ColumnName Enabled="true" >
    <DbProperty>ProductName</DbProperty>
    <ExcelProperty>商品名稱</ExcelProperty>
  </ColumnName>
  <!--原因,導(dǎo)出失敗列表時(shí)用到的字段，導(dǎo)入時(shí)用不到-->
  <ColumnName Enabled="true" >
    <DbProperty>SourceCode</DbProperty>
    <ExcelProperty>原因</ExcelProperty>
  </ColumnName>
  <!--創(chuàng)建時(shí)間-->
  <ColumnName Enabled="true" >
    <DbProperty>CreateTime</DbProperty>
    <ExcelProperty>創(chuàng)建時(shí)間</ExcelProperty>
  </ColumnName>
  <!--更新時(shí)間-->
  <ColumnName Enabled="true" >
    <DbProperty>UpdateTime</DbProperty>
    <ExcelProperty>更新時(shí)間</ExcelProperty>
  </ColumnName>
</TableConfig>