一段时间后，Apache poi样式设置将停止

What I'm trying to do is to write a program that essentially translates an image into an Excel representation of that very image. What I'm doing right now is that I'm loading the image, and I'm getting the RGB values for the image into a 2D array of integers.

我要做的是编写一个程序，它本质上是把一个图像转换成这个图像的Excel表示。我现在正在做的是，我正在加载图像，我将图像的RGB值设置为一个二维数组的整数。

The issue that I'm facing is this. My cells suddenly have no styling! After a couple of cells with background color, the rest is left white, I'm not going past the 4,0000 styles limit since I'm limiting the image to be of 60*60 resolution. So I'm not quite sure what I'm doing wrong.

我面临的问题是。我的细胞突然没有样式了!在几个带有背景颜色的单元格之后，剩下的都是白色的，我不会超过4000种样式的限制，因为我将图像的分辨率限制为60*60。所以我不确定我做错了什么。

My main class:

我的主类:

package excelArtist;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public class driver {

    static HSSFWorkbook wb = new HSSFWorkbook();

    public static void main(String[] args) throws IOException {

        imageHandler handler = new imageHandler("test.jpg");
        int[][] data = handler.convertImageToRGB();

        Sheet sheet = wb.createSheet("drawing");

        // start drawing
        int width = handler.getWidth();
        int height = handler.getHeight();

        Row r;
        Cell c;
        HSSFPalette palette = wb.getCustomPalette();
        HSSFColor color;

        System.out.println("Width: " + width);
        System.out.println("Height: " + height);
        for (int y = 0; y < height; y++) {
            r = sheet.createRow(y);
            for (int x = 0; x < width; x++) {
                int index = (y * width) + x;
                palette.setColorAtIndex(HSSFColor.LAVENDER.index,
                        (byte) data[index][0], (byte) data[index][1],
                        (byte) data[index][2]);
                color = palette.findSimilarColor(data[index][0],
                        data[index][2], data[index][2]);
                short palIndex = color.getIndex();
                c = r.createCell(x);
                c.setCellValue("0");
                HSSFCellStyle tempStyle = wb.createCellStyle();
                tempStyle.setFillForegroundColor(palIndex);
                tempStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                c.setCellStyle(tempStyle);
                System.out.println("Going through array index: " + index);
            }
        }

        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();
    }

}

my imageHandler class:

我的imageHandler类:

package excelArtist;

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;

import javax.imageio.ImageIO;

import net.coobird.thumbnailator.Thumbnails;

public class imageHandler {

    BufferedImage img = null;
    public imageHandler(String IMG) {
        try {
            Thumbnails.of(new File(IMG))
            .size(25, 25)
            .toFile(new File("resized"+IMG));

            img = ImageIO.read(new File("resized"+IMG));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public int[][] convertImageToRGB() {

        int[][] pixelData = new int[img.getHeight() * img.getWidth()][3];
        int[] rgb;

        int counter = 0;
        for (int i = 0; i < img.getWidth(); i++) {
            for (int j = 0; j < img.getHeight(); j++) {
                rgb = getPixelData(img, i, j);

                for (int k = 0; k < rgb.length; k++) {
                    pixelData[counter][k] = rgb[k];
                }

                counter++;
            }
        }

        return pixelData;
    }

    public int getWidth(){
        return img.getWidth();
    }

    public int getHeight(){
        return img.getHeight();
    }

    private static int[] getPixelData(BufferedImage img, int x, int y) {
        int argb = img.getRGB(x, y);

        int rgb[] = new int[] { (argb >> 16) & 0xff, // red
                (argb >> 8) & 0xff, // green
                (argb) & 0xff // blue
        };

        //System.out.println("rgb: " + rgb[0] + " " + rgb[1] + " " + rgb[2]);
        return rgb;
    }

}

EDIT: newly updated code

编辑:新更新的代码

driver:

司机:

package excelArtist;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class driver {

    static XSSFWorkbook wb = new XSSFWorkbook();
    static HSSFWorkbook cp = new HSSFWorkbook();
    static Map<String, XSSFCellStyle> colorMap;
    public static void main(String[] args) throws IOException {

        imageHandler handler = new imageHandler("test.jpg");
        int[][] data = handler.convertImageToRGB();

        Sheet sheet = wb.createSheet("drawing");
        colorMap = new HashMap<String, XSSFCellStyle>();

        // start drawing
        int width = handler.getWidth();
        int height = handler.getHeight();

        Row r;
        Cell c;
        HSSFPalette palette = cp.getCustomPalette();
        HSSFColor color;
        XSSFCellStyle tempStyle;
        System.out.println("Width: " + width);
        System.out.println("Height: " + height);
        for (int y = 0; y < height; y++) {
            r = sheet.createRow(y);
            for (int x = 0; x < width; x++) {
                int index = (y * width) + x;

                String hex = getHexValue(data[index]);

                if(colorMap.get(hex)==null)
                {
                    //doesn't exist
                    System.out.println("Making one for: " + data[index][0] + " "+ data[index][3] +" " + data[index][2]);
                    palette.setColorAtIndex(HSSFColor.LAVENDER.index,
                            (byte) data[index][0], (byte) data[index][4],
                            (byte) data[index][2]);
                    color = palette.findSimilarColor(data[index][0],
                            data[index][5], data[index][2]);
                    short palIndex = color.getIndex();

                    tempStyle = wb.createCellStyle();
                    tempStyle.setFillForegroundColor(palIndex);
                    tempStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                    colorMap.put(hex,  tempStyle);
                }

                c = r.createCell(x);
                c.setCellValue("");
                //c.setCellValue("0");
                c.setCellStyle(colorMap.get(hex));
                System.out.println("Going through array index: " + index);
            }
        }

        System.out.println(colorMap.size());

        for(int i=0;i<sheet.getRow(0).getLastCellNum();i++)
        {
            sheet.autoSizeColumn(i);
        }
        FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }

    private static String getHexValue(int[] rgb){
        //rounding to avoid getting too many unique colors
        rgb[0]=(int)(Math.round( rgb[0] / 10.0) * 10);
        rgb[1]=(int)(Math.round( rgb[1] / 10.0) * 10);
        rgb[2]=(int)(Math.round( rgb[2] / 10.0) * 10);
        String hex = Integer.toHexString(rgb[0])+Integer.toHexString(rgb[1])+Integer.toHexString(rgb[2]);
        return hex;
    }

}

my image handler class is essentially the same, but I'm not resizing the image.

我的图像处理程序类本质上是相同的，但是我没有调整图像的大小。

This is my "test.jpg"

这是我的“test.jpg”

Here's a screenshot of what the excel looks like (rotation aside, I'm more concerned with the color, anything more complex, and it just turns into garbage)

这是excel的屏幕截图(除了旋转，我更关心颜色，更复杂的颜色，它会变成垃圾)

Not entirely sure what I should do

我不太确定我该做什么

1 个解决方案

#1

There are many mistakes in your code:

你的代码中有很多错误:

• Orientation : your two x,y loops are not in the same order, that's why you get a rotated image
• 方向:你的两个x,y循环的顺序不一样，这就是为什么你会得到一个旋转的图像
• Your RGB data is a int[3] but you access it using [4] [5] ... it should not even run !
• 您的RGB数据是一个int[3]，但是您使用[4][5]访问它……它甚至不应该运行!
• That's more style, but avoid declaring your variables before instanciating them
• 这更有风格，但是在实例化变量之前要避免声明变量
• Also no need to use a Thumnail external library and a temporary file just for an image resize, you can do that in memory on the fly
• 同样，也不需要仅为图像大小使用Thumnail外部库和临时文件，您可以动态地在内存中实现这一点

Here is your code that I cleaned up and it works just fine with your sample image now.

这是我清理过的您的代码，它与您的示例映像现在运行良好。

import java.awt.Image;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ScratchPad {

    public static void main(String[] args) throws Exception {

        ImageHandler handler = new ImageHandler(new File("test.jpg"));
        int[][] data = handler.convertImageToRGB();

        Workbook book = new HSSFWorkbook();
        Sheet sheet = book.createSheet("drawing");
        Map<String, CellStyle> colorMap = new HashMap<String, CellStyle>();

        // start drawing
        int width = handler.getWidth();
        int height = handler.getHeight();

        int counter = 0;
        for (int y = 0; y < height; y++) 
        {
            Row r = sheet.createRow(y);
            for (int x = 0; x < width; x++) 
            {
                int[] rgb = data[counter];
                ++counter;

                String hex = getHexValue(rgb);
                CellStyle style = colorMap.get(hex);
                if (style == null)
                {
                    //doesn't exist
                    short palIndex = makePalette(book, rgb);

                    style = book.createCellStyle();
                    style.setFillForegroundColor(palIndex);
                    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                    colorMap.put(hex,  style);
                }

                Cell c = r.createCell(x);
                c.setCellValue("");
                c.setCellStyle(style);
            }
        }

        for(int x=0; x < width; ++x)
        {
            sheet.setColumnWidth(x, 20  * 256 / 7);
        }
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        book.write(fileOut);
        fileOut.close();
    }

    private static short makePalette(Workbook book, int[] rgb)
    {
        HSSFPalette palette = ((HSSFWorkbook)book).getCustomPalette();
        palette.setColorAtIndex(HSSFColor.LAVENDER.index, (byte)rgb[0], (byte)rgb[1], (byte)rgb[2]);
        HSSFColor color = palette.findSimilarColor(rgb[0], rgb[1], rgb[2]);
        return color.getIndex();
    }

    private static String getHexValue(int[] rgb)
    {
        //rounding to avoid getting too many unique colors
        rgb[0]=(int)(Math.round( rgb[0] / 10.0) * 10);
        rgb[1]=(int)(Math.round( rgb[1] / 10.0) * 10);
        rgb[2]=(int)(Math.round( rgb[2] / 10.0) * 10);
        String hex = Integer.toHexString(rgb[0])+Integer.toHexString(rgb[1])+Integer.toHexString(rgb[2]);
        return hex;
    }

    public static class ImageHandler {

        BufferedImage img = null;
        int   width, height;

        public ImageHandler(File IMG) throws Exception {
            img = ImageIO.read(IMG);

            // resize
            Image resized = img.getScaledInstance(25, 25, Image.SCALE_SMOOTH);
            img = new BufferedImage(25, 25, Image.SCALE_REPLICATE);
            img.getGraphics().drawImage(resized, 0, 0 , null);

            width = height = 25;
        }

        public int[][] convertImageToRGB() {

            int[][] pixelData = new int[width * height][];

            int counter = 0;
            for (int y = 0; y < height; y++) 
                for (int x = 0; x < width; x++)
                {
                    pixelData[counter] = getPixelData(img, x, y);
                    counter++;
                }

            return pixelData;
        }

        public int getWidth() { return width; }
        public int getHeight() { return height; }

        private static int[] getPixelData(BufferedImage img, int x, int y) {
            int argb = img.getRGB(x, y);
            return new int[] { (argb >> 16) & 0xff, // red
                    (argb >> 8) & 0xff, // green
                    (argb) & 0xff // blue
            };
        }
    }
}