Toluu

Microsoft Excel 12

The team blog for Microsoft Excel and Excel Services.

Using Excel 2007 for Progress Tracking in the ClassroomAugust 21 2008

Today's author, Danny Khen, a Program Manager on the Excel team, discusses a solution he recently built using Excel.

The Manual Spreadsheet

Spreadsheets are the bread and butter of running the business world, where PCs are ubiquitous. The possibility, indeed even the need, to use spreadsheets in an environment where access to a computer is not taken for granted seems absurd to us, as business users and technology professionals. We do still print reports for some output scenarios, such as executive reports and meeting handouts. But as a whole, using a computer for day to day interaction with a spreadsheet application is so obvious that it's almost embarrassing to state.

Well, meet the preschool classroom.

I won't risk talking about the "typical" preschool classroom in the early 21st century, because I only know one of them - the one my youngest daughter used to go to, and where Michal, my better half, teaches. Let's cautiously say that at least some of them are not yet IT-enabled. This particular one does not have a single computer on premises. And most staff members are novice computer users at best. Which does not mean that they don't have excellent reasons to use Excel (don't we all?); they are just not always quite aware of those reasons.

Enter Michal: a Montessori preschool teacher. Michal has a CS degree (Cum Laude - there, I said it, I can safely come home tonight) and a previous software development career and, as a computer u

Reading Excel Files from LinuxAugust 15 2008

Today's author, Chris Rae, a Program Manager on the Excel team, talks about using Perl code to read Excel's new file format.

The OOXML Format

 

As most readers will know, we spent a lot of time during the development of Office 2007 in creating and documenting a new XML-based file format (Office Open XML) to replace the much more complex binary formats the various Office applications have used for the last twenty years. A lot of time. Many of us dream in XML now. OOXML encompasses an XML file format for each of the largest Office applications, these formats being WordprocessingML, PresentationML and SpreadsheetML. It became an ECMA standard (ECMA-376) in 2006 and was approved with modifications as an ISO/IEC standard (IS 29500) in early 2008, though it has yet to be released by ISO/IEC. I think it would be fair to say that this Office feature has had more press coverage, both good and bad, than all other Office 2007 features combined. Political positions aside, one thing that can be said for definite is that the move made Office files easier to understand, and has created new ways for our customers to work with their data. In this post I'm going to walk through the solution to an imaginary customer's interoperability problem, but first I'd like to cover a couple of OOXML basics.

 

Reading Material

 

The standardisation process means that the file format itself now belongs to ECMA and ISO/I

How to Create a Perpetual Yearly Calendar in ExcelAugust 8 2008

Today's author: Mark Gillis is an Excel writer. He was born October 3, 1952. Does his birthday fall on the same weekday as your birthday?

Do you have nagging, unanswered questions floating around in the back of your mind like: "What day of the week does the date, January 23, 4589 fall on?", "What's the date of the eighth Thursday of 1922?", and "Is 9000 a leap year?".

If you're like me, the answer is clearly no. Nevertheless, there's a remote chance that someday a question like this will arise and you won't have the answer at your finger tips. If and when that day comes, you will either need a friend with a photographic memory or a perpetual yearly calendar. If your friends have other talents, you'll need to dig up a calendar of this type. To save you the trouble, I've written one for you that uses Excel and here's how it works.

Download the perpetual yearly calendar

An overview of the problem

As most of you probably know, Excel's date calculation engine handles a time span between the years 1900 and 9999 by using a serial number to calculate dates within that time span. January 1, 1900 is a value of 1 and Dec. 31 9999 is a value of 2,958,465. Excel's date formatting features, formula calculation abilities, and built-in date and time functions give you a wonderful fou

Building an Asset Tracking Application in Excel Services – Part 5 of 5August 1 2008

Today's author, Dan Parish, concludes his discussion of building an Excel Services solution.

In Part 4, I walked through creating the web part pages for the solution. At this point, you should have a fully functional asset tracking application up and running. The last piece, that we'll build today, is a simple web part that will allow your admin to archive all of the information they have collected.

Overview

At this point we now have the entire application up and running. We can collect information entered by users, and they can see how their machines compare against their coworkers. Our admin can at a glance see who has (and hasn't) filled out the survey, and can see who needs upgrades or new machines.

However, there is still one problem with our application: there is no real way to archive the information. Archiving information is important for transparency, accountability, and sometimes for government compliance. This scenario is no different. With the current model someone could change their information at any point, even after the data collection period was supposed to end. There is also no good way to do year over year comparisons.

To rectify this, we are going to build a small web part that goes on the admin's page. The admin can at any point just click this button to create an arc

Building an Asset Tracking Application in Excel Services – Part 4 of 5July 24 2008

Today's author, Dan Parish, continues on his discussion of building an Excel Services solution.

In Part 3b I walked through creating a User Defined Function (UDF) that pulls information from a SharePoint List into Excel Services. In today's post, I will describe how to create the two web part pages that make up the actual application: the page that end user's see, and the page that the admin uses.

Overview

Now that we have our spreadsheet running on the server and displaying data entered into the SharePoint List, all we need to do is present this information in a user-friendly way. Using Excel Web Access (EWA) – Excel Services' web part – we can seamlessly integrate this information into any existing site.

Creating the end user's web part page

As a refresher, here is the page we are going to create:

The first thing we need to do is actually create the web part page itself. Virtually every page in SharePoint is a web part page, which is a page that you can add web parts to and remove web parts from. You can also create your own web part pages from scra