3
\$\begingroup\$

I'm having some performance issues in this code and need some suggestions on how to improve it. I'm not interested in "general code style" improvements unless they have performance implications.

The purpose is to create an image in Excel using the colors from an input image. What is my performance issue is that it takes time setting the styles (about 45% of time each when profiling) and it is increasing linearly by each "pixel" created.

Currently it becomes annoyingly slow with pictures larger than about 50 x 50.

style.setFillForegroundColor(new XSSFColor(color));
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

I was thinking of doing it in parallel, but I don't think POI is thread safe when trying. I also tried to do with HSSF (but got reduced number of colors and styles allowed and maximum 255 pixels wide, however it was fast).

My next thinking was to reduce number of colors used in the image and cache styles for each color so I can reuse the style for several pixels. I have not tried it yet however.

Do you have any other suggestions?

package test.test;

import java.awt.Color;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.function.IntConsumer;
import java.util.stream.IntStream;

import javax.imageio.ImageIO;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelDrawXSSF {

    public static void main(String[] args) {
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            XSSFSheet sheet = workbook.createSheet("Pic");
            // Can take any picture jpg/png if you want to try
            BufferedImage image = ImageIO.read(new File("DisplayImage.jpg"));

            int height = image.getHeight();
            int width = image.getWidth();

            for (int y = 0; y < height; y++) {
                XSSFRow row = sheet.createRow(y);
                for (int x = 0; x < width; x++) {
                    int rgb = image.getRGB(x, y);
                    Color color = new Color(rgb, true);
                    XSSFCell cell = row.createCell(x);
                    setColor(workbook, cell, color);
                    System.out.println(y + "," + x);
                }
            }

            setSmallerColumnWidth(sheet, width);

            try (FileOutputStream outputStream = new FileOutputStream("Pic.xlsx")) {
                workbook.write(outputStream);
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    private static void setSmallerColumnWidth(XSSFSheet sheet, int width) {
        IntConsumer setToDefaultWidth = col -> sheet.setColumnWidth(col, 4 * 256);
        IntStream.range(0, width).forEach(setToDefaultWidth);
    }

    private static void setColor(XSSFWorkbook workbook, Cell cell, Color color) {
        long start = System.nanoTime();
        XSSFCellStyle style = workbook.createCellStyle();

        // Here is the performance issue.
        style.setFillForegroundColor(new XSSFColor(color));
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        cell.setCellStyle(style);
        System.out.println((System.nanoTime() - start) / (1000 * 1000));
    }

}
\$\endgroup\$
0
\$\begingroup\$

AFAIR, styles are "heavy cost" objects in the workbook. Thus, create each XSSFCellStyle only once and reuse the XSSFCellStyle object for the same color.

\$\endgroup\$
  • \$\begingroup\$ As mentioned, i have that idea as well, but i guess i need to reduce the color range as well, since they most probable are "unique" since I have 16 million colors to choose from :) I guess i could do some similarity matching. (Might even look cool) \$\endgroup\$ – Viktor Mellgren Aug 22 '17 at 12:35
  • \$\begingroup\$ If you seriously expect your users to create an excel sheet with 16 million cells and 16 million different colors, go ahead and reduce the color range. For every REALISTIC scenario I have ever encountered, caching and reusing the XSSFCellStyles is sufficient. \$\endgroup\$ – mtj Aug 23 '17 at 6:22
  • \$\begingroup\$ Reducing to 8-bit color depth and caching gave me a runtime of about 20s for a 1280 x 850 picture, i guess that good enough \$\endgroup\$ – Viktor Mellgren Aug 23 '17 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.