ExcelORM/ExcelORM/ExcelORMTests/WriterTests.cs

using ExcelORM;
using ExcelORM.Models;

namespace ExcelORMTests;

public class WriterTests
{
    private static readonly Test[] ArrayOfThree =
    [
        new() { Name = "Bilbo", Surname = "Baggins", Job = "Eater"},
        new() { Name = "John", Job = "Policeman"},
        new() { Name = "Bruce", Surname = "Lee", Job = "Fighter"}
    ];

    private static readonly List<Test> ListOfTwo =
    [
        new() { Name = "Elon", Surname = "Musk", Job = "Comedian" },
        new() { Name = "Donald", Surname = "Trump", Job = "Bankrupt" }
    ];

    [Fact]
    public void WriteWithAppend()
    {
        var testFile = Path.GetRandomFileName();
        testFile = Path.ChangeExtension(testFile, "xlsx");

        const string worksheetName = "Test";
        using var writer = new ExcelWriter(testFile);
        writer.Write(ArrayOfThree, worksheetName);
        writer.SaveAs(testFile);

        using var reader = new ExcelReader(testFile);
        var readArray = reader.Read<Test>(worksheetName).ToArray();
        Assert.Equal(3, readArray.Length);

        for (int i = 0; i < readArray.Length; i++)
            Assert.Equal(ArrayOfThree[i], readArray[i]);

        writer.Write(ListOfTwo, worksheetName, true);
        writer.SaveAs(testFile);
        
        using var newReader = new ExcelReader(testFile);
        Assert.Equal(5, newReader.Read<Test>(worksheetName).Count());
        File.Delete(testFile);
    }
    
    [Fact]
    public void WriteWithAppendWithoutName()
    {
        var testFile = Path.GetRandomFileName();
        testFile = Path.ChangeExtension(testFile, "xlsx");

        using var writer = new ExcelWriter(testFile);
        writer.Write(ArrayOfThree);
        writer.SaveAs(testFile);

        using var reader = new ExcelReader(testFile);
        Assert.Equal(ArrayOfThree.Length, reader.Read<Test>().Count());

        writer.Write(ListOfTwo, append: true);
        writer.SaveAs(testFile);
        
        using var newReader = new ExcelReader(testFile);
        Assert.Equal(5, newReader.Read<Test>().Count());
        File.Delete(testFile);
    }

    private const string ForAppend = "testFiles/forAppend.xlsx";

    [Fact]
    public void WriteWithAppendExisting()
    {
        var testFile = Path.GetRandomFileName();
        testFile = Path.ChangeExtension(testFile, "xlsx");
        File.Copy(ForAppend, testFile);

        uint headerRowIndex = 3;
        using var writer = new ExcelWriter(testFile);
        writer.Write(ArrayOfThree, append: true, headerRowIndex: headerRowIndex);
        writer.SaveAs(testFile);

        using var reader = new ExcelReader(testFile);
        var readArray = reader.Read<Test>(startFrom: headerRowIndex).ToArray();
        Assert.Equal(6, readArray.Length);

        for (int i = 0; i < ArrayOfThree.Length; i++)
            Assert.Equal(ArrayOfThree[i], readArray[i+3]);

        File.Delete(testFile);
    }

    private const string ForAppendWithRubbish = "testFiles/forAppendWithRubbish.xlsx";
    
    [Fact]
    public void WriteWithAppendExistingAndRubbish()
    {
        var testFile = Path.GetRandomFileName();
        testFile = Path.ChangeExtension(testFile, "xlsx");
        File.Copy(ForAppendWithRubbish, testFile);

        uint headerRowIndex = 3;
        using var writer = new ExcelWriter(testFile);
        writer.Write(ArrayOfThree, append: true, headerRowIndex: headerRowIndex, startFrom: 7);
        writer.SaveAs(testFile);

        using var reader = new ExcelReader(testFile);
        var readArray = reader.Read<Test>(startFrom: headerRowIndex).ToArray();
        Assert.Equal(6, readArray.Length);

        for (int i = 0; i < ArrayOfThree.Length; i++)
            Assert.Equal(ArrayOfThree[i], readArray[i+3]);

        File.Delete(testFile);
    }
    
    private const string ForAppendHeaderFirst = "testFiles/forAppendHeaderFirst.xlsx";

    [Fact]
    public void WriteWithAppendExistingHeaderFirst()
    {
        var testFile = Path.GetRandomFileName();
        testFile = Path.ChangeExtension(testFile, "xlsx");
        File.Copy(ForAppendHeaderFirst, testFile);

        using var writer = new ExcelWriter(testFile);
        writer.Write(ArrayOfThree, append: true);
        writer.SaveAs(testFile);

        using var reader = new ExcelReader(testFile);
        var readArray = reader.Read<Test>().ToArray();
        Assert.Equal(6, readArray.Length);

        for (int i = 0; i < ArrayOfThree.Length; i++)
            Assert.Equal(ArrayOfThree[i], readArray[i + 3]);

        File.Delete(testFile);
    }
    
    private const string EmptyFileForAppend = "testFiles/emptyForAppend.xlsx";
    [Fact]
    public void WriteWithAppendEmpty()
    {
        var testFile = Path.GetRandomFileName();
        testFile = Path.ChangeExtension(testFile, "xlsx");
        File.Copy(EmptyFileForAppend, testFile);

        using var writer = new ExcelWriter(testFile);
        writer.Write(ArrayOfThree, append: true);
        writer.SaveAs(testFile);

        using var reader = new ExcelReader(testFile);
        var readArray = reader.Read<Test>().ToArray();
        Assert.Equal(3, readArray.Length);

        for (int i = 0; i < ArrayOfThree.Length; i++)
            Assert.Equal(ArrayOfThree[i], readArray[i]);
        
        File.Delete(testFile);
    }

    [Fact]
    public void WriteDifferentTypes()
    {
        var testFile = Path.GetRandomFileName();
        testFile = Path.ChangeExtension(testFile, "xlsx");

        var expected = new TestTypes
        {
            Date = DateTime.Now,
            TimeSpan = TimeSpan.FromSeconds(360),
            Double = 2.33,
            Int = 1024,
            Text = "Test"
        };
        
        var list = new List<TestTypes>{ expected };
        
        using var writer = new ExcelWriter(testFile);
        writer.Write(list);
        writer.SaveAs(testFile);

        using var reader = new ExcelReader(testFile);
        var result = reader.Read<TestTypes>().ToList();
        Assert.Single(result);
        var first = result.First();
        Assert.Equal(expected.Date.ToString(), first.Date.ToString());
        Assert.NotNull(first.TimeSpan);
        Assert.Equal(expected.TimeSpan.Value.Minutes, first.TimeSpan.Value.Minutes);
        Assert.Equal(expected.Double, first.Double);
        Assert.Equal(expected.Int, first.Int);
        Assert.Equal(expected.Text, first.Text);

        File.Delete(testFile);
    }

    private static readonly TestSkip[] ArrayWithSkip =
    [
        new() {Text = "Lorem", Date = DateTime.Now.AddHours(1), Int = 1},
        new() {Text = "Ipsum", Date = null, Int = 2}
    ];

    [Fact]
    public void WriteWithSkip()
    {
        var testFile = Path.GetRandomFileName();
        testFile = Path.ChangeExtension(testFile, "xlsx");

        const string worksheetName = "Test";
        using var writer = new ExcelWriter(testFile);
        writer.Write(ArrayWithSkip, worksheetName);
        writer.SaveAs(testFile);

        using var reader = new ExcelReader(testFile);
        var readArray = reader.Read<TestSkip>(worksheetName).ToArray();
        Assert.Equal(ArrayWithSkip.Length, readArray.Length);

        for (int i = 0; i < readArray.Length; i++)
        {
            Assert.NotEqual(ArrayWithSkip[i].Text, readArray[i].Text);
            Assert.Equal(ArrayWithSkip[i].Date.ToString(), readArray[i].Date.ToString());
            Assert.Equal(ArrayWithSkip[i].Int, readArray[i].Int);
        }

        File.Delete(testFile);
    }

    private static readonly TestSkipMiddle[] ArrayWithSkipMiddle =
    [
        new() {Text = "Lorem", Date = DateTime.Now.AddHours(1), Int = 1},
        new() {Text = "Ipsum", Date = DateTime.Now.AddHours(2), Int = 2}
    ];

    [Fact]
    public void WriteWithSkipMiddle()
    {
        var testFile = Path.GetRandomFileName();
        testFile = Path.ChangeExtension(testFile, "xlsx");

        const string worksheetName = "Test";
        using var writer = new ExcelWriter(testFile);
        writer.Write(ArrayWithSkipMiddle, worksheetName);
        writer.SaveAs(testFile);

        using var reader = new ExcelReader(testFile);
        var readArray = reader.Read<TestSkipMiddle>(worksheetName).ToArray();
        Assert.Equal(ArrayWithSkipMiddle.Length, readArray.Length);

        for (int i = 0; i < readArray.Length; i++)
        {
            Assert.Equal(ArrayWithSkipMiddle[i].Text, readArray[i].Text);
            Assert.NotEqual(ArrayWithSkipMiddle[i].Date.ToString(), readArray[i].Date.ToString());
            Assert.Equal(ArrayWithSkipMiddle[i].Int, readArray[i].Int);
        }

        File.Delete(testFile);
    }

    private static readonly TestWithFormula[] ArrayWithFormulas =
    [
        new() { Name = "Bilbo", Surname = "Baggins", Job = "Eater", FullName = new Formula{ FormulaA1 = "B2&C2" } },
        new() { Name = "John", Job = "Policeman", FullName = new Formula{ FormulaA1 = "B3&C3" } },
        new() { Name = "Bruce", Surname = "Lee", Job = "Fighter", FullName = new Formula{ FormulaA1 = "B4&C4" } }
    ];

    [Fact]
    public void WriteWithFormula()
    {
        var testFile = Path.GetRandomFileName();
        testFile = Path.ChangeExtension(testFile, "xlsx");

        using var writer = new ExcelWriter(testFile);
        writer.Write(ArrayWithFormulas);
        writer.SaveAs(testFile);

        using var reader = new ExcelReader(testFile);
        var readArray = reader.Read<TestWithFormula>().ToArray();
        Assert.Equal(ArrayWithFormulas.Length, readArray.Length);

        foreach (var item in readArray)
        {
            Assert.NotNull(item.FullName);
            Assert.Equal($"{item.Name}{item.Surname}", item.FullName.Value);
        }
        
        File.Delete(testFile);
    }

    private static readonly TestNumbersWithFormula[] ArrayNumbersWithFormulas =
    [
        new(){ First = 1, Second = 2, Sum = new Formula{FormulaA1 = "SUM(A2:B2)"} },
        new(){ First = 2, Second = 3, Sum = new Formula{FormulaA1 = "SUM(A3:B3)"} }
    ];

    [Fact]
    public void NumbersWithFormula()
    {
        var testFile = Path.GetRandomFileName();
        testFile = Path.ChangeExtension(testFile, "xlsx");

        using var writer = new ExcelWriter(testFile);
        writer.Write(ArrayNumbersWithFormulas);
        writer.SaveAs(testFile);

        using var reader = new ExcelReader(testFile);
        var readArray = reader.Read<TestNumbersWithFormula>().ToArray();
        Assert.Equal(ArrayNumbersWithFormulas.Length, readArray.Length);

        foreach (var item in readArray)
        {
            Assert.NotNull(item.Sum);
            Assert.Equal(item.First + item.Second, item.Sum.Value);
        }

        File.Delete(testFile);
    }

    private static readonly TestWithHyperlink[] ArrayWithHyperlinks =
    [
        new() { Name = "Bilbo", Surname = "Baggins", Job = "Eater", Link = new Hyperlink{ Value = "Wiki", Link = new XLHyperlink("https://en.wikipedia.org/wiki/Bilbo_Baggins") } },
        new() { Name = "John", Job = "Policeman", Link = new Hyperlink{ Value = "CNN", Link = new XLHyperlink("https://edition.cnn.com/2023/12/10/us/john-okeefe-boston-police-death-cec/index.html") } },
        new() { Name = "Bruce", Surname = "Lee", Job = "Fighter", Link = new Hyperlink{ Value = "IMDb", Link = new XLHyperlink("https://www.imdb.com/name/nm0000045/") } }
    ];

    [Fact]
    public void WriteWithHyperlink()
    {
        var testFile = Path.GetRandomFileName();
        testFile = Path.ChangeExtension(testFile, "xlsx");

        using var writer = new ExcelWriter(testFile);
        writer.Write(ArrayWithHyperlinks);
        writer.SaveAs(testFile);

        using var reader = new ExcelReader(testFile);
        var readArray = reader.Read<TestWithHyperlink>().ToArray();
        Assert.Equal(ArrayWithFormulas.Length, readArray.Length);

        for (var i = 0; i < readArray.Length; i++)
        {
            Assert.NotNull(ArrayWithHyperlinks[i].Link);
            Assert.NotNull(readArray[i].Link);
            Assert.Equal(ArrayWithHyperlinks[i].Link?.Value, readArray[i].Link?.Value);
            Assert.Equal(ArrayWithHyperlinks[i].Link?.Link?.ToString(), readArray[i].Link?.Link?.ToString());
        }
        
        File.Delete(testFile);
    }

    [Fact]
    public void WriteReadInMemory()
    {
        using var workbook = new XLWorkbook();
        const string worksheetName = "Test";
        using var writer = new ExcelWriter(workbook);
        writer.Write(ArrayOfThree, worksheetName);

        using var reader = new ExcelReader(workbook);
        var readArray = reader.Read<Test>(worksheetName).ToArray();
        Assert.Equal(3, readArray.Length);

        for (int i = 0; i < readArray.Length; i++)
            Assert.Equal(ArrayOfThree[i], readArray[i]);
    }
    
    [Fact]
    public void WriteWithStartFrom()
    {
        var testFile = Path.GetRandomFileName();
        testFile = Path.ChangeExtension(testFile, "xlsx");

        const string worksheetName = "Test";
        using var writer = new ExcelWriter(testFile);
        writer.Write(ArrayOfThree, worksheetName);
        writer.Write(ListOfTwo, worksheetName, startFrom: (uint)(ArrayOfThree.Length + 3));
        writer.SaveAs(testFile);

        using var reader = new ExcelReader(testFile);
        var readArray = reader.Read<Test>(worksheetName).ToArray();
        Assert.Equal(6, readArray.Length);
        
        File.Delete(testFile);
    }
    
    [Fact]
    public void FallbackToPropertyName()
    {
        using var workbook = new XLWorkbook();
        var worksheet = workbook.AddWorksheet("Test");
        var firstRow = worksheet.FirstRow();
        var secondRow = worksheet.Row(2);
        var columnIndex = 1;
        
        foreach (var property in typeof(Test).GetProperties())
        {
            firstRow.Cell(columnIndex).Value = property.Name;
            secondRow.Cell(columnIndex).Value = $"{columnIndex}";
            columnIndex++;
        }
        
        var test = new Test{ Name = $"{columnIndex++}", Surname = $"{columnIndex++}", Job = $"{columnIndex}" };
        var writer = new ExcelWriter(workbook);
        writer.FallbackToPropertyName = true;
        writer.Write([test], append: true);
        
        var reader = new ExcelReader(workbook);
        reader.FallbackToPropertyName = true;
        var results = reader.Read<Test>().ToArray();
        Assert.Equal(2, results.Length);
        Assert.Equal(test, results[1]);
    }
}