Tag Archives: OfficeWriter

Knowledge Base, OfficeWriter, WordWriter

WordWriter generated document opens in protected mode in Word 2010

Problem

When a report rendered with WordWriter is opened in Word 2010, the document opens in “Protected Mode” (red warning banner). This might also happen in Excel 2010.

Solution

In Office 2010, Microsoft introduced a security feature which can cause files to be opened in Protected Mode with a red warning banner that locks the file for editing. The Protected Mode is activated when Office detects something in the file format that is not consistent with the security definition.

Unfortunately, since we do not have access to the definitions that the security feature uses to check files, we cannot predetermine what will cause the Protected Mode to activate. We have already addressed a number of items that can cause files to open in Protected Mode, but we are unable to know all the possible causes.

If you are using the latest version of the product and you encounter a file that opens in Protected Mode contact us.

ExcelWriter, Knowledge Base, OfficeWriter

ExcelWriter Error: This file does not appear to be a valid Excel 97 or later binary file

Problem

When trying to open an XLSX or XLSM file with ExcelApplication, you get the following error message:

This file does not appear to be a valid Excel 97 or later binary file. Please try opening in Excel 97 later by saving as “Microsoft Excel Workbook (*.xls)”

Solution

Support for the Office 2007/2010 file formats (XLSX, XLSM) was introduced for ExcelApplication in ExcelWriter 8. Earlier versions with throw the above exception when attempting to open an XLSX file with ExcelApplication.

Sign in to download product updates. Make sure to follow the instructions for installing a new version of the product.

ExcelWriter, Knowledge Base, OfficeWriter

Shapes and PivotCharts stretch when placed next to a PivotTable

Problem

If a pivotchart or drawing shape is placed next to a pivottable, it stretched over many rows in the output file. Also, if a pivotchart or drawing shape is placed beneath a pivottable, it gets pushed down the worksheet.

Solution

The issue of shape behavior was addressed in OfficeWriter 4.6.0. Download the latest version of OfficeWriter.

ExcelWriter, Knowledge Base, OfficeWriter

ExcelWriter leaves empty space for a chart title

Problem

When using ExcelApplication to create or modify charts, there appears to be an empty space for the chart title even though one wasn’t specified or the chart title is set to an empty string.

Solution

ExcelWriter will leave space for a chart title if any of the Chart.Title properties are set; this includes setting Chart.Title.Text to an empty string. This is because ExcelWriter assumes that if Chart.Title properties are being set, then there will be a chart title.

This mimics Excel’s behavior, as chart title properties cannot be accessed or modified unless there is a non-empty chart title.

To avoid having extra space for the title, only set Chart.Title properties when Chart.Title.Text is set to a non-empty value (i.e. you intend for the chart to have a title).

ExcelWriter, Knowledge Base, OfficeWriter

Exception: ‘[character]’, hexidecimal value 0x[value], is an invalid character

Problem

When running an Excel report rendered by ExcelWriter’s ExcelTemplate object, the report will throw the following eception if the data set contains certain special characters:

Unhandled Exception: SoftArtisans.OfficeWriter.ExcelWriter.SAException: ‘[character], hexadecimal value 0x[value], is an invalid character. at SoftArtisans.OfficeWriter.ExcelWriter.ExcelTemplate.Process()

This exception occurs if:

  • Data is imported into an Excel file using ExcelWriter
  • Files are .XLSX (Office 2007) or .XLSM (Office 2007 with macros enabled)
  • Data set contains a character that is not valid in XML

Note: Support for OOXML files was introduced in OfficeWriter 4.0, so this issue only occurs in OfficeWriter 4.0-4.6.0 (ExcleWriter 7.0 – 7.6.0).

Solution

Option 1: Upgrade to OfficeWriter 4.6.1 or later (recommended)

This issue was addressed in OfficeWriter 4.6.1 (ExcelWriter 7.6.1). As of version 7.6.1, ExelWriter correctly preserves characters that are not valid in XML, so they will appear in output.

Product updates for OfficeWriter can be downloaded at www.officewriter.com/product-updates.

Note: Make sure to uninstall the old version of OfficeWriter before installing a new version.

If you are experiencing this issue with OfficeWriter for SQL Server Reporting Services and need to upgrade the server-side installation of OfficeWriter, we generally recommend upgrading the client-side installation of the OfficeWriter Designer as well.

Option 2: Scrub data of invalid XML characters

There are certain ranges of characters that are not valid XML. The XML specification has defined which ranges of characters are supported in XML files. These files cannot be written into XML files, which includes the OOXML file formats. Excel handles the special characters by encoding them before they are written to file.

Since ExcelWriter 7.0-7.6.0 does not handle these characters properly, the other solution is to remove any characters that are not valid XML, if the characters don’t need to be preserved.

ExcelWriter, Knowledge Base, OfficeWriter

ExcelWriter Error: Items in the array exceed 256 characters

Problem

When you specify List data validation in Excel with a sequence of comma-separated values, Excel prevents you from entering more than 255 characters, including commas. Analogously, when you create List data validation in ExcelWriter with an array of values, the CreateDataValidation method throws the exception: Items in the array exceed 256 characters, if the combined length of all of the elements in the array is longer than 255 characters. The limit is actually 255 rather than 256 as stated in the exception message. In practice, the actual limit would be lower if there are multiple elements in the array, as ExcelWriter has to insert tokens to demarcate individual values.

Solution

In order to prevent the exception, you can specify the list as a formula pointing to a range containing the desired values rather than as an array. This approach parallels Excel’s second way to create List data validation. If it’s desirable to hide the validation values, you can place the range on a non-visible area of the worksheet, or on a separate hidden worksheet.

 // Create validation object based on desired cell range 
DataValidation dv = workbook.CreateDataValidation(DataValidation.ValidationType.List, "=Sheet1!A1:A4"); 

// Alternatively, use an existing named range 
DataValidation dv2 = workbook.CreateDataValidation(DataValidation.ValidationType.List, "=ValidationRange"); 

// Set cell B1 to use validation 
worksheet.Cells["B1"].DataValidation = dv;

For more information about creating data validations, please refer to our documentation

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Data not imported when using a recordset for a data source

Problem

When using a recordset as a datasource with the ExcelWriter template object, the cells are not populated with data. This typically happens when the recordset is at EOF due to manipulation in the script. When the template object’s .Process() method is called, the data binding occurs.

Solution

Before calling .Process(), you should move to the first record in the recordset with this command:

 oRS.MoveFirst()

That way, the recordset will be passed properly to the object.

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, WordWriter

Reporting Services Error: Report must be created with OfficeWriter Designer version 3.8.1.85 or later

Problem

When a report is exported from SSRS 2008 after upgrading to OfficeWriter 4.x, the following error message appears:

OfficeWriter report was deployed to Microsoft SQL Server 2008 must be created with OfficeWriter Designer version 3.8.1.85 or later. This report was created with version [version number] and must be run on SQL Server 2005 or earlier.

Solution

There are two known causes for this message, depending on the [verison number] of the Designer:

Designer version 3.8.x – 3.9.x

Full support for SQL Server Reporting Services 2008 was officially introduced in OfficeWriter 4.x. Exporting reports that were designed with an earlier version of the OfficeWriter Designer to SSRS 2008 will result in the error message above.

This message indicates that the report needs to be upgraded with a later version of the Designer.

To upgrade a report:

  1. Follow the instructions for removing the old version of the Designer and installing the new version of the Designer.
  2. Open the report in the new version of the Designer.
  3. Save or publish the report from the OfficeWriter Designer toolbar.

This will make several changes to the RDL to make it compatible with SSRS 2008.

Designer version 4.x

This is a known issue that occurs when the following conditions are met:

  • The report was created in Visual Studio 2008 for SQL Server 2008
  • OfficeWriter Designer version is 4.5.0
  • OfficeWriter v4.1.0 is deployed on the report server

The solution is to upgrade the OfficeWriter on the server from v4.1.0 to v4.5.0 or later.

NOTE: We generally recommend that the version of OfficeWriter on the server matches the version of the OfficeWriter Designer.

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, WordWriter

OfficeWriter Designer Warning: Feature is only available in Enterprise Edition

Problem

OfficeWriter Enterprise Edition is installed on the server, but the warning message: WARNING! This feature is only available in Enterprise Edition of OfficeWriter pops up when using certain features of the Designer.

Solution

This warning message displays when using any features that are only supported with Enterprise Edition of OfficeWriter, such as Reporting Services formulas. This is because the OfficeWriter Designer does not have the capability to detect what version of OfficeWriter is installed in Reporting Services.

The common scenario is for OfficeWriter for Reporting Services to be installed on a server, with the OfficeWriter Designer distributed to end-users, who design reports and upload them to the server. The Designer has no means of determining if the OfficeWriter on the server is Enterprise or Standard Edition, but end-users should be aware that if the OfficeWriter on the server is not Enterprise Edition and they use Enterprise-only features, their report may not render properly.

The OfficeWriter Designer can be used on the same machine that has OfficeWriter for Reporting Services installed, such as on a development machine, but the Designer does not require OfficeWriter to be installed. This is why that alert displays regardless of what version of OfficeWriter is installed.

To avoid seeing the warning message when using features that are only available in OfficeWriter Enterprise Edition, un-check ‘Show this warning when Enterprise Edition feature is used.’

If only Standard Edition is installed on the server, to avoid the warning message and hide Enterprise Edition-only features, click ‘Hide EE Features’ button at the bottom of the pop-up window.