Launching SpreadsheetLight

January 10, 2012 By Leave a Comment

I am excited to tell you that my spreadsheet software library is available!

SpreadsheetLight

For the initial launch version (I decided to go for version 1. Why do people launch with versions 0.8? I don’t know…), you get comprehensive support for styles, rich text formatting, 47 named cell styles, themes (either one of the 20 built-in themes or create-your-own). Well, like I said, comprehensive styling support.

There’s also the (hum-drum) support for merging cells and freezing panes. I actually explored how to split panes. I certainly wrote about it in my Open XML guide, but it turns out that there’s a tiny rectangle at the top-left corner that Microsoft Excel didn’t tell me about. The size of that tiny rectangle is dependent on the font you use, and even the screen resolution of your computer screen.

While I could add a function that allows you to just input the size in EMUs (English Metric Units), I decided that if I can’t do it well, I don’t want to do it. At least for the initial launch.

Row heights and column widths were also big time drains. It turns out that they’re also dependent on the font and screen resolution of your computer screen. I was trying to calculate the standard row heights and column widths for the fonts in the built-in themes, and I thought I had them. I wrote a program using SpreadsheetLight to generate spreadsheets with different minor fonts, and I wrote a program to read in those spreadsheets and get the “standard” row height and column width. I spent 3 hours collecting data.

Then on a whim, I switched my computer screen’s resolution from 120 DPI to 96 DPI (my eyesight’s not that good ok? I need bigger text…), and whoa! All that data doesn’t apply anymore… All in all, I think I spent 6 or 7 days trying to figure out a general calculation formula. I failed. I don’t know how Excel does it.

I also surprised myself by including extensive support for pictures. I thought you just insert a picture into the worksheet and that’s it! It turns out there’s a ton of post-insertion manipulations you can do. For example, if your picture has transparent areas, you could set a background fill, and that background colour will be visible through the picture. Also, you can rotate the picture in 3D.

SpreadsheetLight is licensed under the MIT license. I decided to use one of the available software licenses instead of making up one of my own. As far as I can tell, the MIT license allows the recipient of the software to use the software in personal or commercial products. It’s also categorised as free software, as in freedom of use, not free as in cost. I don’t want to deal with per-client, or per-server, or per-developer or per-what-not licensing restrictions.

Even if you’re not interested in spreadsheet software, have a look at SpreadsheetLight. Tell some other programmer about it. Tell your manager about it. It took me slightly over 2 months of intense coding, and I want someone in the world out there to have an easier life because of SpreadsheetLight. Thanks!

Oh, and the image art is designed by Charlie Pabst from Charfish Design. While I have a fair competence in image work, I decided to get a professional designer to help me. It’s a business and professional product. I’m not going to risk the product’s success so I could stoke my ego…

Filed Under: Programming Tagged With: , , ,

Named cell styles are still explicitly declared

January 5, 2012 By

Styling cells in Microsoft Excel has its difficulties (as I’ve written before). The biggest one is keeping track of all the indices. In Open XML SDK, you have the ability to have a named cell style.

At first glance, you might think that’s awesome. You just use a named cell style, and all the related styles are applied. It’s like there’s a red car that uses hybrid fuels. “Yes, I would like to have a red car that uses hybrid fuels.” Not quite.

You see, the named cell style is dependent on the implementing spreadsheet software. For example, Microsoft Excel has the “Normal”, “Bad”, “Good” and “Neutral” named cell styles. But Google Spreadsheets and OpenOffice.org Calc do not have to have those named cell styles, or even style it the same as Microsoft Excel. This is where Open XML SDK isn’t quite “open”… After much research and work, I discovered the SDK is basically Open-XML-ising Microsoft Excel (and Word and PowerPoint). I’m neutral on the stands of open source and “forcing standards”. I just use whatever there is, and make something within the limitations.

Because of the dependency, the underlying individual styles need to be declared explicitly. Actually more so because of the dependency.

So for my spreadsheet software library SpreadsheetLight, I used Excel as the guideline.

In researching Excel named cell styles, I had to look at the underlying XML files (because Open XML spreadsheets are made of XML files). While the Open XML SDK comes with a document explorer (the Productivity Tool), I needed to make notes and also that I felt the need to see the XML file itself, rather than using the explorer tool.

This gave me a problem because while XML files are supposed to be human-readable, it doesn’t make it easy to read. The “natural” XML file has no indents. Oh my Godiva chocolate, it’s so hard to read… Then I remembered I had an XML tool, XML Studio. I fired that up and a few clicks later, the XML file had nice indents and I could find out where the individual style tags were. XML Studio was amazing to use.

Disclaimer: I was given a free developer license of XML Studio by Liquid Technologies. But the software is really useful if you work with XML files a lot.

After doing my notes for a while, I discovered even that’s not enough. There were too many individual styles! I needed the indices for those styles, because only the index was referenced in the final style (CellFormat classes). I didn’t really feel up to annotating the indices… until I remembered my partially completed Open XML spreadsheet decompiler tool. When I created that tool, one of my aims was to put in comments on the index of the individual styles.

Note to Liquid Technologies: You might want to consider putting in XML comments on the index of an XML child tag with respect to its parent. But I don’t know if that’s useful to programming spheres other than Open XML…

Anyway, my hard work paid off, and SpreadsheetLight allows you to apply named cell styles. Here’s how the spreadsheet looks like:
Applying named cell styles

Note that some of the named cell styles use accent colours. The accent colours are part of the spreadsheet’s theme. So in offering named cell styles as a feature, I also had to allow you to create your own theme. And here’s the code using SpreadsheetLight:

System.Drawing.Color[] clrs = new System.Drawing.Color[12];
clrs[0] = System.Drawing.Color.White;
clrs[1] = System.Drawing.Color.Black;
clrs[2] = System.Drawing.Color.WhiteSmoke;
clrs[3] = System.Drawing.Color.DarkSlateGray;
clrs[4] = System.Drawing.Color.DarkRed;
clrs[5] = System.Drawing.Color.OrangeRed;
clrs[6] = System.Drawing.Color.DarkGoldenrod;
clrs[7] = System.Drawing.Color.DarkOliveGreen;
clrs[8] = System.Drawing.Color.Navy;
clrs[9] = System.Drawing.Color.Indigo;
clrs[10] = System.Drawing.Color.SkyBlue;
clrs[11] = System.Drawing.Color.MediumPurple;

SLDocument sl = new SLDocument("ColourWheel", "Castellar", "Harrington", clrs);

sl.SetRowHeight(6, 24);
sl.SetColumnWidth(1, 1);
sl.SetColumnWidth(2, 13);
sl.SetColumnWidth(3, 13);
sl.SetColumnWidth(4, 13);
sl.SetColumnWidth(5, 13);
sl.SetColumnWidth(6, 13);
sl.SetColumnWidth(7, 13);

sl.SetCellValue(2, 2, "Normal");
sl.ApplyNamedCellStyle(2, 2, SLNamedCellStyleValues.Normal);
sl.SetCellValue(2, 3, "Bad");
sl.ApplyNamedCellStyle(2, 3, SLNamedCellStyleValues.Bad);
sl.SetCellValue(2, 4, "Good");
sl.ApplyNamedCellStyle(2, 4, SLNamedCellStyleValues.Good);
sl.SetCellValue(2, 5, "Neutral");
sl.ApplyNamedCellStyle(2, 5, SLNamedCellStyleValues.Neutral);

sl.SetCellValue(3, 2, "Calculation");
sl.ApplyNamedCellStyle(3, 2, SLNamedCellStyleValues.Calculation);
sl.SetCellValue(3, 3, "Check Cell");
sl.ApplyNamedCellStyle(3, 3, SLNamedCellStyleValues.CheckCell);
sl.SetCellValue(3, 4, "Explanatory Text");
sl.ApplyNamedCellStyle(3, 4, SLNamedCellStyleValues.ExplanatoryText);
sl.SetCellValue(3, 5, "Input");
sl.ApplyNamedCellStyle(3, 5, SLNamedCellStyleValues.Input);

sl.SetCellValue(4, 2, "Linked Cell");
sl.ApplyNamedCellStyle(4, 2, SLNamedCellStyleValues.LinkedCell);
sl.SetCellValue(4, 3, "Note");
sl.ApplyNamedCellStyle(4, 3, SLNamedCellStyleValues.Note);
sl.SetCellValue(4, 4, "Output");
sl.ApplyNamedCellStyle(4, 4, SLNamedCellStyleValues.Output);
sl.SetCellValue(4, 5, "Warning Text");
sl.ApplyNamedCellStyle(4, 5, SLNamedCellStyleValues.WarningText);

sl.SetCellValue(6, 2, "Heading 1");
sl.ApplyNamedCellStyle(6, 2, SLNamedCellStyleValues.Heading1);
sl.SetCellValue(6, 3, "Heading 2");
sl.ApplyNamedCellStyle(6, 3, SLNamedCellStyleValues.Heading2);
sl.SetCellValue(6, 4, "Heading 3");
sl.ApplyNamedCellStyle(6, 4, SLNamedCellStyleValues.Heading3);
sl.SetCellValue(6, 5, "Heading 4");
sl.ApplyNamedCellStyle(6, 5, SLNamedCellStyleValues.Heading4);
sl.SetCellValue(6, 6, "Title");
sl.ApplyNamedCellStyle(6, 6, SLNamedCellStyleValues.Title);
sl.SetCellValue(6, 7, "Total");
sl.ApplyNamedCellStyle(6, 7, SLNamedCellStyleValues.Total);

sl.SetCellValue(8, 2, "Accent1");
sl.ApplyNamedCellStyle(8, 2, SLNamedCellStyleValues.Accent1);
sl.SetCellValue(8, 3, "Accent2");
sl.ApplyNamedCellStyle(8, 3, SLNamedCellStyleValues.Accent2);
sl.SetCellValue(8, 4, "Accent3");
sl.ApplyNamedCellStyle(8, 4, SLNamedCellStyleValues.Accent3);
sl.SetCellValue(8, 5, "Accent4");
sl.ApplyNamedCellStyle(8, 5, SLNamedCellStyleValues.Accent4);
sl.SetCellValue(8, 6, "Accent5");
sl.ApplyNamedCellStyle(8, 6, SLNamedCellStyleValues.Accent5);
sl.SetCellValue(8, 7, "Accent6");
sl.ApplyNamedCellStyle(8, 7, SLNamedCellStyleValues.Accent6);

sl.SetCellValue(9, 2, "Accent1Perc60");
sl.ApplyNamedCellStyle(9, 2, SLNamedCellStyleValues.Accent1Percentage60);
sl.SetCellValue(9, 3, "Accent2Perc60");
sl.ApplyNamedCellStyle(9, 3, SLNamedCellStyleValues.Accent2Percentage60);
sl.SetCellValue(9, 4, "Accent3Perc60");
sl.ApplyNamedCellStyle(9, 4, SLNamedCellStyleValues.Accent3Percentage60);
sl.SetCellValue(9, 5, "Accent4Perc60");
sl.ApplyNamedCellStyle(9, 5, SLNamedCellStyleValues.Accent4Percentage60);
sl.SetCellValue(9, 6, "Accent5Perc60");
sl.ApplyNamedCellStyle(9, 6, SLNamedCellStyleValues.Accent5Percentage60);
sl.SetCellValue(9, 7, "Accent6Perc60");
sl.ApplyNamedCellStyle(9, 7, SLNamedCellStyleValues.Accent6Percentage60);

sl.SetCellValue(10, 2, "Accent1Perc40");
sl.ApplyNamedCellStyle(10, 2, SLNamedCellStyleValues.Accent1Percentage40);
sl.SetCellValue(10, 3, "Accent2Perc40");
sl.ApplyNamedCellStyle(10, 3, SLNamedCellStyleValues.Accent2Percentage40);
sl.SetCellValue(10, 4, "Accent3Perc40");
sl.ApplyNamedCellStyle(10, 4, SLNamedCellStyleValues.Accent3Percentage40);
sl.SetCellValue(10, 5, "Accent4Perc40");
sl.ApplyNamedCellStyle(10, 5, SLNamedCellStyleValues.Accent4Percentage40);
sl.SetCellValue(10, 6, "Accent5Perc40");
sl.ApplyNamedCellStyle(10, 6, SLNamedCellStyleValues.Accent5Percentage40);
sl.SetCellValue(10, 7, "Accent6Perc40");
sl.ApplyNamedCellStyle(10, 7, SLNamedCellStyleValues.Accent6Percentage40);

sl.SetCellValue(11, 2, "Accent1Perc20");
sl.ApplyNamedCellStyle(11, 2, SLNamedCellStyleValues.Accent1Percentage20);
sl.SetCellValue(11, 3, "Accent2Perc20");
sl.ApplyNamedCellStyle(11, 3, SLNamedCellStyleValues.Accent2Percentage20);
sl.SetCellValue(11, 4, "Accent3Perc20");
sl.ApplyNamedCellStyle(11, 4, SLNamedCellStyleValues.Accent3Percentage20);
sl.SetCellValue(11, 5, "Accent4Perc20");
sl.ApplyNamedCellStyle(11, 5, SLNamedCellStyleValues.Accent4Percentage20);
sl.SetCellValue(11, 6, "Accent5Perc20");
sl.ApplyNamedCellStyle(11, 6, SLNamedCellStyleValues.Accent5Percentage20);
sl.SetCellValue(11, 7, "Accent6Perc20");
sl.ApplyNamedCellStyle(11, 7, SLNamedCellStyleValues.Accent6Percentage20);

sl.SetCellValue(13, 2, 12345678);
sl.ApplyNamedCellStyle(13, 2, SLNamedCellStyleValues.Comma);
sl.SetCellValue(13, 4, 12345678);
sl.ApplyNamedCellStyle(13, 4, SLNamedCellStyleValues.Comma0);
sl.SetCellValue(14, 2, 12345678);
sl.ApplyNamedCellStyle(14, 2, SLNamedCellStyleValues.Currency);
sl.SetCellValue(14, 4, 12345678);
sl.ApplyNamedCellStyle(14, 4, SLNamedCellStyleValues.Currency0);
sl.SetCellValue(15, 2, 123);
sl.ApplyNamedCellStyle(156, 2, SLNamedCellStyleValues.Percentage);

sl.SaveAs("NamedCellStyles.xlsx");

I set the column widths of the 2nd to 7th column, and the row height of the 6th row so it’s easier to see.

The main body text (the minor font) is in Harrington, and the title font (major font) is Castellar. You will note that even though the major font is supposedly used for heading and title texts, only the named cell style Title uses the major font. The headings 1 through 4 use the minor font.

Filed Under: Programming Tagged With: , , ,

Pictures in Excel have way too much power…

December 21, 2011 By

You’d think inserting a picture into an Excel worksheet is pretty straightforward. If you’re just inserting the picture, then it is.

Then you check what other properties you can play with manipulating the picture. Woah amigo! There’s a whole plethora of options!

Excel picture format options

That’s like giving the typical user a (good enough) subset of rendering functions, including 3D. And here I thought you could just resize the picture, and maybe rotate it… If you’re not going for full 3D manipulation as in OpenGL or DirectX, Excel will do it for you.

Excel respects alpha/transparency of the picture (right now, I only know of PNG images that contain alpha information AND is fairly prevalent). And you can outline the picture and fill the transparent portions with a colour.

SpreadsheetLight picture fill and outline

You can even do shadows!

SpreadsheetLight picture shadows

Do you know it can take a phenomenal amount of work to get shadows working in 3D renderings? You’ve got to know the light source position, calculate the rays of light blocked by an object to create the illusion of a shadow (by creating a polygon that’s the outline of that object as described by those blocked rays of light). Well, technically speaking, Excel has got it easy, since the object is always a rectangle (an image).

Speaking of light, we can also do reflections. Let’s run a full gamut of the 3D power.

SpreadsheetLight 3D pictures

I lost the software keys to my 2 3D rendering software (Bryce 4 and TrueSpace). It sucked that I couldn’t render landscapes anymore… But, I’m seeing Excel as an alternative to getting some 3D-ish images.

Well, the above screenshots were from a spreadsheet generated by my software library, SpreadsheetLight. The library is based on the Open XML SDK, which in turn is based on what Excel can do. Here’s how the source code looks like:

SLDocument sl = new SLDocument(SLThemeTypeValues.Oriel);

SLPicture pic;

sl.SetCellValue(2, 2, "Accent 1");
sl.ApplyNamedCellStyle(2, 2, SLNamedCellStyleValues.Accent1);
sl.SetCellValue(2, 3, "Accent 2");
sl.ApplyNamedCellStyle(2, 3, SLNamedCellStyleValues.Accent2);
sl.SetCellValue(2, 4, "Accent 3");
sl.ApplyNamedCellStyle(2, 4, SLNamedCellStyleValues.Accent3);
sl.SetCellValue(2, 5, "Accent 4");
sl.ApplyNamedCellStyle(2, 5, SLNamedCellStyleValues.Accent4);
sl.SetCellValue(2, 6, "Accent 5");
sl.ApplyNamedCellStyle(2, 6, SLNamedCellStyleValues.Accent5);
sl.SetCellValue(2, 7, "Accent 6");
sl.ApplyNamedCellStyle(2, 7, SLNamedCellStyleValues.Accent6);

pic = new SLPicture("mandelbrot.png");
// anchor at cell (4,2) with 0 horizontal and vertical offsets.
pic.SetRelativePositionInPixels(4, 2, 0, 0);
sl.InsertPicture(pic);

pic = new SLPicture("mandelbrot.png");
pic.SetRelativePositionInPixels(4, 6, 0, 0);
pic.SetSolidFill(DocumentFormat.OpenXml.Drawing.SchemeColorValues.Accent3, 20);
pic.SetSolidOutline(DocumentFormat.OpenXml.Drawing.SchemeColorValues.Accent4, 0);
pic.SetOutlineStyle(4, DocumentFormat.OpenXml.Drawing.CompoundLineValues.Double, DocumentFormat.OpenXml.Drawing.PresetLineDashValues.LargeDash, DocumentFormat.OpenXml.Drawing.LineCapValues.Round, SLPicture.SLPictureJoinType.Bevel);
sl.InsertPicture(pic);

sl.AddWorksheet("Sheet2");

sl.SetCellValue(2, 2, "Accent 1");
sl.ApplyNamedCellStyle(2, 2, SLNamedCellStyleValues.Accent1);
sl.SetCellValue(2, 3, "Accent 2");
sl.ApplyNamedCellStyle(2, 3, SLNamedCellStyleValues.Accent2);
sl.SetCellValue(2, 4, "Accent 3");
sl.ApplyNamedCellStyle(2, 4, SLNamedCellStyleValues.Accent3);
sl.SetCellValue(2, 5, "Accent 4");
sl.ApplyNamedCellStyle(2, 5, SLNamedCellStyleValues.Accent4);
sl.SetCellValue(2, 6, "Accent 5");
sl.ApplyNamedCellStyle(2, 6, SLNamedCellStyleValues.Accent5);
sl.SetCellValue(2, 7, "Accent 6");
sl.ApplyNamedCellStyle(2, 7, SLNamedCellStyleValues.Accent6);

pic = new SLPicture("mandelbrot.png");
pic.SetRelativePositionInPixels(4, 2, 0, 0);
// transparency 0%, blur 6pt, angle 90 degrees, distance 3pt
pic.SetInnerShadow(DocumentFormat.OpenXml.Drawing.SchemeColorValues.Accent1, 0, 6, 90, 3);
sl.InsertPicture(pic);

pic = new SLPicture("mandelbrot.png");
pic.SetRelativePositionInPixels(4, 6, 0, 0);
// transparency 50%, horizontal size 100%, vertical size -23%, horizontal skew -13.34%, vertical skew 0%
// blur 6pt, angle 45 degrees, distance 3pt
// origin at picture's bottom left, don't rotate with picture.
pic.SetPerspectiveShadow(DocumentFormat.OpenXml.Drawing.SchemeColorValues.Accent2, 50, 100, -23, -13.34m, 0, 6, 45, 3, DocumentFormat.OpenXml.Drawing.RectangleAlignmentValues.BottomLeft, false);
sl.InsertPicture(pic);

sl.AddWorksheet("Sheet3");

sl.SetCellValue(2, 2, "Accent 1");
sl.ApplyNamedCellStyle(2, 2, SLNamedCellStyleValues.Accent1);
sl.SetCellValue(2, 3, "Accent 2");
sl.ApplyNamedCellStyle(2, 3, SLNamedCellStyleValues.Accent2);
sl.SetCellValue(2, 4, "Accent 3");
sl.ApplyNamedCellStyle(2, 4, SLNamedCellStyleValues.Accent3);
sl.SetCellValue(2, 5, "Accent 4");
sl.ApplyNamedCellStyle(2, 5, SLNamedCellStyleValues.Accent4);
sl.SetCellValue(2, 6, "Accent 5");
sl.ApplyNamedCellStyle(2, 6, SLNamedCellStyleValues.Accent5);
sl.SetCellValue(2, 7, "Accent 6");
sl.ApplyNamedCellStyle(2, 7, SLNamedCellStyleValues.Accent6);

pic = new SLPicture("mandelbrot.png");
pic.SetRelativePositionInPixels(6, 3, 0, 0);
pic.SetFullReflection();
// width 6pt, height 6pt
pic.Set3DBevelBottom(DocumentFormat.OpenXml.Drawing.BevelPresetValues.Convex, 6, 6);
// width 3pt, height 4pt
pic.Set3DBevelTop(DocumentFormat.OpenXml.Drawing.BevelPresetValues.ArtDeco, 3, 4);
// extrusion colour transparency 0%, extrusion (or depth) height 15 pt
pic.Set3DExtrusion(System.Drawing.Color.Green, 0, 15);
// contour colour transparency 40%, contour width 4pt
pic.Set3DContour(DocumentFormat.OpenXml.Drawing.SchemeColorValues.Accent3, 40, 4);
pic.Set3DMaterialType(DocumentFormat.OpenXml.Drawing.PresetMaterialTypeValues.TranslucentPowder);
// 5 pt above "ground"
pic.Set3DZDistance(5);
// field of view 105 degrees, zoom 100%
// camera latitude, longitude, revolution in degrees (50, 40, 30)
// light rig latitude, longitude, revolution in degrees (0, 0, 30)
pic.Set3DScene(DocumentFormat.OpenXml.Drawing.PresetCameraValues.PerspectiveFront, 105, 100, 50, 40, 30, DocumentFormat.OpenXml.Drawing.LightRigValues.Sunrise, DocumentFormat.OpenXml.Drawing.LightRigDirectionValues.TopLeft, 0, 0, 30);
sl.InsertPicture(pic);

sl.SaveAs("Pictures.xlsx");

I use the enumerations available in Open XML SDK as far as possible. This means you can sort of port over any programs you have written using Open XML SDK. And also that I don’t have to invent my own enumerations that are essentially copies of the SDK enumerations.

One thing that confused me was the 3D rotation options in Excel.

SpreadsheetLight 3D picture rotation options

Excel used the vertical “axis” as the X value, and the horizontal “axis” as the Y value. To the user, the X, Y and Z values are like the first, second and third values of … something.

To me, X, Y and Z have special meanings, particularly when applied to the context of 3D. Those values don’t even increase in a consistent manner. The X value decreases if you use the right-hand rule (the left button), but the Y and Z values increase when you use the right-hand rule (left and right buttons respectively).

The Open XML SDK then complicates matters by using the terms latitude, longitude and revolution. Each ranging from 0 degrees to 359.9 degrees (or strictly less than 360 degrees in other words).

And in case you’re wondering about the state of the spreadsheet library progress, I froze the feature set of SpreadsheetLight for version 1. Otherwise, I’d never get it shipped. I’m now documenting the hashbrown out of the source code. The point is to make it ridiculously easy for a programmer to pick up SpreadsheetLight and start using it (without having to refer to a manual, which I’ll also be writing).

Filed Under: Curiosita Tagged With: , , , ,

Rich strings and inline strings in spreadsheets

December 13, 2011 By

Quite a while ago, I was mucking around in Excel and I discovered you can set the text in a cell to different fonts! Even different colours! (Ok, you’re probably bored of me going on about spreadsheets and Open XML, but it’s all I’m thinking about right now…) Granted, it’s a limited set of font style manipulations, but I’ve always thought the text in a cell was completely subjected to the cell style. I never thought you could change anything within a cell. I’m not an expert Excel user, ok?

The term used is an “inline string”. At least that’s what it’s referred to in the Open XML SDK, as the InlineString class.

This gave me a problem. How do I implement this in my spreadsheet library? It’s not as easy as just setting a cell value. You’d have to set up all the fonts and colours and bolds and italics and underlines, and then dump that bunch of stuff into a cell.

If you do it by hand, you’ll run (haha, foretelling a pun) into the DocumentFormat.OpenXml.Spreadsheet.Run class. Basically, you’re appending style runs. Here’s how you do it in Excel:

Inline string

You select the text in the formula box (not within the cell). Then you apply any font styles you want.

Aaannd… here’s where I tell you how my spreadsheet library is going to make your life easier. Here’s a sample screenshot of a result:

SpreadsheetLight inline string

Let’s look at the source code to generate that.

SLDocument sl = new SLDocument(SLThemeTypeValues.Metro);

SLFont font;
SLRstType rst;

font = new SLFont();
font.FontColor = System.Drawing.Color.Red;
rst = new SLRstType();
rst.AppendText("Roses are ");
rst.AppendText("red", font);
sl.SetCellValue(2, 2, rst.ToInlineString());

font = new SLFont();
font.FontColor = System.Drawing.Color.Blue;
rst = new SLRstType();
rst.AppendText("And violets are ");
rst.AppendText("blue", font);
sl.SetCellValue(3, 2, rst.ToInlineString());

font = new SLFont();
font.Bold = true;
font.Italic = true;
font.Underline = UnderlineValues.Double;
font.SetFont(FontSchemeValues.Major, 11);
font.SetFontThemeColor(SLThemeColorIndexValues.Accent2Color);
rst = new SLRstType();
rst.AppendText("But seriously...", font);
sl.SetCellValue(4, 2, rst.ToInlineString());

font = new SLFont();
font.SetFont(FontSchemeValues.Minor, 15);
font.SetFontThemeColor(SLThemeColorIndexValues.Accent1Color);
rst = new SLRstType();
rst.AppendText("you don't ", font);

font = new SLFont();
font.Italic = true;
rst.AppendText("have ", font);

rst.AppendText("to ");

font = new SLFont();
font.Underline = UnderlineValues.Single;
font.FontColor = System.Drawing.Color.OrangeRed;
rst.AppendText("emphasise ", font);

rst.AppendText("it ");

font = new SLFont();
font.Bold = true;
font.SetFontThemeColor(SLThemeColorIndexValues.Accent3Color);
rst.AppendText("so ", font);

rst.AppendText("much...");

sl.SetCellValue(5, 2, rst.ToInlineString());

SLStyle style = new SLStyle();
style.Fill.SetPatternForegroundColor(SLThemeColorIndexValues.Accent1Color);
style.Fill.SetPatternType(PatternValues.Solid);
sl.SetCellStyle(7, 2, style);
sl.SetCellValue(7, 3, "Accent 1");

style = new SLStyle();
style.Fill.SetPatternForegroundColor(SLThemeColorIndexValues.Accent2Color);
style.Fill.SetPatternType(PatternValues.Solid);
sl.SetCellStyle(8, 2, style);
sl.SetCellValue(8, 3, "Accent 2");

style = new SLStyle();
style.Fill.SetPatternForegroundColor(SLThemeColorIndexValues.Accent3Color);
style.Fill.SetPatternType(PatternValues.Solid);
sl.SetCellStyle(9, 2, style);
sl.SetCellValue(9, 3, "Accent 3");

style = new SLStyle();
style.Fill.SetPatternForegroundColor(SLThemeColorIndexValues.Accent4Color);
style.Fill.SetPatternType(PatternValues.Solid);
sl.SetCellStyle(10, 2, style);
sl.SetCellValue(10, 3, "Accent 4");

style = new SLStyle();
style.Fill.SetPatternForegroundColor(SLThemeColorIndexValues.Accent5Color);
style.Fill.SetPatternType(PatternValues.Solid);
sl.SetCellStyle(11, 2, style);
sl.SetCellValue(11, 3, "Accent 5");

style = new SLStyle();
style.Fill.SetPatternForegroundColor(SLThemeColorIndexValues.Accent6Color);
style.Fill.SetPatternType(PatternValues.Solid);
sl.SetCellStyle(12, 2, style);
sl.SetCellValue(12, 3, "Accent 6");

sl.SaveAs("InlineString.xlsx");

I’m using the Metro theme, which means the major Latin font is Consolas, and the minor Latin font is Corbel. The body text is in minor Latin font.

You’ll notice the 2 new classes, SLFont and SLRstType classes. The SLRstType models after the Open XML SDK (abstract) class RstType. I think it stands for “rich string type” (r + st + type).

I have filled in 6 cells with the accent colours, just so you can see how the colours are used. The accent colours are tied to the theme used, as is the major and minor Latin fonts. So if you use these colours and fonts, the text is automatically formatted against the current theme.

This is an advantage if you set the font as the minor Latin font, instead of directly as “Corbel”. If the user changes the theme of your resulting spreadsheet, the text changes to the new theme’s minor Latin font. Of course, if you want the text to stay as “Corbel”, regardless of the theme, then set it directly and explicitly as “Corbel”. The SLFont class has overloaded functions for this.

P.S. Can you tell I’m excited about this? I’m going to launch this baby. Soon. I will limit such “promotional” articles, but I really think Excel gives me surprises, so I thought you might want to know what your user thinks is normal Excel activity.

Filed Under: Programming Tagged With: , , ,

SpreadsheetLight gradient fill function

December 12, 2011 By

I’m fascinated by gradient fills in a spreadsheet. More specifically, why would anyone want to have a cell with gradient colours? Is a standard block colour fill not enough? Is a texture image fill not enough? I guess this comes down to the visual aspect. Humans like to look at pretty colours. Especially if you have to stare at financial figures in a spreadsheet for hours.

So, that spreadsheet library I’m working on? It can do this:
Gradient fills in SpreadsheetLight

The code to do that is

SLDocument sl = new SLDocument(SLThemeTypeValues.Flow);

SLStyle style = new SLStyle();
style.Fill.SetCustomGradient(GradientValues.Linear, 45, null, null, null, null);
style.Fill.AppendGradientStop(0, SLThemeColorIndexValues.Light2Color);
style.Fill.AppendGradientStop(0.2, System.Drawing.Color.Red);
style.Fill.AppendGradientStop(0.4, System.Drawing.Color.Green);
style.Fill.AppendGradientStop(0.6, System.Drawing.Color.Blue);
style.Fill.AppendGradientStop(0.8, System.Drawing.Color.Yellow);
style.Fill.AppendGradientStop(1, SLThemeColorIndexValues.Accent1Color, 0.5);

sl.SetCellValue(2, 3, "Custom gradient function");
sl.SetCellStyle(2, 2, style);

style = new SLStyle();
style.Fill.SetGradient(SLGradientShadingStyleValues.DiagonalDown2, SLThemeColorIndexValues.Accent2Color, SLThemeColorIndexValues.Accent6Color);

sl.SetCellValue(4, 3, "Built-in gradient function");
sl.SetCellStyle(4, 2, style);

sl.SetColumnWidth(2, 24);
sl.SetRowHeight(2, 108);
sl.SetRowHeight(4, 108);

sl.SaveAs("GradientFill.xlsx");

The gradient stops are positioned from 0.0 to 1.0. The “built-in” functions (simulating Excel) allow you to specify only 2 colours, even though you can have more.

You will notice that the library allows you to use both theme colours and System.Drawing.Color’s. You can even specify a tint modifier (as seen in the last gradient stop), which range from -1.0 to 1.0 (-1.0 being completely dark, and 1.0 being completely white).

You might also notice that you don’t need to declare many variables from the library. For most of your work, you just need to know SLDocument class (which handles most of the spreadsheet’s functions), and the SLStyle class (which handles all your styling needs). Most of the functions are overloaded, which is why the functions are squeezed into fewer classes.

Here’s my rationale: I walk into a party. I don’t really know anyone. I find one person that I recognise. Probably the host. Then I let the host introduce me to everything. Who the interesting people are. Where’s the food. Where’s the washroom. Look, I don’t mind meeting people in the party, but I’m not really into that particular party. I just want to mingle a little so I can tell my friend that yes, I was at the party. Mission accomplished…

Then I go to that other party that I really wanted to go. (no offense to the host of the first party. “None taken.” Aww, isn’t he a nice guy?).

I don’t want to burden you with yet another software library to learn. So I’ve made it easy. 2 classes for most of your needs. If you’re using one of them intelligent code editing software, you’d get auto-completion too. Exploring what else a class can do for you is just a “.” away.

Yes, I’m finishing the library. It’ll be ready soon, ok? Just a couple of features more, and some testing, and I’ll launch version 1 of the product. I don’t give a flying fishball about eternal software betas. (Just launch already, dammit! Stupid software startups…)

Filed Under: Programming Tagged With: , , , , ,

Working on spreadsheet software library

December 5, 2011 By

As a natural and logical extension from my Open XML spreadsheet guide, I’m writing a software library to create and manipulate Open XML spreadsheets. (Never mind that decompiler project I was working on… 2 months of coding… sunk cost… moving on…).

I did some research (ok, an inordinate amount of research…) on the available spreadsheet software libraries out there, both free and commercial, both supporting Open XML (or .xlsx in any case) and the old .xls (Microsoft Excel in binary). I have 2 observations.

First, there’s a plethora of classes in the library. It’s sort of expected. There’s support for a lot of functionality, and it just burgeoned into many classes. Personally, I hate it when I have to learn a new library. There’s a whole bunch of documentation and classes I have to read up on and experiment to just do a simple thing (printing a string of characters is the first thing I try). When I first encountered the .NET Framework, I was crushed. It’s redeeming feature was its extensive documentation, which made learning easier.

Second, even though there’s support for a lot of functionality, it still takes quite a bit of code to accomplish what you want done (granted, much less than if you wrote low level code). Hey I wrote a guide on Open XML spreadsheets, I know how many lines of code needed to just create an empty Excel file, ok?

But these are spreadsheet software libraries!. They’re supposed to make your life easier. In fact, much easier.

I read that when the iPhone was designed, the engineers told Steve Jobs that it needed to have 4 or 5 buttons. Steve Jobs said no. One button (to rule them all). The iPhone now only has the 1 button.

So I took inspiration from that and designed my library to have that quality. Alright, alright, here’s a code sample:

SLDocument sl = new SLDocument();
sl.Save();

That will save an empty Excel file named “Book1.xlsx”. What, not Hello World enough for you?

SLDocument sl = new SLDocument();
sl.SaveAs("HelloWorld.xlsx");

There. Now the file is named “HelloWorld.xlsx”. What, sheet name? Most (if not all) of the libraries I researched required you to add a new worksheet to an empty file. All spreadsheets have at least one worksheet. Why force the programmer to do it anyway? You don’t see Microsoft Excel forcing the user to add worksheets in a newly created spreadsheet file, right? (Excel even has 3 worksheets added by default).

Alright, fine. The first worksheet’s name is by default “Sheet1″. You can rename it.

SLDocument sl = new SLDocument();
sl.RenameWorksheet(sl.DefaultFirstSheetName, "Hello");
sl.SaveAs("HelloWorld.xlsx");

There, happy? So, how do we set cell values?

SLDocument sl = new SLDocument();
sl.RenameWorksheet(sl.DefaultFirstSheetName, "Hello");
sl.SetCellValue(2, 3, 3.14159);
sl.SetCellValue(2, 4, "This is PI");
sl.SaveAs("HelloWorld.xlsx");

The cell with row 2, column 3 will have the value of PI. The cell with row 2, column 4 will have the string “This is PI”. Yes, the library supports cell references such as “C2″ and “D2″. My opinion? They make better sense to a user with visual interface to the spreadsheet. It’s much harder to use when you’re programming with a non-visual interface to the spreadsheet. Good luck iterating through rows 2 to 500,000, with columns 1 to 1000 (financial reports, I’m looking at you…).

Want to add a new worksheet?

SLDocument sl = new SLDocument();
sl.RenameWorksheet(sl.DefaultFirstSheetName, "Hello");
sl.SetCellValue(2, 3, 3.14159);
sl.SetCellValue(2, 4, "This is PI");
sl.AddWorksheet("SecondWorksheet");
sl.SetCellValue(5, 5, "Why am I not first?");
sl.SaveAs("HelloWorld.xlsx");

Hey, a software library is supposed to make your life easy. The second worksheet’s name is *drum roll*, “SecondWorksheet”. The string “Why am I not first?” is in row 5, column 5 of the newly added worksheet. How does the library know which worksheet to add which cell value? By magic. Ok, fine, it automatically keeps track of worksheets.

When a user enters a cell value in Excel, does the user need to know which worksheet? No, because that information is implied. The user knows which worksheet because the user chose it already. And so does this software library.

Oh yeah, I even have basic theme support!

SLDocument sl = new SLDocument(SLThemeTypeValues.Flow);
sl.RenameWorksheet(sl.DefaultFirstSheetName, "Hello");
sl.SetCellValue(2, 3, 3.14159);
sl.SetCellValue(2, 4, "This is PI");
sl.SaveAs("HelloWorld.xlsx");

That gives you the Flow theme, one of the built-in themes in Microsoft Excel (note: only the fonts and font colours are supported). You can even design your own custom theme.

System.Drawing.Color[] clrs = new System.Drawing.Color[12];
clrs[0] = System.Drawing.Color.White;
clrs[1] = System.Drawing.Color.Black;
clrs[2] = System.Drawing.Color.WhiteSmoke;
clrs[3] = System.Drawing.Color.DarkSlateGray;
clrs[4] = System.Drawing.Color.DarkRed;
clrs[5] = System.Drawing.Color.OrangeRed;
clrs[6] = System.Drawing.Color.DarkGoldenrod;
clrs[7] = System.Drawing.Color.DarkOliveGreen;
clrs[8] = System.Drawing.Color.Navy;
clrs[9] = System.Drawing.Color.Indigo;
clrs[10] = System.Drawing.Color.SkyBlue;
clrs[11] = System.Drawing.Color.MediumPurple;

SLDocument sl = new SLDocument("ColourWheel", "Castellar", "Harrington", clrs);
sl.RenameWorksheet(sl.DefaultFirstSheetName, "Hello");
sl.SetCellValue(2, 3, 3.14159);
sl.SetCellValue(2, 4, "This is PI");
sl.SaveAs("HelloWorld.xlsx");

There are 12 colours you need to define. These correspond to the 2 light colours, 2 dark colours, 6 accent colours, the hyperlink colour and the followed hyperlink colour. “ColourWheel” is the theme name, “Castellar” is the major Latin font and “Harrington” is the minor Latin font. The major Latin font is used when you apply the Title named cell style. The minor Latin font is basically the body font.

What named cell style? A customer suggested supporting the feature.

Named cell styles

So how do you apply it?

sl.ApplyNamedCellStyle(2, 3, SLNamedCellStyleValues.Good);
sl.ApplyNamedCellStyle(2, 4, SLNamedCellStyleValues.Accent1);

I am finishing up version 1 of the library, and it will soon be available. I’m targeting a launch in January 2012. The software library will be called SpreadsheetLight. The primary idea is for it to be simple and clean. Simple for you to use, and you write clean code when you use it.

My internal tests show that SpreadsheetLight runs faster than 2 other free libraries. I won’t tell you which 2, because it’s not relevant, and because I’m automatically biased, and because it’s just not nice to the other programmers who contributed to those 2 libraries. The point is that it runs fast and is effective, which I’m happy about.

Filed Under: Programming Tagged With: , , , , ,

Calculating Excel spreadsheet column names

November 8, 2011 By

I’ve been working with Open XML spreadsheets for the past, I don’t know how long… A year? I just realised that getting that Excel column header name is a frequent task. You know, given that it’s the 4th column, it’s “D”. I don’t work frequently with spreadsheets with lots of columns. So it was interesting that the 26th column is “Z” and the 27th column becomes “AA”. Basically, base-26 arithmetic, using the 26 letters of the English alphabet as tokens.

There are probably lots of code snippets out there showing you how to calculate a column name given the column index. Here’s mine:

string[] saExcelColumnHeaderNames = new string[16384];
string[] sa = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
string s = string.Empty;
int i, j, k, l;
i = j = k = -1;
for (l = 0; l < 16384; ++l)
{
    s = string.Empty;
    ++k;
    if (k == 26)
    {
        k = 0;
        ++j;
        if (j == 26)
        {
            j = 0;
            ++i;
        }
    }
    if (i >= 0) s += sa[i];
    if (j >= 0) s += sa[j];
    if (k >= 0) s += sa[k];
    saExcelColumnHeaderNames[l] = s;
}

That gives you a zero-based indexing version. So to get the 30th column name, you use saExcelColumnHeaderNames[29].

In case you’re wondering, 16384 is the maximum number of columns supported by Excel 2010.

You will notice that it’s not a function given the column index. I find that not as useful. Look, typically when you need the column name, you probably also need to get it frequently, usually with different parameters.

What I did was to store all the calculation results into a string array. Then you reference it with an index. The calculation function typically is a O(n) operation. With you needing to use the function multiple times, your whole algorithm probably goes up to O(n^2).

My method is also an O(n) operation. But referencing a string array is I think an O(1), meaning it’s a constant. I’ve never been good with big O notation…

This style of solving the problem is called pre-calculation. Pre-calculation is especially useful in the games development region, where speed is important. For example, selected values of sine and cosine were pre-calculated and stored in arrays, for use in the numerous 3D/2D calculations in games. Calculating sine’s and cosine’s in real-time were detrimental to a speedy game.

That’s not as useful now because you need a fuller range of floating point values as input. But the concept is still useful.

I think I read somewhere (while I was doing hobbyist game development) this quote:

Pre-calculate everything!

Maybe computers are now much faster. I don’t care. That doesn’t give you an excuse to be sloppy. It’s an optimisation that doesn’t take much effort.

If you need to calculate it, see if you can calculate it just once.

Filed Under: Programming Tagged With: , , , , ,

Decompiling Open XML spreadsheets

November 3, 2011 By

Ok, I’m going to reveal the big secret project that I’ve been working on for the last 2 months. I’m writing a software program that will decompile Open XML spreadsheets into C# and VB.NET source code.

Now I know what you’re thinking. “But Vincent, there’s that SDK Productivity Tool that does that already!”

Frankly, when I started the project, I didn’t even think about the SDK tool. But, when I looked at the generated source code from the SDK tool, I found it… hideous. There were 2 things I found annoying:

  • New classes were created willy-nilly
  • Properties were dumped into class instantiation using object initialisers

The first point meant that most of the classes were created one-off. It didn’t matter if you needed a class of type SomeClass multiple times. The SDK tool simply created another class of type SomeClass. If that class type was used multiple times, you’ll see variables named someClass1, someClass2 all the way to someClass21. It’s why I wrote about multiple use variables versus multiple variables.

The second point meant that if a class has many properties, you might end up with something like:

CellFormat cellFormat3 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)10U, FillId = (UInt32Value)9U, BorderId = (UInt32Value)0U, ApplyNumberFormat = false, ApplyBorder = false, ApplyAlignment = false, ApplyProtection = false };

That’s one line of code.

The problem I have with object initialisers is when you need to comment something in between. Commenting in C# and VB.NET means an entire line is commented, although C# offers the /* comment */ variant. There’s just no easy way to do so. Compare with this:

cellFormat = new CellFormat();
cellFormat.NumberFormatId = 0U;
cellFormat.FontId = 11U;
cellFormat.FillId = 10U;
cellFormat.BorderId = 0U;
cellFormat.ApplyNumberFormat = false;
cellFormat.ApplyBorder = false;
cellFormat.ApplyAlignment = false;
cellFormat.ApplyProtection = false;

I just find that easier to pick and choose stuff I don’t want.

Now the big advantage (my differentiation or unique selling proposition) is that I offer VB.NET too. The SDK tool doesn’t. Here’s a snippet:

run = New Run()
run.RunProperties = New RunProperties()
run.RunProperties.Append(New FontSize() With {.Val = 11R})
clr = New Color()
clr.Theme = 1UI
run.RunProperties.Append(clr)
run.RunProperties.Append(New RunFont() With {.Val = "Calibri"})
run.RunProperties.Append(New FontFamily() With {.Val = 2})
run.RunProperties.Append(New FontScheme() With {.Val = FontSchemeValues.Minor})

You will notice that I do use object initialisers. “That’s hypocritical of you!”. Perhaps, but I use them when the number of properties is small. I’ve kept it to 3 for now. Object initialisers in my case also made it easier that I don’t have to declare and instantiate new classes with actual variable names.

I understand why the SDK tool generates source code the way it does. It has to do with completely iterating through every single part and class of the root class SpreadsheetDocument. If you’ve ever written code to traverse a tree structure, you’ll know how tedious it can be.

The one thing the SDK tool lacks about the source code it generates is context. It runs through the entire Open XML document structure like a squirrel looking for every single acorn on a tree. It doesn’t stop to check any acorn for size, defects or even if it’s an acorn. Look, winter’s coming soon, and the squirrel doesn’t have all day telling you that this particular acorn is related to that particular acorn, and no it doesn’t care how big the acorn is, it’s got the teeth to eat it, ok?

Why are we talking about squirrels again?

So, after about 20 thousand lines of code, I’m just barely getting my software into beta mode. Halfway through that, my heart sank with the enormity of the task. In order to generate more readable code, I cannot iterate through the XML tree structure like the SDK tool. I had to stop and make sense of what the class was.

That made me look at the SDK help file and the ECMA-376 specification file way too much… Did you know the ECMA spec is like over 5000 pages long? And that’s part 1. Parts 2, 3 and 4 are smaller, but still heavyweights in their own right. And there are so many classes and child classes and grandchildren classes and properties and…

I’m going to at least make a valiant effort to have the software self-complete on a subset of the Excel functionality (and thus a subset of the SDK). If you’re interested, I present to you SoxDecompiler. As of this writing, I’m just trying to see if people are interested in the software, so it’s just a page to collect email addresses of the people interested in the software. I think I wrote “interested” way too many times…

For some reason, the name conjures an image of a thread slowly unravelling a sock. But I like it. It stands for “Spreadsheet Open XML Decompiler”.

Filed Under: Programming Tagged With: , , ,

DefinedNames in Cells for Open XML spreadsheets

October 14, 2011 By

So a while back, a customer of mine asked me if I knew how to set names (or labels) to cells in a spreadsheet, so that a cell formula just referenced those names. Frankly, I didn’t even know I could do that. I’m not an Excel wizard, as you can tell.

In case you’re in a hurry, here’s the code and the resulting Excel spreadsheet. The code runs against the Open XML SDK.

To my surprise, the name given to the spreadsheet cell isn’t defined at the Cell class level. You’d think you would name your child and make sure to slap a name tag on your progeny just so everyone knows what to call your offspring, right? After looking through the documentation, and thinking it through, I guess it makes sense to separate it. It works by having a central depository with all the available names, contained within DefinedNames and DefinedName classes. Then the names are available throughout the spreadsheet’s workbook.

It’s sort of like the SharedStringTable, where every piece of text is stored in a SharedStringItem, and referenced with an index. This has problems, in that I don’t know what is contained in the Cell class itself, but I don’t really want to go there right now…

Let’s go through the code a bit. Here’s the part where you define DefinedName(s):

defname = new DefinedName();
defname.Name = "PrimeNum1";
defname.Text = "Sheet1!$C$2";
defnames.Append(defname);

defname = new DefinedName();
defname.Name = "PrimeNum2";
defname.Text = "Sheet1!$C$3";
defnames.Append(defname);

defname = new DefinedName();
defname.Name = "PrimeNum3";
defname.Text = "Sheet1!$C$4";
defnames.Append(defname);

defname = new DefinedName();
defname.Name = "PrimeNum4";
defname.Text = "Sheet1!$C$5";
defnames.Append(defname);

In case you’re unfamiliar with Excel, “Sheet1!$C$5″ means the cell C5 of the sheet named “Sheet1″. The dollar sign acts as a separator, I think. In this case, the relevant cells are C2, C3, C4 and C5 (containing the 1st, 2nd, 3rd and 4th prime numbers). And then we have this part:

r = new Row();
r.RowIndex = 6;
c = new Cell();
c.CellReference = "B6";
c.DataType = CellValues.String;
c.CellValue = new CellValue("SUM");
r.Append(c);
c = new Cell();
c.CellReference = "C6";
c.CellFormula = new CellFormula("SUM(PrimeNum1, PrimeNum2, PrimeNum3, PrimeNum4)");
c.CellValue = new CellValue("17");
r.Append(c);
sd.Append(r);

Note this particular line:

c.CellFormula = new CellFormula("SUM(PrimeNum1, PrimeNum2, PrimeNum3, PrimeNum4)");

That’s our defined names in effect. Otherwise, we would use this:

c.CellFormula = new CellFormula("SUM(C2, C3, C4, C5)");
// or even
// c.CellFormula = new CellFormula("SUM(C2:C5)");

I teach you more about the CellFormula class, as well as a whole bunch of Open XML concepts in my programming guide.

Filed Under: Programming Tagged With: , , , , ,

WOX or online business guide?

May 25, 2011 By

Alright, in my quest to feed myself, I have a few product ideas down the pipeline. Now that version 2 of my spreadsheet Open XML guide is out (privately and affectionately referred to as SOX because the full acronym SOXFS is pronounced “socks fuzz” and doesn’t sound sexy), I am going to create something else.

Here’s where you can influence what I’m going to create. I’m going to ask you what would be useful to you, and I’ll go create that. More details in the following paragraphs.

Naturally, the next logical products should be Open XML guides for Microsoft Word and PowerPoint, named (for consistency) Wordprocessing Open XML From Scratch and Presentation Open XML From Scratch. Or referred to internally as WOX and POX. *grin* That’s 2 product ideas.

I’m also thinking of writing a guide for setting up a small online business, specifically for technically proficient people. You know those online business guides out there? They range from “how to make money online” to “how to make money online in niches”, from setting up small to big online businesses, from setting up businesses in the boardgames niche to the herbal niche to dating niche to the blogging niche.

You know what’s common in all of them? The authors all assume that you’re technically inept, that you don’t know anything about (or fearful of) HTML, CSS, WordPress, PayPal (payment integration), buying and setting up domains, setting up email lists, designing logos or ebook covers or website/blog layouts. The authors will give you step-by-step instructions to whatever they’re teaching and showing you, which is crucial to getting the “common” people (I mean no disrespect with that term. Would you feel better if I called them the “masses”?). They will tell you to get a technical person to help you with your technical problems. Get it outsourced, get a friend to help, but just don’t do it yourself because it wastes time.

Well, technically proficient people face different problems. Specifically, pride and fear of letting go. Our technical proficiency can be a bane at times.

“WordPress runs on PHP? I don’t know PHP, but I’m good at programming. How hard can PHP be?”

Why WordPress? What about Django or .NET Framework or whatever-new-fangled-thing-out-there? Because you’re running a business, and not indulging in your pet peeves. See the problem of pride I mentioned above. Hey, I’m a .NET programmer and the LAMP stack is extremely great at business setup ease. I don’t even deal with the LAMP stack. I let the web host deal with it. Remember, you’re running a business.

That said, any time those authors say it’s a technical thing, you can probably solve it quickly. Maybe in a few minutes. Maybe it takes a couple of hours. So depending on your technical proficiency, you can save a bunch of money from not outsourcing. But there’s the price of your time…

Anyway, that online business guide I might be writing will have everything I know about product creation, how to get product ideas, marketing, sales page writing, setting up the whole sales funnel, why you need an email list (think of it as your CRM). I will teach you the littlest number of activities you need to do to get something up and running.

Let me tell you, there are a lot of these “how to make money online” kind of guides and products out there. I’ve read and gone through many of the free offerings, whether they be blogs or PDF reports (one of the “tricks” used to get people to sign up). The current most-used method is to have a free video giving you valuable information, but you have to sign up to a mailing list. Your email address is the price to watch that video. Why do they want your email address? Because they’re building their CRM.

I’ve even bought a few of these products. There’s a traffic generation product, to generate website or blog traffic because apparently your product/service will fail without millions of views. I’ve been in 2 membership sites, teaching about how to use a blog as a means to creating cashflow and how a membership site is the greatest thing an online business owner can have. Membership sites are great because of the recurring income (see website hosting or Basecamp from 37signals or anything with a subscription model). I’ve bought sleazy products before, and I’ve bought ethical and useful products too.

There will be no startup stories, although I worked in a startup before. So maybe I can tell you about my experience. I can tell you it’s nothing like those stories in Silicon Valley… I suggest you think about a problem people are having and you solve that problem. A startup may be the solution, but you don’t need a development team, venture capitalists, and massive numbers of users. My blog doesn’t have thousands or hundreds of thousands of readers, yet I still manage to sell copies of my guide. Solve a problem people are facing.

So let me know if you’re more interested in another Open XML guide (for Word or PowerPoint) or this online business guide. Write in the comments or you can contact me privately if you want. If I don’t get any particular preferences from you, I’ll just go with whatever I can produce in the shortest amount of time and effort. Like I said, I need to eat…

Filed Under: Curiosita Tagged With: , , , , , , ,
« Older Posts