All posts by Stephanie Rosario

How to Calculate Unsupported or Custom Formulas on the Server with ExcelWriter

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

  1. Create a class that implements an interface provided by ExcelWriter called IFunction. The IFunction interface implements a method called Calculate.
  2. Inside of your class, create the Calculate method with this signature:
    FunctionValue Calculate(IList<FunctionValue> args, Cell currentCell)
  3. Inside of the Calculate Function, code the logic of the formula you would like to implement.
    1. 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.
    2. 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

  1. Once the formula is written, register the function using Workbook.RegisterCustomFunction.
    WB.RegisterCustomFunction("MyFormula", new MyClass());

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);
   }
}

Saving Your Report’s Data in CSV Format

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

  1. 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.
  2. Run this template through your application:
    1. If you have a custom web application that uses ExcelTemplate: run this template through your application.
    2. 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:
      ExcelTemplate xlt= new ExcelTemplate();
      
      //Open the collecting_data_for_sa template
      xlt.Open("collecting_data_for_SA.xlsx"));
      
      //Bind each of your datasources to the template
      xlt.BindData([YOUR DATA SOURCE], "Something", xlt.CreateDataBindingProperties());
      xlt.BindData([YOUR DATA SOURCE2], "Something2", xlt.CreateDataBindingProperties());
      xlt.BindData([YOUR DATA SOURCE3], "Something3", xlt.CreateDataBindingProperties());
      
      xlt.Process();
      xlt.Save("data_collected.xlsx");
    3. If you are using SSRS integration:
      1. Create a copy of your affected RDL.
      2. Open the copy RDL in the OfficeWriter Designer or OfficeWriter Designer .NET for Microsoft Excel.
      3. Click “Import Template” in the Designer tab and overwrite your existing template with the collecting_data_for_sa file.
      4. The copy rdl should now contain the collecting_data_for_sa template.
      5. Upload the rdl to SSRS and run the report.
  3. Send the results to SoftArtisans Technical Services

Adding SSRS Formulas, Global Variables, and Parameters to Your Designer .NET Report

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.

NOTE:  Currently Reporting Services does not allow Visual Studio calculated fields to contain user-defined variables, aggregate, RowNumber, RunningValue, Previous, or lookup functions when rendering the report.

Adding Expressions to Your DataSet

  1. Open your RDL in Visual Studio.
  2. Right click the dataset you want to add the expression to and click “Add Calculated Field…”
    Add a Calculated Field...
  3. A dialog should appear with two columns: Field Name and Field Source.
  4. Enter any name into Field Name
  5. Click the fx symbol to create a formula for the value of your field.
    Click the "fx" button
  6. 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.
    Add a parameter by double-clicking the one you want to add
  7. Hit “Ok” and exit out of the dialogs.
  8. Save your RDL and open it in the Designer .NET.
  9. While designing the report, add the data marker that corresponds to your expression into your report.
    ExpressionDataMarker
  10. When you’re finished designing the report, deploy it to SSRS from the OfficeWriter Designer .NET

How to Use Process Monitor (ProcMon) to Troubleshoot Web Applications

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

You can download Process Monitor here. http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

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

  1. Reset the filter by clicking Filter -> Reset Filter
  2. Add the w3wp.exe process to the filter by going to Filter -> Filter…
  3. A dialog box will appear.
  4. Create a rule that says “Process Name is w3wp.exe”
    Process is w3wp.exe Rule
  5. Click “Add”
  6. Click “Apply” and then “OK” to exit the Dialog

Step 4:  Collect Information from ProcMon

  1. Please make sure that the Capture icon (shaped like a magnifying class) is enabled. There should NOT be a red “X” through it.
    Capture Events
  2. Go to your web application and trigger the error.
  3. Once the error occurs, go back to ProcMon and click the Capture Icon to stop capturing events.

Step 5:  Examine the ProcMon Logs

  1. 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.
  2. Once you find the errors, determine if they are relevant to your issue.
  3. If you would like to save the logs, you can by going to File -> Save.

How to Avoid Extra Page at Document End When Using WordTemplate

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

  1. Click on the top left corner of the page where you want your page break to occur.
  2. In the “Home” tab, there should be a “Paragraph” section. Click the arrow on the bottom-right of the Paragraph Section.
    Screenshot 2014-06-20 11.30.19
  3. Go to the “Line and Page Breaks” tab and check the “Page Break Before” option.
    Screenshot 2014-06-20 11.35.51
  4. 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.

How to Send OfficeWriter Output in an E-mail

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.

//Open Word Template
WordTemplate wt= new WordTemplate();

//Do some databinding and processing here
//YOUR CODE HERE

//Create a file stream that will save the output file on the server
FileStream myStream = new FileStream(@"C:\temp\outfile.xlsx", FileMode.Create);

//Save the output to the stream
wt.Save(myStream);

//Create an Attachment of the file in the stream
Attachment myAttachment= new Attachment(myStream, "output.xlsx");

//Create a mailmessage obj
MailMessage mailObj = new MailMessage("From Address", "To Address", "Subject", "Body");

//Add the attachment
mailObj.Attachments.Add(myAttachment);

//Connect to the server that will send the email
SmtpClient SMTPServer = new SmtpClient("thenameofyourhost");

//Set credentials
SMTPServer.UseDefaultCredentials = true;

//Try sending
try
{
    SMTPServer.Send(mailObj);
}
//Basic error handling
catch (Exception ex)
{
    Response.Write(ex.ToString());
}

Sending OfficeWriter Output Using a SSRS Subscription

If you are integrating OfficeWriter with SSRS, it also possible to send OfficeWriter output by using subscriptions. Be aware that the credentials to run the report must be stored inside of the report.

  1. After uploading your report to the SSRS server, open Report Manager
  2. Find your report and from the Drop Down, select “Manage”
  3. In the left-hand menu, select “Subscriptions” and then “New Subscription”
  4. Fill out the form, including:
    1. Who you want to send the report to
    2. When you want to send it
    3. What parameters the report should run with
    4. Be sure to select either “Excel designed by OfficeWriter” or “Word designed by OfficeWriter” as the rendering format.
      RenderFormat
  5. Click “Ok”
  6. Your new subscription should now appear in the Subscriptions list for your report and will run at the time you specified.

Enabling ExcelTemplate and WordTemplate Properties for SSRS

OfficeWriter SSRS Integration allows users to generate OfficeWriter output from RDLs in Report Manager. To enhance the level of customization that SSRS integration provides, users are able to set ExcelTemplate and WordTemplate properties by adding keys to specific configuration files. This article will describe how to enable supported ExcelTemplate and WordTemplate properties in SSRS. Please note that these properties are global: they will apply to OfficeWriter output generated by every report in Report Manager. To see what ExcelTemplate properties are supported in SSRS, visit this link.

Generating Reports On-Demand

One possibility for generating reports from RDLs is to click on the RDL in report manager and export the RDL to ExcelWriter or WordWriter formats.

If you would like to use this functionality, you will need to add your property keys to:  C:\Program Files\Microsoft SQL Server\[SQL INSTANCE]\Reporting Services\ReportServer\web.config

SSRS Subscriptions

SSRS subscriptions allow reports to be generated from RDLs to a fileshare or delivered to users via e-mail on a schedule. However, it is important to note that SSRS subscriptions run in a different context and process from the rest of SSRS. The result is that you will need to add your property keys to a different configuration file for subscriptions than you will for generating reports on the fly.

If you would like to use this functionality, you will need to add your property keys to:  C:\Program Files\Microsoft SQL Server\[SQL INSTANCE]\Reporting Services\ReportServer\bin\ReportingServicesService.exe.config

Instructions for Adding Keys to A Configuration File

Here are the steps to enabling supported ExcelTemplate and WordTemplate properties to the above configuration files:

  1. Open the configuration file you would like to modify. See above for which configuration file will enable the functionality you desire. You can add properties for reports generated on-demand, by subscription, or both.
  2. Check to see if an appSettings section exists in the file: ie <appSettings></appSettings>
    1. If yes
      1. Add your keys to the existing appSettings section. The format is <add key=”[KEY NAME]” value=”[VALUE]” />
    2. If not
      1.  add an appSettings section before the closing </configuration> tag at the bottom of your file. It should look like: <appSettings></appSettings>
      2. Inside of the appSettings tags, add your keys in the <add key=”[KEY NAME]” value=”[VALUE]” /> format.

webconfig

This is an example appSettings section in a configuration file

Upgrading from OfficeWriter 8.x to 9.0

If you are upgrading from OfficeWriter 8.x to OfficeWriter 9, there are a couple important things to note before you install OfficeWriter 9. Running the OfficeWriter 9.0 automatic installer, remove any previous versions of OfficeWriter on your server. If you intend on having OfficeWriter 8 and OfficeWriter 9 run on the same server, special installation steps will need to be followed. Another issue to consider when upgrading is that several deprecated objects have now been removed from the API.

Running OfficeWriter 8.X to 9.0 on the Same Server

Running the OfficeWriter 9.0 automatic installer will remove any previous OfficeWriter 8 installations on the server.

  1. Any version 8 license keys will be removed from the registry
  2. The Program Files folder will be overwritten by the new version
  3. OfficeWriter integration with SSRS installation will also be overwritten.

You will need to preserve the OfficeWriter 8 license key if you have applications on the server that use OfficeWriter 8.

If you want to run OfficeWriter 8.X and 9. 0 on the same server, please choose one of the two following installation paths before beginning to install OfficeWriter 9.

Option 1 Add v8 License Key after Running v9 Installer

Your first option is to record or backup your OfficeWriter 8 license key before running the OfficeWriter 9 installer. During the installation process, your OfficeWriter 8 license key will be removed from the registry and any applications using this license key will not work. However, once the installation is complete, you can run the License Manager program found in the SoftArtisans program files folder and re-add your OfficeWriter 8 license key there.

  1.  Record your license key
    1. Write down your OfficeWriter 8 license key
      1. Open C:\Program Files\SoftArtisans\OfficeWriter\LicenseManager.exe
      2. Click the dropdown to see the installed keys
      3. Copy your installed OfficeWriter 8 license key
    2. OR Export the OfficeWriter 8 license key in the registry.
      1. Enter regedit.exe to get to the registry
      2. Navigate to HKEY_CLASSES_ROOT\Licenses\SoftArtisans
      3. Go to File->Export and enter a file name. Make sure Export Range is set to HKEY_CLASSES_ROOT\Licenses\SoftArtisans
      4. Save the file to your desktop.
  2. OPTIONAL: Back up the C:\\Program Files\SoftArtisans folder.
  3. Run the OfficeWriter 9 installer
  4. Add your OfficeWriter 8 License Key
    1. If you wrote down the license key:
      1. Run License Manager by going to C:\Program Files\SoftArtisans\OfficeWriter\LicenseManager.exe
      2. Enter your version 8 key in the “New Key” field
      3. Click “Add/Upgrade”
      4. Hit “OK”.
    2. If you exported the version 8 license keys
      1. Go to the registry
      2. Import the keys by clicking File->Import and selecting the registry keys file on the Desktop.

Option 2 Manual v9 Installation

You can also do a manual installation of OfficeWriter 9 to prevent your version 8 license key from being removed.

  1.  Download the OfficeWriter 9 Installer to your desktop
  2. Unpack the V9 Installation Files without Installing OfficeWriter 9
    1. Open Command Prompt and navigate to the desktop by entering: cd desktop
    2. Enter the command msiexec /a C:\Users\YOURUSERNAME\Desktop\OfficeWriterInstaller-9.0.0-x64.msi /qb TARGETDIR=C:\Users\YOURUSERNAME\Desktop\OW9UNPACKED
    3. This will unpack the installer files into a folder on your desktop called “OW9UNPACKED”
  3. Copy the SoftArtisans folder inside of OW9UNPACKED
  4. Paste the SoftArtisans folder into C:\\Program Files.
  5. Run the License Manager inside of the OW9UNPACKED folder
    1. Enter your version 9 key in the “New Key” field
    2. Click “Add/Upgrade”
    3. Hit “OK”.
  6. If you are integrating with SSRs, follow these manual SSRS integration instructions.

API Methods and Properties Removed in Version 9

Some API methods and properties that were deprecated in OfficeWriter 8 have now been completely removed from the OfficeWriter 9 API. For more information, please visit this link.

How to Use the LineBreakstoParagraphs Property

The LineBreakstoParagraphs Property

A new feature in the WordWriter 8.6.1 WordTemplate Object is the ability to output new paragraphs where there are line breaks in your data using the LineBreakstoParagraphs property. This allows the user to have their text separate into new paragraphs every time there is a line break. The biggest benefit of this feature is that each new paragraph that is inserted will inherit the paragraph styles of its MergeField, including hanging paragraph settings, indentations after paragraphs, alignment, spacing, and orientation, to name a few. This means a single merge field can now output many identically formatted paragraphs rather than a single, large paragraph.

How to Use

By default, LineBreakstoParagraphs is set to False, and line breaks are inserted as soft breaks. A soft break is when text goes to the next line, but is still a part of the previous paragraph. Therefore the new line lacks any typical spacing and indentation that would occur between paragraphs. A soft return can be done in Word by pressing Shift + Enter. To use LineBreakstoParagraphs, you must set the property to true before calling Open(). Also be aware that this property only works on OOXML Word documents (.docx, .dotx, etc).

Comparison

LineBreakstoParagraphs Set to FALSE (DEFAULT)
 KB_LB1
LineBreakstoParagraphs Set to TRUE
KB_LB2

Sample Code

// Names of your mergefields
string[] NamesArr = { @"MultiLineString" };

//Your long multi-line string of text with linebreaks.
object[] ValuesArr = {  
@"Lorem ipsum .... 
Mauris ac elit ... 
Cras mi nisi....};

WordTemplate WordTempl = new WordTemplate();

//Set LineBreakstoParagraphs to true
WordTempl.LineBreaksToParagraphs = true;

WordTempl.Open(Page.MapPath("/LineBreakstoParaDemo.docx"));

//Set the datasource to the arrays.
WordTempl.SetDataSource(ValuesArr, NamesArr);
WordTempl.Process(); 
WordTempl.Save(Page.Response, "Demo.docx", false);

Creating Dynamic Links With MergeFields in Microsoft Word

Introduction

MergeFields allow users to import dynamic content in Microsoft Word. While this feature is very useful in many situations, it can be unclear how you must handle MergeFields nested inside of fields like hyperlinks. A user might want to have both dynamic link test and destinations for their hyperlinks. Unfortunately, how to achieve this is unclear because Hyperlink field codes only take a destination parameter. There is no place to put a merge field for your dynamic display text. In this article, I will show how to create fully dynamic links for Microsoft Word 2007 and up!

Step 1: Adding a Hyperlink Field

  • Put your cursor where you want to place the hyperlink
  • Go to Insert->Hyperlink
  • In the dialog box:
    • Next to Text to Display, enter something like “Temp Display Text”
    • Next to Address, enter something like “Temp Link”

You Should Now Have Something Like This:

KB_Step1

Step 2: Adding a Dynamic Destination Location

  • Press ALT+F9 to see the field code.
  • Delete the “Temp Link” text inside of the quotes.
  • If you have a base url location, enter it in between the quotes. Then proceed to the next step.
  • Now we must add a MergeField to get dynamic urls.
    • Put your cursor before the closing quote of your destination location.
      • Go to Insert-> Quick Parts-> Field.
      • Select MergeField from the Field Names menu.
      • Add the Field Name to identify the MergeField.
      • Click “OK” to exit the dialog.

You Should Now Have Something Like This:

KB_Step2 Continue reading Creating Dynamic Links With MergeFields in Microsoft Word