Java lib 操作 excel 插入图片

程序员文章站 2022-07-12 17:27:20

...

https://poi.apache.org/components/spreadsheet/quick-guide.html

1、添加依赖（Maven）

https://mvnrepository.com/artifact/org.apache.poi/poi


		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi</artifactId>
		    <version>4.0.0</version>
		</dependency>

代码实现：



import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.WorkbookUtil;

// https://poi.apache.org/components/spreadsheet/quick-guide.html

public class Excel_01_Basic {
    
    private static String BASE_PATH = "/Users/tiger/Documents/";
    
    private static String FILE_NAME;
    
    private HSSFWorkbook workBook;
   
    public void createWorkBook() throws Exception{
        
        workBook = new HSSFWorkbook();
        
      //============  header ===================================================
        HSSFSheet sheet = workBook.createSheet("2019 Products");
       
        //------------------ 设置列宽
        
        sheet.setColumnWidth(0, 24 * 256); // 24 个字符
        
        
        
        
        //-------------------- 设置行高
        
        Row row = sheet.createRow(2);
        row.setHeightInPoints(30);

        /**
         * HorizontalAlignment.CENTER - one cell
         * HorizontalAlignment.CENTER_SELECTION - multiple cell
         */
        createCell(workBook, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
        createCell(workBook, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM);
        
        /**
         * HorizontalAlignment.FILL - left
         * HorizontalAlignment.GENERAL - left
         */
        createCell(workBook, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER);
        createCell(workBook, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
        
        createCell(workBook, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY);
        createCell(workBook, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
        createCell(workBook, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);
        
        
        
        //------------------------------ Cell2
        
        // merging cells
        int rowIndex = 4;
        Row row2 = sheet.createRow(rowIndex);
        
        Cell cell2 = row2.createCell(1);
        cell2.setCellValue("This is a test of merging");
        
        CellStyle style2 = workBook.createCellStyle();
       
        //------------------------------ Cell Merging
        
        sheet.addMergedRegion(new CellRangeAddress(
                rowIndex, //first row (0-based)
                rowIndex, //last row  (0-based)
                1, //first column (0-based)
                8  //last column  (0-based)
        ));
        
        
        //------------------------------- Heights
        row2.setHeightInPoints(141);
        
        //------------------------------- Fonts
        // Note, the maximum number of unique fonts in a workbook is limited to 32767. 
        // You should re-use fonts in your applications instead of creating a font for each cell. 
        //

        Font font_30 = workBook.createFont();
        font_30.setFontHeightInPoints((short)30);
        font_30.setFontName("Courier New");
        font_30.setItalic(true);
        font_30.setStrikeout(true);
        
        style2.setFont(font_30);
        
        cell2.setCellStyle(style2);
        
        //--------------------------------- Picture
        
        Row row0 = sheet.createRow(0);
        row0.setHeightInPoints(180);
        
        Row row1 = sheet.createRow(1);
        row1.setHeightInPoints(180);
        
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();     // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
        BufferedImage bufferImg = ImageIO.read(new File("/Users/tiger/Downloads/2018-11-28_16-56-28-node.jpg")); //将图片读到BufferedImage 
        ImageIO.write(bufferImg, "png", byteArrayOut); // 将图片写入流中  
        
        
     // 利用HSSFPatriarch将图片写入EXCEL  
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();  
        /**
         * 该构造函数有8个参数
         * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
         * 后四个参数，前连个表示图片左上角所在的columnNum和 rowNum，后天个参数对应的表示图片右下角所在的columnNum和rowNum，
         * excel中的columnNum和rowNum的index都是从0开始的
         *  
         */  
        HSSFClientAnchor anchor0 = new HSSFClientAnchor(0, 0, 950, 245 ,  
                (short) 0, 0, (short)0, 0);
        // 插入图片  
        patriarch.createPicture(anchor0, workBook.addPicture(byteArrayOut  
                .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));  
        
        //图片一导出到单元格B2中  
        HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 950, 245,  
                (short) 0, 1, (short) 0, 1);
        // 插入图片  
        patriarch.createPicture(anchor1, workBook.addPicture(byteArrayOut  
                .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));  
              
        byteArrayOut.close();
        
        //============  footer ===================================================
        
        
        
        
        String safeName = WorkbookUtil.createSafeSheetName("workbook.xls");
        FILE_NAME = BASE_PATH + safeName;
        try  ( OutputStream fileOut = new FileOutputStream(FILE_NAME) ) {
            workBook.write(fileOut);
        }
    }

    /**
     * Creates a cell and aligns it a certain way.
     *
     * @param wb     the workbook
     * @param row    the row to create the cell in
     * @param column the column number to create the cell in
     * @param halign the horizontal alignment for the cell.
     * @param valign the vertical alignment for the cell.
     */
    private void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) {
        Cell cell = row.createCell(column);
        
        cell.setCellValue("Align It");
        
        CellStyle cellStyle = wb.createCellStyle();
        
        // set alignment
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);

        // set borders
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
        
        cellStyle.setBorderTop(BorderStyle.MEDIUM_DASHED);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        
        // set background color // Aqua background
        // cellStyle.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
        // cellStyle.setFillPattern(FillPatternType.BIG_SPOTS);
        
        // set // Orange "foreground", foreground being the fill foreground not the font color.
        cellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        
        //
        cell.setCellStyle(cellStyle);
    }
    
    
    
    public static void main(String[] args) {
        Excel_01_Basic excelWorkBook = new Excel_01_Basic();
        try {
            excelWorkBook.createWorkBook();
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("excel is created successfully!");
        System.out.printf("file path = %s\n", FILE_NAME);
        
        System.out.println("Working Directory = " +
                System.getProperty("user.dir"));
    }

}