How to make the pie chart display larger

Problem

There is a known issue with pie charts that are created with ExcelApplication in the binary XLS file format. Pie chart display was improved in ExcelWriter 8 for the XLSX file format as part of the ExcelApplication OOXML implementation.

This post addresses how to improve the size of a pie chart in an XLS file.

Solution

There are two approaches for increasing the size of a pie chart:

Approach Before After
Increase the size of the chart while maintaining the proportions between the pie graphic and the chart frame. This requires making the chart (as a whole) larger.
Increase the size of the chart in relation to the chart frame. This requires making adjustments to the chart’s plot area.

Option 1: Increase the pie chart and maintain current proportions

If the pie chart graphic displays too small, increase the chart’s area by providing more room for it in Add method of the Charts object. The size of the pie graphic in the chart is directly related to the amount of space provided for the chart.

To provide more area for the chart, after adding the chart with the AddChart method:

  1. Make the chart wider by increasing the Chart.Width property
  2. Make the chart taller by increasing the Chart.Height property

Option 2: Change the proportion of the chart size in relation to the chart frame

Use the PlotArea‘s Height and Width properties. The values you can provide range from 1 to 4000. 4000 makes the chart take up the maximum area possible. In many cases, this value will be too large. For the area provided in the above code, 2500 is a good value for the PlotArea Width and height.

OfficeWriter and Excel Services: When to Use Which

Current and potential customers often ask our sales team: “what is the difference between OfficeWriter and Excel Services?” The high level answer is that that they are very different products designed to address very distinct scenarios. In brief, OfficeWriter is a tool that developers can use to generate, read, and manipulate Excel and Word documents in .NET code. Excel Services is a SharePoint-based server product for sharing, managing, and securing Excel workbooks.

Note that the title of this article is “OfficeWriter and Excel Services”, not “OfficeWriter vs. Excel Services”! There are scenarios where they are complementary to one another, and can be used together to solve some pretty interesting problems.

Excel Services Core Capabilities

Excel Services is a server-based workbook sharing, viewing, and calculation service. It’s a server product that is part of the SharePoint platform. It requires an enterprise-level SharePoint license to use.

The features discussed here pertain to Excel Services in SharePoint 2010, which is the latest version. Excel Services focuses on several key scenarios:

  1. Sharing and viewing workbooks through a browser– Excel Services allows for thin, web-based viewing of Excel 2007 and 2010 workbooks. This feature works only with modern XLSX files, not the legacy XLS files. The key benefits of this are:
    1. Excel is not required to be installed in order to view the file since it’s rendered in a web-based view
    2. Web-based views are interactive to a certain extent. Certain features including filtering, sorting, and page-level slicers are supported.
    3. Since an XLSX file viewable by Excel Services is just another item in document library, it can be secured and managed with SharePoint permissions just as any other document.
    4. The web-based view can be hosted in SharePoint webparts to become part of a larger dashboard.

    Continue reading OfficeWriter and Excel Services: When to Use Which

NEUGS Part 2: Whatcha Gonna Do with All That Junk

In Part 1, I introduced you to the main capabilities of SharePoint, and the parts through which these capabilities are manifested. Now, as promised, I’m going to walk you through some of the basic end-user tasks: creating new sites, uploading documents to a library and editing them, and adding items to a list.  Why these tasks? Because, essentially, they are the gist of what you will need to do on SharePoint.

1.      How to create a new site

SharePoint, like honeybees and the British, operates on a hierarchical system. At the top of this hierarchy is the portal, which is the container that houses all your organizations’ sites, subsites, pages, mysites etc… Assuming you have a portal, please navigate to it (http://portal). Good. Now across the top navigation bar you’ll see links to whatever sites your org already has going. These are team sites. Click on one of them (in my case, it’s “Marketing”).  Now, see that button in the top lefthand corner that says “site actions?” Click it and from the drop-down, select “create new site.” Now you’ll get a pop-up menu with all the different site templates you have at your disposal. Decide which one you need, and give it a title and url, then click create. I’m going to go with “document workspace.”

 

 

 

 

 

 

2.       How to upload a document/spreadsheet/ppt etc to a SharePoint library

In case I forgot to mention this, documents are stored in libraries. All files are stored in libraries. Navigate over to your new site or any other site of your choosing that has a document library or the ability to have a document library. Click on the lefthand link that says “libraries.” It will be empty save for “Shared Documents.” “Shared Documents” is your default library, so that is where we’re going to upload to today. Click the link and then click “add document.” You’ll be guided through a routine doc upload process.

 

 

 

 

 

 

 

 

3.       How to edit a document in SharePoint

If you want to edit a document in SharePoint, just click it and change the prompt toggle form “read only” to edit. If you want to make sure nobody else makes edits while you have it, check out the document first. You do this by clicking the check box next to your document and then choosing “check out” from the ribbon options above. After you’re done editing the doc, save it and it will ask you if you want to check it back in, which you do.

 

 

 

 

 

4.       How to add an item to a list

Again, in your new site or any other site with lists, click “lists” and then select one of the available lists. I’m going with “tasks.” Because I’ve just created this new site, there are no tasks yet. Click “add new item.” A dialog box will come up asking you to at the very least give your task a name. Do so, and then assign it to someone. You can also give it a priority level, a predecessor (eg a task that needs to be completed first), a due date, a status and a % complete. When you assign the task to someone else, they’ll get an email in outlook, or they should if your SharePoint has been properly configured by someone other than yourself.

Done? Congratulations! You now know the nuts of your SharePoint bolts. Stay tuned for Part 3, wherein we’ll take on customization.

 

Exporting SQL Server Database Data to Excel

Introduction

The need to get business data from a SQL Server database (or any other kind of RDBMS for that matter) into Excel for further analysis is universal.  I would venture to say that anyone in any business role from marketer to analyst to salesperson to executive to program manager has had the occasion to say: “I need this data in Excel so that I can crunch it!”

Since getting data into Excel is a common requirement, it stands to reason that there are several different ways to accomplish the task.  For simple, non-stylized, or purely tabular data imports (situations where you don’t really care about styles or fonts or corporate branding) Excel’s out-of-the-box data import solution can be an option.  For more sophisticated scenarios that require a specific look and feel, or something more complex than just straight tabular data dumps there is OfficeWriter.

This article will discuss both methods for getting your data from SQL Server into Excel.

Methods for Exporting Database Data to Excel

As I mentioned there is more than one way to skin the cat here.  Which method you choose depends on what you need to accomplish.

Method 1: Excel-based data access

Continue reading Exporting SQL Server Database Data to Excel

NEUGS Part 1: Welcome to the SharePoint Jungle

Before I came to SoftArtisans, I’d never heard of SharePoint. (You can gasp here or save it for later in the post.) As is my wont, I began using it without ever reading any documentation or general how-it-works-for-essentially-tech-illiterate-fools-type information. Which, in terms of doing most of what I need to do (uploading docs to libraries and writing blog posts on my My Site), is not the worst strategy, but it left a lot of gaps. So, with Ben’s encouragement, I recently began a comprehensive SharePoint-for-the-End-User curriculum. And, to my surprise and chagrin, found that there really isn’t one. Don’t get me wrong, End User SharePoint is an amazing resource—but I’d say it’s more tailored to post-bacs. Microsoft used to have a series of training videos, but they seem to be down at the moment, and their getting started articles are pretty skimpy early on and fragmented after the ABCs. So, like any great innovator (if you’ve been holding in that gasp, you can let it out now), I decided to create my own guide. Welcome to part one of many: What SharePoint does for me and which of its parts I will use. Continue reading NEUGS Part 1: Welcome to the SharePoint Jungle

Downloading Excel file: Internet Explorer cannot download ‘page’.aspx

Once upon a time…

I was helping a customer with his web application, which was dynamically generating Excel workbooks, but he was running into a complication that was happening on his site but not others. On this particular site, every time he tried to download one of the workbooks, he got the following error message from Internet explorer:

Internet Explorer cannot download <page name>.aspx from <server>. Internet Explorer was not able to open this Internet site. The requested site is either unavailable or cannot be found. Please try again later.

Oh, goodness.

The plot thickens…

This message can occur if Internet Explorer attempts to open a downloaded file in Office, but is unable to save the file to the local cache directory. The most common reasons for the behavior are: Continue reading Downloading Excel file: Internet Explorer cannot download ‘page’.aspx

Students Cannot Live on Bubble Sheets Alone

I originally drafted this as a comment to Claire’s excellent post, but it got long, so I posted it here instead. You should really go read that first.

While the need for education reform is obvious, I think there are two conflicting ideas when you talk about the OECD and PISA and Walter Percy and dogfish. I loved that essay by Percy, and it’s absolutely clear to me that a number of subjects are damaged, in some cases irreparably, by being approached as an object of study. Look at this forum thread: it’s eighteen pages of people slagging many books which are, often rightly, considered some of the best literature has to offer. You might not be able to fix The Fountainhead with a dogfish, but you could certainly do wonders for The Great Gatsby.

What dogfish can’t do is raise PISA scores.

I read over the guidelines and sample questions for the Science section of the PISA. While it’s better than most standardized tests I’ve had the misfortune of encountering during my education, it still runs headfirst into the standard failure modes of the genre. When every question must be asked in a single paragraph, and when the correct answers (of which there are never more than two) must be expressed in a single sentence, the chances for subtlety, poetry and joy must be omitted from the testing pamphlet, if they are even considered.[1] The answers to every question in the exam have already been determined before a single student sits down and unsheathes a #2 pencil. Continue reading Students Cannot Live on Bubble Sheets Alone

Spamming Made Easy in ASP.NET

Receiving an auto-generated email is often unpleasant, but when deployed correctly, they can be useful for both customers and companies. C# and SQL make it easy to grab information from a database and send customized emails with little effort.

Step one: grab the data.

Before sending emails, information from the database, like email addresses, needs to be collected by a query. A SQL query is just a string that asks a database for a specific table, but users should be able to dynamically change it depending on their needs.  As such, certain lines need to be flexible.  In the past, I had used string formatting to insert text into the query, which looks like the following:

string text = "I ilke {0} more than {1}, honestly";
text = string.Format(text,"dogs",cats");

Solving the ‘double hop’ issue using Secure Store


[Image via Fabian Williams]

Last week I was working on some ASP.NET web forms that generated internal reports against MS CRM using ExcelWriter and I wanted to port the application to one of our SharePoint instances. Though it seemed simple at first, I ran into a few issues. One of the issues happened to be authentication related. It was a typical ‘double hop’ problem where this SharePoint instance was using integrated Windows NTLM authentication and my code was trying to access the CRM SQL Server database. By nature, NTLM is unable to pass the credentials to the database thus producing access errors. (You can find more information on the NTLM issue and using Kerberos as a solution here.)

Since we don’t have Kerberos configured on this environment, our best solution was Secure Store. This service allows a user to authenticate with domain credentials and then use an account established in Secure Store to access the database. In our case, this was the read-only CRM account. This also enables easy to use and convenient access control using AD groups. Continue reading Solving the ‘double hop’ issue using Secure Store

Andrew Brust on OfficeWriter: “Enhanced Integration for Microsoft Business Intelligence”

[Click here to read the full review!]

To many of you, the name Andrew Brust might ring a bell. After all, this is a man who’s been working with, shaping and talking about Microsoft technologies for twenty years. Maybe you’ve seen him speak or read his columns on Visual Studio Magazine and Redmond Developer News. Maybe you’ve heard of his new Microsoft consulting firm, Blue Badge Insights. Maybe, like me, you follow his personal blog. I started reading Brust Blog in the beginning of the year, and it quickly became my de facto source for objective analysis of Microsoft developments. A few comments led to an email exchange that, a few months later, lead to what has got to be one of the most cogent and comprehensive summaries of an advanced technological product out there. Continue reading Andrew Brust on OfficeWriter: “Enhanced Integration for Microsoft Business Intelligence”

Blogged