예제 #1
0
        private void Bt_ImportOrigin_OnClick(object sender, RoutedEventArgs e)
        {
            var openFileDialog = new Microsoft.Win32.OpenFileDialog()
            {
                Filter = "Excel文件(*.xls,*.xlsx) |*.xls;*.xlsx"
            };
            var result = openFileDialog.ShowDialog();

            if (result == true)
            {
                Hint.Text = "导入中,请稍候...";
                ThreadPool.QueueUserWorkItem((w) =>
                {
                    string strError = "";

                    var Importresult = AsposeExcelTools.ExcelFileToDataTable(openFileDialog.FileName, out currentInPutDataTable, out strError);
                    if (Importresult == true)
                    {
                        Update(() => { Hint.Text = "导入成功,请执行第二步";
                                       Bt_StartCheck.IsEnabled = true; });
                    }
                    else
                    {
                        Update(() =>
                        {
                            Hint.Text = "导入失败。请重试。" + strError;
                        });
                    }
                });
            }
        }
예제 #2
0
        private static DataTable GetDtTable()
        {
            string path = HttpContext.Current.Request.MapPath("~/App_Data/检测返修报表.xlsx");
            //调用ZK的ExcelHelper
            //DataTable dtTable = ExcelHelper.ImportExceltoDt(path);
            DataTable dtTable = new DataTable();
            string    str;

            AsposeExcelTools.ExcelFileToDataTable(path, out dtTable, out str);
            return(dtTable);
        }
예제 #3
0
        /// <summary>
        /// 从附件列表中获取第一个Excel文件,并转换Excel数据为对应的DataTable返回
        /// </summary>
        /// <param name="guid">附件的Guid</param>
        /// <returns></returns>
        protected DataTable ConvertExcelFileToTable(string guid)
        {
            DataTable dt = null;

            if (!string.IsNullOrEmpty(guid))
            {
                //获取上传附件的路径
                string serverRealPath = BLLFactory <FileUpload> .Instance.GetFirstFilePath(guid);

                if (!string.IsNullOrEmpty(serverRealPath))
                {
                    //转换Excel文件到DatTable里面
                    string error = "";
                    dt = new DataTable();
                    AsposeExcelTools.ExcelFileToDataTable(serverRealPath, out dt, out error);
                }
            }
            return(dt);
        }
예제 #4
0
        /// <summary>
        /// 从Excel文件导入(角色)
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private bool DoImport(string filePath)
        {
            bool returnValue = false;

            // 鼠标忙碌状态
            this.Cursor = Cursors.WaitCursor;
            try
            {
                //将Excel表转换为DataTable
                string    error     = "";
                DataTable dataTable = new DataTable();
                AsposeExcelTools.ExcelFileToDataTable(filePath, out dataTable, out error);
                //检查Excell转为DataTable是否成功
                if (error != "")
                {
                    MessageBox.Show(error, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return(false);
                }
                //是否有记录
                if (dataTable.Rows.Count > 0)
                {
                    //将第一行数据给数据列置名称,以便于识别和调用数据。
                    int columnsCount = 0;
                    for (columnsCount = 0; columnsCount < dataTable.Columns.Count; columnsCount++)
                    {
                        dataTable.Columns[columnsCount].ColumnName = dataTable.Rows[0][columnsCount].ToString().Trim();
                    }

                    //循环处理每行数据
                    int            rowsCount  = 0;
                    int            errorCount = 0;
                    BaseRoleEntity tempentity;
                    string         statusCode    = string.Empty;
                    string         statusMessage = string.Empty;
                    DataTable      dt            = DotNetService.Instance.RoleService.GetDataTable(UserInfo);
                    for (rowsCount = 1; rowsCount < dataTable.Rows.Count; rowsCount++)
                    {
                        //角色名称不允许导入重复项
                        bool exists = BaseBusinessLogic.Exists(dt, BaseRoleEntity.FieldRealName, dataTable.Rows[rowsCount][BaseRoleEntity.FieldRealName].ToString());
                        if (!exists)
                        {
                            //清空实体
                            tempentity = new BaseRoleEntity();
                            //给实体赋值
                            tempentity.RealName          = dataTable.Rows[rowsCount][BaseRoleEntity.FieldRealName].ToString();
                            tempentity.Code              = dataTable.Rows[rowsCount][BaseRoleEntity.FieldCode].ToString();
                            tempentity.Description       = dataTable.Rows[rowsCount][BaseRoleEntity.FieldDescription].ToString();
                            tempentity.Enabled           = int.Parse(dataTable.Rows[rowsCount][BaseRoleEntity.FieldEnabled].ToString());
                            tempentity.CategoryCode      = dataTable.Rows[rowsCount][BaseRoleEntity.FieldCategoryCode].ToString();
                            tempentity.AllowDelete       = 1;
                            tempentity.AllowEdit         = 1;
                            tempentity.DeletionStateCode = 0;
                            tempentity.IsVisible         = 1;

                            DotNetService.Instance.RoleService.Add(UserInfo, tempentity, out statusCode, out statusMessage);
                        }
                        else
                        {
                            errorCount++;
                        }
                    }
                    this.Changed = true;
                    returnValue  = true;
                    MessageBox.Show("共有" + (dataTable.Rows.Count - 1) + "条记录,"
                                    + (dataTable.Rows.Count - 1 - errorCount).ToString()
                                    + "条记录被成功导入!", "提示信息",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            catch (Exception ex)
            {
                this.ProcessException(ex);
            }
            finally
            {
                // 设置鼠标默认状态
                this.Cursor = Cursors.Default;
            }
            return(returnValue);
        }
        //从Excel导入数据
        private void ImportExcel()
        {
            OpenFileDialog openDialog = new OpenFileDialog();

            openDialog.Filter           = "Execl files (*.xls)|*.xls|(*.xlsx)|*.xlsx";
            openDialog.FilterIndex      = 0;
            openDialog.RestoreDirectory = true;
            openDialog.Title            = "打开Excel文件";

            string fileName = string.Empty;//要上传的文件名

            if (openDialog.ShowDialog() == DialogResult.OK)
            {
                WinFileTransporter wFile = new WinFileTransporter();

                fileName = openDialog.FileName;
                string uriFiles   = string.Empty;//保存到服务器路径
                string folderName = Application.StartupPath + "\\importFiles";

                if (!Directory.Exists(folderName))//如果路径不存在则创建
                {
                    Directory.CreateDirectory(folderName);
                }

                try
                {
                    if (wFile.UpLoadFile(fileName, folderName, out uriFiles, true))
                    {
                        DataTable dt       = new DataTable();
                        string    errorStr = string.Empty;
                        AsposeExcelTools.ExcelFileToDataTable(uriFiles, out dt, out errorStr);//读取表格数据,转换为datatable
                        List <DATestmap> errorList   = new List <DATestmap>();
                        List <DATestmap> testmapList = new List <DATestmap>();
                        if (dt != null && dt.Rows.Count > 0)
                        {
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                DATestmap testmap = new DATestmap();
                                testmap.Customertestcode = dt.Rows[i]["医院项目代码(项目导入唯一标识)"].ToString().Trim();
                                testmap.Customertestname = dt.Rows[i]["医院项目名称(作为对应参考,不做导入标识)"].ToString().Trim();
                                testmap.Datestcode       = dt.Rows[i]["达安项目代码(项目导入唯一标识)"].ToString().Trim();
                                testmap.Datestname       = dt.Rows[i]["达安项目名称(作为对应参考,不做导入标识)"].ToString().Trim();
                                if (testmap.Customertestcode == string.Empty)
                                {
                                    errorList.Add(testmap);
                                    errorStr += "导入错误:医院项目代码为空,";
                                    continue;
                                }
                                if (testmap.Datestcode == string.Empty)
                                {
                                    errorList.Add(testmap);
                                    errorStr += "导入错误:达安项目代码为空,";
                                    continue;
                                }
                                if (testmap.Customertestname == string.Empty)
                                {
                                    errorList.Add(testmap);
                                    errorStr += "导入错误:医院项目名称为空,";
                                    continue;
                                }
                                if (testmap.Datestname == string.Empty)
                                {
                                    errorList.Add(testmap);
                                    errorStr += "导入错误:达安项目名称为空,";
                                    continue;
                                }
                                if (!IsExistHosp(testmap.Customertestcode, testmap.Customertestname))
                                {
                                    errorList.Add(testmap);
                                    errorStr += "导入错误:医院项目信息不存在,";
                                    continue;
                                }
                                if (!IsExistDaan(testmap.Datestcode, testmap.Datestname))
                                {
                                    errorList.Add(testmap);
                                    errorStr += "导入错误:达安项目信息不存在,";
                                    continue;
                                }
                                //如果在testmap表已经存在医院项目代码的记录,记录对象及错误信息
                                //if (IsExist(testmap.Customertestcode))
                                //{
                                //    errorList.Add(testmap);
                                //    errorStr += "导入失败:该医院项目已存在对应的匹配信息,";
                                //    continue;
                                //}
                                errorList.Add(testmap);
                                errorStr += "导入成功,";

                                testmapList.Add(testmap);
                            }
                            //if (testmapBll.ImportDaTestmap(testmapList))
                            if (testmapBll.SaveDATestmapList(testmapList))
                            {
                                //弹出错误信息
                                //if (errorList != null)
                                //{
                                FrmItemHoapitalAndDaanImportError errorFrm = new FrmItemHoapitalAndDaanImportError();
                                errorFrm.StrFailed     = errorStr;
                                errorFrm.testmapList   = errorList;
                                errorFrm.StartPosition = FormStartPosition.CenterScreen;
                                errorFrm.ShowDialog();
                                BindData();
                                //}
                                //else
                                //{
                                //    ShowMessageHelper.ShowBoxMsg("导入成功!");
                                //}
                            }
                            else
                            {
                                ShowMessageHelper.ShowBoxMsg("导入失败!");
                            }
                        }
                        else
                        {
                            ShowMessageHelper.ShowBoxMsg("没有数据导入!");
                        }
                    }
                }
                catch (Exception e)
                {
                    ShowMessageHelper.ShowBoxMsg("导入的过程中发生了异常:" + e.Message);
                }

                finally
                {
                    //删除文件
                    if (File.Exists(uriFiles))
                    {
                        File.Delete(uriFiles);
                    }
                }
            }
        }
예제 #6
0
        public ActionResult Import(string guid)
        {
            CommonResult result = new CommonResult();

            result.Success = false;
            try
            {
                var attach = BLLFactory <FileUpload> .Instance.GetByAttachGUID(guid).FirstOrDefault();

                if (attach != null)
                {
                    var    filename = Server.MapPath("~\\" + attach.BasePath + "\\" + attach.SavePath);
                    var    dt       = new System.Data.DataTable();
                    string err      = null;

                    if (AsposeExcelTools.ExcelFileToDataTable(filename, out dt, out err))
                    {
                        //清空中间表
                        BLLFactory <Core.BLL.MidConcentrator> .Instance.DeleteByCondition("1=1");

                        BLLFactory <Core.BLL.MidCustomerMeter> .Instance.DeleteByCondition("1=1");

                        //采集器信息
                        var entity      = new Core.Entity.MidConcentrator();
                        var row         = dt.Rows[2];
                        var nvcName     = row[0].ToString();
                        var nvcAddr     = row[1].ToString();
                        var vcAddr      = row[2].ToString();
                        var intProtocol = row[3].ToString().ToInt();
                        var intCount    = row[4].ToString().ToInt();
                        var intCommMode = row[5].ToString().ToInt();
                        var intCOM      = row[6].ToString().ToInt();
                        var vcParam     = row[7].ToString();
                        var vcSimNo     = row[8].ToString();
                        var intStatus   = row[9].ToString().ToInt();
                        var clientIP    = "127.0.0.1";

                        entity.VcClientIP  = clientIP;
                        entity.IntCOM      = intCOM;
                        entity.IntCommMode = intCommMode;
                        entity.IntCount    = intCount;
                        entity.IntProtocol = intProtocol;
                        entity.IntStatus   = intStatus;
                        entity.NvcAddr     = nvcAddr;
                        entity.NvcName     = nvcName;
                        entity.VcAddr      = vcAddr;
                        entity.VcParam     = vcParam;
                        entity.VcSimNo     = vcSimNo;

                        //DbTransaction dbTransaction = BLLFactory<Core.BLL.MidConcentrator>.Instance.CreateTransaction();
                        BLLFactory <Core.BLL.MidConcentrator> .Instance.Insert(entity);

                        //dbTransaction.Commit();

                        //客户及表信息
                        for (int i = 5; i < dt.Rows.Count; i++)
                        {
                            var item          = dt.Rows[i];
                            var IntCustCode   = item[0].ToString().ToInt();
                            var NvcName       = item[1].ToString();
                            var NvcAddr       = item[2].ToString();
                            var VcTelNo       = item[3].ToString();
                            var VcMobile      = item[4].ToString();
                            var VcIDNo        = item[5].ToString();
                            var IntPriceNo    = item[6].ToString().ToInt();
                            var VcCustType    = item[7].ToString();
                            var IntStatusUser = item[8].ToString().ToInt();
                            var DteOpen       = Convert.ToDateTime(item[9].ToString());
                            var NvcInvName    = item[10].ToString();
                            var NvcInvAddr    = item[11].ToString();
                            var NvcVillage    = item[12].ToString();
                            var VcBuilding    = item[13].ToString();
                            var IntUnitNum    = item[14].ToString().ToInt();
                            var IntRoomNum    = item[15].ToString().ToInt();
                            var IntNumber     = item[16].ToString().ToInt();
                            var VcAddr        = item[17].ToString();
                            var NvcAddrIns    = item[18].ToString();
                            var VcAssetNo     = item[19].ToString();
                            var VcBarCode     = item[20].ToString();
                            var IntOrig       = item[21].ToString().ToInt();
                            var IntChannal    = item[22].ToString().ToInt();
                            var IntProtocol   = item[23].ToString().ToInt();
                            var DtCreate      = Convert.ToDateTime(item[24].ToString());
                            var IntCycle      = item[25].ToString().ToInt();
                            var IntState      = item[26].ToString().ToInt();

                            var mMeter = new Core.Entity.MidCustomerMeter();
                            mMeter.DtCreate    = DateTime.Now;
                            mMeter.DteOpen     = DateTime.Now;
                            mMeter.IntChannal  = IntChannal;
                            mMeter.IntCustCode = IntCustCode;
                            mMeter.IntCycle    = IntCycle;
                            mMeter.IntNumber   = IntNumber;
                            mMeter.IntOrig     = IntOrig;
                            mMeter.IntPriceNo  = IntPriceNo;
                            mMeter.IntProtocol = IntProtocol;
                            mMeter.IntRoomNum  = IntRoomNum;
                            mMeter.IntState    = IntState;
                            mMeter.IntStatus   = IntStatusUser;
                            mMeter.IntUnitNum  = IntUnitNum;
                            //mMeter.IntUserID=
                            mMeter.NvcAddr     = NvcAddr;
                            mMeter.NvcAddrIns  = NvcAddrIns;
                            mMeter.NvcCustType = VcCustType;
                            mMeter.NvcInvAddr  = NvcInvAddr;
                            mMeter.NvcInvName  = NvcInvName;
                            mMeter.NvcName     = NvcName;
                            mMeter.NvcVillage  = NvcVillage;
                            mMeter.VcAddr      = VcAddr;
                            mMeter.VcAssetNo   = VcAssetNo;
                            mMeter.VcBarCode   = VcBarCode;
                            mMeter.VcBuilding  = VcBuilding;
                            mMeter.VcClientIP  = clientIP;
                            //mMeter.VcContractNo=
                            mMeter.VcIDNo   = VcIDNo;
                            mMeter.VcMobile = VcMobile;
                            mMeter.VcTelNo  = VcTelNo;

                            BLLFactory <Core.BLL.MidCustomerMeter> .Instance.Insert(mMeter);

                            //BLLFactory<Core.BLL.MidCustomerMeter>.Instance.Insert(mMeter, dbTransaction);
                        }

                        //dbTransaction.Commit();

                        //@sClientIP VARCHAR(64), --< !--客户端IP-- >
                        //@iConcFlag  INTEGER, --< !--采集器标志0:如果已存在相同采集器,则提示错误,终止导入档案信息 1:如果已存在同地址采集器,将客户与表信息导入,表挂接在同地址采集器下-- >
                        //@iMeterFlag INTEGER,    --< !--一户多表标志0:一户一表,MidCustomerMeter.IntCustCode唯一 1:一户多表,MidCustomerMeter.IntCustCode相同的记录认为是同一客户-- >
                        List <SqlParameter> param = new List <SqlParameter>();
                        param.Add(new SqlParameter("@sClientIP", SqlDbType.VarChar, 64)
                        {
                            Value = clientIP
                        });
                        param.Add(new SqlParameter("@iConcFlag", SqlDbType.VarChar, 64)
                        {
                            Value = 1
                        });
                        param.Add(new SqlParameter("@iMeterFlag", SqlDbType.VarChar, 64)
                        {
                            Value = 1
                        });
                        param.Add(new SqlParameter("@sReturn", SqlDbType.VarChar, 256)
                        {
                            Direction = ParameterDirection.Output
                        });
                        //param[0].Value = clientIP;
                        //param[1].Value = 1;
                        //param[2].Value = 1;
                        //param[3].Direction = ParameterDirection.Output;

                        BLLFactory <Core.BLL.ArcConcentratorInfo> .Instance.ExecStoreProc("up_ImportArchive", param);

                        if (param[3].Value.ToString() == "0")
                        {
                            result.Success = true;
                        }
                        else
                        {
                            result.ErrorMessage = "执行up_ImportArchive存储过程出错!错误如下:" + param[3].Value.ToString();
                        }
                    }
                    else
                    {
                        result.ErrorMessage = err;
                    }
                }
            }
            catch (Exception ex)
            {
                result.ErrorMessage = ex.Message;
            }
            return(ToJsonContent(result));
        }