Category Archives: Knowledge Base

ExcelWriter, Knowledge Base

Modifying an existing named range with ExcelWriter

Problem

If a workbook created with Excel or ExcelWriter contains a named range, can the named range be modified (i.e. modify the areas it references)?

A customer asked about this specific scenario:

For example, when the document was first created a named range called “DataRange” was created in the workbook referencing cells A1:B9 on sheet1. The end user has been instructed to create pivots and charts based on this named range.

Then at a later date when new data is available I want to refresh the data in that spreadsheet and refresh the named range. If the number of rows of data has changed, say going from 9 records to 12, how can I update the existing named range to include the new area and thus have all the pivots/charts that were created by the end user automatically linked to the new area?

Solution

Currently there is not a way to modify the area referenced by a named range using the ExcelApplication object. There is a method Range.JoinRange that can add a new area to a named range.

In the scenario listed above, this won’t work for the customer because Excel does not let you specify a formula containing multiple areas as the data source for a pivot table. Doing so would result in a “Reference is not valid” error.

In Excel, if you insert new rows or delete rows inside an existing range, the range would be automatically adjusted; charts and pivot tables which refer to this range are automatically updated. We can follow this approach to update named ranges referenced by pivot tables.

Inserting Data Rows with ExcelApplication

  1. Open the Excel file with the ExcelApplication object.
  2. Retrieve the existing named range. using Workbook.GetNamedRange or Worksheet.GetNamedRanged.
  3. Insert enough rows to accommodate new data within the named range using Worksheet.InsertRows or Worksheet.InsertRow.
  4. Insert data into the new rows by setting Cell.Value or using Worksheet.ImportData.

Inserting Data Rows with ExcelTemplate

  1. Create ExcelWriter data markers within the existing named range (e.g. use ExcelApplication to write Cell.Value = “%%=DataSource.ColumnName”).
  2. Use ExcelTemplate to populate the data markers.

Deleting Data Rows

To delete existing rows, you can use the Worksheet.DeleteRows or Worksheet.DeleteRow method.

Customer Example

In the customer’s scenario, the new rows need to be inserted inside the named range (not at the beginning or end). Also, the pivot table must be set to “Refresh on open”, which is an option under pivot table options > data.

Knowledge Base, WordWriter

How to insert a line break using WordTemplate

Problem

To insert a line break in Word, you can press SHIFT+ENTER. This post covers how to insert a Word line break by injecting a newline character into the string of data that will populate a merge field.

Note: You cannot insert a paragraph break by injecting a character into your data. To start a new paragraph, you should use a separate merge field or use the WordApplication class to programmatically modify the document.

Solution

In this example, the template has one merge field, called “MyParagraph.”

The following code populates the merge field with two lines of text:

 WordTemplate wt = new WordTemplate(); wt.Open(Server.MapPath("./template.docx")); string[] fields = {"MyParagraph"}; object[] data = {"This is the first line." + '\n' + "This is the second line."}; wt.SetDataSource(data, fields); wt.Process(); wt.Save(Page.Response, "Output.docx", true);

The populated document will contain a line break wherever ‘\n’ was inserted:

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 1608: Unable to Create InstallDrive Instance. Return code -2147024891

Problem

When attempting to run the installer for OfficeWriter 4.x or below, InstallShield returns the error:

1608: Unable to Create InstallDriver Instance. Return code -2147024891.

This error has been known to occur for some users when installing or uninstalling OfficeWriter. According to InstallShield, “Error 1608 is a very system specific issue and is often caused by limited system privileges, incorrect DCOM settings, or another malfunction on the computer.”

Solution

In most cases, the instructions in the following InstallShield article resolve this problem. For many of our customers, this error has been resolved by simply following the instructions regarding DCOM Settings (section A).

http://consumer.installshield.com/kb.asp?id=Q108440 (note: installshield’s website can be very slow sometimes.)

If after trying all of the suggestions in the InstallSheild article, you are unable to install or uninstall OfficeWriter using the automatic installer, you will need to perform a manual installation and/or uninstallation.

Instructions for uninstalling and installing OfficeWriter can be found in our documentation. Although the instructions are labeled for ExcelWriter, the same steps apply to installing WordWriter.

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.