欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

ASP.NET MVC NPOI导入Excel DataTable批量导入到数据库

程序员文章站 2024-01-04 20:11:34
使用NPOI导入Excel 首先在MVC项目中导入NPOI 查询NPOI安装,排序依据,选择:最高下载量,选择第一个。 在控制器中创建ExcelController 在Index视图中写入代码: @using (Html.BeginForm("Import", "Excel", FormMethod ......

使用NPOI导入Excel

首先在MVC项目中导入NPOI

ASP.NET MVC NPOI导入Excel DataTable批量导入到数据库

查询NPOI安装,排序依据,选择:最高下载量,选择第一个。

ASP.NET MVC NPOI导入Excel DataTable批量导入到数据库

在控制器中创建ExcelController

ASP.NET MVC NPOI导入Excel DataTable批量导入到数据库

ASP.NET MVC NPOI导入Excel DataTable批量导入到数据库

ASP.NET MVC NPOI导入Excel DataTable批量导入到数据库

在Index视图中写入代码:

@using (Html.BeginForm("Import", "Excel", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<input name="file" type="file" id="=file" />
<input name="submit" id="submit" type="submit" value="批量导入" />
}

创建Import控制器

ASP.NET MVC NPOI导入Excel DataTable批量导入到数据库

 

添加Import视图

ASP.NET MVC NPOI导入Excel DataTable批量导入到数据库

 

Import控制器中的代码:

        [HttpPost]
        public ActionResult Import(HttpPostedFileBase file)
        {
            var fileName = file.FileName;
            var filePath = Server.MapPath(string.Format("~/{0}", "Files"));
            string path = Path.Combine(filePath, fileName);
            file.SaveAs(path);

            DataTable excelTable = new DataTable();
            excelTable = ImportExcel.GetExcelDataTable(path);

            DataTable dbdata = new DataTable();
            dbdata.Columns.Add("email");
            dbdata.Columns.Add("pwd");
            dbdata.Columns.Add("logintime");

            for (int i = 0; i < excelTable.Rows.Count; i++)
            {
                DataRow dr = excelTable.Rows[i];
                DataRow dr_ = dbdata.NewRow();
                dr_["email"] = dr["邮箱"];
                dr_["pwd"] = dr["密码"];
                dr_["logintime"] = dr["时间"];
                dbdata.Rows.Add(dr_);
            }
            RemoveEmpty(dbdata);

            string constr = System.Configuration.ConfigurationManager.AppSettings["meixinEntities_"];

            SqlBulkCopyByDatatable(constr, "m_user1", dbdata);

            return View();
        }

 

添加logic文档,添加ImportExcel类,添加一个Files文档保存Excel文件

ASP.NET MVC NPOI导入Excel DataTable批量导入到数据库

这里需要引用NPOI类库,ImportExcel类里的代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI;
using System.Data;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

namespace MvcExcel导入导出1.logic
{
    public class ImportExcel
    {

        public static DataTable GetExcelDataTable(string filePath)
        {
            IWorkbook Workbook;
            DataTable table = new DataTable();
            try
            {
                using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook 适用XLSX格式,HSSFWorkbook 适用XLS格式
                    string fileExt = Path.GetExtension(filePath).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            //定位在第一个sheet
            ISheet sheet = Workbook.GetSheetAt(0);
            //第一行为标题行
            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
            int rowCount = sheet.LastRowNum;

            //循环添加标题列
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            //数据
            for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();
                if (row != null)
                {
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dataRow[j] = GetCellValue(row.GetCell(j));
                        }
                    }
                }
                table.Rows.Add(dataRow);
            }
            return table;
        }

        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
            {
                return string.Empty;
            }

            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:
                case CellType.Unknown:
                default:
                    return cell.ToString();
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }

    }
}

 

在Import控制器中获取到datatable的数据之后,在下面添加批量插入DataTable数据的方法,传入三个参数。

        /// <summary>
        /// 大数据插入
        /// </summary>
        /// <param name="connectionString">目标库连接</param>
        /// <param name="TableName">目标表</param>
        /// <param name="dtSelect">来源数据</param>
        public static void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dtSelect)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = TableName;
                        sqlbulkcopy.BatchSize = 20000;
                        sqlbulkcopy.BulkCopyTimeout = 0;//不限时间
                        for (int i = 0; i < dtSelect.Columns.Count; i++)
                        {
                            sqlbulkcopy.ColumnMappings.Add(dtSelect.Columns[i].ColumnName, dtSelect.Columns[i].ColumnName);
                        }
                        sqlbulkcopy.WriteToServer(dtSelect);
                    }
                    catch (System.Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }

 

第一个是从NPOI读取到的Excel的数据,保存到excelTable里,第二个是把表的中文表头转换成数据库表中对应的英文,第三个是获取连接字符串,调用批量插入数据库的方法

ASP.NET MVC NPOI导入Excel DataTable批量导入到数据库

插入数据需要传入数据库连接字符串,在Web.config中的<appSettings>中添加自己的数据库连接字符串

ASP.NET MVC NPOI导入Excel DataTable批量导入到数据库

 

ASP.NET MVC NPOI导入Excel DataTable批量导入到数据库

在导入Excel数据的时候,有时候会有空行,用RemoveEmpty方法去空,代码如下

        protected void RemoveEmpty(DataTable dt)
        {
            List<DataRow> removelist = new List<DataRow>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool IsNull = true;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                    {
                        IsNull = false;
                    }
                }
                if (IsNull)
                {
                    removelist.Add(dt.Rows[i]);
                }
            }
            for (int i = 0; i < removelist.Count; i++)
            {
                dt.Rows.Remove(removelist[i]);
            }
        }

 

上一篇:

下一篇: