Category Archives: ExcelWriter

ExcelWriter, Knowledge Base, OfficeWriter, PowerPointWriter, WordWriter

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.

ExcelWriter, Knowledge Base, OfficeWriter

How to Format Rows or Columns only if they Contain Values

Problem

You want to apply particular styles or formats, but only to rows or columns that actually contain values. Here are some examples of styles and formats that you might want to apply to specific rows or columns:

  • Background/Foreground colors
  • Borders
  • Fonts
  • Text orientation, justification, indentation, orientation, number format
  • Horizontal/vertical alignment
  • Cell locked/unlocked when worksheet is protected

Notes:

Conditional formatting is useful if you wish to apply particular formatting that changes depending on the actual values in the cells. Go to Conditional formatting resources.

A similar technique can be used to format just an area. For specifics, see Format an area instead of rows or columns.

Solution

This solution has several steps:

  1. Define an area that contains the populated cells in order to determine which rows and columns to format.
  2. Create the style to apply.
  3. Loop through the rows or columns in the area and apply the style to each row or column.

Step 1:

Get the populate cells in the worksheet. There are two ways to do this:

A. Worksheet.Populated Cells – 

Use Worksheet.PopulatedCells to return an area of all the cells that are populated starting with the top left cell that contains a value or formatting, down to the bottom right cells. It includes both cells with values/formulas as well as cells that contain formatting (e.g. background color, conditional format etc.)

ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Open("MyUnformattedWB.xlsx");
Worksheet ws = wb.Worksheets["SheetToFormat"];
 
Area populatedArea = ws.PopulatedCells;

 

B. Use a named range

Add a named range in the template file around the area that will contain the populated cells and then use Worksheet.GetNamedRange(String) or Workbook.GetNamedRange(String) to retrieve the named range. Use Range.Areas to get a handle on the desired area.

This is less expensive than Worksheet.PopulatedCells since ExcelWriter does not need to go through the worksheet to figure out which cells contain values.  There are some limitations to this approach:

  • The number of columns in the named range will not change after data is imported. If there are columns in the populated named range that do not contain data, the named range won’t reflect that.
  • The number of rows in the named range will only change after data is imported if  the named range is wrapped around ExcelTemplate data markers and new rows are inserted.
  • If you are using Worksheet.ImportData, the named range will not update to reflect where the live data is.
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Open("MyUnformattedWB.xlsx");
Range myRange = wb.GetNamedRange("AreaWithValues");
 
Area populatedArea = myRange.Areas[0];

Step 2:

Create the desired Style with Workbook.CreateStyle().

For this example, we want to leave the populated cells unlocked, but leave the rest of the cells locked when the worksheet is protected. This is done by setting Style.CellLocked.

Style unlockStyle = wb.CreateStyle();
unlockStyle.CellLocked = false;

Step 3:

Determine the first row/column in the area with Area.FirstColumn or Area.FirstRow. Get the total number of rows/columns in the populated area with Area.RowCount or Area.ColumnCount.

int colStart = populatedArea.FirstColumn;
int totalCol = populatedArea.ColumnCount;

Step 4:

Iterate through the rows/columns with Worksheet.GetColumnProperties(int) or Worksheet.GetRowProperties(int). Call ApplyStyle on the appropriate RowProperties or ColumnProperties with ColumnProperties.ApplyStyle(Style) or RowProperties.ApplyStyle(Style). Applying the style on an entire row or column will consume less memory than applying the styles to individual cells.

for(int i = colStart; i < totalCol; i++)
{
     ws.GetColumnProperties(i).ApplyStyle(unlockStyle);
}
 
//For worksheet protection, we also need to protect the worksheet
ws.Protect("MyPassword");

Additional References:

Applying Formatting to just an Area instead of Rows or Columns

If you want to apply the formatting just to specific cells, you can use Area.ApplyStyle()  or Area.SetStyle() instead of looping through the area to apply the style to each row or column.

IMPORTANT: This is only recommended for small areas.  Area.ApplyStyle and Area.SetStyle apply the style to each cell within the area, which increases the time and memory needed to process the file.  Applying styles on a per-row or per-column basis is the recommended best practice since the style is applied to the RowProperties or ColumnProperties object, instead to each individual cell.

Conditional Formatting

ExcelWriter, Knowledge Base, OfficeWriter

Charts not rendering correctly when viewed in non-Excel applications

Problem

An ExcelWriter-generated file with charts displays correctly when opened in Excel.  When the same file is opened in an application that does not have the full Excel functionality, such as Outlook preview or in a mobile application, the charts are missing data or have the wrong data.

When a workbook is opened in Excel, all of the cell values are updated based on the current state of the file. Excel caches these most recent cell values and writes them out to the file. Many mobile apps or applications with a “preview” functionality where the file is locked from being updated, do not have the ability to recalculate the cell values and rely on those cached values to render the charts. When the cached values are not available, the charts may be rendered incorrectly so they may appear to have no data, wrong data, or may not appear in the file at all.

Below are common scenarios where the workbook cannot be fully updated:

  • Mobile applications
  • Applications with “preview,” such as Outlook’s attachment preview
  • Excel’s protected mode, which is activated when files are downloaded from the internet and opened in Excel before saving to disk

Starting in v8.3, ExcelWriter writes out the cached cell values to integrate better with applications that don’t have the ability to update the workbook. However, there are still some limitations that prevent charts from being rendered correctly. This article describes the limitations and expected behavior of rendering charts in files that were generated in ExcelWriter v8.3 or later. 

If you are experiencing issues

Jump to:

Details

iOS Applications

Charts are missing – Certain charts are not supported by iOS, regardless of whether they are in files generated by Excel, ExcelWriter, or some other application. An example of this is the bubble chart. These charts may not be rendered at all, depending on the application. More information is available in this article: Charts in ExcelWriter files are missing when viewed on iOS devices.

Charts are visible, but incorrect – If the chart is visible, but the data appears to be incorrect, then the chart may have data that contains formulas, formatted numbers, or it may be a PivotChart (see below for more info).

Chart data contains formulas

As of v9.0, ExcelWriter has the ability to calculate formulas with the Workbook.CalculateFormulas method. However, for the initial release, only a limited number of formulas are supported.

If ExcelWriter is unable to evaluate formulas (i.e. the formulas you are using are not yet supported or you are using a version prior to v9.0), or Workbook.CalculateFormulas is not called, then the most up-to-date values will not be available. Instead, if there is a formula in the cell, we will write a “0” to the cached chart field. Despite not being an accurate value, having the cached value reduces the risk of the chart not being rendered or the the application from crashing due to the missing data.

Chart data contains values with number formats applied

ExcelWriter does not have the ability to render numbers based on number formats (e.g. percentage). In the case that a number has a number format applied, the underlying value will be used for the cached field. For example, 6.5% has actual numerical value 0.065, which is the value that ExcelWriter will write to the cached field. This also applies to dates, which are stored as serial numbers in the file and then Excel uses the number format to render the formatted date. For more information about dates in Excel, please see this article: http://www.cpearson.com/excel/datetime.htm.

PivotCharts

PivotCharts have a different structure than regular charts and depend on the entire PivotCache being refreshed to be updated.  Updating the PivotCache is an operation that must be performed in Excel or an application that has the ability to do so. ExcelWriter does not have the ability to refresh the PivotCache and update the PivotCharts. The file will need to be opened in Excel (not in protected mode) in order to update the PivotCache.

ExcelWriter, Knowledge Base, OfficeWriter

Charts in ExcelWriter files are missing when viewed on iOS devices

Problem

An ExcelWriter-generated file with charts displays correctly when opened in Excel, but when the same file is opened on an iPad or iPhone, the charts are missing from the file. There are two causes for this behavior:

  1. The chart type is not supported by iOS. – Some chart types are not supported in iOS and will never appear when viewed on an iPad or iPhone, regardless of whether the file was created in Excel or ExcelWriter. An example of this is the “bubble chart.” This is an issue with iOS mobile applications – not ExcelWriter.
  2. Charts in files generated by ExcelWriter v8.2 and earlier are not visible when viewed on iOS devices. – These chart types are supported in iOS and appear correctly on iPads or iPhones when the charts were created in Excel. If the file was generated by ExcelWriter, the charts are missing when viewed on iOS mobile devices. This is a known issue and was resolved in OfficeWriter 8.3.

This article describes why the second scenario occurs and the expected behavior when viewing charts created by OfficeWriter 8.3 or later.

Solution

When a workbook is opened in Excel, all of the cell values are updated based on the current state of the file. Excel caches these most recent cell values and writes them out to the file. Many mobile apps or applications with a “preview” functionality where the file is locked from being updated, do not have the ability to recalculate the cell values and rely on those cached values to render the rest of the file, including charts.

For iOS devices specifically, if the cached cell values are not available, most applications will not render the charts at all. ExcelWriter was not writing out cached values for the cells. Starting in OfficeWriter 8.3, ExcelWriter will copy the cell values to the cached field. This allows iOS applications to render the charts.

Limitations of Rendering Charts

In certain cases, the charts will be visible but may not look as expected. This is due to limitations in ExcelWriter’s ability to write out the cached values. For more information, please see Charts in ExcelWriter files not rendering correctly outside of Excel.

ExcelWriter, Knowledge Base, OfficeWriter

Excel Charts Don’t Follow “Move and Size with Cells” Option

Problem

Excel’s “Move and Size with Cells” option allows you to automatically re-size charts if the cells that contain the chart are added or re-sized.

With ExcelWriter, when the “Move and Size with Cells” option is selected in Excel, charts are not re-sized based on rows that are added from data being imported into data markers using the ExcelTemplate object (or OfficeWriter’s SSRS integration). Charts are re-sized if rows are added explicitly using Worksheet.InsertRow.

Example 

The pictures below show a file that has “Move and Size with Cells” option selected, before and after it has been processed by ExcelWriter.

Template

KB_ChartExample1

Incorrect output – Note that the chart remained the same size as before the data was imported.

ExcelWriter Chart

The correct output should look like the following:

ExcelWriter Chart Continue reading Excel Charts Don’t Follow “Move and Size with Cells” Option →

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 →