Tag Archives: knowledge base

ExcelWriter, Knowledge Base

How to make the pie chart display larger

Problem

There is a known issue with pie charts that are created with ExcelApplication in the binary XLS file format. Pie chart display was improved in ExcelWriter 8 for the XLSX file format as part of the ExcelApplication OOXML implementation.

This post addresses how to improve the size of a pie chart in an XLS file.

Solution

There are two approaches for increasing the size of a pie chart:

Approach Before After
Increase the size of the chart while maintaining the proportions between the pie graphic and the chart frame. This requires making the chart (as a whole) larger.
Increase the size of the chart in relation to the chart frame. This requires making adjustments to the chart’s plot area.

Option 1: Increase the pie chart and maintain current proportions

If the pie chart graphic displays too small, increase the chart’s area by providing more room for it in Add method of the Charts object. The size of the pie graphic in the chart is directly related to the amount of space provided for the chart.

To provide more area for the chart, after adding the chart with the AddChart method:

  1. Make the chart wider by increasing the Chart.Width property
  2. Make the chart taller by increasing the Chart.Height property

Option 2: Change the proportion of the chart size in relation to the chart frame

Use the PlotArea‘s Height and Width properties. The values you can provide range from 1 to 4000. 4000 makes the chart take up the maximum area possible. In many cases, this value will be too large. For the area provided in the above code, 2500 is a good value for the PlotArea Width and height.

Knowledge Base, WordWriter

How to get a list of bookmarks from a Word document

Problem

You want to get a list of all the bookmarks in a document using WordWriter.

Solution

WordTemplate

The WordTemplate.Boomarks will return the names of all the bookmarks in a WordWriter template. This can be useful for binding data to the template with SetRepeatBlock, which requires the name of the bookmark for the repeatblock.

WordApplication

Use the GetElements method to retrieve all the elements of a particular type. In this case, the Element.Type is “Bookmark”.

For example, to retreive an array of all the bookmarks in a document:

Element[] eArray = document.GetElements(Element.Type.Bookmark);
//Get a handle on the first bookmark in the document
Bookmark bookmrk = (Bookmark)eArray[0];
If you need to retrieve a particular bookmark, use Document.GetBookmark(string), which returns the Bookmark object with the specified name.

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Unexpected behavior of the CONTINUE modifier in version 7.1 and below

Problem

Using the CONTINUE modifier in a template to generate a report may show an unexpected behavior. Breaking down the different scenarios, we find that the behavior changes with data source type and amount of data available.

As described in our documentation, the Continue modifier enables data from a single data source to span multiple worksheets using the ExcelTemplate object. Without the modifier, if the data source for the ExcelTemplate uses a forward-only cursor, such as with a DataReader, the second worksheet with the same data marker will automatically start from the next row of data. If the data source is scrollable, however, the second worksheet with the same data marker will rewind to the first row of data. The Continue modifier tells the ExcelTemplate object to start from the next row even if the data source is scrollable. More about data markers and modifiers here.

In version 7.1 and below, the above description is accurate if there is enough data to reach the last data marker with a continue modifier before the data source has exhausted. However, if the data source gets exhausted prior to that point, the ExcelTemplate object will try to rewind the data source which may result in an exception thrown (for forward-only data sources) or duplication of data (for scrollable data sources).

Here are the two specific cases:

  1. When using a forward-only data source, the ExcelTemplate object will throw a runtime exception on the Process method: “Exhausted Data Markers” because it has encountered a new data marker but there is no more data available.
  2. When using a scrollable data source, the ExcelTemplate object will rewind the data source and will result in duplicated data on both worksheets.

Solution

Option 1: Upgrade to ExcelWriter 7.5 or later (recommended)

This issue was fixed in version 7.5 by applying more reasonable behaviors to the edge cases. With the new behavior, if the data has exhausted before reaching all data markers with a CONTINUE modifier, ExcelTemplate will not attempt to rewind the data source and will simply remove the extra data markers (similar to the behavior of the “optional” modifier).

Here is a table comparing the old and new behaviors in all scenarios:

No modifiers (CONTINUE) (OPTIONAL) (CONTINUE,OPTIONAL)
Old New Old New Old New Old New
Forward-only data source that still has data Continue Continue Continue Continue Continue Continue Continue Continue
Forward-only data source with exhausted data Exception Exception Exception Empty Exception Empty Exception Empty
Scrollable data source with that still has data Rewind Rewind Continue Continue Rewind Rewind Continue Continue
Scrollable data source with exhausted data Rewind Rewind Rewind Empty Rewind Rewind Rewind Empty

Option 2: Divide the data

If the template file cannot be modified at runtime, divide the data according to the number of sheets containing data markers with the CONTINUE modifier. This can be done by adjusting the MaxRows setting so there will be data available to the last of these data markers.

Option 3: Use ExcelApplication

Alternatively, if we have the ExcelApplication object available (included in OfficeWriter Enterprise Edition) we can modify the template to contain the correct number of sheets in the workbook. This way we can keep MaxRows a constant. It is possible to pass a template document between the application object and the template object in memory.

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Error: General license key exception: No valid license key found

Problem

When trying to create an instance of the ExcelApplication or ExcelTemplate objects, the following error is returned:

General license key exception: No valid license key found.

This can happen if you are trying to use an OfficeWriter dll that is not the same version as the license key in the registry.

A common scenario where this occurs, is upgrading to a new version of OfficeWriter. When the old version of OfficeWriter is uninstalled and a new version is installed, the dlls in your projects are not automatically updated. This means that your projects may have references to the old dlls (say v4), but the license key in the registry is for the new version (v8).

Update April 4, 2013: If you are running a 32-bit application with OfficeWriter on a 64-bit operating system, you may encounter this error message, even though the license key is in the registry and matches the version of OfficeWriter being used. If you experience this issue, please contact us.

Solution

There are 2 options:

  1. Update the references in your projects to point to the new OfficeWriter dlls. (Dlls can be found under Program Files\SoftArtisans\OfficeWriter\bin).
  2. Add the license key for the old version with the License Key Manager to run both versions of OfficeWriter. For more information about running different versions of OfficeWriter side-by-side, refer to this post.

To add a license key with the License Key Manager:

  1. Locate LicenseManager.exe either from Start > Programs > SoftArtisans > OfficeWriter or from Program Files\SoftArtisans\OfficeWriter.
  2. If you are installing on Windows 7 or Windows Server 2008, right click LicenseManager.exe and select ‘Run as Administrator’. Otherwise, double-click to run the installer.
  3. Click the Add/Update Key button to add the license key.
  4. After the license key is added, you should see license keys for the old and new version registered.

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Install Error: Unable to save file: C:\Windows\Download installations{guid}\OfficeWriter 4.x.msi

Problem

You recieve an error message when running the OfficeWriter installer on Windows 2008 R2 or Windows 7:

Unable to save file: C:\Windows\Download Installations{guid}\OfficeWriter 4.1.0.msi. The system cannot find the path specified.

This error message may vary slightly depending on your system.

Solution

The installer must be run as an administrator. However, being logged in as an administrator does not cause the installer to be run with administrator privileges. You must explictly run it as an administrator.

To run the installer as an administrator, right click the installer and select “Run as Administrator.”

You can also run the installer as the administrator through the Windows Power Shell:

  1. Run Windows Power Shell as an administrator
  2. Within Windows Power Shell, navigate to the directory containing the OfficeWriter installer.
  3. Use the command .\OfficeWriter-Installer-4.1.0.exe. Note that the name of the installer file used in the command will vary depending on the version of OfficeWriter you are installing.

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Install Error 1316 – A network error occurred when attempting to read from the file

Problem

When running the OfficeWriter installer to upgrade to OfficeWriter 4.x or below from an earlier version of OfficeWriter the following error may occur:

Error 1316 – A network error occurred when attempting to read from the file C:\WINNT\Downloaded Installations{EBF98E9A-3A6C-4B10-AB6B-3D9A746A5C35}\SoftArtisans OfficeWriter [version number].msi

Solution

It is not possible to install OfficeWriter 4.x or earlier over a previous version with the automatic installer. The previous version must be uninstalled first. This is a known issue with InstallShield, the component used in the OfficeWriter installer.

Note: This only applies to installers for OfficeWriter 4.x and below. Starting in OfficeWriter 8.0, the installer no longer uses InstallShield so the issue no longer applies. However, you should uninstall the previous version of OfficeWriter before installing OfficeWriter 8.x to ensure clean upgrades.

Instructions for removing older versions of OfficeWriter can be found in our documentation:

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Install Error: 1603 Fatal error during installation

Problem

Installing OfficeWriter 3.9.1 may fail on Windows Vista or Windows Server 2008. The installer appears to proceed normally, but eventually rolls back and displays the following error message:

Error: -1603 Fatal error during installation. Consult Windows Installer Help (Msi.chm) or MSDN for more information.

The installation failure is due to a problem in InstallShield, a software tool used to build the OfficeWriter installer. The problem occurs in an InstallShield method that interfaces with IIS 7, which comes with Windows Vista and Windows Server 2008. OfficeWriter installer for versions 4.6.0 and earlier attempt to interface with IIS to create a new website for sample applications during installation. Because of the InstallShield problem on Windows Vista and Windows Server 2008, the installer fails and subsequently rolls back.

Solution

Option 1: Upgrade to OfficeWriter 3.9.2 or later

Upgrade to a newer version of OfficeWriter. Subsequent to the 3.9.1 release, the OfficeWriter installer is built with a patched version of InstallShield which fixes the problem described above. We recommend that, whenever possible, customers upgrade to the newest release of OfficeWriter to take advantage of the latest bug fixes and new features.

Option 2: Install OfficeWriter 3.9.1 without the samples

Install OfficeWriter 3.9.1 without the samples. Because the error occurs when the installer attempts to create a new website for the samples, not installing the samples would allow the installer to succeed. On the Setup Wizard dialog box which allows selection of the features to install, deselect the “OfficeWriter Samples” option.

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, WordWriter

Designer Install Error: This software requires Excel 97 and/or Word 97 (or higher)

Problem

The installer for the OfficeWriter Designer v8.0 and below throws the following error:

This software requires Excel 97 and/or Word 97 (or higher), with Visual Basic for Applications (VBA) and MSQuery installed. This system does not appear to have Excel or Word installed. Please install Office before continuing.

Solution

Office 2010 is installed

The OfficeWriter Designer v8.0 and below is not supported in Office 2010. Please install v8.1.1 or higher for Office 2010.

No Office is installed

The OfficeWriter Designer is an Office Add-In and requires a version of Office to be installed to use.

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.