Excel 导入方法

       private DataTable ExportToDataTableForMeas(ISheet sheet,int row)
        {
            DataTable dt = new DataTable();

            //默认，第一行是字段
            IRow headRow = sheet.GetRow(row-1);

            //设置datatable字段
            for ( int i = headRow.FirstCellNum , len = headRow.LastCellNum; i < len; i++)
            {
                dt.Columns.Add(headRow.Cells[i].StringCellValue);
            }
            //遍历数据行
            for (int i = (row), len = sheet.LastRowNum + 1; i < len; i++)
            {
                IRow tempRow = sheet.GetRow(i);
                if (tempRow == null) break;
                DataRow dataRow = dt.NewRow();
                int intNullCell = 0;

                //遍历一行的每一个单元格
                for (int r = 0, j = 0, len2 = tempRow.LastCellNum; j < len2 && j < headRow.LastCellNum; j++, r++)
                {

                    ICell cell = tempRow.GetCell(j);

                    if (cell != null && !cell.Equals(string.Empty))
                    {
                        switch (cell.CellType)
                        {
                            case CellType.String:
                                dataRow[r] = cell.StringCellValue;
                                break;
                            case CellType.Numeric:
                                //modify by zhangwy 该方法会将数字误认为日期，改为使用IsCellDateFormatted(cell)
                                //if (DateUtil.IsValidExcelDate(cell.NumericCellValue))
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    dataRow[r] = cell.DateCellValue;
                                }
                                else
                                {
                                    dataRow[r] = cell.NumericCellValue;
                                }
                                break;
                            case CellType.Boolean:
                                dataRow[r] = cell.BooleanCellValue;
                                break;
                            case CellType.Formula:
                                try
                                {
                                    dataRow[r] = cell.StringCellValue;
                                    break;
                                }
                                catch (Exception)
                                { 
                                    dataRow[r] = cell.NumericCellValue;
                                    break;
                                }
                                
                                
                            default: dataRow[r] = "";
                                break;
                        }
                    }

                    if (dataRow[r].Equals(""))
                        intNullCell++;
                }
                if (intNullCell != tempRow.LastCellNum)
                    dt.Rows.Add(dataRow);
            }
            return dt;
        }

 调用：

        public DataTable ExportExcelToDataTableForMeas(int sheetIndex,int row)
        {
            return ExportToDataTableForMeas(_IWorkbook.GetSheetAt(sheetIndex - 1),row);
        }

 

 

 

导入文件BLL：

 /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="strMessage">异常信息</param>
        /// <param name="period">周期参数</param>
        public void ImportData(string filePath, ref string strMessage,string period)
        {
            strMessage = "";
            int row = 0;
            ExcelHelper ehField = new ExcelHelper(filePath);
            //从第1个sheet页第5行开始
            DataTable dt = ehField.ExportExcelToDataTableForMeas(1, 1);
         
            List<O_DIS_TECH_ECONOMIC_A> list = new List<O_DIS_TECH_ECONOMIC_A>();
            if (dt != null && dt.Rows.Count > 0)
            {
                for (var i = 4; i < dt.Rows.Count; i++)
                {
                    //如果找到空行，结束循环。
                    if (dt.Rows[i][0].ToString() == "" || dt.Rows[i][0].ToString() == null)
                    {
                        break;
                    }
                    row = i + 1;
                    int count = 0;
                    O_DIS_TECH_ECONOMIC_A model = new O_DIS_TECH_ECONOMIC_A();
                    model.NAME = dt.Rows[i][0].ToString().Trim().Replace("一.", "").Replace("二.", "").Replace("三.", "").Replace("四.", "").Replace("五.", "").Replace("六.", "").Replace("七.", "").Replace("十一.", "").Replace("其中:", "").Replace("其中：", "");
                    model.UNIT = dt.Rows[i][1].ToString();
                    model.UNIT_NUMBERATOR = dt.Rows[i][2].ToString();
                    model.UNIT_DENOMIATOR = dt.Rows[i][3].ToString();
                    model.CURRENT_MONTH_VAL = dt.Rows[i][4].ToString();
                    model.CURRENT_MONTH_NUMBERATOR = dt.Rows[i][5].ToString();
                    model.CURRENT_MONTH_DENOMIATOR = dt.Rows[i][6].ToString();
                    model.CURRENT_MONTH_END_VAL = dt.Rows[i][7].ToString();
                    model.CURRENT_MONTH_END_NUMBERATOR = dt.Rows[i][8].ToString();
                    model.CURRENT_MONTH_END_DENOMIATOR = dt.Rows[i][9].ToString();
                    model.LAST_YEAR_VAL = dt.Rows[i][10].ToString();
                    model.LAST_YEAR_END_VAL = dt.Rows[i][11].ToString();
                    model.PERIOD = "月";
                    model.DAY =period;
                    //if (int.Parse(dal.IFCourseRepeat(dt.Rows[i][0].ToString())) >= 1)
                    //{

                    //}
                    //else
                    //{
                    list.Add(model);
                    count++;
                    //}
                }
                DAL.ImportData(list, ref strMessage);
            }
            else
            {
                strMessage += "Excel中SheetName错误或内容为空";//string.Format("第{0}行[器具编码]不能为空。", i + 1);
            }


        }

 

 

 

前台view：

 /// <summary>
        /// 上传文件
        /// </summary>
        /// <param name="context"></param>
        /// <param name="fileName">文件名称</param>
        /// <param name="error">错误信息</param>
        /// <returns></returns>
        string upLoadFile(HttpContext context, string fileName, out string error)
        {

            error = "";
            HttpPostedFile uploadFile = context.Request.Files["fileupload"];

            if (uploadFile.ContentLength > 20971520)
            {
                error = "文件不能超过20M.";
                return "";
            }
            //string path = "../planfile/";
            //string filePath = context.Server.MapPath(path);

            string path = new Sys_DicsBLL().GetDicCode2("SYS_FilePath");// UIHelper.GetFilePath(context);
            if (string.IsNullOrEmpty(path) == true)
            {
                error = "配置文件存放路径为空.";
                return "";
            }
            string filePath = path + "/O_DIS_TECH_ECONOMIC/";


           // string file = fileName + "." + uploadFile.FileName.Split('.')[1];
            string file = fileName + Path.GetExtension(uploadFile.FileName);
            if (!Directory.Exists(filePath))//判断文件夹是否存在 
            {
                Directory.CreateDirectory(filePath);//不存在则创建文件夹 
            }
            filePath += file;
            // 如果有文件, 则保存到一个地址
            if (uploadFile.ContentLength > 0)
            {
                uploadFile.SaveAs(filePath);
            }
            //return path + file;
            return filePath;
        }