Tag Archives: OfficeWriter

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 →

ASP.NET, Excel, Featured, News Releases, OfficeWriter

PivotTables Now Available within OfficeWriter 8.4

OfficeWriter 8.4

WATERTOWN, MA (February 12, 2013) – SoftArtisans announced the addition of PivotTables to their OfficeWriter product today. OfficeWriter 8.4 is now available for download. Included in this new version, is the ability to create, modify, and remove PivotTables within users’ Excel workbooks. With the new PivotTable functionality customers have the ability to add Report Filters to better filter and sort their data, as well as change the data source of a PivotTable (including PivotTables already copied with CopySheet). This provides users with more fine-grained control over their data and reports.

Also included in this product release are new features to their WordTemplate model. Within WordTemplate DOCX files, users now have the option to programmatically set the document properties of their DOCX files and to remove bookmarked content when delivering reports.

OfficeWriter provides customers Continue reading PivotTables Now Available within OfficeWriter 8.4 →

ASP.NET, Excel, Featured, News Releases, OfficeWriter, Programming, SQL Server, SSRS

What’s new in OfficeWriter 8.4

PIVOT TABLES ARE HERE!

OfficeWriter 8.4 packs a powerful punch with exciting new features, most notable of which is ExcelApplication support for PivotTables in OOXML (XLSX, XLSM) files. ExcelWriter already supports the use of PivotTables in ExcelTemplate and SSRS reports,  but now you can programmatically create, manipulate, and remove PivotTables with ExcelApplication.

BlogPostPivot

The new PivotTable API gives you the freedom to:

  • Create PivotTables from scratch (see our tutorial on Creating a Basic PivotTable)
  • Add and manipulate PivotTable fields – data value fields, column labels, row labels, and report filter page fields
  • Access common settings like empty/error values, refresh data when opening a file, and the number of cached items to retain

With the API you also have the ability to change the data source of a PivotTable. Continue reading What’s new in OfficeWriter 8.4 →

OfficeWriter, Programming

Enhancing your Word Document with Images Using OfficeWriter

One of the questions that comes up frequently when talking with customers is how can they get their images into their Word document with OfficeWriter? Depending on how you are using OfficeWriter and making your Word documents, there are a few ways to do this. The following outline several of these methods and cases in which to use them.

1. Design your document in Word with images

This is the easiest way to get images into your document. Since OfficeWriter can use the Word file you have already created and allow you to enhance it with data from a database, you can get all of the design functionality of Word with the enhanced capabilities of OfficeWriter. This method is commonly used when designing a document that uses your company logo, for example.

2. Dynamically insert images into a document

Another option is to design your document in Word, but leave a place holder for a dynamically loaded image. (A quick overview on how to do this can be found here.) This method is great when the document is structured the same each time it is run and you want to keep your company logo static, but use different department logos.

3. Design your report with images stored in a database

You can use one of two methods to directly insert images into a Word template from an image column: the placeholder method or the the image modifier method.  For example, in MS SQL Server, there is a column type “varbinary” used just for that purpose. This method is perfect for creating an employee directory containing the employee’s picture along with their contact information, or for a product catalog, showing the product alongside the product details. (For further details on how to to design your report with images stored in a database, please see our documentation on inserting an image.)

4. Design your report with images referenced by PATH in a database

This is the more complicated case of the bunch. Unlike the previous instances where you were obtaining your images directly from a database, this process involves accessing your images stored in another location, such as a network share. Continue reading Enhancing your Word Document with Images Using OfficeWriter →