Masui Masanori

Posted on Jan 3, 2021 • Updated on Jan 8, 2021

【.NET 5】【WPF】【ClosedXML】Draw and print spreadsheets 1

#csharp #closedxml #wpf

Intro

From the last result, I can print any contents what are drawn on a canvas.

【.NET 5】【WPF】Edit and print PDF files 1

This time, I try drawing a spreadsheet on the canvas.

Environments

.NET ver.5.0.101
Microsoft.Extensions.DependencyInjection ver.5.0.1
ClosedXML ver.0.95.4

Get cell values from spreadsheets

First, I get cell informations by ClosedXML.

Font.cs

using System.Drawing;
namespace PdfPrintSample.Spreadsheets.Values
{
    public record Font
    {
        public string Name { get; init; } = "Meiryo UI";
        public double Size { get; init; } = 12d;
        public Color Color { get; init; } = Color.Black;
        public bool Bold { get; init; }
        public bool Italic { get; init; }
        public string Underline { get; init; } = "";

    }
}

Border.cs

using System.Drawing;
namespace PdfPrintSample.Spreadsheets.Values
{
    public record Border
    {
        public string Style { get; init; } = "None";
        public Color Color { get; init; } = Color.Black;
    }
}

Borders.cs

namespace PdfPrintSample.Spreadsheets.Values
{
    public record Borders
    {
        public Border Left { get; init; } = new Border();
        public Border Top { get; init; } = new Border();
        public Border Right { get; init; } = new Border();
        public Border Bottom { get; init; } = new Border();
        // /
        public Border DiagonalUp { get; init; } = new Border();
        // \
        public Border DiagonalDown { get; init; } = new Border();
    }
}

MergedRange.cs

namespace PdfPrintSample.Spreadsheets.Values
{
    public record MergedRange
    {
        public int ColumnFrom { get; init; }
        public int RowFrom { get; init; }
        public int ColumnTo { get; init; }
        public int RowTo { get; init; }
    }
}

Cell.cs

using System.Drawing;
namespace PdfPrintSample.Spreadsheets.Values
{
    public record Cell
    {
        public int Row { get; init; }
        public int Column { get; init; }
        public double Height { get; init; }
        public double Width { get; init; }
        public string Text { get; init; } = "";
        public Color BackgroundColor { get; init; }
        public Font Font { get; init; } = new Font();
        public Borders Borders { get; init; } = new Borders();
        public MergedRange? MergedRange { get; init; }
    }
}

Worksheet.cs

using System.Collections.Generic;
namespace PdfPrintSample.Spreadsheets.Values
{
    public class Worksheet
    {
        public string Name { get; init; } = "";
        public List<Cell> Cells { get; init; } = new List<Cell>();
    }
}

Get from a spreadsheet

ISpreadsheetLoader.cs

using PdfPrintSample.Spreadsheets.Values;
namespace PdfPrintSample.Spreadsheets
{
    public interface ISpreadsheetLoader
    {
        Worksheet? Load(string filePath, string sheetName);
    }
}

SpreadsheetLoader.cs

using System.Drawing;
using System;
using System.IO;
using ClosedXML.Excel;
using NLog;
using PdfPrintSample.Spreadsheets.Values;

namespace PdfPrintSample.Spreadsheets
{
    public class SpreadsheetLoader: ISpreadsheetLoader
    {
        private readonly Logger logger = LogManager.GetCurrentClassLogger();
        public Worksheet? Load(string filePath, string sheetName)
        {
            if(File.Exists(filePath) == false)
            {
                logger.Error("File was not found");
                return null;
            }
            try
            {
                using(var book = new XLWorkbook(filePath))
                {
                    var sheet = book.Worksheet(sheetName);

                    var result = new Values.Worksheet{
                        Name = sheetName,
                    };
                    for(var column = 1; column <= 6; column++)
                    {
                        for(var row = 1; row <= 6; row++)
                        {
                            result.Cells.Add(GetCell(sheet, column, row));
                        }
                    }
                    return result;
                }
            }
            catch(Exception ex)
            {
                logger.Error(ex.Message);
                return null;
            }
        }
        public Values.Cell GetCell(IXLWorksheet sheet, int column, int row)
        {
            var cell = sheet.Cell(row, column);

            return new Values.Cell
            {
                Row = row,
                Column = column,
                Height = sheet.Row(row).Height,
                Width = sheet.Column(column).Width,
                Text = cell.GetString(),
                BackgroundColor = cell.Style.Fill.BackgroundColor.Color,
                Font = GetFont(cell),
                Borders = GetBorders(cell),
                MergedRange = GetMergedRange(cell),
            };
        }
        public Values.Borders GetBorders(IXLCell cell)
        {
            var border = cell.Style.Border;
            var diagonalBorder = new Border
            {
                Style = border.DiagonalBorder.ToString(),
                Color = GetColor(border.DiagonalBorderColor),
            };

            return new Values.Borders
            {
                Left = new Border {
                    Style = border.LeftBorder.ToString(),
                    Color = GetColor(border.LeftBorderColor),
                },
                Top = new Border 
                {
                    Style = border.TopBorder.ToString(),
                    Color = GetColor(border.TopBorderColor),
                },
                Right = new Border 
                {
                    Style = border.RightBorder.ToString(),
                    Color = GetColor(border.RightBorderColor),
                },
                Bottom = new Border 
                {
                    Style = border.TopBorder.ToString(),
                    Color = GetColor(border.TopBorderColor),
                },
                DiagonalUp = (border.DiagonalUp)? diagonalBorder: new Border(),
                DiagonalDown = (border.DiagonalDown)? diagonalBorder: new Border(),
            };
        }
        public Values.Font GetFont(IXLCell cell)
        {
            return new Values.Font
            {
                Name = cell.Style.Font.FontName,
                Size = cell.Style.Font.FontSize,
                Color = GetColor(cell.Style.Font.FontColor),
                Bold = cell.Style.Font.Bold,
                Italic = cell.Style.Font.Italic,
                Underline = cell.Style.Font.Underline.ToString(),
            };
        }
        public Values.MergedRange? GetMergedRange(IXLCell cell)
        {
            var mergedRange = cell.MergedRange();
            if(mergedRange == null)
            {
                return null;
            }
            var cellFrom = mergedRange.FirstCell();
            var cellTo = mergedRange.LastCell();
            return new Values.MergedRange
            {
                ColumnFrom = cellFrom.Address.ColumnNumber,
                RowFrom = cellFrom.Address.RowNumber,
                ColumnTo = cellTo.Address.ColumnNumber,
                RowTo = cellTo.Address.RowNumber,
            };
        }
        public Color GetColor(XLColor color)
        {
            if(color.ColorType == XLColorType.Color)
            {
                return color.Color;
            }
            return Color.Black;
        }
    }
}

Getting Font Colors & Border Colors

Fonts and Borders have two color types.

When their color types are "Color", I can get color from "XLColor.Color".
But if they are "Theme", I must use "XLColor.ThemeColor" or I will get an error.

DI

Last time, I read PDF and print it in the MainWindow's code behind.
But now, ther are many functions in the project, so I try separating them from the code behind.

First, I add DI container.

Using DI in WPF app is pretty much the same as with the Console app.

【.NET Core】Play with Console App

Because WPF doesn't have a "program.cs" and a main method, I need adding using DI codes in App.xaml and its code behind.

App.xaml

<Application x:Class="PdfPrintSample.App"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:local="clr-namespace:PdfPrintSample"
             Startup="Start">
    <Application.Resources>
    </Application.Resources>
</Application>

App.xaml.cs

using System;
using System.Windows;
using Microsoft.Extensions.DependencyInjection;
using PdfPrintSample.Main;
using PdfPrintSample.Spreadsheets;

namespace PdfPrintSample
{
    public partial class App : Application
    {
        public void Start(object sender, StartupEventArgs e)
        {
            var servicesProvider = BuildDi();
            using (servicesProvider as IDisposable)
            {
                var main = servicesProvider.GetRequiredService<MainWindow>();
                main.Show();
            }
        }
        private static IServiceProvider BuildDi()
        {
            var services = new ServiceCollection();
            services.AddScoped<MainWindow>();
            services.AddScoped<MainViewModel>();
            services.AddScoped<ISpreadsheetLoader, SpreadsheetLoader>();
            return services.BuildServiceProvider();
        }
    }
}

MainWindow.xaml.cs

...
namespace PdfPrintSample.Main
{
    public partial class MainWindow : Window
    {
        private readonly Logger logger = LogManager.GetCurrentClassLogger();
        private readonly ISpreadsheetLoader spreadsheets;
        public MainWindow(ISpreadsheetLoader spreadsheets)
        {
            this.spreadsheets = spreadsheets;
            InitializeComponent();
        }
...

Application.Startup Event (System.Windows) | Microsoft Docs

DEV Community