3 ratings
  • 1
  • 2
  • 3
  • 4
  • 5
5 star 3
4 star 0
3 star 0
2 star 0
1 star 0
Compatible with
  • iOS
  • Android

TMS FlexCel Studio for .NET 6.11.0

tmssoftware.com

Manipulate Excel files, create reports based on templates, and export Excel files to Pdf and HTML.

FlexCel Studio is a library to read and write xls and xlsx files, export them to html or pdf and print and preview them.

All code is written in C#, and full sources are included.

It is available for Xamarin.Mac, Xamarin.iOS, Xamarin.Android, Windows Phone, Windows Store (WinRT), Mono for Linux and Desktop .NET. A single license is valid for all supported platforms.

At its core, FlexCel has three main components:

1. An xls/x engine

This is the most lower level component in the pack. It contains an API to read and write xls or xlsx files, and the main object you use for it is the XlsFile object.

Example:

public void CreateFile()
{
   XlsFile xls = new XlsFile(1, true);
   xls.SetCellValue(1, 1, "FlexCel says Hello!");
   xls.Save("result.xlsx");
}

There is a tool available for Windows and OSX that you can use to find out how to use the xls/x engine. Just create the file you want to create in Excel, and open the file with:

APIMate will show you the needed code (in C# or VB.NET) to create the file. Full source for APIMate is included, so you can study how it works too.

2. A reporting engine

This is a higher level component for creating xls or xlsx files. When using it, you create a file in Excel which will be used as a template where the reporting engine will fill the values. Internally, this component uses the xls/x engine to create the files, but it allows you to do it in a more declarative way. It allows you to use Excel as the report designer.

Example:

In Excel, create a file and write:

|   |         A          |           B              |        C                          |
|---|--------------------|--------------------------|-----------------------------------|
| 1 | <#Customer.Name>   | <#if(<#Customer.Vip>;VIP;)> | <#Customer.Age>                   |
| 2 |                    |                          |                                   |
| 3 |                    |                          | ="Average Age: " & Average(C1:C2) |

Add a named range (in the ribbon-> Formula tab->Name Manager). Name it "__Customer__", and make it go from A1 to C1. This name must have 2 underscores at the beginning and end, and the text between the underscores must be the same as the text between <#.... .> tags. It tells FlexCel which rows are used for each record in the database.

Save the file as "template.xlsx"

Then write the following code:

class Customer
{  
   public string Name { get; set; }
   public bool Vip { get; set; }
   public int Age {get; set; }
}

public void CreateFile()
{
   List<Customer> Customers = GetCustomers();

   using (FlexCelReport fr = new FlexCelReport(true))
   {
      fr.AddTable("Customer", Customers);
      fr.Run("template.xlsx", "result.xlsx");
   }
}

This is a basic report, but you can do a lot more, like multiple level master-detail reports, cross ref reports, etc. Take a look at the Windows examples for a list of possible reports.

3. A rendering engine

This component is used to convert any xls or xlsx file to pdf, images, html or to print them. Internally it also uses the xls/x engine, as the reporting engine does.

Example:

To convert a file to pdf:

    XlsFile xls = new XlsFile(System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "result.xlsx"));

    using (var pdf = new FlexCelPdfExport(xls, true))
    {
        pdf.Export(System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "result.pdf"));
    }

To convert a file to html:

    XlsFile xls = new XlsFile(System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "result.xlsx"));

    using (var html = new FlexCelHtmlExport(xls, true))
    {
        html.Export(System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "result.html"), null);
    }

Release Notes

New on v 6.11.0.0

  • Support for Excel tables in xlsx files. There is partial support for tables in the FlexCel API.

    • Tables are preserved when editing xlsx files. Note that we refer to the tables introduced in Excel 2007: Other tables like "what-if" tables were already preserved.
    • Tables will be copied and modified when you insert or copy ranges.
    • API for reading tables
    • Preview API for writing tables. Note that this API is not complete yet and might fail in some cases. APIMate will show you how to add a table with the API.
    • There is no rendering yet (exporting tables to pdf, etc), and no calculation of table references like =SUM(@Table1[column2]
  • New properties FullRecalcOnLoad and FullRecalcOnLoadMode in [[vcl T]]XlsFile. FullRecalcOnLoad will tell you if the xlsx file opened with FlexCel had the property "Full Recalc on Load" true. When it is true, normally the file doesn't have the values of the calculated formulas and you need to do a manual XlsFile.Recalc() to get the values. FullRecalcOnLoadMode allows you to tell FlexCel how it should mark the files it creates. In the default mode it will mark them as not needing full calculation id they were calculated on save by FlexCel (the default) and mark them as needing recalc on open in other case. Note that those 2 properties only apply to xlsx files: xls files don't have this property and the value returned by those properties will always be false.

  • Some repeated function results are now calculated only once for better recalculation speed. Now FlexCel can detect repeated subexpressions inside a formula and calculate them only once. So for example if you have a thousand formulas like =If(A1,2,3... = Sum($B$1:$E$1000),1,Sum($B$1:$E$1000)) then the Sum($B$1:$E$1000) will be calculated only once for all the formulas. This can have significant speed improvements if you have formulas with this pattern.

  • Performance improvements in function calculations. Some of the most used functions like SUM, COUNT, AVERAGE, SUMIF, COUNTIF, etc have been optimized to work at a higher speed.

  • Performance improvements in formula parsing. Now the formula parser is a little faster and that can lead to faster loading of xlsx files with thousands of formulas.

  • Performance improvements loading xlsx files with thousands of comments. Now xlsx files with thousands of comments should load much faster.

  • Improved rendering of numbers which don't fit inside a cell. When a number doesn't fit inside a cell, Excel shows #### instead. But it will always show at least one #, if it can't fit a complete # into the cell, then it will display it empty. FlexCel was showing part of a # sign when a full # sign wouldn't fit, not it behaves as Excel and shows the cell empty.

  • Bug Fix. In form objects like checkboxes or listboxes saved by Excel 2007 in xlsx files, the resulting coordinates could be wrong if the value of the anchor took more than one cell. FlexCel would move the anchor to the next cell, but Excel just ignores the extra width or height. Note that this only applies to xlsx files saved by Excel 2007, xls files or xlsx files saved by Excel 2010 or later would be correctly read by FlexCel.

  • Bug Fix. When deleting sheets with locally stored defined names and you had multiple references to those names in a single formula, FlexCel could fail to update the names.

  • Bug Fix. When setting a column format for many columns at the same time and reset cells true, some cells might not be reset.

  • Bug Fix. Now FlexCel won't let you enter formulas with unions ranges of numeric or string values. Before it would allow you to enter a formula like "=1, 2" and it would be interpreted as the union of the reference "1" and "2". Excel would read it if saved as xls, but would fail to parse the formula when saved as xlsx. So now we don't allow those formulas anymore.

  • Bug Fix. While it is invalid to write a file with conditional formats or data validations with formulas that refer to other sheets, Excel can load them (but you won't be able to modify them). Now FlexCel can read those too without reporting an error.

  • Bug Fix. FlexCel could raise an exception when deleting ranges with conditional formats.

Write a Review

1 review

Hao Li rates this with
  • 1
  • 2
  • 3
  • 4
  • 5

Perfect, easy to use. A good tool to create code - even no need to read document, it's enough to just check the code

Posted on: April 30, 2014 / Version: 6.1.0.0