public void SavingWithExtendedObject_Succeeds()
{
var a = new Extended <object>(
new object(),
new Dictionary <string, object?>()
{
{ "a", "b" }
}
);
var fileInfo = GetXlsxTempFileInfo();
try
{
// Save
using (var s1 = new MagicSpreadsheet(fileInfo))
{
s1.AddSheet(new List <Extended <object> > {
a
});
s1.Save();
}
using var s2 = new MagicSpreadsheet(fileInfo);
s2.Load();
var b = s2.GetExtendedList <object>();
b.Should().NotBeNullOrEmpty();
var firstItem = b[0];
firstItem.Properties.Keys.Should().Contain("a");
firstItem.Properties["a"].Should().Be("b");
}
finally
{
fileInfo.Delete();
}
}
public void TablesWithSameDisplayNameShouldNotFail()
{
var a = new Extended <object>(
new object(),
new Dictionary <string, object?>()
{
{ "a", "b" }
}
);
var fileInfo = GetXlsxTempFileInfo();
try
{
// Save
using var s1 = new MagicSpreadsheet(fileInfo);
s1.AddSheet(new List <Extended <object> > {
a
}, "Sheet A", new AddSheetOptions {
TableOptions = new TableOptions {
DisplayName = "Table1"
}
});
s1.AddSheet(new List <Extended <object> > {
a
}, "Sheet B", new AddSheetOptions {
TableOptions = new TableOptions {
DisplayName = "Table1"
}
});
}
finally
{
fileInfo.Delete();
}
}
public void AddSheet_SheetWithExtraExtendedProperties_Succeeds()
{
var fileInfo = GetXlsxTempFileInfo();
try
{
using var s = new MagicSpreadsheet(fileInfo);
var sheetOptions = new AddSheetOptions
{
TableOptions = new TableOptions {
XlsxTableStyle = XlsxTableStyle.TableStyleDark1
}
};
s.AddSheet(new List <Extended <object> >
{
new Extended <object>(new object(), new Dictionary <string, object?> {
{ "Id", 10 },
{ "My Name", "Ryan" }
})
}, "Subscriptions", sheetOptions);
s.Save();
}
finally
{
fileInfo.Delete();
}
}
public void TablesWithAutoDisplayNameShouldSucceed()
{
var a = new Extended <object>(
new object(),
new Dictionary <string, object?>()
{
{ "a", "b" }
}
);
var fileInfo = GetXlsxTempFileInfo();
try
{
// Save
using var s1 = new MagicSpreadsheet(fileInfo);
s1.AddSheet(new List <Extended <object> > {
a
}, "Sheet A");
s1.AddSheet(new List <Extended <object> > {
a
}, "Sheet B");
s1.Save();
}
finally
{
fileInfo.Delete();
}
}
public void LoadParentChild_MissingColumnsOptionSet_Succeeds()
{
// Load the parent/child relationships
using var magicSpreadsheet = new MagicSpreadsheet(GetSheetFileInfo("ParentChild"), new Options { IgnoreUnmappedProperties = true });
magicSpreadsheet.Load();
magicSpreadsheet.GetList <ExtendedParentChildRelationship>();
// Loaded
}
public void LoadParentChild_MissingColumns_ThrowsException()
{
// Load the parent/child relationships
using var magicSpreadsheet = new MagicSpreadsheet(GetSheetFileInfo("ParentChild"));
magicSpreadsheet.Load();
Assert.ThrowsAny <Exception>(() => magicSpreadsheet.GetList <ExtendedParentChildRelationship>());
// Loaded
}
public void GetValuesAsDateTime_Succeeds()
{
// DateTimeTest and DateTimeTest2 have a couple of date formats in (and number formats, etc)
using var magicSpreadsheet = new MagicSpreadsheet(GetSheetFileInfo("DateTimeTest2"), new Options { StopProcessingOnFirstEmptyRow = true });
magicSpreadsheet.Load();
var items = magicSpreadsheet.GetExtendedList <object>("Sheet1");
// Loaded
}
public void LoadParentChild()
{
// Load the parent/child relationships
using var magicSpreadsheet = new MagicSpreadsheet(GetSheetFileInfo("ParentChild"));
magicSpreadsheet.Load();
magicSpreadsheet.GetList <ParentChildRelationship>();
// Loaded
}
public void LoadSheet_WithBinaryValues_Succeeds()
{
// Load the parent/child relationships
using var magicSpreadsheet = new MagicSpreadsheet(GetSheetFileInfo("LMREP-7413"), new Options { StopProcessingOnFirstEmptyRow = true });
magicSpreadsheet.Load();
var values = magicSpreadsheet.GetExtendedList <object>();
((bool?)values[0].Properties["IncludeSection2"]).Should().BeTrue();
}
public void TypesWithLists_Succeeds()
{
var fileInfo = GetXlsxTempFileInfo();
var dealerships = new List <CarDealership>
{
new CarDealership
{
Name = "Slough",
Cars = new List <Car?>(),
},
new CarDealership
{
Name = "Maidenhead",
Cars = new List <Car?>
{
new Car
{
Name = "Ford Prefect",
WeightKg = 1200
},
null,
},
},
new CarDealership
{
Name = "Reading",
Cars = new List <Car?>
{
new Car
{
Name = "Ford Prefect",
WeightKg = 1200
},
new Car
{
Name = "Ford Focus",
WeightKg = 1500
},
},
},
};
try
{
using var s = new MagicSpreadsheet(fileInfo);
s.AddSheet(dealerships);
s.Save();
}
finally
{
fileInfo.Delete();
}
}
public void GetValueFromFormula_Succeeds()
{
using var magicSpreadsheet = new MagicSpreadsheet(GetSheetFileInfo("FormulaTest"), new Options { StopProcessingOnFirstEmptyRow = true });
magicSpreadsheet.Load();
var items = magicSpreadsheet.GetExtendedList <object>("Sheet2");
// Check the items
Assert.NotEmpty(items);
Assert.True(items.Count > 0);
Assert.Equal("6", items[0].Properties["Total"]);
}
public void SaveSheet_WithNoData_Succeeds()
{
var fileInfo = GetXlsxTempFileInfo();
try
{
using var s = new MagicSpreadsheet(fileInfo);
s.Save();
}
finally
{
fileInfo.Delete();
}
}
public void WriteAndLoadBackAsExtended()
{
var tempFileInfo = GetXlsxTempFileInfo();
try
{
// Generate some data
var funkyAnimals = GetFunkyAnimals();
var cars = GetCars();
using (var magicSpreadsheet = new MagicSpreadsheet(tempFileInfo))
{
magicSpreadsheet.AddSheet(funkyAnimals);
magicSpreadsheet.AddSheet(cars);
magicSpreadsheet.Save();
}
// Reload
using (var magicSpreadsheet = new MagicSpreadsheet(tempFileInfo,
new Options
{
LoadNullExtendedProperties = true
}))
{
magicSpreadsheet.Load();
var sheetNames = magicSpreadsheet.SheetNames;
sheetNames.Should().Contain("FunkyAnimals");
sheetNames.Should().Contain("Cars");
var reloadedCars = magicSpreadsheet.GetList <Car>();
reloadedCars.Count.Should().Be(cars.Count);
var reloadedAnimals = magicSpreadsheet.GetExtendedList <SimpleAnimal>("FunkyAnimals");
reloadedAnimals.Count.Should().Be(funkyAnimals.Count);
// Make sure the extra fields are there in the additional items
Assert.All(reloadedAnimals, extendedAnimal => Assert.NotNull(extendedAnimal.Item));
Assert.All(reloadedAnimals, extendedAnimal => Assert.NotEqual(0, extendedAnimal.Item !.Id));
Assert.All(reloadedAnimals, extendedAnimal => Assert.NotNull(extendedAnimal.Item !.Name));
Assert.All(reloadedAnimals, extendedAnimal => Assert.NotNull(extendedAnimal.Properties));
Assert.All(reloadedAnimals, extendedAnimal => Assert.NotEmpty(extendedAnimal.Properties));
Assert.All(reloadedAnimals, extendedAnimal => Assert.NotNull(extendedAnimal.Properties.Select(p => p.Value)));
Assert.All(reloadedAnimals, extendedAnimal => Assert.All(extendedAnimal.Properties.Where(p => p.Key != nameof(FunkyAnimal.Description)).Select(p => p.Value), Assert.NotNull));
}
}
finally
{
// Clean up
tempFileInfo.Delete();
}
}
public void AddSheet_SheetWithStyle_Succeeds()
{
var fileInfo = GetXlsxTempFileInfo();
try
{
using var s = new MagicSpreadsheet(fileInfo);
var sheetOptions = new AddSheetOptions
{
TableOptions = new TableOptions
{
XlsxTableStyle = XlsxTableStyle.TableStyleDark1
}
};
s.AddSheet(new List <FunkyAnimal>
{
new FunkyAnimal {
Id = 0, Name = "Old Woman", WeightKg = 60, Leg_Count = 2
},
new FunkyAnimal {
Id = 1, Name = "Horse", WeightKg = 200, Leg_Count = 4
},
new FunkyAnimal {
Id = 2, Name = "Cow", WeightKg = 100, Leg_Count = 4
},
new FunkyAnimal {
Id = 3, Name = "Dog", WeightKg = 50, Leg_Count = 4
},
new FunkyAnimal {
Id = 4, Name = "Cat", WeightKg = 25, Leg_Count = 4
},
new FunkyAnimal {
Id = 5, Name = "Mouse", WeightKg = 0.1, Leg_Count = 4
},
new FunkyAnimal {
Id = 7, Name = "Spider", WeightKg = 0.01, Leg_Count = 8
},
new FunkyAnimal {
Id = 8, Name = "Fly", WeightKg = 0.001, Leg_Count = 6
}
}, "Animals", sheetOptions);
s.Save();
}
finally
{
fileInfo.Delete();
}
}
public void ExcludeProperties_ListSpecified_CorrectProperties()
{
var fileInfo = GetXlsxTempFileInfo();
try
{
var funkyAnimals = LoadSheetTests.GetFunkyAnimals();
var options = new AddSheetOptions
{
TableOptions = new TableOptions
{
Name = "FunkyAnimals",
DisplayName = "FunkyAnimals",
},
ExcludeProperties = new HashSet <string>
{
nameof(FunkyAnimal.Leg_Count),
nameof(FunkyAnimal.WeightKg),
nameof(FunkyAnimal.Description)
}
};
using (var s = new MagicSpreadsheet(fileInfo))
{
s.AddSheet(funkyAnimals, "FunkyAnimals", options);
s.Save();
}
// Reload the values back in and verify only the included properties exist
using (var s = new MagicSpreadsheet(fileInfo))
{
s.Load();
var reloadedAnimals = s.GetExtendedList <SimpleAnimal>("Sheet1");
Assert.Equal(funkyAnimals.Count, reloadedAnimals.Count);
// Make sure there are no extra properties
Assert.All(reloadedAnimals, extendedAnimal => Assert.Empty(extendedAnimal.Properties));
// Make sure items exist for every row
Assert.All(reloadedAnimals, extendedAnimal => Assert.NotNull(extendedAnimal.Item));
// Make sure that there are no "default" values we know are NOT in the test data
Assert.All(reloadedAnimals, extendedAnimal => Assert.NotEqual(0, extendedAnimal.Item !.Id));
Assert.All(reloadedAnimals, extendedAnimal => Assert.NotEqual(string.Empty, extendedAnimal.Item !.Name));
}
}
finally
{
fileInfo.Delete();
}
}
public void AddSheet_SheetNameTooLong_Fails(string badSheetName)
{
var fileInfo = GetXlsxTempFileInfo();
try
{
using var s = new MagicSpreadsheet(fileInfo);
Assert.Throws <ArgumentException>(() => s.AddSheet(new List <SimpleAnimal>(), badSheetName));
}
finally
{
fileInfo.Delete();
}
}
public void LoadAbc()
{
// Load the parent/child relationships
List <AbcThing?> things;
using (var magicSpreadsheet = new MagicSpreadsheet(GetSheetFileInfo("EnumTest")))
{
magicSpreadsheet.Load();
things = magicSpreadsheet.GetList <AbcThing>();
}
// Loaded
things.Count.Should().Be(6);
things[1]?.AbcEnum.Should().Be(AbcEnum.B);
}
public void AddSheet_SheetNameAlreadyExists_Fails()
{
var fileInfo = GetXlsxTempFileInfo();
try
{
using var s = new MagicSpreadsheet(fileInfo);
s.AddSheet(new List <SimpleAnimal>(), "Sheet1");
Assert.Throws <ArgumentException>(() => s.AddSheet(new List <SimpleAnimal>(), "Sheet1"));
}
finally
{
fileInfo.Delete();
}
}
public void LoadSheet_WithBlankRow_Succeeds()
{
// Load the parent/child relationships
List <ParentChildRelationship?> parentChildRelationships;
using (var magicSpreadsheet = new MagicSpreadsheet(GetSheetFileInfo("ParentChildWithBlankRows"), new Options {
StopProcessingOnFirstEmptyRow = true
}))
{
magicSpreadsheet.Load();
parentChildRelationships = magicSpreadsheet.GetList <ParentChildRelationship>();
}
// Loaded
parentChildRelationships.Count.Should().Be(3);
}
public void SheetNames_Succeeds()
{
// Load the parent/child relationships
List <string> sheetNames;
using (var magicSpreadsheet = new MagicSpreadsheet(GetSheetFileInfo("SitesAndDevices")))
{
magicSpreadsheet.Load();
sheetNames = magicSpreadsheet.SheetNames;
}
// Loaded
sheetNames.Count.Should().Be(2);
sheetNames[0].Should().NotBeNull();
sheetNames[0].Should().Be("Sites");
sheetNames[1].Should().NotBeNull();
sheetNames[1].Should().Be("Devices");
}
public void CheckFormats_Succeeds()
{
using var magicSpreadsheet = new MagicSpreadsheet(GetSheetFileInfo("Cell Formats"), new Options { StopProcessingOnFirstEmptyRow = true });
magicSpreadsheet.Load();
var items = magicSpreadsheet.GetExtendedList <object>("Sheet1");
// Check the items
Assert.NotEmpty(items);
Assert.True(items.Count > 0);
Assert.Equal("Happy Christmas!", items[0].Properties["General"]);
Assert.Equal("99.00", items[0].Properties["Number (N2)"]);
Assert.Equal("99.0", items[0].Properties["Number (N1)"]);
Assert.Equal("99", items[0].Properties["Number (N0)"]);
Assert.Equal("25/05/1975", items[0].Properties["Date"]);
Assert.Equal("50.0%", items[0].Properties["Percentage N1"]);
Assert.Equal("50.00%", items[0].Properties["Percentage N2"]);
Assert.Equal("Here is some text", items[0].Properties["Text"]);
}
public void SavingWithExtendedModel_Succeeds()
{
const int carWeightKg = 2200;
const string customPropertyName = "CustomPropertyName";
const string customPropertyValue = "CustomPropertyValue";
var car = new Extended <Car>(new Car
{
Id = 1,
Name = "Yumyum",
WheelCount = 4,
WeightKg = carWeightKg
},
new Dictionary <string, object?>
{
{ customPropertyName, customPropertyValue }
});
var fileInfo = GetXlsxTempFileInfo();
try
{
// Save
using (var s1 = new MagicSpreadsheet(fileInfo))
{
s1.AddSheet(new List <Extended <Car> > {
car
});
s1.Save();
}
using var s2 = new MagicSpreadsheet(fileInfo);
s2.Load();
var cars = s2.GetExtendedList <Car>();
cars.Should().NotBeNullOrEmpty();
var firstCar = cars[0];
firstCar.Item.Should().NotBeNull();
carWeightKg.Should().Be(firstCar.Item !.WeightKg);
firstCar.Properties.Keys.Should().Contain(customPropertyName);
firstCar.Properties[customPropertyName].Should().Be(customPropertyValue);
}
finally
{
fileInfo.Delete();
}
}
public void WriteAndLoadBack()
{
var tempFileInfo = GetXlsxTempFileInfo();
try
{
// Generate some data
var funkyAnimals = GetFunkyAnimals();
var cars = GetCars();
using (var magicSpreadsheet = new MagicSpreadsheet(tempFileInfo))
{
magicSpreadsheet.AddSheet(funkyAnimals);
magicSpreadsheet.AddSheet(cars);
magicSpreadsheet.Save();
}
// Reload
using (var magicSpreadsheet = new MagicSpreadsheet(tempFileInfo))
{
magicSpreadsheet.Load();
var sheetNames = magicSpreadsheet.SheetNames;
sheetNames.Should().Contain("FunkyAnimals");
sheetNames.Should().Contain("Cars");
var reloadedCars = magicSpreadsheet.GetList <Car>();
reloadedCars.Count.Should().Be(cars.Count);
var reloadedAnimals = magicSpreadsheet.GetList <FunkyAnimal>("FunkyAnimals");
reloadedAnimals.Count.Should().Be(funkyAnimals.Count);
}
}
finally
{
// Clean up
tempFileInfo.Delete();
}
}
public void AddSheetOptions_SheetWithExtendedPropertiesUnsorted_Succeeds()
{
var fileInfo = GetXlsxTempFileInfo();
try
{
using var s = new MagicSpreadsheet(fileInfo);
var sheetOptions = new AddSheetOptions
{
SortExtendedProperties = false
};
var animals = new Dictionary <string, object?> {
{ "Type", "Hamster" },
{ "Name", "Scruffy" }
};
s.AddSheet(new List <Extended <object> >
{
new Extended <object>(new object(), animals)
}, "Animals", sheetOptions);
s.Save();
// Reload the values back in and verify only the included properties exist
s.Load();
// TODO
var reloadedAnimals = s.GetExtendedList <object>("Animals");
//Assert.Equal(reloadedAnimals.[0].Key, "Type");
}
finally
{
fileInfo.Delete();
}
}
public void TweakStrings(string input, string expectedOutput) => Assert.Equal(expectedOutput, MagicSpreadsheet.TweakString(input));
public void AddSheetOptions_CustomTableStyle_Succeeds()
{
var fileInfo = GetXlsxTempFileInfo();
try
{
using var s = new MagicSpreadsheet(fileInfo, new Options
{
TableStyles = new List <CustomTableStyle>
{
new CustomTableStyle
{
Name = "My Table Style",
HeaderRowStyle = new TableRowStyle
{
BackgroundColor = Color.FromArgb(112, 48, 160),
FontColor = Color.White,
FontWeight = FontWeight.Bold
},
OddRowStyle = new TableRowStyle
{
BackgroundColor = Color.FromArgb(225, 204, 240),
},
EvenRowStyle = new TableRowStyle
{
BackgroundColor = Color.LightYellow,
},
WholeTableStyle = new TableRowStyle
{
InnerBorderColor = Color.Red,
OuterBorderColor = Color.Blue
},
}
}
});
var sheetOptions = new AddSheetOptions
{
SortExtendedProperties = false,
TableOptions = new TableOptions
{
CustomTableStyle = "My Table Style"
}
};
var scruffy = new Dictionary <string, object?> {
{ "Type", "Hamster" },
{ "Name", "Scruffy" }
};
var wuffy = new Dictionary <string, object?> {
{ "Type", "Dog" },
{ "Name", "Wuffy" }
};
var puffy = new Dictionary <string, object?> {
{ "Type", "Fish" },
{ "Name", "Puffy" }
};
var gruffy = new Dictionary <string, object?> {
{ "Type", "Goat" },
{ "Name", "Gruffy" }
};
s.AddSheet(new List <Extended <object> >
{
new Extended <object>(new object(), scruffy),
new Extended <object>(new object(), wuffy),
new Extended <object>(new object(), puffy),
new Extended <object>(new object(), gruffy)
}, "Animals", sheetOptions);
s.Save();
}
finally
{
fileInfo.Delete();
}
}
