public void FormWriterWrites()
{
var fileName = @"D:\Test\ThisIsATest.xlsx";
var eventImageFileName = @"C:\src\storytree\src\StoryTree.IO\giraffe-png-images-8.png";
string eventName = "TestEvent";
string expertName = "Test Expert";
DateTime date = DateTime.Now;
double[] waterLevels = { 1.0, 2.0, 3.0, 4.0 };
double[] frequencies = { 0.0001, 0.001, 0.01, 0.1 };
var writer = new ElicitationFormWriter();
var form = new DotForm
{
Date = date,
GetFileStream = () => new FileStream(eventImageFileName, FileMode.Open),
EventTreeName = eventName,
ExpertName = expertName,
Nodes = new DotNode[]
{
CreateNode("Knoop 1", waterLevels, frequencies),
CreateNode("Knoop 2", waterLevels, frequencies),
}
};
writer.WriteForm(fileName, new[] { form });
}
private void WriteWorksheet(WorkbookPart workbookPart, Sheets sheets, DotForm form)
{
//Add data to first sheet
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Worksheet worksheet = new Worksheet();
worksheetPart.Worksheet = worksheet;
Columns columns = new Columns();
columns.Append(new Column {
Min = 1, Max = 2, Width = 4, CustomWidth = true
});
columns.Append(new Column {
Min = 3, Max = 7, Width = 12, CustomWidth = true
});
columns.Append(new Column {
Min = 8, Max = 8, Width = 40, CustomWidth = true
});
columns.Append(new Column {
Min = 9, Max = 9, Width = 20, CustomWidth = true
});
columns.Append(new Column {
Min = 10, Max = 11, Width = 4, CustomWidth = true
});
var sheetData = new SheetData();
var dataValidations = new DataValidations();
worksheet.Append(columns);
worksheet.Append(sheetData);
var mergeCells = CreateOrGetMergeCells(worksheet);
worksheet.Append(dataValidations);
Sheet sheet = new Sheet
{
Name = form.EventTreeName,
SheetId = (uint)sheets.Count() + 1,
Id = workbookPart.GetIdOfPart(worksheetPart),
};
sheets.Append(sheet);
WriteHeader(sheetData, mergeCells);
WriteEventHeader(form, sheetData, mergeCells);
WriteExpertInformation(form, sheetData);
WriteElicitationCodeInformation(sheetData, mergeCells);
AddImage(form.GetFileStream, worksheetPart);
var rowNumber = WriteNodes(form, sheetData, dataValidations);
WriteSheetBottom(sheetData, rowNumber);
worksheetPart.Worksheet.Save();
}
private static Dictionary <DotNode, NodeValidationResult> ValidateNodes(DotForm form, Project project)
{
var results = new Dictionary <DotNode, NodeValidationResult>();
if (form.Nodes == null || !form.Nodes.Any())
{
// TODO: Return validation result instead of null
return(null);
}
var eventTree = project.EventTrees.First(et => et.Name == form.EventTreeName);
foreach (var formNode in form.Nodes)
{
var estimatedTreeEvent = eventTree.MainTreeEvent.FindTreeEvent(treeEvent => treeEvent.Name == formNode.NodeName);
if (estimatedTreeEvent == null)
{
results[formNode] = NodeValidationResult.NodeNotFound;
}
foreach (var estimate in formNode.Estimates)
{
var condition =
project.HydraulicConditions.FirstOrDefault(hc =>
Math.Abs(hc.WaterLevel - estimate.WaterLevel) < 1e-6);
if (condition == null)
{
results[formNode] = NodeValidationResult.WaterLevelNotFound;
break;
}
if (!(Math.Abs(condition.Probability - estimate.Frequency) < 1e-8))
{
results[formNode] = NodeValidationResult.InvalidFrequencyForWaterLevel;
}
if (estimate.LowerEstimate < 0 || estimate.LowerEstimate > 7 ||
estimate.BestEstimate < 0 || estimate.BestEstimate > 7 ||
estimate.UpperEstimate < 0 || estimate.UpperEstimate > 7)
{
results[formNode] = NodeValidationResult.InvalidEstimationValue;
break;
}
}
if (!results.ContainsKey(formNode))
{
results[formNode] = NodeValidationResult.Valid;
}
}
return(results);
}
private static ExpertValidationResult ValidateExperts(DotForm form, Project project)
{
if (project.Experts == null || !project.Experts.Any())
{
return(ExpertValidationResult.NoExperts);
}
if (project.Experts.Any(e => e.Name == form.ExpertName))
{
return(ExpertValidationResult.Valid);
}
return(ExpertValidationResult.ExpertNotFound);
}
private static EventTreesValidationResult ValidateEventTrees(DotForm form, Project project)
{
if (project.EventTrees == null || !project.EventTrees.Any())
{
return(EventTreesValidationResult.NoEventTrees);
}
if (project.EventTrees.Any(e => e.Name == form.EventTreeName))
{
return(EventTreesValidationResult.Valid);
}
return(EventTreesValidationResult.EventTreeNotFound);
}
private void WriteExpertInformation(DotForm form, SheetData sheetData)
{
AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, 7,
ConstructCell("Expert:", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
ConstructCell(form.ExpertName, CellValues.String, StyleSheetLibrary.TableBodyStyleNormalIndex));
AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, 8,
ConstructCell("Datum:", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
new Cell
{
CellValue = new CellValue(form.Date.ToOADate().ToString(CultureInfo.InvariantCulture)),
StyleIndex = StyleSheetLibrary.DateTimeBodyStyle
});
AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, 9);
AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, 10);
}
private void WriteEventHeader(DotForm form, SheetData sheetData, MergeCells mergeCells)
{
AddRow(sheetData, 0, 5,
ConstructCell("Gebeurtenis:", CellValues.String, StyleSheetLibrary.TitleStyleIndex),
EmptyCell(StyleSheetLibrary.TitleStyleIndex),
ConstructCell(form.EventTreeName, CellValues.String, StyleSheetLibrary.TitleStyleIndex),
EmptyCell(StyleSheetLibrary.TitleStyleIndex),
EmptyCell(StyleSheetLibrary.TitleStyleIndex),
EmptyCell(StyleSheetLibrary.TitleStyleIndex),
EmptyCell(StyleSheetLibrary.TitleStyleIndex),
EmptyCell(StyleSheetLibrary.DefaultStyleIndex));
mergeCells.Append(new MergeCell {
Reference = new StringValue("C5:D5")
});
mergeCells.Append(new MergeCell {
Reference = new StringValue("E5:I5")
});
AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, 6);
}
public static DotFormValidationResult Validate(DotForm form, Project project)
{
if (project == null)
{
throw new ArgumentNullException(nameof(project));
}
var nodesValidationResult = ValidateNodes(form, project);
var validationResult = new DotFormValidationResult
{
EventTreesValidation = ValidateEventTrees(form, project),
ExpertValidation = ValidateExperts(form, project),
};
if (validationResult.EventTreesValidation == EventTreesValidationResult.Valid &&
validationResult.ExpertValidation == ExpertValidationResult.Valid)
{
validationResult.NodesValidationResult = nodesValidationResult;
}
return(validationResult);
}
private uint WriteNodes(DotForm form, SheetData sheetData, DataValidations dataValidations)
{
uint rowNumber = 21;
// Write table parts
foreach (DotNode node in form.Nodes)
{
var estimates = node.Estimates.OrderBy(n => n.WaterLevel).ToArray();
AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, rowNumber++);
// TODO: merge all cells above table
AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, rowNumber++,
ConstructCell(node.NodeName, CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex));
AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, rowNumber++,
ConstructCell("Waterstand", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
ConstructCell("Frequentie", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
ConstructCell("Onder", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
ConstructCell("Gemiddeld", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
ConstructCell("Boven", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
ConstructCell("Weergave", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
ConstructCell("Toelichting", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex));
var styleIndex = StyleSheetLibrary.TableBodyStyleNormalIndex;
foreach (var estimate in estimates)
{
AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, rowNumber++,
ConstructCell(estimate.WaterLevel, CellValues.Number, styleIndex),
ConstructCell(estimate.Frequency, CellValues.Number, styleIndex),
ConstructCell(estimate.LowerEstimate == 0 ? double.NaN : estimate.LowerEstimate, CellValues.Number,
styleIndex),
ConstructCell(estimate.BestEstimate == 0 ? double.NaN : estimate.BestEstimate, CellValues.Number,
styleIndex),
ConstructCell(estimate.UpperEstimate == 0 ? double.NaN : estimate.UpperEstimate, CellValues.Number,
styleIndex),
ConstructCell(double.NaN, CellValues.Number, styleIndex),
ConstructCell("", CellValues.String, styleIndex));
dataValidations.Append(new DataValidation
{
AllowBlank = true,
Type = DataValidationValues.List,
Formula1 = new Formula1(elicitationCodeCellRange),
SequenceOfReferences = new ListValue <StringValue>
{
InnerText = "E" + (rowNumber - 1)
}
});
dataValidations.Append(new DataValidation
{
AllowBlank = true,
Type = DataValidationValues.List,
Formula1 = new Formula1(elicitationCodeCellRange),
SequenceOfReferences = new ListValue <StringValue>
{
InnerText = "F" + (rowNumber - 1)
}
});
dataValidations.Append(new DataValidation
{
AllowBlank = true,
Type = DataValidationValues.List,
Formula1 = new Formula1(elicitationCodeCellRange),
SequenceOfReferences = new ListValue <StringValue>
{
InnerText = "G" + (rowNumber - 1)
}
});
styleIndex = styleIndex == StyleSheetLibrary.TableBodyStyleNormalIndex
? StyleSheetLibrary.TableAlternateBodyStyleIndex
: StyleSheetLibrary.TableBodyStyleNormalIndex;
}
}
return(rowNumber);
}
