#tinhtun #apache_poi

Read-Write-Excel-in-Java-with-Apache-POI.md

How to read and write excel files in Java using Apache POI

Maven Dependency

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

Adding images into excel file

• Make sure to setup anchor properly as below
• Do not reuse the same anchor for multipe images

// Sample code
ClientAnchor anchor = helper.createClientAnchor();
anchor.setCol1(0); // Start Column
anchor.setCol2(3); // End Column
anchor.setRow1(0); // Start Row
anchor.setRow2(5); // End Row

References

• https://www.mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/
• https://gist.github.com/madan712/3912272

WriteImageInExcelFile.java

package me.tinhtun;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteImageInExcelFile {

	public static void main(String[] args) throws Exception {
		System.out.println("Start writing excel file.");
		writeXLSXFile();
		System.out.println("Done.");
	}

	public static void writeXLSXFile() throws IOException {

		String excelFileName = "Test.xlsx";// name of excel file

		String sheetName = "Sheet1";// name of sheet

		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet(sheetName);

		CreationHelper helper = workbook.getCreationHelper();
		try (InputStream catLeftIs = WriteImageInExcelFile.class.getClassLoader().getResourceAsStream("cat-left.jpg");
				InputStream catRightIs = WriteImageInExcelFile.class.getClassLoader()
						.getResourceAsStream("cat-right.jpg")) {
			byte[] catLeftBytes = IOUtils.toByteArray(catLeftIs);
			int catLeftIdx = workbook.addPicture(catLeftBytes, Workbook.PICTURE_TYPE_JPEG);
			XSSFDrawing drawing = sheet.createDrawingPatriarch();
			ClientAnchor catLeftAnchor = helper.createClientAnchor();
			catLeftAnchor.setCol1(0);
			catLeftAnchor.setCol2(3);
			catLeftAnchor.setRow1(0);
			catLeftAnchor.setRow2(5);
			drawing.createPicture(catLeftAnchor, catLeftIdx);

			byte[] catRightBytes = IOUtils.toByteArray(catRightIs);
			int catRightIdx = workbook.addPicture(catRightBytes, Workbook.PICTURE_TYPE_JPEG);
			ClientAnchor catRightAnchor = helper.createClientAnchor();
			catRightAnchor.setCol1(10);
			catRightAnchor.setCol2(13);
			catRightAnchor.setRow1(0);
			catRightAnchor.setRow2(5); 
			drawing.createPicture(catRightAnchor, catRightIdx);
		}

		// iterating r number of rows
		for (int r = 6; r < 10; r++) {
			XSSFRow row = sheet.createRow(r);

			// iterating c number of columns
			for (int c = 0; c < 5; c++) {
				XSSFCell cell = row.createCell(c);

				cell.setCellValue("Cell " + r + " " + c);
			}
		}

		try (FileOutputStream fileOut = new FileOutputStream(excelFileName)) {
			// write this workbook to an Outputstream.
			workbook.write(fileOut);
			fileOut.flush();
		}

		workbook.close();
	}

}