ExcelUtils.java

/*
 * Copyright 2021 Global Crop Diversity Trust
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
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;
	}

}