asp.net 实现excel文件下载、导入控件

首先说说需求：从A系统中对接数据到B系统，本来是想用JSON传输，但是数据过大不好处理，所以考虑用文件流接收。刚好现在系统中有文件导入控件，可以把二者综合起来利用，省去一些时间成本。思路：系统A把需要对接的数据生成为excel文件并向B系统发起请求（把文件的http下载路径给B），B系统从A系统中下载需要导入的文件到指定的目录，然后调用系统中的导入文件控件将文件内容导入到B系统数据库中，最终B系统将数据导入结果反馈回A系统。

详细步骤（只针对数据接收方）

1. 下载文件

     

        //文件下载保存目录
        private string _directory =  HttpContext.Current.Server.MapPath("~/UserMgr/TeachInfoMgr/DataConnetion/TempFiles/");
        //导入控件相关配置文件保存目录
        private string _directory1 = HttpContext.Current.Server.MapPath("~/UserMgr/TeachInfoMgr/DataConnetion/");
        /// <summary>
        /// 文件下载到指定路径
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        public  string DownLoadFile(string url)
        {
            try
            {
                string fileName = CreateFileName(url);
                if (!Directory.Exists(_directory))
                {
                    Directory.CreateDirectory(_directory);
                }
                WebClient client = new WebClient();
                client.DownloadFile(url, _directory + fileName);
                return fileName;
            }
            catch (Exception e)
            {
                return "";
            }
        }
        /// <summary>
        /// <summary>
        /// 创建文件名称
        /// </summary>
        public static string CreateFileName(string url)
        {
            string fileName = "";
            string fileExt = url.Substring(url.LastIndexOf(".")).Trim().ToLower();
            Random rnd = new Random();
            fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + rnd.Next(10, 99).ToString() + fileExt;
            return fileName;
        }

     2. 校验文件

主要是对文件的格式、大小进行限制：

/// <summary>
        /// 导入文件校验
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
public string FileChek(string FileName)
        {
            try
            {
                //获取所有文件
                var files = Directory.GetFiles(_directory);
                DirectoryInfo folder = new DirectoryInfo(_directory);
                string extension = Path.GetExtension(FileName);
                if (!extension.Equals(".xls") && !extension.Equals(".xlsx"))
                {
                    return "{\"error\":1,\"msg\":\"上传失败！请上传后缀名为xlsx或xls的Excel文件！\"}";
                }
                foreach (FileInfo file in folder.GetFiles())
                {
                    if (file.Name == FileName)
                    {
                        if (file.Length > 4194304)
                        {
                            return "{\"error\":1,\"msg\":\"上传失败！文件大小不能超过4M！\"}";
                        }
                        else
                        {
                            int emptyRow = 0;
                            string filePath = _directory + FileName;
                            if (ExcelBll.RowsTrim(filePath, out emptyRow))
                            {
                                int rowNum = 0;
                                FileStream streamRead = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                                List<ICell> list = ExcelBll.GetFirstRowCells(streamRead, out rowNum);
                                if (rowNum < 3)
                                {
                                    CommonHelper.DeleteFile(filePath);
                                    return  "{\"error\":1,\"msg\":\"上传失败！Excel文件行数小于3行视为无效文件！\"}";
                                }
                                else
                                {
                                    List<ColumnModel> models = XmlBll.GetColumnModels("courseclass",
                                       _directory1 + "Columns.xml");
                                    string sb = UploadBll.GetColMatchJson(models, list);
                                    //TODO:正则校验
                                    return "{ \"error\":0, \"msg\":" + sb + ",\"filename\":\"" + FileName + "\"}";
                                }
                            }
                            else
                            {
                                if (10 < emptyRow)
                                {
                                    return "{\"error\":99,\"msg\":\"Excel文件含有过多的空行，请删除空行后再上传。\"}";
                                }
                                else
                                {
                                    return  "{\"error\":99,\"msg\":\"程序清除Excel空行失败，请手动清除空行后再上传。\"}";
                                }

                            }
                        }
                    }
                }
                return "";
            }
            catch (Exception e)
            {
                return "";
            }
        }

 

     3. 导入请求处理

     /// <summary>
       /// 导入文件处理
       /// </summary>
       /// <param name="userID"></param>
       /// <param name="userName"></param>
       /// <param name="schoolID"></param>
       /// <param name="tbName"></param>
       /// <param name="fileName"></param>
       /// <returns></returns>
        public string HandleImport(string userID,string userName,string schoolID,string tbName,string fileName)
        {
            #region 请求参数合法性验证
            if (string.IsNullOrEmpty(userID) || string.IsNullOrEmpty(fileName))
            {
                CommonHelper.DeleteFile(_directory + fileName);
                return "{\"error\":2,\"msg\":\" 参数合法性校验失败\"}";
            }

            #endregion
            string colNames = "";
            //把参数中的对应列关系拿出来
            Dictionary<string, int> cols = new Dictionary<string, int>();
            if (tbName == "courseclass")
            {
                cols.Add("SubjectName", 0);
                cols.Add("ClassType", 1);
                cols.Add("CourseClassName", 2);
                cols.Add("GlobalGrade", 3);
                cols.Add("TeacherID", 4);
                cols.Add("StudentID", 5);
                colNames = "SubjectName,ClassType,CourseClassName,GlobalGrade,TeacherID,TeacherID";
            }
            else
            {
                cols.Add("OddOrEvenWeekName", 0);
                cols.Add("StartWeekNO", 1);
                cols.Add("EndWeekNO", 2);
                cols.Add("WeekDayName", 3);
                cols.Add("ClassHourNO", 4);
                cols.Add("ClassRoomType", 5);
                cols.Add("ClassRoomName", 6);
                cols.Add("SubjectName", 7);
                cols.Add("GlobalGrade", 8);
                cols.Add("ClassType", 9);
                cols.Add("ClassName", 10);
                cols.Add("TeacherID", 11);
                cols.Add("Type", 12);
                colNames = "OddOrEvenWeekName,StartWeekNO,EndWeekNO,WeekDayName,ClassHourNO,ClassRoomType,ClassRoomName,SubjectName,GlobalGrade,ClassType,ClassName,TeacherID,Type";
            }
            try
            {
                List<ColumnModel> models = XmlBll.GetColumnModels(tbName, _directory1+"Columns.xml");
                //需要校验重复性的列的列号
                List<int> uniqueColList = new List<int>();
                uniqueColList.AddRange(
                    models.Where(t => cols.ContainsKey(t.ColName) && t.IsUnique).Select(m => cols[m.ColName]));
                //正则序号列表
                List<string> idList = models.Where(t => cols.ContainsKey(t.ColName)).Select(m => m.RegExpStr).ToList();
                //正则对象列表
                List<Regex> regexes =
                    XmlBll.GetRegexes(_directory1+"RegExp.xml", idList).Select(m => new Regex(m)).ToList();
                ISheet sheet = ExcelBll.GetSheet(_directory + fileName);

                //唯一性校验
                List<Dictionary<string, string>> uniqueErrList = CheckBll.CheckExcelColumnUnique(uniqueColList, sheet, cols);
                List<Dictionary<string, string>> importErrList; //导入出错的
                int overlap = 0;//正则与唯一性校验重叠的行数
                int successNum = 0;
                int repCount = 0;
                //正则校验
                List<Dictionary<string, string>> regErrList = CheckBll.CheckExcelContent(cols, sheet, regexes, uniqueErrList, out importErrList, out overlap, out successNum,
                    userID, userName, schoolID, tbName, _directory1+ "Columns.xml", out repCount);
                int totalRowNum = sheet.LastRowNum + 1 - 2;// 有效数据行=最后行号 + 1（行号从0计算） - 2（表头两行）； 
                int errNum = totalRowNum - successNum; //错误的条数
                if (errNum == 0)
                {
                    //没有错误
                    return "{\"error\":0,\"msg\":\" 全部导入成功\"}";
                }
                else
                {
                    string resourceName = _directory  + fileName;
                    string outputName = _directory  + "out_" + fileName.Substring(0, fileName.LastIndexOf('.')) + ".xls";

                    string uri = "~/UserMgr/Comm/Controls/Import/TempFiles/out_" + fileName.Substring(0, fileName.LastIndexOf('.')) + ".xls";
                    uri = HttpContext.Current.Request.Url.Scheme + "://" + HttpContext.Current.Request.Url.Host + ":" + HttpContext.Current.Request.Url.Port + "/UserMgr/Comm/Controls/Import/DownloadFile.ashx?fileUrl=" + Uri.EscapeDataString(uri);

                    bool success = ExcelBll.CreateErrorExcel(resourceName, outputName, cols, colNames, uniqueErrList, regErrList, importErrList, regexes, models);

                    return  "{ \"error\":1,\"msg\":\"" + uri + "\"}";
                    //context.Response.Write(jsonStr);
                }
            }
            catch (Exception ex)
            {
                //读取文件等异常，说明服务器处理发生问题
                return "{\"error\":2,\"msg\":\" 读取文件异常\"}";
            }
            finally
            {
                CommonHelper.DeleteFile(_directory+ fileName);
            }
        }

     4. 响应

        导入结果分为：全部成功、部分成功、全部失败，部分成功和全部失败将生成失败列表文件并将 下载地址回馈回A系统，A系统可下载并查看哪些是导入失败及其失败的原因。

    5. 导入控件Web端的配置文件有如下这些

其中，columns.xml 是用来匹配导入文件中的字段格式的，可以重复利用，即需要导入哪些数据都可以自由配置。RegExp.xml是用来正则校验的配置文件。当然后台处理导入程序也要跟上哦。Web端及类库完整代码可留言。