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.

Email, RSS Follow
Email

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

Email, RSS Follow
Email

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.

Email, RSS Follow
Email

Knowledge Base, OfficeWriter, WordWriter

New line characters are not displayed in WordTemplate output

Problem

The data source for a merge field contains text with new line characters (e.g. Chr(13) & Chr(10), “/n”), but the output is not being displayed on multiple lines in OOXML documents (.DOCX, DOCM) in WordWriter 4.0.0-4.1.0.

Solution

This issue was resolved in WordWriter 4.5.0. WordWriter software updates are available for download at www.officewriter.com/product-updates.

Email, RSS Follow
Email

Excel, OfficeWriter

Pitan Pivot Mage and the Quest to Slay the Bracket Problem

All is not quiet on the home front…

“Cannot open PivotTable source file” – If you have ever encountered this error message, then you know the frustrations of The Bracket Problem. If you haven’t run into this issue before, let me paint you a picture:

There some sort of pivot report on a server, for example, a report that’s being dynamically generated for Excel using OfficeWriter. An end user decides to download the report on her work machine with IE 7. When she opens the workbook, instead of the pivot table refreshing, she gets a warning message:

Cannot open PivotTable source file: ‘C:\Users\Pitan\AppData\Local\Microsoft\Windows\Temporary Internet Files\….\ [PitanPivotReport[1].xlsx]Sheet1’.

When she tries downloading the report using Mozilla FireFox, the warning message doesn’t occur.

This, my friends, is The Bracket Problem.

What trickery is afoot? Continue reading Pitan Pivot Mage and the Quest to Slay the Bracket Problem →

Email, RSS Follow
Email

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.

Email, RSS Follow
Email

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.

Email, RSS Follow
Email

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services

OfficeWriter Designer prompts to publish during preview

Problem

The OfficeWriter Designer keeps prompting to publish the report when attempting to view the report from Excel. This happens even when the report is retrieved from the server and no changes are made to the report before trying to preview.

Solution

How View in the OfficeWriter Designer works

When the View button is clicked, the OfficeWriter Designer does several things:

  1. If the report has not been published, prompt the user to save and publish the report.
  2. If a previously published report has been changed in Excel, prompt the user to save and re-publish
  3. Make a request to Reporting Services to export/render the published report using the OfficeWriter renderer (OfficeWriter for Excel)
  4. Download a copy of the rendered report and open in Excel

In order for the OfficeWriter Designer to view a report, the most updated version of the report must be published to the server.

Designer still prompts when no changes have been made to the report

Certain Excel features, such as macros and pivottables, can be set to automatically execute or update when the workbook is opened. This is considered to be a change to the file, even though there are no visible changes to the report.

The OfficeWriter Designer uses the same logic as Excel when determining if a change has been made to the file. This means that any time Excel would prompt a user to save changes made to a file, the OfficeWriter Designer will prompt to publish the changes to the server.

For example, if a report contains a pivottable that is set to update the data when the workbook opens, Excel will interpret this a change to the file, even though it appears as though nothing has changed. In this case, the Designer will ask the user to publish the report before the View function can be run.

Email, RSS Follow
Email

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, SharePoint, SSRS, WordWriter

Error installing OfficeWriter for Reporting Services 2012 in SharePoint integrated mode

Problem

When installing OfficeWriter for SQL Server Reporting Services 2012 in SharePoint mode (SharePoint 2010), the OfficeWriter installer aborts with message:

SoftArtisans OfficeWriter Setup Wizard ended prematurely because of an error. Your system has not been modified. To install this program at a later time, run Setup Wizard again. Click the Finish button to exit the Setup Wizard.

Solution

Starting in SQL Server 2012, the SharePoint integration for Reporting Services was re-architectured: SSRS 2012 is configured as a shared service against SharePoint 2010. Configuration information is stored in a database and can be managed through SharePoint Central Administration or with Powershell.

Due to this change in architecture, the OfficeWriter installer is not currently equipped to run against SSRS 2012 in SharePoint integration mode. We will be adding support for this configuration to the OfficeWriter installer in a future release.

Update 4/11/2013: Instructions for manually installing OfficeWriter in this configuration are now available in our documentation: Manual Installation for SSRS 2012 with SharePoint 2010. The fix to the installer will be available in a soon to be released version of OfficeWriter.

Note:

This does not happen outside of SSRS 2012 with SharePoint 2010. OfficeWriter has full compatibility with SQL Server Reporting Services 2012 in native mode starting in v8.2. OfficeWriter also works with other combinations of SSRS and SharePoint.

Email, RSS Follow
Email

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, WordWriter

Designer Install Error: No Office 2007 or 2010 found

Problem

Installing the OfficeWriter Designer 8.1.0 and higher throws the error:

No Office 2007 or 2010 found. Office 2007 or later must be installed to use this product. If you are using an earlier version of Office please download the installer version 8.0 or earlier.

Solution

Office 2010 is installed

This error may occur if you have the 64-bit version of Office 2010 installed.

The OfficeWriter Designer does not currently support the 64-bit version of Office 2010. There are no immediate plans to support the 64-bit version of Office 2010 because we have not seen much demand from customers and Microsoft recommends using the 32-bit version in most cases:

IMPORTANT The 32-bit version of Office 2010 is the recommended option for most people because it prevents potential compatibility issues with other 32-bit applications, specifically third-party add-ins that are available only for 32-bit operating systems. — Choose the 32-bit or 64-bit version of Microsoft Office.

The OfficeWriter Designer is supported on 64-bit operating systems, but requires a 32-bit version of Office. If no 32-bit version of Office 2007 or 2010 is detected on the machine, the installer will throw the error above.

Office 2003 is installed

Starting in OfficeWriter 8.1, the OfficeWriter Designer is only supported in Office 2007 and 2010. If you need to use the Designer in an earlier version of Office, please download OfficeWriter Designer 8.0 or earlier.

No Office is installed

The OfficeWriter Designer is an Office add-in and requires a 32-bit version of Office to be installed.

Email, RSS Follow
Email

Blogged