/*
 * Copyright (C) 2005  Markus Kraetzig
 * Created on 21.10.2005
 */
package com.jstatcom.io;

import java.util.List;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;

/**
 * Helper methods for dealing with Excel files.
 * 
 * @author Markus Kraetzig 
 */
public final class ExcelHelper {

    /**
     * Extracts a double array from an excel sheet. It first checks the highest
     * and lowest row indices with numbers and then extracts all cell data in
     * between to a number array. All non numbers within are coded NaN.
     * 
     * @param w
     *            the workbook
     * @param sheetIndex
     *            index of the sheet
     * @param firstAsDateCol
     *            whether first column should be skipped, often it contains date
     *            coding
     * @param firstColList
     *            list that is filled with elements (usually dates) from the
     *            first column if firstAsDateCol == true, if
     *            null then it is ignored
     * @param labelsList
     *            list that is filled with labels for each column, the first
     *            CellType.LABEL value in each column is used; if
     *            firstAsDateCol == true then the first column is
     *            skipped, if null then it is ignored
     * @return double array with data
     * @throws IllegalArgumentException
     *             if null argument or invalid sheetIndex
     */
    public static double[][] extractNumberArray(Workbook w, int sheetIndex,
            boolean firstAsDateCol, List firstColList,
            List labelsList) {

        if (w == null)
            throw new IllegalArgumentException("Argument was null.");

        if (sheetIndex < 0)
            throw new IllegalArgumentException("Sheet index < 0.");
        int sheetNum = w.getNumberOfSheets();
        String[] sheetNames = w.getSheetNames();

        if (sheetIndex >= sheetNum)
            throw new IllegalArgumentException("Specified sheet index "
                    + sheetIndex + " not contained.");

        Sheet s = w.getSheet(sheetIndex);
        int highestIndex = getHighestIndexWithNumber(s);
        if (highestIndex < 0)
            throw new RuntimeException("Excel sheet \""
                    + sheetNames[sheetIndex] + "\" contains no numbers.");

        // Then check, at which index the first series starts.
        int lowestIndex = getLowestIndexWithNumber(s);

        int dataLength = highestIndex - lowestIndex + 1;
        int first = firstAsDateCol ? 1 : 0;
        int dataCols = s.getColumns() - first;
        double[][] data = new double[dataLength][dataCols];

        for (int i = first; i < s.getColumns(); i++) {
            Cell[] col = s.getColumn(i);
            // Fill the data array with values.
            for (int j = lowestIndex; j <= highestIndex; j++) {
                if (j < col.length
                        && (col[j].getType() == CellType.NUMBER || col[j]
                                .getType() == CellType.NUMBER_FORMULA))
                    data[j - lowestIndex][i - first] = ((NumberCell) col[j])
                            .getValue();
                else {
                    data[j - lowestIndex][i - first] = Double.NaN;
                }
            }
            // and the labels
            if (labelsList != null) {

                // default
                String toAdd = "var" + (i + 1);

                // try to find better
                for (int j = 0; j < col.length; j++)
                    if (col[j].getType() == CellType.LABEL) {
                        toAdd = col[j].getContents().trim();
                        break;
                    }
                labelsList.add(toAdd);
            }

        }
        // fill first column
        Cell[] firstColCells = s.getColumn(0);
        if (firstAsDateCol && firstColList != null)
            for (int j = lowestIndex; j <= highestIndex; j++) {
                if (firstColCells[j].getType() == CellType.DATE)
                    firstColList.add(((DateCell) firstColCells[j]).getDate());
                else
                    firstColList.add(firstColCells[j].getContents());
            }

        return data;
    }

    /**
     * Gets the highest row index in sheet s that contains
     * numerical data. This includes formulas.
     * 
     * @param s
     * @return index, -1 if no numerical data
     * @throws IllegalArgumentException
     *             if (s == null)
     */
    private static int getHighestIndexWithNumber(Sheet s) {
        if (s == null)
            throw new IllegalArgumentException("Sheet was null.");
        // Now get the highest index in the sheet with a Number.
        int highestIndex = -1;
        for (int i = 0; i < s.getColumns(); i++) {
            Cell[] col = s.getColumn(i);
            for (int j = col.length - 1; j >= 0; j--) {
                if (col[j].getType() == CellType.NUMBER
                        || col[j].getType() == CellType.NUMBER_FORMULA) {
                    if (highestIndex < j)
                        highestIndex = j;
                    break;
                }
            }
        }
        return highestIndex;
    }

    /**
     * Gets the lowest row index in sheet s that contains
     * numerical data. This includes formulas.
     * 
     * @param s
     * @return index, -1 if no numerical data
     * @throws IllegalArgumentException
     *             if (s == null)
     */
    private static int getLowestIndexWithNumber(Sheet s) {
        if (s == null)
            throw new IllegalArgumentException("Sheet was null.");
        // could be made more efficient by giving highestNumber as
        // argument, but usually not relevant
        int lowestIndex = s.getRows();
        for (int i = 0; i < s.getColumns(); i++) {
            Cell[] col = s.getColumn(i);
            for (int j = 0; j < col.length; j++) {
                if (col[j].getType() == CellType.NUMBER
                        || col[j].getType() == CellType.NUMBER_FORMULA) {
                    if (j < lowestIndex)
                        lowestIndex = j;
                    break;
                }
            }
        }
        return lowestIndex;
    }
}

 
 


        
        

                    




        
      

      
Ads help maintain this website.