public static void Run()
{
using (var stream = new FileStream($"{nameof(MultipleSheet)}.xlsx", FileMode.Create, FileAccess.Write))
using (var xlsxWriter = new XlsxWriter(stream))
{
var whiteFont = new XlsxFont("Segoe UI", 9, Color.White, bold: true);
var blueFill = new XlsxFill(Color.FromArgb(0, 0x45, 0x86));
var yellowFill = new XlsxFill(Color.FromArgb(0xff, 0xff, 0x88));
var headerStyle = new XlsxStyle(whiteFont, blueFill, XlsxBorder.None, XlsxNumberFormat.General, XlsxAlignment.Default);
var highlightStyle = XlsxStyle.Default.With(yellowFill);
var dateStyle = XlsxStyle.Default.With(XlsxNumberFormat.ShortDateTime);
xlsxWriter
.BeginWorksheet("Sheet&'<1>\"", splitColumn: 1, splitRow: 2, columns: new [] { XlsxColumn.Unformatted(count: 2), XlsxColumn.Formatted(width: 20) })
.SetDefaultStyle(headerStyle)
.BeginRow().Write("Col<1>").Write("Col2").Write("Col&3")
.BeginRow().Write().Write("Sub2").Write("Sub3")
.SetDefaultStyle(XlsxStyle.Default)
.BeginRow().Write("Row3").Write(42).WriteFormula("B3*10", highlightStyle)
.BeginRow().Write("Row4").SkipColumns(1).Write(new DateTime(2020, 5, 6, 18, 27, 0), dateStyle)
.SkipRows(2)
.BeginRow().Write("Row7", XlsxStyle.Default.With(XlsxBorder.Around(new XlsxBorder.Line(Color.DeepPink, XlsxBorder.Style.Dashed))), columnSpan: 2).Write(3.14159265359)
.SetAutoFilter(1, 1, xlsxWriter.CurrentRowNumber, 3)
.BeginWorksheet("Sheet2", splitColumn: 1, splitRow: 1)
.BeginRow().Write("Lorem ipsum dolor sit amet,")
.BeginRow().Write("consectetur adipiscing elit,")
.BeginRow().Write("sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.")
.SetAutoFilter(1, 1, xlsxWriter.CurrentRowNumber, 1);
}
}
/// <summary>
/// Try to modify the chart border settings.
/// </summary>
/// <param name="border">Reference to border settings to apply</param>
/// <returns>
/// <para>
/// A <see cref="BooleanResult"/> reference that contains the result of the operation, to check if the operation is correct, the <b>Success</b>
/// property will be <b>true</b> and the <b>Value</b> property will contain the value; Otherwise, the the <b>Success</b> property
/// will be false and the <b>Errors</b> property will contain the errors associated with the operation, if they have been filled in.
/// </para>
/// <para>
/// The type of the return value is <see cref="BooleanResult"/>, which contains the operation result
/// </para>
/// </returns>
public IResult SetBorder(XlsxBorder border)
{
if (border == null)
{
return BooleanResult.CreateErroResult("border can not be null");
}
if (border.Show == YesNo.No)
{
return BooleanResult.SuccessResult;
}
try
{
var chartBorder = Chart.Border;
chartBorder.Fill.Style = eFillStyle.SolidFill;
chartBorder.Fill.Color = border.GetColor();
chartBorder.Fill.Transparancy = border.Transparency;
chartBorder.LineStyle = border.Style.ToEppLineStyle();
chartBorder.Width = border.Width;
return BooleanResult.SuccessResult;
}
catch (Exception e)
{
return BooleanResult.FromException(e);
}
}
public uint GetBorder(IXlsxBorder borderLayer)
{
XlsxBorder layer = borderLayer as XlsxBorder;
if (layer == null)
{
throw new Exception("Unexpected Border Type");
}
return(GetBorder(layer.TopBorder, layer.RightBorder, layer.BottomBorder, layer.LeftBorder, layer.DiagonalBorder));
}
/// <summary>
/// Creates a new <see cref="XlsxPicture"/> instance from this image.
/// </summary>
/// <param name="pictureName">Picture name</param>
/// <param name="size">Picture size</param>
/// <param name="border">Picture border</param>
/// <param name="content">Picture content</param>
/// <param name="shapeEffects">picture shape effects</param>
/// <returns>
/// A <see cref="XlsxPicture"/> reference from this image.
/// </returns>
public XlsxPicture AsPicture(string pictureName, XlsxBaseSize size = null, XlsxBorder border = null, XlsxPictureContent content = null, XlsxShapeEffects shapeEffects = null) =>
new XlsxPicture
{
Name = pictureName,
UnderliyingImage = this,
Size = size ?? XlsxSize.Default,
Border = border ?? XlsxBorder.Default,
ShapeEffects = shapeEffects ?? XlsxShapeEffects.Default,
Content = content ?? XlsxPictureContent.Default,
Path = Path == null ? null : Path.AbsolutePath
};
/// <summary>
/// Set picture border.
/// </summary>
/// <param name="border">Target border.</param>
/// <param name="model">Border to draw.</param>
public static void SetBorder(this ExcelDrawingBorder border, XlsxBorder model)
{
SentinelHelper.ArgumentNull(border, nameof(border));
SentinelHelper.ArgumentNull(model, nameof(model));
if (model.Show == YesNo.No)
{
return;
}
border.Fill.Style = eFillStyle.SolidFill;
border.Fill.Color = model.GetColor();
border.Fill.Transparancy = model.Transparency;
border.LineStyle = model.Style.ToEppLineStyle();
border.Width = model.Width;
}
public static void Run()
{
using (var stream = new FileStream($"{nameof(Border)}.xlsx", FileMode.Create, FileAccess.Write))
using (var xlsxWriter = new XlsxWriter(stream))
{
var leftBorderStyle = XlsxStyle.Default.With(new XlsxBorder(left: new XlsxBorder.Line(Color.DeepPink, XlsxBorder.Style.Thin)));
var allBorderStyle = XlsxStyle.Default.With(XlsxBorder.Around(new XlsxBorder.Line(Color.CornflowerBlue, XlsxBorder.Style.Dashed)));
var diagonalBorderStyle = XlsxStyle.Default.With(
new XlsxBorder(diagonal: new XlsxBorder.Line(Color.Red, XlsxBorder.Style.Dotted), diagonalDown: true, diagonalUp: true));
xlsxWriter
.BeginWorksheet("Sheet1")
.SkipRows(1)
.BeginRow(height: 50).SkipColumns(1).Write("B1", leftBorderStyle).SkipColumns(1).Write("D1", allBorderStyle).Write("E1", diagonalBorderStyle)
.BeginRow().SkipColumns(1).Write("B2", leftBorderStyle).SkipColumns(1).Write("D2", allBorderStyle).Write("E2", diagonalBorderStyle)
.BeginRow().SkipColumns(1).Write(leftBorderStyle).SkipColumns(1).Write(allBorderStyle).Write(diagonalBorderStyle);
}
}
public static void Run()
{
using (var stream = new FileStream($"{nameof(Simple)}.xlsx", FileMode.Create, FileAccess.Write))
using (var xlsxWriter = new XlsxWriter(stream))
{
var headerStyle = new XlsxStyle(
new XlsxFont("Segoe UI", 9, Color.White, bold: true),
new XlsxFill(Color.FromArgb(0, 0x45, 0x86)),
XlsxStyle.Default.Border,
XlsxStyle.Default.NumberFormat,
XlsxAlignment.Default);
var highlightStyle = XlsxStyle.Default.With(new XlsxFill(Color.FromArgb(0xff, 0xff, 0x88)));
var dateStyle = XlsxStyle.Default.With(XlsxNumberFormat.ShortDateTime);
var borderedStyle = highlightStyle.With(XlsxBorder.Around(new XlsxBorder.Line(Color.DeepPink, XlsxBorder.Style.Dashed)));
xlsxWriter
.BeginWorksheet("Sheet 1", columns: new[] { XlsxColumn.Unformatted(count: 2), XlsxColumn.Formatted(width: 20) })
.SetDefaultStyle(headerStyle)
.BeginRow().AddMergedCell(2, 1).Write("Col1").Write("Top2").Write("Top3")
.BeginRow().Write().Write("Col2").Write("Col3")
.SetDefaultStyle(XlsxStyle.Default)
.BeginRow().Write("Row3").Write(42).WriteFormula(
$"{xlsxWriter.GetRelativeColumnName(-1)}{xlsxWriter.CurrentRowNumber}*10", highlightStyle)
.BeginRow().Write("Row4").SkipColumns(1).Write(new DateTime(2020, 5, 6, 18, 27, 0), dateStyle)
.SkipRows(2)
.BeginRow().Write("Row7", borderedStyle, columnSpan: 2).Write(3.14159265359)
.BeginRow().Write("Bold").Write().Write("Be bold", XlsxStyle.Default.With(XlsxFont.Default.WithBold()))
.BeginRow().Write("Italic").Write().Write("Be italic", XlsxStyle.Default.With(XlsxFont.Default.WithItalic()))
.BeginRow().Write("Strike").Write().Write("Be struck", XlsxStyle.Default.With(XlsxFont.Default.WithStrike()))
.BeginRow().Write("Underline").Write().Write("Single", XlsxStyle.Default.With(XlsxFont.Default.WithUnderline()))
.BeginRow().Write("Underline").Write().Write("Double", XlsxStyle.Default.With(XlsxFont.Default.WithUnderline(XlsxFont.Underline.Double)))
.BeginRow().Write("Underline").Write().Write("SingleAccounting", XlsxStyle.Default.With(XlsxFont.Default.WithUnderline(XlsxFont.Underline.SingleAccounting)))
.BeginRow().Write("Underline").Write().Write("DoubleAccounting", XlsxStyle.Default.With(XlsxFont.Default.WithUnderline(XlsxFont.Underline.DoubleAccounting)))
.SetAutoFilter(2, 1, xlsxWriter.CurrentRowNumber - 1, 3);
}
}
private static IList <IXlsxSheet> SetSheets()
{
IList <IXlsxSheet> sheets = new List <IXlsxSheet>();
XlsxSheet sheet = new XlsxSheet();
sheet.SheetName = "Just to test";
IXlsxFill fill1 = new XlsxFill {
PatternType = PatternType.DarkTrellis
};
IXlsxFill fill2 = new XlsxFill {
PatternType = PatternType.LightTrellis,
BackgroundColorArgb = "FF0000FF"
};
IXlsxBorder border1 = new XlsxBorder {
TopBorder = BorderType.Dashed,
RightBorder = BorderType.Double
};
IXlsxBorder border2 = new XlsxBorder {
DiagonalBorder = BorderType.MediumDashDot
};
XlsxCell[] header = new XlsxCell[] {
new XlsxCell("Name")
{
Fill = fill1, Border = border1
},
new XlsxCell("Zahl")
{
Fill = fill1, Border = border1
},
new XlsxCell("Datum")
{
Fill = fill1, Border = border1
}
};
XlsxCell[] row1 = new XlsxCell[] {
new XlsxCell("Sanjay", new XlsxFont {
FontType = FontType.Calibri,
Bold = true,
Italic = true,
Size = 13,
UnderlineType = UnderlineType.DoubleLine,
ColorArgb = "FF00FF00"
})
{
Fill = fill1
},
new XlsxCell(5.2765)
{
Fill = fill2
},
new XlsxCell(new DateTime(2018, 1, 1, 12, 12, 12))
{
Border = border2
}
};
IXlsxCell[][] cells = new XlsxCell[][] {
header,
row1
};
sheet.Cells = cells;
sheets.Add(sheet);
return(sheets);
}
