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.

Email, RSS Follow
Email

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.

Email, RSS Follow
Email

Featured, PowerPointWriter

Announcing PowerPointWriter Beta

Join the beta

PowerPointWriter, the template-based way to merge your data with PowerPoint presentations, has arrived. Take your company data and merge it with PowerPoint to create custom presentations. We’re looking for beta testers to try out this new technology.

What is PowerPointWriter?

  • PowerPointWriter is a template-based approach to your presentations.
  • It allows you to build presentations programmatically with 5 lines of code.
  • No more add-ins. PowerPointWriter is a server-side solution to your custom presentations.

Learn more by joining the beta. You’ll receive a special reward just for signing up. Still curious? Take a quick look at our overview video!

 and 



Email, RSS Follow
Email

Programming

How Not Using Stack Overflow Solves All of My Problems

1 Comment

[OfficeWriter developer and Stack Overflow user, Seth, lets us in on the thought process and psychology behind Stack Overflow, by delving into one way he uses the online community to answer his programming questions. Anything sound familiar? Or relatable? Do you use Stack Overflow in a similar fashion? Let us know in the comments section.]

stackoverflow_post_seth

Stack Overflow is a question and answer site for professional and ardent programmers. It’s a part of the Stack Exchange network of Q&A sites. Their aim is to assemble a resource of quality answers to any and all questions pertaining to programming.

There are a lot of great things about Stack Overflow:

  • It’s free.
  • The point system incentivizes people to help others.
  • The best answers have the highest visibility (usually).
  • There are experts and legendary programmers answering your questions.
  • It’s almost like a sport or competition for those answering questions.

But my favorite thing about StackOverflow is often overlooked: fear.

I can’t tell you how many times I have been so afraid that my question will get downvoted into oblivion that I end up finding the solution while writing it. (I’ll give you a hint: it’s way more than the actual number of questions I’ve posted.)

You see, posting a question on SO is like a code review to the umpteenth power. You’re opening up, not only your code, but your thoughts, explanations, and questions to be scrutinized and judged by anyone in the world. And when you throw tags on your question, you’re basically begging people who know a lot about the subject to come and take their turn.

Also, an increasing number of companies list jobs on careers.stackoverflow, and even more companies have asked me if I have an SO profile…that’s pretty daunting. Companies may be looking at and analyzing my knowledge (or lack thereof) to determine if I’m worthy of their employment!

Not only are prospective employers trolling about there, but the people on SO are my peers. They may end up being co-workers of mine one day. I don’t want them to think I’m an idiot!

And let’s not forget about ego. I worked really hard to get my measly ~2,000 points. It may not seem like much when compared to the Jon Skeet’s of the world, but I answer a lot of questions and get no points, and the questions I ask are usually really domain-specific and never get upvotes. It took me years to get that many points! Because I worked hard for my points, Continue reading How Not Using Stack Overflow Solves All of My Problems →

Email, RSS Follow
Email

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 →

Email, RSS Follow
Email

Knowledge Base, OfficeWriter, WordWriter

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);
Email, RSS Follow
Email

Knowledge Base, OfficeWriter, Programming, WordWriter

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 →

Email, RSS Follow
Email

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? →

Email, RSS Follow
Email

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/

 

Email, RSS Follow
Email

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? →

Email, RSS Follow
Email

Blogged