private void TeamFile_UploadPlanningArea() { if (!_ConnectionOK) { return; } try { //Validate("DuplicateCombo"); Validate("DuplicateCombo"); Validate("InvalidRow"); if (!ValidationResult()) { return; }; ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService(); excelToSql.ExcelApplictaion = this.ExcelApplication; excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); //runtime parameterek létrehozása MyParameters myparams = GetMyParameters(); parameters.RuntimeParameters = myparams; var statements = _SqlSatementModule.GetSqlStatementList(); var query = CreateUploadAreaList(); excelToSql.CommandRunner<UploadAreaClass>(parameters, statements, "TeamFile_UploadPlanningArea", _tblTeamFileTempDefinition, query, null); System.Windows.Forms.MessageBox.Show("Done!"); rngp_LastUpload.SetValue(DateTime.Now); //rngp_PlanningAreaChanged.SetValue("FALSE"); } catch (Exception ex) { ExceptionHandler.HandleException(ex); } }
private void DownloadMasterData() { ExcelToSql.ExcelToSqlService _excelToSql = new ExcelToSql.ExcelToSqlService(); _excelToSql.ExcelApplictaion = this.ExcelApplication; _excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); try { MyParameters myparams = GetMyParameters(); parameters.RuntimeParameters = myparams; if (_SqlSatementModule.TestQuery(ref _excelToSql)) { _ConnectionOK = true; _SqlSatementModule.GetSqlStatements(ref _excelToSql); var statements = _SqlSatementModule.GetSqlStatementList(); _excelToSql.CommandRunner<object>(parameters, statements, "TeamFile_DownloadMasterData", null, null); } else { _ConnectionOK = false; } } catch (Exception ex) { _ConnectionOK = false; ExceptionHandler.HandleException(ex); } }
private void TeamFile_DownloadPlanningArea() { if (!_ConnectionOK) { return; } ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService(); excelToSql.ExcelApplictaion = this.ExcelApplication; try { excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); MyParameters myparams = GetMyParameters(); parameters.RuntimeParameters = myparams; var statements = _SqlSatementModule.GetSqlStatementList(); this.ExcelApplication.ScreenUpdating = false; this.ExcelApplication.EnableEvents = false; var query = CreateUploadAreaList(); excelToSql.CommandRunner<UploadAreaClass>(parameters, statements, "TeamFile_DownloadPlanningArea",_tblTeamFileTempDefinition,query,null); //ClearCellsWithTypeWhereColumnEmpty(rngcl_PlanningAreaFixed.GetRange(),InteropExcel.XlCellType.xlCellTypeConstants, 2); rngp_LastDownload.SetValue(DateTime.Now); //rngp_PlanningAreaChanged.SetValue("FALSE"); } catch (Exception ex) { ExceptionHandler.HandleException(ex); } finally { this.ExcelApplication.ScreenUpdating = true; this.ExcelApplication.EnableEvents = true; } }
private void DownloadSummaryReport() { if (!_ConnectionOK) { return; } ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService(); excelToSql.ExcelApplictaion = this.ExcelApplication; this.ExcelApplication.ScreenUpdating = false; this.ExcelApplication.EnableEvents = false; this.ExcelApplication.DisplayAlerts = false; try { excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); MyParameters myparams = GetMyParameters(); parameters.RuntimeParameters = myparams; this.ExcelApplication.TryGetSheet("TeamApproval").Delete(); CreateModuleSheets(this.ActiveWorkbook); InitModuleObjectRanges(this.ActiveWorkbook, true); var statements = _SqlSatementModule.GetSqlStatementList(); excelToSql.CommandRunner<object>(parameters, statements, "SummaryFile_DownloadSummaryReport", null, null); FormatReport(); } catch (Exception ex) { ExceptionHandler.HandleException(ex); } finally { this.ExcelApplication.ScreenUpdating = true; this.ExcelApplication.EnableEvents = true; this.ExcelApplication.DisplayAlerts = true; } }
private void UploadSummaryArea() { if (!_ConnectionOK) { return; } ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService(); excelToSql.ExcelApplictaion = this.ExcelApplication; try { excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); var statements = _SqlSatementModule.GetSqlStatementList(); excelToSql.CommandRunner<object>(parameters, statements, "SummaryFile_UploadSummaryArea", _tblSummaryFileTempDefinition, null); System.Windows.Forms.MessageBox.Show("Done!"); } catch (Exception ex) { ExceptionHandler.HandleException(ex); } finally { } }
private void DownloadMasterData() { ExcelToSql.ExcelToSqlService _excelToSql = new ExcelToSql.ExcelToSqlService(); _excelToSql.ExcelApplictaion = this.ExcelApplication; _excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); try { if (_SqlSatementModule.TestQuery(ref _excelToSql)) { _ConnectionOK = true; _SqlSatementModule.GetSqlStatements(ref _excelToSql); } else { _ConnectionOK = false; } } catch (Exception ex) { _ConnectionOK = false; ExceptionHandler.HandleException(ex); } }
private void CurrentTeamFile_Upload() { if (!_ConnectionOK) { return; } ExcelToSql.ExcelToSqlService excelToSql = new ExcelToSql.ExcelToSqlService(); excelToSql.ExcelApplictaion = this.ExcelApplication; try { InteropExcel.Worksheet ws = ActiveWorkbook.TryGetSheet("CurrentTeam"); ws.Activate(); excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); var statements = _SqlSatementModule.GetSqlStatementList(); this.ExcelApplication.ScreenUpdating = false; this.ExcelApplication.EnableEvents = false; var query = CreateUploadAreaList(); if (query.Count > 0) { excelToSql.CommandRunner<CurrentTeam>(parameters, statements, "CurrentTeamFile_Upload", _tblCurrentTeam, query, null); rngp_UploadArea.GetRange().ClearContents(); } System.Windows.Forms.MessageBox.Show("Done!"); } catch (Exception ex) { ExceptionHandler.HandleException(ex); } finally { this.ExcelApplication.ScreenUpdating = true; this.ExcelApplication.EnableEvents = true; } }
private void CurrentTeamFile_DownloadReport() { if (!_ConnectionOK) { return; } ExcelToSql.ExcelToSqlService _excelToSql = new ExcelToSql.ExcelToSqlService(); _excelToSql.ExcelApplictaion = this.ExcelApplication; _excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); ExcelToSql.SqlProcessParameters parameters = new ExcelToSql.SqlProcessParameters(); try { InteropExcel.Worksheet ws = ActiveWorkbook.TryGetSheet("Report"); ws.Activate(); var statements = _SqlSatementModule.GetSqlStatementList(); this.ExcelApplication.ScreenUpdating = false; this.ExcelApplication.EnableEvents = false; _excelToSql.CommandRunner<object>(parameters, statements, "CurrentTeamFile_DownloadReport", null, null); } catch (Exception ex) { ExceptionHandler.HandleException(ex); } finally { this.ExcelApplication.ScreenUpdating = true; this.ExcelApplication.EnableEvents = true; } }
//gombhoz esemény rendelése void testButton_Click(object sender, RibbonControlEventArgs e) { ExcelToSql.ExcelToSqlService _excelToSql = new ExcelToSql.ExcelToSqlService(); _excelToSql.ExcelApplictaion = this.ExcelApplication; _excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); SqlProcessParameters sqlparam = new SqlProcessParameters(); sqlparam.SqlStatement.SqlScript = "Select * from dbo.tbl_TeamFile"; sqlparam.SqlStatement.WorksheetName = "TestSheet"; sqlparam.SqlStatement.RangeName = testRange.RangeName; _excelToSql.GetCommand(sqlparam); }
void exportButton_Click(object sender, RibbonControlEventArgs e) { ExcelToSql.ExcelToSqlService _excelToSql = new ExcelToSql.ExcelToSqlService(); _excelToSql.ExcelApplictaion = this.ExcelApplication; _excelToSql.InitConnectionParameters(SERVER, DATABASE, SQLUSER, SQLPW); SqlProcessParameters sqlparam = new SqlProcessParameters(); sqlparam.SqlStatement.SqlScript = "EXEC sp.CSVCreator 'gen','tbl_LOGA_MorganStanley_vacation',1,32,';'"; sqlparam.SqlStatement.WorksheetName = "ExportSheet"; sqlparam.SqlStatement.RangeName = exportRange.RangeName; _excelToSql.ExecuteCommand(sqlparam); sqlparam.SqlStatement.SqlScript = "SELECT * from gen.tbl_LOGA_MorganStanley_vacationCSV"; _excelToSql.GetCommand(sqlparam); ActiveWorkbook.SaveAs(@"c:\Users\csegenyr\Desktop\csvresult.csv", InteropExcel.XlFileFormat.xlCSV); }