Intro
From the last result, I can print any contents what are drawn on a canvas.
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.
- GitHub - ClosedXML/ClosedXML
- 【.NET Core】【ClosedXML】Getting cell values 1
- 【.NET Core】【ClosedXML】Getting cell values 2
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.
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();
}
...
Top comments (0)