Excel stores values internally either as strings or numbers. The data you import can be in a variety of formats, including numbers, strings, dates, booleans, etc. ExcelTemplate uses the data type of your data to determine how it should be inserted into the Excel template. However, by default ExcelTemplate also attempts to convert strings to numbers where appropriate. This article will explain what options are available for controlling this conversion.
Special case: Please note that preserving numbers with leading zeros as strings does not always result in the desired behavior. Special consideration should be taken when importing numbers with leading zeros. For information about the steps that should be taken to best do this, please refer to KB article 994: Preserving leading zeros with ExcelWriter.
Conversion features
There are two features which control whether the automatic conversion of numeric strings takes place:
1. PreserveStrings property
This property is set to false by default, but setting it to true will disable any automatic conversion of strings to numbers from occurring. Be sure to set this property before calling Process.
For example, if you wanted to disable the conversion of any numbers to strings when importing data, you would set the PreserveStrings property to true:
[C#]
xlt.PreserveStrings = true;
2. Convert and Preserve data marker modifiers
The Convert and Preserve data markers modifiers (available in ExcelWriter 6.5.1 and later, .NET and Java only) allow finer control of which values are converted
and which are not. Depending on how the PreserveStrings property is set, you
can use these modifiers in your template to override the global behavior on
certain columns.
For example, if PreserveStrings is set to its default value of false, you could
set a specific column to not be converted by putting the Preserve modifier on
that column's data marker.
%%=MyData.ColumnOfStrings(Preserve)
Recommended practices
By default, ExcelTemplate will attempt to convert strings to numbers wherever it
can. This is desirable when your data is not already stored in objects of the
appropriate data type. We generally recommend setting the PreserveStrings
property to true, so that this automatic conversion will not occur. It is
generally preferrable to use the appropriate object/data types for your data to
eliminate the need for any conversion by ExcelWriter. This provides the most predictable behavior when importing data. |
