SpreadsheetUtility 1.4.0

SpreadsheetUtility

Ultra lightweight spreadsheet utility to display processed collections of data and occasionally read it

Features

• Uses XLSX file format
• Writes public properties of a collection into a dedicated sheet
• Reads sheet data into an Enumerator(List)
• Supports multiple sheets
• Auto-fits column width and freezes the first row for comfortable viewing
• Can set a startup sheet
• Supports type independent sheet names
• Supports custom string formatting
• Supports color scale formatting
• Supports horizontal and vertical data layout
• Can exclude specific properties from writing to the spreadsheet
• Supports formulas referencing values in the same entry
• Can set tooltips to columns in the form of a comment on the title cell

Tutorial

Let's create an employee class to store in a spreadsheet.

class Employee
{
    public string? Name { get; set; }
    public string? Position { get; set; }

    [Format("0$")]
    [ColorScale("red", "#00FF00" /* green */)]
    public decimal Salary { get; set; }

    public Employee() { }

    public Employee(string name, string position, decimal salary)
    {
        Name = name;
        Position = position;
        Salary = salary;
    }
}

Now we can make an array of company's employees.

var employees = new[]
{
    new Employee("John", "CEO", 10000),
    new Employee("Steve", "Manager", 6000),
    new Employee("Will", "Senior Software Engineer", 4000),
    new Employee("Kate", "Software Engineer", 2000),
    new Employee("Paul", "Quality Assurance", 1000)
};

This array can now go into the spreadsheet.

using (var spreadsheet = new Spreadsheet("Company.xlsx"))
{
    spreadsheet.Write(employees);
}

Here is how this data looks in the spreadsheet.

And if we need to read some of that data back, we can do it too.

using (var spreadsheet = new Spreadsheet("Company.xlsx"))
{
    foreach (var employee in spreadsheet.Read<Employee>())
    {
        Console.WriteLine($"Salary: {employee.Salary} \t Position: {employee.Position}");
    }
}

You can review the whole tutorial here

Additional features

Layout

By default all sheets will have a horizontal data layout but we can change it to vertical using Layout attribute.

[Layout(Flow.Vertical)]
class Employee
{
    public string? Name { get; set; }
    public string? Position { get; set; }

    [Format("0$")]
    [ColorScale("red", "#00FF00" /* green */)]
    public decimal Salary { get; set; }

    public Employee() { }

    public Employee(string name, string position, decimal salary)
    {
        Name = name;
        Position = position;
        Salary = salary;
    }
}

Here is how it looks in the spreadsheet.

Hidden attribute

If there is no need to export a property to the spreadsheet we can exclude it via Hidden attribute.

class Employee
{
    [Hidden]
    public string? Name { get; set; }
    public string? Position { get; set; }
    ...
}

Formula

Sometimes we want to have cells that update in real time or react to the changes we make in the spreadsheet. For this case we can use formulas. Keep in mind though that formulas can only reference properties in the same line. Also note that we don't declare a setter for formula property as we don't really need to read the formula back.

class Employee
{
    ...
    [Format("0$")]
    [ColorScale("red", "#00FF00" /* green */)]
    public decimal Salary { get; set; }

    public string DesiredSalary => $"= {nameof(Salary)} * 2";
    ...
}

Tooltip

If descriptive property name would take too much space, use shortened name and set a tooltip explaining what data is contained in the slot.

class Employee
{
    [Tooltip("I don't care about surnames")]
    public string? Name { get; set; }
    public string? Position { get; set; }
    ...
}