Thinking Outside the Spreadsheet Box: Unexpected Uses of OfficeWriter

If you leave me alone for too long, I do foolish things like make a mini-paint app that writes the image to 1×1 cells in Excel. And so can you! But before I show you how to make a mini-paint app (the easiest thing you will ever do in your life), let me explain to you what part of me said, “Yeah, Champ, this is a great idea! Who wouldn’t want to make a weird spreadsheet that contains an image made with the background color on cells?” It all began when I was thinking about this awesome idea by my art crush, Evan Roth (*swoon*). He said, “Hey world! I want work to be art,” and decided to make a double-mouse that outputs to an art program on one screen and his regular work on the other. So I figured, why not put some art in Excel too?

Then I got more and more excited as I realized there are actually a ton of cool things you can do…

  • Use the formulas in Excel as transformations on your picture.
  • Randomize cell size to introduce weirdness.
  • Flip the project to input an Excel template and output an image, using Excel calculations and System.Draw to make a simple ray tracer.
  • Do some stuff with conditional formats, or all those light transform equations you learned in high school for no reason…until now!
  • Make a mock tool that lets users draw a spreadsheet design and output it to a real spreadsheet! (I thought a lot about this, and it’s not necessarily simple, but it is a really cool idea. If anyone puts that together, please let me know. I mean you, Evan Roth….Call me?)

The Mini-Paint

For the sake of simplicity, I’m showing you the randomized cell idea. I’ve made it an optional setting in this example, but, you know, make it your own and all. Oh, also, get at me with your art! For this app you’ll need WinForms and OfficeWriter.

appwindow

I started by making a pretty basic app that has some simple actions. The “canvas” is a PictureBox that I save and resave some drawing objects to on each mouse event. The color selector is just 8 flat buttons set to be the color they represent. The shapes are again flat buttons with Wingdings images because I was too lazy to find a relevant .res file with some neat picture icons. Can you guess what the size toggles are? Flat buttons. Generic plus/minus from your plain boring keyboard. Okay. Basic functions.

  • MouseDown sets a pointer location.
  • MouseUp nullifies the pointer location.
  • MouseMoved is a basic drawing function. (You can find a ton of examples, but I like this one from good ol’ SO.)
  • Color buttons set a global variable, _selectedColor, to the color of the button.
  • Shape button clicks set a global variable called _selectedShape. I set it to an enum of possible shape options and I use a switch statement in the draw method. Instead of calling DrawLine or DrawRectangle, I call DrawShape(x,y) with x and y being current mouse x and y.

DrawShape is roughly this:
Graphics graph = Graphics.FromImage(pbCanvas.Image);
Rectangle rect = new Rectangle();
switch (_selectedShape)
{
case PossibleShapes.Circle:
rect.Width = rect.Height = _size;
graph.DrawEllipse(pen, rect);
break;
case PossibleShapes.Rect:
rect.Width = _size + (_size/2);
rect.Height = _size;
graph.DrawRectangle(pen, rect);
break;
case PossibleShapes.Square:
rect.Width = _size;
rect.Height = _size;
graph.DrawRectangle(pen, rect);
break;
case PossibleShapes.Line:
pen.Width = _size;
pen.EndCap = System.Drawing.Drawing2D.LineCap.Triangle;
pen.StartCap = System.Drawing.Drawing2D.LineCap.Triangle;
graph.DrawLine(pen, rect.Location, new Point(x+3, y));
break;
case PossibleShapes.Tri:
Point[] triangle = new Point[3];
triangle[0] = new Point(x,y);
int width = x + _size;
triangle[1] = new Point(width, y);
int angle = (x + width)/2;
int angleHeight = y - _size;
triangle[2] = new Point(angle, angleHeight);
graph.DrawPolygon(pen, triangle);
}

The most basic shape options.

You may have noticed “Toggle Fill” on the app. I did that for ease. I didn’t want to have a filled triangle, open triangle, filled circle, open circle…etc in the shape buttons box. It would look cluttery and make my enum GIGANTIC.  My logic was that each shape could be set to a PossibleShape value, and then based on a separate Boolean this could be called:

if (fill)

graph.FillRectangle(pen, rect);

Easy, and it keeps the switch manageable. Since everything except the triangle is based on the same method-scoped Rectangle, I can just call that statement almost shape-agnostically. The last step is to see if Triangle is the selected shape, in which case use graph.FillPolygon. Depending on your aliasing and where you declare your fill, you may want to call graph.FillEllipse if you’re filling a circle. It still takes rect as an argument though.

Of course there’s also a reset button, which sets the “canvas” back to white and resets all the globals back to default.

Making a paint app with only features you want is a breeze.

Now. On to the Excel part. The code for this is actually pretty simple. The Randomize checkbox and Generate buttons both use ExcelWriter objects.

The Generate and Randomize methods:
private void btnGen_Click(object sender, EventArgs e)
{
if (pbCanvas.Image != null)
{
//Disable the buttons, since this part can take a while
ToggleBtns(false);
//Get the image from the canvas
Bitmap inputImg = new Bitmap(pbCanvas.Image);
//Get your ExcelApp objects together
ExcelApplication _xla = new ExcelApplication();
Workbook wb = _xla.Create(ExcelApplication.FileFormat.Xlsx);
Palette pal = wb.Palette;
Style cellStyle = wb.CreateStyle();
Worksheet ws = wb.Worksheets[0];
//Make the pixels into cells!
for (int x = 0; x < inputImg.Width; x++)
{
for (int y = 0; y < inputImg.Height; y++)
{
System.Drawing.Color clr = inputImg.GetPixel(x, y);
//don't bother setting white - it's the default background color.
if (clr != System.Drawing.Color.White)
{
ws.Cells[y, x].Style.BackgroundColor = pal.GetClosestColor(clr);
}
//Create an area and set the cells to 1x1
Area area = ws.CreateArea(0, 0, inputImg.Height, inputImg.Width);
area.AllColumnWidths = 1;
area.AllRowsHeight = 1;
//If you're randomizing, call the random method
if (_rand)
{
Randomize(area, inputImg.Width, inputImg.Height);
}

//Save wherever. This is a nice place to add a save
//prompt, but I don’t care enough
string desktop = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
_xla.Save(wb, desktop + “\\NewArt.xlsx”);

//All done!
MessageBox.Show(“Finished!”);

//Here are your buttons
ToggleBtns(true);
}
else
{
MessageBox.Show(“Dude. Draw something”);

}
}

private void Randomize(Area area, int x, int y)
{

Random rand = new Random();

//I don't want to change any more that 50 rows or columns
//The upper limit is up to you
//I also don't want rand to return 0 and look like it's broken
//The lower limit is up to you too!
int max = rand.Next(5, 50);
for (int i = 0; i < max; i++)
{
 //50/50 chance of changing row or column (in theory)
 //The math doesn't actually work out that way, I don't think
 //Something about the impossibility of true randomness?
 //Anyway, this is probably the closest you can come.
 if (rand.NextDouble() < 0.5)
 {
area.SetColumnWidth(rand.Next(x), rand.Next(2, 20));
}
 else
 {
area.SetRowHeight(rand.Next(y), rand.Next(2, 20));
}
}
}

Don’t use magic numbers in your code. I did it because this isn’t going into production. Make some nice “UpperBound” and “LowerBound” constants. Yep. Like that!

I used GetPixel, even though it’s slower. The reason I used GetPixel even though it’s slower is because I don’t understand enough about LockBits and stride to confidently explain the faster method to you. You can read a little here, but it’s beyond the scope of this anyway.

I also recommend disabling buttons or maybe having a timer icon, since you’re setting a style on 90,000 cells for an image that’s 300×300 pixels. I also recommend keeping the canvas small, because you’ll get an out of memory exception if you’re not careful.

And this is the button toggle. You might want to add some error handling, but just don’t like… explicitly set each value for each button… this will work, guys. Keep it simple. Keep it safe. (Wait, that’s “keep it secret, keep it safe,” right? I guess technically it works both ways.)
private void ToggleBtns(bool enable)
{
var controls = this.Controls.OfType<Button>();
foreach (Button ctrl in controls)
{
ctrl.Enabled = enable;
}
}
Okay. Generate. Here are some of my favorite transforms…

I made this eye:

OriginalEye

And the transform made this weirder eye:

eyetransform

Or this slinky ocean:

slinkyocean

That became this neat 1960s dress fabric:

60sfabric

Or even this cube-y thing:

stupidsquares

That became this desktop wallpaper:

background

You basically can’t live without this if you design fabric or desktop backgrounds, especially if it’s 1965. It’ll be so cool. Everyone will ask you, “What’s a desktop background?” and then you’ll answer back, “Look at this fabric I made!”

Anyway, go make your own art!

(That means you, Evan Roth.)

(*swoon*)

Related posts:

Leave a Reply

Your email address will not be published. Required fields are marked *