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

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

  • Experimental support for .NET Core 1.0 and ASP.NET Core 1.0. There is a new nuget package included which can be used in .net core. As .net core doesn't have a graphics library yet, this package can only deal with xls and xlsx files, and has no graphics capabilities like exporting to pdf or html.

    • New methods OffsetRelativeFormula and RecalcRelativeFormula in [[vcl T]]ExcelFile. Those new methods allow you to know the real value of a relative formula, such as those returned by names and data validations. Relative formulas depend on the cell the cursor is, so if the cursor moves the formula changes. As FlexCel doesn't have a cursor, it always returns the formulas considering the cursor at A1. With OffsetRelativeFormula you can get how the formula would look like when the cursor is at for example B3, and with RecalcRelativeFormula you can recalculate the formula and get the result when the cursor is at B3.
    • New static events TUIFont.FontCreating and TUIFont.FontCreated. Those events allow you to customize the font replacements in your system. For example, if you have Excel files that have a font "MyDeprecatedFont" and you would want to replace it by "MyNewCoolFont" when exporting to pdf, you can use the FontCreatingEvent to do so. You can use the FontCreated event to catch fonts where the original wasn't present in the machine and were substituted by the operating system into something else. You can then provide a different substitute font.
    • Support for quoted column names in reports. Now you can quote a column name inside a tag in a report, like <#"db.column ) "> This can be useful if you have column names with for example unbalanced parenthesis. Note that you don't need to quote the name if it has balanced parenthesis.
    • Bug Fix. In some cases when opening an xls file with existing formats, and calling AddFormat for a format already present in the file, FlexCel would fail to realize the format already existed and create a new one. This could lead to having more formats than the number allowed by Excel if you opened a file, added existing formats and save it a lot of times.
    • Bug Fix. When <#including> subreports inside FlexCel Reports with the RC option, empty row formats would be copied to non empty row formats.
    • Bug Fix. ActiveX controls with a size larger than an Int32 would raise an Exception when loading.
    • Bug Fix. Bidirectional reports could fill some wrong cells when using multiple master-details in the rows.
    • Bug Fix. Xlsx files with autofilters could become invalid if you deleted the range which contained the autofilter.
    • Bug Fix. VLookup and HLookup would return a match if you searched for a blank string ("") and the cell was empty. Excel doesn't return a match in those cases, and now FlexCel doesn't either.
    • Bug Fix. Double bordered lines could render wrong when the zoom was big (about 200% or more)
    • Bug Fix. Some invalid formulas including more than one "=" sign in a not valid location, like "=1 + =1" didn't throw an Exception when you tried to manually enter them, and would raise the exception later when trying to save. Now FlexCel will report those formulas as invalid when you try to enter them.

New on v 6.7.3.0

  • New JOIN and UNION commands for reports. Those commands are written in the config sheet and allow you to either JOIN the columns of multiple tables into a single table, or to do an UNION of the rows of multiple tables into a single one. See the new "Join and Union" demo.

  • Improved bidirectional reports. Now bidirectional reports can work with rows in master detail and they also will delete empty column bands if none of the columns has records.

  • Improved preservation of timelines in xlsx. Timelines are a feature introduced in Excel 2013, which allow you to graphically navigate a timeline in a data source. Now FlexCel should preserve timelines for pivot tables.

  • Bug Fix. Fixed order of records specific for Excel 2010 to workaround a bug in Excel 2010. Some very complex files could raise an error when opened in Excel 2010, even when they were correct by the xlsx spec.

New on v 6.7.2.0

  • Copy to the clipboard now supports html. Now there is an extra option in the formats to be copied to the clipboard: In addition to native xls (best for copying from one spreadsheet to another) and text (for apps that don't understand anything else), now you can also copy as html, which gives the best results when pasting a spreadsheet in Microsoft Word or PowerPoint. You can copy to html either using XlsFile.CopyToClipboardFormat or FlexCelHtmlExport.ExportToClipboardFormat.

  • Bidirectional Reports. Now you can create ranges in shape of a cross that expand to the right and down at the same time. While you could do this before by splitting one of the ranges in 3, now you can directly intersect the ranges and get the correct result. Take a look at the new Bidirectional Reports demo and the documentation in the report designer guide.

  • Changed default fallback fonts in pdf. Windows 10 doesn't come with MS Mincho or MS Gothic installed by default (you need to manually install the language packs to get the fonts). So now FlexCel looks for both MS Mincho/Gothic (for windows older than 10), and YuMincho/Gothic for Windows 10.

  • The tags <#List>, <#DbValue> and <#Aggregate> can now work inside nested Array/Linq datasets. Now when you have a master detail relationship where the detail is a property of the master, FlexCel can find the master dataset for the <#List>, <#DbValue> and <#Aggregate> even when they are not added with AddTable.

  • New property XlsFile.DocumentProperties.PreserveModifiedDate. FlexCel by default sets the modified date of the files it saves to the date when the file was saved. But if you want to change this date to an arbitrary date, then you can set PreserveModifiedDate to true.

  • FlexCel will now set the creation and modification date in xls files too. Now Creation and Modification dates are stored in xls files, same as they already were in xlsx.

  • FlexCel will now allow you to set the file creator for xlsx files. By default, files created by FlexCel are identified as created by FlexCel in the document properties. Now you can change the application creator by writing xls.DocumentProperties.SetStandardProperty(TPropertyId.NameOfCreatingApplication, "SomeNewCreator")

  • Bug fix. LastModifiedDateTime wasn't returned correctly for xlsx files.

  • Bug fix. Macros converted from xls files to xlsx could fail to open in Excel 2016 in some border cases.

  • Improved Getting Started document. Now GettingStarted shows actual code examples on how to do simple tasks and contains links to all documentation.

New on v 6.7.1.0

  • Support for new Excel 2016 features. While old FlexCel versions will still work fine with Excel 2016 (as expected), FlexCel now provides support for new extra features in Excel 2016. Now XlsFile.NewFile allows to create files like Excel 2016 creates by default. Also XlsFile.RecalcVersion has a 2016 option to tag your files as created by Excel 2016 so Excel 2016 doesn't ask for saving when closing them.

  • Improved support for DataValidations that have lists with cells from other sheets. DataValidations with lists of cells from other lists were introduced in Excel 2010, and while FlexCel preserved them, it wouldn't modify them when inserting or deleting ranges. They wouldn't either be reported by the API. Now they are modified and also reported by the API, just like all the other data validations.

  • Slicers for Pivot Tables are now preserved in xlsx. Now FlexCel will preserve the slicers for pivot tables present in xlsx files. This is a feature available only in Excel 2010 or newer, so you won't see them in older Excel versions, but the generated files will still open without errors.

  • Excel 2010 equations are now preserved in xlsx. Now FlexCel will preserve the new equations in Excel 2010 (Ribbon->Insert->Equation)

  • Center across selection cells are now exported to html. Now html export will export cells marked as "center across selection", same as exporting to pdf or other exports already did.

  • Improved exporting of superscripts and subscripts to html. Now superscripts and subscripts are exported better to html files.

  • Full support for formulas attached to textboxes or autoshapes. Now FlexCel will preserve and convert betwen xls and xlsx textboxes or shapes which have their text linked to a formula. If you modify the linked cell, the text in the textbox will change.

  • New methods XlsFile.SheetID and XlsFile.GetSheetIndexFromID. Those new methods can be used to identify a sheet in a FlexCel session and get it back later. Note that as this ID is not saved in the file, it will change every time you load a new file and so it can only be used in a single session.

  • New static property "UseLegacyLookup" in FlexCelReport. If you set this property to true, FlexCel will use DataViews to do lookups in DataSets instead of the new and faster internal lookup, Set this property to true only if your existing reports rely in bugs in the DataView implementation.

  • Data validations entered manually in xls files could fail to work when opened in Excel. In some border cases, Excel would report all values as invalid for a data validation entered with FlexCel, even if the values were valid. This only applied to xls files.

  • Now FlexCel can open xlsx files with images with the wrong image type. If an xlsx file now contains for example a png but it is declared as jpg, now FlexCel will open it as a png anyway. This will only happen with corrupt files or files generated by incorrect third party products.

  • Error when deleting rows in a pivot table. When deleting rows in a pivot table in an xlsx file, the rows could go negative creating invalid files.

  • Improved compatibility with third party tools. Workaround for some tags not understood by other third party tools, and able to read files missing some required records.

New on v 6.7.0.0

  • Support for opening xls versions from 2 to 4. As FlexCel already supported xls 5 and up and Excel 1 doesn't exist for Windows, this completes the support for all versions of xls. While xls versions from 2 to 4 aren't in wide use, they are still used by other third party libraries.

  • Enhanced High DPI Support in FlexCelPreview. Now FlexCelPreview supports High DPI in Windows, besides iOS or OSX as it already did.

  • Breaking Change: Property Resolution in FlexCelPreview has been removed. The property Resolution of FlexCelPreview has been removed because now FlexCelPreview automatically adjusts to the resolution of the monitor.

  • Full support for background images in a sheet. XlsFile adds two new methods to deal with background images in a sheet: SetSheetBackground and GetSheetBackground. Background images are now converted between xls and xlsx. ApiMate will also report the code to add a background image to a sheet. A new property ExportSheetBackgroundImages allows you to print or export the background images. (note that Excel never prints the background images, so this property is false by default)

  • Full support for manipulating Custom XML parts with XlsFile. The new methods CustomXmlPartCount AddCustomXmlPart, GetCustomXmlPart and RemoveCustomXmlPart in XlsFile allow for reading and writing the custom xml files of an xlsx files, as explained here: https://msdn.microsoft.com/en-us/library/bb608618.aspx ApiMate will now show how to enter custom xml parts in an xlsx file.

  • New property for PDF files: InitialZoomAndView. The new InitialZoomAndView property allows you to specify the initial page and zoom when opening the document.

  • New property for PDF files: PageLayoutDisplay. The new PageLayoutDisplay property allows you to specify if to display one or two pages, and continuous scrolling or one page at a time when opening the document.

  • Two new modes for PDF files PageLayout. Now generated PDF files can use a PageLayout of TPageLayout.OptionalContent to show the optional content panel, or TPageLayout.AttachmentPanel to show the attachments panel.

  • New property ScreenScaling in XlsFile. This new property allows you to workaround Excel bugs when working in high dpi displays. For more information read http://www.tmssoftware.com/site/blog.asp?post=311

  • Better handling of stored numbers in xlsx. Now numbers are saved in xlsx with a roundtrip format, which ensures the number we write in the file is exactly the same number that will be read.

  • Ability to <#insert> empty names in reports. Now when you use the <#include> tag in a report, you can leave the name to include empty. This will mean to insert all the used range in the active sheet.

  • New overload for XlsFile.DeleteRange. There is a new option for XlsFile.DeleteRange, which will clear the cells but not the formats on it: It will behave similar to when you press "Delete" in a range of cells in Excel.

  • New property ExportEmptyBands in FlexCelReport. ExportEmptyBands replaces the existing ExportEmptyRanges property which has been deprecated. It allows you to choose between 3 possibilities when the data table has 0 records: Delete the range and move cells up, clear the data and format of the range, or clear only the data.

  • Bug Fix. Now FlexCel will make sure the xml declaration in the custom xml parts added with AddCustomXmlPart have the same encoding as the encoding being used to store the file.

  • Bug Fix. Xlsx files with external formulas referring to other sheets starting with a number weren't quoted, and Excel would report an error when opening those files.

  • Bug Fix. FlexCel would fail to load files with formulas which pointed to tables in other files with the new table formula syntax.

  • Bug Fix. Subtotal function could recalculate wrong in border cases.

  • Breaking Change: Improved lookup tag in reports. The <#lookup> tag in reports has been rewritten to be faster and behave better. IF you are defining your own VirtualDataSets and overriding the Lookup function you might need to rewrite it, as parameters changed. But with the new base lookup implementation that is now available for all, you might just remove the override and use the base.

  • SPLIT Datasets in Reports can now be used as datasets for sheets. This allows you to overflow a report into multiple sheets. When the data in a sheets reaches the maximum of the split, it will continue in a different sheet. A new sample "Overflow sheets" shows how to do it.

  • Copy to clipboard wasn't working in Excel 2013. We modified the clipboard format so now it is working.

  • Bug Fix. When inserting or deleting columns, array formulas located in other sheets might not update to take in account those changed rows or columns.

  • Bug Fix. Sometimes when moving a range array formulas which pointed to that range might fail to update.

  • Bug Fix. Some functions with array arguments could not be calculated correctly when the formula was not an array formula.

  • Bug Fix. The lookup tag introduced in 6.6.32 could fail if the lookup value was a tag in the template

  • Bug Fix. The functions SumIfs, AverageIfs and CountIfs could give wrong results in some cases.

  • Bug Fix. When rendering a chart with an image inside, there could be an exception.

  • Bug Fix. Images inside charts with negative coordinates weren't rendered.

  • Bug Fix. Now scatter charts behave like Excel, and if any of the x-axis values is a string, it will be rendered as a line chart instead.

  • Bug Fix. XlsFile.SetAutoRowHeigth wouldn't work if the row was empty.

  • Bug Fix. Chart rendering now renders charts where all values are 0.

  • Bug Fix. Chart rendering now respects the label positions next to axis, high and low.

  • Bug Fix. ExportEmptyBands introduced in 6.6.25 wouldn't work in detail reports.

  • Bug Fix. In some cases when generating reports and exporting them to pdf directly without saving them as xls/x, there could be a range check error.

  • Bug Fix. Tabs inside text in autoshapes now will render as 8 spaces. (note that we don't use the tab definitions from the autoshape, so this is an approximation)

  • Bug Fix. When exporting to bitmaps, the bitmaps where a little bigger than the page size

  • Bug Fix. Reports using LINQ could raise an Exception in some cases with null values.

  • Improved compatibility with invalid xlsx files generated by third parties. FlexCel can now read some invalid formulas written in xlsx by other third party products.

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