java / POI / excel

 

🥕 코드먼저 기록.

package a.test;

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.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;
 
public class PoiExcelImageTest {

	public static void main(String[] args){
		Workbook wb = new XSSFWorkbook();
		CellStyle styleVertAlingTop = wb.createCellStyle();
		styleVertAlingTop.setVerticalAlignment(VerticalAlignment.TOP);

		Sheet sheet = wb.createSheet();
		sheet.setColumnWidth(0, 15 * 256); //15 default characters width
		sheet.setColumnWidth(1, 30 * 256); //30 default characters width

		Row row = sheet.createRow(2);
		row.setHeight((short)(100 * 20)); //100pt height * 20 = twips (twentieth of an inch point)

		Cell cell = row.createCell(0);
		cell.setCellValue("Task 1");

		cell = row.createCell(1);
		cell.setCellValue("Replace Kemplon-Pipe");
		cell.setCellStyle(styleVertAlingTop);

		InputStream inputstream;
		try {
			System.out.println("testestsetsetset");
			inputstream = new FileInputStream("D:/test1123.png");
			byte[] bytes = IOUtils.toByteArray(inputstream);
			int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
			inputstream.close();
	
			int left = 20; // 20px
			int top = 20; // 20pt
			int width = Math.round(sheet.getColumnWidthInPixels(1) - left - left); //width in px
			int height = Math.round(row.getHeightInPoints() - top - 10/*pt*/); //height in pt
	
			drawImageOnExcelSheet((XSSFSheet)sheet, 2, 1, left, top, width, height, pictureIdx);
	
			wb.write(new FileOutputStream("D:/ExcelDrawImagesOverCell.xlsx"));
			wb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	// Writing Image on Excel 
	public static void drawImageOnExcelSheet(XSSFSheet sheet
			, int row
			, int col
			, int left/*in px*/
			, int top/*in pt*/
			, int width/*in px*/
			, int height/*in pt*/
			, int pictureIdx) throws Exception{

		CreationHelper helper = sheet.getWorkbook().getCreationHelper();
		Drawing drawing = sheet.createDrawingPatriarch();
		ClientAnchor anchor = helper.createClientAnchor();
		anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);

		anchor.setCol1(col); //first anchor determines upper left position
		anchor.setRow1(row);
		anchor.setDx1(Units.pixelToEMU(left)); //dx = left in px
		anchor.setDy1(Units.toEMU(top)); //dy = top in pt

		anchor.setCol2(col); //second anchor determines bottom right position
		anchor.setRow2(row); 
		anchor.setDx2(Units.pixelToEMU(left + width)); //dx = left + wanted width in px
		anchor.setDy2(Units.toEMU(top + height)); //dy= top + wanted height in pt

		drawing.createPicture(anchor, pictureIdx);
	}

}

 

출처 : https://yjacket.tistory.com/66

 

poi 라이브러리가 가장 작다면, 

물론 row 개수가 엄청 많은 파일도 가끔 존재하나, 

속도가 조금 느릴 수는 있지만 현재는 다운로드받아 놓는 파일의 개수가 많아짐을 대비해서 파일 크기는 가장 작은 것으로 처리하는게 낫다고 생각해서 사용.

 

pom.xml 주의사항 : 

    poi /// poi-ooxml 2개 버젼이 동일해야 함.

실행 결과 : 

 

입력 확인.