本文假定 Excel 文件中保存的是員工數據，并且數據中帶有員工的頭像。代碼支持的圖片格式有png、bmp、jpg、gif。但是這里需要注意，有些網站上下載的圖片雖然后綴名是 jpg，但是文件二進制內容的格式是 WebP 的。Java8 目前官方api不支持 WebP ，本文不涉及webp相關話題，本文代碼也不支持 WebP 格式。
另外我還遇到個坑，POI 5.2.5 在處理部分 jpg 格式圖片的時候，無法把圖片輸出到 Excel 文件。為了解決這個問題，我在代碼中把所有圖片強行轉成 png，保存為硬盤上的臨時文件，再重新輸出到 Excel 文件中。這個問題我沒有在 POI 4.1.2 版本遇到過。
POI 的接口是線程不安全的，多個線程同時向一個文件輸出會造成錯誤。如果讀者想要在多線程環境（比如網站后端）使用下面的代碼，要么使用鎖，要么確保各個線程輸出不同的文件。
本文的代碼計算了圖片的縮放比例，并且使用 picture.resize(scaleX, scaleY); 方法來設置圖片縮放比例。這也與 POI 4.1.2 版本不同。POI 4.1.2 版本使用 picture.resize(1, 1); 會自動縮放圖片調整成合適大小。
員工的 DTO 類：
/*** 員工DTO*/
public class EmployeeDTO {// 工號private String no;// 姓名private String name;// 性別private String gender;// 頭像private String portrait;@Overridepublic String toString() {final StringBuffer sb = new StringBuffer("Employee{");sb.append("no='").append(no).append('\'');sb.append(", name='").append(name).append('\'');sb.append(", gender='").append(gender).append('\'');sb.append(", portrait='").append(portrait).append('\'');sb.append('}');return sb.toString();}public String getNo() {return no;}public void setNo(String no) {this.no = no;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}public String getPortrait() {return portrait;}public void setPortrait(String portrait) {this.portrait = portrait;}
}用來生成Excel 文件的 ImageExcelUtils 類
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
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.HorizontalAlignment;
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.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;import javax.imageio.ImageIO;public class ImageExcelUtils {/*** 讀取jpg圖片* @param path 圖片文件路徑* @return BufferedImage*/public static BufferedImage readBufferedImage(String path) {BufferedImage originImage = null;BufferedImage result = null;try {File file = new File(path);originImage = ImageIO.read(file);// 確保圖片顏色只有RGB，沒有alpha透明度result = new BufferedImage(originImage.getWidth(),originImage.getHeight(),BufferedImage.TYPE_INT_RGB);result.getGraphics().drawImage(originImage, 0, 0, null);} catch (IOException e) {e.printStackTrace();}return result;}/*** 設置excel文件的圖片* @param workbook 工作簿* @param sheet sheet頁* @param imgFilePath 圖片文件路徑* @param row1 圖片起始的行* @param col1 圖片起始的列* @param tempFilePath 臨時文件路徑*/public static void setExcelImg(Workbook workbook, Sheet sheet, String imgFilePath,int row1, int col1, int width, int height, String tempFilePath) {File file = new File(imgFilePath);if (!file.exists()) {return;}// 臨時文件File tempFile = new File(tempFilePath);if (tempFile.exists()) {tempFile.delete();}FileInputStream inputStream = null;boolean isClose = false;try {BufferedImage bufferedImage = readBufferedImage(imgFilePath);int imageWidth = bufferedImage.getWidth();int imageHeight = bufferedImage.getHeight();ImageIO.write(bufferedImage, "png", tempFile);inputStream = new FileInputStream(tempFile);//利用POI提供的工具類把文件流轉化成二進制數據byte[] bytes = IOUtils.toByteArray(inputStream);//向POI內存中添加一張圖片，返回圖片在圖片集合中的索引int pictureIndex = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);//參數一：圖片的二進制數據，參數二：圖片類型//從Workbook中得到繪制圖片的工具類CreationHelper helper = workbook.getCreationHelper();//創建錨點，設置圖片坐標ClientAnchor clientAnchor = helper.createClientAnchor();clientAnchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);clientAnchor.setRow1(row1); // 設置起始行clientAnchor.setCol1(col1); // 設置起始列//從sheet對象中得到一個繪圖對象Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();//繪制圖片，錨點，圖片在內存中的位置Picture picture = drawingPatriarch.createPicture(clientAnchor, pictureIndex);// 使用固定的長寬比例系數double scaleX = 1.0;double scaleY = 1.0;if (imageWidth <= width && imageHeight <= height) {scaleX = 1.0;scaleY = 1.0;} else {scaleX = (double) width / (double) imageWidth;scaleY = (double) height / (double) imageHeight;double min = Math.min(scaleX, scaleY);scaleX = scaleY = min;}picture.resize(scaleX, scaleY);//自適應渲染圖片} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {if (inputStream != null && !isClose) {inputStream.close();}tempFile.delete();} catch (IOException e) {e.printStackTrace();}}}/*** 創建Excel文件* @param employeeDTOList 員工列表* @param file 輸出的 Excel 文件* @param tempFilePath 臨時文件路徑*/public static void createExcelFile(List<EmployeeDTO> employeeDTOList, File file, String tempFilePath) {//創建工作簿，excel2007版本的，如果是excel2003的話。創建的對象是：HSSFWorkbookWorkbook workbook = new SXSSFWorkbook();//創建sheetSheet sheet = workbook.createSheet("picture sheet");// 列寬分別是25個字符和40個字符，一字符等于 6.107 像素sheet.setColumnWidth(0, 25 * 256);sheet.setColumnWidth(1, 25 * 256);sheet.setColumnWidth(2, 25 * 256);sheet.setColumnWidth(3, 40 * 256);// 行高是 120 磅，1磅是 1.34039 像素sheet.setDefaultRowHeightInPoints(120f);// 設置字體，黑體Font font = workbook.createFont();font.setFontName("黑體");// 字體加粗font.setBold(true);CellStyle cellStyle = workbook.createCellStyle();cellStyle.setFont(font);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setAlignment(HorizontalAlignment.CENTER);// 表格標題行Row firstRow = sheet.createRow(0);Cell r0c0 = firstRow.createCell(0);r0c0.setCellValue("工號");Cell r0c1 = firstRow.createCell(1);r0c1.setCellValue("姓名");Cell r0c2 = firstRow.createCell(2);r0c2.setCellValue("性別");Cell r0c3 = firstRow.createCell(3);r0c3.setCellValue("頭像");r0c0.setCellStyle(cellStyle);r0c1.setCellStyle(cellStyle);r0c2.setCellStyle(cellStyle);r0c3.setCellStyle(cellStyle);int size = employeeDTOList.size();for (int i = 0; i < size; i++) {EmployeeDTO dto = employeeDTOList.get(i);int rowIndex = i + 1;Row row = sheet.createRow(rowIndex);Cell c0 = row.createCell(0);Cell c1 = row.createCell(1);Cell c2 = row.createCell(2);Cell c3 = row.createCell(3);// 向excel中輸入圖片String portrait = dto.getPortrait();if (null != portrait && portrait.trim().length() > 0) {portrait = portrait.trim();setExcelImg(workbook, sheet, portrait, rowIndex, 3, 244, 160, tempFilePath);}c0.setCellValue(dto.getNo());c1.setCellValue(dto.getName());c2.setCellValue(dto.getGender());}//創建文件輸入流FileOutputStream out = null;//創建文件輸出流try {out = new FileOutputStream(file);//調用工作簿的write創建excelworkbook.write(out);} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {if (out != null) {out.flush();out.close();out = null;}} catch (IOException e) {e.printStackTrace();}} // end finally}
}包含 main 方法的 Test 類，測試具體效果
import java.io.File;
import java.util.List;
import java.util.ArrayList;public class Test {public static void main(String[] args) {List<EmployeeDTO> employeeDTOList = new ArrayList<>();EmployeeDTO emp_1 = new EmployeeDTO();emp_1.setNo("1");emp_1.setName("張三");emp_1.setGender("男");emp_1.setPortrait("D:/1715284526222417922.jpg");EmployeeDTO emp_2 = new EmployeeDTO();emp_2.setNo("2");emp_2.setName("李四");emp_2.setGender("男");emp_2.setPortrait("D:\\bmptest.bmp");EmployeeDTO emp_3 = new EmployeeDTO();emp_3.setNo("3");emp_3.setName("王二");emp_3.setGender("女");emp_3.setPortrait("D:\\113.jpg");EmployeeDTO emp_4 = new EmployeeDTO();emp_4.setNo("4");emp_4.setName("涂軍");emp_4.setGender("男");emp_4.setPortrait("D:\\309446533.gif");employeeDTOList.add(emp_1);employeeDTOList.add(emp_2);employeeDTOList.add(emp_3);employeeDTOList.add(emp_4);File file = new File("D:\\ws\\tmpdir\\out.xlsx");String tempFilePath = "D:\\ws\\tmpdir\\temp.png";ImageExcelUtils.createExcelFile(employeeDTOList, file, tempFilePath);}
}
![[linux] 解壓縮xz](http://pic.xiahunao.cn/[linux] 解壓縮xz)
 屏切換)
