Meet Mike, our QA Engineer. Drumming on his down time, ensuring product quality for our sister company Riparian Data, and harboring a soft spot for pugs.

1. What do you do?

I’m the QA Engineer for Riparian Data. While the role touches a lot of areas, at its most basic level it means that no change is allowed into our product until I’ve had a chance to thoroughly vet it and ensure that it doesn’t affect our product’s quality. Oh, and preferably those changes should work as designed too.

2. What are you listening to right now?

At the moment I’m listening to the new National album on Spotify. 

3. If you could build any app, what would it be and why?

I’ve always wanted an app that you could play a song into and it would spit back accurate drum tablature. When I was a kid I used to spend hours writing out the drum notation for songs I wanted to learn, and I don’t feel like anybody has released an app to make that experience any easier.

4. When you were 5 what did you want to be and why?

At that point I still had designs to be a professional baseball player. While I think modern day sabermetrics would appreciate my adept skill at being hit by pitches, it didn’t necessarily translate over into any success during my little league days.

5. What is your favorite tech blog and why?

I don’t think either would count as a tech blog, but I spend a big chunk of my day on StackOverflow and the QA Automation forums of LinkedIn. Both are great for picking people’s brains when I hit a dead end with what I’m trying to test. 

6. If you were a superhero, what would be your superhero name/power?

The Snoopist, with the power of unbridled snooping!  I may or may not already refer to myself by this name.

7. What is the last thing you bought? 

I bought my sister a cookbook filled with Kolache recipes. It’s easily the most self-serving gift I’ve ever given.

8. Name something that makes you smile.

Pug hugs.

9. If you could teach a college course what would it be?

I’ve never actually encountered a class dedicated solely to QA, so I’d love to see a QA 101 course offered somewhere. 

We are so honored (not to mention pumped) to be recognized in the Watertown Award Program this year. Thank you!

“The Watertown Award Program recognizes outstanding local businesses that serve their local market. Each year, we identify companies that we believe have achieved exceptional marketing success in their local community and business category. These are local companies that enhance the positive image of small business through service to their customers and community.“

Press Release

FOR IMMEDIATE RELEASE

Software Artisans Receives 2013 Best of Watertown Award

Watertown Award Program Honors the Achievement

WATERTOWN June 6, 2013 — Software Artisans has been selected for the 2013 Best of Watertown Award in the Retail Computer Software category by the Watertown Award Program.

Each year, the Watertown Award Program identifies companies that we believe have achieved exceptional marketing success in their local community and business category. These are local companies that enhance the positive image of small business through service to their customers and our community. These exceptional companies help make the Watertown area a great place to live, work and play.

Various sources of information were gathered and analyzed to choose the winners in each category. The 2013 Watertown Award Program focuses on quality, not quantity. Winners are determined based on the information gathered both internally by the Watertown Award Program and data provided by third parties.

About Watertown Award Program

The Watertown Award Program is an annual awards program honoring the achievements and accomplishments of local businesses throughout the Watertown area. Recognition is given to those companies that have shown the ability to use their best practices and implemented programs to generate competitive advantages and long-term value.

The Watertown Award Program was established to recognize the best of local businesses in our community. Our organization works exclusively with local business owners, trade groups, professional associations and other business advertising and marketing groups. Our mission is to recognize the small business community’s contributions to the U.S. economy.

SOURCE: Watertown Award Program

CONTACT:
Watertown Award Program
Email: PublicRelations@awardprogram.org
URL: http://www.awardprogram.org

###

I suppose I shouldn’t say, “Welcome back, privacy concerns,” as I’m sure they never left, just quietly assumed their position humming in the background and shadows of the internet noise. This week, however, they took center stage both in the healthcare space and in government news.

This week, The New York Times published an article on a significant announcement for the healthcare industry. A group of global partners spanning 41 countries and including 70 medical, research and advocacy organizations agreed to share a heap of genetic data. “Their aim is to put the vast and growing trove of data on genetic variations and health into databases that would open to researchers and doctors all over the world, not just to those who created them,” The New York Times wrote. Currently, research labs and facilities are very much siloed. Each institution has their own research within their own walls and with their own records and system of operations. There is no universal method for representing and sharing genetic data, which could lead to advanced findings in cures and other health-related research.

One reason for the lack of a central system is the sheer volume of data. There is just too much information being produced by the minute. Not only that, but it is often unstructured and not of quality (meaning information was entered or gathered incorrectly/differently, such as January being entered in as Jan, 1, 01, or January, making it difficult to analyze). While volume and quality of data is an issue, the overarching problem, or rather challenge, healthcare professionals face lies mostly in the security space. With all of that sensitive patient data, there need to be strict, infallible measures to protect that information. Along those same lines is the question of who will have access to that information.

This is especially significant as it comes at the same time of privacy concerns regarding the NSA’s reported access to granular consumer data. The NSA is taking heat on a project called PRISM. PRISM is reported to be a secret government program whereby the government can access your data from certain company’s servers. And by certain company’s, I mean tech giants like Apple, Microsoft, and Facebook. What’s most concerning about this program is the type of content the government can access through it. Instead of looking at data in the aggregate or general consumer behavior patterns, PRISM allows the government to access specific content within emails, online conversations, calls, etc. President Obama maintained the government is acting within legal measures and only doing so to protect citizens. AOL, Facebook, and others reported to be involved with this government program have turned to Twitter and their other social media accounts to deny their involvement with the program. Anonymous, an advocacy hacker group, published NSA documents today, and citizens aren’t too happy about PRISM. For the average citizen, I maintain that they don’t mind if you’re watching them, just so long as you don’t point out that you’re watching.

Issues of privacy concerns around big data are not new. Who owns and has access to all of the information you are publishing/generating/recording about yourself has cropped up again and again. Not even one year ago Facebook came under fire for changing its privacy statements and settings without properly informing users. Privacy laws are confusing and questions around who owns and should have access to data, for the most part, remain vaguely addressed and unanswered. With new initiatives like PRISM and genetic data happening within healthcare and government, you can bet privacy concerns surrounding who has access to your data and how they use that information will continue to hog the spotlight.

A couple months ago, I worked with file downloads that had filenames with non-ASCII Unicode characters, as well as special characters, such as semi-colons, commas, periods, and others (@, %, +, {, }, [, ], $, #, &, =). Here is an example of a filename that has all of those characters: 

After a couple days of online research,  I finally found a solution that worked across different major browsers like IE8, IE9 and IE10, Firefox 21, Chrome 27, Safari 5 (on Windows). These are the most up-to-date versions as of this writing. Opera mostly works fine as well; however, I noticed that it doesn’t like the space and curly bracket characters.

Without going into detail about character encodings in the HTTP headers, I’d like to mention two major pieces of information you should know that I found regarding this issue:

1. According to the section 2.3 in RFC-2183 (Content-Disposition header field), the “filename” parameter in the Content-Disposition header can only use the US-ASCII characters.

2. However, as specified by RFC-5987 and RFC-2231, the correct encoding can be included right in the header field by using the filename* syntax and then by percent-encoding the non-ASCII characters in the filename. For example, for the filename ”test-€.txt,” the content-disposition header would look like the following:  attachment; filename*=utf-8”test-%E2%82%AC.txt. Here is the .NET code that I had for writing the response header:

Response.AddHeader(“Content-Disposition”, “attachment; filename*=utf-8”” + Server.UrlEncode(fileName);

However, when I tried that syntax with my test filename  “日本人 中國的 ~=[]()%+{}@;’#!$_&-  éè  ;∞¥₤€   .txt,” I saw that all the spaces were replaced with “+” signs in Firefox, Chrome, IE9 and IE10 (“日本人+中國的+~=[]()%+{}@;’#!$_&-++éè++;∞¥₤€+++.txt”)

In Opera, it was worse. In addition to the “+” signs, the curly brackets were removed. In IE8 and Safari, the filename didn’t even appear. In the download dialog, it displayed the name of the ASP.NET page instead (Default.aspx in my case).

After that, I tried using “Uri.EscapeDataString(fileName)” instead of Server.UrlEncode() in the code above. The results were a little bit better but still varied depending on the browser.  In Firefox, IE10 and IE9, the filename showed correctly. However, IE8 and Safari still showed the ASP.NET page and now Chrome did too. As for Opera, it was still missing the curly brackets.

After further testing, I finally came up with the following solution that worked for all popular browsers that I tested with:

var headerValue = (Request.UserAgent.ToLower().Contains(“msie”))

? string.Format(“attachment; filename=\”{0}\”", Uri.EscapeDataString(fileName))

: string.Format(“attachment; filename=\”{0}\”", fileName); //for Firefox, Chrome, Safari, Opera

Response.Clear();

Response.ContentType = “text/plain”;

Response.AddHeader(“Content-Disposition”, headerValue);

Response.TransmitFile(filePath);

Response.End();

The trick was to set the content-encoding header based on the user-agent. For IE, I percent-encoded the filename using the Uri.EscapeDataString() method.  For the other browsers, I just left the filename string as is. It turned out the other browsers (at least the most recent versions as of this writing) didn’t need to use the RFC-5987 filename* syntax.

A whirlwind week of panels, classroom discussions, and events is the only way to describe Internet Week NY. Bringing together masterminds behind the digital space, Internet Week NY covered how companies from startups to non-profits to corporations like MLB are harnessing the power of online personalities to market their brand and really integrate customers into their brand experience. Much like the internet itself (and was discussed by several panels), there was much “noise” or information to sift through. While valuable, it can be overwhelming. Thus, I wanted to provide a general guide with the most helpful info. While I’m tempted to talk about famous celebrities, Joan Rivers and Erica Oyama, I’ll save those and my favorite classes for later discussions. For now, I’ll attempt to boil down the overarching themes that wove the conference together and overlapped all of the different industries (from fashion to food to sports) taking over the digital arena. For all of the entrepreneurs, advertisers, and marketers out there, these are the key takeaways of which to make note.

Key Takeaways from Internet Week NY:

• Humanize your brand - Social media and the internet in general allow for a more personalized customer experience. Not only that, they allow for customers to have an active involvement with your brand. People buy from people.
• Encourage customer involvement with your brand – In other words, create experiences that involve people with your brand, such as holding contests, recognizing fans, and providing people the ability to vote on different elements of your brand. (Fun fact: People like their name and their voice. A representative from USA Mini Cooper and a panel speaker for a crowdfunding class both touched on this. USA Mini Cooper found out people liked to name their cars, so they had a contest where people could name the next Mini Cooper. The crowdfunding class found that rewards on Kickstarter projects that used those two elements, did well. For instance, if you’re trying to raise money on Kickstarter, offer donors an incentive that involves their name or voice, such as having their name in the credits of your film.) Active involvement with your brand gives customers a sense of ownership in your brand.
• Be customer-centric – Bob Bowman from MLB said you need to be product-centric as well as customer-centric in your marketing approaches. One moment that really stuck out in my mind was when Bob Bowman emphasized that you need to be mindful and respectful of people’s time. I completely agree. In this day and age people are busy. They’re multi-tasking; they’re using multiple devices; they can choose when and if they want to listen to and interact with you; they have many activities crammed into their everyday lives. Be respectful of that, and give them quick, high-quality ways to engage with you and come back at a later time. Don’t insult your audience with low-quality, long-winded content, because that conveys to them that you don’t care about their time. Which brings me to my next point of…
• Focus on quality over quantity – The mantra, “Content is king,” still stands. Providing valuable, quality content to your audience is still an important asset to any company. That is how you attract and keep an audience. That is how you get people to pay for your content (Andrew Sorkin). That is how you get people to find your company and interact with your brand.
• Understand there isn’t necessarily a one-size-fits-all solution - Technology is constantly changing. The ways in which people are using technology are constantly changing. Therefore, the ways in which people interact with your brand are constantly changing. As a marketer/advertiser/entrepreneur, you need to be flexible and adapt to those changes. In order to reach your audience, you must be adept at moving along with them, at altering your content to fit with the different platforms and your different audiences’ needs.

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.

1. After opening the project you want to open a report in, select ‘Add Web Reference’ from the Project menu (or by right-clicking on the project name in the Solution Explorer).
2. Enter the URL of the report server in the text box. In most cases, this should be “http:///reportserver/reportservice.asmx”. Click ‘go’.
3. Visual Studio should find a single reference named ‘reportservice’. Enter a friendly name for the service in the text box on the right. For this example, I’ll use the name “MyReportService”.
4. Click ‘Add Reference’.

Creating a ReportingService Object

Adding the web reference in the last section created a special namespace that contains the object we’ll use to render the report. This namespace is .. In the example I used above this would be ReportingServiceRender.MyReportService. This namespace contains a ReportingService object that we’ll use to do the actual rendering of the report. To create a ReportingService object, use its default (empty) constructor. You can either fully qualify the class name or add a using (imports in VB.NET) statement to your code like I do in the sample below.

Note that while the default behavior is to add the report service in the project’s namespace, this is not always guaranteed, especially if the project name changes. If you add a web reference and cannot find it in the project namespace, right click on the web service in the Solution Explorer and select ‘View in Object Browser’. This will open the object browser and highlight the web service with its full namespace.

After creating the ReportingService object, we have to tell it how to authenticate itself to the report server. As long as the account your program is running as has permissions to render reports, you can use the default credentials here. If you need the code to run under a different set of credentials, you’ll need to create those at this step using the CredentialCache object.

 // Replace this name with whatever you named your reporting service instance using ReportingServiceRender.MyReportService; // ... Namespace and class declarations, etc ... ReportingService rs = new ReportingService(); rs.Credentials = System.Net.CredentialCache.DefaultCredentials; 

Calling the Render Function

The function we’ll use to get a copy of the populated report is ReportingService.Render. For our purposes, all we need are the first two arguments: the path of the report and the name of the rendering extension.

The path to a report always begins with a ‘/’, and does not include the ‘.rdl’ extension. For example, a report in the Home folder named “MyFancyReport” will have a path of “/MyFancyReport”. If we moved that same report to the “ExtraFancy” folder, the path would be “/ExtraFancy/MyFancyReport”. Note that all the slashes are forwards, as in a URL, instead of backwards, as in a Windows path.

The rendering extension is a string that tells Reporting Services what format to return the report in. By default, SSRS can render reports to a few simple formats including HTML, XML, PDF and basic, limited Word and Excel documents. To render a report as an Excel workbook with the SoftArtisans OfficeWriter renderer, use the “XLTemplate” extension. For Word documents, use the “WordTemplate” extension.

Because the last five arguments are output arguments in C#, we need to create variables to pass to the function instead of simply using null. Even though we don’t use the values in those variables, the variables must exist for the code to compile.

 // These variables are all the output variables the Render function takes. // We don't use them, but we need to create them to pass to the function. // Note that the ParameterValue array and the Warning array are members of // the ReportingServiceRender.MyReportService namespace we created earlier string optionalEncoding = null; string optionalMimeType = null; ParameterValue[] optionalParams = null; Warning[] optionalWarnings = null; string[] streamIDs; // The first argument tells Render where to find the report, the second // argument tells it what extension to use to render the report. string reportPath = "/ExtraFancy/MyFancyReport"; string reportFormat = "XLTemplate"; // The actual call to Render. The only important parameters are the first // two; the rest are either null or unused. byte[] result = rs.Render(reportPath, reportFormat, null, null, null, null, null, out optionalEncoding, out optionalMimeType, out optionalParams, out optionalWarnings, out streamIDs); 

Opening the Report in the Application Object

As you can see in the code snippet above, the Render function returns a byte array. The ExcelApplication object can’t open a byte array directly, but it can open a MemoryStream object (ExcelApplication, WordApplication. We can create a MemoryStream object by passing it a byte array in the constructor.

Once the Application object has opened the stream we can use it just like any other file, including streaming the file to users with one of the overloads of Save (ExcelApplication, WordApplication.

 // Turn the byte[] to a stream object that Open can read. MemoryStream reportStream = new MemoryStream(result); ExcelApplication xla = new ExcelApplication(); Workbook wb = xla.Open(reportStream); reportStream.Close(); // ... Modify the report with the Application object ... // Stream the rendered and modified report back to the user. xla.Save(wb, Page.Response, "RenderedReport.xls", false); 

Note that this code should be executed as part of a postback in a web form. By invoking this code through a web form you can create an interface that lets users request a report by clicking on a button or link, then stream that report back to them as though it were any other downloaded file.

Attached to this article is a zip file containing a Visual Studio 2008 project with this code in it. The project also contains a web form with a button that fires the code. Note that the web reference in the project assumes that the report server is hosted on the same machine that the code runs on, so it may need to be updated before the code will run. Also keep in mind that the project looks for a report named MyFancyReport in the /ExtraFancy folder. If you don’t have a report there, you’ll either need to create one or change the path in Default.aspx.cs.

Attachments

• SSRSPostProcessing.zip

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.

The Excel .XLSX file format supports millions of colors, so when working in newer versions of Excel, the user interface will give you the option to select any color because Excel does not know what file format you will save in.

• When creating a document using Excel 2007 and later and you save your file as XLS format, everything will appear as normal, but when the XLS file is opened in Excel 2003 the colors will revert to the closest 56 colors in the workbook palette. Depending on your application settings you may or may not receive a Minor loss of fidelity warning.
• If you save the file as an XLSX file you will not receive a warning, but when an XLSX file is opened in Excel 2003 with the compatibility pack, Excel will perform a file conversion back to XLS, which limits the workbook to the 56 colors in the palette.

Since the XLS file format is limited to 56 colors stored in the workbook, special consideration must be taken when designing a document that will be viewed in previous versions of Excel.

To ensure that colors appear the same in all versions of Excel, one can customize the workbook palette to include the colors needed. This can be done both manually, and automatically with 3rd party tools.

Solution

Creating a custom palette using Excel Application

If you are using ExcelApplication to programmatically set colors using Palette.GetClosestColor, ExcelWriter will attempt to find the closest color in the palette. If the color or similar color is not in the workbook’s palette, the colors may not appear as expected. You can use ExcelWriter’s SetColorAt method to replace a color in the palette and ensure that your color will be found.  You can also use ExcelWriter’s Palette.GetColorAt method to retrieve specific colors.  By ensuring the colors you are using are in the Workbook Palette you can ensure the document will render with the colors desired.

 ExcelApplication xla = newExcelApplication(); Workbook wb = xla.Create(); //Set a color in the Palette wb.Palette.SetColorAt(1, 240, 217, 216); //Can also use GetColorAt(1); wb[0].Cells["A1"].Style.BackgroundColor = wb.Palette.GetClosestColor(240, 217, 216); xla.Save(wb,"colors.xls"); 

Creating a custom palette using Excel

When designing a template in Excel, the colors in the workbook’s palette can be modified.

Manually creating a custom palette in Excel 2010:

• Open the Workbook whose palette you wish to modify.
• In the upper left hand corner select File
• select Options
• Select the Save menu
• Under Preserve Visual Appearance of a workbook, next to choose what colors will be seen in previous versions of Excel select Colors…
• Select a color you wish to change and press Modify
• Change the color and repeat sets as needed.
• Press OK until you are back to the Workbook

Manually creating a custom palette in Excel 2007:

• Open the Workbook whose palette you wish to modify.
• In the upper left hand corner press the Microsoft Office Button and select Excel Options
• Select the Save menu
• Under Preserve Visual Appearance of a workbook, next to choose what colors will be seen in previous versions of Excel select Colors…
• Select a color you wish to change and press Modify
• Change the color and repeat sets as needed.
• Press OK until you are back to the Workbook

Manually creating a custom palette in Excel 2003:

• Open the Workbook whose palette you wish to modify.
• From the Tools menu select Options, and then select the Color tab.
• Select a color you wish to change and press Modify
• Change the color and repeat sets as needed.
• Press OK until you are back to the Workbook

Automatically converting colors to custom palette:

Since manually editing the workbook color palette can be a time consuming process, some people have tried to automate the process.  Jon from Excel Campus has done just this with his color palette conversion tool.

By ensuring the colors you are using are in the workbook’s palette, you can ensure that your workbook will appear the same throughout different versions of Excel.

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:

• 40 Standard colors, which includes 8 system colors
• 8 Chart fill colors
• 8 Chart line colors

The 40 standard colors are available in all color selection dialogs in Excel, including the drop-down dialogs on the Formatting toolbar. In ExcelWriter, they can all be accessed through the Palette class’s “get” methods. The system colors can also be accessed through the Palette.SystemColor enumeration. For example:

 //--- Get black using the SystemColor enumeration Color black = Palette.SystemColor.Black; 

Chart Fills and Chart Lines are what Excel uses when automatically setting fill and line colors on charts. Chart fills and lines not limited to these colors however. These colors are available as options in Patterns and Chart color dialogs (i.e. Format > Cells > Patterns). In ExcelWriter, all Chart fill and line colors can be accessed and replaced using the Palette’s “get” and “set” methods.

All standard and chart colors can be replaced with other colors using the Palette class’s “set” methods except for the system colors. For example:

 //--- Replace color at index 4 with RGB values (162, 221, 138) wb.Palette.SetColorAt(4, 162, 221, 138); 

This would be equivalent to changing the color palette in Excel through Tools > Options > Color.

Palette Color Reference Charts

Provided below are two views of the default palette. The first is ordered by palette index, and the second is similar to how the palette is displayed from within Excel.

The colors in order of palette index

 //--- Get the color at palette index 9 Color darkGreen = wb.Palette.GetColorAt(9); 

The RGB and HEX equivalents of each color are provided

* The colors as displayed in Excel *

The following is similar to how the palette is displayed in Excel. To find the RGB and HEX equivalents, simply match up the palette index to the chart above.

Note: When accessing the palette through VBA, the color indexes are 1-based, not 0-based as they are when using ExcelWriter. Therefore, when using VBA, simply add 1 to the indexes listed above.

Excel version of this information

An Excel file containing similar information about default palette colors is attached to this article. You may find it useful for testing different effects on the palette when changing colors using ExcelWriter.

Related Links

• ExcelWriter Docs – The Color Palette
• ExcelApplication Palette API Reference

Attachments

Color_Palette_Example.zip

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 the specified cell, whether it is in the current sheet or another sheet.

Note about worksheet references:

Microsoft’s documentation on the HYPERLINK function has more information about how to create links in a spreadsheet. Careful readers will notice that their description of how to create links to cells in the same workbook is different from the one just discussed.

Namely, they recommend prefacing the sheet name with the name of the workbook in square brackets, as in =HYPERLINK(“[Book1]Sheet2!C3″, “Link to C3″). Unfortunately, this only works if you include the file extension with the name of the workbook: =HYPERLINK(“[Book1.xls]Sheet2!C3″, “Link to C3″).

Even then, this technique only works when the spreadsheet is saved to disk, not streamed to a user. The pound sign we used above is a special symbol that references the current workbook, no matter its name. Using the pound sign in your hyperlinks will let them reference the current workbook, even when streaming that workbook to the user.