When working with pivot charts in Excel 2007, the pivot table looks fine, but the pivot chart comes out empty in the output file.
The issue of pivot charts not displaying data in Excel 2007 was addressed in OfficeWriter 4.5.1. Download the latest version of OfficeWriter.
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.
The issue of shape behavior was addressed in OfficeWriter 4.6.0. Download the latest version of OfficeWriter.
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.
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).
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:
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).
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.
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.
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.
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
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.
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.
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.
This issue was resolved in WordWriter 4.5.0. WordWriter software updates are available for download at www.officewriter.com/product-updates.
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.
There are two known causes for this message, depending on the [verison number] of the Designer:
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:
This will make several changes to the RDL to make it compatible with SSRS 2008.
This is a known issue that occurs when the following conditions are met:
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.
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.
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.
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.
When the View button is clicked, the OfficeWriter Designer does several things:
In order for the OfficeWriter Designer to view a report, the most updated version of the report must be published to the server.
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.