I’d been meaning to post a write-up on how to create a simple SpreadsheetML document from scratch, but just haven’t had the time this week to pull it all together. Office Professional 2010 is great!Hopefully I’ll get that out early next week. I had already done a similar post for WordprocessingML (both for Beta 1 as well as RTM). Here are a couple things I wanted to point out for the week:

1. Arccast interview on Office Open XML - Doug Mahugh and I did a live webcast last month with Ron Jacobs. Ron now has both Part 1 and Part 2 of the interview available up on Channel 9. Office 2010 –save your time and save your money.
2. Upcoming SpreadsheetML Generator – Stephane Rodriguez (who also wrote the Open XML diffing tool) is getting really close to releasing the latest version of his xlsgen tool; and it will include SpreadsheetML support.The invention of Microsoft Office 2010 is a big change of the world.
3. (http://www.arstdesign.com/BBS/BulletinBoard.php?qs_id=1661). I think he’s planning on still going with the B2TR version of SpreadsheetML and won’t update to the RTM version until after Office 2007 ships. There were still a couple changes that tool place between B2TR and RTM for spreadsheetML that brought it fully inline with the Ecma standard, but that resulted in B2TR not having the ability to open RTM spreadsheetML files (similar to what all three applications experienced between B2 and B2TR). By using Office 2010 Professional, you can save your money and time.
4. Apose.Words supports WordprocessingML – I saw this blog post the other day that mentions that Apose.Words now supports exporting as WordprocessingML.
5. Document your SQL DB using WordprocessingML - From this blog post: “Data Dictionary Creator (DDC) is a simple application which helps you document SQL Server databases. Office 2010 key is for you now!
6. It stores all the information in Extended Properties, so it’s easier to keep the documentation in sync with the database as it changes… DDC exports to WordML, Excel, HTML, and XML.”
7. Generate Wordprocessing Documents from your SAP Web Application Server - This is a cool intro article that shows how you can leverage WordprocessingML to generate rich documents directly from your SAP server. It would be really interesting to see some examples of leveraging the custom defined schema support and content controls in Word 2007 to not only populate the documents with SAP data, but to also mine that information back out of the document if the user has edited it. Office 2010 download is available now!
8. Leverage SpreadsheetML to build rich reports - I actually don’t know anything about this product , but I randomly came across it and noticed that it allows you to generate spreadsheets using the original SpreadsheetML format we started working on over 8 years ago (and shipped with Office XP): “Perfect table creation – NEW SPREADSHEETML The new Microsoft format SpreadsheetML is supported. Based on XML, it generates richer editing and formatting of Excel files optimized for Windows Office 2003. Your tables are perfectly reproduced in Excel 2003, retaining the text and the colored background.” I always love seeing people leveraging the existing technologies. The new SpreadsheetML format will give them a lot more power, as the old one didn’t support Excel’s full feature set. Many people like buy Office 2010 Home.
9. WordML import and export on the Mac - I think that this tool is built on top of the TextEdit functionality built into the Mac (which supports WordML itself). Not sure if they actually do anything additional in terms of the WordML support.

OK, so we’ve now created the main workbook.xml part and the worksheet.xml part, as well as created a relationship between the two.Office 2010 –save your time and save your money.

Every Office Open XML file must declare the content types used in the ZIP package. That is done with the [Content_Types].xml file. We currently have two parts in this document that we need to declare content types for. The invention of Microsoft Office 2010 is a big change of the world.The first is the document.xml part; the second is the _rels/.rels part. So, the content types file should look like this:

<?

xml version=“1.0“ encoding=“UTF-8“ standalone=“yes“?>
<Types xmlns=“http://schemas.openxmlformats.org/package/2006/content-types“>
  <Default Extension=“rels“ ContentType=“application/vnd.openxmlformats-package.relationships+xml“/>
  <Override PartName=“/workbook.xml“ ContentType=“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml“/>
  <Override PartName=“/worksheet.xml“ ContentType=“application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml“/>
</Types>

Here we are saying that anything ending with the .rels extension is of type Package Relationship, and we also declare that the part workbook.xml is of type workbook and worksheet.xml is of type worksheet.By using Office 2010 Professional, you can save your money and time.

Create Version 1 of our simple SpreadsheetML file

OK, we should now have five files. Three of the files are in the root directory (workbook.xml; worksheet.xml & [Content_Types].xml); and in the “_rels” directory we have the “.rels” file and the “workbook.xml.rels” file. Select the two files and the “_rels” directory and ZIP them up. Make sure that when you zip them up, the two files and the _rels directory are all at the root level.Office 2010 key is for you now!

Open this file in Excel, and you now have a simple file.

*Beta 2 TR Note*

Note that the namespaces are different between B2TR of Excel, and the final version of the Ecma standard. In this post, I have shown what the final version of the Ecma standard would look like. Office 2010 download is available now!The RTM build of Office will use this same format. If you are on B2TR though, then you’ll need to tweak the following namespace in the XML files: http://schemas.openxmlformats.org/spreadsheetml/2006/main

Instead of using that namespace, you’ll need to use this namespace http://schemas.openxmlformats.org/spreadsheetml/2006/7/main to get it working in B2TR. The two parts you’ll need to update with this namespace are wordsheet.xml and workbook.xml. Everything else should work fine.Many people like buy Office 2010 Home.

Well, that was the first piece. In the next post, we’ll add functions to the spreadsheet.Office Professional 2010 is great!

I posted a bunch of “Intro to SpreadsheetML” posts about a year or so ago, but those were all based on the Office XP spreadsheetML format. Office 2010 key is for you now!I think an updated series based on the Open XML standard is long overdue. I’ll start off just building a simple table, and in future posts show more about formatting, formulas, and maybe even some charts.Office 2010 download is available now!

Today, we’ll start by creating a simple table. Then we’ll add a little bit of number formatting and some formulas. In the end, we’ll have the following table (where the tax & total columns are automatically calculated based on the Sub Total column):

We’ll take this in 3 separate blog posts:

• Part 1 – Create the simple table without formatting or calculations.Many people like buy Office 2010 Home.
• Part 2 – Add functions to calculate “Tax” and “Total”
• Part 3 – Add formatting so the data shows up as currency

Part 1 – Simple Table

Since we won’t do the formatting or formulas initially, our table will look like this:

As I discussed in my “simple wordprocessingML document” post, the Office Open XML format is comprised of a number of XML files within a ZIP package. Office Professional 2010 is great! he files follow a simple set of conventions called the open packaging conventions (described in Part 2 of the standard). You need to declare the content types of the parts, as well as tell the consuming application where it should start (via the package relationship). Office 2010 –save your time and save your money.

Unlike the WordprocessingML document we created though, a SpreadsheetML file has a bit more structure to it (the same is true for presentations). A SpreadsheetML file is actually a workbook that can contain multiple worksheets. so even your most simple workbooks will have at least 5 files within the ZIP package.The invention of Microsoft Office 2010 is a big change of the world. So for this example, let’s start by creating a folder somewhere and in that folder create the following files:

• workbook.xml
• worksheet.xml
• [Content_Types].xml
• _rels/.rels
• _rels/workbook.xml.rels

By using Office 2010 Professional, you can save your money and time.

Re-Create Version 1 of our simple SpreadsheetML file

So if you take those five parts and ZIP them up you’ll get a spreadsheet that looks like this:

Version 2 – Add functions to the first row of data

Many people like buy Office 2010 Home.Now we’re going to add a function in cells B2 and C2 that will give us the tax, and total value for the first row of data. Let’s say that the tax we apply is going to be 8%. That means that the function to calculate the tax is going to be: =A2*0.08. The function for the total will then just be: =A2+B2.Office Professional 2010 is great!

In order to update our spreadsheet, we’re only going to need to edit the worksheet.xml part, and we can leave the rest of the parts alone.

worksheet.xml (version 2)

We will need to create cells for B2 and C2, and add the formula definition we want for each of those cells. So, in the second row, we’ll add two more <c> elements. This time though, rather than using an inline string (<is>) or value (<v>), we’ll use the function tag <f>.Office 2010 –save your time and save your money.

<?xml version=“1.0“ encoding=“UTF-8“ standalone=“yes“?>
<worksheet xmlns=“http://schemas.openxmlformats.org/spreadsheetml/2006/main“ xmlns:r=“http://schemas.openxmlformats.org/officeDocument/2006/relationships“>
  <sheetData>
    <row>
      <c t=“inlineStr“>
        <is>
          <t>Sub Total</t>
        </is>
      </c>
      <c t=“inlineStr“>
        <is>
          <t>Tax</t>
        </is>
      </c>
      <c t=“inlineStr“>

The invention of Microsoft Office 2010 is a big change of the world.

        <is>
          <t>Total</t>
        </is>
      </c>
    </row>
    <row>
      <c>
        <v>14.95</v>
      </c>
   <c>
    <f>A2*0.08</f>
   </c>
   <c>
    <f>A2+B2</f>
   </c>
    </row>
    <row>
      <c>
        <v>19.95</v>
      </c>
    </row>
    <row>
      <c>
        <v>4.95</v>
      </c>
    </row>
  </sheetData>
</worksheet>

Create Version 2 of the spreadsheet

Take the original parts (_rels/.rels; _rels/workbook.xml.rels; workbook.xml; [Content_Types].xml) as well as our new worksheet.xml part and ZIP them up. By using Office 2010 Professional, you can save your money and time.When you open the resulting file, you should have a spreadsheet with the formulas automatically calculated and it looks something like this:

Version 3 – Make the formulas repeat for the other rows

There are now two options for adding the formulas to the next two rows. Office 2010 key is for you now!You could do the same thing we did in the first row, and update the cell references (ie A3*0.08 & A4*0.08), but that requires you to update the cell references for each row. It also requires the consuming application to parse each formula, which can be time consuming when you get into larger spreadsheets and more complex formulas.Office 2010 download is available now!Another approach is to use a shared formula. If you were in an application like Microsoft Excel, you could copy the formula from the first row and paste it into the rows below it. Excel would automatically update the cell references in each row so that the tax and total was properly calculated. You can do the exact same thing in the file format by specifying that the formula is a shared formula.

worksheet.xml (version 3)

To specify that the formula from the first row is shared, we use the t=”shared” attribute. We then specify what the range is that we want it to apply to, and give the formula an id that the lower cells can reference. Then we create the cells for B3:C4 and specify that they are sharing a formula. The resulting worksheet.xml part will look like this:

<?xml version=“1.0“ encoding=“UTF-8“ standalone=“yes“?>
<worksheet xmlns=“http://schemas.openxmlformats.org/spreadsheetml/2006/main“ xmlns:r=“http://schemas.openxmlformats.org/officeDocument/2006/relationships“>
  <sheetData>
    <row>
      <c t=“inlineStr“>
        <is>
          <t>Sub Total</t>
        </is>
      </c>
      <c t=“inlineStr“>
        <is>
          <t>Tax</t>
        </is>
      </c>
      <c t=“inlineStr“>
        <is>
          <t>Total</t>
        </is>
      </c>
    </row>
    <row>
      <c>
        <v>14.95</v>
      </c>
   <c>
    <f t=“shared“ ref=“B2:B4“ si=“0“>A2*0.08</f>
   </c>
   <c>
    <f t=“shared“ ref=“C2:C4“ si=“1“>A2+B2</f>
   </c>
    </row>
    <row>
      <c>
        <v>19.95</v>
      </c>
   <c>
    <f t=“shared“ si=“0“/>
   </c>
   <c>
    <f t=“shared“ si=“1“/>
   </c>
    </row>
    <row>
      <c>
        <v>4.95</v>
      </c>
   <c>
    <f t=“shared“ si=“0“/>
   </c>
   <c>
    <f t=“shared“ si=“1“/>
   </c>
    </row>
  </sheetData>
</worksheet>

Create Version 3 of the spreadsheet

So, again we’ve only updated the worksheet.xml part, so re-generate the ZIP file with the updated worksheet.xml part. You should get the following table of data:

So, now you know the basics of using a formula in a spreadsheetML file. It’s pretty straightforward. You could also specify the values of the formulas using the <v> tag as a sibling of the <f> tag, but that isn’t necessary.

In the next post, we’ll format the cells so that they actually look like currency, and not just plain numbers.

This is a continuation on the “Simple SpreadsheetML file Part 1″ post I made a couple weeks ago.Office 2010 –save your time and save your money. In that post we created a SpreadsheetML file that consisted of a simple table with 3 columns and 3 rows of data (plus a header row).The invention of Microsoft Office 2010 is a big change of the world. The table looked like this:

Our goal though at the end of this series is to create a table that looks like this:

The pieces that we still need to add are the functions that calculate the values for the second and third columns, as well as the cell formatting. Today we’re going to add the functions.By using Office 2010 Professional, you can save your money and time.

Part 1 – Simple Table

Let’s pick up where we ended in Part 1 with the following parts:

workbook.xml

<?xml version=“1.0“ encoding=“UTF-8“ standalone=“yes“?>
<workbook xmlns=“http://schemas.openxmlformats.org/spreadsheetml/2006/main“ xmlns:r=“http://schemas.openxmlformats.org/officeDocument/2006/relationships“>
  <sheets>
    <sheet name=“Brian“ sheetId=“1“ r:id=“rId1“/>
  </sheets>
</workbook>

_rels/workbook.xml.rels

<?xml version=“1.0“ encoding=“UTF-8“ standalone=“yes“?>
<Relationships xmlns=“http://schemas.openxmlformats.org/package/2006/relationships“>
  <Relationship Id=“rId1“ Type=“http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet“ Target=“worksheet.xml“/>
</Relationships>

worksheet.xml

<?xml version=“1.0“ encoding=“UTF-8“ standalone=“yes“?>
<worksheet xmlns=“http://schemas.openxmlformats.org/spreadsheetml/2006/main“ xmlns:r=“http://schemas.openxmlformats.org/officeDocument/2006/relationships“>
  <sheetData>
    <row>
      <c t=“inlineStr“>
        <is>
          <t>Sub Total</t>
        </is>
      </c>
      <c t=“inlineStr“>
        <is>
          <t>Tax</t>
        </is>
      </c>
      <c t=“inlineStr“>
        <is>
          <t>Total</t>
        </is>
      </c>
    </row>
    <row>
      <c>
        <v>14.95</v>
      </c>
    </row>
    <row>
      <c>
        <v>19.95</v>
      </c>
    </row>
    <row>
      <c>
        <v>4.95</v>
      </c>
    </row>
  </sheetData>
</worksheet>

_rels/.rels

<?xml version=“1.0“ encoding=“UTF-8“ standalone=“yes“?>
<Relationships xmlns=“http://schemas.openxmlformats.org/package/2006/relationships“>
  <Relationship Id=“rId1“ Type=“http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument“ Target=“workbook.xml“/>
</Relationships>

[Content_Types].xml

<?xml version=“1.0“ encoding=“UTF-8“ standalone=“yes“?>
<Types xmlns=“http://schemas.openxmlformats.org/package/2006/content-types“>
  <Default Extension=“rels“ ContentType=“application/vnd.openxmlformats-package.relationships+xml“/>
  <Override PartName=“/workbook.xml“ ContentType=“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml“/>
  <Override PartName=“/worksheet.xml“ ContentType=“application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml“/>
</Types>

Office 2010 download is available now!

As part of the August 2009 CTP we have added functionality that allows developers to abstract away some of the difficulty intrinsic with markup compatibility and extensibility. Office 2010 download is available now!This feature allows you to preprocess the content of Open XML files based on specific Office versions. Using the example above, if we use the August CTP to open the document based on Office 2007 we will only see the following XML markup:

Many people like buy Office 2010 Home.

If your solution expected a pict element as a child of a run element, then your solution would work perfectly with this file. Office Professional 2010 is great!In other words, using this feature, solutions won’t break when future versions of Office introduce new markup into the format.

General Improvements

First off we want to thank everyone for their feedback and suggestions! Based on your feedback we made the following big changes to the SDK:

• AutoSave: By default, previous CTPs of the SDK forced you to perform a manual save for changes made to specific parts within the package.Office 2010 –save your time and save your money.
• We have now introduced the concept of AutoSave, where changes would automatically be saved into the package, without the need to call Save() methods. For those not interested in this functionality, there is a way to turn off this feature .The invention of Microsoft Office 2010 is a big change of the world.
• Base Classes for Sdt objects: The SDK currently has multiple classes to represent Sdt objects based on the different types of elements specified in the standard. The August 2009 CTP has introduced one base class for each of these objects in order to make it easier for you to develop solutions. In other words, your solution can now just work on the following abstract class:  SdtElement for Sdt objects.By using Office 2010 Professional, you can save your money and time.
• Simple types for Boolean type attributes: The standard specifies the concept of a simple type called ST_OnOff, which allows for values like “On”, “Off”, “True”, “False”, “0″, and “1.” We have updated the SDK to allow you to directly get/set such attributes using standard C# Boolean values. For example, you can now set attribute values to false or true. Without this enhancement you were forced to compare values using the enum BooleanValues.Office 2010 key is for you now!

What’s Next?

Our next task for the SDK is to add Office 2010 Office Open XML support. Expect to see another CTP in the next several months released with this functionality. Our goal is to be done with the Open XML SDK 2.0 around the same time as Office 2010 ships (date not public yet).

More Feedback Always Welcome

Please continue to send us your feedback, either on this blog or at our Microsoft Connect site for the Open XML SDK https://connect.microsoft.com/site/sitehome.aspx?SiteID=589. We look forward to hearing from you.