com.extentech.ExtenXLS
Class ExcelTools

java.lang.Object
  com.extentech.ExtenXLS.ExcelTools

All Implemented Interfaces:

Serializable

public class ExcelTools
extends Object
implements Serializable

ExtenXLS helper methods.
Contains helpful methods to ease use of the ExtenXLS toolkit.

Since:

1.3

Version:

4.1

Author:

John McMahon -- Copyright ©2011Extentech Inc.

See Also:

ByteTools, Serialized Form

Field Summary

static String[]

ALPHASDELETE

Constructor Summary

ExcelTools()

Method Summary

static void	benchmark(String info, Object perfobj) tracks minimal info container for counters -> start time, last time, start mem, last mem
static String	formatLocation(int[] s) Takes an int array representing a row and column and formats it as a cell address.
static String	formatLocation(int[] s, boolean bRelRow, boolean bRelCol) Takes an int array representing a row and column and formats it as a cell address, taking into account relative or absolute refs The index is zero-based.
static String	formatNumericNotation(String num, int notationType) Formats a string representation of a numeric value as a string in the specified notation:
static String	formatRange(int[] s) Takes an array of four shorts and formats it as a cell range.
static String	formatRangeRowCol(int[] s) format a range as a string, range in format of [r][c][r1][c1]
static String	formatRangeRowCol(int[] s, boolean[] bRelAddresses) format a range as a string, range in format of [r][c][r1][c1] including relative address state
static String	getAlphaVal(int i) get the Excel-style Column alphabetical representation of an integer (0-based).
static CellHandle[]	getCellHandlesFromSheet(String strRange, WorkSheetHandle sheet) Return an array of cell handles specified from the string passed in.
static int	getIntVal(String c) get the int value of the Excel-style Column alpha representation.
static String	getLogDate() get recordy byte def as a String public static String getRecordByteDef(XLSRecord rec){ byte[] b = rec.read(); StringBuffer sb = new StringBuffer("byte[] rbytes = {"); for(int t = 0;t
static String	getNumberAsString(double fpnum) Formats a double in the standard ExtenXLS (General) format.
static Object	getObject(Object in) Takes an input Object and attempts to convert to numeric Objects of the highest precision possible.
static float	getPixels(float twips) convert twips to pixels In addition to a calculated size unit derived from the average size of the default characters 0-9, Excel uses the 'twips' measurement which is defined as: 1 twip = 1/20 point or 20 twips = 1 point 1 twip = 1/567 centimeter or 567 twips = 1 centimeter 1 twip = 1/1440 inch or 1440 twips = 1 inch 1 pixel = 0.75 points 1 pixel * 1.3333 = 1 point 1 twip * 20 = 1 point
static int[]	getRangeCoords(String range) return the first and last coords of a range in int form + the number of cells in the range range is in the format of Sheet
static int[]	getRangeRowCol(String range) Parses an Excel cell range and returns the addresses as an int array.
static int[]	getRowColFromString(String address) Parses an Excel cell address into row and column integers.
static float	getTwips(float pixels) convert pixels to twips In addition to a calculated size unit derived from the average size of the default characters 0-9, Excel uses the 'twips' measurement which is defined as: 1 pixel = 0.75 points 1 pixel * 1.3333 = 1 point 1 twip * 20 = 1 point
static boolean	intersects(String rng, int[] rc) returns true if range intersects with range2
static boolean	isAfterRange(int[] rc, int[] rng) returns true if address is before the range coordinates defined by rc
static boolean	isBeforeRange(int[] rc, int[] rng) returns true if address is before the range coordinates defined by rc
static boolean	isInRange(String rng, int rowFirst, int rowLast, int colFirst, int colLast) A FAIL FAST implementation for finding whether a cell string address falls within a set of row/col range coordinates.
static String[]	stripSheetNameFromRange(String address) Strip sheet name(s) from range string can be Sheet1!AB:Sheet!BC or Sheet!AB:BC or AB:BC or Sheet1:Sheet2!A1:A2
static int[]	transformStringToIntVals(String trans) Transforms a string to an array of ints for evaluation purposes.

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

ALPHASDELETE

public static final String[] ALPHASDELETE

Constructor Detail

ExcelTools

public ExcelTools()

Method Detail

getNumberAsString

public static String getNumberAsString(double fpnum)

Formats a double in the standard ExtenXLS (General) format. Up to 99999999999 is expressed in standard notation. Above that is formatted in scientific notation In addition, Excel precision of 9 digits is maintained returns a number formatted in Excel's General format, (assuming a wide enough column width - see below) example: formatNumericNotation(1234567890123) returns "1.23457E+12" Information on NOTATION_STANDARD_EXCEL (i.e. Excel's General Format): // Excel will show as many decimal places that the text item has room for, it won't use a thousands separator, and if the // number can't fit, Excel uses a scientific number format. // RULES: // 1- Assuming the column is wide enough numbers will only be displayed in the scientific format when they contain more than 10 digits. // 2- If you enter a number into a cell and thre is not enough room to display all the digits // then the number will either be displayed in scientific format or will not be displayed at all, meaning that ##### will appear. // The exact precision of the scientific format will depend on the width of the actual cell.

Parameters:

fpnum -

Returns:

String formatted number

isInRange

public static boolean isInRange(String rng,
                                int rowFirst,
                                int rowLast,
                                int colFirst,
                                int colLast)

A FAIL FAST implementation for finding whether a cell string address falls within a set of row/col range coordinates. Sep 21, 2010

Parameters:

rng - the range you want to test

rowFirst - in the target range

rowLast - in the target range

colFirst - in the target range

colLast - in the target range

Returns:

intersects

public static boolean intersects(String rng,
                                 int[] rc)

returns true if range intersects with range2

Parameters:

rng -

rc -

Returns:

isBeforeRange

public static boolean isBeforeRange(int[] rc,
                                    int[] rng)

returns true if address is before the range coordinates defined by rc

Parameters:

rc - row col of address

rng - int[] coordinates as: row0, col0, row1, col1

Returns:

true if address is before the range coordinates

isAfterRange

public static boolean isAfterRange(int[] rc,
                                   int[] rng)

returns true if address is before the range coordinates defined by rc

Parameters:

rc - row col of address

rng - int[] coordinates as: row0, col0, row1, col1

Returns:

true if address is before the range coordinates

getObject

public static Object getObject(Object in)

Takes an input Object and attempts to convert to numeric Objects of the highest precision possible. This method is useful for avoiding the Excel warnings "Number Stored As Text" when storing string data that contains numbers. NOTE: this method is useful for ensuring that Formula references contain true numeric values as not all String numbers are properly interpreted in Formula engines, and can silently fail. For this reason, always use numeric, non-string values to calculated cells.

Parameters:

input -

Returns:

getPixels

public static final float getPixels(float twips)

convert twips to pixels In addition to a calculated size unit derived from the average size of the default characters 0-9, Excel uses the 'twips' measurement which is defined as: 1 twip = 1/20 point or 20 twips = 1 point 1 twip = 1/567 centimeter or 567 twips = 1 centimeter 1 twip = 1/1440 inch or 1440 twips = 1 inch 1 pixel = 0.75 points 1 pixel * 1.3333 = 1 point 1 twip * 20 = 1 point

Parameters:

pixels -

Returns:

twips

getTwips

public static final float getTwips(float pixels)

convert pixels to twips In addition to a calculated size unit derived from the average size of the default characters 0-9, Excel uses the 'twips' measurement which is defined as: 1 pixel = 0.75 points 1 pixel * 1.3333 = 1 point 1 twip * 20 = 1 point

Parameters:

pixels -

Returns:

twips

getLogDate

public static String getLogDate()

get recordy byte def as a String public static String getRecordByteDef(XLSRecord rec){ byte[] b = rec.read(); StringBuffer sb = new StringBuffer("byte[] rbytes = {"); for(int t = 0;t

benchmark

public static void benchmark(String info,
                             Object perfobj)

tracks minimal info container for counters -> start time, last time, start mem, last mem

Parameters:

info -

perfobj -

getAlphaVal

public static String getAlphaVal(int i)

get the Excel-style Column alphabetical representation of an integer (0-based). for example: 0 = A 26= AA 701= ZZ 702= AAA 16383= XFD (max)

getIntVal

public static int getIntVal(String c)

get the int value of the Excel-style Column alpha representation.

Parameters:

String - column name

Returns:

int the 0-based column number

getRowColFromString

public static int[] getRowColFromString(String address)

Parses an Excel cell address into row and column integers.

Parameters:

address - the address to parse, either A1 or R1C1

Returns:

int[2]: [0] row index, [1] column index

Throws:

IllegalArgumentException - if the argument is not a valid address

getRangeRowCol

public static int[] getRangeRowCol(String range)

Parses an Excel cell range and returns the addresses as an int array. The range may not be qualified with sheet names. Strip them with stripSheetNameFromRange(java.lang.String) before calling this method. If the argument is a single cell address it will be returned for both bounds.

Parameters:

range - the range to parse

Returns:

int[4]: [0] first row, [1] first column, [2] second row, [3] second column

Throws:

IllegalArgumentException - if the addresses are invalid

formatLocation

public static String formatLocation(int[] s)

Takes an int array representing a row and column and formats it as a cell address. The index is zero-based. [0][0] is "A1" [1][1] is "B2" [2][2] is "C3"

Parameters:

int[] - the numeric range to convert

Returns:

String the string representation of the range

formatLocation

public static String formatLocation(int[] s,
                                    boolean bRelRow,
                                    boolean bRelCol)

Takes an int array representing a row and column and formats it as a cell address, taking into account relative or absolute refs The index is zero-based. [0][0] is "A1", $A1, A$1 or $A$1 depending upon bRelRow or bRelCol [1][1] is "B2", $B1, B$1 or B$1 depending upon bRelRow or bRelCol [2][2] is "C3", $C1, C$1 or $C$1 depending upon bRelRow or bRelCol

Parameters:

int[] - the numeric range to convert

bRelRow - if true, no "$"s are added, relative row reference

bRelCol - if true, no "$"s are added, relative col reference

Returns:

String the string representation of the range

formatRange

public static String formatRange(int[] s)

Takes an array of four shorts and formats it as a cell range. IE [0][3][1][4] would be "A2:B3"

Parameters:

int[] - the numeric range to convert

Returns:

String the string representation of the range

formatRangeRowCol

public static String formatRangeRowCol(int[] s)

format a range as a string, range in format of [r][c][r1][c1]

Parameters:

s -

Returns:

String representation of the integers as a range, ie A1:B4

formatRangeRowCol

public static String formatRangeRowCol(int[] s,
                                       boolean[] bRelAddresses)

format a range as a string, range in format of [r][c][r1][c1] including relative address state

Parameters:

s -

bRelAddresses - contains relative row and col state for each rcr1c1

Returns:

String representation of the integers as a range, ie A1:B4

transformStringToIntVals

public static int[] transformStringToIntVals(String trans)

Transforms a string to an array of ints for evaluation purposes. For example, acdc == [0][2][3][2]

formatNumericNotation

public static String formatNumericNotation(String num,
                                           int notationType)

Formats a string representation of a numeric value as a string in the specified notation:

Parameters:

int -
NOTATION_STANDARD = 0,
NOTATION_SCIENTIFIC = 1,
NOTATION_SCIENTIFIC_EXCEL = 2,
EXTENXLS_NOTATION = 3 example: formatNumericNotation(1.23456E5, 0) returns a "123456" example: formatNumericNotation(123456, 1) returns "1.23456E5" example: formatNumericNotation(123456, 2) returns "1.23456E+5" example: formatNumericNotation(123456, 3) returns "1.23456E+5"

getCellHandlesFromSheet

public static CellHandle[] getCellHandlesFromSheet(String strRange,
                                                   WorkSheetHandle sheet)

Return an array of cell handles specified from the string passed in. Note that a CellHandle cannot exist for an empty cell, so the cells retrieved in this manner will be blank cells, not empty cells.

Parameters:

cellstr - - a comma delimited String representing cells and cell ranges, example "A1,A5,A6,B1:B5" would return cells A1, A5, A6, B1, B2, B3, B4, B5

sheet - the worksheet containing the cells.

Returns:

CellHandle[]

stripSheetNameFromRange

public static String[] stripSheetNameFromRange(String address)

Strip sheet name(s) from range string can be Sheet1!AB:Sheet!BC or Sheet!AB:BC or AB:BC or Sheet1:Sheet2!A1:A2

Parameters:

address - or range String

Returns:

1st sheetname

getRangeCoords

public static int[] getRangeCoords(String range)

return the first and last coords of a range in int form + the number of cells in the range range is in the format of Sheet