private void FormatRange(ExcelRange range, CellsAppearance appearance)
{
AppearanceHelper.SetFromFont(range.Style.Font.SetFromFont, appearance.FontFamily, appearance.FontSize);
range.Style.Font.Size = appearance.FontSize;
range.Style.Font.Bold = appearance.Bold;
range.Style.Font.Italic = appearance.Italic;
range.Style.Font.Color.SetColor(appearance.TextColor);
if (appearance.Underline)
{
range.Style.Font.UnderLine = true;
range.Style.Font.UnderLineType = ExcelUnderLineType.Single;
}
else
{
range.Style.Font.UnderLine = false;
}
if (appearance.UseBackColor)
{
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(appearance.BackgroundColor);
}
else
{
range.Style.Fill.PatternType = ExcelFillStyle.None;
}
switch (appearance.TextAlignment)
{
case eTextAlignment.Left:
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
break;
case eTextAlignment.Right:
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
break;
default:
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
break;
}
switch (appearance.TextVerticalAlignment)
{
case eTextAnchoringType.Top:
range.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
break;
case eTextAnchoringType.Bottom:
range.Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;
break;
default:
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
break;
}
}
private static void FormatPicture(ExcelPicture shape, CellsAppearance appearance)
{
if (!appearance.UseOutlineColor)
{
return;
}
shape.Border.LineStyle = eLineStyle.Solid;
shape.Border.Fill.Color = appearance.OutlineColor;
}
private void DrawBelowCaptions(Flight flight,
RowDefinition rowDefinition,
int startColumn,
int endColumn,
CellsAppearance appearance)
{
DrawCaptions(flight.FlightCaption?.Below,
rowDefinition.PrimaryExcelRowIndex + 1,
startColumn,
endColumn,
appearance,
false);
}
private static void ApplyAppearance(ExcelRange cells, CellsAppearance appearance)
{
cells.Merge = true;
cells.Style.ShrinkToFit = true;
switch (appearance.TextAlignment)
{
case eTextAlignment.Left:
cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
break;
case eTextAlignment.Right:
cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
break;
default:
cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
break;
}
switch (appearance.TextVerticalAlignment)
{
case eTextAnchoringType.Top:
cells.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
break;
case eTextAnchoringType.Bottom:
cells.Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;
break;
default:
cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
break;
}
AppearanceHelper.SetFromFont(cells.Style.Font.SetFromFont, appearance.FontFamily, appearance.FontSize);
cells.Style.Font.Color.SetColor(appearance.TextColor);
cells.Style.Font.Size = appearance.FontSize;
cells.Style.Font.Bold = appearance.Bold;
cells.Style.Font.Italic = appearance.Italic;
if (appearance.Underline)
{
cells.Style.Font.UnderLine = appearance.Underline;
cells.Style.Font.UnderLineType = ExcelUnderLineType.Single;
}
cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
cells.Style.Fill.BackgroundColor.SetColor(appearance.BackgroundColor);
}
private static void FormatShape(ExcelShape shape, CellsAppearance appearance)
{
if (appearance.UseFillColor)
{
shape.Fill.Color = appearance.FillColor;
}
else
{
shape.Fill.Color = Color.Transparent;
shape.Fill.Transparancy = 100;
}
shape.Border.Width = appearance.StrokeWidth;
shape.Border.Fill.Color = appearance.StrokeColor;
}
private void DrawAboveCaptions(Flight flight,
RowDefinition rowDefinition,
int startColumn,
int endColumn,
CellsAppearance appearance)
{
var startRowNumber = rowDefinition.StartExcelRowIndex
+ (rowDefinition.AboveCount - flight.FlightCaption?.Above?.Count ?? 0);
DrawCaptions(flight.FlightCaption?.Above,
startRowNumber,
startColumn,
endColumn,
appearance,
true);
}
private static void FormatCaption(ExcelShape captionShape, CellsAppearance appearance)
{
captionShape.Fill.Transparancy = 30;
captionShape.TextAlignment = appearance.TextAlignment;
captionShape.TextAnchoring = appearance.TextVerticalAlignment;
AppearanceHelper.SetFromFont(captionShape.Font.SetFromFont, appearance.FontFamily, appearance.FontSize);
captionShape.Font.Color = appearance.TextColor;
captionShape.Font.Size = appearance.FontSize;
captionShape.Font.Bold = appearance.Bold;
captionShape.Font.Italic = appearance.Italic;
captionShape.Font.UnderLine = appearance.Underline ? eUnderLineType.Single : eUnderLineType.None;
captionShape.Fill.Color = appearance.BackgroundColor;
captionShape.Border.Fill.Color = appearance.CellBorderColor;
}
private static void SetRotation(XmlDocument xml, CellsAppearance appearance)
{
if (appearance.RotationAngle == 0)
{
return;
}
var shapeNode = xml.LastChild.LastChild;
var xdrNode = shapeNode.ChildNodes[2];
var spPrNode = xdrNode.ChildNodes[1];
var xfrmNode = xml.CreateNode(XmlNodeType.Element,
"a:xfrm",
"http://schemas.openxmlformats.org/drawingml/2006/main");
xfrmNode = spPrNode.PrependChild(xfrmNode);
var rotAttribute = xml.CreateAttribute("rot");
rotAttribute.Value = (60000 * appearance.RotationAngle).ToString();
xfrmNode.Attributes.Append(rotAttribute);
}
private static void SetTransparency(XmlDocument xml, CellsAppearance appearance)
{
if (Math.Abs(appearance.Transparency - 1) < 0.001)
{
return;
}
var shapeNode = xml.LastChild.LastChild;
var xdrNode = shapeNode.ChildNodes[2];
var blipFillNode = xdrNode.ChildNodes[1];
var blipNode = blipFillNode.ChildNodes[0];
var alphaModFixNode = xml.CreateNode(XmlNodeType.Element,
"a:alphaModFix",
"http://schemas.openxmlformats.org/drawingml/2006/main");
alphaModFixNode = blipNode.AppendChild(alphaModFixNode);
var amtAttribute = xml.CreateAttribute("amt");
amtAttribute.Value = (100000 * appearance.Transparency).ToString();
alphaModFixNode.Attributes.Append(amtAttribute);
}
private static void FormatFlight(ExcelRange cells, CellsAppearance appearance)
{
ApplyAppearance(cells, appearance);
cells.Style.Border.BorderAround(ExcelBorderStyle.Thin, appearance.CellBorderColor);
}
private void DrawCaptions(IReadOnlyCollection <FlightCaptionPosition> captions,
int startRowNumber,
int startColumn,
int endColumn,
CellsAppearance appearance,
bool drawBorderAbove)
{
var current = startRowNumber;
var index = 0;
captions = captions ?? new List <FlightCaptionPosition>();
foreach (var caption in captions)
{
var cells = _worksheet.Cells[current, startColumn, current, endColumn];
cells.Style.WrapText = true;
var captionAppearance = AppearanceHelper.GetAppearance(caption.Appearance);
if (captionAppearance.UseBackColor)
{
cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
cells.Style.Fill.BackgroundColor.SetColor(captionAppearance.BackgroundColor);
}
cells.Style.Font.Bold = captionAppearance.Bold;
cells.Style.Font.Italic = captionAppearance.Italic;
if (captionAppearance.Underline)
{
cells.Style.Font.UnderLine = true;
cells.Style.Font.UnderLineType = ExcelUnderLineType.Single;
}
cells.Style.Font.Color.SetColor(captionAppearance.TextColor);
cells.Style.Font.Size = captionAppearance.FontSize;
cells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Right.Color.SetColor(appearance.CellBorderColor);
cells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Left.Color.SetColor(appearance.CellBorderColor);
if (drawBorderAbove && index == 0)
{
cells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Top.Color.SetColor(appearance.CellBorderColor);
}
else if (!drawBorderAbove && index == captions.Count - 1)
{
cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
cells.Style.Border.Bottom.Color.SetColor(appearance.CellBorderColor);
}
var firstCell = _worksheet.Cells[cells.Start.Address];
captionAppearance.FillValue(caption.FormattedValue, firstCell, _currencies);
cells.Merge = true;
cells.Style.ShrinkToFit = true;
cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
current++;
index++;
}
}
private static void ApplyAppearance(ExcelRange range, CellsAppearance appearance)
{
range.Merge = true;
range.Style.ShrinkToFit = true;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
var backColor = appearance.UseBackColor
? appearance.BackgroundColor
: Colors.DefaultFormulaBackgroundColor;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(backColor);
range.Style.Border.BorderAround(ExcelBorderStyle.Thin,
appearance.UseCellBorderColor
? appearance.CellBorderColor
: Colors.DefaultFormulaBorderColor);
AppearanceHelper.SetFromFont(range.Style.Font.SetFromFont, appearance.FontFamily, appearance.FontSize);
range.Style.Font.Size = appearance.FontSize;
range.Style.Font.Bold = appearance.Bold;
range.Style.Font.Italic = appearance.Italic;
range.Style.Font.Color.SetColor(appearance.TextColor);
if (appearance.Underline)
{
range.Style.Font.UnderLine = true;
range.Style.Font.UnderLineType = ExcelUnderLineType.Single;
}
else
{
range.Style.Font.UnderLine = false;
}
switch (appearance.TextAlignment)
{
case eTextAlignment.Left:
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
break;
case eTextAlignment.Right:
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
break;
default:
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
break;
}
switch (appearance.TextVerticalAlignment)
{
case eTextAnchoringType.Top:
range.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
break;
case eTextAnchoringType.Bottom:
range.Style.VerticalAlignment = ExcelVerticalAlignment.Bottom;
break;
default:
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
break;
}
}
private void CalculateSize(ExcelPicture shape,
int startRowIndex,
int endRowIndex,
int startColumnIndex,
int endColumnIndex,
Image image,
CellsAppearance appearance)
{
if (appearance.UseImageFillSizing)
{
shape.From.Row = startRowIndex;
shape.From.Column = startColumnIndex;
shape.To.Row = endRowIndex;
shape.To.Column = endColumnIndex;
return;
}
shape.From.Row = startRowIndex;
shape.From.Column = startColumnIndex;
const double columnCoefficient = 5;
var targetWidth = 0;
var targetHeight = 0;
for (var i = startColumnIndex; i < endColumnIndex; i++)
{
targetWidth += (int)(_worksheet.Column(i).Width *columnCoefficient);
}
for (var i = startRowIndex; i <= endRowIndex; i++)
{
targetHeight += (int)_worksheet.Row(i).Height;
}
var sourceWidth = image.Width;
var sourceHeight = image.Height;
var percentW = targetWidth / (float)sourceWidth;
var percentH = targetHeight / (float)sourceHeight;
var percent = percentH < percentW ? percentH : percentW;
var newWidth = (int)(sourceWidth * percent);
var newHeight = (int)(sourceHeight * percent);
shape.SetSize(newWidth, newHeight);
//note: temporary fix since it doesn't works with some images
//shape.From.Row = startRowIndex;
//shape.From.Column = startColumnIndex;
//double newWidth = 0;
//double newHeight = 0;
//double coeff = 6.5;
//for (int i = startColumnIndex; i <= endColumnIndex; i++)
//{
// newWidth += _worksheet.Column(i).Width;
//}
//for (int i = startRowIndex; i <= endRowIndex; i++)
//{
// newHeight += _worksheet.Row(i).Height;
//}
//newWidth *= coeff;
//if ((newWidth > image.Width) || (newHeight > image.Height))
//{
// if (newWidth > image.Width)
// {
// double widthOffset = (newWidth - image.Width) / 2.0;
// double currWidth = 0;
// int i;
// for (i = startColumnIndex; i <= endColumnIndex; i++)
// {
// if (widthOffset <= _worksheet.Column(i).Width * coeff + currWidth)
// {
// break;
// }
// currWidth += _worksheet.Column(i).Width * coeff;
// }
// shape.From.Column = i;
// }
// if (newHeight > image.Height)
// {
// double heightOffset = (newHeight - image.Height) / 2.0;
// double currHeight = 0;
// int i;
// for (i = startRowIndex; i <= endRowIndex; i++)
// {
// if (heightOffset <= _worksheet.Row(i).Height + currHeight)
// {
// break;
// }
// currHeight += _worksheet.Row(i).Height;
// }
// shape.From.Row = i;
// }
//}
//shape.SetSize(image.Width, image.Height);
}
