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

  • Support for Windows 10 Universal Apps. The dll FlexCelProtable 81 can now be used from Windows 10 Universal apps.

  • Full support of Hyperlinks in autoshapes in xlsx files. Now hyperlinks in shapes inside xlsx files are fully preserved as they were in xls files. They also will convert between xls and xlsx, and you can change the hyperlinks of the shapes with xls.SetObjectProperty. Links are exported to pdf, html and svg.

  • Full support for "Allow users to Edit ranges" in the API. The new methods XlsFile.Protection.AddProtectedRange, XlsFile.Protection.DeleteProtectedRange, XlsFile.Protection.ProtectedRangeCount and XlsFile.Protection.ClearProtectedRanges allow you to read and modify protected ranges. Note that for simple protection you can still just lock or unlock the cells in the cell formatting. APIMate should report now how to enter Protected Ranges too.

  • New <#Switch> and <#IFS> tags in FlexCel reports. Those tags behave like the IFS and SWITCH functions added in Excel 2016 january update. They can eliminate "if chains" and make the expressions simpler. For example <#ifs(<#value> < 10;<#format cell(red)>;<#value> < 20;<#format cell(yellow)>;true;<#format cell(green)>)>

  • Autosize of chart axis when rendering charts. Now when exporting xls charts to pdf/html/etc, the axis of the chart will move to fit the data in the axis so it doesn't get cut out.

  • New parameter convertFormulasToValues added to PasteFromXlsClipboardFormat. This parameter will allow you to paste the formulas as values from the clipboard. This is useful specially if the formulas you are pasting reference other books, so they won't reference the correct cells when pasted.

  • New parameter recalcBeforeConverting added to ConvertFormulasToValues and ConvertExternalNamesToRefErrors. This parameter will allow you to convert formulas to values without first having FlexCel recalculating the file (which was the default before). So if your file can't be recalculated by FlexCel because for example it contains links to other files that don't exist anymore, you can still convert the formulas to the latest calculated values.

  • New overload of [[vcl T]]FlexCelPdfExport.ExportAllVisibleSheets taking a filename. This overload is a shortcut for creating a filestream, calling BeginExport on the stream, then calling ExportAllVisibleSheets and then calling EndExport.

  • Support for adding horizontal scrollbars with the API. There is a new property in TSpinProperties which allows to specify if the scrollbar is horizontal. APIMate will report how to do it from an horizontal scrollbar in Excel.

  • Support for ShrinkToFit attribute in cells when exporting to pdf/html/svg/images/printing/previewing. Now the ShrinkToFit attribute of cells is rendered when exporting, and will show in printing, previewing and exporting.

  • <#IF> tag in reports can now omit the false section. You can now write a tag like <#if(true;hi)> instead of <#if(true;hi;)>

  • Better chart rendering for xls files. Now the labels overflow in a way similar to Excel, and FlexCel calculates the chart axis positions so t won't overflow.

  • The file created by [[vcl T]]XlsFile.NewFile(n, TExcelFileFormat.v2016) now is in the Excel "January update" format. The "January update" of Excel 2016 added some new fonts to the themes and changed the build id of a default empty file. Now the empty files that FlexCel generates when you specify v2016 include those new fonts in the themes. The build id had already been updated in a previous FlexCel release.

  • Bug Fix. When exporting xls bar and column charts with a single data series and "Vary colors per point" = true, FlexCel was not changing the colors on each point.

  • Bug Fix. When copying sheets with data validations to other files, and the data validations would refer to a list in a different sheet, the data validation would be copied wrong.

  • Bug Fix. When rendering conditional formats in xls files sometimes the background color could be ignored.

  • Bug Fix. The constructor of TBlipFill wasn't public.

  • Bug Fix. TXlsFile.DpiForImages would be ignored in some metafiles.

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