java - 生成带有图像的 Excel 工作表

Question

我正在尝试使用 Netbeans 使用 Java 生成 excel 表，我希望 excel 表包含可以使用 JFileChooser 添加的图像，方法是将其解析为数组中的字节并将其放入 A 列，而 B 列是该图像的描述，但是我制作的代码是在 B 列中仅放置一张图像，而我想在不同的行中列出多个图像并在 B 列中对其进行描述。

主类：

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import org.apache.commons.compress.utils.IOUtils;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;



public class ExcelWriter {

    static String imagePath;
    static String imageDesc;

    static int numOfData;

    private static String[] columns = { "DropBox Link", "Description"};
    private static List<Data> datas = new ArrayList<Data>();


        static JFileChooser chooser = new JFileChooser();

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

        //taking user input using Scanner Library
        Scanner in = new Scanner(System.in);



        System.out.println("How many data you need?");
        numOfData = in.nextInt();
//      numOfData = numOfData+1;

        for (int i = 0; i < numOfData; i++) {

                    System.out.println("Choose the file");

                    File file = chooser.getSelectedFile();
                    chooser.setFileSelectionMode(JFileChooser.FILES_AND_DIRECTORIES);
                    int returnVal = chooser.showOpenDialog(chooser);
                    if(returnVal == JFileChooser.APPROVE_OPTION) {
                    System.out.println("You chose to open this directory: " +
                    chooser.getSelectedFile().getAbsolutePath());
                    }

                    imagePath = chooser.getSelectedFile().getAbsolutePath();

            System.out.println("Description for it?");

            imageDesc = JOptionPane.showInputDialog("Input");

                    datas.add(new Data(imagePath, imageDesc));

        }

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("dropboxLinks");
                //FileInputStream obtains input bytes from the image file
                InputStream inputStream = new FileInputStream(chooser.getSelectedFile().getAbsolutePath());
                //Get the contents of an InputStream as a byte[].
                byte[] bytes = IOUtils.toByteArray(inputStream);
                //Adds a picture to the workbook
                int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                //close the input stream
                inputStream.close();
                //Returns an object that handles instantiating concrete classes
                CreationHelper helper = workbook.getCreationHelper();
                //Creates the top-level drawing patriarch.
                Drawing drawing = sheet.createDrawingPatriarch();
                //Create an anchor that is attached to the worksheet
                ClientAnchor anchor = helper.createClientAnchor();
                //create an anchor with upper left cell _and_ bottom right cell
                anchor.setCol1(1); //Column B
                anchor.setRow1(2); //Row 3
                anchor.setCol2(2); //Column C
                anchor.setRow2(3); //Row 4
                //Creates a picture
                Picture pict = drawing.createPicture(anchor, pictureIdx);

                //Reset the image to the original size
                //pict.resize(); //don't do that. Let the anchor resize the image!

                //Create the Cell B3
                //Cell cell = sheet.createRow(2).createCell(1);

        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setFontHeightInPoints((short) 14);
        headerFont.setColor(IndexedColors.RED.getIndex());

        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setFont(headerFont);

        // Create a Row
        Row headerRow = sheet.createRow(0);

        for (int i = 0; i < columns.length; i++) {
                    Cell cell = headerRow.createCell(i);
                    cell.setCellValue(columns[i]);
                    cell.setCellStyle(headerCellStyle);
        }

        // Create Other rows and cells with datas data
        int rowNum = 1;

        for (Data datas : datas) {
                    Row row = sheet.createRow(rowNum++);
                    row.createCell(0).setCellValue(datas.imagePath);
                    row.createCell(1).setCellValue(datas.imageDesc);
        }

        // Resize all columns to fit the content size
        for (int i = 0; i < columns.length; i++) {
                    sheet.autoSizeColumn(i);
        }

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("dropboxLinks.xlsx");
        workbook.write(fileOut);
        fileOut.close();
              }


    }

数据类：

    public String imagePath;
      public String imageDesc;

      public Data(String imagePath, String imageDesc) {
        this.imagePath = imagePath;
        this.imageDesc = imageDesc;
      }

}

我想要第一行的标题，代码从第二行开始，使用 JFileChooser 在 A 列中放置图像，在 BI 列中希望它是使用 JOptionPane.showInputDialog() 的 A 列中该图像的描述。

标签： javaapache-poi

Answer 1

你从不为 迭代images，你只为imagePathand迭代imageDescription。

在您的代码中：

InputStream inputStream = new FileInputStream(chooser.getSelectedFile().getAbsolutePath())

这是获取最后一个输入，因此您的 excel 只有最后一张图片。

在此处更正以解决您的问题：

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("dropboxLinks");

        int startRow = 1;
        for(int intR=0; intR<datas.size(); intR++) {
                //FileInputStream obtains input bytes from the image file
                InputStream inputStream = new FileInputStream(datas.get(intR).imagePath);
                //Get the contents of an InputStream as a byte[].
                byte[] bytes = org.apache.poi.util.IOUtils.toByteArray(inputStream);
                //Adds a picture to the workbook
                int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                //close the input stream
                inputStream.close();
                //Returns an object that handles instantiating concrete classes
                CreationHelper helper = workbook.getCreationHelper();
                //Creates the top-level drawing patriarch.
                Drawing drawing = sheet.createDrawingPatriarch();
                //Create an anchor that is attached to the worksheet
                ClientAnchor anchor = helper.createClientAnchor();
                //create an anchor with upper left cell _and_ bottom right cell
                anchor.setCol1(1); //Column B
                anchor.setRow1(startRow);
                anchor.setCol2(2); //Column C
                anchor.setRow2(3); //Row 4
                //Creates a picture
                Picture pict = drawing.createPicture(anchor, pictureIdx);
                startRow++;
        }
                //Reset the image to the original size
                //pict.resize(); //don't do that. Let the anchor resize the image!

                //Create the Cell B3
                //Cell cell = sheet.createRow(2).createCell(1);