private ExcelTemplateDefinition Execute(string templateName, ExcelInterop.Range templateDeclarationFirstCell)
{
ExcelInterop.Worksheet worksheet = null;
try
{
if (string.IsNullOrEmpty(templateName))
{
throw new EtkException("Template name cannot be null or empty.");
}
if (templateDeclarationFirstCell == null)
{
throw new EtkException("Template caller cannot be null.");
}
//Get template option
XmlExcelTemplateOption xmlTemplateOption = (templateDeclarationFirstCell.Value2 as string).Deserialize <XmlExcelTemplateOption>();
TemplateOption templateOption = new TemplateOption(xmlTemplateOption);
// Get the template end.
worksheet = templateDeclarationFirstCell.Worksheet;
ExcelInterop.Range templateDeclarationLastRange = worksheet.Cells.Find(string.Format(TEMPLATE_END_FORMAT, templateName), Type.Missing, ExcelInterop.XlFindLookIn.xlValues, ExcelInterop.XlLookAt.xlPart, ExcelInterop.XlSearchOrder.xlByRows, ExcelInterop.XlSearchDirection.xlNext, false);
if (templateDeclarationLastRange == null)
{
throw new EtkException($"Cannot find the end of template '{templateName.EmptyIfNull()}' in sheet '{worksheet.Name.EmptyIfNull()}'");
}
ExcelTemplateDefinition excelTemplateDefinition = new ExcelTemplateDefinition(templateDeclarationFirstCell, templateDeclarationLastRange, templateOption);
ExcelTemplateDefinitionPart header, body, footer;
ParseTemplate(excelTemplateDefinition, ref worksheet, out header, out body, out footer);
excelTemplateDefinition.ExcelInit(header, body, footer);
return(excelTemplateDefinition);
}
catch (Exception ex)
{
throw new EtkException($"Cannot create the template '{templateName.EmptyIfNull()}'. {ex.Message}");
}
finally
{
if (worksheet != null)
{
ExcelApplication.ReleaseComObject(worksheet);
worksheet = null;
}
}
}
private ExcelTemplateDefinitionPart Execute(ExcelTemplateDefinition excelTemplateDefinition, TemplateDefinitionPartType partType, ExcelInterop.Range firstRange, ExcelInterop.Range lastRange)
{
ExcelTemplateDefinitionPart part = new ExcelTemplateDefinitionPart(excelTemplateDefinition, partType, firstRange, lastRange);
for (int rowId = 0; rowId < part.DefinitionCells.Rows.Count; rowId++)
{
List <int> posLinks = null;
ExcelInterop.Range row = part.DefinitionCells.Rows[rowId + 1];
for (int cellId = 0; cellId < row.Cells.Count; cellId++)
{
ExcelInterop.Range cell = row.Cells[cellId + 1];
IDefinitionPart definitionPart = AnalyzeCell(part, cell);
part.DefinitionParts[rowId, cellId] = definitionPart;
if (definitionPart is LinkedTemplateDefinition)
{
if (posLinks == null)
{
posLinks = new List <int>();
}
posLinks.Add(cellId);
}
if (definitionPart is IBindingDefinition)
{
if (((IBindingDefinition)definitionPart).IsMultiLine)
{
part.ContainMultiLinesCells = true;
}
if (((IBindingDefinition)definitionPart).OnAfterRendering != null)
{
if (part.OnAfterRendering == null)
{
part.OnAfterRendering = new List <EventCallback>();
}
part.OnAfterRendering.Add(((IBindingDefinition)definitionPart).OnAfterRendering);
}
}
}
part.PositionLinkedTemplates.Add(posLinks);
}
return(part);
}
/// <summary>Create a binding definition from a cell value</summary>
private IBindingDefinition CreateBindingDefinition(ExcelTemplateDefinitionPart templateDefinitionPart, string value, string trimmedValue)
{
ExcelTemplateDefinition excelTemplateDefinition = templateDefinitionPart.Parent as ExcelTemplateDefinition;
IBindingDefinition ret = null;
if (trimmedValue.StartsWith(ExcelBindingDefinitionButton.BUTTON_TEMPLATE_PREFIX))
{
ret = ExcelBindingDefinitionButton.CreateInstance(excelTemplateDefinition, trimmedValue);
}
else if (trimmedValue.StartsWith(ExcelBindingDefinitionCheckBox.CHECKBOX_TEMPLATE_PREFIX))
{
ret = ExcelBindingDefinitionCheckBox.CreateInstance(excelTemplateDefinition, trimmedValue);
}
else if (trimmedValue.StartsWith(ExcelBindingDefinitionFormulaResult.FORMULA_RESULT_PREFIX))
{
ret = ExcelBindingDefinitionFormulaResult.CreateInstance(excelTemplateDefinition, trimmedValue);
}
else if (trimmedValue.StartsWith(ExcelBindingDefinitionNamedRange.NAMEDRANGE_TEMPLATE_PREFIX))
{
ExcelNamedRangeDefinition excelNamedRangeDefinition = ExcelBindingDefinitionNamedRange.RetrieveNamedRangeDefinition(trimmedValue);
if (excelNamedRangeDefinition != null)
{
BindingDefinition nestedBindingDefinition = null;
if (!string.IsNullOrEmpty(excelNamedRangeDefinition.Value))
{
nestedBindingDefinition = CreateBindingDefinition(templateDefinitionPart, excelNamedRangeDefinition.Value, excelNamedRangeDefinition.Value.Trim()) as BindingDefinition;
}
ret = ExcelBindingDefinitionNamedRange.CreateInstance(templateDefinitionPart, excelNamedRangeDefinition, nestedBindingDefinition);
}
}
else
{
BindingDefinitionDescription bindingDefinitionDescription = BindingDefinitionDescription.CreateBindingDescription(templateDefinitionPart.Parent, value, trimmedValue);
if (bindingDefinitionDescription.Formula != null)
{
ret = ExcelBindingDefinitionWithFormula.CreateInstance(excelTemplateDefinition, bindingDefinitionDescription);
}
else
{
ret = BindingDefinitionFactory.CreateInstances(excelTemplateDefinition, bindingDefinitionDescription);
}
}
return(ret);
}
//@@/// <summary> If a expandable header is defined, contains the binding excelTemplateDefinition used to manage the 'Expand' property of the template (needs a header defined on the template.</summary>
//public IBindingDefinition ExpanderBindingDefinition
//{ get; set; }
#endregion
#region .ctors
public ExcelTemplateDefinitionPart(ExcelTemplateDefinition parent, TemplateDefinitionPartType partType, ExcelInterop.Range firstRange, ExcelInterop.Range lastRange) : base(partType)
{
Parent = parent;
DefinitionFirstCell = firstRange;
DefinitionLastCell = lastRange;
Width = DefinitionLastCell.Column - DefinitionFirstCell.Column + 1;
Height = DefinitionLastCell.Row - DefinitionFirstCell.Row + 1;
if (Width == 0 || Height == 0)
{
throw new System.Exception("A template part ('Header','Body' or 'Footer' must have a 'Height' and a 'Width' >= 1");
}
ExcelInterop.Range templateRange = DefinitionFirstCell;
DefinitionCells = DefinitionFirstCell = templateRange.Cells[1, 1];
DefinitionCells = templateRange.Resize[Height, Width];
DefinitionParts = new IDefinitionPart[Height, Width];
PositionLinkedTemplates = new List <List <int> >();
}
/// <summary>Analyze a cell of the template part</summary>
private IDefinitionPart AnalyzeCell(ExcelTemplateDefinitionPart templateDefinitionPart, ExcelInterop.Range cell)
{
IDefinitionPart part = null;
if (cell.Value2 != null)
{
string value = cell.Value2.ToString();
if (!string.IsNullOrEmpty(value))
{
string trimmedValue = value.Trim();
if (trimmedValue.StartsWith(LINKED_TEMPLATE_PREFIX))
{
try
{
XmlTemplateLink xmlTemplateLink = trimmedValue.Deserialize <XmlTemplateLink>();
TemplateLink templateLink = TemplateLink.CreateInstance(xmlTemplateLink);
ExcelTemplateDefinition templateDefinition = (ETKExcel.TemplateManager as ExcelTemplateManager).GetTemplateDefinitionFromLink(templateDefinitionPart, templateLink);
LinkedTemplateDefinition linkedTemplateDefinition = new LinkedTemplateDefinition(templateDefinitionPart.Parent, templateDefinition, templateLink);
templateDefinitionPart.AddLinkedTemplate(linkedTemplateDefinition);
part = linkedTemplateDefinition;
}
catch (Exception ex)
{
string message = $"Cannot create the linked template dataAccessor '{trimmedValue}'. {ex.Message}";
throw new EtkException(message, false);
}
}
else
{
if (trimmedValue.StartsWith(ExcelBindingFilterDefinition.Filter_PREFIX))
{
ExcelBindingFilterDefinition filterdefinition = ExcelBindingFilterDefinition.CreateInstance(templateDefinitionPart, trimmedValue);
templateDefinitionPart.AddFilterDefinition(filterdefinition);
part = filterdefinition;
}
else if (trimmedValue.StartsWith(ExcelBindingSearchDefinition.Search_PREFIX))
{
ExcelBindingSearchDefinition searchdefinition = ExcelBindingSearchDefinition.CreateInstance(trimmedValue);
templateDefinitionPart.AddSearchDefinition(searchdefinition);
part = searchdefinition;
}
else
{
try
{
IBindingDefinition bindingDefinition = CreateBindingDefinition(templateDefinitionPart, value, trimmedValue);
templateDefinitionPart.AddBindingDefinition(bindingDefinition);
part = bindingDefinition;
}
catch (Exception ex)
{
string message = $"Cannot create the binding definition for '{trimmedValue}'. {ex.Message}";
throw new EtkException(message, false);
}
}
}
}
}
return(part);
}
public static ExcelTemplateDefinitionPart CreateInstance(ExcelTemplateDefinition excelTemplateDefinition, TemplateDefinitionPartType partType, ExcelInterop.Range firstRange, ExcelInterop.Range lastRange)
{
ExcelTemplateDefinitionPartFactory factory = new ExcelTemplateDefinitionPartFactory();
return(factory.Execute(excelTemplateDefinition, partType, firstRange, lastRange));
}
/// <summary> Parse the template. Retrieve its components. </summary>
private void ParseTemplate(ExcelTemplateDefinition excelTemplateDefinition, ref ExcelInterop.Worksheet worksheet, out ExcelTemplateDefinitionPart header, out ExcelTemplateDefinitionPart body, out ExcelTemplateDefinitionPart footer)
{
try
{
header = body = footer = null;
int headerSize;
int footerSize;
RetrieveHeaderAndFooterSize(excelTemplateDefinition.DefinitionFirstCell, excelTemplateDefinition.DefinitionLastCell, excelTemplateDefinition.Orientation, out headerSize, out footerSize);
ExcelInterop.Range firstRange = worksheet.Cells[excelTemplateDefinition.DefinitionFirstCell.Row + 1, excelTemplateDefinition.DefinitionFirstCell.Column + 1];
ExcelInterop.Range lastRange = worksheet.Cells[excelTemplateDefinition.DefinitionLastCell.Row, excelTemplateDefinition.DefinitionLastCell.Column - 1];
int width = lastRange.Column - firstRange.Column + 1;
int height = lastRange.Row - firstRange.Row + 1;
// Header
/////////
if (headerSize != 0)
{
ExcelInterop.Range headerLastRange;
if (excelTemplateDefinition.Orientation == Etk.BindingTemplates.Definitions.Templates.Orientation.Horizontal)
{
headerLastRange = worksheet.Cells[firstRange.Row + height - 1, firstRange.Column + headerSize - 1];
}
else
{
headerLastRange = worksheet.Cells[firstRange.Row + headerSize - 1, lastRange.Column];
}
//string name = string.Format("{0}-{1}", excelTemplateDefinition.Name, "Header");
header = ExcelTemplateDefinitionPartFactory.CreateInstance(excelTemplateDefinition, TemplateDefinitionPartType.Header, firstRange, headerLastRange);
headerLastRange = null;
}
// Footer
/////////
if (footerSize != 0)
{
ExcelInterop.Range footerFirstRange;
if (excelTemplateDefinition.Orientation == Etk.BindingTemplates.Definitions.Templates.Orientation.Horizontal)
{
footerFirstRange = worksheet.Cells[lastRange.Row - height + 1, lastRange.Column - footerSize + 1];
}
else
{
footerFirstRange = worksheet.Cells[lastRange.Row - footerSize + 1, firstRange.Column];
}
//string name = string.Format("{0}-{1}", excelTemplateDefinition.Name, "Footer");
footer = ExcelTemplateDefinitionPartFactory.CreateInstance(excelTemplateDefinition, TemplateDefinitionPartType.Footer, footerFirstRange, lastRange);
footerFirstRange = null;
}
// Body
///////
ExcelInterop.Range bodyFirstRange;
ExcelInterop.Range bodyLastRange;
if (excelTemplateDefinition.Orientation == Orientation.Horizontal)
{
bodyFirstRange = worksheet.Cells[firstRange.Row, firstRange.Column + headerSize];
bodyLastRange = worksheet.Cells[lastRange.Row, lastRange.Column - footerSize];
}
else
{
bodyFirstRange = worksheet.Cells[firstRange.Row + headerSize, firstRange.Column];
bodyLastRange = worksheet.Cells[lastRange.Row - footerSize, lastRange.Column];
}
body = ExcelTemplateDefinitionPartFactory.CreateInstance(excelTemplateDefinition, TemplateDefinitionPartType.Body, bodyFirstRange, bodyLastRange);
bodyFirstRange = bodyLastRange = null;
}
catch (Exception ex)
{
throw new EtkException($"The parsing of template '{excelTemplateDefinition.Name}' in sheet '{worksheet.Name.EmptyIfNull()}' failed: {ex.Message}");
}
}
