Tag Archives: knowledge base

ExcelWriter, Knowledge Base, OfficeWriter

Can I Print ExcelWriter Generated Files from the Server?

Problem

I am trying to print ExcelWriter generated files. What are my options?

Solution

ExcelWriter does not currently support printing directly from the server.  In order to print, the contents of the Excel file must first be rendered. As ExcelWriter does not currently have the ability to render content, rendering is handled by Excel when the output file is opened by the client. This means that in order to support printing directly from a web server it would be necessary to have Excel on the server. However, having Excel on a web server is not recommended.

There are two workarounds that we recommend to meet your printing needs:

  • Save files to a dedicated printing server
  • Automate printing using a VBA macro, when the file is opened on the client

The ability to render Excel files is being planned for a future release of ExcelWriter.

Setting Print Options

Before printing you can set all your print options programatically through ExcelApplication.      Example code is below: Continue reading Can I Print ExcelWriter Generated Files from the Server? →

ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

What to Do When OfficeWriter Sporadically Throws “General license key exception: Product key not installed.” Error

Problem

Even though the license key is in the registry, OfficeWriter sporadically throws the error “General license key exception: Product key not installed.” on Windows 2008 or above.

This issue has been reported by certain customers running a custom identity on an application pool in IIS 7 or above.  Most commonly this behavior has been seen on applications in 32-bit application pools, but has also been reported with 64-bit applications.

The combination of several factors in IIS 7.5 and Windows 2008 can cause problems for application pools running under a custom identity if they need to read from the registry.  These factors may be responsible for the sporadic OfficeWriter error.

  1. Windows 2008 introduced application pool identities.
  2. Windows 2008 also added new functionality to the user profile in Windows 2008 that causes the OS to more aggressively clean up registry handles when they are not needed.
  3. Additionally, IIS 7 and above does not load the user profiles by default.

Troubleshooting

If you are receiving the “General license key exception” sporadically, changing the ‘Load user profile’ setting in IIS may resolve the issue.

The ‘Load user Profile’ setting

In IIS 7 and above, there is an application pool setting called ‘load user profile’.  When this is set to true IIS will load the user profile for the application pool identity.  This should keep Windows from cleaning the registry handles necessary for OfficeWriter to read the license key.

To set ‘load user profile’ to true:

  1. Open the IIS Management Console.  This can be done by going to Run and typing in inetmgr
  2. Open the Application Pools window by clicking View Application Pools on the Actions pane, and select the Application Pool you want to configure.
  3. Right click the Application Pool and select “Advanced Settings…”
  4. Under the Process Model menu, change ‘Load User Profile’ to true.

 

 

Next Steps

If you find that the “General license key exception’ error still occurs after setting the ‘Load user profile’ setting, please contact OfficeWriter support.

 

Note:  If you are receiving the  “General license key exception: Product key not installed.” error consistently, it is likely that the license key is not installed properly.   Instructions for installing OfficeWriter license keys can be found at httpblog.softartisans.com/tag/how-to-add-a-license-key/

 

ExcelWriter, Knowledge Base, OfficeWriter

Can ExcelWriter Handle Formulas with External References?

Problem

External references are references to a cell or range on a worksheet in another Excel workbook, or a reference to a defined name in another workbook.

ExcelWriter does not currently have the ability to parse formulas with external references. External references in formulas will cause ExcelWriter to throw an “Unable to parse formula: Error: Could not match input” error.

Solutions

Depending on how the formulas are being used, there are two workarounds to handle external references.

Solution 1: Avoid making API calls that would cause the formulas to be parsed

The ability to delay the parsing of formulas was added in ExcelWriter version 8.5.1.  Before this version, ExcelWriter parsed every formula in a worksheet automatically.  In 8.5.1 and later versions, formulas are only parsed if it is necessary. This means that as long as there are no calls in your code that would require ExcelWriter to parse the formulas, the formulas will be preserved. Calling CopySheet, or inserting or deleting sheets, columns, or rows will all cause ExcelWriter to parse the formulas.

Solution 2: Excel’s INDIRECT Function

If you need to set a formula programmatically or use methods that would cause ExcelWriter to parse the formulas with external references, Excel’s INDIRECT function can be used with the formula string passed as a parameter. For example:

wb.Worksheets[0].Cells[0,0].Formula = "+INDIRECT(\"'C:\\Temp\\[Book2.xlsx]Sheet1'!A1\")";

ExcelWriter will generate the correct ourput when the INDIRECT function is used. However, in order for the external reference in the formula to take effect, the source workbook needs to be open as well. If the source workbook is not open, the cell’s value will display “invalid cell reference error (#REF!)” Continue reading Can ExcelWriter Handle Formulas with External References? →

ExcelWriter, Knowledge Base, OfficeWriter

Why Do Some Blank Cells Return Different Values in OOXML?

Problem

Some blank cells in OOXML file formats return an empty string, while other blank cells return null.  The same file in BIFF format returns only null values.

This problem occurs because OOXML (.xlsx /.xlsm) preserves any empty strings in cells when the file is saved.  When saving a file in BIFF (.xls) any cells containing an empty string will be set to a null value.

This can be a concern for users switching to the OOXML file formats. Code that checks worksheets for null values may need to be amended to account for the change in behavior.

Example 


The following code illustrates how this discrepancy might affect users:

           ExcelApplication xlapp = new ExcelApplication();
		   Workbook wb = xlapp.Open("SampleFile.xlsx")  
		   Worksheet ws = wb[0];

		   //Trim a range of cells. 
		   //Because A1 is empty, its value will be 'empty string' after trimming.
		   for(int i = 0; i<= 20; i++)
           {
		   	  for(int j = 0; j<= 20; i++)
              {
                  string value = (string)ws.Cells[i, j].Value;
				  ws.Cells[i, j].Value = value.Trim();
              }
           }

 		   //Save and reopen the file in the OOXML format
           xlapp.Save(wb, Directory + "SampleFile.xlsx");
           wb = xlapp.Open(Directory + "SampleFile.xlsx");
           ws = wb.[0];
           if (ws.Cells[0, 0].Value == null)
            {
               // This code would be reached in .xls
			   // but not in .xlsx  
            }

Users who currently check for cells containing null values may find that their code produces unexpected results when switching to OOXML files.


Solution

In order to ensure that code will return correct results when dealing with blank cells, it is important to use logic that handles all possible values.  There are 2 ways that we suggest implementing this logic.

Solution 1 – Use IsNullOrEmpty when checking for blank cells

Use logic that checks whether a cell is either null or an empty string.

Example:

            if(string.IsNullOrEmpty((string)ws.Cells[0, 0].Value))
            {
               //With this new logic, it could be either file format
                ws.Cells[1, 1].Value = "This is a .xls or an .xlsx file";   
            }

Solution 2 – Keep empty string values from being assigned to cells

Check if a cell is null before performing any operations that would assign an empty string value to a null cell.

Example:

           //Check that the cell is not empty before trimming
           if( ws.Cells[0,0].Value != null)
           {
                  string value = (string)ws.Cells[0, 0].Value;
				  ws.Cells[0, 0].Value = value.Trim();
           }
Related Information:

If your worksheet contains formulas, best practice would be to check the cell for formulas as well. More information on how to check for empty cells can be found here.


ExcelWriter, Knowledge Base, OfficeWriter

How to Use Worksheet Protection Properties In ExcelWriter

How to Use Worksheet Protection Properties In ExcelWriter

In ExcelWriter 8.6.1, the ability to set specific worksheet protection properties was added with the new Sheet Protection Object. Worksheet Protection Properties allow the user to add permissions to do certain actions on protected worksheets.

The Excel Protection Properties

Excel Worksheet Protection Property ExcelWriter SheetProtection Object Property
Select Locked cells SheetProtection.AllowSelectLockedCells
Select Unlocked Cells SheetProtection.AllowSelectUnlockedCells
Format Cells SheetProtection.AllowFormatCells
Format columns SheetProtection.AllowFormatColumns
Format Rows SheetProtection.AllowFormatRows
Insert Columns SheetProtection.AllowInsertColumns
Insert Rows SheetProtection.AllowInsertRows
Insert Hyperlinks SheetProtection.AllowInsertHyperlinks
Delete Columns SheetProtection.AllowDeleteColumns
Delete Rows SheetProtection.AllowDeleteRows
Sort SheetProtection.AllowSort
Use Autofilter SheetProtection.AllowUseAutoFilter
Use PivotTable reports SheetProtection.AllowUsePivotTableReports
Edit Objects Not Supported by ExcelWriter
Edit Scenarios Not Supported by ExcelWriter

Protection Properties Limitations

Microsoft Excel has a limitation on four of the protection properties: AllowSort, AllowDeleteColumns, AllowDeleteRows, and AllowInsertHyperlinks. These properties require that affected cells be editable when the worksheet is locked. This is because these properties entirely remove cells or alter the content of cells that are protected. Therefore, in order to use the properties you must unlock cells, put cells in an editable range, or use a macro.

Using the Protection Properties in Excel Writer

Steps to Use Protection Properties in ExcelWriter: 

  1. Create a new sheet protection object
  2. Set the worksheet protection properties as desired. By default, Select Locked cells and Select Unlocked Cells are set to true
  3. Call Protect() on the worksheet Continue reading How to Use Worksheet Protection Properties In ExcelWriter →

Excel, ExcelWriter, Knowledge Base

How to Sort Locked Cells in Protected Worksheets

Problem

When creating workbooks with protected sheets, it is common to want to allow users to get different views of the data by sorting and filtering,  without allowing them to change the data. However, while filtering works fine on locked cells, sorting does not. Even if Sort is enabled in the worksheet protection settings, if a user attempts to sort locked cells when a worksheet is protected, Excel throws the error “the cell or chart you are trying to change is protected and therefore read-only.” There is no obvious way to allow users to sort data on a protected worksheet.

Details

Locked VS. Unlocked Cells in Excel

The purpose of locking a cell is to prevent a user from editing the content of a cell when a worksheet is protected. This means when worksheet protection is turned off, a locked cell is no different from an unlocked cell. By default, all cells in an Excel worksheet are locked.

In order to unlock cells in Excel:

  1. Unprotect the sheet you are working on
  2. Right-click the cell, select “Format Cells”
  3. Select the “Protections” tab
  4. Uncheck the “Locked” checkbox property
  5. Click “OK”

Lock Cells Dialog

Lock Cells Dialog Box

It is also possible to unlock specific ranges of cells in Excel using the Allow Users to Edit Ranges feature in the Review tab. Making ranges editable for ranges of cells makes the cells behave like unlocked cells for the most part (e.g. their content is editable). However, the cell’s official locked/unlocked status does not actually change. See this Microsoft Excel article  for more details about how to unlock ranges of cells.


About Worksheet Protection Properties

When you protect a sheet, Excel allows you to select from 15 different permissions you want to give to all viewers of the worksheet. You can allow users of the worksheet to:

  • Select Locked Cells
  • Select Unlocked Cells
  • Format Cells
  • Format Columns
  • Format Rows
  • Insert Columns
  • Insert Rows
  • Insert Hyperlinks
  • Delete Columns
  • Delete Rows
  • Sort
  • Use Autofilter
  • Use PivotTable Reports
  • Edit Objects
  • Edit Scenarios

Protect Sheet Dialog

properties

How Protection Properties are Affected By Locked Cells

When a cell is locked, not all worksheet protection properties operate as you’d expect. Four of the properties do not work when a cell is locked:

  • AllowSort
  • AllowDeleteColumns
  • AllowDeleteRows
  • AllowInsertHyperlinks

This is because in order to use these features the affected cell’s content must be changed. For example, using “Sort” does not just change the order of how the cells are viewed, it actually changes the values of the cells so that they are sorted. Due to this implementation of “Sort,” this worksheet protection property does not work when the cells are locked.

Solution

There are two approaches we can take to solve this issue:

Solution 1: Using “Allow Users to Edit Ranges” to Allow Locked Cell Sorting (RECOMMENDED)

This solution takes advantage of how allowing users to edit ranges makes locked cells behave like unlocked cells.

Step 1: Make cells editable so that sorting will work.

Add cells we want to sort to a range and make that range editable in “Allow Users to Edit Ranges.” This allows users to edit these cells when the worksheet is protected, even if they are locked cells.

  1. Select all the cells you would like the user to be able to sort, including their column headings.
  2. Go to the Data tab and click Filter. An arrow should appear next to each column header.
  3. Go to Review tab-> Allow Users to Edit Ranges
    1. Click “New…”
    2. Give the range a title.
    3. “Refers to Cells” should already contain the cells you want to allowing sorting on.
    4. If you want to allow only certain people to sort, give the range a password.
    5. Click “OK”

Step 2: Prevent users from editing these cells

When protecting the worksheet, uncheck “Select Locked Cells” worksheet protection property. This will prevent users from editing the cells.

  1. In the “Allow Users to Edit Ranges” dialog:
    1. Click “Protect Sheet…”
    2. Give the worksheet a password
    3. Uncheck the worksheet protection property called “Select Locked Cells”
    4. Check the “Sort” property and the “AutoFilter” properties
    5. Click “OK”

This solution allows users to use the Auto Filter arrows in the column names or the Sort buttons in the Data tab to sort data. Another benefit is that you have the option of allowing only certain users to sort by giving the range a password. Please note that this range password is separate from the password you set to protect the sheet.

Solution 2: Using Macros to Allow Locked Cell Sorting

To use a Macro to allowed the sorting of locked cells, you will need to make a macro for every sort operation you would like to allow. For example, ascending sorts and descending sorts would have to be written in separate macros. In addition, not all users have macros enabled because they are a security risk. However, the advantage of macros is that you don’t need to configure your template in any special way.

The macro we will write unprotects the sheet, selects a range called “range1,” sorts range1, and protects the sheet again:

Sub Macro1() 
' 
' Macro1 Macro 
' 
' Keyboard Shortcut: Ctrl+d 
'    
     //unprotect sheet 
     ActiveSheet.Unprotect
     //selects range     
     Application.Goto Reference:="range1"     
     //clears sort
     ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear     
     //does descending sort, sets sort properties 
     ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
         SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
         .SetRange Range("A1:A4")
         .Header = xlNo
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
     End With
     //protects the work sheet again
     ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
         False
 End Sub

Knowledge Base, OfficeWriter, WordWriter

How to prevent line breaks after an empty merge field

Problem

When you use the WordTemplate object to populate a WordWriter template document with data retrieved from a data source, you may have situations in which a merge field is not bound to a value. The most common scenario is creating a mailing address. Each field of a mailing address may be represented by a merge field. Typically, the second line of the address is optional. In the generated document, if the merge field corresponding to the second line of the address does not have a value, a blank line may be present.

In Microsoft Word, a blank line generated during a mail merge can be suppressed using application settings. With WordWriter, a blank line is not automatically removed.

WordWriter template document:

Generated document:

Solution

The following discussion uses a merge field called AddressLine2 as an example, but is applicable to any field with optional value.

In the WordWriter template document, if the line break is separate from the AddressLine2 merge field, the line break will be present in the generated document whether or not Continue reading How to prevent line breaks after an empty merge field →

ExcelWriter, Knowledge Base, Reporting Services, SSRS

How to create a drill-down report in Excel with hyperlinks

Problem

Reporting Services provides drill-down report behavior, where clicking on a populated data field generates another report based on the value of the data field clicked. This post addresses how to acheive similar behavior within Excel with OfficeWriter using Reporting Sevices URL access and Excel’s HYPERLINK function.

Solution

Overview

When the OfficeWriter renderer populates the Excel report with data, it inserts new rows for each row of data. This means that all Excel formulas are updated, including the HYPERLINK function formula. This approach uses this formula update functionality and imported data values to dynamically create hyperlinks that point to the Reporting Services URLs.

For the sake of example, “SalesReport” is the original report that contains a list of invoice IDs. “InvoiceReport” is the linked report that takes an InvoiceID as a parameter.

Reporting Services URL Access

Typically, Reporting Service reports are rendered through an application, such as the Report Manager. Reporting Services also allows for rendering reports using just URLs. The basic syntax to render a report wtith a parameter is:

 http://[servername]/reportserver?/[File path to report on Report Server] &rs:Command=Render&rs:Format=[Rendering Format]&[ParamID]=[Param Value]

The URL for rendering the “InvoiceReport” in the OfficeWriter for Excel format, with Invoice ID 12345, is as follows:

 http://myservername/reportserver?/ InvoiceReport&rs:Command=Render&rs:Format=XLTemplate&InvoiceID=12345

Notes:

Excel, ExcelWriter, Knowledge Base, OfficeWriter

ExcelTemplate: How to format alternating row colors

Problem

A common way to display data in Excel is to alternate the background color of every other row when displaying a large table of data. With ExcelWriter there are multiple ways to accomplish this. This post covers some possible ways to apply alternating row colors with ExcelTemplate.

There is another post that discusses how to do this with ExcelApplication.

Solution

Option 1: Format as Table in Excel 2007/2010

Starting in Excel 2007, Excel provides pre-formatted table styles which already contain alternating row or column colors. This is the easiest way to format your data with alternating row colors. Note: these table styles may not render properly in Excel 2003 or in the XLS file format.

To format an area of cells as a table:

1. Highlight the area of cells.

2. Go to Format as Table in the ribbon.

3. Select a table style from the available styles.

4. If you chose to include your table header row, make sure to check off “My table has headers” in the confirmation dialog.

There are basic options for modifying the banding patterns:

You can also create new table styles:

You can do this for ExcelTemplate templates:

When the rows of data are inserted, the color banding will be applied:

Option 2: Use Conditional Formatting

The other approach is to use conditional formatting in the template to achieve alternating row colors. This may be more appropriate if you are not certain if your end-users will have Excel 2007/2010.

1. Create an ExcelWriter template with data markers in Excel.

2. Highlight the cells with data markers that correspond to the data you wish to display with alternating background colors.

3. From the menu, choose Format>Conditional Formatting. The formatting you define for this row will be applied to every new row that will be inserted by ExcelTemplate at runtime.

4. First define the formatting for even rows. In the “Condition1” field, choose “Formula Is” and in the formula field, type the following formula:

 =MOD(ROW(),2) = 0

This formula uses the MOD( ) function to determine if the number of the current row (returned by the ROW( ) function) can be evenly divided by 2.

5. Click on the “format” button.

6. Click on the “patterns” tab and select a background color.

7. Now set a condition for odd rows, by clicking “ADD” and following the same steps as above but with a different formula:

 =MOD(ROW(),2) = 1

8. Save the template and use it in your ExcelWriter application.

When ExcelTemplate imports new rows of data, the conditional formatting will also be applied to all the new rows:

ExcelWriter, Knowledge Base

How to insert a hard carriage return into a string of data

Problem

In Excel, to have text displayed on multiple lines, two conditions must be met:

  1. The cell must be formatted with Wrap Text
  2. The text must contain the new line character (hard carriage return)

When you press ALT+ENTER in Excel, Excel automatically formats the cell to have Wrap Text and inserts a new line character into the cell.

This post covers how to insert a hard carriage return into data that is imported into an Excel file with ExcelWriter.

Solution

Text Wrap

If you are importing data using ExcelTemplate, make sure to format the cell alignment with “Text Wrap” in the cell that contains the data marker. This also applies if you are importing data using ExcelApplication with a partial template.

To set “Wrap Text” in Excel:

  1. Right-click on the cell
  2. Go to the Alignment tab
  3. Under Text Control, check off “Wrap Text”

If you aren’t working with a template, you can also set this property on a cell using ExcelApplication:

 //The WrapText property must be set to true in the cell containing the new line character worksheet.Cells["A1"].Style.WrapText = true;

New Line Character

To import the new line character with your data, just add the new line character to your string of data.

Here is an example of using ExcelApplication to write a string into a cell. The string contains the new line character:

 worksheet.Cells["A1"].Value = "Fred Smith," + '\n' + "President";

Here is an example of some data that might be bound to a ExcelTemplate file:

 object[] dataArray = new object {"Fred Smith \n President", "2003", "Boston, MA"};