Announcing SpreadsheetLight version 1.1

February 20, 2012 By

After much researching and programming and suffering (and tea), I’m happy to announce that version 1.1 of SpreadsheetLight is up! SpreadsheetLight is a spreadsheet manipulation library based on Open XML.

I added some functionality for defined names and clearing out data from rows/columns/cells. You can also split worksheet panes now. Version 1 didn’t have split pane functionality because I couldn’t find a way to make it easy for the programmer. Well, I wrote a function that allows you to do it with a minimum of fuss.

But the main bulk of version 1.1 updates is the insert/delete rows/columns part. Say you want to insert 4 rows at the 3rd row. This means all the rows from the 3rd row (including the 3rd row) must be shifted downwards 4 rows. Every merged cell, cell formula, table and defined name must be accounted for.

Merged cells need to be enlarged or trimmed off as needed. Or just simply deleted if it’s entirely within the delete range.

Tables need to be updated so the cell references within are correctly adjusted. If it used to contain C3:F6, after inserting 4 rows, it has to be C7:F10.

Cell formulas and defined names are sort of related, with the former typically of a more complex form than the latter. Consider taking on something like this:

= A1 +LOG10 +”BCD32″ -SUM($H6:Sheet1!K$9) *LOG10($F$5) + BCD32 – SUM(F2:G3)

That has to become this:

= A1 +LOG14 +”BCD32″ -SUM($H10:Sheet1!K$13) *LOG10($F$9) + BCD36 – SUM(F2:G7)

Note the LOG10 part. The first instance is a cell reference. The second is the logarithm function, base 10, of the cell originally $F$5. Note also that the first instance of BCD32 is a literal string. Literal strings should not be changed, even if they contain a valid cell reference.

That part took me quite a while. Let’s just say I’m glad I’m fairly proficient in regular expressions…

The only thing I left out was pictures (aka worksheet drawings) for insert/delete rows/columns. To create similar behaviour to when you’re doing it in Excel requires more arcane coding…

So if you’re looking for a spreadsheet library that’s light-weight yet capable of heavy-weight functionality, with simple-to-use functions, then consider SpreadsheetLight. Or tell your boss.

I’ve also included a Platinum version, which includes the source code of SpreadsheetLight (licensed with the MIT license). So if you want to know how I accomplished all that magic, consider the Platinum version then. Lots of comments to make the hairy parts less hairy…

Filed Under: Programming Tagged With: , ,

Image rotation with bilinear interpolation and alpha progressive borders

February 2, 2012 By

So a blog reader, Fabien Auréjac, emailed me an improvement over the code I posted on image rotation. Here’s the one with bilinear interpolation, and here’s the one with bilinear interpolation and no clipping.

Fabien translated the core of my code into PHP. The improvement was on assigning alpha values to the edge pixels after rotation. Edge pixels are pixels beside the “blank” pixels (I used black in my code, for instance). The alpha values mean the edge pixels are “softer” and thus the resulting image looks smoother.

I suppose if you really want to, you could also “dumb down” the values of the red, green and blue colour components for more softening (in addition to the alpha component). I say “dumb down” because the blank pixels I used are black (meaning zero for the RGB values). You’re free to go ahead and do more interpolation.

Fabien has given permission for me to post the code here. I’ll leave it as an exercise for you to translate to your programming language.

$distOmbre=3;
$flouOmbre=4;
$angleRot=60;
$img=imagecreatefromjpeg("media/diapo-Chinon.jpg");
$size=getimagesize("media/diapo-Chinon.jpg");
$LsupH=($size[0]>$size[1])?1:0;
$angleBool=(int)($angleRot/90)%2==0?0:1;
if (($angleBool+$LsupH)%2==0) {
	$largeur=round(abs($size[0]*sin($angleRot%90*pi()/180))+abs($size[1]*sin((90-$angleRot%90)*pi()/180)));
	$hauteur=round(abs($size[0]*cos($angleRot%90*pi()/180))+abs($size[1]*cos((90-$angleRot%90)*pi()/180)));
} else {
	$largeur=round(abs($size[0]*cos($angleRot%90*pi()/180))+abs($size[1]*cos((90-$angleRot%90)*pi()/180)));
	$hauteur=round(abs($size[0]*sin($angleRot%90*pi()/180))+abs($size[1]*sin((90-$angleRot%90)*pi()/180)));
}
$largeur+=$distOmbre+$flouOmbre*2;
$hauteur+=$distOmbre+$flouOmbre*2;
$angleRot*=pi()/180;
$imgRot=imagecreatetruecolor($largeur, $hauteur);
imagealphablending($imgRot, true);
imageantialias($imgRot, true);
for ($i=0; $i<$hauteur; $i++) {
	for ($j=0; $j<$largeur; $j++) {
		// convert raster to Cartesian
        $x = $j - $largeur*0.5;
        $y = $hauteur*0.5 - $i;

        // convert Cartesian to polar
        $fDistance = sqrt($x * $x + $y * $y);
   	 	$fPolarAngle = atan2($y, $x);

        // the crucial rotation part
        // "reverse" rotate, so minus instead of plus
        $fPolarAngle -= $angleRot;
		 // convert polar to Cartesian
        $fTrueX = $fDistance * cos($fPolarAngle);
        $fTrueY = $fDistance * sin($fPolarAngle);

        // convert Cartesian to raster
        $fTrueX = $fTrueX + $size[0]*0.5;
        $fTrueY = $size[1]*0.5 - $fTrueY;

        $iFloorX = (int)(floor($fTrueX));
        $iFloorY = (int)(floor($fTrueY));
        $iCeilingX = (int)(ceil($fTrueX));
        $iCeilingY = (int)(ceil($fTrueY));
        //echo $fTrueX." ".$fTrueY." ".$iFloorX." ".$iCeilingX." ".$iFloorY." ".$iCeilingY."<br>";
		if ($iFloorX >= 0 && $iCeilingX >= 0 && $iFloorX < $size[0] && $iCeilingX < $size[0] && $iFloorY >= 0 && $iCeilingY >= 0 && $iFloorY < $size[1] && $iCeilingY < $size[1]) {
			$fDeltaX = $fTrueX - $iFloorX;
			$fDeltaY = $fTrueY - $iFloorY;
			$clrTopLeft = imagecolorat($img, $iFloorX, $iFloorY);
			$colorsTopLeft = imagecolorsforindex($img, $clrTopLeft);
			$clrTopRight = imagecolorat($img, $iCeilingX, $iFloorY);
			$colorsTopRight = imagecolorsforindex($img, $clrTopRight);
			$clrBottomLeft = imagecolorat($img, $iFloorX, $iCeilingY);
			$colorsBottomLeft = imagecolorsforindex($img, $clrBottomLeft);
			$clrBottomRight = imagecolorat($img, $iCeilingX, $iCeilingY);
			$colorsBottomRight = imagecolorsforindex($img, $clrBottomRight);
			// linearly interpolate horizontally between top neighbours
			$fTopRed = (1 - $fDeltaX) * $colorsTopLeft['red'] + $fDeltaX * $colorsTopRight['red'];
			$fTopGreen = (1 - $fDeltaX) * $colorsTopLeft['green'] + $fDeltaX * $colorsTopRight['green'];
			$fTopBlue = (1 - $fDeltaX) * $colorsTopLeft['blue'] + $fDeltaX * $colorsTopRight['blue'];
			// linearly interpolate horizontally between bottom neighbours
			$fBottomRed = (1 - $fDeltaX) * $colorsBottomLeft['red'] + $fDeltaX * $colorsBottomRight['red'];
			$fBottomGreen = (1 - $fDeltaX) * $colorsBottomLeft['green'] + $fDeltaX * $colorsBottomRight['green'];
			$fBottomBlue = (1 - $fDeltaX) * $colorsBottomLeft['blue'] + $fDeltaX * $colorsBottomRight['blue'];
			// linearly interpolate vertically between top and bottom interpolated results
			$iRed = (int)(round((1 - $fDeltaY) * $fTopRed + $fDeltaY * $fBottomRed));
			$iGreen = (int)(round((1 - $fDeltaY) * $fTopGreen + $fDeltaY * $fBottomGreen));
			$iBlue = (int)(round((1 - $fDeltaY) * $fTopBlue + $fDeltaY * $fBottomBlue));
			// make sure colour values are valid
			if ($iRed < 0) $iRed = 0;
			if ($iRed > 255) $iRed = 255;
			if ($iGreen < 0) $iGreen = 0;
			if ($iGreen > 255) $iGreen = 255;
			if ($iBlue < 0) $iBlue = 0;
			if ($iBlue > 255) $iBlue = 255;
			if ($iFloorX > 0 && $iCeilingX > 0 && $iFloorX < $size[0]-1 && $iCeilingX < $size[0]-1 && $iFloorY > 0 && $iCeilingY > 0 && $iFloorY < $size[1]-1 && $iCeilingY < $size[1]-1) {
				$colorallocation=imagecolorallocate($imgRot, $iRed, $iGreen, $iBlue);
				imagesetpixel($imgRot, $j, $i, $colorallocation);
			} else if ($iFloorX == 0 && $iFloorY >= 0 && $iCeilingY >= 0 && $iFloorY < $size[1] && $iCeilingY < $size[1]) {//left
				$alpha=round((1-abs($fDeltaX))*127);
				$colorallocation=imagecolorallocatealpha($imgRot, $iRed, $iGreen, $iBlue, $alpha);
				imagesetpixel($imgRot, $j, $i, $colorallocation);
			} else if ($iFloorX >= 0 && $iCeilingX >= 0 && $iFloorX < $size[0] && $iCeilingX < $size[0] && $iFloorY == 0) {//top
				$alpha=round((1-abs($fDeltaY))*127);
				$colorallocation=imagecolorallocatealpha($imgRot, $iRed, $iGreen, $iBlue, $alpha);
				imagesetpixel($imgRot, $j, $i, $colorallocation);
			} else if ($iCeilingX == $size[0]-1 && $iFloorY >= 0 && $iCeilingY >= 0 && $iFloorY < $size[1] && $iCeilingY < $size[1]) {//right
				$alpha=round(abs($fDeltaX)*127);
				$colorallocation=imagecolorallocatealpha($imgRot, $iRed, $iGreen, $iBlue, $alpha);
				imagesetpixel($imgRot, $j, $i, $colorallocation);
			} else if ($iFloorX >= 0 && $iCeilingX >= 0 && $iFloorX < $size[0] && $iCeilingX < $size[0] && $iCeilingY == $size[1]-1) {//bottom
				$alpha=round(abs($fDeltaY)*127);
				$colorallocation=imagecolorallocatealpha($imgRot, $iRed, $iGreen, $iBlue, $alpha);
				imagesetpixel($imgRot, $j, $i, $colorallocation);
			}
		}
	}
}

Fabien is French (I think), which is why you get variable names such as distOmbre (shadow distance?), flouOmbre (fuzzy shadow?), largeur (width), hauteur (height). And this one took me a bit more time to translate… LsupH is probably “width greater than height?”. The “L” probably refers to “largeur”, and “H” refers to “hauteur”.

Reading international programming code is fun. *smile*

There’s also an interesting piece of code:

$size=getimagesize("media/diapo-Chinon.jpg");
$LsupH=($size[0]>$size[1])?1:0;
$angleBool=(int)($angleRot/90)%2==0?0:1;
if (($angleBool+$LsupH)%2==0) {
	$largeur=round(abs($size[0]*sin($angleRot%90*pi()/180))+abs($size[1]*sin((90-$angleRot%90)*pi()/180)));
	$hauteur=round(abs($size[0]*cos($angleRot%90*pi()/180))+abs($size[1]*cos((90-$angleRot%90)*pi()/180)));
} else {
	$largeur=round(abs($size[0]*cos($angleRot%90*pi()/180))+abs($size[1]*cos((90-$angleRot%90)*pi()/180)));
	$hauteur=round(abs($size[0]*sin($angleRot%90*pi()/180))+abs($size[1]*sin((90-$angleRot%90)*pi()/180)));
}

So here’s your mission, should you choose to accept it (I recently watched Mission Impossible…). What is Fabien trying to accomplish in that section of code? Hint: it has something to do with getting a “nice” resulting image width and height.

I’ll tell you a more “elegant” alternative to that code section. But it’ll involve some mathematics. And drawings. Prepare for poorly drawn diagrams…

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

Launching SpreadsheetLight

January 10, 2012 By

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: , , ,

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: , , ,
« Older Posts
Newer Posts »