Drawings in MS Excel using Apache POI

Some time ago, an article about an artist appeared in Habré, who paints pictures in MS Excel, using his vector capabilities. But long before that, I ran across stories about phenomenal raster drawings in the same Excel, the idea of ​​which is based on pixel art. Those. someone simply reduces the size of the cells and uses color shading to produce a kind of mosaic. It looks quite impressive, although, of course, it does not hold out to vector drawings in quality.

Seeing such pictures, I, of course, doubted that someone would have enough assiduity to create them in manual mode and decided to look for a way to automate “office creativity”. The task turned out to be simple for implementation in the Java language provided that the Apache POI library , designed to work with proprietary Microsoft Office formats, was used. Details under the cut.

So what we have. I have set myself the task to create an application for converting the most ordinary JPG image (or, in principle, any other common format) into an Excel document. Immediately it is worth mentioning the existing restrictions:
the width of the "picture" should not exceed 255 points (the maximum number of columns per sheet)
the maximum number of styles (in our case, the number of colors) is 4000
Thus, you either have to pre-find and prepare a picture (reduce the size and color depth) or do it programmatically. We will go the second way :)

To begin with we will scribble the Main-class of our program containing the only method:
')

public class Main { public static void main(String[] args) { IMGRead ir = new IMGRead(); Map<String, Object[]> data = ir.read("C:\\picture.jpg"); POIWrite pw = new POIWrite(); pw.write(data); } } 

Of course, poor and hardcode, but for the demonstration will do.

Consider a class for reading pictures from a file. It contains a method for actually reading a picture and returns the result in the form of a map containing objects of the type RGBColor, which store information about the three color components of the point:

 public Map<String, Object[]> read(String fileName) { File file = new File(fileName); BufferedImage source, image;//source and resized images Map<String, Object[]> data = new TreeMap<String, Object[]>(); try { source = ImageIO.read(file);//read picture from file int type = source.getType() == 0? BufferedImage.TYPE_INT_ARGB : source.getType();//get type image = resizeImage(source, type);//resize source = convert8(image); image = source; // :) // Getting pixel color for every pixel for (Integer y = 0; y < image.getHeight(); y++) { Object[] line = new Object[image.getWidth()]; for (int x = 0; x < image.getWidth(); x++) { int clr = image.getRGB(x, y); int red = (clr & 0x00ff0000) >> 16; int green = (clr & 0x0000ff00) >> 8; int blue = clr & 0x000000ff; line[x] = new RGBColor(red, green, blue); } data.put(String.format("%03d", y), line); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return data; } 

We also have a method for resizing the image:

 private static BufferedImage resizeImage(BufferedImage originalImage, int type) { BufferedImage resizedImage = new BufferedImage(IMG_WIDTH, IMG_HEIGHT, type); Graphics2D g = resizedImage.createGraphics(); g.drawImage(originalImage, 0, 0, IMG_WIDTH, IMG_HEIGHT, null); g.dispose(); return resizedImage; } 

And a method for reducing color (I use eight-bit color, for other options, see the source ):

 public static BufferedImage convert8(BufferedImage src) { BufferedImage dest = new BufferedImage(src.getWidth(), src.getHeight(), BufferedImage.TYPE_BYTE_INDEXED); ColorConvertOp cco = new ColorConvertOp(src.getColorModel() .getColorSpace(), dest.getColorModel().getColorSpace(), null); cco.filter(src, dest); return dest; } 

We proceed to the class that implements the recording of the “picture” in the Excel document. Here we have 2 methods, in the first of them we write to the file:

 public void write(Map<String, Object[]> data) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Picture"); Map<String, HSSFCellStyle> colorToStyle = new HashMap<String, HSSFCellStyle>(); HSSFCellStyle style; Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); row.setHeight((short) 50); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { sheet.setColumnWidth(cellnum, 100); Cell cell = row.createCell(cellnum++); RGBColor rgb = (RGBColor) obj; try { style = colorToStyle.get(rgb.toString()); cell.setCellStyle(style); } catch (Exception e) { style = workbook.createCellStyle(); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFillForegroundColor(setColor(workbook, rgb.getR(), rgb.getG(), rgb.getB()).getIndex()); colorToStyle.put(rgb.toString(), style); cell.setCellStyle(style); } } } try { FileOutputStream out = new FileOutputStream(new File("C:\\picture.xls")); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } 

The colorToStyle map is used to store cell styles. Creating a new style, we associate it with a specific color and each time we fill a new cell, we check whether there is a ready-made style for the desired color, or whether we need to create it.

Well, and finally, a method for converting RGB colors to the HSSFColor format used in Apache POI. Notice that the findSimilarColor () method is used, which tries to automatically match a similar color in the palette.

 public HSSFColor setColor(HSSFWorkbook workbook, byte r, byte g, byte b) { HSSFPalette palette = workbook.getCustomPalette(); HSSFColor hssfColor = null; try { hssfColor = palette.findSimilarColor(r, g, b); if (hssfColor == null) { System.err.println("null " + r + " " + g + " " + b); palette.setColorAtIndex(HSSFColor.RED.index, r, g, b); hssfColor = palette.getColor(HSSFColor.RED.index); } } catch (Exception e) { e.printStackTrace(); } return hssfColor; } 

The results of "creativity":

• Firefox icon
• The picture is bigger (Google Docs displays it as 2 parts, but when downloading it turns out a normal picture)
• Project sources on GitHub

Of course, such mosaics are far from a masterpiece, but with the right selection of colors in the image and its size, you can get pretty nice “drawings”. Does Apache POI (or any other lib) know how to work with desktop drawings in office documents? I don’t know :(