public async Task Spreadsheet_StartWorksheet_NoFreezing()
{
// Arrange
var worksheetOptions = new WorksheetOptions();
using var stream = new MemoryStream();
await using (var spreadsheet = await Spreadsheet.CreateNewAsync(stream))
{
// Act
await spreadsheet.StartWorksheetAsync("My sheet", worksheetOptions);
await spreadsheet.FinishAsync();
}
// Assert
SpreadsheetAssert.Valid(stream);
using var actual = SpreadsheetDocument.Open(stream, true);
var worksheet = actual.WorkbookPart !.WorksheetParts.Select(x => x.Worksheet).Single();
Assert.Null(worksheet.SheetViews);
}
public async Task Spreadsheet_AddDataValidation_DecimalValue(string op, XLOperator expectedOperator)
{
// Arrange
const double value = 123.45;
var validation = op switch
{
"eq" => DataValidation.DecimalEqualTo(value),
"neq" => DataValidation.DecimalNotEqualTo(value),
"gt" => DataValidation.DecimalGreaterThan(value),
"gte" => DataValidation.DecimalGreaterThanOrEqualTo(value),
"lt" => DataValidation.DecimalLessThan(value),
"lte" => DataValidation.DecimalLessThanOrEqualTo(value),
_ => throw new ArgumentOutOfRangeException(nameof(op))
};
using var stream = new MemoryStream();
await using (var spreadsheet = await Spreadsheet.CreateNewAsync(stream))
{
await spreadsheet.StartWorksheetAsync("Sheet");
// Act
spreadsheet.AddDataValidation("A1", validation);
await spreadsheet.FinishAsync();
}
// Assert
SpreadsheetAssert.Valid(stream);
using var workbook = new XLWorkbook(stream);
var worksheet = workbook.Worksheets.Single();
var actualValidation = Assert.Single(worksheet.DataValidations);
var actualRange = Assert.Single(actualValidation.Ranges);
var cell = Assert.Single(actualRange.Cells());
Assert.Equal(1, cell.Address.ColumnNumber);
Assert.Equal(1, cell.Address.RowNumber);
Assert.Equal(XLAllowedValues.Decimal, actualValidation.AllowedValues);
Assert.Equal(expectedOperator, actualValidation.Operator);
Assert.Equal(value.ToString(CultureInfo.InvariantCulture), actualValidation.MinValue);
Assert.Empty(actualValidation.MaxValue);
}
public async Task Spreadsheet_AddRow_MixedCellTypeRows(bool firstRowStyled, Type type)
{
// Arrange
const string firstCellValue = "First";
const string secondCellValue = "Second";
using var stream = new MemoryStream();
await using (var spreadsheet = await Spreadsheet.CreateNewAsync(stream))
{
await spreadsheet.StartWorksheetAsync("Sheet");
var style = new Style();
style.Font.Bold = true;
var styleId = spreadsheet.AddStyle(style);
// Act
if (firstRowStyled)
{
await spreadsheet.AddRowAsync(CellFactory.Create(type, firstCellValue, styleId));
await spreadsheet.AddRowAsync(new DataCell(secondCellValue));
}
else
{
await spreadsheet.AddRowAsync(new DataCell(firstCellValue));
await spreadsheet.AddRowAsync(CellFactory.Create(type, secondCellValue, styleId));
}
await spreadsheet.FinishAsync();
}
// Assert
SpreadsheetAssert.Valid(stream);
using var workbook = new XLWorkbook(stream);
var worksheet = workbook.Worksheets.Single();
var actualFirstCell = worksheet.Cell(1, 1);
var actualSecondCell = worksheet.Cell(2, 1);
Assert.Equal(firstCellValue, actualFirstCell.Value);
Assert.Equal(secondCellValue, actualSecondCell.Value);
Assert.Equal(firstRowStyled, actualFirstCell.Style.Font.Bold);
Assert.Equal(!firstRowStyled, actualSecondCell.Style.Font.Bold);
}
public async Task Spreadsheet_AddRow_MultiFormatCellWithStringValue(bool formatting, Type type)
{
// Arrange
const string cellValue = "Formatting test";
var fontName = formatting ? "Arial" : null;
var fillColor = formatting ? Color.Green as Color? : null;
var fontColor = formatting ? Color.Red as Color? : null;
using var stream = new MemoryStream();
await using (var spreadsheet = await Spreadsheet.CreateNewAsync(stream))
{
await spreadsheet.StartWorksheetAsync("Sheet");
var style = new Style();
style.Fill.Color = fillColor;
style.Font.Bold = formatting;
style.Font.Color = fontColor;
style.Font.Italic = formatting;
style.Font.Name = fontName;
style.Font.Strikethrough = formatting;
var styleId = spreadsheet.AddStyle(style);
var styledCell = CellFactory.Create(type, cellValue, styleId);
// Act
await spreadsheet.AddRowAsync(styledCell);
await spreadsheet.FinishAsync();
}
// Assert
SpreadsheetAssert.Valid(stream);
using var workbook = new XLWorkbook(stream);
var worksheet = workbook.Worksheets.Single();
var actualCell = worksheet.Cell(1, 1);
Assert.Equal(cellValue, actualCell.Value);
Assert.Equal(formatting, actualCell.Style.Fill.BackgroundColor.Color == fillColor);
Assert.Equal(formatting, actualCell.Style.Font.Bold);
Assert.Equal(formatting, actualCell.Style.Font.FontColor.Color == fontColor);
Assert.Equal(formatting, string.Equals(actualCell.Style.Font.FontName, fontName, StringComparison.Ordinal));
Assert.Equal(formatting, actualCell.Style.Font.Italic);
Assert.Equal(formatting, actualCell.Style.Font.Strikethrough);
}
public async Task Spreadsheet_StartWorksheet_Freezing(int?columns, int?rows)
{
// Arrange
var worksheetOptions = new WorksheetOptions
{
FrozenColumns = columns,
FrozenRows = rows
};
var expectedColumnName = CellReferenceHelper.GetExcelColumnName((columns ?? 0) + 1);
var expectedCellReference = $"{expectedColumnName}{(rows ?? 0) + 1}";
var expectedActivePane = columns switch
{
not null when rows is not null => PaneValues.BottomRight,
not null => PaneValues.TopRight,
_ => PaneValues.BottomLeft
};
using var stream = new MemoryStream();
await using (var spreadsheet = await Spreadsheet.CreateNewAsync(stream))
{
// Act
await spreadsheet.StartWorksheetAsync("My sheet", worksheetOptions);
await spreadsheet.FinishAsync();
}
// Assert
SpreadsheetAssert.Valid(stream);
using var actual = SpreadsheetDocument.Open(stream, true);
var worksheet = actual.WorkbookPart !.WorksheetParts.Select(x => x.Worksheet).Single();
var sheetView = worksheet.SheetViews !.Cast <SheetView>().Single();
Assert.Equal(PaneStateValues.Frozen, sheetView.Pane !.State !.Value);
Assert.Equal(columns, (int?)sheetView.Pane.HorizontalSplit?.Value);
Assert.Equal(rows, (int?)sheetView.Pane.VerticalSplit?.Value);
Assert.Equal(expectedCellReference, sheetView.Pane.TopLeftCell?.Value);
Assert.Equal(expectedActivePane, sheetView.Pane.ActivePane?.Value);
}
