爱上编程网

ASP.NETCore -----导入Excel文件

  • 时间:2019-11-01 17:20 编辑:青柠小助手 来源:计算机/互联网 阅读:99941
  • 扫一扫,手机访问
摘要:ASP.NETCore -----导入Excel文件

标签:win   前端   for   empty   value   div   returns   i++   tor   

前端上传excel文件利用npoi读取数据转换成datatable(netcore坑爹啊,用的vs2017竟然不能可视化)

前端界面

@{
    Layout = null;
}
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Xlsx</title>
</head>
<body>
    <form enctype="multipart/form-data" method="post" asp-action="ImportToDataTable">
        <input type="file" name="excelfile" />
        <input type="submit" value="上传" />
    </form>
   
</body>
</html>

 

后台

 /// <summary>
        /// 导入EXCEL数据  
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        
        public void  ImportToDataTable(IFormFile excelfile)
        {         
            DataTable dt = new DataTable();
            var filePath = excelfile.FileName.Split(.);
            if (filePath[1].ToLower()==".xls")
            {//.xls
                #region .xls文件处理:HSSFWorkbook

                HSSFWorkbook hssfworkbook;
                try
                {
                    using (MemoryStream ms = new MemoryStream())
                    {
                        excelfile.CopyTo(ms);
                        ms.Seek(0, SeekOrigin.Begin);
                        hssfworkbook = new HSSFWorkbook(ms);
                    }
                   
                    ISheet sheet = hssfworkbook.GetSheetAt(0);
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                    var maxRowIndex = 0;

                    while (rows.MoveNext())
                    {
                        IRow row = (HSSFRow)rows.Current;
                        var r = row.Cells.Select(p => p.StringCellValue).ToList();
                        var _result = row.Cells.Any(t => !string.IsNullOrEmpty(t.StringCellValue));
                        if (_result)
                        {
                            maxRowIndex = row.RowNum;
                            break;
                        }
                    }

                    HSSFRow headerRow = (HSSFRow)sheet.GetRow(maxRowIndex);//取第二行

                    var _ColumnIndex = new List<int>();

                    //一行最后一个方格的编号 即总的列数 
                    for (int j = 0; j < (headerRow.LastCellNum); j++)
                    {
                        //SET EVERY COLUMN NAME
                        HSSFCell cell = (HSSFCell)headerRow.GetCell(j);

                        if (!string.IsNullOrEmpty(cell.StringCellValue))
                        {
                            dt.Columns.Add(cell.ToString());
                            _ColumnIndex.Add(cell.ColumnIndex);
                        }
                    }

                    while (rows.MoveNext())
                    {
                        IRow row = (HSSFRow)rows.Current;
                        DataRow dr = dt.NewRow();

                        if (row.RowNum <= headerRow.RowNum) continue;

                        for (int i = 0; i < _ColumnIndex.Count; i++)
                        {                            
                            if (i >= dt.Columns.Count)
                            {
                                break;
                            }
                            ICell cell = row.GetCell(_ColumnIndex[i]);

                            if ((i == 0) && cell == null)//每行第一个cell为空,break
                            {
                                break;
                            }

                            if (cell == null)
                            {
                                dr[i] = null;
                            }
                            else
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.String:
                                        dr[i] = cell.StringCellValue;
                                        break;
                                    case CellType.Numeric:

                                        if (DateUtil.IsCellDateFormatted(cell))
                                        {
                                            dr[i] = cell.DateCellValue;
                                        }
                                        else
                                        {
                                            dr[i] = cell.NumericCellValue;
                                        }
                                        break;
                                    default:
                                        dr[i] = null;
                                        break;
                                }
                            }
                        }
                      
                            dt.Rows.Add(dr);
                                            
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message, e);
                }

                #endregion
            }
            else
            {//.xlsx
                #region .xlsx文件处理:XSSFWorkbook

                XSSFWorkbook hssfworkbook;
                try
                {
                    using (MemoryStream ms = new MemoryStream())
                    {
                        excelfile.CopyTo(ms);
                        ms.Seek(0, SeekOrigin.Begin);
                        hssfworkbook = new XSSFWorkbook(ms);
                    }                   
                    ISheet sheet = hssfworkbook.GetSheetAt(0);
                    // ISheet sheet = hssfworkbook.GetSheet(sheetName);
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                    var maxRowIndex = 0;

                    while (rows.MoveNext())
                    {
                        IRow row = (XSSFRow)rows.Current;
                        var _result = row.Cells.Any(t => !string.IsNullOrEmpty(t.StringCellValue));
                        if (_result)
                        {
                            maxRowIndex = row.RowNum;
                            break;
                        }
                    }
                    XSSFRow headerRow = (XSSFRow)sheet.GetRow(maxRowIndex);//取第二行
                    var _ColumnIndex = new List<int>();

                    //一行最后一个方格的编号 即总的列数 
                    for (int j = 0; j < (headerRow.LastCellNum); j++)
                    {
                        //SET EVERY COLUMN NAME
                        XSSFCell cell = (XSSFCell)headerRow.GetCell(j);

                        if (!string.IsNullOrEmpty(cell.StringCellValue))
                        {
                            dt.Columns.Add(cell.ToString());
                            _ColumnIndex.Add(cell.ColumnIndex);
                        }
                    }

                    while (rows.MoveNext())
                    {
                        IRow row = (XSSFRow)rows.Current;
                        DataRow dr = dt.NewRow();

                        if (row.RowNum == 0) continue;

                        for (int i = 0; i < _ColumnIndex.Count; i++)
                        {
                            if (i >= dt.Columns.Count)
                            {
                                break;
                            }

                            ICell cell = row.GetCell(_ColumnIndex[i]);

                            if ((i == 0) && (cell == null))//每行第一个cell为空,break
                            {
                                break;
                            }

                            if (cell == null)
                            {
                                dr[i] = null;
                            }
                            else
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.String:
                                        dr[i] = cell.StringCellValue;
                                        break;
                                    case CellType.Numeric:

                                        if (DateUtil.IsCellDateFormatted(cell))
                                        {
                                            dr[i] = cell.DateCellValue;
                                        }
                                        else
                                        {
                                            dr[i] = cell.NumericCellValue;
                                        }
                                        break;
                                    default:
                                        dr[i] = null;
                                        break;
                                }
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message, e);
                }
                #endregion
            }
            var a =  dt.Rows.Count;//测试看是否有数据
        }
       

 

ASP.NETCore -----导入Excel文件

标签:win   前端   for   empty   value   div   returns   i++   tor   

原文地址:https://www.cnblogs.com/macT/p/11613663.html



技术交流学习或者有任何问题欢迎加群

编程技术交流群 : 154514123 爱上编程
Java技术交流群 : 6128790  Java

  • 全部评论(0)
最新发布的资讯信息
【数码/游戏/手机|网络游戏】some try on func swap about & and *(2019-12-06 18:04)
【数码/游戏/手机|网络游戏】“云小蜜”使用总结(2019-12-06 18:03)
【数码/游戏/手机|网络游戏】测验6: 组合数据类型 (第6周)-程序题(2019-12-06 18:03)
【数码/游戏/手机|网络游戏】Django大纲(2019-12-06 18:03)
【数码/游戏/手机|网络游戏】自下而上语法分析(2019-12-06 18:03)
【数码/游戏/手机|网络游戏】RocketMq安装(2019-12-06 18:03)
【数码/游戏/手机|网络游戏】JVM内存和JVM调优(十)--总结(2019-12-06 18:03)
【数码/游戏/手机|网络游戏】JVM-基本操作(2019-12-06 18:03)
【数码/游戏/手机|网络游戏】如何用Jpype创建HashMap和ArrayList(2019-12-06 18:03)
【数码/游戏/手机|网络游戏】Pandas操作excel(2019-12-06 18:03)
展开