ExcelUtils.java

/*
 * Copyright 2019 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.genesys.util;

import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.Date;
import java.util.Objects;
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 url.
	 *
	 * @param row the row
	 * @param column the column
	 * @param value the value
	 * @return the cell
	 */
	public static Cell addCellUrl(Cell c, String url) {
		if (url == null) return c;

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

		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 date the date
	 * @param dateStyle the date style
	 * @return the cell
	 */
	public static Cell updateCell(Row row, int column, LocalDate 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, LocalDateTime 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 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;
	}

	/**
	 * Update cell.
	 *
	 * @param row the row
	 * @param column the column
	 * @param value the value
	 * @return the cell
	 */
	public static <T> Cell updateCell(Row row, int column, T value) {
		if (value == null)
			return null;
		Cell c = row.getCell(column);
		if (c == null)
			c = row.createCell(column);

		if (value instanceof String) {
			c.setCellValue((String) value);
		} else if (value instanceof Number) {
			c.setCellValue(((Number) value).doubleValue());
		} else if (Boolean.class.isAssignableFrom(value.getClass())) {
			c.setCellValue(((Boolean) value).booleanValue());
		} else {
			c.setCellValue(Objects.toString(value));
		}
		return c;
	}
}