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

Bookmarks: 

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:



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:



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"



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:



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:

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:



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:



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


Bookmarks: 

This blog post has not received any comments yet. Add a comment.



An Excel report generated by TMS FlexCel for TMS FlexCel at Xamarin

Bookmarks: 

Tuesday, July 09, 2013

With Xamarin targettng iOS, Android, Windows Phone mobile devices, they offer an online tool to perform an analysis for .NET assembly compatibiliy & readiness with the Xamarin tools. Having an interest to make our TMS Flexcel for .NET product ready for Xamarin, we did this analysis and one of the options is to save the analysis report as Excel file.
Guess what, behind the online analysis tool from Xamarin is our TMS Flexcel for .NET product that generates this report on the fly. So, when we got the report for the Xamarin analuysis of the TMS Flexcel assembly, the report was actually generated by TMS Flexcel itself. A nice coincidences we'd like to share.



Bruno Fierens


Bookmarks: 

This blog post has not received any comments yet. Add a comment.



Differencing Excel files with style

Bookmarks: 

Friday, May 20, 2011

If there is one thing I do a lot is playing "find the differences" with xls and xlsx files.

I do it when reverse engineering a feature: If I want to find some undocumented byte that changes some feature, I save the file in Excel with and without the feature, open both files and find what changed.

I do it when testing: We have a really huge set of "reference files" that our test suite must create in order to verify FlexCel is working correctly. If some code change makes a file different from the reference file, I need to find the differences to know if we introduced a bug, or if it is ok and the reference file must be updated.

And I do it as an Excel user, when I need to find what really changed between two data files.

Differencing the raw files

For reverse engineering and testing, I just look at the raw data in a diff program (I currently use the excellent Beyond Compare). Many years of doing so almost every day give you some Matrix-like powers where you just look at a bunch of Hexadecimal codes and you can see how that means that a formula changed. Let's see it with a real world example of a file I had to deal a week ago:


As you can easily see from the image, somehow the formula that was at row 1 is now at row 2, and the formula that was at row 2 moved to row 3. This is clearly a 1-off error, so we need to look at the code and fix it, we must have forgotten some "-1" somewhere.

But while this works fine for a controlled environment, it breaks when you want to compare arbitrary files. An xls file has thousands of "reserved" bytes that will change whenever you change the Excel version, and records might change a lot, without the file actually changing. What is even worse, all strings in an xls or xlsx file are stored inside an unsorted string table, and referenced by number. So one file might have the string number 1 at cell A1 and string number 2 at cell A2, while the other has the string number 2 at A1 and 1 at A2, and both files would be exactly the same because the shared string table is ordered differently. This is true also for xlsx files. Unless changes are trivial and controlled, diffing raw files can only go so far.

Time for a second example. I took the file I had just used in the images above, opened it in Office 2010 and saved it. This is now how the diff looks like:


As you can see at the left column, there is too much "red stuff" for this to be useful.

Differencing using ApiMate

So, for general differencing we need a better solution. Normally, all diff tools come with some way to diff xls files, but in both WinMerge and Beyond Compare they just convert the files to csv and diff that. All styling is removed. And if you look at the title of this post, you will see this is unacceptable, we want to know changes in styles too. Maybe the data is the same but now the title is 24 points instead of 22? We want to know.

What we really need is some kind of tool that could "convert" an xls or xlsx file to some kind of text representation, so we can diff that text later. Creating a tool like that was in my perpetual todo list of things-that-I-should-investigate-someday-but-never-will-because-there-are-more-urgent-things-to-do-right-now. Of course, until the day I woke up and realized that I already had such a tool and it is already distributed with FlexCel .NET.

It is called APIMate, and it will convert an xls or xlsx file to C#, Delphi or VB.NET code. It was designed as a learning tool to answer all those "how to" questions ("How to change the color of a cell?", "How to insert an image?"), and it does a great job at that, to the point that I use it myself all the time instead of looking at the docs. But there is nothing forbidding using that text representation in a diff tool. I just needed to add some changes so it can be called from the command line and so it will diff all the sheets instead of just the active sheet.

Setting it up is easy: You need the APIMate shipped with FlexCel 5.5 as older versions won't run from the command line. Then you need to go to your diff application and setup a filter to import xls and xlsx files. While this is different in all diff applications, most of them have the option. In particular, in Beyond Compare you would go to "Tools->File Formats", select "xls" and write ApiMate.exe as the filter app. When used as a console application, you need to pass two parameters to APIMate.exe: the source xls/x file and the target text file that will be generated.


Once you have done that, you can right click any two Excel files in the Windows explorer and diff them. Going back to our original example, this is how it would look now:


While APIMate won't tell you every difference in the file (for example it won't show charts yet) it will do a much better job than the bundled diff filters. Myself, I still do mostly raw diffs when developing since I need to know about every bit that changed, but for general use "ApiMate diff" has proven to be surprisingly useful. As a final example, let's look at the following files:


What changed? This is what APIMate will show: (note that we are showing only changed lines)


As you can see the range C6:C7 has been merged. A cell changed the formatting (you can't see that in the screenshot because of the filtered lines, but if we saw the full diff it would be simple to see it was cell C4). Also the image was moved and the checkbox changed from checked to unchecked. Oh, and the selected cell changed too.

One last thing.

We have made it so you don't need to be a registered FlexCel user to use this. If you just want to diff Excel files and don't need FlexCel, you can install the trial, and keep APIMate.exe and FlexCel.dll in some folder. It will work fine and won't expire.

Adrian Gallero


Bookmarks: 

This blog post has received 3 comments. Add a comment.



FlexCel.NET, IPhone, MonoTouch and some magic dust

Bookmarks: 

Monday, November 01, 2010

For some time now I have been wanting to evaluate how FlexCel .NET behaves in other platforms. Not just plain Mono running in a Linux server (that's boring, we have always supported that), but some more fun possibilities.

That's why I decided to try two different technologies this weekend: MonoTouch and CrossTalk. (I will speak about Cross Talk in a separate post).

As you might know, we have been really busy here at FlexCel development, both in VCL (porting all features from FlexCel .NET back to FlexCel VCL) and in .NET (adding the missing xlsx support bits). So I had not too much time to play before going back to "real" coding. I allocated 5 hours in the weekend to test each one; if I couldn't have something working when time was due, I would give up.

And if I have to be sincere I was completely expecting to give up. Even when most of FlexCel code is non visual, it is over 100,000 lines of complex non visual code, and I didn't imagine I was going to have something compiling, let's not say working, in 5 hours. You are a smart reader, and you know I actually got something to show or I wouldn't be writing this post, but I didn't know that while I was waiting for MonoTouch to download. That's why I want to emphasize how impressed I was.

So I installed MonoTouch, launched MonoDevelop, and added all the files from the "Core" FlexCel namespace to a new project. I pressed the little "compile all" button; the project compiled and reported "0 errors". What? 0 Errors?? Something had to be wrong, and indeed it was. No file had been generated. Tried to compile a couple of times, and always the same result. Finally when trying to change some options to make it compile, MonoDevelop crashed and dissapeared.

Not what I would call a good start, and it set my expectations even lower. I wasn't going to be able to do anything nice in my 4.5 hours left. But well, I still had time, so I tried it again from zero; created a new project, added just one file and compiled it. And finally I got some errors! Encouraged by that small victory, I tried again to put the full "Core" namespace in the project, and this time it compiled and gave a gazillon of errors. Looking at them, most were about some missing classes, repeated once and again. No "System.Drawing.Font". No "System.Drawing.Image" And again. And again. But I did find some similar classes "MonoTouch.UIKit.UIFont" and "MonoTouch.UIKit.UIImage", that looked like good replacements.

Next step was then to start writing at the beginning of each file:
#if(MONOTOUCH)
using Font = MonoTouch.UIKit.Font;
using Image = MonoTouch.UIKit.Image;
#endif
(Semi-Related-Note: You can't image how I miss the Delphi type aliases in C#... in Delphi this would have been a single
type Font = MonoTouch.UIKit.Font;
in a single file, not in every file)

Bit well, after that things started to look good. Now my issue was with different methods, for example "System.Drawing.Font" has a "FromArgb(a,r,g,b)" method, while "MonoTouch.UIKit.Font" has a "FromRGBA(r,g,b,a)". To fix that I created some extension methods that extended the MonoTouch classes to have similar interface to what FlexCel expected. I had no such luck with properties, since C# doesn't allow extension properties. So I had to change things like Color.R to Color.R() in code, and create an extension method "R()" that will return the property "R" in normal framework and the equivalent in MonoTouch.

Finally after one hour, the "Core" namespace was compiling. At this time I had forgotten about my first bad impression, and was very excited to see everything compiling. I didn't really believe it would actually work, but just seeing it compiling was great. After that it took me other 30 minutes to compile "XlsAdapter" namespace (the actual xls engine). I had to remove xlsx support because some missing classes in the framework, and I didn't even try to compile the rendering engine (as it has more visual stuff, and I expect it to be a little harder)

Now two hours had gone, and I already had something that compiled and looked like I could actually use. So now I went to make a small test application. I have zero experience in mac development, but I could get a form with some labels and buttons relatively fast. And now comes what really impressed me. Everything worked! At first try! I had a working calculator in 3 hours.


I was so happy that I created a little screencast to show how it to some friends. My idea was to create a more "professional" one for this post, (where I would even try to correctly pronounce "button"), but I feel the original one is more authentic and probably funnier to watch. Of course the screencast has no script, I just pressed "record" and started babbling things in an impossible to understand pseudo English. Not that I am any better doing a speech in Spanish, but that's off topic.

You can get the screencast here:

http://www.tmssoftware.net/public/flexmonotouch.mov

After I recorded that I tested some other things, and everything went incredible smooth. For example creating a 60,000 rows x 20 cols (8.5mb) spreadsheet from the iPhone simulator just took seconds, in fact Excel was slower opening the file than the iPhone simulator creating it. Wow. Mono has always been outstanding technology, but what they did with MonoTouch is just incredible.

So there you have it. From void to a fully working app in 3 hours. Just so we can compare, I am still trying to port FlexCel to Windows phone 7. So much stuff missing and changed in wp7 that I would need many other blog posts to enumerate them all. And in the meantime, MonoTouch just worked, in a platform that is designed around compiled obj-c, not .NET, not even a JITter allowed. Some serious magic is going on here.

Now, I am not really sure on what we are going to do with this. Is it really interesting to have support for reading and writing Excel files from a phone? Would you use it? Do we leave this as a "cool experiment" or do we keep working on it? Please let us know in the comments, or email me directly at adrian@tmssoftware.com

Adrian Gallero


Bookmarks: 

This blog post has received 5 comments. Add a comment.




Previous  |  Next  |  Index