poi 导出图片及数据到excel

Maven

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
 </dependency>

java代码

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelUtil {

    public void exportXSSFExcel() throws IOException {
        File file = new File("d:/test/rows.xlsx");
        if (!file.exists()) {
            file.createNewFile();
        }
        FileOutputStream fileOutput = new FileOutputStream(file);
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = sheet.createRow(10);
        File photoFile = new File("C:\\Users\\guang.wang\\Desktop\\PlantMI\\1.jpg");
        if (photoFile.exists()) {
            BufferedImage bufferedImage = ImageIO.read(photoFile);
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            ImageIO.write(bufferedImage, "jpg", byteArrayOut);
            byte[] data = byteArrayOut.toByteArray();
            XSSFDrawing drawingPatriarch = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = new XSSFClientAnchor(480, 30, 700, 250, (short) 0, 1, (short) 10, 20);
            drawingPatriarch.createPicture(anchor, workbook.addPicture(data, XSSFWorkbook.PICTURE_TYPE_JPEG));
            sheet.setColumnWidth((short) 500, (short) 500);
            row.setHeight((short) 500);
        }
        XSSFCell cell;
        for (int i = 0; i <10 ; i++) {
            row = sheet.createRow(i+20);
            for (int j = 0; j < 10; j++) {
               cell= row.createCell(j);
               cell.setCellValue("行"+i+",列"+j);
            }
        }
        for (int i = 1; i < 10; i++) {
                sheet.autoSizeColumn(i);
        }
        CellRangeAddress region = new CellRangeAddress(21, 21, 0, 3);
        sheet.addMergedRegion(region);
        row=sheet.getRow(21);

        row.getCell(0).setCellValue("123123211321331");

        // 处理中文不能自动调整列宽的问题
        this.setXSSFSizeColumn(sheet, 10);
        fileOutput.flush();
        workbook.write(fileOutput);
        fileOutput.close();
    }
    // 自适应宽度(中文支持)
    private void setXSSFSizeColumn(XSSFSheet sheet, int size) {
        for (int columnNum = 0; columnNum < size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                XSSFRow currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(columnNum) != null) {
                    XSSFCell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            columnWidth = columnWidth * 256;
            sheet.setColumnWidth(columnNum, columnWidth >= 65280 ? 6000 : columnWidth);
        }
    }

    public void exportHSSFExcel() throws IOException {
       {
        File file = new File("d:/test/rows.xls");
        if (!file.exists()) {
            file.createNewFile();
        }
        FileOutputStream fileOutput = new FileOutputStream(file);
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        InputStream inputStream = null;
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet();
            HSSFRow row = sheet.createRow(10);
            //图片

            URL imageUrl = new URL("http://xxxxxx/123.jpg");
            HttpURLConnection conn = (HttpURLConnection) imageUrl.openConnection();
            conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
            inputStream = conn.getInputStream();
            byte[] buffer = new byte[1024];
            int len = 0;
            while ((len = inputStream.read(buffer)) != -1) {
                outStream.write(buffer, 0, len);
            }
            byte[] data = outStream.toByteArray();

            HSSFPatriarch drawingPatriarch = sheet.createDrawingPatriarch();
            HSSFClientAnchor anchor = new HSSFClientAnchor(480, 30, 700, 250, (short) 0, 1, (short) 10, 19);
            drawingPatriarch.createPicture(anchor, workbook.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
            sheet.setColumnWidth((short) 500, (short) 500);
            row.setHeight((short) 500);


            HSSFCell cell;
            for (int i = 0; i < 10; i++) {
                row = sheet.createRow(i + 20);
                for (int j = 0; j < 10; j++) {
                    cell = row.createCell(j);
                    cell.setCellValue("行" + i + ",列" + j);
                }
            }
            for (int i = 1; i < 10; i++) {
                sheet.autoSizeColumn(i);
            }
            CellRangeAddress region = new CellRangeAddress(21, 21, 0, 3);
            sheet.addMergedRegion(region);
            row = sheet.getRow(21);

            row.getCell(0).setCellValue("123123211321331");

            // 处理中文不能自动调整列宽的问题
            this.setHSSFSizeColumn(sheet, 10);
            fileOutput.flush();
            workbook.write(fileOutput);
            fileOutput.close();
        } catch (Exception e) {
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
                outStream.close();

            } catch (Exception e) {
            }
        }

    }
    }
    // 自适应宽度(中文支持)
    private void setHSSFSizeColumn(HSSFSheet sheet, int size) {
        for (int columnNum = 0; columnNum < size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(columnNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            columnWidth = columnWidth * 256;
            sheet.setColumnWidth(columnNum, columnWidth >= 65280 ? 6000 : columnWidth);
        }
    }

    public static void main(String[] args) throws IOException {
        ExcelUtil excelUtil=new ExcelUtil();
        excelUtil.exportXSSFExcel();
//        excelUtil.exportHSSFExcel();
    }

}