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

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 / Version: 6.1.0.0