Problem

Importing numbers with leadings zeros, such as zip codes and social security numbers, can pose a challenge. While these numbers are most often stored in a database as strings, they can be imported into Excel as either numbers or strings. Importing as numbers is the preferred option, as importing as strings will cause Excel to display a warning message that a number is being displayed as text.

Solution

When importing numbers with leading zeros, you have two options:

1. Import or insert the values as numbers and not as strings. Set the appropriate special format as needed to display the desired number of leading zeros. This is the recommended practice.
2. Import or insert the values as strings. Format the cells as General or Text. Excel will display a warning on each cell that a number is displayed as text. However, the cell values will contain the leading zeros.

Option 1: Import numbers with leading zeros as numbers (Recommended)

As mentioned above, the preferred option for importing numbers with leading zeros as numbers. Leading zeros will be stripped off, as numeric data types cannot contain leading zeros. To display the desired number of leading zeros within Excel on these numeric values, the appropriate formatting string should be set on the cells which contain the data.

It should be noted that a limitation to this approach is that when a value is read out of a cell there will be no leading zeros. If you would prefer to have leading zeros stored in the cell values (and have Excel display the warning message instead), you should use Option 2 as described below.

ExcelTemplate

When using ExcelTemplate, you have three options for converting your data from strings to an appropriate numeric data type. You can:

• Let ExcelTemplate attempt to globally convert all strings to numeric values where appropriate. (This is the default behavior.)
• Convert the applicable zip code values (and any other numbers) to numeric data types through native features of the language. Then set the PreserveStrings property to true to force ExcelTemplate to preserve all strings as strings. This approach will result in the most predictable results, since setting PreserveStrings to true will prevent ExcelWriter from performing string to numeric data type conversions.
• Use a combination of Preserve and/or Convert data marker modifiers on specific columns. Which modifiers you use should depends on how PreserveStrings is set. If you choose this option, the recommended practice is to set PreserveStrings to true and use Convert data marker modifiers on zip code columns. For more info about data marker modifiers, please refer to the Creating Data Markers documentation.

Then set the appropriate cell formats on the template for the columns which will contain these values. Use the Zip Code or Social Security formats (listed under the Special category), or use your own custom string.

ExcelApplication

With ExcelApplication, no automatic string to number conversion exists. Therefore, you must convert the applicable zip code data using native features of the language. Set the appropriate cell formats on the existing file, if there is one, or use the ExcelApplication API to set it on an Area, Range, or individual Cell.

When setting the formatting through the ExcelApplication API, you should use the Style.NumberFormat property. For example, to set a zip code format on a cell, you would do:

 myCell.Style.NumberFormat = NumberFormat.Special_EN_US.ZipCode; 

In the above example, the NumberFormat.SpecialENUS.ZipCode constant is equivalent to “00000”. This forces Excel to always display five digits, prepending zeros to the number as necessary.

Option 2: Import numbers with leading zeros as strings

As mentioned above, you can import numbers with leading zeros as strings instead of converting them to numbers. However, Excel will display a warning that a number is displayed as text. This will allow you store the leading zeros in the cell values, preventing the need to set a special cell format, and also allowing the value to be read from the cell so that it includes the leading zero.

ExcelTemplate

To preserve strings using ExcelTemplate, use a combination of the following techniques:

• Set the global PreserveStrings property to true.
• Use a combination of Preserve and/or Convert data marker modifiers on specific columns. Which modifiers you use depends on how PreserveStrings is set.

Set the applicable cell formats to General or Text in the template.

For more information about using ExcelTemplate to convert strings to numbers where appropriate, please see this post: How to preserve strings with ExcelTemplate.

ExcelApplication

To preserve string values with ExcelApplication, no action is needed. ExcelApplication will always preserve strings, because it provides no automatic conversion functionality.

Set the appropriate cell formats to General or Text, either on the template or using the ExcelApplication API. The following example sets a cell format to Text:

 myCell.Style.NumberFormat = "@";

Problem

ExcelWriter supports the insertion of horizontal and vertical page breaks within a Worksheet using the ExcelApplication class/object. This post covers how to insert page breaks in a worksheet.

Solution

• Worksheet.InsertHorizontalPageBreak – inserts a horizontal page break
• Worksheet.InsertVerticalPageBreak – inserts a vertical page break

C# Example


//--- The following namespace has been imported:
//--- SoftArtisans.OfficeWriter.ExcelWriter

//--- Instantiate ExcelApplication and create a new Workbook
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create();
Worksheet sheet1 = wb.Worksheets[0];

//--- Insert horizontal page break below cell A15
sheet1.InsertHorizontalPageBreak(sheet1.Cells["A15"]);

//--- Insert vertical page break to the right of cell H1
sheet1.InsertVerticalPageBreak(sheet1.Cells["H1"]);

//--- Save Workbook
xla.Save(wb, Response, "out.xls", false);

Problem

When importing data using the ExcelTemplate class, you can choose to preserve strings or to have ExcelTemplate attempt to convert them to a number. Your choice depends upon how you import the data.

Solution

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 this post: How to preserve 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:

 xlt.PreserveStrings = true; 

2. Convert and preserve data marker modifiers

The convert and preserve data markers modifiers 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.

Problem

ExcelApplication provides programmatic control over Excel files, including the ability to hide worksheet columns. This post covers how to hide a column using the ExcelApplication API.

Solution

To set a column in a worksheet to be hidden, use Worksheet.GetColumnProperties to return a ColumnProperties object and set ColumnProperties.Hidden to true.

For example:

Worksheet.GetcolumnProperties(0).Hidden = true; //Hides column A in Worksheet 

Problem

When using PivotTables in ExcelTemplate in OOXML files (XLSX, XLSM), the drop-down filters show data marker values:

Solution

The default PivotTable behavior is to cache the filter items, even when they are no longer in the data source.

To make sure that this cached information is cleared when the PivotTable refreshes:

1. In your template file, right-click on the PivotTable and select PivotTable options
2. Go to the Data tab.
3. Under Number of items to retain per field select None from the drop-down.

Make sure that Refresh data when opening file is also checked so that the PivotTable refreshes with the imported data.

When you populate the file with data, the data markers should be gone from the drop-down:

Problem

When populating a data marker, ExcelWriter inserts as many rows into the worksheet as there are rows in the data source. If a range reference includes a cell containing a data marker, the range reference will be modified to include all of the newly inserted rows, regardless of whether relative or absolute referencing is used. This behavior is desirable in many situations, such as using ExcelTemplate to populate the source data range for a chart.

However, in some cases, it is desirable that a range reference stays constant, such as when it is used in a formula.

Solution

You can use Excel’s INDIRECT worksheet function to ensure that a range reference is not modified even as new rows are inserted into the range.

For example, you have the following formula: =SUM(A1:A10)

Let’s say one of the cells in the range, A5, contains a data marker. If 10 new rows are inserted into the range when the data marker is populated, the formula will be updated to =SUM(A1:A19)

This behavior occurs even if you use absolute referencing. i.e., the formula =SUM($A$1:$A$10) will be converted to =SUM($A$1:$A$19).

The INDIRECT function converts a text string into a range reference. Because the range reference is created from a text string, it is not modified by Excel when rows are inserted. For the example above, you can preserve the range reference as A1:A10 with the following formula: =SUM(INDIRECT(“A1:A10”))

Related Links

Microsoft KB article for the INDIRECT function

Problem

ExcelTemplate allows users to specify the number of rows they want to important from a data set. For instance, importing only the top 20 rows into the report.

Solution

When importing multiple rows of data with the BindData() method, you can limit the maximum number of rows to import using the MaxRows property of the DataBindingProperties class.To limit the max number of rows to 20:

//Create a DataBindingProperties object
DataBindingProperties dataprops = xlt.CreateDataBindingProperties();

//Limit the number of rows to import to 20
dataprops.MaxRows = 20;

//Bind the data using that DataBindingProperties object
xlt.BindData(dataTable, "DataSourceName", dataprops);
...rest of ExcelTemplate code...

Problem

A conditional format is a format in Excel that is applied to a cell if a specified condition is met. ExcelWriter’s ExcelApplication object also provides the ability to create conditional formats with the ConditionalFormat object.

These are some tips for getting started with conditional formatting in ExcelWriter.

Solution

Background

The ConditionalFormat object contains up to three Condition objects, which represent the condition that determines if the format is applied, and a Range object, which represents the cells to which the format will be applied.

The condition stores a ComparisonType, which indicates whether the cell values will be compared to one or two other values, or if a particular formula will be evaluated for each cell. The condition also stores a Style object that defines the formatting to be applied.

This example will show how to use a formula evaluation conditional format to apply a red font color if a cell value in a row of cells is less than the average of cell values across that row.

Note: This is equivalent to creating a conditional format in Excel with the option to ‘Use a formula to determine which cells to format’.

Defining the Formulas

The formula being evaluated to determine which cells to format is no different from the formula that would be used in Excel. If the formula evaluates to TRUE, the format will be applied to the cell.

In this example, there is a row of integer values. The conditional format should be applied if any one of those values is less than the average of the values. An example of this formula would be:

=(A3 < AVERAGE($A$3:$G$3))

Note: The reference to A3 is relative and will change for each cell in the range that the conditional format is applied to. $A$3:$G$3 is an absolute reference, and will not update for the cells in the range.

The other formula needed is the formula to define the range of cells that the conditional format will be applied to. In this case, the range formula is for cells A3 to G3 on Sheet1:

“=Sheet1!A3:G3”

For more information about defining formulas for ranges, please refer to this post.

Creating the Conditonal Format

To create and apply a conditional format with ExcelWriter:

1. Create a ConditionalFormat with Worksheet.CreateConditionalFormat
2. Create a Condition using the Excel evaluation formula in ConditionalFormat.CreateCondition
3. Define the conditional format Style
4. Define a Range to apply the conditional format to
5. Apply the ConditionalFormat to the Range

Code snippet:

//Create a conditional format
ConditionalFormat cf = wb.CreateConditionalFormat();

//Create the condition to be evaluated when determining which cells to format
Condition cond = cf.CreateCondition(Condition.Comparison.FormulaEvaluation, "=(A3 < AVERAGE($A$3:$G$3))");

//The conditional format will apply a red font color
cond.Style.Font.Color = wb.Palette.GetClosestColor(128, 0, 0);

//Creates a range and applies the conditional format to the range
Range cond_range = wb.CreateRange("=Sheet1!A3:G3");
cond_range.SetConditionalFormat(cf);

Adding Conditions to the Conditional Format

Since a ConditionalFormat object can hold up to 3 Condition objects, up to 3 different conditional format styles and evaluation rules can be applied for a single conditional format. For example, any cell values in A3:G3 that are over 100 should have a bold, green font.

Create two conditions instead of one:

Condition cond1 = cf.CreateCondition(Condition.Comparison.FormulaEvaluation, "=(A3 < AVERAGE($A$3:$G$3))");

Condition cond2 = cf.CreateCondition(Condition.Comparison.FormulaEvaluation,"=(A3 > 100");
Define styles for both of the conditions:

cond1.Style.Font.Color = wb.Palette.GetClosestColor(128, 0, 0);
cond2.Style.Font.Color = wb.Palette.GetClosestColor(0, 128, 0);
cond2.Style.Font.Bold = true;