excel insert image from url

<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();
	}
}

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>