Introduction
When working with Excel frequently while coding in C#, the smart direction is to pay for a quality library which supports all Excel formats even if not needed as there may be a time a format is needed.
While Excel operations are infrequent the choices are code yourself without a library which means if Microsoft makes breaking changes the developer must figure out how to fix code to handle the breaking changes.
In this article learn how to read sheets, write to sheets, update cells. For the code samples ExcelMapper and SpreadSheetLight NuGet packages are used where ExcelMapper does the core work.
Data operations are performed by Microsoft Entity Framework Core 8.
Important
All data read from Excel files in the code sample are tubular for 99% of the examples presented and the data is perfect, meaning that for instance if a column contains dates, all rows of that column have valid dates.
In the real world there will be imperfect data which means that a developer should always consider at least one cell has bad or invalid data. With that known there is no one method to checks for proper data. For each Excel file a developer must understand assertion must be performed such as, for example for a sheet containing customer data can first and last name be empty or that for a sheet of products, what to do if data is missing.
Example of checking if the fourth column contains valid dates. This is a hard coded sample which is provided in source code.
using SpreadsheetLight;
namespace ExcelMapperApp1.Classes;
internal class LightOperations
{
/// <summary>
/// For article to show an example to test if the person's birthdate can be read as a date
/// </summary>
/// <returns>
/// If there are issues, the list of rows with issues is returned
/// </returns>
public static (List<int> rows, bool hasIssues) Iterate()
{
List<int> list = [];
const string excelFile = "Nested1.xlsx";
const int columnIndex = 4;
using SLDocument document = new(excelFile);
var stats = document.GetWorksheetStatistics();
// skip header row
for (int rowIndex = 2; rowIndex < stats.EndRowIndex + 1; rowIndex++)
{
var date = document.GetCellValueAsDateTime(rowIndex, columnIndex);
if (date == new DateTime(1900,1,1))
{
list.Add(rowIndex);
}
}
return (list, list.Any());
}
}
Other considerations, does the file exists? Can the file be opened? Does the worksheet exists?
Always check if the file exists. Concerning if a file can be opened, if a user has the file open outside of the program and a developerβs code attempting to read the file, an exception is throw so wrap the code in a try/catch. Regarding sheet names, if they happen to changed, code will fail. Code has been provided to show how to check for sheet existence.
Read sheet data to a SQL-Server table
First check if the worksheet exists, if not return to caller.
Next, reset the SQL-Server database table to zero rows and reset identity.
Read worksheet data using ExcelMapper using the following model.
public partial class Customers
{
public int Id { get; set; }
public string Company { get; set; }
public string ContactType { get; set; }
public string ContactName { get; set; }
public string Country { get; set; }
public DateOnly JoinDate { get; set; }
public override string ToString() => Company;
}
To match this sheet.
Next, take the list returned and add to EF Core database. If an exception is thrown it is written to the console screen while for a real application the exception should be written to a log file using a package like SeriLog. See Serilog logging and EF Core logging.
public static async Task CustomersToDatabase()
{
PrintCyan();
const string excelFile = "Customers.xlsx";
if (SheetExists(excelFile, nameof(Customers)) == false)
{
AnsiConsole.MarkupLine($"[red]Sheet {nameof(Customers)} not found in {excelFile}[/]");
return;
}
try
{
DapperOperations operations = new();
operations.Reset();
ExcelMapper excel = new();
await using var context = new Context();
var customers = (await excel.FetchAsync<Customers>(excelFile, nameof(Customers))).ToList();
context.Customers.AddRange(customers);
var affected = await context.SaveChangesAsync();
AnsiConsole.MarkupLine(affected > 0 ? $"[cyan]Saved[/] [b]{affected}[/] [cyan]records[/]" : "[red]Failed[/]");
}
catch (Exception ex)
{
ex.ColorWithCyanFuchsia();
}
}
Read/write to secondary Excel file
In this example the idea is say business asked for a report and only a few columns are required.
Data is read using the following model.
public class Products
{
public int ProductID { get; set; }
public string ProductName { get; set; }
public string CategoryName { get; set; }
public int? SupplierID { get; set; }
public int? CategoryID { get; set; }
public string Supplier { get; set; }
public string QuantityPerUnit { get; set; }
public decimal? UnitPrice { get; set; }
public short? UnitsInStock { get; set; }
public short? UnitsOnOrder { get; set; }
public short? ReorderLevel { get; set; }
public override string ToString() => ProductName;
}
Then written to another Excel file using the following model.
public class ProductItem
{
public int ProductID { get; set; }
public string ProductName { get; set; }
public string CategoryName { get; set; }
public decimal? UnitPrice { get; set; }
}
Code first checks if the Excel file exists (as mentioned above), delete the file for a fresh start.
Next, read the worksheet into a list then create a second list with less properties than the first list.
Save the smaller list to a new file.
/// <summary>
/// Read products from Products.xlsx as list of <see cref="Products"/> then write to a new
/// file as <see cref="ProductItem"/> ProductsCopy.xlsx
/// </summary>
/// <returns></returns>
public static async Task ReadProductsCreateCopyWithLessProperties()
{
PrintCyan();
const string excelReadFile = "Products.xlsx";
const string excelWriteFile = "ProductsCopy.xlsx";
if (File.Exists(excelWriteFile))
{
try
{
File.Delete(excelWriteFile);
}
catch (Exception ex)
{
ex.ColorWithCyanFuchsia();
return;
}
}
ExcelMapper excel = new();
var products = (await excel.FetchAsync<Products>(excelReadFile,
nameof(Products))).ToList();
var productItems = products.Select(p => new ProductItem
{
ProductID = p.ProductID,
ProductName = p.ProductName,
CategoryName = p.CategoryName,
UnitPrice = p.UnitPrice
}).ToList();
await new ExcelMapper().SaveAsync("productsCopy.xlsx", productItems, "Products");
}
Read sheet - update properties and save
This example demonstrates reading a worksheet, making edits and removal of a row. Other than the fact edits are made, this code sample follows the same path as the example above.
/// <summary>
/// Read products from Products.xlsx as list of <see cref="Products"/> then update
/// several products and save to a new file ProductsOut.xlsx
/// </summary>
public static async Task ReadProductsAndUpdate()
{
PrintCyan();
const string excelReadFile = "Products.xlsx";
const string excelWriteFile = "ProductsOut.xlsx";
if (File.Exists(excelWriteFile))
{
try
{
File.Delete(excelWriteFile);
}
catch (Exception ex)
{
ex.ColorWithCyanFuchsia();
return;
}
}
ExcelMapper excel = new();
var products = excel.Fetch<Products>(excelReadFile, nameof(Products)).OrderBy(x => x.ProductName).ToList();
var p1 = products.FirstOrDefault(x => x.ProductName == "CΓ\u00b4te de Blaye");
if (p1 is not null)
{
p1.ProductName = "Cafe de Blave";
}
var p2 = products.FirstOrDefault(x => x.Supplier == "Aux joyeux ecclΓ\u00a9siastiques");
if (p2 is not null)
{
p2.Supplier = "Aux Joy";
}
var p3 = products.FirstOrDefault(x => x.ProductID == 48);
if (p3 is not null)
{
products.Remove(p3);
}
await excel.SaveAsync(excelWriteFile, products, "Products");
}
Read nested properties
This code sample has person details and their address on the same row and the task is to separate both.
Models
public class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateOnly BirthDate { get; set; }
public Address Address { get; set; }
public override string ToString() => $"{FirstName} {LastName} {Address}";
}
public class Address
{
public string Street { get; set; }
public string City { get; set; }
public string State { get; set; }
public string ZipCode { get; set; }
public override string ToString() => State;
}
ExcelMapper seamlessly figures out what to do with the models above.
public static async Task NestedReadPeople()
{
PrintCyan();
const string excelFile = "Nested.xlsx";
ExcelMapper excel = new();
var contactList = (await excel.FetchAsync<Person>(excelFile, "Contacts")).ToList();
AnsiConsole.MarkupLine(ObjectDumper.Dump(contactList)
.Replace("{Person}", "[cyan]{Person}[/]")
.Replace("Address:", "[cyan]Address:[/]"));
}
Below is the output using Nuget package ObjectDumper.NET which is great for examining results.
Before running provided code
See the following.
Summary
Code has been provided to read tubular data working with several NuGet packages were ExcelMapper performs most of the work along with Microsoft Entity Framework Core for database operations.
See ExcelMapper GitHub page for more e.g.
- Map to Excel files using header rows (column names) or column indexes (no header row)
- Optionally skip blank lines when reading
- Preserve formatting when saving back files
- Map formulas or formula results depending on property type
- Use records
- Provide custom object factories
Top comments (1)
Very good
Thanks π