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

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

  • Visual Studio 2015 and .NET 4.6 support. Support has been added for the latest Visual Studio and .NET betas.

  • Fix for the latest Xamarin version. Xamarin changed how encodings behave when they don't exist: Before they used to raise an Exception and now they return null. This broke the fallback support in older FlexCel versions, and has been fixed now.

  • Bug Fix. There could be an error when rendering error bars in charts and there were missing values.

New on v 6.6.22.0

  • New property SheetView in XlsFile allows you to set the page view mode and zoom for each mode. Now you can see or set the page view mode in a sheet (normal, page layout or page break preview). You can also specify the zoom for each of the modes. As usual, APIMate will show you the syntax.

  • New property LinksInNewWindow for FlexCelHtmlExport and FlexCelSVGExport. When you set LinksInNewWindow to true, both FlexCelHtmlExport and FlexCelSVGExport will export the hyperlinks in the file to open in a new window.

  • Links to local files and to current workbook are now exported in TFlexCelHtmlExport. Now links to local files or other cells in the current workbook are exported to html. This allows for navigating inside a file. Links in the current workbook work even when exporting to different tabs.

  • Breaking Change: XlsFile.AddImage(row, col, TUIImage) now takes in account the declared image dpi. Now if you are calling AddImage without specifying the dimensions, FlexCel will use the dimensions corrected by the dpi declared by the image. This is the same way Excel works. In previous FlexCel versions we always assumed a 96dpi image.

  • Rendering of error bars in xls charts. Now when exporting to pdf/html/etc, FlexCel will draw error bars. All modes (StdErr, StdDev, fixed, percent, custom) are supported.

  • Improved display of line charts. Now colors and sizes of lines in xls charts will be read from the new xlsx records on it if they exist. This leads to a more faithful rendering, because the xlsx records have extra information, like for example a line width that isn't restricted to 4 sizes.

  • TXlsNamedRange.GetRanges is now public and documented. GetRanges will return an array with the ranges composing a name. So if you for example have a name with the range "1:1, A:A", GetRanges will return an array with 1:1 and A:A. This method can be used to parse the PRINT_TITLES range.

  • Improved display of markers in charts. Now markers in charts render much more alike Excel 2013, with the new options for images, etc.

  • Bug fix. XlsFile.FillPageHeaderOrFooter could return an extra "&" character at the end in some cases.

New on v 6.6.21.0

  • Better Xamarin package for osx. The Xamarin package used to copy the files in the "osx" folder, now it copies to "mac" to comply with the new naming.

  • New UsePrintScale property in FlexCelHtmlExport. If you set the new property FlexCelHtml.UsePrintScale to true, then the exported html will use the scaling of the printed sheet instead of being exported at 100% zoom.

  • Bug fix. Some JPEG images weren't recognized as such.

  • Bug fix. Reports might not read expression values when tags had a default value, like <#value;0>

  • Bug fix. Sometimes FlexCel could fail to load an xlsx file with different images with the same extension but different case (like image1.png and image2.PNG)

  • New parameters in FlexCelPdfExport.AfterGeneratePage and BeforeGeneratePage events. The new parameters are the XlsFile being exported, the FlexCelPdfExport component doing the export, and the current sheet.

  • Improved RecoveryMode. Now FlexCel can recover more types of wrong files when RecoveryMode is true.

  • When drawing xls charts, we now use the options for not plotting empty cells. This option was introduced in Excel 2007, and FlexCel was ignoring it. Now if you choose not to ignore hidden rows or columns, the chart will render as expected.

  • New method XlsFile.RemoveUserDefinedFunction. XlsFile.RemoveUserDefinedFunction allows to unregister a previously registered UDF for recalculation.

  • FlexCelReport can use also arrays besides IEnumerable for detail bands. When using IEnumerable as data source, now you can use a fields which are array instead of an IEnumerable as detail tables.

  • Breaking Change: Now when drawing chart labels that have N/A! error as result, FlexCel won't draw them. Excel 2003 or older is different in the way it draws #NA! errors in chart labels from Excel 2007 or newer. In older Excel versions, the label would just draw as #NA!. In newer Excel versions, it doesn't draw. To be consistent with more modern Excel versions, now FlexCel won't draw them either when exporting to pdf or html.

  • New NOGRAPHICS define. If you define NOGRAPHICS and undefine GDIPLUS in the FlexCel project properties, you'll get a build which doesn't depend on any drawing engine.

  • New DOTNETZIP define. If you define DOTNETZIP in the FlexCel project properties and add a reference to it, you'll get a build which uses dotnetzip instead of System.IO.Compression.

  • Bug fix. In some cases, when pasting a file with autofilters from Excel you could get a range error. This is because Excel copies the filter in its totality and part of the filter might be outside the range copied. Now FlexCel will resize the autofilter if it extends beyond the copied range.

New on v 6.6.11.0

  • Support for recalculating 31 new functions introduced in Excel 2013. Support has been added for: DAYS, ISOWEEKNUM, BITAND, BITOR, BITXOR, BITLSHIFT, BITRSHIFT, PDURATION, RRI, ISFORMULA, SHEET, SHEETS, IFNA, XOR, FORMULATEXT, COT, ACOT, COTH, ACOTH, CSC, CSCH, SEC, SECH, ARABIC, BASE, DECIMAL, COMBINA, PERMUTATIONA, MUNIT, UNICHAR, UNICODE

  • Subtotal command in XlsFile. There is a new command xls.SubTotal(...) which works the same as the command "Subtotal" in the Excel Ribbon, "Data" tab. While you shouldn't use this when creating new files, it can be useful for formatting old files. For new files, it is best to just create the subtotals in place.

  • New option "ExcelLike" in XlsFile.Sort. Now when doing a XlsFile.Sort command you can choose between the correct way to handle formulas (this was the only option before) or the "Excel" way of handling formulas, where references are not updated when a row is moved of place in the sort. The ExcelLike mode doesn't adapt formulas that reference those rows, but it can be much faster for tens of thousands of records.

  • New methods IsRowMarkedForAutofit and IsColMarkedForAutofit in XlsFile. The new methods will return true if a row or column was marked for autofit.

  • New property ExcelFile.AllowEnteringUnknownFunctionsAndNames. If you set this property to true, you will be able to enter unknown functions inside formulas, like "=SomeText()". Excel will show the result as a #NAME! error. When this property is false (the default) FlexCel will raise an Exception if the name is not know, which is better to detect misspells.

  • New properties XlsFile.RecalcVersion and FlexCelReport.RecalcVersion. This new properties allow you to specify the Excel version that last calculated the file. If you set it to for Example Excel 2010, any Excel newer than Excel 2010 will recalculate the file on open, and ask for saving changes when you close the file. Excel 2010 or older won't recalculate the file on open. If you want every version of Excel to recalculate on open set this property to AlwaysRecalc (the default). Look at the API developers guide for more information.

  • Included reports can now reference the formats of the parent report. Now an included report can reference the formats of the parent report, same way as it can reference the expressions.

  • Improved Xamarin Unified API support. Changed the unified API support to compile in the latest beta.

  • Breaking Change: XlsFile.RecalcForced and FlexCelReport.RecalcForced properties have been removed. RecalcForced used a way to make files recalculate on open which has been deprecated in newer versions of Excel, and will cause validation errors with the generated files. For this reason, RecalcForced wasn't doing anything in the last couple of years. Look at the new RecalcVersion property if you were using RecalcForced and want new not deprecated way to create files which Excel will recalculate on open.

  • New overloads of XlsFile.GetObjectProperties and XlsFile.GetObjectAnchor that take an object path. This new methods allow you to access the properties and anchor of an object by specifying its name, as in Xls.GetObjectAnchor(-1, "@MyObject")

  • Autofitting columns with 90 degree rotation would work always as if the column had "Wrap text" enabled. When autofitting columns which had a rotation of 90 degrees, FlexCel would always try to wrap the text so it fitted in many lines, even if the cell wasn't set to wrap. Now it will only do this if the cell has "Wrap text" on.

  • Breaking Change: Removed Xamarin Android 2.2 support. As Froyo (2.2) is now deprecated, we've removed this support in order to avoid deprecated warnings. Now minimum supported is 2.3 (Gingerbread).

  • Bug Fix. When doing reports with Linq, aggregating a double field could raise Exceptions.

  • Pivot tables in xlsx are now copied when you copy sheets. Now if you InsertAndCopySheet(...) a sheet with a pivot table from an xlsx file, the table will be copied. (pivot tables in xls were already copied)

  • Unknown names in formulas now return #NAME! instead of #NA!. Now when a formula references a name that doesn't exist, FlexCel will return #NAME! as the formula result, instead of #NA! as it used to do.

  • Bug fix. When setting the text of an object using SetObjectText the font might not be preserved.

  • Bug fix. When changing the font in HtmlFont event in TFlexCelHtmlExport there could be an Exception.

  • Bug fix. RoundUP and RoundDown functions could return the same number and not the rounded up number in some cases when the number of digits was negative.

  • Bug Fix. Rendering some files with thousands of hidden columns could take too long.

  • Improved compatibility with invalid xls files. Now FlexCel can read some more invalid xls files created by third parties.

  • Hidden rows could sometimes count when finding the maximum used column in the sheet. When printing or exporting an xls/x file, a hidden row with columns outside the printing range could in some cases cause the maximum column to be that in the hidden row, which wouldn't be printed.

  • Sheet names aren't always quoted when returning formula text. In older FlexCel versions, the sheet was always quoted in formulas. So if you retrieved for example the formula in A1, it could be 'Sheet1'!A2. Now we quote the sheet only if needed, same as Excel does. So we would return Sheet1!A2 instead.

  • New convenience constructor for XlsFile which takes a Stream. Now you can create an XlsFile and open a stream in a single operation, without having to first create the XlsFile and then call xls.Open.

  • New properties FlxConsts.MaxRowCount and FlxConsts.MaxColCount. Those properties return FlxConsts.Max_Rows + 1 and FlxConsts.Max_Columns + 1 respectively. Max_Rows and Max_Columns were zero based, so for example Max_Rows return 65535 for xls and not 65536 which is the row count. The new properties return the one-based maximum, which makes it simpler to work in the one-based FlexCel API.

  • Improved error message when opening files with 0 bytes. Now when opening files with 0 bytes or streams with the position at the end, FlexCel will say a clear message instead of saying that the file format isn't Excel or newer.

New on v 6.6.2.0

  • Better rendering of text in rotated shapes. In Excel 2003 or older, text in rotated shapes was shown without rotation and that's how FlexCel would show them. Since Excel 2007 the text can rotate with the shape, using an undocumented record in xls. Now FlexCel can read it and will honor that setting when converting to pdf/html/svg/printing/etc.

  • Bug Fix. A local link in a pdf to a page that wasn't exported could cause an Exception.

  • Bug Fix. Exporting "Center on selection" cells could be too slow in border cases.

  • Bug Fix. XlsFile.SetCommentRow could set the wrong comment in some cases.

New on v 6.6.1.0

  • Generic reports using <#table.*> can now use user defined functions. Now you can apply an user defined function to a <#table.*> tag.

  • Generic reports using <#table.*> can now reference fixed fields in the table. Now you can mix <#table.field> with <#table.*> in the same cell.

  • Better compatibility with files created by third parties. Now FlexCel will load invalid xlsx files with repeated comments.

  • Bug Fix. Generated xlsx files could be invalid when removing frozen panes from an existing file.

New on v 6.6.0.0

  • Breaking Change: Now the result of ShapeOptions.Text is a TDrawingRichString instead of a TRichString. In order to allow more customizability in the text of shapes and objects, we had to move the text property from a TRichString (which is used for cells, and in xls was also used for objects) to a TDrawingRichString (Which in xlsx offers more possibilities to customize the text). As there is an automatic conversion from a TDrawingRichString to a TRichString, most code will just keep working. But there might be some cases (like functions where you pass a var parameter) where you will need to change the types form TRichString to TDrawingRichString in order to compile.

  • Better support for preserving autoshape text in xlsx. Now when you change the text of an autoshape in xlsx, the existing properties of the text will be preserved.

  • Support for reading and writing a cell's text direction (RTL, LTR or Context). Now you can specify the text direction in a cell, and APIMate will show you how to do it. The FlexCel rendering engine also now supports better RTL code (still without providing official RTL support, it is better in this version and usable in most cases)

  • Support reading the number of horizontal and vertical page breaks in a sheet. Two new properties: XlsFile.HPageBreakCount and XlsFile.VPageBreakCount return the count of page breaks in a sheet.

  • Bug Fix. XlsFile.LastFormattedCol returned the last formatted column - 1. Now it is returning the correct number.

  • Bug Fix. Rendered xls charts could show an extra line in some corner cases with missing data.

  • Bug Fix. TOPN datatables didn't inherit their relationships with master datasets.

  • Bug Fix. Macro references in buttons could be copied wrong when copying sheets.

  • Bug fix. When copying a range of cells to another sheet which included formulas introduced in Excel 2007 or newer there could be an error when saving as xls.

  • Bug Fix. FlexCel enforced a maximum of 1023 manual page breaks for xls but not for xlsx. Now We also check that the generated xlsx files don't have more than 1023 manual page breaks, since that would crash Excel.

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