四导入

来源：互联网发布：淘宝点卡退款编辑：程序博客网时间：2024/04/30 01:09

导入

（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="导出" onclick="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=0if(@type='ForDownLoad')beginInsert into Person(Name,Age,Gender)VALUES(@Name,@Age,@Gender)End

只供学习参考！

0 0

四 导入

导入

四导入