<code>Dim url_column As Range Dim image_column As Range Set url_column = Worksheets(1).UsedRange.Columns("A") Set image_column = Worksheets(1).UsedRange.Columns("B") Dim i As Long For i = 1 To url_column.Cells.Count With image_column.Worksheet.Pictures.Insert(url_column.Cells(i).Value) .Left = image_column.Cells(i).Left .Top = image_column.Cells(i).Top image_column.Cells(i).EntireRow.RowHeight = .Height End With Next</code>
import java.awt.image.BufferedImage; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.net.URL; import java.util.Scanner; import javax.imageio.ImageIO; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFHyperlink; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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.util.CellRangeAddress; public class ExcelInsertImageFromURL{ public static void main(String[] args) throws Exception { Scanner sc = new Scanner(new File("d:/g.txt")); String fileName = "d:/2501-3000.xls"; ByteArrayOutputStream byteArrayOut = null; BufferedImage bufferImg = null; HSSFWorkbook wb = null; Sheet sheet = null; File f = new File(fileName); URL url = null; try { if (!f.exists()) { wb = new HSSFWorkbook(); } else { FileInputStream in = new FileInputStream(fileName); wb = new HSSFWorkbook(in); } HSSFHyperlink link = null; HSSFPatriarch patri = null; HSSFClientAnchor anchor = null; CellStyle style = wb.createCellStyle(); style.setFillForegroundColor(HSSFCellStyle.THIN_BACKWARD_DIAG); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // style.setLeftBorderColor(HSSFColor.RED.index); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框 style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框 style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框 style.setWrapText(true); sheet = wb.createSheet("sheet " + ((int) (100000 * Math.random()))); // 设置列的宽度 sheet.setDefaultColumnWidth(20); sheet.setDefaultRowHeight((short) 400); Row row = null; Cell cell = null; int r = 1; int c = 2; sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1)); while (sc.hasNext()) { row = sheet.createRow(r); row.setHeightInPoints(180); String[] strs = sc.nextLine().split("\\s"); cell = row.createCell(0); cell.setCellValue(strs[0]); // 添加远程图片信息 cell = row.createCell(1); cell.setCellValue(strs[1]); cell = row.createCell(2); url = new URL(strs[2]); bufferImg = ImageIO.read(url.openStream()); byteArrayOut = new ByteArrayOutputStream(); ImageIO.write(bufferImg, "PNG", byteArrayOut); patri = (HSSFPatriarch) sheet.createDrawingPatriarch(); anchor = new HSSFClientAnchor(10, 2, 0, 0, (short) c, r, (short) (c + 2), r + 1); patri.createPicture(anchor, wb.addPicture( byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG)); bufferImg.flush(); r+=1; } } catch (Exception e) { } FileOutputStream out = new FileOutputStream(fileName); wb.write(out); out.close(); } }