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.
-
