4D View Pro: Excel import/export update

As part of 4D View Pro‘s continuous development, we’ve expanded the Excel import and export functionalities in 4D 20 R8. In this update, new language options have been added to align with the interface capabilities and give you more control over the exported workbook.

Export enhancement

We’ve added a new set of export options to improve your experience when exporting Excel files with the VP EXPORT DOCUMENT command. These options are aligned with the interface and give you more control over the exported workbook:

• includeBindingSource : Whether to include the binding source when exporting, default false.
• includeStyles ^new: Whether to include the style when exporting, default true.
• includeFormulas ^new: Whether to include the formula when exporting, default true.
• saveAsView ^new: Whether to apply the format string to exporting value when exporting, default false.
• rowHeadersAsFrozenColumns ^new: Whether to treat the row headers as frozen columns when exporting, default false.
• columnHeadersAsFrozenRows ^new: Whether to treat the column headers as frozen rows when exporting, default false.
• includeAutoMergedCells ^new: Whether to include the automatically merged cells when exporting, default false.
• includeCalcModelCache ^new: Whether to include the extra data of calculation. It can be faster when opening the file with those data, default false.
• includeUnusedNames ^new: Whether to include the unused custom name when exporting, default true.
• includeEmptyRegionCells ^new: Whether to include any empty cells(cells with no data or only style) outside the used data range, default true.
• password: Set the password to open the workbook.

To make the code easier to understand, all these new features are grouped together in an excelOptions object. The old attributes are still supported to ensure compatibility with your old code.

var $excelOptions:={}
$excelOptions.includeBindingSource:=False
$excelOptions.includeStyles:=True
$excelOptions.includeFormulas:=True
$excelOptions.saveAsView:=False
$excelOptions.rowHeadersAsFrozenColumns:=False
$excelOptions.columnHeadersAsFrozenRows:=False
$excelOptions.includeAutoMergedCells:=False
$excelOptions.includeCalcModelCache:=False
$excelOptions.includeUnusedNames:=True
$excelOptions.includeEmptyRegionCells:=True
$excelOptions.password:="mypassword"
VP EXPORT DOCUMENT("ViewProarea"; "c:\\tmp\\testexcel.xlsx"; 
{excelOptions: $excelOptions)})

import enhancement

We’ve also introduced advanced import options to complement your Excel workflows in the VP IMPORT DOCUMENT command. These new options provide greater flexibility when importing data from Excel files:

• includeStyles ^new: Whether to include the style when loading, default true.
• includeFormulas ^new: Whether to include the formula when loading, default true.
• frozenColumnsAsRowHeaders ^new: Whether to treat the frozen columns as row headers when loading, default false.
• frozenRowsAsColumnHeaders ^new: Whether to treat the frozen rows as column headers when loading, default false.
• fullRecalc ^new: Whether to calculate after loading the json data, false by default.
• dynamicReferences ^new: Whether to calculate functions with dynamic reference, default true.
• calcOnDemand ^new: Whether to calculate formulas only when they are demanded, default false.
• includeUnusedStyles ^new: Whether to include the unused name style when converting excel xml to the json, default true.
• password ^new: The password to open the workbook.
• openMode ^new: The open mode of normal, lazy and incremental. By default is normal.

To make the code easier to understand, all these new features are grouped together in an excelOptions object. The old attributes are still supported to ensure compatibility with your old code.

var $excelOptions:={}
$excelOptions.includeStyles:=True
$excelOptions.includeFormulas:=True
$excelOptions.frozenColumnsAsRowHeaders:=False
$excelOptions.frozenRowsAsColumnHeaders:=False
$excelOptions.fullRecalc:=False
$excelOptions.dynamicReferences:=False
$excelOptions.calcOnDemand:=False
$excelOptions.includeUnusedStyles:=True
$excelOptions.password:="mypassword"
$excelOptions.openMode:="normal"
VP IMPORT DOCUMENT("ViewProarea1"; "c:\\tmp\\textfreeze.xlsx";
 {excelOptions: $excelOptions})

Conclusion

These updates simplify and enhance your work with Excel files in 4D View Pro. For full details on the new features, check out the 4D View Pro documentation.