Category Archives: Excel

Excel, OfficeWriter

Pitan Pivot Mage and the Quest to Slay the Bracket Problem

All is not quiet on the home front…

“Cannot open PivotTable source file” – If you have ever encountered this error message, then you know the frustrations of The Bracket Problem. If you haven’t run into this issue before, let me paint you a picture:

There some sort of pivot report on a server, for example, a report that’s being dynamically generated for Excel using OfficeWriter. An end user decides to download the report on her work machine with IE 7. When she opens the workbook, instead of the pivot table refreshing, she gets a warning message:

Cannot open PivotTable source file: ‘C:\Users\Pitan\AppData\Local\Microsoft\Windows\Temporary Internet Files\….\ [PitanPivotReport[1].xlsx]Sheet1’.

When she tries downloading the report using Mozilla FireFox, the warning message doesn’t occur.

This, my friends, is The Bracket Problem.

What trickery is afoot? Continue reading Pitan Pivot Mage and the Quest to Slay the Bracket Problem →

Excel, OfficeWriter

Pitan Pivot Mage: The Mystery of the ‘Replace Content’ Message

I was working with a formatted Excel report and I needed to add a pivot table. Easy-peasy, I added my lovely little pivot table with a pivot chart. Since I would be importing the data into the pivot table later, I made sure to place the pivot chart outside of the cells that the pivot table would occupy once it was populated with data (to avoid overlap). I saved my template and went to generate my report.

When I opened the output, I was greeted with a mysterious message from Excel:

 

I was baffled because I knew that nothing should overlap with the pivot table or pivot chart, even with after the data had been imported. Continue reading Pitan Pivot Mage: The Mystery of the ‘Replace Content’ Message →

Excel, ExcelWriter, Knowledge Base, OfficeWriter, WordWriter

Excel or Word file generated by OfficeWriter opens in protected view (yellow)

Problem

A report generated using ExcelWriter or WordWriter opens and there is a yellow warning banner saying that the file has been opened in Protected View.

Solution

Starting in Office 2010, Microsoft introduced a security feature that scans files before opening them in Excel or Word. If there is something wrong with the file format definition, it will open in Protected Mode with a red warning banner.

One of the common scenarios for opening a file in Protected View is opening the file from an unsafe location, such as the Temporary Internet Files folder. This means that if the file is streamed to an end-user, it will open in protected view.

For more information about Protected View settings in Office 2010, please refer to this Microsoft TechNet article on how to configure Protected View settings in Office 2010.

Excel

Macro Security Settings in Excel 2007 and 2010

What is the problem?

A customer was exporting Excel reports by OfficeWriter to the XLSM format and was alarmed when he opened his output and saw a security warning:

Some active content may have been disabled:

 

 

Obviously, anyone who hadn’t experienced the Excel Trust Center Security Warning before could easily have been alarmed by this.

What is going on?

The security prompts are caused by the settings in Excel’s Trust Center, which was introduced in Excel 2007. Basically, when a workbook is opened in Excel 2007 or 2010, it is automatically run through the Trust Center feature for security reasons. Excel’s behavior for handling workbooks with macros will change depending on the settings selected in the Trust Center.

Some notes about Trust Center security warning messages
  • Some active content may have been disabled indicates that there are multiple types of active content that have been disabled, not just macros. If the workbook contains just macros, the security warning will read Macros have been disabled.
  • In Excel 2010, the security messages are the same, but the coloring makes the warning more obvious. For more information about the difference in the security warning between Excel 2007 and Excel 2010, check out this article.

To get to the Trust Center: Office button > Excel Options > Trust Center > Trust Center Settings. And on my left, Macro settings:

 

For example, say the ‘Disable all macros with notification’ option is selected. This is the default macro security level for Excel 2007/2010 and this setting will prompt the user to enable macros anytime the workbook is opened. The end-user can change the security setting to ‘Enable all macros’, and then macros will run without the prompting.

For more general information about Macro security settings, please refer to this Microsoft article: http://office.microsoft.com/en-us/excel-help/change-macro-security-settings-in-excel-HP010096919.aspx

I am developing a workbook with macros. How can I avoid having the Trust Center prompt my end-users?

A note about macro security settings
Enabling macros to run without prompts could be potentially dangerous if a workbook contains malicious macros. The security prompt is designed to prevent macros from running unless the user decides they are safe to run. Each of the following suggestions will allow macros to run without prompting, which may harm your computer if the macros are malicious

Since the Trust Center’s settings are determined on a per-user basis, depending on which security settings the user has selected, the behavior the end-user experiences may vary. Here are some ideas for avoiding the Trust Center security prompt:

Digitally sign the macros (recommended)

One of the macro settings is to ‘Disable all macros except digitally signed macros’. This means that if the macros are signed by a Trusted Publisher, Excel will run the macros without any security prompts. All unsigned macros will still prompt the user to enable them, but they will not be prompted for workbooks that contained macros signed by the Trusted Publisher.

This Microsoft article explains how to digitally sign a macro project: http://office.microsoft.com/en-us/excel-help/digitally-sign-a-macro-project-HA001231781.aspx

After you have signed the project, the end-user will need to add you as a Trusted Publisher. This is explained below.

The first time the user opens a workbook with signed macros, they will be given the following options:

  1. Help protect me from unknown content (which is to leave the macro disabled)
  2. Enable this content (enable for one-time use)
  3. Trust all documents from this publisher (enable all macros from this publisher).

Instruct the end-user to select Trust all documents from this publisher, which will add the publisher who signed the macros, you, as a Trusted Publisher. All subsequent workbooks with macros signed by you will not display security prompts when opened on the end-user’s machine.

For more information about Trusted Publishers, please refer to this Microsoft article: http://office.microsoft.com/en-us/excel-help/add-remove-or-view-a-trusted-publisher-HA010034138.aspx?CTT=5&origin=HA010167227

Use a trusted location (not recommended in some cases)

If a workbook is opened from a trusted location, it does not go through the Trust Center. This means that regardless of the Trust Center settings, Excel will run the macros in the workbook without any security prompts. Trusted locations should always be secure and trustworthy.

If your end users are opening the workbooks directly from the browser cache (e.g. the workbook is streamed to the client from a web application), DO NOT tell them add their browser cache as a trusted location. The browser cache is an easy target for hackers who can force the browser to download potentially comprimised workbooks. This is not to say that trusted locations are never recommended. For instance, your end-users could specifically designate another location as trusted, and copy any files to that location before opening them, which is the secure way to use trusted locations to avoid the security prompts.

Here is a Microsoft article that discusses trusted locations: http://office.microsoft.com/en-us/excel-help/create-remove-or-change-a-trusted-location-for-your-files-HA010031999.aspx?CTT=5&origin=HA010167227

Enable all macros (not recommended)

This option runs all macros without prompting. This is NEVER recommended because it potentially exposes the end-user to malicious macros, even if your macros are safe.

Macro Settings in Excel 2003

If you are using Excel 2003, the macro settings will be different. Excel Hints has a good overview of those settings here: http://excelhints.com/2010/08/06/adjust-the-security-level-in-excel/.

Excel, OfficeWriter

Pivot Tables Version Nightmare

Pivot tables can be tricky by nature and a significant portion of my support work has been dedicated to helping customers incorporate pivot tables with ExcelWriter.

One customer wrote in with the following issue: his client had recently imposed a constraint that all reports had to be in the .xls format, so he needed to convert several OfficeWriter reports designed in Excel 2007 to the 2003 format, but he was having trouble switching the pivot tables.

When he tried to save the 2007 format report as a 2003 format report, the pivot tables were locked in a read-only state, wherein users could only edit the pivot tables if the report was resaved in the 2007 format. After doing some testing in Excel 2003 and 2007, I found that this was the native behavior for Excel files as well. Continue reading Pivot Tables Version Nightmare →

Excel, OfficeWriter

Adding Headers to a Grouped Spreadsheet

Introduction

In previous blog posts, I showed how José and Jillian, salespersons with PostOne Bicycles, used the new %%group marker to group and nest hierarchical data from a flat data set. In this tutorial, they will expand upon the concept by introducing headers to help separate each group, using the %%value() marker to show data values in headers and the (hide) modifier to hide data that is already shown in a header.

If you’re not a current ExcelWriter user, you can download a free evaluation and follow along!

Setup

This tutorial uses the same code and data as the original blog post. The template used in this tutorial starts where the previous blog post left off.

Adding Headers

Headers in ExcelWriter grouping and nesting are repeated for every new value in a grouped column. José marks the beginning of a header block is by using a %%header marker; the block is marked by the next %%header block (if you have nested headers) or the data marker row, as in this case. Continue reading Adding Headers to a Grouped Spreadsheet →

Excel, OfficeWriter

Excel Binary HFPicture Record: Parsing Pictures in Headers and Footers

We have recently added support for inserting and retrieving images from headers and footers section of an Excel spreadsheet. To enable this, we needed to parse the HFPicture records, which turned out to be a difficult task, especially due to lacking documentation.

When a user adds a picture to a worksheet header or footer section, there is no change to the HEADER or FOOTER records (except for the addition of the &G or &[Picture] tokens to the text). So how are the pictures added to file, and how are they linked to the specific header or footer section?

Let’s start by examining the HFPicture record – we will find that, oddly enough, the HFPicture record is simply a wrapper around the DGContainer or the DGGContainer. Which of these it is is determined by the HFPicture’s fIsDrawing and fIsDrawingGroup bits, and if this HFPicture record is a continuation of a previous HFPicture (due to the size limit of the records), the bit fContinue will also be set. Therefore, an image in a header/footer section will be represented very much like regular images. Continue reading Excel Binary HFPicture Record: Parsing Pictures in Headers and Footers →

Excel, OfficeWriter

Grouping and Nesting on Multiple Columns

Starting in version 7.1 of ExcelWriter, you can use grouping and nesting markers to display flat data in a grouped and nested format using the ExcelTemplate object and a OOXML (.xlsx) template. Not a current ExcelWriter user? No problem: you can download a free evaluation and follow along!

Introduction

In my previous post, I showed how José, a salesperson at PostOne Bicycles, was able to easily pick out the sales made by the sales team in different regions by using a %%group marker. His coworker, Jillian, wants to go further than that, breaking down sales first by region, then by Salesperson, so that she can see from a glance whose sales are strong or weak in each region, and whether she needs to improve her performance. That’s easy to do thanks to the nesting feature of ExcelTemplate grouping and nesting.

Nested grouping with multiple groups

Jillian can group by multiple columns with nesting by placing additional %%group markers in more columns. Continue reading Grouping and Nesting on Multiple Columns →

Excel, OfficeWriter

Using ExcelTemplate Grouping

Starting in version 7.1 of ExcelWriter, you can use grouping and nesting markers to display flat data in a grouped and nested format using the ExcelTemplate object and a OOXML (.xlsx) template. Not a current ExcelWriter user? No problem: you can download a free evaluation and follow along!

Introduction

If you have hierarchical data in an Excel report, grouping the data is a good way to make it easier to read. ExcelTemplate now features grouping a nesting options that make it easy to group hierarchical data. The basic feature of ExcelTemplate grouping and nesting is removing unnecessary repeated values from a field if it is given hierarchical data in a flat format. We’ll focus on a single group in this post; for information on grouping and nesting multiple groups, you can check our documentation, and be sure to check back here for further blog posts. Advanced features such as headers and footers are also discussed in our documentation and will also be described in a future blog post.

Creating a Data Set for grouping

Though grouping and nesting is meant to show hierarchical data, the input data for a template with grouping and nesting must be in flat format. This means that a data set for nesting and grouping will have repeated values for fields that are higher in the hierarchy. In this case, salesperson José Saraiva of PostOne Bicycles is faced with sales data that looks like this: Continue reading Using ExcelTemplate Grouping →

Excel, Programming

Reverse Polish Notation

My knowledge of reverse polish notation came in handy today. It’s weird how these things come up in real life and not just in an academic setting. Granted, not every college graduate deals with the Excel file format.

Excel’s formulas are stored in binary spreadsheet files in reverse polish notation, which makes sense. There aren’t usually numbers in the file which say how many arguments a function contains, so we have to know that in advance to be able to understand the formula. You evaluate the arguments of a formula before the formula itself, so the stack at the point of evaluating the formula itself should contain exactly the number of arguments the formula has. It’s elegant when it works, but it makes it difficult to figure out when you have an error (unlike in XML where everything has boundaries)

Usually, if you read in more arguments than are available, an exception is thrown, and if you read in too few, an exception is thrown when evaluation completes and you have extra tokens to read. You could theoretically read too few arguments at one point and too many at another and just have an invalid result, but that would require two errors happening simultaneously, which is (hopefully) improbable.