Documentation Buy Contact Blog
Cell Color Example

I'm sure you've seen the posts on Reddit, cells of a spreadsheet colored by hand to look like classic video games. And I'm sure the temptation of delicious karma has almost led you to spend a few hours at the office creating your own. But that sounds a lot like work, and that's the last thing I want to do at my job, so lets do it more efficiently using Number Duck!

This is what your final result will look like
This is what your final result will look like, assuming you don't use a crab picture or something.
The cool duck sprite was created by Alex Hanson-White

The idea is pretty simple, we load the image from disk, then loop through all the pixels, setting the appropriate cell background colors as we go. We also need to set the cell widths and heights to a nice square size and we're done.

Select your language below to view the source. You can download Number Duck here, along with the source and binaries for this example.

C++ PHP
C++

To load the image we use the awesome stb_image.c library by Sean Barrett. It is already included in the download.

printf("Cell Color Example\n");
printf("Set cell size and background color!\n");

// Load an image to get the colors we should assign to cells
int nWidth;
int nHeight;
int nColorDepth;
unsigned char* pImageData = stbi_load("Duck.png", &nWidth, &nHeight, &nColorDepth, 4);

// the image could be larger than the maximum worksheet bounds, check for that to be safe
if (nWidth > Worksheet::MAX_COLUMN || nHeight > Worksheet::MAX_ROW)
{
	printf("Sorry, Duck.png is too large. Max dimensions: %dx%d\n", Worksheet::MAX_COLUMN, Worksheet::MAX_ROW);
	stbi_image_free(pImageData); // free image data
	return 1;
}

// Now we construct the workbook, and grab the default worksheet. Ready to start messing with cells
Workbook * pWorkbook = new Workbook("");
Worksheet* pWorksheet = pWorkbook->GetWorksheetByIndex(0);

// Now that we have our image and our worksheet we'll setup a nice square grid
// Here we are setting the cell size to 7px by 7px.
for (int nX = 0; nX < nWidth; nX++)
	pWorksheet->SetColumnWidth(nX, 7);

for (int nY = 0; nY < nHeight; nY++)
	pWorksheet->SetRowHeight(nY, 7);

for (int nY = 0; nY < nHeight; nY++)
{
	for (int nX = 0; nX < nWidth; nX++)
	{
		// Get the data offset into the packed image data and read out the cell color there
		uint32_t nOffset = (nX + nY * nWidth)*4;

		unsigned char nRed = pImageData[nOffset+0];
		unsigned char nGreen = pImageData[nOffset+1];
		unsigned char nBlue = pImageData[nOffset+2];
		unsigned char nAlpha = pImageData[nOffset+3];

		// Skip transparent pixels
		if (nAlpha > 0)
		{
			Color color(nRed, nGreen, nBlue);

			Style* pStyle = NULL;
			// reuse the same style if it has the same background color
			for (uint16_t i = 0; i < pWorkbook->GetNumStyle(); i++)
			{
				Style* pTestStyle = pWorkbook->GetStyleByIndex(i);
				if (pTestStyle->GetBackgroundColor() && pTestStyle->GetBackgroundColor()->Equals(&color))
				{
					pStyle = pTestStyle;
					break;
				}
			}

			// reusable style not found, create a new one
			if (pStyle == NULL)
			{
				pStyle = pWorkbook->CreateStyle();
				pStyle->SetBackgroundColor(color);
			}

			// set the cell style (color)
			Cell* pCell = pWorksheet->GetCell(nX, nY);
			pCell->SetStyle(pStyle);
		}
	}
}
stbi_image_free(pImageData); // free image data

pWorkbook->Save("CellColorExample.xls");