Spreadsheet chart reference manual now available

After weeks of working on this, I’ve finally finished my reference manual on Open XML charts in spreadsheets. It’s called (imaginatively) Spreadsheet Open XML Charts.

It’s sort of a sequel to my other reference manual on Open XML spreadsheets.

If you do any work on Open XML spreadsheets and in charting specifically, I’m certain you’ll find this useful. Please check it out and let me know what you think.

Now if you’ll excuse me, I’ve got to go crash and do nothing. Perhaps get a cup of tea or hot chocolate.

Compression and space

Back when I was in my making games mode years ago, I was interested in space. Specifically data storage.

Blitting images to the screen as quickly as possible to keep the frame rate consistent was a thing. Texture images are pre-created. Then there’s the concept of generating textures as the program was executed. This was how demos pack data into a small space.

I was studying fractals then, the iterated function system was a thing. I found it a pain to implement though…

And then I was interested in having moving images in my game engine. Packing movies was way harder than images.

Due to the copyrights of all the video formats, in my infinite wisdom, I decided to create my own. I mean, it’s a series of images, right?

The resulting customised movie file was just slightly larger in size than the QuickTime .mov format. I took that as an encouragement. I’ll tell you what I did next time.

Difference between app business and app development

A few days ago, I went to a BlackBerry developer meetup session hosted by my friend. Being primarily a .NET developer, I thought I’d broaden my horizons and learn what the BlackBerry platform was about.

It was a little bit of a show-and-tell. It turns out that the next version of BlackBerry is going to be out soon, and the session was sort of a “getting developers to develop for the BlackBerry 10” thing.

I’m not really going to go into the details of the new BlackBerry nor its development platform. I am going to talk about apps, since it’s the in-thing currently. Here are the markets I know of:

  • Apple’s app market on the iPhone and iPad
  • Microsoft’s app market on Windows phones and the to-be-released Windows 8
  • Google and its Android phone app market
  • RIM (Research In Motion) and its BlackBerry app market

I’m using “app market” in the general sense. There are probably other app markets, but the ones listed should be in the top few in terms of audience size.

What I learnt at the session

I’m not a BlackBerry developer, so I mainly kept quiet and listened. However, as the presentations went on, and I asked questions, something hit me. I approached app development differently from the developers present, including the presenters.

Preemptive disclaimer: The developers are probably very good at their work. The following are my opinions after being self-employed for over a year and studying business and related materials for longer than that. I’m not putting those developers down.

Here we go. These developers were interested in the technical aspects of BlackBerry development, whether it be the hardware specs of the BlackBerry, or the tools used for BlackBerry development.

I asked my friend who’s a consultant for RIM about the BlackBerry, and he told me a bunch of specs on the new BlackBerry. Now I’m a programmer, but I don’t get real excited about stats.

Let me put it to you this way. Most consumers will have their eyes glazed over when you tell them some piece of hardware has X gigabytes of storage and so on. Apple, just said the iPod can hold 1000 songs.

Get the picture?

The developers were interested in what they could do on the new BlackBerry. I was interested in what consumers could do on the new BlackBerry. That difference is what makes you money. If you don’t like the idea of monetary gains, then think of the popularity of your app. Think of widespread acceptance and downloads of your apps.

File selection

One developer asked my friend (who’s presenting at that moment) to go to a particular website and click a button there. Nothing happened. I asked that developer what’s supposed to happen.

He said you’re supposed to have a pop-up to select files. The first thought I had was security. And even if file selection is allowed, and assuming the user knows what he’s doing, what kind of files were allowed and why would it even be useful?

Sure, you could select photos for upload, or business documents for transfer. But I believe the app should handle the file selection interface, which makes it seamless for the user.

Designing for the consumer

But that question is really the heart of what most (or all) of the developers there were concerned about. They want to know what’s the storage capacity, the screen size, what tools to use for development, how to debug your applications and so on.

These are all valid questions and concerns. And I understand that some people are really into the technical aspects.

So what kind of questions did I ask?

I asked if videos can be taken. How’s the quality of a video playback? How’s the quality of the audio? Is there a YouTube app?

On the train or buses, people plug in earphones and either watch videos or play games or text communicate (either messaging or Twitter or Facebook or whatnot) on their mobile devices. The technical aspects are useful. I just think coming up with an app that people would want to use is priority one. The app doesn’t have to be complicated. It just has to either be useful or entertaining.

So I basically asked questions that consumers are more concerned about. And the new BlackBerry from what I’m told, is targeting the consumer market. It makes sense that BlackBerry developers should also be concerned about similar topics.

There were questions on the price of licensing. It’s free to upload apps, but there’s a fee for certification. From some research I did, the revenue sharing scheme is the standard 30/70, with 70% of the revenue going to the developer.

Here’s the funny thing. No one asked about making money stuff. Not about how to do in-app purchases, or the kinds of prices used (although the developers are free to set their own price), or how to get paid. Maybe they already know, and I’m the only one in the dark (I’m not a BlackBerry developer).

I look at the apps from the perspective of both a business owner and a developer. Maybe that’s why I stopped fretting about technical difficulties. Because the instant a human user uses your software, you’ll find out the real and important difficulties.

Singapore Mini Maker Faire Part 1

I went to the mini Maker Faire at the Singapore Science Centre.

In the video, there’s a device that measures distances using ultrasonic waves, and based on that, turns a light on or off. It took me a while before I finally understood the concept. I thought it was directional, because she explained the device by pointing it up and down for light-on and light-off. Then I asked if it could be programmed to understand sideways directions…

Basically, if you block the emitting device (ultrasonic waves) within a certain distance, the light switches on. That’s why pointing to the ceiling switches it off, because the distance between the device and the ceiling exceeds the programmed distance limit.

How to properly use OpenXmlWriter to write large Excel files

Brian Jones already wrote something on using the OpenXmlWriter with the Open XML SDK to write large Excel files. There are a couple of things that weren’t mentioned though.

OpenXmlWriter works best for creating new things

For the purposes of explanation, we’ll assume the “new thing” is a new worksheet, although OpenXmlWriter can be used to write any Open XML SDK part (such as WorkbookPart).

The reason for this is that OpenXmlWriter is essentially writing out XML tags. The SDK offers classes and properties that we’re familiar with, but underneath it all, an Open XML spreadsheet’s internal structure are XML files zipped together. So instead of using the SDK classes to form the “XML” parts, we use the OpenXmlWriter to directly write out the XML.

The problem comes when we need to save. If you’re working with an existing worksheet, there’s already an existing XML structure in place. This means there’s a corresponding WorksheetPart (to your Worksheet class), and this WorksheetPart already has the XML structure (and more importantly, a DOM structure).

So you can’t just use the OpenXmlWriter to write the XML stuff into an existing WorksheetPart, because any changes you made will be ignored.

This is why in Brian’s code, he reads in the existing worksheet and writes the content into a new worksheet, thus using a new WorksheetPart that’s “attached” to the WorkbookPart. After he’s done reading, he deletes the old WorksheetPart.

In conclusion, either you

  • Work with the DOM structure using the SDK, which means snail-pace if you’re writing many cells
  • Work with new WorksheetPart

If you have something like worksheetPart.Worksheet.Save() mixed with the use of OpenXmlWriter, it’s not going to work.

XML attributes

The more subtle (and possibly more crucial) information that’s missing is of attributes. When you use the OpenXmlWriter, it works best if it’s used all the way. As in you don’t mix SDK class/property usage code with OpenXmlWriter code.

Basically, if there’s an SDK class property you want to set, don’t assign to that property in code. Use the OpenXmlWriter to write that property, because that property is eventually an XML attribute anyway. In this case, think of the SDK as getting in the way of making your code run faster.

In Brian’s code, notice that the RowIndex property of the Row class is not assigned. Which is fine since it’s optional. In this case, Excel tries to guess which row it is, and places the first Row class content into the first row on the spreadsheet. This is good if that’s what you want. And bad if your want your “first” Row class to be on the 3rd row instead.

You might also notice that the Cell class has no properties assigned. And no, CellFormula and CellValue are not properties of the Cell class, they’re actually child classes. The SDK allows you to access them as though they’re properties. This means the “standard” set of cell properties are not assigned:

  • The cell reference, such as “A1”
  • The cell data type, such as string or number
  • The cell style index

The code works because the cell content is a random number. And the default data type of a cell is a number (so it doesn’t need to be explicitly assigned). It will fail if you need strings or other data types.

And because there’s no cell reference given, Excel tries to guess where each cell is. Which is to say, place all the cells in that row flush to the left. This means you can’t have a cell in A1, and a blank cell in B1, and then a cell in C1, because Excel mooshes all the Cell classes together.

And because there’s no style index given, the default style is used. This is typically the minor font (such as Calibri) with no special formatting (such as font colour or bold or italics or borders).

So here’s sample code on how to use OpenXmlWriter. Note the class OpenXmlAttribute. And if you understand this, you will know that the OpenXmlWriter isn’t only for writing large Excel files. It just happens that it directly writes XML tags and attributes, and thus works the fastest for writing a lot of data.

using (SpreadsheetDocument xl = SpreadsheetDocument.Create("LargeFile.xlsx", SpreadsheetDocumentType.Workbook))
{
    List<OpenXmlAttribute> oxa;
    OpenXmlWriter oxw;

    xl.AddWorkbookPart();
    WorksheetPart wsp = xl.WorkbookPart.AddNewPart<WorksheetPart>();

    oxw = OpenXmlWriter.Create(wsp);
    oxw.WriteStartElement(new Worksheet());
    oxw.WriteStartElement(new SheetData());

    for (int i = 1; i <= 50000; ++i)
    {
        oxa = new List<OpenXmlAttribute>();
        // this is the row index
        oxa.Add(new OpenXmlAttribute("r", null, i.ToString()));

        oxw.WriteStartElement(new Row(), oxa);

        for (int j = 1; j <= 100; ++j)
        {
            oxa = new List<OpenXmlAttribute>();
            // this is the data type ("t"), with CellValues.String ("str")
            oxa.Add(new OpenXmlAttribute("t", null, "str"));

            // it's suggested you also have the cell reference, but
            // you'll have to calculate the correct cell reference yourself.
            // Here's an example:
            //oxa.Add(new OpenXmlAttribute("r", null, "A1"));

            oxw.WriteStartElement(new Cell(), oxa);

            oxw.WriteElement(new CellValue(string.Format("R{0}C{1}", i, j)));

            // this is for Cell
            oxw.WriteEndElement();
        }

        // this is for Row
        oxw.WriteEndElement();
    }

    // this is for SheetData
    oxw.WriteEndElement();
    // this is for Worksheet
    oxw.WriteEndElement();
    oxw.Close();

    oxw = OpenXmlWriter.Create(xl.WorkbookPart);
    oxw.WriteStartElement(new Workbook());
    oxw.WriteStartElement(new Sheets());

    // you can use object initialisers like this only when the properties
    // are actual properties. SDK classes sometimes have property-like properties
    // but are actually classes. For example, the Cell class has the CellValue
    // "property" but is actually a child class internally.
    // If the properties correspond to actual XML attributes, then you're fine.
    oxw.WriteElement(new Sheet()
    {
        Name = "Sheet1",
        SheetId = 1,
        Id = xl.WorkbookPart.GetIdOfPart(wsp)
    });

    // this is for Sheets
    oxw.WriteEndElement();
    // this is for Workbook
    oxw.WriteEndElement();
    oxw.Close();

    xl.Close();
}

This creates a spreadsheet with 100 columns and 50000 rows of data. On my machine, it ran in about 18 seconds.

I’m not going to list all the available XML attributes, but you can find out the correct attribute name by exploring the Open XML SDK documentation, or the official ECMA documentation.