Monday 28 November 2011

Converting Bitmaps to Excel

Introduction
After having dinner I was browsing the internet and noticed someone making a joke about wasting time in an Excel course by using the grid to draw an image. As I'm interested in learning more about working with Excel automation I thought this would be an ideal learning exercise as working with Bitmaps is already simple.

Excel 2010 Addin
My initial approach was to write an addin driven by a button on the ribbon which would allow the user to select  a file and then convert that to an array and set the colour for each cell. I succeeded in this goal but came to the following conclusions

  1. I am missing something quite fundamental about COM Interobability with Excel or it really is needlessly irritating.
  2. It performed terribly so I'm clearly doing something wrong.
  3. There's no decent documentation anywhere.
  4. It restricts you to working with certain versions of Excel.
However, I had proved to myself that the logic was sound and as there wasn't anything on telly I decided to revert back to writing a command line application.

Command Line Application
I already know that you can generate an XML file that Excel will load and display having come across it when maintaining a COBOL program that built an XML file for Excel using only string concatenation. As I was well aware of the horror of this approach my first instinct was to find a library to do the work (why reinvent the wheel)?

I found this excellent and easy to use library (which was handy as I couldn't get the CHM help file to open) -CarlosAg Excel Xml Writer Library. This meant that I could concentrate on just getting a quick and dirty implementation up and running.

I knew I had the following constraints
  • Only 255 columns
  • Only 4000 unique colours
This meant that the process should be a simple
  1. Resize the image if necessary
  2. Identify every distinct colour
  3. Generate a style for each colour
  4. For each pixel in the image create a cell with the appropriate style.
Resizing the image is very easy with .Net


   1:          private void Resize(int width, int height)
   2:          {
   3:              Bitmap b = new Bitmap(width, height);
   4:              Graphics g = Graphics.FromImage((Image)b);
   5:              g.InterpolationMode = InterpolationMode.HighQualityBicubic;
   6:              g.DrawImage(image, 0, 0, width, height);
   7:              g.Dispose();
   8:          }


Identifying the unique colours was even simpler with LINQ as you only need the following command to get all distinct values.





   1:  s = k.Distinct().ToArray();

Generating the styles was just a case of iterating through the distinct array and adding each style to the workbook.

The Application
The application can be downloaded from here and is very simple to use.

From the commandline it's just

BitmapToExcel <filename>

This will create an xml file with the same name as your image which you may need to rename to .xls to get Excel to initially open it.