/// <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();
}
private void CreatePipingIssuesWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Issue> issues)
{
excelXmlWriter.OpenWorkSheet("Issues - Piping");
double[] colWidths = new[]
{
80d, 110d, 80d, 220d, 200d
};
excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);
int row = 1;
string[] header = new[]
{
"ID", "Title", "Pipe", "Implemented","Tested"
};
excelXmlWriter.WriteRow(row++, header, true);
foreach (Issue issue in issues)
{
foreach (IssueRelatedPipe issueRelatedPipe in issue.IssueRelatedPipes)
{
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1 CMS Issue ID
values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//2 CMS Issue Title
values.Add(new ExcelValueObject(issue.Name));
//3 PIPE ID
values.Add(new ExcelValueObject(issueRelatedPipe.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//5 implemented Yes/No
if (issueRelatedPipe.Implemented)
{
values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
else
{
values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
//6 TESTED
if (issueRelatedPipe.Tested)
{
values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
else
{
values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
excelXmlWriter.WriteRow(row++, values.ToArray());
}
}
excelXmlWriter.CloseTable();
excelXmlWriter.CloseWorkSheet();
}
private void CreateMobilePlantIssuesWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Issue> issues)
{
excelXmlWriter.OpenWorkSheet("Issues - Mobile Plant");
double[] colWidths = new[]
{
80d, 110d, 80d, 220d, 200d,
75d
};
excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);
int row = 1;
string[] header = new[]
{
"ID", "Title", "MobilePlant", "Mobile Plant Description", "Implemented",
"Tested"
};
excelXmlWriter.WriteRow(row++, header, true);
foreach (Issue issue in issues)
{
foreach (IssueRelatedMobilePlant relatedMobilePlant in issue.IssueRelatedMobilePlants)
{
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1 CMS Issue ID
values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//2 CMS Issue Title
values.Add(new ExcelValueObject(issue.Name));
//3 CP ID
values.Add(new ExcelValueObject(relatedMobilePlant.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//4 CP Desc
values.Add(new ExcelValueObject(relatedMobilePlant.MobilePlant.Description));
//5 implemented Yes/No
if (relatedMobilePlant.Implemented)
{
values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
else
{
values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
//6 tested
if (relatedMobilePlant.Tested)
{
values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
else
{
values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
excelXmlWriter.WriteRow(row++, values.ToArray());
}
}
excelXmlWriter.CloseTable();
excelXmlWriter.CloseWorkSheet();
}
private void CreateMechEquipWorksheet(ExcelXmlWriter excel, IEnumerable<Issue> matchList)
{
excel.OpenWorkSheet("Issues - Mech Equipment");
double[] colWidths = new[]
{
30d, 180d, 150d, 150d, 150d,
100d, 100d
};
excel.OpenTable(200, colWidths.Length, colWidths, 1);
int row = 1;
string[] header = new[]
{
"ID", "Title", "Mech Equipment", "Mech Equipment Description", "Maint Sys ID",
"Implemented", "Tested"
};
excel.WriteRow(row++, header, true);
using (CmsEntities cee = new CmsEntities())
{
cee.Configuration.LazyLoadingEnabled = true;
foreach (Issue issue in matchList)
{
var rsq = from rs in cee.IssueRelatedMechanicalEquipments
where rs.IssueId == issue.Id
select rs;
foreach (IssueRelatedMechanicalEquipment rs in rsq.ToList())
{
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1
values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//2
values.Add(new ExcelValueObject(issue.Name));
//3
values.Add(new ExcelValueObject(rs.MechanicalEquipment != null ? rs.MechanicalEquipment.Name : ""));
//4
values.Add(new ExcelValueObject(rs.MechanicalEquipment != null ? rs.MechanicalEquipment.Description : ""));
//5
values.Add(new ExcelValueObject(rs.MechanicalEquipment != null ? rs.MechanicalEquipment.MaintSystemId : ""));
//6
values.Add(new ExcelValueObject(CmsWebService.GetTimestampUserString(rs.ImplementedDate, rs.ImplementedById)));
//7
values.Add(new ExcelValueObject(CmsWebService.GetTimestampUserString(rs.TestedDate, rs.TestedById)));
excel.WriteRow(row++, values.ToArray());
}
}
}
excel.CloseTable();
excel.CloseWorkSheet();
}
private void CreateActionsSummaryWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Issue> issues)
{
excelXmlWriter.OpenWorkSheet("Actions - Summary");
//excelXmlWriter.WriteHeader(Styles);//does not work....
double[] colWidths = new[]
{
80d, 110d, 80d, 220d, 200d,
110d, 110d, 150d, 120d, 100d,
575d
};
excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);
int row = 1;
string[] header = new[]
{
"CMS Issue ID", "CMS Issue Title", "Action ID", "Action Description", "Action Initiated By",
"Action Initiated On", "Action Assigned To", "Action Required By", "Action Days Overdue","Action Implemented",
"Action Notes"
};
excelXmlWriter.WriteRow(row++, header, true);
foreach (Issue issue in issues)
{
foreach (IssueRelatedAction action in issue.IssueRelatedActions)
{
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1 CMS Issue ID
values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//2 CMS Issue Title
values.Add(new ExcelValueObject(issue.Name));
//3 Action ID
values.Add(new ExcelValueObject(action.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//4 Action Desc
values.Add(new ExcelValueObject(action.Description));
//5 Action Initiated By
values.Add(new ExcelValueObject(action.InitiatedByUser.UserName));
//6 Action initiated on
if (action.InitiatedDate.HasValue)
{
values.Add(new ExcelValueObject(action.InitiatedDate.Value.ToString("s"), ExcelCellFormat.DateTime, ExcelCellStyle.Date));
}
else
{
values.Add(new ExcelValueObject(String.Empty));
}
//7 Action Assigned To
if (action.AssignedToUser != null)
{
values.Add(new ExcelValueObject(action.AssignedToUser.UserName));
}
else
{
values.Add(new ExcelValueObject(String.Empty));
}
//8 Required By
if (action.RequiredDate.HasValue)
{
values.Add(new ExcelValueObject(action.RequiredDate.Value.ToString("s"), ExcelCellFormat.DateTime, ExcelCellStyle.Date));
}
else
{
values.Add(new ExcelValueObject(String.Empty));
}
//9 Days overdue
if (action.RequiredDate.HasValue)
{
TimeSpan ts = DateTime.Now - action.RequiredDate.Value;
if (ts.Days < 0)
{
values.Add(new ExcelValueObject(ts.Days.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
}
else
{
//RED BACKGROUND...cannot get to work using stupid styles...
values.Add(new ExcelValueObject(ts.Days.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
}
}
else
{
values.Add(new ExcelValueObject(String.Empty));
}
//10 implemented Yes/No
if (action.Implemented)
{
values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
else
{
values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
//11 Notes
values.Add(new ExcelValueObject(action.Notes, ExcelCellFormat.String, ExcelCellStyle.Normal));
excelXmlWriter.WriteRow(row++, values.ToArray());
}
}
excelXmlWriter.CloseTable();
excelXmlWriter.CloseWorkSheet();
}
private void CreateIssuesSummaryWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Issue> matchList)
{
excelXmlWriter.OpenWorkSheet("Issues - Summary");
double[] colWidths = new[]
{
30d, 150d, 150d, 150d, 150d,
75d, 100d, 100d, 75d, 75d,
75d, 75d, 75d, 110d, 110d,
110d, 110d, 180d, 180d, 180d,
165d, 165d, 165d, 165d, 165d,
165d, 90d, 90d, 90d, 90d,110d,
110d
};
excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);
int row = 1;
string[] header = new[]
{
"ID", "Title", "Description", "Reason", "Suggested Solution",
"Status", "Priority", "Classification", "Issue Type", "Risk Rating",
"Category", "Assigned To", "Initiated By", "Initiated On",
"Requested By", "Designed By", "Approval",
"Commissioning Engineer Approval", "Operations Engineer Approval",
"Estimated Implementation (Hrs)", "Actual Implementation (Hrs)",
"Estimated Testing (Hrs)", "Actual Testing (Hrs)", "Estimated Documentation (Hrs)",
"Actual Documentation (Hrs)", "Implemented", "Tested", "Documented", "Actions",
"Last Modified By",
"Last Modified On"
};
excelXmlWriter.WriteRow(row++, header, true);
foreach (Issue issue in matchList)
{
CmsWebService.IssueSummaryTotals totals = CmsWebService.GetIssuesSummaryTotals(issue.Id);
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1
values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//2
values.Add(new ExcelValueObject(issue.Name));
//3
values.Add(new ExcelValueObject(issue.Description));
//4
values.Add(new ExcelValueObject(issue.Reason));
//5
values.Add(new ExcelValueObject(issue.SuggestedSolution));
//6
values.Add(new ExcelValueObject(CmsWebService.GetStatusName(issue.IssueStatusId)));
//7
values.Add(new ExcelValueObject(CmsWebService.GetPriorityName(issue.IssuePriorityId)));
//8
values.Add(new ExcelValueObject(CmsWebService.GetClassificationName(issue.IssueClassificationId)));
//9
values.Add(new ExcelValueObject(CmsWebService.GetIssueTypeName(issue.IssueTypeId)));
//10
values.Add(new ExcelValueObject(CmsWebService.GetFinalRiskRatingText(issue.Id)));
//11
values.Add(new ExcelValueObject(CmsWebService.GetAssignedCategoryNames(issue)));
//12
values.Add(new ExcelValueObject(CmsWebService.GetUserName(issue.CurrentlyAssignedToId)));
//13
values.Add(new ExcelValueObject(CmsWebService.GetUserName(issue.InitiatedById)));
//14
values.Add(new ExcelValueObject(issue.InitiatedDate.ToString("s"), ExcelCellFormat.DateTime, ExcelCellStyle.Date));
//15
values.Add(new ExcelValueObject(issue.RequestedBy));
//16
values.Add(new ExcelValueObject(CmsWebService.GetUserName(issue.ProjectSupervisorId)));
//17
values.Add(new ExcelValueObject(GetApproversText(issue)));
//18
values.Add(new ExcelValueObject(String.Empty));//now redundant
//19
values.Add(new ExcelValueObject(String.Empty));
//20
if (issue.IssueTracking.ImplementedEstimate != null)
values.Add(new ExcelValueObject(issue.IssueTracking.ImplementedEstimate.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
else
values.Add(new ExcelValueObject(String.Empty));
//21
if (issue.IssueTracking.ImplementedActual != null)
values.Add(new ExcelValueObject(issue.IssueTracking.ImplementedActual.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
else
values.Add(new ExcelValueObject(String.Empty));
//22
if (issue.IssueTracking.TestedEstimate != null)
values.Add(new ExcelValueObject(issue.IssueTracking.TestedEstimate.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
else
values.Add(new ExcelValueObject(String.Empty));
//23
if (issue.IssueTracking.TestedActual != null)
values.Add(new ExcelValueObject(issue.IssueTracking.TestedActual.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
else
values.Add(new ExcelValueObject(String.Empty));
//24
if (issue.IssueTracking.DocumentedEstimate != null)
values.Add(new ExcelValueObject(issue.IssueTracking.DocumentedEstimate.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
else
values.Add(new ExcelValueObject(String.Empty));
//25
if (issue.IssueTracking.DocumentedActual != null)
values.Add(new ExcelValueObject(issue.IssueTracking.DocumentedActual.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
else
values.Add(new ExcelValueObject(String.Empty));
//26
if (totals.ImplementedTotal == 0)
values.Add(new ExcelValueObject(String.Empty));
else
values.Add(new ExcelValueObject(totals.ImplementedPercent.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Percent));
//27
if (totals.TestedTotal == 0)
values.Add(new ExcelValueObject(String.Empty));
else
values.Add(new ExcelValueObject(totals.TestedPercent.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Percent));
//28
if (totals.DocTotal == 0)
values.Add(new ExcelValueObject(String.Empty));
else
values.Add(new ExcelValueObject(totals.DocPercent.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Percent));
//29 ACTIONS
if (totals.ImplementedTotal == 0)
values.Add(new ExcelValueObject(String.Empty));
else
values.Add(new ExcelValueObject(totals.ImplementedPercent.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Percent));
//30
values.Add(new ExcelValueObject(CmsWebService.GetUserName(issue.ModifiedById)));
//31
values.Add(new ExcelValueObject(issue.ModifiedDate.ToString("s"), ExcelCellFormat.DateTime, ExcelCellStyle.Date));
//end
excelXmlWriter.WriteRow(row++, values.ToArray());
}
excelXmlWriter.CloseTable();
excelXmlWriter.CloseWorkSheet();
}
/// <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;
}
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;
}
private void CreateWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Pipe> matchList, IList<Document> documents)
{
excelXmlWriter.OpenWorkSheet("Pipes");
double[] colWidths = new[]
{
30d, 150d, 150d, 150d, 150d,
75d, 100d, 100d, 75d
};
excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);
int row = 1;
string[] header = new[]
{
"Area", "Sequence No.", "Pipe Class", "Size (mm)", "Fluid Code", "Special Feature", "P&ID", "From", "To"
};
excelXmlWriter.WriteRow(row++, header, true);
foreach (Pipe pipe in matchList)
{
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1
values.Add(new ExcelValueObject(pipe.Area.AreaNumber.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//2
values.Add(new ExcelValueObject(pipe.SequenceNo.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//3
values.Add(new ExcelValueObject(pipe.PipeClass.Name));
//4
values.Add(new ExcelValueObject(pipe.PipeSize.Name));
//5
values.Add(new ExcelValueObject(pipe.PipeFluidCode.Name));
//6
values.Add(new ExcelValueObject(pipe.PipeSpecialFeature.Name));
//7
Document document = (from d in documents where d.Id == pipe.PIDDocumentId select d).FirstOrDefault();
if (document != null)
{
values.Add(new ExcelValueObject(document.Name));
}
else
{
values.Add(new ExcelValueObject(""));
}
//8
values.Add(new ExcelValueObject(pipe.From));
//9
values.Add(new ExcelValueObject(pipe.To));
//end
excelXmlWriter.WriteRow(row++, values.ToArray());
}
excelXmlWriter.CloseTable();
excelXmlWriter.CloseWorkSheet();
}
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 void CreateWorksheet(string componentTypeName, ExcelXmlWriter excelXmlWriter, IEnumerable<PipeComponent> matchList, IList<PipeProperty> properties)
{
excelXmlWriter.OpenWorkSheet(componentTypeName);
List<string> header = new List<string>
{
"Pipe Name",
"Component Tag",
"Component Description"
};
List<double> colWidths = new List<double> { 80d, 150d, 150d };
//dynamic properties
header.AddRange(properties.Select(property => property.Name));
//dynamic cols - set widths
if (header.Count > colWidths.Count)
{
int k = header.Count - colWidths.Count;
for (int i = 0; i < k; i++)
{
colWidths.Add(80);
}
}
excelXmlWriter.OpenTable(200, colWidths.Count, colWidths.ToArray(), 1);
int row = 1;
excelXmlWriter.WriteRow(row++, header.ToArray(), true);
foreach (PipeComponent pipeComponent in matchList)
{
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1
values.Add(new ExcelValueObject(pipeComponent.Pipe.Name));
//2
values.Add(new ExcelValueObject(pipeComponent.Name));
//3
values.Add(new ExcelValueObject(pipeComponent.Description));
//4
foreach (PipeProperty pipeComponentProperty in properties)
{
PipePropertyValue propertyValue = (from pv in pipeComponent.PipePropertyValues
where (pv.PipePropertyId == pipeComponentProperty.Id) && (pv.ComponentId==pipeComponent.Id)
select pv).FirstOrDefault();
if (propertyValue != null)
{
values.Add(new ExcelValueObject(propertyValue.Value, ExcelCellFormat.String));
}
else
{
values.Add(new ExcelValueObject(string.Empty, ExcelCellFormat.String));
}
}
//end
excelXmlWriter.WriteRow(row++, values.ToArray());
}
excelXmlWriter.CloseTable();
excelXmlWriter.CloseWorkSheet();
}
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;
}
