Blog

All Blog Posts | Next Post | Previous Post

Creating Excel files in Windows, OSX and Linux with .NET Core

Wednesday, February 10, 2016

Introduction

In FlexCel .NET 6.7.9, we introduced preview support for .NET Core

This is a preview FlexCel release since it is compiled against a preview version of .NET Core and shouldn't be used in production, but it can be fun to explore.

This FlexCel version is also limited, because .NET core doesn't have currently any graphics support ( see https://github.com/dotnet/corefx/issues/2020 ) and so we can't do anything that needs graphics manipulations or measuring fonts. This means no rendering of Excel files, no exporting to PDF or HTML, no autofitting columns or rows. At the moment we are releasing support for almost the full API for reading and writing xls and xlsx files and also the reporting engine. The only limitation I can think of in the API is the Autofit methods to autofit rows and columns, because those need a graphics library to measure the fonts used in the cells. But the rest, including the full recalculation engine is there and working.

Getting Started

The first thing to do is of course to install .NET Core in Windows, OSX or Linux.
Once you have installed it, the next step would be to create a simple console application and see if it works. This is done by typing the following commands:

dotnet new
dotnet restore
dotenet run

It should look something like this:

TMS Software Delphi Components

Note:At the time of this writing, if you run those steps in OSX, you will see an error as explained here. Probably by the time you read this it is already solved, but if you get an error, as a temporary workaround you should try restoring the 10.10 (Yosemite) framework:

dotnet new
dotnet restore --runtime osx.10.10-x64
dotenet run

And the result should look like this:

TMS Software Delphi Components

Installing FlexCel

Ok, the first step is done. If we had any luck, we got a working .NET app, and we didn't even write a single line of code. So now, what about using FlexCel to create an xlsx file?

.NET core is all about NuGet, and even the .NET framework itself will be distributed via NuGet. So you need to download the FlexCel NuGet package from either the tms registered users page if you have a valid FlexCel license, or a trial from the product page.

Once we have the FlexCel NuGet package, we need to put it in a NuGet server so it can be used.
You can find information in how to setup a private nuget server for your own packages here: http://docs.nuget.org/docs/creating-packages/hosting-your-own-nuget-feeds

For this example, we are going to use the simplest way: Sharing a Windows folder. So we will put the package in r: mscomponents, and share that folder as "tmscomponents"

TMS Software Delphi Components

And that is it. Once you have shared the folder, you should see it in the Windows Explorer and in OSX Finder.

Configuring the project to use FlexCel

For the next steps, we are going to be using Visual Studio Code, but it can be done just as easy with any text editor. We will also be working in OSX, even when it is exactly the same for Linux or Windows. It just doesn't make sense to repeat the same information three times.

So we go to FIle->Open, and open the folder we created in the "Getting Started" section. In the screenshots, it was ~/dotnetcore.
Then we will edit our NuGet.Config file, and add the "tmscomponents" feed we created in the "Installing FlexCel" section to host the FlexCel NuGet package.

We need to add this line:

<add key="api.tmssoftware.com" value="/Volumes/tmscomponents" />

to the "packageSources" section of our project.json:

TMS Software Delphi Components

Note: This step is a little different depending in the operating system you are using. In Windows, you would write "\tmscomponents" instead of "/Volumes/tmscomponents" and in Linux you would write the path to the folder where you mounted the windows shared folder. Also note that in OSX and Linux, you might have to mount the folder before using it. In OSX this means opening the folder with the Finder, and in Linux it might change depending in the distribution you are using: either doing a mount command or opening it with the file explorer.

Adding a reference to FlexCel

Now, we need to add a reference to the nuget package in our project. Note that different from normal .NET where we add references to assemblies, we now add references to nupkg files which contain the assembly inside.

We will be adding the line:

"flexcel-dnx": "6.7.9-rc1"

to the "dependencies" section of the file project.json in our working folder: TMS Software Delphi Components

Then we go back to the terminal, and type "dotnet restore" in our working folder. As we are in OSX and we have the bug we mentioned before, we will actually do "dotnet restore --runtime osx.10.10-x64". As mentioned, very shortly after this blog is published this won't be necessary.

The result should look something like this:

TMS Software Delphi Components

Note: For this reference, we use 6.7.9-rc1 which is the FlexCel version at the time I am writing this. .NET core uses semantic versioning for the names of the packages, and so the "-rc1" part of this release means it is not a stable release yet.

Writing the app

All the pieces of the puzzle are now in place: The only thing remaining now is to write the actual application. We will create an xlsx file with an image in my hard disk, some text and a formula. To do so, open the file Program.cs and replace the code in this file with the following:

using System;
using FlexCel.XlsAdapter;
using FlexCel.Core;
using System.IO;

namespace ConsoleApplication
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var xls = new XlsFile(1, TExcelFileFormat.v2016, true);
            xls.SetCellValue(10, 1, "What");
            xls.SetCellValue(10, 2, "a");
            xls.SetCellValue(10, 3, "wonderful");
            xls.SetCellValue(10, 4, "world");
            xls.SetCellValue(11, 1, new TFormula("=A10 &  " " & B10 & " " & C10 & " " & D10"));
            xls.AddImage(File.ReadAllBytes("/Users/adrian/Documents/cube2b256.png"), 
            TXlsImgType.Png, 
            new TImageProperties(new TClientAnchor(TFlxAnchorType.MoveAndResize, 2, 0, 2, 0, 9, 0, 4, 0), ""));
            
            xls.Save("test-flexcel.xlsx");
            Console.WriteLine("File has been written to folder: " + Directory.GetCurrentDirectory());
        }
    }
}

For this particular example, I used an image I have at /Users/adrian/Documents/cube2b256.png. If you are running the code of this tutorial, make sure to write the path to a png you have on your machine.

After writing the code, save it, go back to the terminal and type:

dotnet run

If there are no errors, then you can type

open test-flexcel.xlsx

And if you have Excel installed in the mac, you should be able to see the file we just created:

TMS Software Delphi Components

Appendix: (Trying to) Convert the app to a native C++ app

So we have managed to create an Excel file with FlexCel and .NET Core in some short simple steps. And we did it in OSX, and we could have done it in Linux too. This is all interesting, but it is not like we couldn't have done it before with Mono and/or Xamarin

One of the things that I was really wanting to evaluate when we adapted FlexCel code to run in .NET Core was two mysterious lines which appear if you write

dotnet compile --help

Usage: dotnet compile [arguments] [options]

Arguments:
    The project to compile, defaults to the current directory. Can be a path to a project.json or a project directory

Options:
  -h|--help                           Show help information
  -o|--output             Directory in which to place outputs
  -t|--temp-output        Directory in which to place temporary outputs
  -f|--framework           Compile a specific framework
  -c|--configuration   Configuration under which to build
  --no-host                           Set this to skip publishing a runtime host when building for CoreCLR
  -n|--native                         Compiles source to native machine code.
  -a|--arch                     The architecture for which to compile. x64 only currently supported.
  --ilcargs                     Command line arguments to be passed directly to ILCompiler.
  --ilcpath                     Path to the folder containing custom built ILCompiler.
  --ilcsdkpath                  Path to the folder containing ILCompiler application dependencies.
  --appdepsdkpath               Path to the folder containing ILCompiler application dependencies.
  --cpp                               Flag to do native compilation with C++ code generator.
  --cppcompilerflags           Additional flags to be passed to the native compiler.

Now, before continuing: the word "native" has been so abused that it has lost any meaning. Everything is native and nothing is. You can have native code which is using Reflection/RTTI all over the place and behaving like interpreted code. You can have interpreted code that is JITed AOT and runs like compiled code. And what about memory management? Are we using a Garbage Collector, manual allocation/deallocation of maybe reference counting?

But well, from what I understand this --native switch seems to be a port of .NET Native but running in desktop Win64 apps (and OSX and Linux). This does seem indeed very interesting.

The --cpp switch seems even more interesting. It converts your C# code to C++ code, and uses Visual C++ to compile it. The generated cpp file is stored inside the obj folder so you can see it, even if you can't do much with it as it is not "readable C++" but more like "IL written in C++" which only a C++ compiler can understand.

So my idea was to run the tests in "normal", "native" and "cpp" modes to see how they behave. We have thousands of tests here which we can use to measure the performance, and while not designed as benchmarks, those tests do a lot of I/O (reading and writing thousands of Excel files), they do a lot of calculations (when testing the calculation engine) and they allocate and free a lot of memory (when testing huge files). In addition, they are heavily multithreaded. So is it any different? 1.5x, 2x, or maybe 10x faster? Or maybe slower?

And the answer is.... we don't have an answer yet. I couldn't get either the --native or the --cpp switches to work with FlexCel, and I didn't try much since I understand this is a prerelease version of .NET Core. What I could get was a simple console app with a single Console.WriteLine sentence to work in both "Native" and "CPP". But not anything complex enough as to benchmark it.

Ok, this is kind of a downer to finish this post. After all the nice stuff already possible with .NET core, it doesn't seem fair to close the post with something that doesn't really work yet (or that I wasn't able to make it work). Maybe I should delete this whole section before pressing "publish"? . I actually thought about it, maybe write something when I can actually make it work, but at the end I decided to keep it. The truth is, this whole native stuff is very interesting and I am really looking forward to see it come to life. I think it is well worth mentioning even if it isn't ready yet.

Adrian Gallero

This blog post has not received any comments yet.