private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, object data, XlsxBaseRange location, XlsxCellStyle style)
{
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(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
ExcelAddressBase locationAddress = location.ToEppExcelAddress();
XlsxCellStyle safeStyle = excel.CreateStyle(style);
XlsxCellMerge merge = safeStyle.Content.Merge;
string range = merge.Cells == 1
? locationAddress.ToString()
: merge.Orientation == KnownMergeOrientation.Horizontal
? ExcelCellBase.GetAddress(locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.Start.Row, locationAddress.Start.Column + merge.Cells - 1)
: ExcelCellBase.GetAddress(locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.Start.Row + merge.Cells - 1, locationAddress.Start.Column);
ExcelRange cell = ws.Cells[range];
cell.StyleName = locationAddress.End.Row.IsOdd()
? $"{safeStyle.Name}_Alternate"
: safeStyle.Name ?? XlsxBaseStyle.NameOfDefaultStyle;
if (style.Content.Show == YesNo.Yes)
{
if (data != null)
{
cell.Value = safeStyle.Content.DataType.GetFormattedDataValue(data.ToString()).FormattedValue;
if (merge.Cells > 1)
{
cell.Merge = true;
}
}
}
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxBaseRange location, QualifiedAggregateDefinition aggregate, XlsxCellStyle style)
{
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(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
string aggregateWorksheetName = aggregate.WorkSheet;
if (string.IsNullOrEmpty(aggregate.WorkSheet))
{
aggregateWorksheetName = sheetName;
}
var aggregateWorksheet = ws;
bool sameSheetName = aggregateWorksheetName.Equals(sheetName, StringComparison.OrdinalIgnoreCase);
if (!sameSheetName)
{
aggregateWorksheet = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(aggregateWorksheetName, StringComparison.OrdinalIgnoreCase));
}
if (aggregateWorksheet == null)
{
return(InsertResult.CreateErroResult(
$"Aggregate sheet '{aggregateWorksheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
ExcelAddressBase locationAddress = location.ToEppExcelAddress();
XlsxCellStyle safeStyle = excel.CreateStyle(style);
XlsxCellMerge merge = safeStyle.Content.Merge;
string range = merge.Cells == 1
? locationAddress.ToString()
: merge.Orientation == KnownMergeOrientation.Horizontal
? ExcelCellBase.GetAddress(locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.Start.Row, locationAddress.Start.Column + merge.Cells - 1)
: ExcelCellBase.GetAddress(locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.Start.Row + merge.Cells - 1, locationAddress.Start.Column);
ExcelRange cell = aggregateWorksheet.Cells[range];
cell.StyleName = cell.StyleName = locationAddress.End.Row.IsOdd()
? $"{safeStyle.Name}_Alternate"
: safeStyle.Name ?? XlsxBaseStyle.NameOfDefaultStyle;
if (style.Content.Show == YesNo.Yes)
{
var formula = new XlsxFormulaResolver(aggregate)
{
HasAutoFilter = aggregate.HasAutoFilter, WorkSheet = sheetName
};
cell.Formula = formula.Resolve();
if (merge.Cells > 1)
{
cell.Merge = true;
}
}
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
