Category Archives: ExcelWriter

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services

Designer Error: Type Mismatch

Problem

Selecting Open Report in the OfficeWriter Designer toolbar in Excel displays a type mismatch error.

The type mismatch error occurs when a client machine is configured with regional settings that cause Excel to return it’s version number in a format the designer does not expect. For example, French regional settings by default use the space (” “) digit grouping symbol.

Solution

Option 1: Upgrade (recommended)

This issue has been fixed in the OfficeWriter Designer version 4.6.1 and later without the need to adjust regional settings.

Option 2: Adjust the current regional settings for the OS

Navigate to the systems control panel > regional settings. Depending on your operating system this may appear as:

  • Region and Language
  • Date, Time, Language, and Regional Options
  • Select either Customize or Advanced settings

Change the Digit grouping symbol to “.” excluding quotes, or other symbol that does not cause an error. Then apply changes.

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Error: Could not load file or assembly ‘vsjlib, Version=1.0.3300.0’

Problem

After migrating to the .NET 4.0 Framework, an OfficeWriter application throws the error:

Could not load file or assembly ‘vjslib, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

Solution

OfficeWriter 3.x relies on the J# runtime, so users who have tried using OfficeWriter 3.x with Visual Studio 2010 and the .NET 4.0 Framework have run into issues. This is due to the fact that Microsoft does not have a version of the J# runtime for .NET 4.0.

There are two possible solutions:

#1: Update to OfficeWriter 4.x or later (recommended)

The J# runtime dependency was removed in OfficeWriter 4.0.0, so upgrading to OfficeWriter 4.x or later resolves the issue. We recommend upgrading because all new features and bug fixes are released in the latest version of OfficeWriter.

#2: Downgrade to an earlier version of .NET

For .NET versions 2.0 or 3.0, Microsoft has a J# redistributable package available for 32-bit and 64-bit systems. Microsoft also has a redistributable package of J# for .NET Version 1.1.

NOTE: OfficeWriter 3.x is no longer supported. OfficeWriter Supported Versions Policy

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, SQL Server, SSRS, WordWriter

Reporting Services Error: Could not load file or assembly ‘x’ or one of its dependencies

Problem

You want to use a newer version of ExcelWriter or WordWriter with OfficeWriter’s Reporting Services integration. A common scenario is upgrading to an intermediate build of ExcelWriter or WordWriter which fixes a bug or adds a new feature.

To provide context, OfficeWriter’s Reporting Services integration consists of the following components:

  • ExcelWriter (SoftArtisans.OfficeWriter.ExcelWriter.dll)
  • WordWriter (SoftArtisans.OfficeWriter.WordWriter.dll)
  • A custom rendering extension (SoftArtisans.OfficeWriter.RS2008.dll for SQL Server Reporting Services 2008)

When upgrading to a different version of ExcelWriter or WordWriter, normally you also have to upgrade to the matching version of the custom rendering extension. This is because the custom rendering extension is built against a specific version of ExcelWriter and WordWriter and only works with that version. If the version of ExcelWriter or WordWriter does not match the version of the custom rendering extension, you will get this error when exporting the report using ExcelWriter or WordWriter:

Could not load file or assembly ‘x’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference.

If you are performing a regular upgrade in which you replace the custom rendering extension DLL as well as the ExcelWriter or WordWriter DLL, use the manual installation instructions as a guideline.

However, if there are no changes to the custom rendering extension, it is convenient to use the same custom rendering extension DLL and replace just the ExcelWriter or WordWriter DLL. This approach also allows you to easy test any version of the ExcelWriter or WordWriter DLL. In this case, follow the below instructions.

Solution

To upgrade the ExceWriter or WordWriter DLL without having to replace the custom rendering extension, you can create an assembly binding redirect so that request for the old DLL are redirected to the new DLL.

The solution is also applicable to any ASP.NET application for which you want to replace a dependent assembly with another version. Use the instructions below as guidelines for making modifications to the application’s web.config.

The process for creating an assembly binding redirect is slightly different for ExcelWriter and WordWriter, because by default WordWriter is installed into the Global Assembly Cache (GAC).

Upgrading ExcelWriter

1. Navigate to the Reporting Services directory, typically `DRIVE:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services’

2. Copy SoftArtisans.OfficeWriter.ExcelWriter.dll to ReportServer\bin, replacing the existing DLL

3. Determine the version of the new DLL; for example, 7.5.1.2770

4. Edit ReportServer\web.config and add the following section under the section:

XML Config Script

 <configuration>     <runtime>         <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">             <dependentAssembly>                 <assemblyIdentity name="SoftArtisans.OfficeWriter.ExcelWriter" publicKeyToken="f593502af6ee46ae" culture="neutral" />                 <bindingRedirect oldVersion="7.5.0.1-7.5.1.9999" newVersion="7.5.1.2770"/>             </dependentAssembly>         </assemblyBinding>     </runtime> </configuration>

Note: If there is an existing section, add only the ” section inside it, so that there is only one assembly binding definition.

In the example above, set the newVersion attribute to the version of the new DLL. Set the oldVersion attribute to a single version or a range of versions which are to be mapped to the new version.

5. Perform the same steps for ReportServer\bin\ReportingServicesService.exe.config

6. Restart the Report Server

Upgrading WordWriter

If WordWriter is installed in the GAC, as done by the installer, you must also install the new DLL into the GAC. Because the GAC supports multiple versions of a DLL, you do not have to uninstall the old DLL. However, by using assembly binding redirect, you can force Reporting Services to use the new DLL. Follow the procedure below to upgrade WordWriter:

1. Install the new DLL into the GAC using, for example, the gacutil tool.

2. Navigate to the Reporting Services directory, typically ‘DRIVE:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services’ Copy SoftArtisans.OfficeWriter.WordWriter.dll to ReportServer\bin

3. Determine the version of the new DLL; for example, 4.5.1.1648

4. Edit ReportServer\web.config and add the following section under the section:

XML Config Script

 <configuration>     <runtime>         <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">             <dependentAssembly>                 <assemblyIdentity name="SoftArtisans.OfficeWriter.WordWriter" publicKeyToken="f593502af6ee46ae" culture="neutral" />                 <bindingRedirect oldVersion="4.5.0.1-4.5.1.9999" newVersion="4.5.0.1567"/>             </dependentAssembly>         </assemblyBinding>     </runtime> </configuration>

Note: If there is an existing section, add only the section inside it, so that there is only one assembly binding definition.

In the example above, set the newVersion attribute to the version of the new DLL. Set the oldVersion attribute to a single version or a range of versions which are to be mapped to the new version.

5. Perform the same steps for ReportServer\bin\ReportingServicesService.exe.config

6. Restart the Report Server

If WordWriter is not installed in the GAC, skip step 1 and follow steps 2-7 in the above procedure.

ExcelWriter, Knowledge Base, OfficeWriter

Trendline Label doesn’t show Equation/R-squared value

Problem

It is possible, in Excel, to add a label to a trendline of a chart that shows the Equation or the R-squared value of the trendline like in the image below:

Trendlines can also be created as objects in ExcelApplication through the Series of a Chart. The Trendline object has the properties to ShowEquation and ShowRSquaredValue.

Setting these properties to true does not seem to have any effect and the label is not shown. However, if you open, with ExcelApplication, an existing workbook which already has a Trendline label these properties will work properly. From that point on you can programmatically change the label as you like using the properties.

Solution

This issue has been resolved for charts in the OOXML file format (XLSX, XLSM) as part of implementing OOXML support for ExcelApplication in OfficeWriter v8.0.

This issue persists for the BIFF (XLS) file format. It can be worked around in the following manner:

1. Create a new chart in Excel and add a trendline.

2. Add the Equation or the R-Square: Select the trendline then right-click the trendline or choose Format>Selected Trendline from the menu.

3. Now simply open the workbook with ExcelApplication.Open, retrieve the trendline and program it any way you like (Including changing the trendline regression type or value or show/hide the Equation or R-Squared value):

Trendline trend = oSeries.Trendlines[0]; //now this will work trend.ShowEquation = true;

ExcelWriter, Knowledge Base, OfficeWriter

Error: Cannot open PivotTable source file ‘[filename[x].xls]SourceData’

Problem

When opening an Excel file containing a pivot table that was streamed to an IE client, Excel will throw the following error message:

Cannot open PivotTable source file ‘[filename[1].xls]SourceData’

This is caused by IE caching the streamed file with a temporary name containing brackets (e.g. filename[1].xlsx). Since Excel does not allow pivot table data sources to contain brackets, when the cached file is opened, the pivot tables try to refresh using the temporary file name.

Solution

There are several ways to work around this:

  • Use a non-IE browser, such as Firefox or Chrome
  • Use IE 9 or later – this appears to have been resolved in the newer versions of IE
  • Save the file to disk before opening it to avoid opening the file with a temporary name
  • Stream the file inline instead of an attachment. This will open the file in IE’s Excel plugin instead of downloading it. If you are using IE 7 or later, you will need to configure the registry on the client to use this option.
  • Add Excel macros to the report to dynamically rename the PivotTable to exclude the bracket and then refresh the PivotTable. An example of these macros is available in this blog post.

Note: This is not an issue with the OfficeWriter product. This behavior occurs anytime someone attempts to download and open an Excel workbook with a PivotTable directly from certain versions of IE.

We have made these solutions available online because our customers have found the information helpful. However, these solutions are not covered under OfficeWriter product support. If you experience issues implementing any of these solutions, we encourage you to reach out to the appropriate vendor (i.e. Mozilla for Firefox, Google for Chrome, and Microsoft for IE 9 (or later) or to configure the in-line plugin).

In particular, if you encounter difficulties with the macro solution, feel free to leave a reply on the blog post so that the author of the post can address your concerns.

ExcelWriter, Knowledge Base, OfficeWriter

FitToPagesTall and FitToPagesWide settings do not take effect

Problem

FitToPagesTall and FitToPagesWide settings do not take effect unless Zoom is explicitly set to false.

Solution

FitToPagesTall, FitToPagesWide and Zoom are properties of the PageSetup object of the ExcelWriter Application object.

The Page Setup dialog in Microsoft Excel allows you to select one of the following two options:

  1. Adjusting the worksheet so that it fits to the dimension of “x” number of pages width-wise and/or length-wise.
  2. Adjusting the worksheet so that it prints to a certain percentage of the original size, ranging between 10 and 400 percent.

The equivalent properties in ExcelWriter are:

The Zoom property has a default value of “True” and “100” percent. This means that even if the Zoom is not explicitly set in your code, these values will be assumed. Any values provided for FitToPagesTall or FitToPagesWide will be ignored. If you want to use these properties, you must explicitly change the value of Zoom to “False”:

worksheet.PageSetup.Zoom = false; worksheet.PageSetup.FitToPagesTall = 2; worksheet.PageSetup.FitToPagesWide = 2;

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Files generated by OfficeWriter do not open in the browser

Problem

When OpenInBrowser in the Save method is set to true, the Excel spreadsheet or Word document does not open in the Internet Explorer Browser window as expected. Instead, the spreadsheet opens in Excel or the document opens in Word.

Solution

Using a browser other than Internet Explorer

The plugin that is used to display Office files in the browser is only available in Internet Explorer, so other browsers cannot display Excel or Word files within the browser. However, you can set whether you want to always open the files in Excel or Word, or save them to disk by default.

Firefox:

When a user is sent an Excel or Word file, a dialog box prompting to Open or Save the file pops up. The user can choose an option and then check the “Do this automatically for files like this from now on” box to have it remember this choice.

Chrome:

Chrome defaults to saving the file to disk. If you want to open a file type automatically in Excel or Word, download a file of that type. Once the file is downloaded, right-click the file in the download bar at the bottom of the browser. Select “Always open files of this type”.

Using Internet Explorer

It is likely that the “Browse in Same Window” is disabled for the file types (XLS, XLSX, XLSM, DOC, DOCX, DOCM).

Previously, this option was available under Folder Options > File Types > Select a file type > Check Browse in the same Window.

Starting in Windows Vista, this option is no longer exposed and the registry needs to be modified to enable certain file types to open in the browser. This Microsoft Knowledge Base article explains how to enable the behavior by modifying the registry: A new window opens when you try to view a 2007 Microsoft Office program document in Windows Internet Explorer 7 or Internet Explorer 8.

Excel, ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Excel or Word file generated by OfficeWriter opens in protected view (yellow)

Problem

A report generated using ExcelWriter or WordWriter opens and there is a yellow warning banner saying that the file has been opened in Protected View.

Solution

Starting in Office 2010, Microsoft introduced a security feature that scans files before opening them in Excel or Word. If there is something wrong with the file format definition, it will open in Protected Mode with a red warning banner.

One of the common scenarios for opening a file in Protected View is opening the file from an unsafe location, such as the Temporary Internet Files folder. This means that if the file is streamed to an end-user, it will open in protected view.

For more information about Protected View settings in Office 2010, please refer to this Microsoft TechNet article on how to configure Protected View settings in Office 2010.

ExcelWriter, OfficeWriter, Reviews, SSRS, WordWriter

SQLServer.com’s Jonathan Spink Reviews OfficeWriter for SQL Server Reporting Services

The following is an excerpt from SQLServer.com’s Jonathan Spink as he reviews OfficeWriter’s integration with SQL Server Reporting Services (SSRS). Read the full review here.

“…The idea behind OfficeWriter is to extend the functionality of software users are already familiar with, i.e. Excel and Word, so they’re able to both acquire the data they need and arrange it into a useful form. While OfficeWriter does come in two more specific forms, ExcelWriter and WordWriter, this review looks at the version developed for use with SQL Server Reporting Services (SSRS), available in both 2000 and 2005 incarnations.

In order to take advantage of the different parts of this system, the OfficeWriter for SSRS package comes in two parts. The Designer is what you use to create the reports, either in Excel or Word, while the Renderer sits on the Reporting Services server.

This then is the OfficeWriter approach: from the Ms Office front-end, reports are produced in the SSRS XML-based RDL format, then uploaded to the SSRS ReportServer database, like any report produced using Visual Studio (VS). This means that they can also be viewed in Report Manager along with any other SSRS reports you have.”

[Read the full review]