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();
}

React: Key learning touch points

React itself is a powerful framework including a number of key techniques:

• Higher Order Components and 
• Children to solve prop-drilling
• Portals - modal story
• React Suspense and lazy: https://hackernoon.com/magic-of-react-suspense-with-concurrent-react-and-react-lazy-api-e32dc5f30ed1

Redux:

• Saga's versus thunks... or both?
• Global versus local state... are you over-using redux?

Other learning:

• Remember that setState() can also take a function as a parameter:
this.setState((prevState, props) => ({ ...prevState, answer}));
• setState() is asynchronous, if you call it in the component you cannot guarantee that local state has been updated immediately

React: overkill with Redux and where Portals save the day

Recently I encountered a situation where I needed to overlay a div on top of another div. The standard way to do this is to use CSS and absolute or fixed positioning (https://stackoverflow.com/questions/2941189/how-to-overlay-one-div-over-another-div).

Sounds simple enough, however I need to do this from within a deep hierarchy of elements:

-> Detail Component
  -> Action Bar Component
  -> Items Component: Loop through and display expandable content divs
       -> On Click : overlay a component which is a div containing "expandable content"

The "items" component uses a standard bootstrap table to align the elements like so:

item 1	item 2
item 3	item 4

This layout means that when you select the onClick() for "item 1" and you render the expandable component which is essentially just a div, and it is positioned relatively the the content will appear in place of item one in the table.

item 1 (expandable content)	item 2
item 3	item 4

Cut a long story short, every which way I tried to style the popup unwanted styles would be inherited from the table and other components above it. I also found that I had to place it absolutely to get the effect I was looking for which created a raft of further problems. Finally, it was suggested that this might be a good use case for React Portals which did indeed save the day.

I also learned on this project how much Redux can be over utilized. "When you have a hammer, everything starts to look like a nail". This was said a lot, and it should be, because when you are deep into the detail of a project it can be hard to see that it has been over-engineered.