All posts by Gabe Goodwin

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.


Office Life, Programming

5 Ways to Make Programming Less of a Nightmare

Credit: HubSpot

Programming can be scary.  There are plenty of things that can go wrong, from haunting errors to seemingly supernatural result inconsistencies.   To help keep the ghosts in the machine from getting the best of you, here are some tips my co-workers and I have picked up over the years.

 

 1)  Make a rough draft

Just like writing a paper, programming requires some forethought.  When you’re given a new programming problem it is always tempting to start coding right away, especially if you are in a rush.  However, taking the time to make an outline of your program first will save you lots of headaches later.  Even if it is a seemingly simple problem, think about what methods and helper methods you are going to need, what variables they will take, and how they will interact.  It is generally easier to debug code errors than logic errors, so work out the logic as much as possible before starting to code.

2) Listen to music

Having also tried TV, audio books, and silence, the best way I have found to stay productive while coding is to listen to some upbeat music.  It seems I am not alone in this opinion, as the internet has many playlists labeled specifically for programming. (In fact, we have a few of our own you might like.)  Music drowns out background noise but can easily be tuned out, keeping you focused and helping the time go faster.  So to prepare for your next coding marathon just put on your headphones.  Before you know it you will be halfway through your code and typing in beat to the music.      Continue reading 5 Ways to Make Programming Less of a Nightmare →