Build 4.5.0.73 of xlsgen fixes two problems related to rendering :

1) correct page margin inclusion (right edge)

2) in header/footer, any content in the center portion can span along the entire width of the page, excluding margins, not just the third of the width of the page, excluding margins.

Build 4.5.0.72 of xlsgen includes a number of fixes :

1) better handle fitToWidth and fitToTall page setup settings from XLSX files

2) improved fit-to-page algorithm when either fitToWidth or fitToTall is unspecified

3) localize PDF bookmarks in the PDF bookmarks bar by taking into account the current formula language

4) improved position of pictures when rendering pages with repeat columns or rows

5) avoid picture duplicates in XLSX and XLSB files (and their variants, XLSM, XLST, ...)

6) merged cells pre-write for rendering needs

Build 4.5.0.71 of xlsgen is a pack of 2 fixes.

1) Support for the variant of =CEILING(number, significance) function introduced by Microsoft in Excel 2013. A silent behavorial change was introduced which allows the number to be negative while the significative to remain positive in order to ask for the rounding be the smallest integer instead of highest integer.

2) More fixing of internal shared formulas in cell manipulation scenarios.

Build 4.5.0.70 of xlsgen adds object name setting and querying. This applies to shapes, pictures, text boxes, vector shapes and charts.

In Excel, object names appear in the same list than defined named, even though technically speaking they are not because they have no formula attached to it. Some of those names can be edited using the corresponding ribbon name field.

Querying a known name is a convenient method for getting an object and doing something with it, such as calculating depending formulas, rendering it and so on. Querying a known name in xlsgen is achieved by first getting the objects collection in question (for instance the charts collection for the current worksheet) and then use the ItemByName property to obtain the object by just passing the name as argument.

In xlsgen, when new objects such as shapes, pictures, text boxes, vector shapes and charts are created, they have no name by default. You can add one by just using the Name property which is a read/write property.

This works with all Excel file types.

Build 4.5.0.69 of xlsgen improves the page order algorithm in print/preview/PDF/XPS scenarios.

Prior to this build, pages would be rendered by xlsgen in a top-down then left-right algorithm with no API available to change that.

The page setup object has a new property, PageNumberLeftToRight, which when set to TRUE does the opposite, i.e. pages are rendered in a left-right then top-down algorithm.

Build 4.5.0.68 of xlsgen has 6 fixes :

1) fix picture duplication for XLS files (mso drawing solver container records correctly placed)

2) migrate form controls from XLS files to XLSX/XLSB files

3) migrate shape arrows properly from XLS files to XLSB/XLSB files

4) automatic source code generator exposes more accurate column width (floating point values)

5) sparkline rendering of "same min and max property for all sparklines" in XLSX/XLSB files

6) translation of formula error codes such as #VALUE! in the 6 formula languages (english, french, german, spanish, italian and portuguese). So #NAME? appears as #¿NOMBRE? in spanish, #VALUE! appears as #WERT! in german, and so on. Don't forget to set the formula language before processing a formula (reading or writing) with the workbook FormulaLanguage property.

Build 4.5.0.67 of xlsgen has two fixes :

1) case of formula corruption on row manipulation (insertion/deletion)

2) range selection allows max column and max row of current worksheet

Build 4.5.0.66 of xlsgen is a fix pack.

1) incorrect sparklines minimum and maximum axis options handling for XLSX files

2) incorrect picture migration from XLS to XLSX or XLSB files, related to changing the sign of internal offsets

The automatic source code generator can already create source code in VB, VB.NET, VBScript, C++, C# and Java from an arbitrary Excel file, by just passing the file to it. Build 4.5.0.65 of xlsgen adds Python to the list of supported languages.


Support for Python in the xlsgen automatic source code generator

Here is an example of what it produces from an existing Excel file :


#
# Automatically generated Python code for xlsgen
#
# Produced by xlsgen code generator (c) ARsT design.
# For more info, see http://xlsgen.arstdesign.com
#

#
# Setting up Python for use in xlsgen :
#
# - Download python MSI installer (for instance python 2.7.10, 17 MB) from
# https://www.python.org/downloads/release/python-2710/
#
# - Double-click on python-2.7.10.msi in order to install the software
#
# - Download the python COM types library from
# https://pypi.python.org/pypi/comtypes
# (deep download link is https://github.com/enthought/comtypes/archive/1.1.2.zip)
#
# - Extract the /comtypes folder from the COM types library
# into the \Lib folder, where is the Python 2.7.10 install folder.
#
#
# In order to run the project, use the following command :
# \python.exe test.py
#


#
# Excel workbook creation
#

import comtypes.client
from comtypes.client import CreateObject

engine = CreateObject("ExcelGenerator.ARsTDesign")

xlsgen = comtypes.client.GetModule(["{2BDEA919-2489-4ED2-8F55-C12D74DAD9B8}", 1, 0])

wbk = engine.New("myfile.xlsx")

wbk.Theme = xlsgen.workbooktheme_classic

#
# Worksheet "MU_REPORT"
#

wksht001 = wbk.AddWorksheet( "MU_REPORT" )

#
# Worksheet "Data Dictionary"
#

wksht002 = wbk.AddWorksheet( "Data Dictionary" )

#
# Worksheet "SheetNew"
#

wksht003 = wbk.AddWorksheet( "SheetNew" )

#
# declaration of dynamic named ranges
#

dynrange0000 = wksht001.NewDynamicRange("_ftnref1")
dynrange0000.Formula = "='Data Dictionary'!$C$8"

wksht001.Cell[1,1].HtmlLabel = "<font size=11 name=\"Calibri\">EHR Certification Number</font>"
wksht001.Cell[1,2].HtmlLabel = "<font size=11 name=\"Calibri\">Vendor Name</font>"
wksht001.Cell[1,3].HtmlLabel = "<font size=11 name=\"Calibri\">EHR Product CHP Id</font>"
wksht001.Cell[1,4].HtmlLabel = "<font size=11 name=\"Calibri\">EHR Product Name</font>"
wksht001.Cell[1,5].HtmlLabel = "<font size=11 name=\"Calibri\">EHR Product Version</font>"
wksht001.Cell[1,6].HtmlLabel = "<font size=11 name=\"Calibri\">Product Classification</font>"
wksht001.Cell[1,7].HtmlLabel = "<font size=11 name=\"Calibri\">Product Setting</font>"
wksht001.Cell[1,8].HtmlLabel = "<font size=11 name=\"Calibri\">Product Certification Edition Yr</font>"
wksht001.Cell[1,9].HtmlLabel = "<font size=11 name=\"Calibri\">Attestation Month</font>"
wksht001.Cell[1,10].HtmlLabel = "<font size=11 name=\"Calibri\">Attestation Year</font>"
wksht001.Cell[1,11].HtmlLabel = "<font size=11 name=\"Calibri\">Business State/Territory</font>"
wksht001.Cell[1,12].HtmlLabel = "<font size=11 name=\"Calibri\">Provider Type</font>"
wksht001.Cell[1,13].HtmlLabel = "<font size=11 name=\"Calibri\">Specialty</font>"
wksht001.Cell[1,14].HtmlLabel = "<font size=11 name=\"Calibri\">Program Year</font>"
wksht001.Cell[1,15].HtmlLabel = "<font size=11 name=\"Calibri\">Payment Year</font>"
wksht001.Cell[1,16].HtmlLabel = "<font size=11 name=\"Calibri\">Provider Stage Number</font>"
wksht001.Cell[1,17].HtmlLabel = "<font size=11 name=\"Calibri\">Program Type</font>"
wksht001.Cell[1,18].HtmlLabel = "<font size=11 name=\"Calibri\">Attestation_ID</font>"
wksht001.Cell[1,19].HtmlLabel = "<font size=11 name=\"Calibri\">Provider_ID</font>"
wksht001.Cell[2,1].HtmlLabel = "<font size=11 name=\"Calibri\">30000001SVGWEAS</font>"
wksht001.Cell[2,2].HtmlLabel = "<font size=11 name=\"Calibri\">NextGen Healthcare</font>"
wksht001.Cell[2,3].HtmlLabel = "<font size=11 name=\"Calibri\">CHP-007425</font>"
wksht001.Cell[2,4].HtmlLabel = "<font size=11 name=\"Calibri\">NextGen Ambulatory EHR</font>"



pivotTable003s0 = wksht003.NewPivotTable();
pivotTable003s0.DataSource.Range = "'MU_REPORT'!A1:S300"
pivotTable003s0.Options.Layout = xlsgen.pivottablelayout_outline
pivotTable003s0.Options.BuiltInPivotTableStyle = xlsgen.pivottablestyle_light16
pivotTable003s0.Options.ShowRowHeaders = True
pivotTable003s0.Options.ShowColumnHeaders = True
pivotTable003s0.Options.ShowRowStripes = False
pivotTable003s0.Options.ShowColumnStripes = False
pivotTable003s0.Options.ShowGrandTotalsForRows = True
pivotTable003s0.Options.ShowGrandTotalsForColumns = True
pf003s0r1 = pivotTable003s0.Rows.AddByName("EHR Product Name")
pf003s0r1.SortAscending = True
pf003s0r2 = pivotTable003s0.Rows.AddByName("Vendor Name")
pf003s0r3 = pivotTable003s0.Rows.AddByName("EHR Product Version")
pf003s0d1 = pivotTable003s0.Data.AddByName("Specialty")
pf003s0d1.AggregateFunction = xlsgen.aggrpivotfunction_count
pivotTable003s0.InsertAt(2, 2)

#
# Excel workbook epilogue
#


wbk.Close()


Build 4.5.0.64 of xlsgen is a fix pack, including the following :

- better rewrite protected ranges from existing files

- better handle empty non-zero strings from some XLSB records

- handle corrupt files with right or bottom shape anchors outside the sheet

Build 4.5.0.63 of xlsgen fixes an column overflow problem related to inserting a picture/chart/shape whose right anchor is on the maximum possible column of the sheet (i.e. 256 for XLS files, 16384 elsewhere). This did not lead to a corrupt Excel file, just inproper overflow handling in client code. By overflow is meant the column plus the offset shift in that column, i.e. how this is actually stored in an Excel file.

Build 4.5.0.62 of xlsgen improves the compatibility of xlsgen with Office 365 updates by making sure to preserve new namespaces that may be included in files, even those who will exist only in the future.

The previous build, 4.5.0.61, tackled a missing revision namespace that was included after Excel 2016 shipped, during one of the many Office 365 updates. That was the needed fix, but the build in question fixed the preservation of this very namespace, not all namespaces that may appear in the future.

Preserving future namespaces automatically is what build 4.5.0.62 by making sure that any part of the file written by xlsgen rewrites any namespace that may be declared when opening an existing file, regardless the version of Excel used to create it.

Build 4.5.0.61 of xlsgen fixes a problem related to preserving a newly introduced revision tag for Notes (i.e. comments) in cells. This tag was introduced after Excel 2016 apparently, during one of the many Office 365 updates.

Build 4.5.0.60 of xlsgen renders pictures in header/footer.

Basically any Excel file can have one or more pictures in headers or footers. Beginning with this build, those pictures get rendered in print/preview/PDF/XPS scenarios.

Build 4.5.0.59 of xlsgen improves the rendering quality of pictures in print/preview/PDF/...

Beginning with this build, pictures are exported at twice the target resolution so they can scale much better (zoom level in the client application). Before this build, a picture would be rendered at the exact target resolution. In the case the target resolution was actually small, the picture would easily appear grainy and blurry at any scale.

Build 4.5.0.58 of xlsgen has a fix related to rendering repeat columns in print/preview/PDF/XPS scenarios.

Indeed repeating rows was correctly handled, but not repeating columns. For the record, repeating rows or columns is a range in print options that is rendered respectively at the top or to the left of each rendered page.

Build 4.5.0.57 of xlsgen has fix for XLSX files and their variants (XLSB, ...) related to better handling locally defined names. xlsgen makes sure, when it looks up an existing defined name, to take a preference for the local name, as opposed to either the global name or local names from different worksheets.

Build 4.5.0.56 of xlsgen has a fix for calculating TODAY() and NOW() functions. Beginning with this build, xlsgen uses the local time instead of the system time. The local time accounts for the timezone of the interactive user and is therefore what is expected most often in calculations.

Build 4.5.0.55 of xlsgen improves the import from CSV, XML, JSON and HTML files, by adding the ability to split columns.

Let's take an example of a data table including a person's name. This name often differentiates the first and last name in different columns, ie :


Data with first and last names in separate columns

But sometimes both are in the same column, separated by a special character, here a comma :


Data with both first and last names in the same column

In the latter case, xlsgen can import the data in separate columns. The mechanism is available for all import file formats, ie CSV, XML, JSON and HTML.

VB code

' import a CSV file, split Name column in two First name and Last name columns Dim wbk As xlsgen.IXlsWorkbook Set wbk = engine.New("ResultingFile.xlsx") Dim wksht As xlsgen.IXlsWorksheet Set wksht = wbk.AddWorksheet("Sheet1") Dim cs As xlsgen.IXlsImportColumnSplitter Set cs = wksht.Import.CSV.Options.ColumnSplitByName("Name") cs.SplitBySeparator = "," cs.AddColumn("First name") cs.AddColumn("Last name") wksht.Import.CSV.ImportFile ("datasource.csv")

Other than splitting based on a special character, the split can be based on a string length. For instance, if you know the data is made of 3 pieces of 10 characters each, you can set SplitByLength to 10.

Each split column can have its own style and number format. This is specified in the IXlsImportColumnSplit interface.

Build 4.5.0.54 of xlsgen fixes a problem that was introduced by the previous build 4.5.0.53 related to the isolated server (xlsgen.exe). Late-binding programming languages (VBScript and JScript) were not working anymore because the type libraries used to marshall calls were not loaded prior or during the first call.