Category Archives: OfficeWriter

Excel, ExcelWriter, Knowledge Base, OfficeWriter, SQL Server, SSRS, WordWriter

How to open a report from SQL Server Reporting Services with the Application object

Problem

You want to modify your reports with the WordApplication or ExcelApplication object after they’ve been populated with data by SQL Server Reporting Services. The ExcelApplication or WordApplication object’s Open method can’t pull a report from a report server, so how do you open a SSRS report with OfficeWriter?

Solution

For simplicity and ease of reading the code examples and text in this article refer to the ExcelApplication object exclusively. However, the exact same techniques can be used with the WordApplication object. The only strict requirement for opening reports from SSRS is for the Open method to have an overload that takes a Stream argument, which both objects have.

SQL Server Reporting Services exposes a web service that lets programs access and work with the reports on the server. The web service has a number of functions for interacting with a report server and in fact is the same API that the Report Manager tool is built on top of. One of its functions will let us retrieve a fully rendered report that we can then pass to the ExcelApplication object. Once the ExcelApplication object has opened the report, it can be used as if it were any other file.

Getting a Reference to the Report Server

There are a number of different ways to access a report server to manage your reports. The MSDN documentation has detailed instructions for all the different techniques. The most powerful way, and the one that the Report Manager application uses, is to use the report server’s web service, or SOAP API. The easiest way to use the web service is to add the report server as a web reference in Visual Studio. This will allow us to make web service calls to the report server as though it were any other object in a .NET project. I’ll provide a brief overview of the steps to add a report server as a web reference; details can be found at MSDN. Continue reading How to open a report from SQL Server Reporting Services with the Application object →

Excel, ExcelWriter, Knowledge Base, OfficeWriter

Workbook colors are not displayed as expected in older versions of Excel

Problem

When using ExcelWriter’s ExcelApplication object to generate a new workbook, custom colors that are assigned in code to fonts, charts, cell backgrounds, etc., display incorrectly on the client when the workbook is generated.

OR

When creating an Excel File or an Excel file to use as a template with the ExcelTemplate object using Excel 2007 and above, colors are not colors are not preserved in Excel 2003 or older.

Continue reading Workbook colors are not displayed as expected in older versions of Excel →

Excel, ExcelWriter, Knowledge Base, OfficeWriter

Excel’s color palette explained

Problem

Excel’s color palette contains 56 colors, all of which can be accessed and most of which can be replaced using ExcelWriter (v6 or later). This post describes the layout of the palette and enumerate the default palette colors.

This content is most pertinent to the Excel 2003 color palette, which only has 56 colors. In Excel 2007 and later, workbooks can support millions of colors, but there is still an underlying workbook palette that has 56 colors. For more information about colors in multiple versions of Excel, we have enough post about workbook colors that are not displayed properly in older versions of Excel.

Solution

The palette is split up into a few different sections: Continue reading Excel’s color palette explained →

Excel, ExcelWriter, Knowledge Base, OfficeWriter, SQL Server, SSRS

How to create hyperlinks between cells in an Excel spreadsheet

Problem

Your application requires cells to contain hyperlinks to other cells in the same spreadsheet, but the Cell.CreateHyperlink function only makes links to URLs on the internet. This article discusses a workaround using Excel’s HYPERLINK function.

Solution

Excel has a function named hyperlink() that, with some special syntax, can create links to other cells in a spreadsheet.

Normally, the hyperlink function creates a link to a URL, much like the CreateHyperlink function. Simply passing the name of a cell to the function causes it to form a bad link; Excel will interpret it as a URL.

The solution is to enclose the name of the destination cell in quotes and preface it with a pound sign. For example, a cell with formula =HYPERLINK(“#Sheet2!C3”, “Link to C3”) will contain the text “Link to C3”, and function as a hyperlink to that cell in Sheet2. The formula can be set either by editing an Excel file directly, or through setting the Cell.Formula property in ExcelWriter.

When the user clicks on the link, Excel’s focus will move to Continue reading How to create hyperlinks between cells in an Excel spreadsheet →

Excel, Featured, News Releases, OfficeWriter, SharePoint, SQL Server, SSRS

What’s new in OfficeWriter 8.5

Spring has sprung, bringing with it our newest release of OfficeWriter: OfficeWriter 8.5!  What’s in store for this maintenance release? Scroll down to see the latest additions our development team has been working on.

WordTemplate – Embed DOCX files into templates

In OfficeWriter 8.0, we added the ability to embed RTF or HTML documents in Word files with WordTemplate.  The feature uses the document modifier to signify that a RTF or HTML document will be inserted. To learn more about using the document modifier, see our guide on inserting an embedded document under our WordTemplate Tutorials.

HTMLtoWord

We have also extended the feature to include DOCX files. Now you can embed other Word documents into your WordWriter templates. Continue reading What’s new in OfficeWriter 8.5 →

OfficeWriter, SQL Server, Windows Server

How to Find Your OfficeWriter License Keys

First Things First: What do we have, and where is it?

If you’ve ever gone through a licensing review or license audit, you know that sometimes the hardest part of the whole process is information gathering. What keys do you have? Where are they installed? What is still under support, and what is not? Are we overpaying or over-provisioned? These questions and more can drive a sysadmin to insanity, especially if your records are less than perfect.

While no method can substitute for proper record keeping, I’m here to show you how to find any OfficeWriter product keys that may be installed on your servers. Basically, what I am looking to do is get a list of license keys, versions, and where they are.

Prerequisites

SoftArtisans stores all of its license keys in HKCR:\Licenses\Softartisans. We could manually open RegEdit on all our machines and find the keys, then copy them out into an Excel spreadsheet, but that would take WAY too long, even for my development environment.

Before we begin scripting, I first got a list of all the machines I wanted to check. Continue reading How to Find Your OfficeWriter License Keys →

Events, Excel, OfficeWriter, Programming

March Madness: Predict Your NCAA Basketball Brackets with Excel

Every year in March, the NCAA hosts the college Men’s Basketball Tournament. Every year, I go through all of the teams, create my brackets, and see if I can do better than my friends. My one downfall is I always have my favorites: you know, the teams you follow because you went to school there, the team your significant other cheers for, your alma mater’s arch-nemesis, or the team with that really cool mascot. Whatever the reason, the biases end up costing you the first round, sidelining you out of the pool. What we need is a simple way to generate our bracket and remove some of that bias.

Instead of using a totally random outcome, let’s use a few of Excel’s analytical features to increase our chances of putting together a winning bracket. Let’s take Mens NCAA Basketball tournament data since 1985 (courtesy of The Washington Post‘s database), that has the history for specific seeds, teams, coaches and conferences – everything from each school’s tournament results to how No. 7 seeds have fared against No. 10 seeds in the first round. Using this data as a starting point allows us to inject reality into our random numbers. Because really, it is not ever worth considering if a 16 can beat a 1, right?

After copying and pasting the data from the webpage into an Excel spreadsheet, it’s time to input the formulas. It’s basically a two-step process. What we need to calculate is the probability of a particular seed winning or losing. If you hadn’t seen it before, a 9 seed beets an 8 seed more often than not, and a 1 seed has never lost. The pivot table I created reflects that, proving to be a useful tool in our selection process. It’s not too difficult to create a pivot table by seed and win/loss and by round for our source data. (See the workbook attached at the end of this post.)

First Pivot Table

Next, we develop a formula that uses the probabilities along with the RAND() function to predict the outcome of a match-up. All we need to do is apply the random number to the pivot table data to determine which of the two seeds advance to the next round. There is no easy way to do this, except with a long and complicated formula. Luckily, most of the formula is calculated by Excel by doing a simple click. The two Excel functions that get this done for us are the RAND() and the GETPIVOTDATA(). RAND() is well documented, but the GETPIVOTDATA() allows us to treat the pivot data like a database to get our probability for a seed to win the match-up.
Bracket

After playing with the output for a few runs, I noticed that the later rounds are fully dominated by the higher seeds. That happens because of the limited data for lower seeds in the later rounds. I want to allow for those Special Case teams to triumph over the Big League teams, so I added Continue reading March Madness: Predict Your NCAA Basketball Brackets with Excel →

Events, Featured, OfficeWriter, Programming, SQL Server, SSRS, Webinars

[Webinar] Making Reporting Easier with SSRS Designer Ribbon

SSRS Webinar

With data sets tied to different data sources and multiple people handling one report, the process of building a report can get messy. OfficeWriter’s built-in Designer Ribbon makes it easier to interact with SQL Server Reporting Services (SSRS), helping you access reports from your applications on time and with ease. Join us Friday, March 22nd as our Senior Sales Engineer, Chad Evans, shows you how this feature makes using SSRS simpler.

In this webinar you will learn:

  • How to build an SSRS report from start to finish using the OfficeWriter designer ribbon
  • How to easily open existing reports, create new ones, and view reports in process with SSRS

We will also have a question and answer period at the end of the webinar. Feel free to send in questions prior to the webinar so that we can include them in the presentation.

When: Friday, March 22nd at 1 P.M. EST

Can’t attend, but still want a copy of the recording and slides? Register below and we’ll email it out following the webinar.

**Spots are limited. So please register early to secure your seat.






ASP.NET, Big Data, OfficeWriter, Programming, SQL Server, SSRS

Big Data and OfficeWriter

Big Data DemosWe partnered with Andrew Brust from Blue Badge Insights to integrate OfficeWriter with Hadoop and Big Data. Taking existing OfficeWriter sample projects, Andrew discusses how he created two demos showing OfficeWriter’s capabilities to work with Big Data. One demo uses C#-based MapReduce code to perform text-mining of Word docs. The other demo focuses on connecting to Hadoop through Hive.

In these demos you will learn:

  • How OfficeWriter integrates with Hadoop and Big Data
  • How to use ExcelWriter with Hadoop