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

Go to download
Show more of this group Show more artifacts with this name
Show all versions of databene-formats Show documentation
'Databene Formats' is an open source software library for supporting data file and other formats like CSV, fixed width files, XLS, Properties and Regex. It is designed for multithreaded use and high performance.
There is a newer version: 1.0.14
Show newest version
/*
 * 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;
	}

}