About Excel exporting using Winnovative Excel Library for .NET

In search for a good managed Excel exporting tool I’ve come to test a Excel library from Winnovative. I’ll follow the same procedure as in my previous Excel-related post about CarlosAG component for easier comparison..

What does it do ?

Create xls/xlsx documents, including containing formulas, images, comments, charts, etc. Operate with CSVs, load data from DataTables, etc. See the feature list yourself.

What do I need ?

Support for data types, unicode, basic formatting, formulas, cell merge, column width – the same things as in my previous excel-component test.

What did I get ?

Functionally - everything I wanted. Actually for not having licence I got one extra sheet reminding me that fact, but that is not going to be the issue for production environments.

The file was decent xlsx-file, I also briefly tested getting the older xls_2003-file and it seemed to be working as well. No warning on open.. just such file:

How did I do it ?

using System;
using System.Globalization;
using System.Threading;
using Winnovative.ExcelLib;

public class WinnovativeExcelTest
{
   private const int DummyRows = 15;

   public static void Build()
   {
      //prepare sheet
      ExcelWorkbook book = new ExcelWorkbook(ExcelWorkbookFormat.Xlsx_2007);
      ExcelWorksheet sheet = book.Worksheets[0];
      sheet.Name = "Sample";

      //create data      
      int currentRow = 1;
      sheet["A1:D1"].Merge(); //wow, we can use excel style range instead of coordinates.
      sheet["A1"].Text = "demonstrate merge";

      BuildHeaderRow(sheet, ++currentRow);
      for(int i = 1; i <= DummyRows; i++)
      {
         BuildDataRow(sheet, i, ++currentRow);
      }
      BuildSummaryRow(sheet, ++currentRow);

      //I don't like the default it offered so I'm setting it myself:
      sheet[currentRow - DummyRows, 4, currentRow +1, 4].Style.Number.NumberFormatString 
         = Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern;
      
      //corrigate column widths:
      sheet.AutofitColumns();

      //output result
      book.Save("Winnovative_result.xlsx");
   }

   private static void BuildHeaderRow(ExcelWorksheet sheet, int toRow)
   {
      sheet[toRow, 1].Text = "index";
      sheet[toRow, 2].Text = "1/index";
      sheet[toRow, 3].Text = "name";
      sheet[toRow, 4].Text = "datetime";
      sheet[toRow, 1, toRow, 4].Style.Font.Bold = true;
   }

   private static void BuildDataRow(ExcelWorksheet sheet, int dataIndex, int toRow)
   {
      //generate dummy data row using dataIndex for values:
      sheet[toRow, 1].Value = dataIndex;
      sheet[toRow, 2].Value = (1.0/dataIndex); //decimal is not recognized as number, by doc floats are.
      sheet[toRow, 3].Value = "Ilus õõvaöö nr " + dataIndex.ToString(CultureInfo.InvariantCulture);
      sheet[toRow, 4].Value = DateTime.Today.AddDays(dataIndex);
   }

   private static void BuildSummaryRow(ExcelWorksheet sheet, int toRow)
   {
      sheet[toRow, 1].Formula = String.Format("=AVERAGE(A{0}:A{1})", toRow-DummyRows, toRow-1);
      sheet[toRow, 2].Formula = String.Format("=SUM(B{0}:B{1})", toRow-DummyRows, toRow-1);
      sheet[toRow, 4].Formula = String.Format("=D{0}+1", toRow-1);
   }
}

Some subjective numbers:

• ~64 lines of code (including empty lines, usings, etc)
• runs approx 55-60 ms on E8300 @2.83GHz, 4GB RAM.

What do I think ?

I may be a bit biased toward them because I’ve used their PDF libraries before, but it DID feel good to code this API, clean and simple.

The good

• Fully managed code, .Net fw 2.0
• Single dll deployment (~3.5 MB)
• Seems to do everything I need + more
  • by documentation, did not try them all.
  • images, comments, hidden columns, DataTable to sheet, etc ..
• Good sleek API
  • you don’t get drowned in methods but you can do a lot with the ones given.
  • very friendly and flexible cell referencing tools
    • indexed (row 1, col 1) or “A1”-style
    • the same for cell ranges
  • dynamically tracking the cells to use:  no manual creation of 100 empty lines to add a single value to 100th row.
  • applying styles on the fly to cells/ranges. no manual messing with prepared styles unless you want to.
• Automatic cell value type detection
  • no need to format datetimes (unless you want to) or think about decimal separator.
• Good documentation
  • sample project in package.
  • xmldoc alongside dll
  • helpful user guide (I had two questions during this testing, I found both answers with less than a minute from the manual.
• Respectable company
  • For all the bugs I’ve seen in their products, I’ve seen also the release to fix it.
  • there is tech support.
• Fully functional evaluation possible
  • Can test everything before buying. The only addition is one extra sheet in file reminding that you cannot go live without a licence.
  • The same dll as the licenced version. Adding a licence 2 days before release will not give dll-problems.

The bad

• It’s not free
  • prices start from 350$. Too expensive to buy for personal use, but for enterprises this is good value for the money.
  • licence key should be stored within system.
• It’s not open source
  • no legal way to know or modify what the component does..
• .NEt requirements
  • it requires System.Web which means it cannot be used with .NET FW 4 client profile. This may be important in some scnearios.
  • it requires .NET 2.0, but i don’t think this is a problem. FW 1.1 applications should have died out a long time ago.

• Noticable dll size
  • 3.5MB is not too heavy and we have tons of RAM and HDD but smaller size would be even nicer.
• Could be slower than some alternatives
  • I didn’t do proper performance-testing but for this ultrasmall sample file it was ca 25% slower than CarlosAG.
• Decimals are not automatically recognized as number types.
  • floats can be used instead or kept as general type (default). Not sure if this is a excel limitation or Winnovative component optimization.