SpreadSheetML and Row/Col indexing
Today I discovered that SpreadSheetML has a very minimalistic requirement for indexing of cells and rows. Whenever cells or rows are adjacent the index of the cell/row to the right/bottom doesn’t need an explicit index attribute. Numbering is assumed to start from 1 if the first row or cell doesn’t give an index. This also works for skipping rows and cells without having to describe empty cells. I wanted to give an example, but I just discovered I don’t know how to quote xml fragments in this blog system yet…
This sounds really great until you find out that you can merge cells. When cells are merged the upper left corner cell is marked with some combination of the attributes, mergedown and mergeacross (probably should have been mergeright). So, if a cell has the attribute mergedown=”1″, then that cell takes up two spaces — one in the row it’s in and one in the row below it. If you try to put anything in the row below it, excel will complain that your spreadsheet is invalid. To get around this you have to use cell indexes to explicitly skip over that column. This means that to correctly generate any row given a sequence of adjacent cells you have to look at all the mergedown attribute in all the cells in the column above your cell. Actually, it gets worse…
Remember I mentioned mergeacross? Well, it turns out that merges can be rectangular regions. To correctly skip over “used” cells from a mergeacross, mergedown combination you’ll need to look at all the cell attributes for cells that are in a row before your row and all the columns upto your column.
Perhaps the moral of the story is not to try to generate a row of adjacent cells alone but to instead store the cells as a grid in memory taking into account merges. The disappointing thing for me is that I wanted to transform database queries into SpreadSheetML using xslt. The xslt would weave in the style information for me. Of course, I wanted to have merged cells in the headers columns of my generated spreadsheets. The number and size of the merged columns depends on the data and quickly I see that this can get tricky.
Oh well…back to the design table.