Пример #1
0
        /// <summary>
        /// 导入Excel数据
        /// </summary>
        /// <param name="excelData">Excel数据实体</param>
        /// <returns></returns>
        public int ImportExcelData(ExcelData excelData)
        {
            //出错的工作薄名称
            string exceptionSheetName = string.Empty;

            //出错的行号
            int exceptionRowNo = 0;

            ////出错的列号
            //int exceptionColNo = 0;

            using (TransactionScope scope = new TransactionScope())
            {
                try
                {
                    //int count = 0;
                    //try
                    //{
                    foreach (ExcelSheetData excelSheetData in excelData.excelSheetData)
                    {
                        //count += 1;
                        //HttpContext.Current.Response.Write(count + "<br/>");
                        exceptionSheetName = excelSheetData.sheetName;
                        exceptionRowNo = excelSheetData.sheetRowNo;
                        //exceptionColNo = excelSheetData.sheetColNo;

                        SqlHelper.ExecuteNonQuery(conn, excelSheetData.spName, excelSheetData.sqlParammeters);

                        //执行插入操作
                        //SqlHelper.ExecuteNonQuery(conn, excelSheetData.spName, excelSheetData.sqlParammeters);
                    }
                    //}
                    //catch
                    //{
                    //    HttpContext.Current.Response.Write(count);
                    //    //HttpContext.Current.Response.End();
                    //}

                    //HttpContext.Current.Response.End();
                    //获取当前学期标识
                    string termTag = DalCommon.GetTermTag(conn);

                    //HttpContext.Current.Response.Write(termTag.ToString());
                    //HttpContext.Current.Response.End();

                    //以下需要添加插入密码映射表操作

                    //foreach (ExcelPasswordMapping excelPasswordMapping in excelData.excelPasswordMapping)
                    //{
                    //    AddPasswordMapping(new PasswordMapping
                    //    {
                    //        userName = excelPasswordMapping.listPasswordMappingUserName[0],
                    //        userNo = excelPasswordMapping.listPasswordMappingUserNo[0],
                    //        initializePassword = excelPasswordMapping.listPasswordMappingInitializePassword[0],
                    //        //此处暂时添加为0
                    //        termTag = termTag
                    //    });
                    //}
                    //count = 1;
                    foreach (PasswordMapping passwordMapping in excelData.excelPasswordMapping)
                    {
                        AddPasswordMapping(new PasswordMapping
                        {
                            userName = passwordMapping.userName,
                            userNo = passwordMapping.userNo,
                            initializePassword = passwordMapping.initializePassword,
                            //此处暂时添加为0
                            termTag = termTag,
                            userType = passwordMapping.userType
                        });
                    }
                    scope.Complete();

                    HttpContext.Current.Response.Write("<script type='text/javascript'>alert('导入Excel数据成功!确定后将转至系统首页');location.href='/';</script>");
                }
                catch (Exception ex)
                {
                    MongoDBLog.LogRecord(ex);
                    HttpContext.Current.Response.Write("<script type='text/javascript'>alert('导入Excel数据失败!此次操作未更改任何数据库数据,相关信息如下:\\n\\n出错的工作薄名称为:"
                    + exceptionSheetName
                    + "\\n出错的单元格行号为:" + exceptionRowNo
                        //+ "\n出错的单元格列号为:" + exceptionColNo
                    + "\\n可能的原因为:\\n1. 此单元格数据格式可能不正确,例如:单元格数据是否存在多余的空格。\\n2. 此单元格数据存在重复而发生冲突。即可能数据库中已经存在此条记录,或者在Excel文件中存在两条重复的记录。');history.go(-1);</script>");
                }
            }
            return 1;
        }
Пример #2
0
        /// <summary>
        /// 读取Excel数据并返回ExcelSheetData类型的集合给Dal进行处理
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <param name="colsCount">记录列数</param>
        /// <param name="FileUpload1"></param>
        /// <returns>返回ExcelData实体类</returns>
        public static ExcelData BllImportExcelData(string filePath, int[] colsCount, FileUpload FileUpload1)
        {
            //设定一个自定义ExcelData类型,用于返回全部的Excel数据
            ExcelData excelData = null;

            //出错的工作薄名称
            string exceptionSheetName = string.Empty;

            //出错的行号
            int exceptionRowNo = 0;

            ////出错的列号
            //int exceptionColNo = 0;

            try
            {
                List<ExcelSheetData> excelSheetDataArray = new List<ExcelSheetData>();

                List<PasswordMapping> excelPasswordMapping = new List<PasswordMapping>();

                int sheetCount = 0;

                using (FileStream file = new FileStream(filePath, FileMode.Open))
                {
                    //建立WorkBook
                    HSSFWorkbook HSSFWorkbook = new HSSFWorkbook(file);
                    //获取工作薄的数目
                    sheetCount = HSSFWorkbook.NumberOfSheets;

                    //循环存储值
                    for (int i = 0; i < sheetCount; i++)
                    {

                        HSSFSheet Sheet = HSSFWorkbook.GetSheetAt(i);

                        string sheetName = HSSFWorkbook.GetSheetName(i);

                        //设置当前的工作薄名称
                        exceptionSheetName = sheetName;

                        IEnumerator rows = Sheet.GetRowEnumerator();

                        //当前行编号
                        int currentRowNo = 0;

                        while (rows.MoveNext())
                        {
                            if (currentRowNo > 0)
                            {
                                exceptionRowNo = currentRowNo;

                                HSSFRow row = (HSSFRow)rows.Current;

                                List<string> ilistTemp = new List<string>();

                                //初始密码(4位数字)
                                string initializePwd = CommonUtility.GenerateRandomPassword();

                                for (int j = 0; j < colsCount[i]; j++)
                                {
                                    //exceptionColNo = j + 1;

                                    ilistTemp.Add((row.GetCell(j) != null ? row.GetCell(j).ToString().Trim() : string.Empty));

                                    if (j == (colsCount[i] - 1))
                                    {
                                        //try
                                        //{
                                        //实例化一个初始密码映射实体类
                                        PasswordMapping passwordMapping = null;

                                        switch (sheetName)
                                        {
                                            case "Sheet1":
                                                passwordMapping = new PasswordMapping
                                                {
                                                    userNo = row.GetCell(0).ToString(),
                                                    userName = row.GetCell(1).ToString(),
                                                    initializePassword = initializePwd
                                                };
                                                passwordMapping.userType = 1;
                                                excelPasswordMapping.Add(passwordMapping);
                                                ilistTemp.Add(CommonUtility.EncodeUsingMD5(initializePwd));
                                                break;
                                            case "Sheet2":
                                                passwordMapping = new PasswordMapping
                                                {
                                                    userNo = row.GetCell(0).ToString(),
                                                    userName = row.GetCell(1).ToString(),
                                                    initializePassword = initializePwd
                                                };
                                                passwordMapping.userType = 2;
                                                excelPasswordMapping.Add(passwordMapping);
                                                ilistTemp.Add(CommonUtility.EncodeUsingMD5(initializePwd));
                                                break;
                                            case "Sheet3":
                                                passwordMapping = new PasswordMapping
                                                {
                                                    userNo = row.GetCell(0).ToString(),
                                                    userName = row.GetCell(1).ToString(),
                                                    initializePassword = initializePwd
                                                };
                                                passwordMapping.userType = 3;
                                                excelPasswordMapping.Add(passwordMapping);
                                                ilistTemp.Add(CommonUtility.EncodeUsingMD5(initializePwd));
                                                break;
                                            default:
                                                break;
                                        }
                                    }
                                }
                                //将要执行的SQL信息添加到ExcelSheetData数据中

                                ExcelSheetData excelSheetDataTemp = ReturnSqlJudgeBySheetName(sheetName, ReturnModelDataJudgeBySheetName(sheetName, ilistTemp));

                                excelSheetDataTemp.sheetName = exceptionSheetName;
                                excelSheetDataTemp.sheetRowNo = exceptionRowNo;

                                excelSheetDataArray.Add(excelSheetDataTemp);
                            }

                            //行编号值加1
                            currentRowNo += 1;
                        }
                    }
                    excelData = new ExcelData { excelSheetData = excelSheetDataArray, excelPasswordMapping = excelPasswordMapping };
                }
            }
            catch (Exception ex)
            {
                MongoDBLog.LogRecord(ex);
                HttpContext.Current.Response.Write("<script type='text/javascript'>alert('很抱歉,读取Excel文件数据失败!此次操作未更改任何数据库数据,相关信息如下:\\n\\n出错的工作薄名称为:"
                    + exceptionSheetName
                    + "\\n出错的单元格行号为:" + exceptionRowNo
                    //+ "\n出错的单元格列号为:" + exceptionColNo
                    + "\\n可能的原因为:\\n 此单元格数据格式可能不正确,例如:单元格数据是否存在多余的空格。"
                    + "请检查Excel文件数据,修改后重新上传!');history.go(-1);</script>");
            }
            finally
            {
                if (File.Exists(HttpContext.Current.Server.MapPath("/" + FileUpload1.FileName)))
                {
                    File.Delete(HttpContext.Current.Server.MapPath("/" + FileUpload1.FileName));
                }
            }

            return excelData;
        }