Excel Export Using Infragistics Wingrid

using Excel; using System; using System.Drawing; using System.IO; using System.Reflection; using System.Threading; using System.Windows.Forms; using Infragistics.Documents.Excel; using Infragistics.Win.UltraWinGrid.ExcelExport; using Infragistics.Win.UltraWinToolbars; using com.airbus.pm00.model; using com.airbus.pm00.ui; using com.airbus.pm00.controller.util; using System.Data; using Infragistics.Win.UltraWinTree; using System.Globalization; using System.Security.AccessControl; using System.Security.Permissions; using System.Collections; namespace com.airbus.pm00.controller { public class CtrlExcelExport { public UiLayoutsUpdate UiM; public UiExcelExport uiExcelExport; private CtrlMain mainCtrl; private CtrlExcelExportMilestone milExpCtrl = CtrlExcelExportMilestone.GetInstance(); private CtrlExcelExportRfc rfcExpCtrl = CtrlExcelExportRfc.GetInstance(); private CtrlExcelExportMscn mscnExpCtrl = CtrlExcelExportMscn.GetInstance(); private CtrlExcelExportOutstandingPoint outstandingPointExpCtrl = CtrlExcelExportOutstandingPoint.GetInstance(); private CtrlExcelExportFleetCard fleetCardExpCtrl = CtrlExcelExportFleetCard.GetInstance(); private CtrlExcelExportActions actionsExcelCtrl = CtrlExcelExportActions.GetInstance(); private CtrlExcelExportFleetAC fleetAcExcelCtrl = CtrlExcelExportFleetAC.GetInstance(); private string currentAppliLayout; private string exportFullPath; private int currentLanguage = 1; //1 : English, 2 : French, 3 : Deutsh, 4 : Espanol //code updated by koshlendra for implementing FcoPmt req EVO07 on 21/11/2012 starts private string excelExtension = ".xls"; // private string excelExtension = ".xlsx"; //code updated by koshlendra for implementing FcoPmt req EVO07 on 21/11/2012 Ends // constants used for WorkSheet identification public const string CNameFleetACWS = "Fleet_AC"; public const string CNameMilestoneWS = "Milestones"; //Correction point 212 (05/11/2010) public const string CNameRFCWS = "RFC"; public const string CNameMscnWS = "MSCN"; public const string CNameTeamWS = "Team"; public const string CNameMeetingWS = "Meetings"; //Correction point 212 (05/11/2010) public const string CNameProgressStatusWS = "Progress Status"; public const string CNameOutstandingPointWS = "Outstanding_Points"; public const string CNameFleetCardWS = "Fleet ID"; //code upated for evo 12 by koshlendra on 23/05/2013 Start //public const string CNameQGOverviewWS = "Quality Gate Overview"; //public const string CNameFCOActionsWS = "FCO Director Actions"; public const string CNameQGOverviewWS = "Milestones Overview"; public const string CNameFCOActionsWS = " Actions"; //code upated for evo 12 by koshlendra on 23/05/2013 Start public const string CNameActionsWS = "Actions"; public const int CColumnOffset = 0; public const int CRowOffset = 0; //Point 155 (05/10/2010) //private bool isFilteredMil = false; //private bool isFilteredRFC = false; //private bool isFilteredMSCN = false; //private bool isFilteredOP = false; public CtrlExcelExport(UiLayoutsUpdate uiMain) { this.UiM = uiMain; mainCtrl = CtrlMain.GetInstance(); uiExcelExport = new UiExcelExport(); uiExcelExport.BtnBrowse.Click += new System.EventHandler(this.BtnBrowse_Click); uiExcelExport.BtnExport.Click += new System.EventHandler(this.BtnExport_Click); uiExcelExport.BtnQuit.Click += new System.EventHandler(this.BtnQuit_Click); uiExcelExport.BtnSelectPath.Click += new System.EventHandler(this.BtnSelectPath_Click); // Events utilisés pour formatter l'export Excel uiMain.UgeMain.BeginExport += new BeginExportEventHandler(this.UgeMain_BeginExport); uiMain.UgeMain.InitializeColumn += new InitializeColumnEventHandler(this.UgeMain_InitializeColumn); uiMain.UgeMain.HeaderRowExporting += new HeaderRowExportingEventHandler(this.UgeMain_HeaderRowExporting); uiMain.UgeMain.HeaderRowExported += new HeaderRowExportedEventHandler(this.UgeMain_HeaderRowExported); uiMain.UgeMain.RowExporting += new RowExportingEventHandler(this.UgeMain_RowExporting); uiMain.UgeMain.RowExported += new RowExportedEventHandler(this.UgeMain_RowExported); uiMain.UgeMain.HeaderCellExporting += new HeaderCellExportingEventHandler(this.UgeMain_HeaderCellExporting); uiMain.UgeMain.HeaderCellExported += new HeaderCellExportedEventHandler(this.UgeMain_HeaderCellExported); uiMain.UgeMain.CellExporting += new CellExportingEventHandler(this.UgeMain_CellExporting); uiMain.UgeMain.CellExported += new CellExportedEventHandler(this.UgeMain_CellExported); uiMain.UgeMain.EndExport+= new EndExportEventHandler(this.UgeMain_EndExport); } public void ShowExcelExport() { string ProgramCode = mainCtrl.GetCurrentProgramCode(); //string pathPFiles = Environment.GetEnvironmentVariable("ProgramFiles", EnvironmentVariableTarget.Process).ToString(); string pathPFiles = Environment.GetEnvironmentVariable("LocalShare", EnvironmentVariableTarget.Process).ToString(); string filesOut = pathPFiles + "\\" + BusParameters.GetInstance().GetParameterValue("EXCEL_EXPORT_DEFAULT_PATH", ProgramCode); uiExcelExport.TxtPath.Text = filesOut; //Retrieve layouts list in combo box ComboBoxTool CbbLoadLayout = (ComboBoxTool)UiM.UtmMain.Toolbars[3].Tools[0]; currentAppliLayout = CbbLoadLayout.Text; exportFullPath = ""; uiExcelExport.CbbLayout.Items.Clear(); DirectoryInfo layoutsDir = new DirectoryInfo(CtrlLayout.LayoutPath); foreach(DirectoryInfo layoutDir in layoutsDir.GetDirectories()) { if (!layoutDir.Name.StartsWith("_")) uiExcelExport.CbbLayout.Items.Add(layoutDir.Name); } uiExcelExport.CbbLayout.Text = currentAppliLayout; uiExcelExport.ClbListCheckbox.Items.Clear(); //Load Actions CtrlMain.GetInstance().actionsCtrl.CompleteActionsUpdates(); CtrlMain.GetInstance().actionsCtrl.LoadActions(); if (CtrlLayout.GetInstance().currentState == CtrlLayout.toolState.Project) { if (UiM.UgrTeam.Rows.Count != 0) uiExcelExport.ClbListCheckbox.Items.Add(CNameTeamWS, false); if (UiM.UgrMeetings.Rows.Count != 0) uiExcelExport.ClbListCheckbox.Items.Add(CNameMeetingWS, false); if (UiM.UgrFleetAC.Rows.Count != 0) uiExcelExport.ClbListCheckbox.Items.Add(CNameFleetACWS, false); if (UiM.UgrMilestones.Rows.Count != 0) uiExcelExport.ClbListCheckbox.Items.Add(CNameMilestoneWS, false); if (UiM.UgrRFC.Rows.Count != 0) uiExcelExport.ClbListCheckbox.Items.Add(CNameRFCWS, false); if (UiM.UgrMSCN.Rows.Count != 0) uiExcelExport.ClbListCheckbox.Items.Add(CNameMscnWS, false); if (UiM.UgrOutstandingPoints.Rows.Count != 0) uiExcelExport.ClbListCheckbox.Items.Add(CNameOutstandingPointWS, false); if (UiM.UgrAllActions.Rows.Count != 0) uiExcelExport.ClbListCheckbox.Items.Add(CNameActionsWS, false); uiExcelExport.ClbListCheckbox.Items.Add(CNameProgressStatusWS, false); uiExcelExport.ClbListCheckbox.Items.Add(CNameFleetCardWS, false); } else if (CtrlLayout.GetInstance().currentState == CtrlLayout.toolState.Dashboard) { uiExcelExport.ClbListCheckbox.Items.Add(CNameQGOverviewWS, false); uiExcelExport.ClbListCheckbox.Items.Add(CNameFCOActionsWS, false); //uiExcelExport.ClbListCheckbox.SetItemChecked(0, true); } uiExcelExport.ShowDialog(); } private void BtnBrowse_Click(object sender, System.EventArgs e) { System.Diagnostics.Process proc = new System.Diagnostics.Process(); // Nom de l'executable à lancer proc.StartInfo.FileName = "explorer.exe"; // Arguments à passer à l'exécutable à lancer proc.StartInfo.Arguments=uiExcelExport.TxtPath.Text; // Démarrage du processus proc.Start(); } private void BtnExport_Click(object sender, System.EventArgs e) { Cursor.Current = Cursors.WaitCursor; string layoutToExport = uiExcelExport.CbbLayout.SelectedItem.ToString(); bool changeLayout = (layoutToExport != currentAppliLayout); Infragistics.Documents.Excel.Workbook wb = new Infragistics.Documents.Excel.Workbook(WorkbookPaletteMode.StandardPalette); //An Excel sheet is created at this point. if (uiExcelExport.ClbListCheckbox.CheckedItems.Contains(CNameTeamWS)) //Checking whether Team is checked or not { if (changeLayout) CtrlLayout.GetInstance().LoadTeamLayout(layoutToExport); Infragistics.Documents.Excel.Worksheet teamSheet = wb.Worksheets.Add(CNameTeamWS); UiM.UgeMain.Export(UiM.UgrTeam, teamSheet, CRowOffset, CColumnOffset); if (changeLayout) CtrlLayout.GetInstance().LoadTeamLayout(currentAppliLayout); } if (uiExcelExport.ClbListCheckbox.CheckedItems.Contains(CNameMeetingWS)) { Infragistics.Documents.Excel.Worksheet meetingsAttendeesSheet = wb.Worksheets.Add(CNameMeetingWS); //on recharge les meetings attendees pour être sur d'être en ligne avec les dernieres modifs de meeting if (this.mainCtrl.UiM.UgrMeetings.ActiveRow != null) this.mainCtrl.UiM.UgrMeetings.ActiveRow.Update(); this.mainCtrl.meetingsAttendeesController.LoadMeetingsAttendees(); UiM.UgeMain.Export(UiM.UgrMeetingsAttendees, meetingsAttendeesSheet, CRowOffset, CColumnOffset); } if (uiExcelExport.ClbListCheckbox.CheckedItems.Contains(CNameFleetACWS)) { if (changeLayout) CtrlLayout.GetInstance().LoadFleetAcLayout(layoutToExport); Infragistics.Documents.Excel.Worksheet fleetACSheet = wb.Worksheets.Add(CNameFleetACWS); UiM.UgeMain.Export(UiM.UgrFleetAC, fleetACSheet, CRowOffset, CColumnOffset); if (changeLayout) CtrlLayout.GetInstance().LoadFleetAcLayout(currentAppliLayout); } if (uiExcelExport.ClbListCheckbox.CheckedItems.Contains(CNameMilestoneWS)) { if (changeLayout) CtrlLayout.GetInstance().LoadMilestonesLayout(layoutToExport); Infragistics.Documents.Excel.Worksheet milestonesSheet = wb.Worksheets.Add(CNameMilestoneWS); // CRowOffset+1 pour avoir la ligne qui affiche le MSN en + UiM.UgeMain.Export(UiM.UgrMilestones, milestonesSheet, CRowOffset+1, CColumnOffset); // isFilteredMil = (UiM.UgrMilestones.DisplayLayout.Bands[0].ColumnFilters.Count != 0); if (changeLayout) CtrlLayout.GetInstance().LoadMilestonesLayout(currentAppliLayout); } if (uiExcelExport.ClbListCheckbox.CheckedItems.Contains(CNameRFCWS)) { if (changeLayout) CtrlLayout.GetInstance().LoadRfcLayout(layoutToExport); Infragistics.Documents.Excel.Worksheet rfcSheet = wb.Worksheets.Add(CNameRFCWS); UiM.UgeMain.Export(UiM.UgrRFC, rfcSheet, CRowOffset, CColumnOffset); // isFilteredRFC = (UiM.UgrRFC.DisplayLayout.Bands[0].ColumnFilters.Count != 0); if (changeLayout) CtrlLayout.GetInstance().LoadRfcLayout(currentAppliLayout); } if (uiExcelExport.ClbListCheckbox.CheckedItems.Contains(CNameMscnWS)) { if (changeLayout) CtrlLayout.GetInstance().LoadMscnLayout(layoutToExport); Infragistics.Documents.Excel.Worksheet mscnSheet = wb.Worksheets.Add(CNameMscnWS); UiM.UgeMain.Export(UiM.UgrMSCN, mscnSheet, CRowOffset, CColumnOffset); // isFilteredMSCN = (UiM.UgrMSCN.DisplayLayout.Bands[0].ColumnFilters.Count != 0); if (changeLayout) CtrlLayout.GetInstance().LoadMscnLayout(currentAppliLayout); } if (uiExcelExport.ClbListCheckbox.CheckedItems.Contains(CNameOutstandingPointWS)) { if (changeLayout) CtrlLayout.GetInstance().LoadOutstandingPointsLayout(layoutToExport); Infragistics.Documents.Excel.Worksheet outstandingPointSheet = wb.Worksheets.Add(CNameOutstandingPointWS); UiM.UgeMain.Export(UiM.UgrOutstandingPoints, outstandingPointSheet, CRowOffset, CColumnOffset); // isFilteredOP = (UiM.UgrOutstandingPoints.DisplayLayout.Bands[0].ColumnFilters.Count != 0); if (changeLayout) CtrlLayout.GetInstance().LoadOutstandingPointsLayout(currentAppliLayout); } if (uiExcelExport.ClbListCheckbox.CheckedItems.Contains(CNameActionsWS)) { Infragistics.Documents.Excel.Worksheet actionsSheet = wb.Worksheets.Add(CNameActionsWS); RemoveHiddenColumn(); UiM.UgeMain.Export(UiM.UgrAllActions, actionsSheet, CRowOffset, CColumnOffset); } if (uiExcelExport.ClbListCheckbox.CheckedItems.Contains(CNameProgressStatusWS)) { Infragistics.Documents.Excel.Worksheet progressStatusSheet = wb.Worksheets.Add(CNameProgressStatusWS); UiM.UgeMain.Export(UiM.UgrProgressStatus, progressStatusSheet, CRowOffset, CColumnOffset); } if (uiExcelExport.ClbListCheckbox.CheckedItems.Contains(CNameQGOverviewWS)) { Infragistics.Documents.Excel.Worksheet QGOverviewSheet = wb.Worksheets.Add(CNameQGOverviewWS); UiM.UgeMain.Export(UiM.UgrMilestonesDatesForManager, QGOverviewSheet, CRowOffset, CColumnOffset); } if (uiExcelExport.ClbListCheckbox.CheckedItems.Contains(CNameFCOActionsWS)) { Infragistics.Documents.Excel.Worksheet FCOActionsSheet = wb.Worksheets.Add(CNameFCOActionsWS); UiM.UgeMain.Export(UiM.UgrMilestonesActions, FCOActionsSheet, CRowOffset, CColumnOffset); } if (uiExcelExport.ClbListCheckbox.CheckedItems.Contains(CNameFleetCardWS)) { int CurrentRowIndex1 = 0; Infragistics.Documents.Excel.Worksheet fleetCardSheet = wb.Worksheets.Add(CNameFleetCardWS); CurrentRowIndex1++; DataView dvFleetCard = BusFleetCard.GetInstance().GetCurrentFleetCard(); fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 0, "Project:", ExcelDefaultableBoolean.True); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 1, BusProject.GetInstance().GetCurrentProjectName()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 0, "HoV:", ExcelDefaultableBoolean.True); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 1, dvFleetCard[0]["C_HOV"].ToString()); fleetCardExpCtrl.FillCellComment(fleetCardSheet, CurrentRowIndex1, 2, dvFleetCard[0]["C_HOV_COMMENT"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 0, "Contr. Month:",ExcelDefaultableBoolean.True); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 1, dvFleetCard[0]["C_CONTR_MONTH"].ToString()); fleetCardExpCtrl.FillCellComment(fleetCardSheet, CurrentRowIndex1, 2, dvFleetCard[0]["C_CONTR_MONTH_COMMENT"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 0, "CDF:",ExcelDefaultableBoolean.True); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 1, dvFleetCard[0]["C_CDF"].ToString()); fleetCardExpCtrl.FillCellComment(fleetCardSheet, CurrentRowIndex1, 2, dvFleetCard[0]["C_CDF_COMMENT"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 0, "Spec:", ExcelDefaultableBoolean.True); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 1, dvFleetCard[0]["C_SPEC"].ToString()); fleetCardExpCtrl.FillCellComment(fleetCardSheet, CurrentRowIndex1, 2, dvFleetCard[0]["C_SPEC_COMMENT"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 0, "Standard:", ExcelDefaultableBoolean.True); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 1, dvFleetCard[0]["C_STANDARD"].ToString()); fleetCardExpCtrl.FillCellComment(fleetCardSheet, CurrentRowIndex1, 2, dvFleetCard[0]["C_STANDARD_COMMENT"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 0, "A/C model:", ExcelDefaultableBoolean.True); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 1, dvFleetCard[0]["C_AC_MODEL"].ToString()); fleetCardExpCtrl.FillCellComment(fleetCardSheet, CurrentRowIndex1, 2, dvFleetCard[0]["C_AC_MODEL_COMMENT"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 0, "Engines:", ExcelDefaultableBoolean.True); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 1, dvFleetCard[0]["C_ENGINES"].ToString()); fleetCardExpCtrl.FillCellComment(fleetCardSheet, CurrentRowIndex1, 2, dvFleetCard[0]["C_ENGINES_COMMENT"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 0, "Certif. Author.:", ExcelDefaultableBoolean.True); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 1, dvFleetCard[0]["C_CERTIF_AUTHORITY"].ToString()); fleetCardExpCtrl.FillCellComment(fleetCardSheet, CurrentRowIndex1, 2, dvFleetCard[0]["C_CERTIF_AUTHORITY_COMMENT"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 0, "Design weights:", ExcelDefaultableBoolean.True); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 0, "MTOW:", ExcelDefaultableBoolean.False); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 1, dvFleetCard[0]["C_MTOV"].ToString()); CurrentRowIndex1++; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 0, "MLW:", ExcelDefaultableBoolean.False); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 1, dvFleetCard[0]["C_MLW"].ToString()); fleetCardExpCtrl.FillCellSimple(fleetCardSheet, CurrentRowIndex1, 2, " " + dvFleetCard[0]["C_DESIGN_WEIGHTS_COMMENT"].ToString()); CurrentRowIndex1++; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 0, "MZFW:", ExcelDefaultableBoolean.False); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 1, dvFleetCard[0]["C_MZFW"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 3; //Export Properties Grid UiM.UgrFleetCardProperties.DisplayLayout.Bands[1].Override.ExpansionIndicator = Infragistics.Win.UltraWinGrid.ShowExpansionIndicator.Never; UiM.UgrFleetCardProperties.Rows.ExpandAll(true); UiM.UgeMain.Export(UiM.UgrFleetCardProperties, fleetCardSheet, CRowOffset + CurrentRowIndex1, 0); UiM.UgrFleetCardProperties.Rows.CollapseAll(true); UiM.UgrFleetCardProperties.DisplayLayout.Bands[1].Override.ExpansionIndicator = Infragistics.Win.UltraWinGrid.ShowExpansionIndicator.CheckOnDisplay; CurrentRowIndex1 = 1; //exporty Layouts Summary fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 3, "Layouts Summary:", ExcelDefaultableBoolean.True); CurrentRowIndex1++; if (UiM.UgrFleetCardLSummary.Rows.Count != 0) { UiM.UgeMain.Export(UiM.UgrFleetCardLSummary, fleetCardSheet, CurrentRowIndex1, 3); CurrentRowIndex1 = CurrentRowIndex1 + UiM.UgrFleetCardLSummary.Rows.Count + 1; } else { fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 3, ""); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 4, ""); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 5, ""); fleetCardExpCtrl.FillCellRounded(fleetCardSheet, CurrentRowIndex1, 6, ""); CurrentRowIndex1++; } fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 3, "References:", ExcelDefaultableBoolean.True); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 4, "Cabin layout drwg:", ExcelDefaultableBoolean.False); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 5, dvFleetCard[0]["C_CABIN_LAYOUT_DRG"].ToString()); fleetCardExpCtrl.FillCellComment(fleetCardSheet, CurrentRowIndex1, 6, dvFleetCard[0]["C_CABIN_LAYOUT_DRG_COMMENT"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 4, "Alt Cabin layout drwg:", ExcelDefaultableBoolean.False); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 5, dvFleetCard[0]["C_ALT_CABIN_LAYOUT_DRG"].ToString()); fleetCardExpCtrl.FillCellComment(fleetCardSheet, CurrentRowIndex1, 6, dvFleetCard[0]["C_ALT_CABIN_LAYOUT_DRG_COMMENT"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 4, "Cabin emerg. drwg:", ExcelDefaultableBoolean.False); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 5, dvFleetCard[0]["C_CABIN_EMERGENCY_DRG"].ToString()); fleetCardExpCtrl.FillCellComment(fleetCardSheet, CurrentRowIndex1, 6, dvFleetCard[0]["C_CABIN_EMERGENCY_DRG_COMMENT"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 4, "Cabin colour drwg:", ExcelDefaultableBoolean.False); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 5, dvFleetCard[0]["C_INTERIOR_COLOR_SPEC"].ToString()); fleetCardExpCtrl.FillCellComment(fleetCardSheet, CurrentRowIndex1, 6, dvFleetCard[0]["C_INTERIOR_COLOR_SPEC_COMMENT"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 4, "External livery drwg:", ExcelDefaultableBoolean.False); fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 5, dvFleetCard[0]["C_EXTERNAL_LIVERY_DRG"].ToString()); fleetCardExpCtrl.FillCellComment(fleetCardSheet, CurrentRowIndex1, 6, dvFleetCard[0]["C_EXTERNAL_LIVERY_DRG_COMMENT"].ToString()); CurrentRowIndex1 = CurrentRowIndex1 + 2; fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 3, "BFE list:", ExcelDefaultableBoolean.True); if (UiM.UgrFleetCardBFE.Rows.Count != 0) { CurrentRowIndex1++; UiM.UgeMain.Export(UiM.UgrFleetCardBFE, fleetCardSheet, CurrentRowIndex1, 4); CurrentRowIndex1 = CurrentRowIndex1 + UiM.UgrFleetCardBFE.Rows.Count + 1; CurrentRowIndex1++; } else { CurrentRowIndex1++; fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 4, ""); fleetCardExpCtrl.FillCellRounded(fleetCardSheet, CurrentRowIndex1, 5, ""); CurrentRowIndex1++; } fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 3, "SFE list:", ExcelDefaultableBoolean.True); if (UiM.UgrFleetCardSFE.Rows.Count != 0) { CurrentRowIndex1++; UiM.UgeMain.Export(UiM.UgrFleetCardSFE, fleetCardSheet, CurrentRowIndex1, 4); CurrentRowIndex1 = CurrentRowIndex1 + UiM.UgrFleetCardSFE.Rows.Count + 1; CurrentRowIndex1++; } else { CurrentRowIndex1++; fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 4, ""); fleetCardExpCtrl.FillCellRounded(fleetCardSheet, CurrentRowIndex1, 5, ""); CurrentRowIndex1++; } fleetCardExpCtrl.FillCellTitle(fleetCardSheet, CurrentRowIndex1, 3, "SSBFE list:", ExcelDefaultableBoolean.True); if (UiM.UgrFleetCardSSBFE.Rows.Count != 0) { CurrentRowIndex1++; UiM.UgeMain.Export(UiM.UgrFleetCardSSBFE, fleetCardSheet, CurrentRowIndex1, 4); CurrentRowIndex1 = CurrentRowIndex1 + UiM.UgrFleetCardSSBFE.Rows.Count + 1; } else { CurrentRowIndex1++; fleetCardExpCtrl.FillCellValue(fleetCardSheet, CurrentRowIndex1, 4, ""); fleetCardExpCtrl.FillCellRounded(fleetCardSheet, CurrentRowIndex1, 5, ""); CurrentRowIndex1++; } CurrentRowIndex1++; fleetCardExpCtrl.FillCellSimple(fleetCardSheet, CurrentRowIndex1, 3, dvFleetCard[0]["C_COMMENT"].ToString()); fleetCardSheet.Rows[CurrentRowIndex1].Cells[3].CellFormat.WrapText = ExcelDefaultableBoolean.True; fleetCardSheet.Rows[CurrentRowIndex1].Cells[3].CellFormat.VerticalAlignment = VerticalCellAlignment.Top; fleetCardSheet.MergedCellsRegions.Add(CurrentRowIndex1, 3, CurrentRowIndex1 + 15, 5); } //Ensure that the path exist and create it if not existing string exportPath = uiExcelExport.TxtPath.Text; if (!Directory.Exists(exportPath)) Directory.CreateDirectory(exportPath); string projectName = ""; string managerFullName = ""; if (CtrlLayout.GetInstance().currentState == CtrlLayout.toolState.Project) { projectName = CtrlUtils.NormalizeFileName(mainCtrl.projectController.GetCurrentProjectName()); managerFullName = mainCtrl.projectController.GetCurrentProject()[0]["MANAGER_FULLNAME"].ToString(); exportFullPath = exportPath + "\\" + CtrlUtils.GenerateFileName(projectName) + excelExtension; } else if (CtrlLayout.GetInstance().currentState == CtrlLayout.toolState.Dashboard) { projectName = ""; managerFullName = BusNavigation.GetInstance().GetManagerNameById(mainCtrl.dashboardController.CurrentLoadedManagerId); exportFullPath = exportPath + "\\" + CtrlUtils.GenerateFileName(projectName) + excelExtension; } if (uiExcelExport.ClbListCheckbox.CheckedItems.Count != 0) { if (File.Exists(exportFullPath)) { DialogResult result = MessageBox.Show("A file having the same name already exists, would you replace it?", "", System.Windows.Forms.MessageBoxButtons.YesNo); if (result == DialogResult.No) { MessageBox.Show("Export canceled by user"); return; } } try { FileStream newFile = File.Create(exportFullPath); newFile.Flush(); newFile.Close(); //BIFF8Writer.WriteWorkbookToFile(wb, exportFullPath); //DEPRECATED, remplacé par wb.save wb.Save(exportFullPath); _Workbook exportedWorkbook = RetrieveExportedWorkbook(); ReplaceActionsColumns(exportedWorkbook); AddActionsHyperlinks(exportedWorkbook); AutofitRows(exportedWorkbook); AddLogosToFleetCard(exportedWorkbook); MapOutPointsLinksObjects(exportedWorkbook); //il faut le faire après l'autofit rows AutofilterColumns(exportedWorkbook); //code added for deleting exported .xls file for Evo7 by Koshlendra start if(File.Exists(exportFullPath+"x")) { File.Delete(exportFullPath+"x"); } //code added for deleting exported .xls file for Evo7 by Koshlendra end // code added by Nitin Bodade on 3rdjune for evo07 starts string processFilePath = Environment.GetEnvironmentVariable("ProgramFiles") + @"\Microsoft Office\Office12\excelcnv.exe"; string processArguments = "-oice \"" + exportFullPath + "\" \"" + Path.GetDirectoryName(exportFullPath) + @"\" + Path.GetFileNameWithoutExtension(exportFullPath) + ".xlsx\""; System.Diagnostics.Process process = new System.Diagnostics.Process(); process.StartInfo.FileName = processFilePath; process.StartInfo.Arguments = processArguments; process.Start(); // code added by Nitin Bodade on 3rdjune for evo07 End MessageBox.Show("End of Export"); //code added for deleting exported .xls file for Evo7 by Koshlendra start File.Delete(exportFullPath); //code added for deleting exported .xls file for Evo7 by Koshlendra end } catch (Exception ex) { //Code commented by Koshlendra For removing detailed error on excel Export on 20 june 2013 start //MessageBox.Show(ex.ToString()); //Code commented by Koshlendra For removing detailed error on excel Export on 20 june 2013 end MessageBox.Show("Selected file is already in use, please close it before export !"); } } else MessageBox.Show("You have to select one or more items before export."); } private void BtnQuit_Click(object sender, System.EventArgs e) { uiExcelExport.Close(); } private void BtnSelectPath_Click(object sender, System.EventArgs e) { Thread thread = new Thread(new ThreadStart(OpenDialog)); thread.SetApartmentState(ApartmentState.STA); thread.Start(); thread.Join(); uiExcelExport.Activate(); } private void OpenDialog() { uiExcelExport.FbdBrowse.RootFolder = System.Environment.SpecialFolder.Desktop; if (uiExcelExport.FbdBrowse.ShowDialog() == DialogResult.OK) { uiExcelExport.TxtPath.Text = uiExcelExport.FbdBrowse.SelectedPath; } } // -------------------- Events used to format Excel export ------------- private void UgeMain_BeginExport(object sender, BeginExportEventArgs e) { if (e.CurrentWorksheet.Name == CNameMilestoneWS) milExpCtrl.BeginExport(e); else if (e.CurrentWorksheet.Name == CNameRFCWS) rfcExpCtrl.BeginExport(e); else if (e.CurrentWorksheet.Name == CNameMscnWS) mscnExpCtrl.BeginExport(e); else if (e.CurrentWorksheet.Name == CNameOutstandingPointWS) outstandingPointExpCtrl.BeginExport(e); else if (e.CurrentWorksheet.Name == CNameFleetCardWS) fleetCardExpCtrl.BeginExport(e); } private void UgeMain_InitializeColumn(object sender, InitializeColumnEventArgs e) { if (e.Column.DataType == typeof(DateTime)) { e.ExcelFormatStr ="dd/mm/yyyy;@"; } } private void UgeMain_HeaderRowExporting(object sender, HeaderRowExportingEventArgs e) { // CRowOffset+1 pour la ligne qui affiche le MSN en + //code for adding link column in export by Koshlendra on 21/06/2013 start // if (e.CurrentWorksheet.Name == CNameMilestoneWS) milExpCtrl.HeaderRowExporting(e, CRowOffset if (e.CurrentWorksheet.Name == CNameMilestoneWS) milExpCtrl.HeaderRowExporting(e, CRowOffset, CColumnOffset); //code for adding link column in export by Koshlendra on 21/06/2013 end else if (e.CurrentWorksheet.Name == CNameRFCWS) rfcExpCtrl.HeaderRowExporting(e, CRowOffset, CColumnOffset); else if (e.CurrentWorksheet.Name == CNameMscnWS) mscnExpCtrl.HeaderRowExporting(e, CRowOffset, CColumnOffset); else if (e.CurrentWorksheet.Name == CNameOutstandingPointWS) outstandingPointExpCtrl.HeaderRowExporting(e, CRowOffset, CColumnOffset); } private void UgeMain_HeaderRowExported(object sender, HeaderRowExportedEventArgs e) { if (e.CurrentWorksheet.Name == CNameMilestoneWS) milExpCtrl.HeaderRowExported(e); else if (e.CurrentWorksheet.Name == CNameRFCWS) rfcExpCtrl.HeaderRowExported(e); else if (e.CurrentWorksheet.Name == CNameMscnWS) mscnExpCtrl.HeaderRowExported(e); else if (e.CurrentWorksheet.Name == CNameOutstandingPointWS) outstandingPointExpCtrl.HeaderRowExported(e); } private void UgeMain_RowExporting(object sender, RowExportingEventArgs e) { if (e.CurrentWorksheet.Name == CNameMilestoneWS) milExpCtrl.RowExporting(e, CColumnOffset); else if (e.CurrentWorksheet.Name == CNameRFCWS) rfcExpCtrl.RowExporting(e, CColumnOffset); else if (e.CurrentWorksheet.Name == CNameMscnWS) mscnExpCtrl.RowExporting(e, CColumnOffset); else if (e.CurrentWorksheet.Name == CNameOutstandingPointWS) outstandingPointExpCtrl.RowExporting(e, CColumnOffset); else if (e.CurrentWorksheet.Name == CNameFleetCardWS) fleetCardExpCtrl.RowExporting(e, CColumnOffset); else if (e.CurrentWorksheet.Name == CNameActionsWS) actionsExcelCtrl.RowExporting(e, CColumnOffset); } private void UgeMain_RowExported(object sender, RowExportedEventArgs e) { if (e.CurrentWorksheet.Name == CNameMilestoneWS) milExpCtrl.RowExported(e); //else if (e.CurrentWorksheet.Name == CNameRFCWS) rfcExpCtrl.RowExported(e); } private void UgeMain_HeaderCellExporting(object sender, HeaderCellExportingEventArgs e) { if (e.CurrentWorksheet.Name == CNameMilestoneWS) milExpCtrl.HeaderCellExporting(e); } private void UgeMain_HeaderCellExported(object sender, HeaderCellExportedEventArgs e) { if (e.CurrentWorksheet.Name == CNameMilestoneWS) milExpCtrl.HeaderCellExported(e); else if (e.CurrentWorksheet.Name == CNameRFCWS) rfcExpCtrl.HeaderCellExported(e); else if (e.CurrentWorksheet.Name == CNameMscnWS) mscnExpCtrl.HeaderCellExported(e); else if (e.CurrentWorksheet.Name == CNameOutstandingPointWS) outstandingPointExpCtrl.HeaderCellExported(e); } private void UgeMain_CellExporting(object sender, CellExportingEventArgs e) { if (e.CurrentWorksheet.Name == CNameMilestoneWS) milExpCtrl.CellExporting(e); else if (e.CurrentWorksheet.Name == CNameFleetCardWS) fleetCardExpCtrl.CellExporting(e); } private void UgeMain_CellExported(object sender, CellExportedEventArgs e) { if (e.CurrentWorksheet.Name == CNameMilestoneWS) milExpCtrl.CellExported(e); else if (e.CurrentWorksheet.Name == CNameRFCWS) rfcExpCtrl.CellExported(e); else if (e.CurrentWorksheet.Name == CNameMscnWS) mscnExpCtrl.CellExported(e); else if (e.CurrentWorksheet.Name == CNameOutstandingPointWS) outstandingPointExpCtrl.CellExported(e); else if (e.CurrentWorksheet.Name == CNameFleetCardWS) fleetCardExpCtrl.CellExported(e); else if (e.CurrentWorksheet.Name == CNameFleetACWS) fleetAcExcelCtrl.CellExported(e, currentLanguage); else if (e.CurrentWorksheet.Name == CNameActionsWS) actionsExcelCtrl.CellExported(e); else if (e.CurrentWorksheet.Name != CNameProgressStatusWS) { e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin; e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin; e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex].CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin; e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin; //changes by kp //e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex].CellFormat.TopBorderColor = Color.Black; //e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex].CellFormat.BottomBorderColor = Color.Black; //e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex].CellFormat.LeftBorderColor = Color.Black; //e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex].CellFormat.RightBorderColor = Color.Black; } } private void UgeMain_EndExport(object sender, EndExportEventArgs e) { if (e.CurrentWorksheet.Name == CNameFleetACWS) { for(int iRow=CRowOffset; iRow<=e.currentrowindex; irow="" e="" currentworksheet="" rows="" expanded="true;" deprecated="" hidden="false;" else="" if="" name="=" cnamefleetcardws="" for="" int="" currentrowindex="" outlinelevel="0;" cnamemilestonews="" crowoffset="" 1="" pour="" la="" ligne="" qui="" affiche="" le="" msn="" en="" milexpctrl="" endexport="" ccolumnoffset="" isfilteredmil="" cnamerfcws="" rfcexpctrl="" isfilteredrfc="" cnamemscnws="" mscnexpctrl="" isfilteredmscn="" cnameoutstandingpointws="" outstandingpointexpctrl="" isfilteredop="" cnameactionsws="" actionsexcelctrl="" private="" _workbook="" retrieveexportedworkbook="" try="" excel="" application="" app="" msolanguageidexemode="Execution" mode="" language="" msolanguageidhelp="Help" msolanguageidinstall="Install" msolanguageidui="" interface="" msolanguageiduiprevious="User" used="" prior="" to="" the="" current="" user="" switch="" languagesettings="" get_languageid="" office="" msoapplanguageid="" case="" 1033:="" currentlanguage="=" break="" 1036:="" 1031:="" 3082:="" default:="" visible="true;" workbook="" missing="" value="" return="" catch="" exception="" throw="" new="" fcoexception="" method:="" ctrlexcelexport="" tostring="" void="" addlogostofleetcard="" foreach="" _worksheet="" worksheet="" in="" worksheets="" activate="" autofit="" titles="" columns="" range="" type="" cells="" 4="" entirecolumn="" insert="" blanc="" logos="" xldirection="" xldown="" set="" column="" widths="" columnwidth="20;" 2="" 3="" 7="" string="" customerid="" operatorid="" logoscurseur="" accessconfig="" configuration="" retrieve="" logpath="configuration.Config.excelLogoPath;" of="" xml="" config="" file="" draw="" customer="" logo="" length="" 0="" busreferencedata="" getinstance="" getthirdlogobycode="" null="" memorystream="" customerlogo="" bitmap="" custlogo="" directory="" exists="" excellogopath="" createdirectory="" save="" jpeg="" system="" drawing="" imaging="" imageformat="" size="" outputsize="" 160="" 90="" shapes="" addpicture="" msotristate="" msofalse="" msoctrue="" 10="" width="" height="" operator="" ---="" operatorlogo="" operlogo="" move="" a="" another="" position="" repositionnecolumn="" columnname="" rowindex="" xllookat="" lookat="" colinsert="" coltomoverange="" xlfindlookin="" xlvalues="" xlsearchdirection="" xlnext="" false="" fillleft="" coltomove="" copy="" xlinsertshiftdirection="" xlshifttoright="" delete="" replace="" actions="" according="" program="" a380="" replaceactionscolumns="" ctrlmain="" getcurrentprogramcode="" r="" compute="" milestones="" sheet="" find="" header="" as="" reference="" colreplaceposname="BusParameters.GetInstance().GetParameterValue("OUTSTANDING_POINT_ACTIONS_COLUMNS_POS"," colreplaceposrange="" xlwhole="" begin="" insertions="" repositionne="" remark="" action="" responsible="" owner="" due="" date="" rfcs="" mscn="" code="" addded="" req="" evo05="" by="" koshlendra="" start="" on="" 17="" 05="" 2013="" outstanding="" point="" ends="" add="" files="" instead="" links="" points="" mapoutpointslinksobjects="" addlink="" linkslist="" arraylist="" array="" link="" celllink="" select="" rowheight="65;" addoleobject="" linkstr="" true="" obligation="" de="" changer="" ce="" fonctionnement="" 2007="" :="" probl="" me="" d="" icone="" taille="" correction="" 183="" 24="" 09="" 2010="" iconfilename="" substring="" lastindexof="" toupper="" bmp="" images="" icone-jpg="" ico="" doc="" icone-word="" docx="" dot="" dotx="" htm="" icone-html="" html="" jpg="" msg="" icone-outlook="" pdf="" icone-pdf="" ppt="" icone-powerpoint="" pptx="" rtf="" icone-rtf="" txt="" icone-notepad="" wmf="" xls="" icone-excel="" xlsx="" icone-default="" left="" top="" 60="" runtime="" interopservices="" comexception="" messagebox="" show="" error="" message="" ctrlutils="" trace="" autofitrows="" lastmilestonerow="" currow="" entirerow="" lastrfcrow="" mscns="" lastmscnrow="" lastoutstandingpointrow="" lastactionrow="" all="" fco="" director="" cnamefcoactionsws="" s="" codenames="" are="" sheets="" with="" unique="" and="" permanent="" names="" which="" doesn="" t="" change="" hyperlinks="" from="" tab="" still="" working="" returnwhorksheetcodename="" worksheetname="" when="" vbproject="" is="" not="" open="" codename="" microsoft="" vbe="" interop="" vbp="workbook.VBProject;" cname="" c_action_ref="" corresponding="" item="" into="" addactionshyperlinks="" bool="" milestoneok="" rfcok="" fleetok="" epacok="" fleetcolumn="" epaccolumn="" outstandingok="" voir="" si="" n="" cessaire="" garder="" colonne="" msnexist="" rechercher="" les="" colonnes="" f="" renc="" es="" dans="" liens="" chaque="" feuille="" i="" nbcolumnsmilestones="" id="" nbcolumnsrfc="" rfc="" nbcolumnsmscn="" fleet="=" epac="=fleetepac.Substring(fleetepac.IndexOf("-")+1))))" tdu="" lastoutstandingpointcolumn="" title="" nbactions="" index="" while="" mise="" jour="" du="" flag="" est="" msnrange="" cellactionref="" milestone="" nitin="" b="" evo29="" formula="=HIPERVINCULO(\"[\"&EXTRAE(CELDA(\"filename\",A1),ENCONTRAR(\"[\",CELDA(\"filename\",A1),1)+1,ENCONTRAR(\"]\",CELDA(\"filename\",A1),1)-ENCONTRAR(\"[\",CELDA(\"filename\",A1),1)-1)&\"]\"&DIRECCION(COINCIDIR(\"" indirect="" char="" 64="" match="" 2:2="" car="" equiv="" indirekt="" zeichen="" vergleich="" indirecto="" caracter="" coincidir="" 1:1="" 166="" 23="" chercher="" bonne="" l="" onglet="" oblig="" pouvoir="" faire="" lien="" sur="" wsmscn="" fleetepac="" nbmscn="" indexmscn="" indexof="" -="" force="" sortie="" boucle="" une="" fois="" qu="" trait="" cette="" outstanding_points="" during="" hyperlink="" generation="" cancelled="" supprimer="" xldeleteshiftdirection="" xlshifttoleft="" autofiltercolumns="" toutes="" feuilles="" 196="" 20="" 213="" 04="" 11="" pagesetup="" papersize="XlPaperSize.xlPaperA3;" orientation="XlPageOrientation.xlLandscape;" centerhorizontally="true;" centervertically="true;" centerheader="&F \n&A" rightheader="Page &P/&N" cnameteamws="" printtitlerows="$1:$1" zoom="false;" fittopageswide="2;" fittopagestall="50;" cnamemeetingws="" cnamefleetacws="" pose="" un="" souci="" au="" niveau="" comment="" 165="" outline="" summaryrow="XlSummaryRow.xlSummaryBelow;" et="" met="" a3="" landscap="" lastrfccolumn="" 25="" firstrfccell="Convert.ToChar(65" lastrfccell="rfcExpCtrl.lastRfcRow.ToString();"> 52) lastRfcCell = "B" + Convert.ToChar(65 + rfcExpCtrl.lastRfcColumn - 52).ToString() + lastRfcCell; else if (rfcExpCtrl.lastRfcColumn + 1 > 26) lastRfcCell = "A" + Convert.ToChar(65 + rfcExpCtrl.lastRfcColumn - 26).ToString() + lastRfcCell; else lastRfcCell = Convert.ToChar(65 + rfcExpCtrl.lastRfcColumn).ToString() + lastRfcCell; worksheet.PageSetup.PrintArea = "A1:" + lastRfcCell; Excel.Range rRfc = worksheet.get_Range(firstRfcCell, lastRfcCell); rRfc.AutoFilter(1, Missing.Value, Excel.XlAutoFilterOperator.xlAnd, Missing.Value, true); } else if (worksheet.Name == CNameMscnWS) { worksheet.Activate(); //Pour RFC et MSCN on met en A3 et en Landscape worksheet.PageSetup.PaperSize = XlPaperSize.xlPaperA3; worksheet.PageSetup.Orientation = XlPageOrientation.xlLandscape; worksheet.PageSetup.Zoom = false; worksheet.PageSetup.FitToPagesTall = 50; if (mscnExpCtrl.lastMscnColumn < 25) worksheet.PageSetup.FitToPagesWide = 1; else worksheet.PageSetup.FitToPagesWide = 2; worksheet.PageSetup.PrintTitleRows = "$1:$1"; //worksheet.Columns.AutoFit(); string firstMscnCell = Convert.ToChar(65 + CColumnOffset).ToString() + (CRowOffset + 1).ToString(); string lastMscnCell = mscnExpCtrl.lastMscnRow.ToString(); if (mscnExpCtrl.lastMscnColumn + 1 > 52) lastMscnCell = "B" + Convert.ToChar(65 + mscnExpCtrl.lastMscnColumn - 52).ToString() + lastMscnCell; else if (mscnExpCtrl.lastMscnColumn + 1 > 26) lastMscnCell = "A" + Convert.ToChar(65 + mscnExpCtrl.lastMscnColumn - 26).ToString() + lastMscnCell; else lastMscnCell = Convert.ToChar(65 + mscnExpCtrl.lastMscnColumn).ToString() + lastMscnCell; worksheet.PageSetup.PrintArea = "A1:" + lastMscnCell; Range rMscn = worksheet.get_Range(firstMscnCell, lastMscnCell); rMscn.AutoFilter(1, Missing.Value, Excel.XlAutoFilterOperator.xlAnd, Missing.Value, true); } else if (worksheet.Name == CNameOutstandingPointWS) { worksheet.Activate(); worksheet.PageSetup.PrintTitleRows = "$1:$1"; worksheet.PageSetup.Zoom = false; worksheet.PageSetup.FitToPagesWide = 1; worksheet.PageSetup.FitToPagesTall = 5; //worksheet.Columns.AutoFit(); string firstOutstandingPointCell = Convert.ToChar(65 + CColumnOffset).ToString() + (CRowOffset + 1).ToString(); string lastOutstandingPointCell = outstandingPointExpCtrl.lastOutstandingPointRow.ToString(); if (outstandingPointExpCtrl.lastOutstandingPointColumn + 1 > 52) lastOutstandingPointCell = "B" + Convert.ToChar(65 + outstandingPointExpCtrl.lastOutstandingPointColumn - 52).ToString() + lastOutstandingPointCell; else if (outstandingPointExpCtrl.lastOutstandingPointColumn + 1 > 26) lastOutstandingPointCell = "A" + Convert.ToChar(65 + outstandingPointExpCtrl.lastOutstandingPointColumn - 26).ToString() + lastOutstandingPointCell; else lastOutstandingPointCell = Convert.ToChar(65 + outstandingPointExpCtrl.lastOutstandingPointColumn).ToString() + lastOutstandingPointCell; worksheet.PageSetup.PrintArea = "A1:" + lastOutstandingPointCell; Range rOutstandingPoint = worksheet.get_Range(firstOutstandingPointCell, lastOutstandingPointCell); rOutstandingPoint.AutoFilter(1, Missing.Value, Excel.XlAutoFilterOperator.xlAnd, Missing.Value, true); rOutstandingPoint = worksheet.Cells.Find("Remark", worksheet.Cells[1, 1], Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Missing.Value, Excel.XlSearchDirection.xlNext, false, Missing.Value); if (rOutstandingPoint != null) { //Passer l'alignement à Top Left pour les Remark dans l'onglet Outstanding Points rOutstandingPoint.EntireColumn.HorizontalAlignment = XlHAlign.xlHAlignLeft; rOutstandingPoint.EntireColumn.VerticalAlignment = XlVAlign.xlVAlignTop; //Recentrer le titre rOutstandingPoint.EntireRow.HorizontalAlignment = XlHAlign.xlHAlignCenter; rOutstandingPoint.EntireRow.VerticalAlignment = XlVAlign.xlVAlignCenter; } } else if (worksheet.Name == CNameActionsWS) { worksheet.Activate(); worksheet.PageSetup.PrintTitleRows = "$1:$1"; worksheet.PageSetup.Zoom = false; worksheet.PageSetup.FitToPagesWide = 1; worksheet.PageSetup.FitToPagesTall = 5; //worksheet.Columns.AutoFit(); string firstActionsCell = Convert.ToChar(65 + CColumnOffset).ToString() + (CRowOffset + 1).ToString(); string lastActionsCell = (CtrlMain.GetInstance().UiM.UgrAllActions.Rows.Count + 1).ToString(); int nbColumnsActions = CtrlMain.GetInstance().UiM.UgrAllActions.DisplayLayout.Bands[0].Columns.Count - 4; //on enlève les colonnes cachées if (nbColumnsActions > 26) lastActionsCell = "A" + Convert.ToChar(65 + nbColumnsActions - 26).ToString() + lastActionsCell; else lastActionsCell = Convert.ToChar(65 + nbColumnsActions).ToString() + lastActionsCell; worksheet.PageSetup.PrintArea = "A1:" + lastActionsCell; Range rActions = worksheet.get_Range(firstActionsCell, lastActionsCell); rActions.AutoFilter(1, Missing.Value, Excel.XlAutoFilterOperator.xlAnd, Missing.Value, true); } else if (worksheet.Name == CNameProgressStatusWS) { worksheet.Activate(); worksheet.PageSetup.Orientation = XlPageOrientation.xlLandscape; worksheet.PageSetup.PrintTitleRows = "$1:$1"; worksheet.PageSetup.Zoom = false; worksheet.PageSetup.FitToPagesWide = 2; worksheet.PageSetup.FitToPagesTall = 2; } else if (worksheet.Name == CNameFleetCardWS) { worksheet.Activate(); worksheet.PageSetup.Zoom = false; worksheet.PageSetup.FitToPagesWide = 1; worksheet.PageSetup.FitToPagesTall = 1; } else if (worksheet.Name == CNameQGOverviewWS) { worksheet.Activate(); worksheet.PageSetup.Orientation = XlPageOrientation.xlLandscape; worksheet.PageSetup.PrintTitleRows = "$1:$3"; worksheet.PageSetup.Zoom = false; worksheet.PageSetup.FitToPagesWide = 2; worksheet.PageSetup.FitToPagesTall = 50; } else if (worksheet.Name == CNameFCOActionsWS) { worksheet.Activate(); worksheet.PageSetup.PrintTitleRows = "$1:$1"; worksheet.Rows.AutoFilter(1, Missing.Value, Excel.XlAutoFilterOperator.xlAnd, Missing.Value, true); worksheet.PageSetup.Zoom = false; worksheet.PageSetup.FitToPagesWide = 1; worksheet.PageSetup.FitToPagesTall = 50; } } //A la fin, on réactive la sheet "Actions" foreach (_Worksheet worksheet in workbook.Worksheets) { if (worksheet.Name == CNameActionsWS) worksheet.Activate(); else if (worksheet.Name == CNameQGOverviewWS) worksheet.Activate(); } workbook.Save(); workbook.Application.DisplayAlerts = false; workbook.Application.Quit(); } catch(Exception e) { throw new FCOException ("Method: CtrlExcelExport.AutofilterColumns(). "+ e.ToString()); } } private Size RedimensionneLogo(Size imageSize, Size celSize) { Size newImageSize = imageSize; float RatioTarget = (float)celSize.Width/celSize.Height; float RatioSource = (float)imageSize.Width/imageSize.Height; //RatioTarget is smaller than RatioSource => compute source size with Target Width and Source Size if (RatioTarget < RatioSource) { newImageSize.Width = celSize.Width; newImageSize.Height = (int)(celSize.Width / RatioSource); } else //RatioTarget is bigger than RatioSource => compute source size with Target Height and Source Size { newImageSize.Height = celSize.Height; newImageSize.Width = (int)(celSize.Height * RatioSource); } return newImageSize; } private void RemoveHiddenColumn() { // UiM.UgrAllActions. } } //Create object Link having row index, column index and link class Link { private int _row; private int _column; private string _linkStr; public Link(int row, int column, string linkStr) { this._row = row; this._column = column; this._linkStr = linkStr; } public int Row { get { return (this._row); } set { this._row = value; } } public int Column { get { return (this._column); } set { this._column = value; } } public string LinkStr { get { return (this._linkStr); } set { this._linkStr = value; } } } }