四导入

最新推荐文章于 2022-03-28 10:09:16 发布

爱夏-程序猿

最新推荐文章于 2022-03-28 10:09:16 发布

阅读量632

点赞数

分类专栏：技术解析文章标签：导入

本文链接：https://blog.csdn.net/GuXAiXia/article/details/46351681

版权

技术解析专栏收录该内容

5 篇文章 0 订阅

订阅专栏

导入

（1）导入一张Excel表：

（图 1）

（图 2）

实现界面的代码：

<html>
<head>
    <script src="../../Scripts/jquery-1.4.4.js" type="text/javascript"></script>
    <script src="../../Scripts/jquery-1.4.4.min.js" type="text/javascript"></script>
<script type="text/javascript">
    function daoru() {

        window.location.href = "/Main/DownloadFile";
    }

</script>
</head>
<body>
<input type="button" value="导出" οnclick="daoru()"/>
 <a href="/Main/ExportExcel">Click here</a>

<form action="/Main/GetTableFromExcel" method="post"  enctype="multipart/form-data">
   //调用控制器的方法                    
                        <text>选择上传文件</text>
                        <input name="file1" type="file" id="file" />
                        <input type="submit" name="Upload" value="导入" />
                      
                        </form>
                        </body>
</html>

调用控制器的方法：

   #endregion
        //导入
        public ActionResult GetTableFromExcel()
        {
            HttpPostedFileBase fostFile = Request.Files["file1"];

            Stream streamfile = fostFile.InputStream;
          

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);
            using (NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0))
            {
                DataTable table = new DataTable();
                IRow headerRow = sheet.GetRow(0);//第一行为标题行
                int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
                int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1

                //handling header.
                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);
                }
                for (int i = 0; i < table.Rows.Count; i++) {

                    myUpLoadBLL.ForDownLoad(table.Rows[i][1].ToString(), table.Rows[i][2].ToString(),Convert.ToBoolean( table.Rows[i][3]));
//调用逻辑层的方法
                }
            }

         

            return Content("");
        }
        /// <summary>
        /// 根据Excel列类型获取列的值
        /// </summary>
        /// <param name="cell">Excel列</param>
        /// <returns></returns>
        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();
                    }
            }
        }

逻辑层的方法：

  #region 导入

        public int ForDownLoad(string Name,string Age,bool Gender)
        {
            //创建数组
            SqlParameter[] sql ={
                                   //实例化并传入参数和参数类型
                                   new SqlParameter("@type",SqlDbType.Char),
                                   new SqlParameter("@Name",SqlDbType.NVarChar),
                                   new SqlParameter("@Age",SqlDbType.NVarChar),
                                   new SqlParameter("@Gender",SqlDbType.Bit),
                               };
            //为参数赋值
            sql[0].Value = "ForDownLoad";
            sql[1].Value = Name;
            sql[2].Value = Age;
            sql[3].Value = Gender;

            //调用DAL方法连接数据库提取数据存放到数据表dt中
            int count = myPublicMoth.UpdateData("ForUpLoadAndDownLoad", sql);
            //返回dt
            return count;
        }
        #endregion

数据层的SQL语句：

@type char(100)='',
@Name varchar(50)='',
@Age varchar(10)='',
@Gender bit=0
if(@type='ForDownLoad')
	begin
	Insert into Person(Name,Age,Gender)
	VALUES(@Name,@Age,@Gender)
	End

只供学习参考！