org.databene.formats.xls.XLSUtil Maven / Gradle / Ivy

Go to download
/*
 * Copyright (C) 2011-2015 Volker Bergmann ([email protected]).
 * All rights reserved.
 *
 * 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.databene.formats.xls;

import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.databene.commons.ConfigurationError;
import org.databene.commons.Converter;
import org.databene.commons.MathUtil;
import org.databene.commons.converter.ToStringConverter;

/**
 * Provides utility methods for HSSF (POI).
 * 
 * Created at 09.08.2009 07:47:52
 * @since 0.5.0
 * @author Volker Bergmann
 */

public class XLSUtil {

	private XLSUtil() { }
	
	public static Object resolveCellValue(Cell cell) {
		return resolveCellValue(cell, "'", null, null);
	}
	
	public static Object resolveCellValue(Cell cell, String emptyMarker, String nullMarker, Converter stringPreprocessor) {
		if (cell == null)
			return null;
		switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING: return convertString(cell, emptyMarker, nullMarker, stringPreprocessor);
			case Cell.CELL_TYPE_NUMERIC: 
				if (HSSFDateUtil.isCellDateFormatted(cell))
					return cell.getDateCellValue();
				else
					return mapNumberType(cell.getNumericCellValue());
			case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue();
			case Cell.CELL_TYPE_BLANK: 
			case Cell.CELL_TYPE_ERROR: return cell.getRichStringCellValue().getString();
			case Cell.CELL_TYPE_FORMULA:
				FormulaEvaluator evaluator = createFormulaEvaluator(cell); 
				CellValue cellValue = evaluator.evaluate(cell);
				switch (cellValue.getCellType()) {
					case HSSFCell.CELL_TYPE_STRING: return convertString(cellValue, emptyMarker, stringPreprocessor);
				    case HSSFCell.CELL_TYPE_NUMERIC:
				    	if (HSSFDateUtil.isCellDateFormatted(cell))
				    		return HSSFDateUtil.getJavaDate(cellValue.getNumberValue());
				    	else
				    		return mapNumberType(cellValue.getNumberValue());
				    case Cell.CELL_TYPE_BOOLEAN: return cellValue.getBooleanValue();
				    case HSSFCell.CELL_TYPE_BLANK:
				    case HSSFCell.CELL_TYPE_ERROR: return null;
				    default: throw new IllegalStateException("Unexpected cell type: " + cellValue.getCellType());
				    	// CELL_TYPE_FORMULA is not supposed to be encountered here
				}	
			default: throw new ConfigurationError("Not a supported cell type: " + cell.getCellType());
		}
	}
	
	/** Resolves a formula or a normal cell and formats the result as it would be displayed in Excel. 
	 * @param cell the cell to resolve
	 * @return a string representation of the cell value */
	public static String resolveCellValueAsString(Cell cell) {
		return resolveCellValueAsString(cell, "'", null, null);
	}
	
	/** Resolves a formula or a normal cell and formats the result as it would be displayed in Excel 
	 * @param cell the cell to resolve
	 * @param emptyMarker the string to interpret as empty field
	 * @param nullMarker the string to interpret as null value
	 * @param stringPreprocessor a preprocessor to apply to the raw field values
	 * @return a string representation of the cell value */
	public static String resolveCellValueAsString(Cell cell, String emptyMarker, String nullMarker, Converter stringPreprocessor) {
		if (cell == null)
			return null;
		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
	    	String content = cell.getRichStringCellValue().getString();
	    	if (content != null) {
		    	if (content.equals(emptyMarker) || content.equals("'"))
		    		content = "";
		    	else if (content.equals(nullMarker))
		    		content = null;
	    	}
	    	if (stringPreprocessor != null)
	    		content = ToStringConverter.convert(stringPreprocessor.convert(content), null);
	    	return content;
		} else {
			DataFormatter formatter = new DataFormatter();
			if (cell.getCellType() == Cell.CELL_TYPE_FORMULA)
				return formatter.formatCellValue(cell, createFormulaEvaluator(cell));
			else
				return formatter.formatCellValue(cell);
		}
	}

	public static void autoSizeColumns(Workbook workbook) {
		int sheetCount = workbook.getNumberOfSheets();
		for (int i = 0; i < sheetCount; i++) {
			Sheet sheet = workbook.getSheetAt(i);
			int firstRowNum = sheet.getFirstRowNum();
			if (firstRowNum >= 0) {
				Row firstRow = sheet.getRow(firstRowNum);
				for (int cellnum = firstRow.getFirstCellNum(); cellnum < firstRow.getLastCellNum(); cellnum++)
					sheet.autoSizeColumn(cellnum);
			}
		}
	}
	
	public static boolean isEmpty(Row row) {
		if (row == null)
			return true;
		for (int i = 0; i < row.getLastCellNum(); i++)
			if (!isEmpty(row.getCell(i)))
				return false;
		return true;
	}
	
	public static boolean isEmpty(Cell cell) {
		if (cell == null)
			return true;
		if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)
			return true;
		if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
			return cell.getStringCellValue().isEmpty();
		return false;
	}
	
	
	// private helpers -------------------------------------------------------------------------------------------------
	
	private static FormulaEvaluator createFormulaEvaluator(Cell cell) {
		return cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
	}

	private static Number mapNumberType(double numericCellValue) {
		if (MathUtil.isIntegralValue(numericCellValue))
			return ((Double) numericCellValue).longValue();
		return numericCellValue;
	}

    @SuppressWarnings({ "unchecked", "rawtypes" })
	private static Object convertString(CellValue cellValue, String emptyMarker, Converter stringPreprocessor) {
    	String content = cellValue.getStringValue();
    	if (content != null && (content.equals(emptyMarker) || content.equals("'")))
    		content = "";
    	return (stringPreprocessor != null ? ((Converter) stringPreprocessor).convert(content) : content);
    }

	@SuppressWarnings({ "unchecked", "rawtypes" })
	private static Object convertString(Cell cell, String emptyMarker, String nullMarker, Converter stringPreprocessor) {
    	String content = cell.getRichStringCellValue().getString();
    	if (content != null) {
	    	if (content.equals(emptyMarker) || content.equals("'"))
	    		content = "";
	    	if (content.equals(nullMarker))
	    		content = null;
    	}
    	return (stringPreprocessor != null ? ((Converter) stringPreprocessor).convert(content) : content);
    }

	public static int getColumnCount(Sheet sheet) {
		int columnCount = 0;
		Iterator rowIterator = sheet.rowIterator();
		while (rowIterator.hasNext())
			columnCount = Math.max(columnCount, rowIterator.next().getLastCellNum());
		return columnCount;
	}

}