/// <summary>
/// Implementation to execute when set action.
/// </summary>
/// <param name="input">stream input</param>
/// <param name="context">Input context</param>
/// <returns>
/// <para>
/// A <see cref="SetResult"/> 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="SetResultData"/>, which contains the operation result
/// </para>
/// </returns>
protected override SetResult SetImpl(Stream input, IInput context)
{
if (string.IsNullOrEmpty(SheetName))
{
return(SetResult.CreateErroResult(
"Sheet name can not be null or empty",
new SetResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Columns == null)
{
return(SetResult.CreateSuccessResult(new SetResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
return(SetImpl(context, input, SheetName, Columns));
}
private static SetResult SetImpl(IInput context, Stream input, XlsxDocumentMetadataSettings settings)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
excel.Workbook.Properties.SetDocumentMetadata(settings);
excel.SaveAs(outputStream);
return(SetResult.CreateSuccessResult(new SetResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(SetResult.FromException(
ex,
new SetResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
/// <summary>
/// Implementation to execute when set action.
/// </summary>
/// <param name="input">stream input</param>
/// <param name="context">Input context</param>
/// <returns>
/// <para>
/// A <see cref="SetResult"/> 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="SetResultData"/>, which contains the operation result
/// </para>
/// </returns>
protected override SetResult SetImpl(Stream input, IInput context)
{
if (Settings == null)
{
return(SetResult.CreateSuccessResult(new SetResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
return(SetImpl(context, input, Settings));
}
private static SetResult SetImpl(IInput context, Stream input)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
foreach (var ws in excel.Workbook.Worksheets)
{
var hasDimension = ws.Dimension != null;
if (!hasDimension)
{
continue;
}
ws.Cells[ws.Dimension.Address].AutoFitColumns();
var start = ws.Cells[ws.Dimension.Address].Start.Column;
var end = ws.Cells[ws.Dimension.Address].End.Column;
for (int col = start; col <= end; col++)
{
ws.Column(col).BestFit = true;
ws.Column(col).Width = ws.Column(col).Width + 1;
}
}
excel.SaveAs(outputStream);
return(SetResult.CreateSuccessResult(new SetResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(SetResult.FromException(
ex,
new SetResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
private static SetResult SetImpl(IInput context, Stream input, string sheetName, IEnumerable <ColumnDefinition> columns)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(SetResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new SetResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
foreach (var column in columns)
{
ws.Column(column.Column).Width = column.Width;
}
excel.SaveAs(outputStream);
return(SetResult.CreateSuccessResult(new SetResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(SetResult.FromException(
ex,
new SetResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
private static SetResult SetImpl(IInput context, Stream input, string sheetName, YesNo show)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(SetResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new SetResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
ws.View.ShowGridLines = show == YesNo.Yes;
excel.SaveAs(outputStream);
return(SetResult.CreateSuccessResult(new SetResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(SetResult.FromException(
ex,
new SetResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
private static SetResult SetImpl(IInput context, Stream input, XlsxSheetsSettingsCollection settings)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var settingsSheetsNames = settings.Select(sheet => sheet.SheetName).ToList();
var settingsSheetsCount = settingsSheetsNames.Count();
int i = 0;
var sheets = excel.Workbook.Worksheets;
foreach (var sheet in sheets)
{
#region Select appropiate sheet settings
var sheetMatch = settingsSheetsNames.Contains(sheet.Name);
var sheetSettings = XlsxSheetSettings.Default;
if (i <= settingsSheetsCount - 1)
{
sheetSettings = sheetMatch ? settings.FirstOrDefault(s => s.SheetName == sheet.Name) : settings[i];
}
if (sheetSettings == null)
{
continue;
}
#endregion
#region Updates sheet view
sheet.View.ToEppSheetView(sheetSettings.View);
if (sheetSettings.View == KnownDocumentView.Normal)
{
if (sheetSettings.FreezePanesPoint.Column != 1 && sheetSettings.FreezePanesPoint.Row != 1)
{
sheet.View.FreezePanes(sheetSettings.FreezePanesPoint.Row, sheetSettings.FreezePanesPoint.Column);
}
}
#endregion
#region Updates sheet header
sheet.HeaderFooter.SetSheetHeader(sheetSettings.Header);
#endregion
#region Updates sheet footer
sheet.HeaderFooter.SetSheetFooter(sheetSettings.Footer);
#endregion
#region Updates page orientation, margins and size
sheet.PrinterSettings.PaperSize = sheetSettings.Size.ToEppPaperSize();
sheet.PrinterSettings.Orientation = sheetSettings.Orientation.ToEppOrientation();
sheet.PrinterSettings.SetMarginsFromModel(sheetSettings.Margins);
var hasDimension = sheet.Dimension != null;
if (!hasDimension)
{
continue;
}
ExcelRange printAreaRange = sheet.Cells[sheet.Dimension.Address];
sheet.PrinterSettings.PrintArea = printAreaRange;
//sheet.PrinterSettings.RepeatRows = sheetSettings.Cells[repeatRowsRange];
#endregion
i++;
}
excel.SaveAs(outputStream);
return(SetResult.CreateSuccessResult(new SetResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(SetResult.FromException(
ex,
new SetResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}