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);
}
