ExcelUtils.java

/*
 * Copyright 2026 Global Crop Diversity Trust
 * Licensed under the Apache License, Version 2.0
 * See LICENSE file in project root folder or http://www.apache.org/licenses/LICENSE-2.0
 */

package org.gringlobal.util;

import java.time.Instant;
import java.time.ZoneOffset;
import java.util.Date;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.Row;

/**
 * ExcelUtils.
 *
 * @author Matija Obreza
 */
public abstract class ExcelUtils {

	/**
	 * Update cell email.
	 *
	 * @param row the row
	 * @param column the column
	 * @param value the value
	 * @return the cell
	 */
	public static Cell updateCellEmail(Row row, int column, String value) {
		if (StringUtils.isBlank(value))
			return null;
		Cell c = row.getCell(column);
		if (c == null)
			c = row.createCell(column);

		CreationHelper createHelper = row.getSheet().getWorkbook().getCreationHelper();
		Hyperlink link = createHelper.createHyperlink(HyperlinkType.EMAIL);
		link.setAddress("mailto:" + value);
		c.setHyperlink(link);
		c.setCellValue(value);

		return c;
	}

	/**
	 * Update cell url.
	 *
	 * @param row the row
	 * @param column the column
	 * @param value the value
	 * @return the cell
	 */
	public static Cell updateCellUrl(Row row, int column, String value) {
		if (StringUtils.isBlank(value))
			return null;
		Cell c = row.getCell(column);
		if (c == null)
			c = row.createCell(column);

		CreationHelper createHelper = row.getSheet().getWorkbook().getCreationHelper();
		Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
		link.setAddress(value);
		c.setHyperlink(link);
		c.setCellValue(value);

		return c;
	}

	/**
	 * Update cell.
	 *
	 * @param row the row
	 * @param column the column
	 * @param values the values
	 * @return the cell
	 */
	public static Cell updateCell(Row row, int column, Set<?> values) {
		if (values == null || values.isEmpty())
			return null;
		Cell c = row.getCell(column);
		if (c == null)
			c = row.createCell(column);
		c.setCellValue(MCPDUtil.toMcpdArray(values));
		return c;
	}

	/**
	 * Update cell.
	 *
	 * @param row the row
	 * @param column the column
	 * @param value the value
	 * @return the cell
	 */
	public static Cell updateCell(Row row, int column, String value) {
		if (StringUtils.isBlank(value))
			return null;
		Cell c = row.getCell(column);
		if (c == null)
			c = row.createCell(column);
		c.setCellValue(value);
		return c;
	}

	/**
	 * Update cell.
	 *
	 * @param row the row
	 * @param column the column
	 * @param date the date
	 * @param dateStyle the date style
	 * @return the cell
	 */
	public static Cell updateCell(Row row, int column, Date date, CellStyle dateStyle) {
		if (date == null)
			return null;
		Cell c = row.getCell(column);
		if (c == null)
			c = row.createCell(column);
		if (dateStyle != null) {
			c.setCellStyle(dateStyle);
		}
		c.setCellValue(date);
		return c;
	}

	/**
	 * Update cell.
	 *
	 * @param row the row
	 * @param column the column
	 * @param date the date
	 * @param dateStyle the date style
	 * @return the cell
	 */
	public static Cell updateCell(Row row, int column, Instant date, CellStyle dateStyle) {
		if (date == null)
			return null;
		Cell c = row.getCell(column);
		if (c == null)
			c = row.createCell(column);
		if (dateStyle != null) {
			c.setCellStyle(dateStyle);
		}
		c.setCellValue(date.atOffset(ZoneOffset.UTC).toLocalDateTime());
		return c;
	}

	/**
	 * Update cell.
	 *
	 * @param row the row
	 * @param column the column
	 * @param bool the bool
	 * @return the cell
	 */
	public static Cell updateCell(Row row, int column, Boolean bool) {
		if (bool == null)
			return null;
		Cell c = row.getCell(column);
		if (c == null)
			c = row.createCell(column);
		c.setCellValue(bool);
		return c;
	}

	/**
	 * Update cell.
	 *
	 * @param row the row
	 * @param column the column
	 * @param number the number
	 * @return the cell
	 */
	public static Cell updateCell(Row row, int column, Number number) {
		if (number == null)
			return null;
		Cell c = row.getCell(column);
		if (c == null)
			c = row.createCell(column);
		c.setCellValue(number.doubleValue());
		return c;
	}

}