aaronanderson/ExcelEmeddedFileTest.java

## ExcelEmeddedFileTest.java
//Simple example of embedding an arbitrary file in Excel using Apache POI mimicing the Office Insert Object functionality.

import java.io.ByteArrayOutputStream;
import java.net.URL;
import java.nio.file.Files;
import java.nio.file.Paths;

import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.xssf.usermodel.XSSFObjectData;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;

//requires maven org.apache.poi:ooxml-schemas:1.4 , no visible difference if excluded.
//import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDvAspect;

public class ExcelEmeddedFileTest {

	@Test
	void excelEmbeddedFileTest() {
		try (XSSFWorkbook wb = new XSSFWorkbook()) {
			XSSFSheet sheet = wb.createSheet();
			XSSFRow row = sheet.createRow(0);
			row.createCell(0).setCellValue("Embedded File Test");

			String content = "<html><head></head><body><h1>Test Embedded HTML File</h1></body></html>";
			String objectName = "test_embedded";
			String fileExtension = ".html";
			int rowNo = 1;
			int colNo = 1;

			ByteArrayOutputStream firefoxIcon = new ByteArrayOutputStream();
			//https://design.firefox.com/photon/visuals/product-identity-assets.html
			URL iconURL = new URL("https://d33wubrfki0l68.cloudfront.net/06185f059f69055733688518b798a0feb4c7f160/9f07a/images/product-identity-assets/firefox.png");
			//Thread.currentThread().getContextClassLoader().getResourceAsStream("firefox.png").transferTo(excelIcon);
			iconURL.openStream().transferTo(firefoxIcon);
			final int iconId = wb.addPicture(firefoxIcon.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);

			final int oleIdx = wb.addOlePackage(content.getBytes(), objectName, objectName + fileExtension, objectName + fileExtension);
			final Drawing<?> pat = sheet.createDrawingPatriarch();
			final ClientAnchor anchor = pat.createAnchor(0, 0, 0, 0, colNo, rowNo, colNo + 1, rowNo + 2);//tweak cell ranges to minimize image distortion
			anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);

			final XSSFObjectData objectData = (XSSFObjectData) pat.createObjectData(anchor, oleIdx, iconId);
			objectData.getCTShape().getNvSpPr().getCNvPr().setName(objectName);
			objectData.getCTShape().getNvSpPr().getCNvPr().setHidden(false);
			//objectData.getOleObject().setDvAspect(STDvAspect.DVASPECT_ICON);

			wb.write(Files.newOutputStream(Paths.get("target/embedded-file-test.xlsx")));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
	//Simple example of embedding an arbitrary file in Excel using Apache POI mimicing the Office Insert Object functionality.

	import java.io.ByteArrayOutputStream;
	import java.net.URL;
	import java.nio.file.Files;
	import java.nio.file.Paths;

	import org.apache.poi.ss.usermodel.ClientAnchor;
	import org.apache.poi.ss.usermodel.Drawing;
	import org.apache.poi.xssf.usermodel.XSSFObjectData;
	import org.apache.poi.xssf.usermodel.XSSFRow;
	import org.apache.poi.xssf.usermodel.XSSFSheet;
	import org.apache.poi.xssf.usermodel.XSSFWorkbook;
	import org.junit.jupiter.api.Test;

	//requires maven org.apache.poi:ooxml-schemas:1.4 , no visible difference if excluded.
	//import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDvAspect;

	public class ExcelEmeddedFileTest {

	@Test
	void excelEmbeddedFileTest() {
	try (XSSFWorkbook wb = new XSSFWorkbook()) {
	XSSFSheet sheet = wb.createSheet();
	XSSFRow row = sheet.createRow(0);
	row.createCell(0).setCellValue("Embedded File Test");

	String content = "<html><head></head><body><h1>Test Embedded HTML File</h1></body></html>";
	String objectName = "test_embedded";
	String fileExtension = ".html";
	int rowNo = 1;
	int colNo = 1;

	ByteArrayOutputStream firefoxIcon = new ByteArrayOutputStream();
	//https://design.firefox.com/photon/visuals/product-identity-assets.html
	URL iconURL = new URL("https://d33wubrfki0l68.cloudfront.net/06185f059f69055733688518b798a0feb4c7f160/9f07a/images/product-identity-assets/firefox.png");
	//Thread.currentThread().getContextClassLoader().getResourceAsStream("firefox.png").transferTo(excelIcon);
	iconURL.openStream().transferTo(firefoxIcon);
	final int iconId = wb.addPicture(firefoxIcon.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);

	final int oleIdx = wb.addOlePackage(content.getBytes(), objectName, objectName + fileExtension, objectName + fileExtension);
	final Drawing<?> pat = sheet.createDrawingPatriarch();
	final ClientAnchor anchor = pat.createAnchor(0, 0, 0, 0, colNo, rowNo, colNo + 1, rowNo + 2);//tweak cell ranges to minimize image distortion
	anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);

	final XSSFObjectData objectData = (XSSFObjectData) pat.createObjectData(anchor, oleIdx, iconId);
	objectData.getCTShape().getNvSpPr().getCNvPr().setName(objectName);
	objectData.getCTShape().getNvSpPr().getCNvPr().setHidden(false);
	//objectData.getOleObject().setDvAspect(STDvAspect.DVASPECT_ICON);

	wb.write(Files.newOutputStream(Paths.get("target/embedded-file-test.xlsx")));
	} catch (Exception e) {
	e.printStackTrace();
	}
	}

	}