public async Task Spreadsheet_AddRow_EmptyMemoryRow(Type type)
{
// Arrange
using var stream = new MemoryStream();
await using (var spreadsheet = await Spreadsheet.CreateNewAsync(stream))
{
await spreadsheet.StartWorksheetAsync("Sheet");
var addRowTask = type switch
{
_ when type == typeof(Cell) => spreadsheet.AddRowAsync(ReadOnlyMemory <Cell> .Empty),
_ when type == typeof(DataCell) => spreadsheet.AddRowAsync(ReadOnlyMemory <DataCell> .Empty),
_ when type == typeof(StyledCell) => spreadsheet.AddRowAsync(ReadOnlyMemory <StyledCell> .Empty),
_ => throw new ArgumentOutOfRangeException(nameof(type), type, null)
};
// Act
await addRowTask;
await spreadsheet.FinishAsync();
}
// Assert
SpreadsheetAssert.Valid(stream);
using var actual = SpreadsheetDocument.Open(stream, true);
var sheetPart = actual.WorkbookPart !.WorksheetParts.Single();
Assert.Empty(sheetPart.Worksheet.Descendants <OpenXmlCell>());
}
public async Task Spreadsheet_AddRow_CellWithIntegerValue(int?value, Type type)
{
// Arrange
using var stream = new MemoryStream();
await using (var spreadsheet = await Spreadsheet.CreateNewAsync(stream))
{
await spreadsheet.StartWorksheetAsync("Sheet");
var cell = CellFactory.Create(type, value);
// Act
await spreadsheet.AddRowAsync(cell);
await spreadsheet.FinishAsync();
}
// Assert
SpreadsheetAssert.Valid(stream);
using var actual = SpreadsheetDocument.Open(stream, true);
var sheetPart = actual.WorkbookPart !.WorksheetParts.Single();
var actualCell = sheetPart.Worksheet.Descendants <OpenXmlCell>().Single();
Assert.Equal(CellValues.Number, actualCell.GetDataType());
Assert.Equal(value?.ToString() ?? string.Empty, actualCell.InnerText);
}
public async Task Spreadsheet_AddRow_CellWithVeryLongStringValue(int length, Type type)
{
// Arrange
var value = new string('a', length);
using var stream = new MemoryStream();
var options = new SpreadCheetahOptions {
BufferSize = SpreadCheetahOptions.MinimumBufferSize
};
await using (var spreadsheet = await Spreadsheet.CreateNewAsync(stream, options))
{
await spreadsheet.StartWorksheetAsync("Sheet");
var cell = CellFactory.Create(type, value);
// Act
await spreadsheet.AddRowAsync(cell);
await spreadsheet.FinishAsync();
}
// Assert
SpreadsheetAssert.Valid(stream);
using var actual = SpreadsheetDocument.Open(stream, true);
var sheetPart = actual.WorkbookPart !.WorksheetParts.Single();
var actualCell = sheetPart.Worksheet.Descendants <OpenXmlCell>().Single();
Assert.Equal(CellValues.InlineString, actualCell.DataType?.Value);
Assert.Equal(value, actualCell.InnerText);
}
public async Task Spreadsheet_AddRow_CellWithStringValue(string?value, Type type)
{
// Arrange
using var stream = new MemoryStream();
await using (var spreadsheet = await Spreadsheet.CreateNewAsync(stream))
{
await spreadsheet.StartWorksheetAsync("Sheet");
var cell = CellFactory.Create(type, value);
// Act
await spreadsheet.AddRowAsync(cell);
await spreadsheet.FinishAsync();
}
// Assert
SpreadsheetAssert.Valid(stream);
using var actual = SpreadsheetDocument.Open(stream, true);
var sheetPart = actual.WorkbookPart !.WorksheetParts.Single();
var actualCell = sheetPart.Worksheet.Descendants <OpenXmlCell>().Single();
CellValues?expectedDataType = value is null ? null : CellValues.InlineString;
Assert.Equal(expectedDataType, actualCell.DataType?.Value);
Assert.Equal(value ?? string.Empty, actualCell.InnerText);
}
public async Task Spreadsheet_AddAsRow_ObjectWithCustomType()
{
// Arrange
const string value = "value";
var customType = new CustomType("The name");
using var stream = new MemoryStream();
await using (var spreadsheet = await Spreadsheet.CreateNewAsync(stream))
{
await spreadsheet.StartWorksheetAsync("Sheet");
// Act
await spreadsheet.AddAsRowAsync(new RecordWithCustomType(customType, value));
await spreadsheet.FinishAsync();
}
// Assert
stream.Position = 0;
using var actual = SpreadsheetDocument.Open(stream, false);
var sheetPart = actual.WorkbookPart !.WorksheetParts.Single();
var cells = sheetPart.Worksheet.Descendants <OpenXmlCell>().ToList();
var actualCell = Assert.Single(cells);
Assert.Equal(value, actualCell.InnerText);
}
public async Task Spreadsheet_AddAsRow_ObjectWithProperties(ObjectType type)
{
// Arrange
const string firstName = "Ola";
const string lastName = "Nordmann";
const int age = 30;
using var stream = new MemoryStream();
await using (var spreadsheet = await Spreadsheet.CreateNewAsync(stream))
{
await spreadsheet.StartWorksheetAsync("Sheet");
// Act
if (type == ObjectType.Class)
{
await spreadsheet.AddAsRowAsync(new ClassWithProperties(firstName, lastName, age));
}
else if (type == ObjectType.Record)
{
await spreadsheet.AddAsRowAsync(new RecordWithProperties(firstName, lastName, age));
}
else if (type == ObjectType.Struct)
{
await spreadsheet.AddAsRowAsync(new StructWithProperties(firstName, lastName, age));
}
else if (type == ObjectType.ReadOnlyStruct)
{
await spreadsheet.AddAsRowAsync(new ReadOnlyStructWithProperties(firstName, lastName, age));
}
await spreadsheet.FinishAsync();
}
// Assert
stream.Position = 0;
using var actual = SpreadsheetDocument.Open(stream, false);
var sheetPart = actual.WorkbookPart !.WorksheetParts.Single();
var cells = sheetPart.Worksheet.Descendants <OpenXmlCell>().ToList();
Assert.Equal(firstName, cells[0].InnerText);
Assert.Equal(lastName, cells[1].InnerText);
Assert.Equal(age.ToString(), cells[2].InnerText);
Assert.Equal(3, cells.Count);
}
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_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);
}
