public async Task Spreadsheet_StartWorksheet_ColumnWidth(double?width)
{
// Arrange
var worksheetOptions = new WorksheetOptions();
worksheetOptions.Column(1).Width = width;
const double defaultWidthInExcelUi = 8.43;
const double defaultWidthInXml = defaultWidthInExcelUi + 0.71062;
var expectedWidth = width ?? defaultWidthInXml;
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 package = new ExcelPackage(stream);
var worksheet = package.Workbook.Worksheets.Single();
var actualWidth = worksheet.Column(1).Width;
Assert.Equal(expectedWidth, actualWidth, 5);
}
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);
}
private static void WriteSheetViewsXml(StringBuilder sb, WorksheetOptions options)
{
sb.Append("<sheetViews><sheetView workbookViewId=\"0\"><pane ");
if (options.FrozenColumns is not null)
{
sb.Append("xSplit=\"").Append(options.FrozenColumns.Value).Append("\" ");
}
if (options.FrozenRows is not null)
{
sb.Append("ySplit=\"").Append(options.FrozenRows.Value).Append("\" ");
}
sb.Append("topLeftCell=\"");
sb.AppendColumnName((options.FrozenColumns ?? 0) + 1);
sb.Append((options.FrozenRows ?? 0) + 1);
sb.Append("\" activePane=\"");
if (options.FrozenColumns is not null && options.FrozenRows is not null)
{
sb.Append("bottomRight");
}
