EXCEL批量导入数据MVC

来源：互联网发布：雅典奥运女排知乎编辑：程序博客网时间：2024/06/04 19:44

这里简单实现EXCEL的批量导入

直接看代码，里面有注释

前端页面实现

@{    ViewBag.Title = "批量添加数据";    //Layout = null;}    .hide {        display: none;    }    td {        height: 50px;    }    .input-group-addon {        width: 85px;    }    .jFiler-item {        list-style: none;    }    jFiler-item-title {        display: none;    }    .jFiler-item-thumb .jFiler-item-info {        display: none;    }    .jFiler-theme-default .jFiler-input {        width: 300px;    }    img {        width: 300px;    }            耗材数据Excel表格批量导入        下载模板

后台需要引用dll

using NPOI.HSSF.UserModel;//批量导入的页面Actionpublic ActionResult ExcelAppendData(){    return View();}        ///         /// 导入Excel方法        /// 作者 刘建超：2017年10月16日17:32:11        /// 
        ///         [HttpPost]        public JsonResult LeadInMethod()        {            HttpPostedFileBase file = Request.Files["files"];            try            {                string FileName;                string savePath;                //添加公有字段，成功后传给前台，便于提示用户：文件导入成功，不能重复导入                string fileFullName;                #region 检查文件/存储文件                if (file == null || file.ContentLength <= 0)                {                    return Json(new { result = false, message = "文件不能为空,请先浏览选择需要导入的耗材表Excel文件！" }, JsonRequestBehavior.AllowGet);                }                else                {                    string filename = Path.GetFileName(file.FileName);                    int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte                    string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名                    string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名                    int Maxsize = 20 * 1024 * 1024;//定义上传文件的最大空间大小为20M                    string FileType = ".xls,.xlsx";//定义上传文件的类型字符串                    FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;                    if (!FileType.Contains(fileEx))                    {                        return Json(new { result = false, message = "文件类型不对，只能导入xls和xlsx格式的文件" }, JsonRequestBehavior.AllowGet);                    }                    if (filesize >= Maxsize)                    {                        return Json(new { result = false, message = "上传文件超过20M，不能上传" }, JsonRequestBehavior.AllowGet);                    }                    string path = AppDomain.CurrentDomain.BaseDirectory + @"\Files\Excel";                    savePath = Path.Combine(path, FileName);                    fileFullName = filename;                    #region 文件保存到服务器                    if (!Directory.Exists(path))                    {                        Directory.CreateDirectory(path);                    }                    file.SaveAs(savePath);                    #endregion                }                #endregion                #region 将导入的Excel转换成Table   出现问题：不同IIS会出现版本问题，这里注释改成下面的兼容性                //string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";" + "Extended Properties='Excel 12.0;IMEX=1'";                //OleDbConnection conn = new OleDbConnection(strConn);                //conn.Open();                //OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$];", strConn);                //DataSet myDataSet = new DataSet();                //myCommand.Fill(myDataSet, "Sheet1$");                //myCommand.Dispose();                //conn.Close();                //conn.Dispose();                ////将数据表转换成datatable                //System.Data.DataTable table = myDataSet.Tables["Sheet1$"].DefaultView.ToTable();                #endregion                System.Data.DataTable table = new DataTable();                if (file.FileName.Substring(file.FileName.LastIndexOf(".") + 1).ToLower() == "xlsx")                {                    table = XlsxImport(savePath);                }                else                {                    table = XlsImport(savePath);                }                if (table != null && table.Rows.Count > 0)                {                    #region 判断模板正确与否                    var falseNum = 0;//判断模板错误的列数                    if (table.Columns.Count == 10)//判断列数是否是十个                    {                        HM_ConsumableModel models = new HM_ConsumableModel();                        //models.ConsNo = models.ConsName = models.ConsSpecification = models.ConsUnit = models.ConsPrice = models.ConsPayPrice = models.ConsSupplier;                        string[] TemplateTitle = { "耗材编号", "耗材名称", "耗材规格", "单位", "应售", "实售", "生产厂家", "拼音码", "五笔码", "自定义码" };                        string[] TemplateTitlel = { "ConsNo", "ConsName", "ConsSpecification", "ConsUnit", "ConsPrice", "ConsPayPrice", "ConsSupplier", "PYCode", "WBCode", "CustomCode" };                        for (int k = 0; k < table.Columns.Count; k++)                        {                            if (TemplateTitle[k] != table.Columns[k].ColumnName.ToString().Trim())//判断标题是否相同                            {                                falseNum++;                            }                            else                            {                                table.Columns[k].ColumnName = TemplateTitlel[k];//更换标题                            }                        }                    }                    else                    {                        falseNum++;                    }                    #endregion                    #region 数据处理                    if (falseNum == 0)                    {                        #region 删除空行数据(耗材名称，耗材规格和单位都为空的时候删除此行数据)                        List arrayRows = new List();//存放要删除的行                        for (int i = 0; i < table.Rows.Count; i++)                        {                            if (table.Rows[i][1].ToString() == "" && table.Rows[i][2].ToString() == "" && table.Rows[i][3].ToString() == "")                            {                                arrayRows.Add(i);                            }                        }                        if (arrayRows.Count > 0)                        {                            for (int i = 0; i < arrayRows.Count; i++)                            {                                table.Rows.RemoveAt(i);//移除索引的行                            }                        }                        #endregion                        var tableRowsNum = table.Rows.Count;//初始行数                        table.Columns.Add("IsValid", typeof(int));//加一列：有效                        table.Columns.Add("HpId", typeof(int));//加一列：医院ID                         table.Columns.Add("EpUid", typeof(Int64)); //加一列：企业用户ID                         var hpID = Convert.ToInt32(currentLogin.HpId);                        var epuID = currentLogin.EpUid;                        //var hpID = 160;                        //var epuID = 1;                        #region 给table循环添加列和列的值                        for (int i = 0; i < tableRowsNum; i++)                        {                            table.Rows[i]["IsValid"] = 1;                            table.Rows[i]["HpId"] = hpID;                            table.Rows[i]["EpUid"] = epuID;                        }                        #endregion                        #region BulkCopy                        SqlBulkCopyByDatatable(DBConfig.CustomerManage.HM_Consumable, table);                        #endregion                        return Json(new { result = true, message = "成功导入" + tableRowsNum + "条数据！", fileFullName = fileFullName }, JsonRequestBehavior.AllowGet);                    }                    else                    {                        return Json(new { result = true, message = "模板错误请重新上传！" }, JsonRequestBehavior.AllowGet);                    }                    #endregion                }                else                {                    return Json(new { result = false, message = "模板无数据！" }, JsonRequestBehavior.AllowGet);                }            }            catch (Exception ex)            {                var values = ex.Message.ToString();                var errorMessage = "";                if (values == "列“ConsNo”不允许 DBNull.Value。")                {                    errorMessage += "导入异常，原因是：耗材编号不能有空，请检查模板数据表！";                }                else if (values == "列“ConsPrice”不允许 DBNull.Value。" || values == "列“ConsPayPrice”不允许 DBNull.Value。")                {                    errorMessage += "导入异常，原因是：应售(或实售)不能有空，请检查模板数据表！";                }                else if (values == "列“ConsSupplier”不允许 DBNull.Value。")                {                    errorMessage += "导入异常，原因是：生产厂家不能有空，请检查模板数据表！";                }                else                {                    errorMessage += values;                }                return Json(new { result = false, message = errorMessage }, JsonRequestBehavior.AllowGet);            }        }        #endregion        /// 读取excel Xlsx        /// 默认第一行为标头        /// 
        /// excel文档路径        ///         public static DataTable XlsxImport(string strFileName)        {            DataTable dt = new DataTable();            NPOI.XSSF.UserModel.XSSFWorkbook hssfworkbook;            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))            {                hssfworkbook = new NPOI.XSSF.UserModel.XSSFWorkbook(file);            }            var sheet = hssfworkbook.GetSheetAt(0);            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();            var headerRow = sheet.GetRow(0);            int cellCount = headerRow.LastCellNum;            for (int j = 0; j < cellCount; j++)            {                var cell = headerRow.GetCell(j);                dt.Columns.Add(cell.ToString());            }            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)            {                var row = sheet.GetRow(i);                DataRow dataRow = dt.NewRow();                for (int j = row.FirstCellNum; j < cellCount; j++)                {                    if (row.GetCell(j) != null)                        dataRow[j] = row.GetCell(j).ToString();                }                dt.Rows.Add(dataRow);            }            return dt;        }        /// 读取excel Xls        /// 默认第一行为标头        /// 
        /// excel文档路径        ///         public static DataTable XlsImport(string strFileName)        {            DataTable dt = new DataTable();            HSSFWorkbook hssfworkbook;            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))            {                hssfworkbook = new HSSFWorkbook(file);            }            var sheet = hssfworkbook.GetSheetAt(0);            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();            var headerRow = sheet.GetRow(0);            int cellCount = headerRow.LastCellNum;            for (int j = 0; j < cellCount; j++)            {                var cell = headerRow.GetCell(j);                dt.Columns.Add(cell.ToString());            }            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)            {                var row = sheet.GetRow(i);                DataRow dataRow = dt.NewRow();                for (int j = row.FirstCellNum; j < cellCount; j++)                {                    if (row.GetCell(j) != null)                        dataRow[j] = row.GetCell(j).ToString();                }                dt.Rows.Add(dataRow);            }            return dt;        }        ///         /// SqlBulkCopy批量导入数据 Attention:SqlBulkCopy中列的名称受大小写敏感限制，因此在构造DataTable的时候应请注意列名要与表一致。        /// 
        /// 数据库目标表        /// 源数据        private void SqlBulkCopyByDatatable(string TableName, System.Data.DataTable dt)        {            using (var dbConnection = SqlDbHelper.CreateConnection())            {                var connectionString = ConfigurationManager.ConnectionStrings["SqlConnString"].ConnectionString;                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))                {                    try                    {                        sqlbulkcopy.DestinationTableName = TableName;                        for (int i = 0; i < dt.Columns.Count; i++)                        {                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);                        }                        sqlbulkcopy.WriteToServer(dt);                    }                    catch (System.Exception ex)                    {                        throw ex;                    }                }            }        }

这里使用了SqlBulkCopy的写入方法。注意的内容标注在region后面。

阅读全文

1 0