Beginning in OfficeWriter 9.1, ExcelApplication’s calculation engine will offer the ability to implement custom formulas. This feature should prove helpful to users whom would like to calculate their own custom formulas or calculate formulas not currently supported by ExcelWriter on the server using ExcelWriter. This tutorial will show you how to implement these formulas.

Creating Your Own Formula

Create a class that implements an interface provided by ExcelWriter called IFunction. The IFunction interface implements a method called Calculate.

Inside of your class, create the Calculate method with this signature:

FunctionValue Calculate(IList<FunctionValue> args, Cell currentCell)

Inside of the Calculate Function, code the logic of the formula you would like to implement.

The first argument of Calculate is a list of FunctionValue objects. FunctionValue objects are how we pass values in and out of formulas. FunctionValues have several properties available to them, so please see the documentation for more information about how you can use the properties in your formula logic.

The second argument of Calculate is the cell that contains the formula. Please see the documentation for more information about Cell objects and their available properties and methods.

Registering the Formula with ExcelWriter

Calculating the Formula and Removing it From a Workbook

Once your formula is registered, you can use WB.CalculateFormulas to have ExcelWriter calculate the value of any cells that use the formula in your Workbook. If you are generating Excel output using ExcelApplication.Save, please note that if you have implemented a custom formula that it is not recognized by Microsoft Excel, the end user will not see the calculated values in the output workbook when opened in Excel. To get around this issue, you can use Workbook.RemoveFormulas or Worksheet.RemoveFormulas to remove the formulas from the worksheet or workbook, while leaving the last calculated values in the cells.

Here is an example of what your finished code should look like:

public class sample
{
MyMainMethod()
{
ExcelApplication xla = new ExcelApplication(ExcelApplication.FileFormat.Xlsx);
Workbook WB = xla.Create(ExcelApplication.FileFormat.Xlsx);
WB[0]["A1"].Formula = "=COUNTARGUMENTS(4, 5, 6)";
WB.RegisterCustomFunction("COUNTARGUMENTS", new Formula());
WB.CalculateFormulas();
//Optionally remove all formulas from the workbook, so only values remain. This is good in case you are using a custom formula that Excel will not be able to calculate.
WB.RemoveFormulas();
xla.Save("output.xlsx");
}
}
class Formula : IFunction
{
public FunctionValue Calculate(IList<FunctionValue> args, Cell cell)
{
//Returns the number of arguments
return new FunctionValue(args.Count);
}
}

If you have ever filed a support incident with SoftArtisans Technical Services concerning your OfficeWriter reports, you know that one of the most important steps in resolving an issue is having a Technical Services Engineer reproduce it. While there are many components to reproducing a customer issue, one of the most critical aspects is having sample data to run the affected report with. Unfortunately, this can be a special challenge because a Technical Services Engineer does not have access to your data sources. The inability to run the report can delay or even halt Technical Service’s ability to troubleshoot an issue. However, do not fret, as CSV files can save the day!

Steps to Save Your Report Data as a CSV File

Download the attached template Collecting_Data_For_SA. The template will allow you to gather data for up to 10 datasources with up to 35 columns each.

Run this template through your application:

If you have a custom web application that uses ExcelTemplate: run this template through your application.

If you have a custom web application that uses ExcelApplication or Word Application: run this template through your application using ExcelTemplate with code that looks something like this:

The original OfficeWriter Designer allowed users to add the equivalent to SSRS expressions in their Designer report by using a feature built-in to the Designer called the formula manager. However, in recent months, SoftArtisans has released a new, beautiful, more robust designer called the Designer .NET. The only complication is that the Designer. NET does not yet have a built in formula manager. Fortunately, you can still add many calculated values, parameters, and other report information to your report by using SSRS calculated fields in Visual Studio.

Right click the dataset you want to add the expression to and click “Add Calculated Field…”

A dialog should appear with two columns: Field Name and Field Source.

Enter any name into Field Name

Click the fx symbol to create a formula for the value of your field.

In the new dialog, you decide what formula you want your field to express. Let’s say you want to display a parameter in your report. In this case, you would click “Parameters”, and then double-click the parameter you want to add. You should now see a formula at the top of the window.

Hit “Ok” and exit out of the dialogs.

Save your RDL and open it in the Designer .NET.

While designing the report, add the data marker that corresponds to your expression into your report.

When you’re finished designing the report, deploy it to SSRS from the OfficeWriter Designer .NET

Process Monitor is a great tool that can help you troubleshoot applications when error messages alone just aren’t enough information to solve a problem. Process Monitor works by logging in-depth about the actions of particular processes. It will give you in-depth information about file access, registry access, threading, and permissions. In this how-to tutorial, we will show you how to collect information on the process that ASP and ASP.NET web applications run on – wpw3.exe.

Step 1: Download Process Monitor

Step 2: Open Your Web Application

Go to the page in your web application before your error occurs. You need to be easily able to trigger the event that causes the error while ProcMon is running to avoid collecting too much information.

Step 3: Monitor the w3wp.exe Process

Reset the filter by clicking Filter -> Reset Filter

Add the w3wp.exe process to the filter by going to Filter -> Filter…

A dialog box will appear.

Create a rule that says “Process Name is w3wp.exe”

Click “Add”

Click “Apply” and then “OK” to exit the Dialog

Step 4: Collect Information from ProcMon

Please make sure that the Capture icon (shaped like a magnifying class) is enabled. There should NOT be a red “X” through it.

Go to your web application and trigger the error.

Once the error occurs, go back to ProcMon and click the Capture Icon to stop capturing events.

Step 5: Examine the ProcMon Logs

The first thing you should do when examining the logs is to see if anything in the “Result” column is not “SUCCESS”. Please take notes of any warnings or errors.

Once you find the errors, determine if they are relevant to your issue.

When you want part of your WordTemplate document to repeat on every page, you must set a PageBreak on that page. However, if you set a default PageBreak, Word will automatically insert what is called a Page Break After. The Page Break After will result in an extra page at the end of your document. In order to fix this, you will need to set a Page Break Before at the beginning of the page you want to repeat.

Instructions on Setting a Page Break Before

Click on the top left corner of the page where you want your page break to occur.

In the “Home” tab, there should be a “Paragraph” section. Click the arrow on the bottom-right of the Paragraph Section.

Go to the “Line and Page Breaks” tab and check the “Page Break Before” option.

Click “Ok”.

Word will have now inserted a PageBreak before at the spot where you cursor lay in the document. Your document should now generate the correct number of pages.

Sending OfficeWriter output in an e-mail is possible while either using OfficeWriter in .NET or with SSRS integration. When using the OfficeWriter .NET API, you can use the .NET MailMessage class to send your OfficeWriter output as an attachment in an e-mail. In SSRS, you can send the output as a subscription-based e-mail.

Sending OfficeWriter Output Using the .NET MailMessage Class

In order to send OfficeWriter in an e-mail output using .NET, you need to use the MailMessage object. If you save the workbook, document, or presentation to a stream, you can create an attachment out of the file in the stream. This attachment can be added to the MailMessage object and sent to the workbook’s or document’s end users.

What version of OfficeWriter do I need for 64-bit support?

Full 64-bit support for OfficeWriter’s pure .NET classes was introduced in OfficeWriter 3.9.1 (ExcelWriter 6.9.1). The OfficeWriter .NET dlls are compiled with the /anycpu flag so they will work fine on either 32-bit or 64-bit systems.

OfficeWriter’s COM dlls are still 32-bit. If you have old ASP.NET applications that are still using the .NET wrapper classes for ExcelWriter COM, you will need to run those applications in a 32-bit application pool or change your code to use the pure .NET objects. See Using OfficeWriter COM.

What is the difference between the 32-bit and 64-bit installers?

There are no significant differences between OfficeWriter’s 32-bit and 64-bit installers. Both contain the same .NET dlls which are compatible with both 32-bit and 64-bit systems.

The 64-bit installer will create the OfficeWriter program folder in Progam Files rather than Program Files (x86)

You can run the 32-bit installer on a 64-bit OS. However if you are installing OfficeWriter in SSRS integration mode, the 32-bit installer may have trouble finding a 64-bit instance of SSRS.

There is no problem manually deploying files from a 32-bit installation to a 64-bit machine

At times customers have experienced OutOfMemory exceptions when generating very large Excel reports with OfficeWriter. Generating reports with millions of cells will necessarily use a significant amount of memory, especially when using the ExcelApplication object. Note that ExcelWriter is not just keeping a file in memory, it is populating an entire object model in order to be able to manipulate the file. For example, each cell has associated objects for the value, formula, formatting, and more. The ExcelTemplate object (which is also used behind the scenes in our Reporting Services integration) has a smaller object model, so it uses less memory than ExcelApplication. However, a very large report can still require a significant amount of memory with ExcelTemplate.

This article provides tips about how to avoid memory issues when generating large reports.

Try to use the latest version of OfficeWriter

Many performance improvements have been implemented in ExcelWriter over time. To take advantage of these optimizations, upgrade to the latest version when possible. See the OfficeWriter Change Log for details about changes in every release since OfficeWriter version 4.0 (ExcelWriter version 7.0).

Make sure your application is compiled as 64-bit if possible

A 32-bit application will have never have more than 2 GB of memory available to it, even you are running the application on a 64-bit OS with a huge amount of RAM. The pure .NET ExcelWriter objects (in the namespace SoftArtisans.OfficeWriter.ExcelWriter) are fully 64-bit compatible (see Using OfficeWriter .NET on a 64-bit machine). If you are using the pure .NET objects and running on a 64-bit machine, make sure your project is compiled as a 64-bit application. If you have an ASP.NET application that was written against a very old version of ExcelWriter, it may have dependencies on ExcelWriter COM which is 32-bit, in which case compiling for 64-bit will not be an option (see this KB article for more information)

Use the ExcelApplication API in the most efficient manner

There are a number of steps that will improve performance when working with large reports with the ExcelApplication API (For more details, see Best Practices with Large Reports) :

Populate data with ExcelTemplate, and use ExcelApplication beforehand for any necessary runtime file manipulations. ExcelTemplate is the most efficient way to import data but it cannot make fine-grained changes to the workbook. If you need to modify the workbook at runtime, modify the template programmatically before passing it to ExcelTemplate to avoid having to open a fully populated report with ExcelApplication. See Preprocessing vs. Postprocesssing.

Avoid referencing Empty Cells. Any time you touch an empty cell with ExcelApplication, a Cell object (and all its associated objects) is created even if it didn’t already exist. If you need to loop through cells to look for something, used Worksheet.PopulatedCells to get an Area containing only populated cells. Note that PopulatedCells will return cells that have only formatting and no data. For this reason is it important to apply formatting in the most efficient manner. More information about this below.

Apply Styles to Columns and Rows, not Cells. Setting styles on a cell-by-cell basis or applying a style to an area, causes a separate Style object to be created for every Cell. On the other hand, if you Set a Style on a column or row, using ColumnProperties.Style or RowProperties.Style, there will only be one formatting record for the entire column or row. Currently there is no option to set conditional formatting at the column or row level with the ExcelWriter API. A workaround is to set the conditional formatting on the columns or rows in your input file, and ExcelWriter will preserve it.

Cache reports if possible

If you have a report that requires a lot of memory and is requested by many concurrent users, investigate whether it might be possible to keep a cached copy of the report and serve it to multiple users. Does the data change constantly or only at certain intervals? Are there parameters that tend to be different for every user or do multiple users run the report with the same parameters? If you have multiple users requesting a report with the same data and the same parameters, this report could be a good candidate for caching. You could run the report at a certain time with a background process, or you could cache a copy the first time any user generates the report within a certain timeframe.

If you are using OfficeWriter in a custom .NET application, you would implement caching in your own code. If you are using OfficeWriter in SSRS-integration mode, you can use SSRS’s built-in caching functionality.

Queue reports if necessary

If you have a report being accessed by multiple users that requires a great deal of memory in a memory-constrained environment (i.e. your server is 32-bit or you have to compile your application as 32-bit for some reason), and caching isn’t an option (i.e. every time a user runs the report it is different), then queuing reports may be something to consider. Instead of delivering every report on-demand, you can restructure your application to store the users’ requests, process them sequentially in a background process, and notify the users by email or some other means when their reports are ready. This approach should not cause your users to have to wait much longer for a report than if it was being generated on-demand, but of course the user experience will be a little different. One option is to implement logic in your application to only queue very large reports but deliver smaller reports on-demand.

If you are using OfficeWriter in SSRS-integration mode, you can accomplish queuing by using SSRS subscriptions.

If you are generating a Word document with OfficeWriter and you wish to import some HTML-formatted text, there are various options, depending on the version of OfficeWriter you are using and the file format of your document (.doc or .docx/.docm)

Template-based approach

With WordWriter’s template-based approach, using the WordTemplate API or OfficeWriter’s SSRS integration, HTML-formatted text can be imported by using special merge field modifiers. This functionality was introduced in version 8.0 with some limitations (i.e. no SSRS support). Further enhancements were added in version 9.0 and 9.1. Using version 9.1 is highly recommended, as it provides the most comprehensive support for this functionality in both custom .NET applications and SSRS integration mode.

Advantages of template-based approach

No complex coding required, everything is controlled through your template and data

Allows HTML-formatted text to be used with WordWriter’s mail merge and grouping functionality

Limitations of template-based approach

Only supports the OOXML file format. The template must be a .docx or .docm file

The feature relies on Word’s “altChunk” functionality. Each HTML snippet is embedded as a separate little file, and Word renders the contents when the file is opened on the client machine. Therefore, if you are viewing the output file in something other than Word (i.e. a mobile device), the HTML may not be rendered correctly. Note: If you resave the output in MS Word, the HTML will be merged into the main document and can be viewed in any Word-compatible application.

How to Use It

Your merge field should look something like this:
<<DataSourceName.ColumnName(document(html)>>

Your HTML-formatted text must be passed in as a byte array, since the “document(format)” modifier expects a file rather than a string.

Starting in Version 9.1, it is possible for your data to include a filepath or URL rather than a byte array, by using the new AllowURIs property. However, in 9.1 the “document(format)” modifier is no longer the best way to import HTML snippets. In 9.1 and above, the “document(format)” modifier should be reserved for cases where you wish to embed an entire document (HTML, DOCX or RTF)

Prior to version 9.0, the data must contain an opening and closing <html> tag. Beginning in version 9.0, WordWriter will add the opening and closing tags for you.

OfficeWriter version 3.x includes ExcelWriter version 6.x and WordWriter version 3.x.

OfficeWriter v3.x is no longer supported, and upgrading to the current version is highly recommended. However, if for some reason you need to use version 3.x on a 64-bit system, this article provides all the information you need.

OfficeWriter 3.9.1 and 3.9.2

The first version of OfficeWriter to include 64-bit support was version 3.9.1. The 3.9.1 installer will run on a 64-bit machine. The .NET assemblies in 3.9.1 and above are compiled with the /anycpu flag, which means they will work with both 32-bit and 64-bit processes. The COM dlls, however, are 32-bit. Here are all the considerations when using 3.9.1 or 3.9.2 on a 64-bit system:

If you are using OfficeWriter in a classic ASP application or an ASP.NET application that is using our legacy .NET wrapper classes for ExcelWriter COM, you will need to set your application pool to 32-bit mode (For more information about OfficeWriter COM, see Using OfficeWriter COM)

Open the IIS management console

Create a new app pool or select an existing one

Click on “Advanced Settings”?

Set “Enable 32-Bit Applications” to True

Click OK

If your application is ASP.NET, whether it is using the pure .NET objects or the wrapper classes, you will need to install the 64-bit version of the J# runtimebefore running the OfficeWriter installer. OfficeWriter 3.x had a dependency on J#. This was removed in version 4. You can get the 64-bit J# redistributable here. Note that the version of J# must match the version of the .NET framework you are using for your application. Microsoft has deprecated J# and there is no version of J# later than 2.0.

OfficeWriter 3.0 to 3.8.1

Versions of OfficeWriter 3 prior to version 3.9.1 do not officially have 64-bit support, however the dlls may be used on a 64-bit system with certain limitations. The automatic installer is not 64-bit compatible and the .NET assembles are not compiled with the /anycpu flag. Therefore, both the .NET and COM dlls are 32-bit. OfficeWriter 3.0 – 3.8.1 can be used on a 64-bit system only in the following manner:

You must do a manual installation:

If your application is ASP.NET, make sure the 64-bit J# runtime is installed. See step 2 above.

Copy the OfficeWriter dlls and LicenseManager.exe from an OfficeWriter program folder on a 32-bit machine

If you are using OfficeWriter in classic ASP or if you are using the .NET wrapper classes for the COM objects, register the COM dlls (see Using OfficeWriter COM)

Copy the .NET dlls to the bin directory of your application(s)

You must be using OfficeWriter in a custom application rather than in SQL Server Reporting Services. A 32-bit dll cannot run in a 64-bit process. 64-bit SSRS does not have an option to set it to run in 32-bit mode. Therefore in order to use OfficeWriter in 64-bit SSRS, you must use version 3.9.1 or above.

You must assign your application to a 32-bit application pool:

Open the IIS management console

Create a new app pool or select an existing one

Click on “Advanced Settings”?

Set “Enable 32-Bit Applications” to True

Click OK

Upgrading from v3.x

If you have an ASP.NET application and you wish to upgrade from version 3.x in order to eliminate the dependency on J# and take advantage of fixes and enhancements in later versions, you will need to make some changes to your code. See these pages in the documentation: