Excel files with .NET Core

I found myself needing to quickly manipulate some data in an excel sheet.

I had no access to the full blown Visual Studio, no problems I thought, let's see what VSCode, the command line and .NetCore 2 can do.

New project: no problem

Snideness aside it really is easy - just think of a project folder name...

• dotnet new console 
• dotnet restore
• dotnet run

C# 7 has arrived... you no longer need to write this kind of madness:

static Main(string[] args) {
  thingIamDoing.RunAsync().GetAwaiter().GetResult(); 
}

instead:

public static async Task Main(string[] args)
{
   await thingIamDoing();
}

pleasing... but a caveat - you must specify LangVersion  in the csproj as latest to use this:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.0</TargetFramework>
    <LangVersion>latest</LangVersion>
  </PropertyGroup>
</Project>

nuget is yesterdays news... stop using it along with package.config.

instead:

dotnet add package EPPlus.Core

also pleasing... especially as it automatically adds the reference to the csproj:

<ItemGroup>
   <PackageReference Include="EPPlus.Core" Version="1.5.4" />
</ItemGroup>

</Project>

so long package.config

So now I have a library to help me read an xlsx file we can experiment. Can this library tell me how many columns and rows there are?

using System.IO;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml;
...

var sFileName = @"test_file.xlsx";
FileInfo file = new FileInfo(Path.Combine("C:\\", sFileName));
var sb = new StringBuilder();

try
{
  using (ExcelPackage package = new ExcelPackage(file))
  {
    ExcelWorksheet worksheet = package.Workbook.Worksheets[4];
    var rowCount = worksheet.Dimension.Rows;
    var colCount = worksheet.Dimension.Columns;
    sb.Append($"{colCount} rows: {rowCount}");
  }
} catch (Exception ex) {
  sb.Append($"An error occurred while importing. {ex.Message}");
}

Console.WriteLine(sb.ToString());

This gives me a result of 13 and 55 rows, which is what I expected.
Interesting to note:

• The API is not zero based, in the example test_file, there really are 4 worksheets
• If the excel spreadsheet is open, expect a file IO/lock error

Now I know the number of columns and rows we can simply iterate through the spreadsheet to read the data. e.g.

var headerPrefix = "headerPrefix_";

if (hasHeader) {
  headerPrefix = worksheet.Cells[1, col].Value.ToString();
}

for (int row = hasHeader ? 2 : 1; row <= rowCount; row++)
{
  if (worksheet.Cells[row, col].Value == null) continue;
  var newValue = worksheet.Cells[row, col].Value.ToString();
  
  

To export:

string fileName = @"results.xlsx";
var exportfile = new FileInfo(Path.Combine("C:\\Code\\", fileName));

if (exportfile.Exists)
{
  exportfile.Delete();
  exportfile = new FileInfo(Path.Combine("C:\\Code\\", fileName));
}

using (ExcelPackage package = new ExcelPackage(exportfile))
{
  var worksheet = package.Workbook.Worksheets.Add("results");
  worksheet.Cells[1, 1].Value = "Column 1";

  // continue on to fill out row data things here ....
  // once complete - save
  package.Save();
}