package com.excel;
import java.io.FileInputStream;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
public class ExcelWriter {
private static String[] columns = {"Name", "Email", "Date Of Birth", "Salary", "Photo"};
private static List<Employee> employees = new ArrayList<>();
static {
Calendar dateOfBirth = Calendar.getInstance();
dateOfBirth.set(1992, 7, 21);
employees.add(new Employee("Ravinath Fernandoh", "ravi@example.com",
dateOfBirth.getTime(), 1200000.0));
dateOfBirth.set(1965, 10, 15);
employees.add(new Employee("Gayathri Sirimanna", "fer@example.com",
dateOfBirth.getTime(), 1500000.0));
dateOfBirth.set(1987, 4, 18);
employees.add(new Employee("Bivon Jethmain", "bivon@example.com",
dateOfBirth.getTime(), 1800000.0));
}
public static void main(String[] args) throws IOException, InvalidFormatException {
Workbook workbook = new XSSFWorkbook();
CreationHelper createHelper = workbook.getCreationHelper();
Sheet sheet = workbook.createSheet("Employee");
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short) 14);
headerFont.setColor(IndexedColors.RED.getIndex());
CellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFont(headerFont);
Row headerRow = sheet.createRow(0);
for (int i = 0; i < columns.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(columns[i]);
cell.setCellStyle(headerCellStyle);
}
CellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MM-yyyy"));
int rowNum = 1;
for (Employee employee : employees) {
Row row = sheet.createRow(rowNum++);
row.setHeight((short) 1000);
row.createCell(0).setCellValue(employee.getName());
row.createCell(1).setCellValue(employee.getEmail());
Cell dateOfBirthCell = row.createCell(2);
dateOfBirthCell.setCellValue(employee.getDateOfBirth());
dateOfBirthCell.setCellStyle(dateCellStyle);
row.createCell(3).setCellValue(employee.getSalary());
InputStream my_banner_image = new FileInputStream("D:\\PB_PROJECT\\NFC School Card\\NFCREST\\web\\photo_student\\4566.png");
byte[] bytes = IOUtils.toByteArray(my_banner_image);
int my_picture_id = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
my_banner_image.close();
XSSFDrawing drawing = (XSSFDrawing) sheet.createDrawingPatriarch();
XSSFClientAnchor my_anchor = new XSSFClientAnchor();
my_anchor.setCol1(5);
my_anchor.setRow1(rowNum-1);
my_anchor.setCol2(6);
my_anchor.setRow2(rowNum);
XSSFPicture my_picture = drawing.createPicture(my_anchor, my_picture_id);
}
for (int i = 0; i < columns.length; i++) {
sheet.autoSizeColumn(i);
}
FileOutputStream fileOut = new FileOutputStream("poi-generated-file.xlsx");
workbook.write(fileOut);
fileOut.close();
workbook.close();
}
}