/// <summary> /// Create the Report /// </summary> /// <param name="usersBaseReportingDirectory">The file path.</param> /// <returns></returns> public string Create(string usersBaseReportingDirectory) { if (String.IsNullOrEmpty(usersBaseReportingDirectory)) throw new ArgumentNullException("usersBaseReportingDirectory"); if (!Directory.Exists(usersBaseReportingDirectory)) { Directory.CreateDirectory(usersBaseReportingDirectory); } string fileName = Path.Combine(usersBaseReportingDirectory, "Issues_" + DateTime.Now.ToString("dd_MM_yyyy_hhmm") + ".xls"); using (ExcelXmlWriter excelXmlWriter = new ExcelXmlWriter(fileName) { Landscape = true }) { using (CmsEntities cee = new CmsEntities()) { cee.Configuration.LazyLoadingEnabled = true; IList<Issue> issues; List<int> ids = new List<int>(); ids.Add(1); if (System.Diagnostics.Debugger.IsAttached) { issues = (from x in cee.Issues select x).Take(20).ToList(); } else { issues = (from x in cee.Issues select x).ToList(); } CreateIssuesSummaryWorksheet(excelXmlWriter, issues); CreateActionsSummaryWorksheet(excelXmlWriter, issues); CreateMobilePlantIssuesWorksheet(excelXmlWriter, issues); CreateMechEquipWorksheet(excelXmlWriter, issues); CreateElectEquipWorksheet(excelXmlWriter, issues); CreatePipingIssuesWorksheet(excelXmlWriter, issues); CreateDocumentationWorksheet(excelXmlWriter, issues); excelXmlWriter.Close(); } } return fileName; }
/// <summary> /// Create the Report /// </summary> /// <param name="fileName">Filename to save Excel file to.</param> /// <param name="fdsId">The FDS id.</param> /// <param name="fdsName">Name of the FDS.</param> public void Create(string fileName, int fdsId, string fdsName) { ExcelXmlWriter excel = new ExcelXmlWriter(fileName, false); #region HardCoded Styles string[] Styles = { "<Style ss:ID=\"Border\">", " <Alignment ss:Vertical=\"Top\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\"/>", "</Style>", "<Style ss:ID=\"BorderCentreBold\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\" ss:Bold=\"1\"/>", "</Style>", "<Style ss:ID=\"BorderCentre\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\"/>", "</Style>", "<Style ss:ID=\"BorderCentreH\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Top\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\"/>", "</Style>", "<Style ss:ID=\"BorderCentreLeft\">", " <Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\"/>", "</Style>", "<Style ss:ID=\"BorderWrapTextCentreV\">", " <Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\"/>", "</Style>", "<Style ss:ID=\"BorderWrapText\">", " <Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\"/>", "</Style>", "<Style ss:ID=\"BorderBold\">", " <Alignment ss:Vertical=\"Top\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\" ss:Bold=\"1\"/>", "</Style>", "<Style ss:ID=\"BorderGray\">", " <Alignment ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\" ss:Bold=\"1\"/>", " <Interior ss:Color=\"#969696\" ss:Pattern=\"Solid\"/>", "</Style>", "<Style ss:ID=\"BorderLightGray\">", " <Alignment ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\" ss:Bold=\"1\"/>", " <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>", "</Style>", "<Style ss:ID=\"BorderLightGrayCentre\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\" ss:Bold=\"1\"/>", " <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>", "</Style>", "<Style ss:ID=\"BorderLightGrayCentreWrap\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" ss:WrapText=\"1\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\" ss:Bold=\"1\"/>", " <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>", "</Style>", // Colours "<Style ss:ID=\"BorderCentreCyan\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\"/>", " <Interior ss:Color=\"#00FFFF\" ss:Pattern=\"Solid\"/>", "</Style>", "<Style ss:ID=\"BorderCentreGrey\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\"/>", " <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>", "</Style>", "<Style ss:ID=\"BorderCentreMagenta\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\"/>", " <Interior ss:Color=\"#FF00FF\" ss:Pattern=\"Solid\"/>", "</Style>", "<Style ss:ID=\"BorderCentreBlack\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\" ss:Color=\"#FFFFFF\"/>", " <Interior ss:Color=\"#000000\" ss:Pattern=\"Solid\"/>", "</Style>", "<Style ss:ID=\"BorderCentreRed\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\"/>", " <Interior ss:Color=\"#FF0000\" ss:Pattern=\"Solid\"/>", "</Style>", "<Style ss:ID=\"BorderCentreWhite\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\"/>", "</Style>", "<Style ss:ID=\"BorderCentreYellow\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"8\"/>", " <Interior ss:Color=\"#FFFF00\" ss:Pattern=\"Solid\"/>", "</Style>", }; #endregion excel.WriteHeader(Styles); excel.Close(); }
internal string CreateOverdues(string usersBaseReportingDirectory) { if (String.IsNullOrEmpty(usersBaseReportingDirectory)) throw new ArgumentNullException("usersBaseReportingDirectory"); if (!Directory.Exists(usersBaseReportingDirectory)) { Directory.CreateDirectory(usersBaseReportingDirectory); } string fileName = Path.Combine(usersBaseReportingDirectory, "Overdue_Actions_" + DateTime.Now.ToString("dd_MM_yyyy_hhmm") + ".xls"); using (ExcelXmlWriter excelXmlWriter = new ExcelXmlWriter(fileName) { Landscape = true }) { using (CmsEntities cee = new CmsEntities()) { cee.Configuration.LazyLoadingEnabled = true; List<int> ids = new List<int>(); ids.Add(1); IList<Issue> issues = (from x in cee.Issues .Include("IssueType") .Include("IssueClassification") .Include("IssuePriority") .Include("IssueStatus") .Include("CurrentlyAssignedToUser") .Include("ModifiedByUser") .Include("InitiatedByUser") .Include("IssueRelatedPipes") from relatedAction in x.IssueRelatedActions where relatedAction.RequiredDate.HasValue && relatedAction.RequiredDate.Value < DateTime.Now select x).ToList(); CreateIssuesSummaryWorksheet(excelXmlWriter, issues); CreateActionsSummaryWorksheet(excelXmlWriter, issues); CreateMobilePlantIssuesWorksheet(excelXmlWriter, issues); CreateMechEquipWorksheet(excelXmlWriter, issues); CreateElectEquipWorksheet(excelXmlWriter, issues); CreatePipingIssuesWorksheet(excelXmlWriter, issues); CreateDocumentationWorksheet(excelXmlWriter, issues); excelXmlWriter.Close(); } } return fileName; }
/// <summary> /// Create the Report /// </summary> /// <param name="issueIdList">The issue id list.</param> /// <param name="filePath">The file path.</param> /// <returns></returns> public string Create(int[] issueIdList, string filePath) { if (issueIdList == null) throw new ArgumentNullException("issueIdList"); if (String.IsNullOrEmpty(filePath)) throw new ArgumentNullException("filePath"); if (!Directory.Exists(filePath)) { Directory.CreateDirectory(filePath); } string fileName = Path.Combine(filePath, "FilteredIssues_" + DateTime.Now.ToString("dd_MM_yyyy_hhmm") + ".xls"); using (ExcelXmlWriter excelXmlWriter = new ExcelXmlWriter(fileName) { Landscape = true }) { using (CmsEntities cee = new CmsEntities()) { cee.Configuration.LazyLoadingEnabled = true; IList<Issue> issues; if (System.Diagnostics.Debugger.IsAttached) { issues = (from x in cee.Issues where issueIdList.Contains(x.Id) select x).Take(20000).ToList(); } else { issues = (from x in cee.Issues where issueIdList.Contains(x.Id) select x).ToList(); } CreateIssuesSummaryWorksheet(excelXmlWriter, issues); CreateActionsSummaryWorksheet(excelXmlWriter, issues); CreateMobilePlantIssuesWorksheet(excelXmlWriter, issues); CreateMechEquipWorksheet(excelXmlWriter, issues); CreateElectEquipWorksheet(excelXmlWriter, issues); CreatePipingIssuesWorksheet(excelXmlWriter, issues); CreateDocumentationWorksheet(excelXmlWriter, issues); excelXmlWriter.Close(); } } return fileName; }
private string RunExport(string usersBaseReportingDirectory, IEnumerable<Pipe> pipes) { string fileName = Path.Combine(usersBaseReportingDirectory, "Pipes_" + DateTime.Now.ToString("dd_MM_yyyy_hhmm") + ".xls"); using (ExcelXmlWriter excelXmlWriter = new ExcelXmlWriter(fileName) { Landscape = true }) { using (CmsEntities cee = new CmsEntities()) { List<int> ids = new List<int>(); ids.Add(1); List<Document> documents = cee.Documents.ToList(); CreateWorksheet(excelXmlWriter, pipes, documents); excelXmlWriter.Close(); } } return fileName; }
/// <summary> /// Create the Report /// </summary> /// <param name="usersBaseReportingDirectory">Filename to save Excel file to.</param> public string Create(string usersBaseReportingDirectory) { if (string.IsNullOrEmpty(usersBaseReportingDirectory)) throw new ArgumentNullException("usersBaseReportingDirectory"); if (!Directory.Exists(usersBaseReportingDirectory)) { Directory.CreateDirectory(usersBaseReportingDirectory); } string fileName = Path.Combine(usersBaseReportingDirectory, "OverrideSummary_" + DateTime.Now.ToString("dd_MM_yyyy_hhmm") + ".xls"); using (CmsEntities cee = new CmsEntities()) { cee.Configuration.LazyLoadingEnabled = true; IList<IssueRelatedOverride> issueRelatedOverrides; if (IncludeRemovedOverrides) { // Include both removed and not removed overrides in the report issueRelatedOverrides = (from iro in cee.IssueRelatedOverrides orderby iro.IssueId select iro).ToList(); } else { // Include only not removed overrides in the report issueRelatedOverrides = (from iro in cee.IssueRelatedOverrides where iro.Removed == false orderby iro.IssueId select iro).ToList(); } using (ExcelXmlWriter excel = new ExcelXmlWriter(fileName, false)) { #region Style string[] Styles = { "<Style ss:ID=\"Border\">", " <Alignment ss:Vertical=\"Top\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>", "</Style>", "<Style ss:ID=\"BorderCentreBold\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>", "</Style>", "<Style ss:ID=\"BorderCentre\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>", "</Style>", "<Style ss:ID=\"BorderCentreRed\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>", " <Interior ss:Color=\"#FF0000\" ss:Pattern=\"Solid\"/>", "</Style>", "<Style ss:ID=\"BorderCentreH\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Top\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>", "</Style>", "<Style ss:ID=\"BorderCentreLeft\">", " <Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>", "</Style>", "<Style ss:ID=\"BorderWrapTextCentreV\">", " <Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>", "</Style>", "<Style ss:ID=\"BorderWrapText\">", " <Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>", "</Style>", "<Style ss:ID=\"BorderBold\">", " <Alignment ss:Vertical=\"Top\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>", "</Style>", "<Style ss:ID=\"BorderGray\">", " <Alignment ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>", " <Interior ss:Color=\"#969696\" ss:Pattern=\"Solid\"/>", "</Style>", "<Style ss:ID=\"BorderLightGray\">", " <Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>", " <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>", "</Style>", "<Style ss:ID=\"BorderLightGrayCentre\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>", " <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>", "</Style>", "<Style ss:ID=\"BorderCentreBoldNR\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>", "</Style>", "<Style ss:ID=\"BorderCentreWingdings2\">", " <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>", " <Borders>", " <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>", " </Borders>", " <Font ss:FontName=\"Wingdings 2\" x:CharSet = \"2\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>", "</Style>" }; #endregion excel.WriteHeader(Styles); _pageBreakRows.Clear(); excel.Landscape = true; excel.PrintScale = 100; //excel.PaperSize = 8; // A3 excel.SetPageMargins(0.5, 0.5, 0.5, 0.5); excel.OpenWorkSheet("Override Summary"); excel.PrintTitle(1, 1); double[] colWidths = new[] { 52.5, //Issue ID 76.5, //Element Tag 130, //Description 69, //Interlock Type 69, //Interlock No. 130, //Interlock Description 118.5, //Interlock Cause 118.5, //Approval (Area Engineer / Tech) 118.5,//"Approval (Lead Engineer / Supervisor) 118.5,//"Approval (Manager) 69, //Assigned To 118.5,//Applied 118.5, //Target Removal 118.5, //Removed 130 //Notes }; excel.OpenTable(200, colWidths.Length, colWidths); int row = 1; excel.StartTitle(); excel.OpenRow(row++, 20); excel.WriteCell(new ExcelValueObject("Issue ID", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Element Tag", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Description", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Interlock Type", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Interlock No.", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Interlock Description", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Interlock Cause", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Approval (Area Engineer / Tech)", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Approval (Lead Engineer / Supervisor)", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Approval (Manager)", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Assigned To", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Applied", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Target Removal", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Removed", ExcelCellFormat.String, "BorderLightGray")); excel.WriteCell(new ExcelValueObject("Notes", ExcelCellFormat.String, "BorderLightGray")); excel.CloseRow(); excel.EndTitle(); foreach (IssueRelatedOverride relatedOverride in issueRelatedOverrides) { excel.OpenRow(row++, 17); string intDescription, number, type, xtag, desc, cause; int ControlSystemId; int? dummy; int interlockId = relatedOverride.InterlockId; CmsWebService.GetInterlockInfo(interlockId, out ControlSystemId, out intDescription, out number, out type, out dummy, out cause, _artDbConnectionString); CmsWebService.GetControlSystemInfo(ControlSystemId, out xtag, out desc, _artDbConnectionString); excel.WriteCell(new ExcelValueObject(relatedOverride.IssueId.ToString(), ExcelCellFormat.Number, "BorderCentre")); excel.WriteCell(new ExcelValueObject(xtag, ExcelCellFormat.String, "BorderCentre")); excel.WriteCell(new ExcelValueObject(desc, ExcelCellFormat.String, "BorderCentre")); excel.WriteCell(new ExcelValueObject(type, ExcelCellFormat.String, "BorderCentre")); excel.WriteCell(new ExcelValueObject(number, ExcelCellFormat.Number, "BorderCentre")); excel.WriteCell(new ExcelValueObject(intDescription, ExcelCellFormat.String, "BorderCentre")); excel.WriteCell(new ExcelValueObject(cause, ExcelCellFormat.String, "BorderCentre")); excel.WriteCell(new ExcelValueObject(CmsWebService.GetUserName(relatedOverride.Issue.CurrentlyAssignedToId, _issueDbConnectionString), ExcelCellFormat.String, "BorderCentre")); excel.WriteCell(new ExcelValueObject(CmsWebService.GetTimestampUserString(relatedOverride.AppliedDate, relatedOverride.AppliedById, _issueDbConnectionString), ExcelCellFormat.String, "BorderCentre")); // Highlight an override that's overdue for removal string targetRemovalStyle = "BorderCentre"; if (relatedOverride.TargetRemovalDate.HasValue) { if (DateTime.Now.Date > relatedOverride.TargetRemovalDate.Value.Date && !relatedOverride.Removed) { targetRemovalStyle = "BorderCentreRed"; } } excel.WriteCell(new ExcelValueObject(CmsWebService.GetDateString(relatedOverride.TargetRemovalDate), ExcelCellFormat.String, targetRemovalStyle)); excel.WriteCell(new ExcelValueObject(CmsWebService.GetTimestampUserString(relatedOverride.RemovedDate, relatedOverride.RemovedById, _issueDbConnectionString), ExcelCellFormat.String, "BorderCentre")); excel.WriteCell(new ExcelValueObject(relatedOverride.Notes, ExcelCellFormat.String, "BorderWrapTextCentreV")); excel.CloseRow(); } excel.CloseTable(); excel.WritePageBreaks(_pageBreakRows.ToArray()); excel.CloseWorkSheet(); excel.Close(); } } return fileName; }
private string RunExport(CmsEntities cee, string usersBaseReportingDirectory, IList<PipeComponent> allPipeComponents) { string fileName = Path.Combine(usersBaseReportingDirectory, "PipeComponents_" + DateTime.Now.ToString("dd_MM_yyyy_hhmm") + ".xls"); using (ExcelXmlWriter excelXmlWriter = new ExcelXmlWriter(fileName) { Landscape = true }) { List<int> ids = new List<int>(); ids.Add(1); IList<PipeComponentType> allPipeComponentTypes = (from x in cee.PipeComponentTypes select x).ToList(); IList<PipeProperty> allComponentProperties = (from p in cee.PipeProperties select p).ToList(); foreach (PipeComponentType componentType in allPipeComponentTypes) { IList<PipeComponent> pipeComponents = (from x in allPipeComponents where x.PipeComponentType.Id == componentType.Id select x).ToList(); if (pipeComponents.Count > 0) { IList<PipeProperty> componentProperties = new List<PipeProperty>(); int[] componentpropertyIds = (from x in cee.PipeComponentTypeProperties where x.ComponentTypeId == componentType.Id select x.PipePropertyId).ToArray(); if (componentpropertyIds.Length > 0) { componentProperties = (from p in allComponentProperties where componentpropertyIds.Contains(p.Id) select p).ToList(); } CreateWorksheet(componentType.Name, excelXmlWriter, pipeComponents, componentProperties); } } excelXmlWriter.Close(); } return fileName; }