com.psec.excel

Class ReadExcelFile

java.lang.Object

com.psec.excel.ReadExcelFile

public class ReadExcelFile
extends java.lang.Object

The ReadExcelFile class opens an existing Workbook with one or more Sheets.

Methods are provided that can return selected rows of data from each sheet. The ReadExcelRecord class is used to provide selection capabilities as to what rows are to be returned and what content of each row is to be returned.

This selection capability is used to create a regression test facility for the WriteExcel set of methods.

The ReadClassFile class is also used to point to a sheet that can be cloned using the method.

Example

This reads the file demo-excel-inp.xlsx in the demo package.

  public static class OpenBalRec extends ReadExcelRecord {
    static Matcher oM = Pattern.compile("^[ALS][0-9]{3}$").matcher("");
    public String[] getColMap() {
      return "3=sLab;2=sName;4=dVal".split("/");
    }
    public String sLab;
    public String sName;
    public double dVal;

    @Override
    public boolean canAccept() throws Exception {
      if (this.sLab == null) return false;
      if (oM.reset(this.sLab).find()) return true;
      return false;
    }
  }

  private void demoReader() throws Exception {
    oREF = new ReadExcelFile();
    oREF.openFile(oOpt.sInp);
    ReadExcelRecord[] oRows = oREF.readExcelSmart("sample-bal-template",OpenBalRec.class,1,true);
    for(ReadExcelRecord oRER:oRows) {
      OpenBalRec oOBR = (OpenBalRec)oRER;
      log(String.format("%s %10.2f %s",oOBR.sLab,oOBR.dVal,oOBR.sName));
    }
    oREF.closeFile();
  }
  

See Also:

WriteExcel description, ReadExcelRecord

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
static class	ReadExcelFile.ColMap The ColMap class describes a single column used internally as part of the readExcel method processing.

Constructor Summary

Constructors

Constructor and Description
ReadExcelFile()

Method Summary

Modifier and Type	Method and Description
void	attachWorkbook(Workbook oWB) Attaches the Workbook to a ReadExcelFile instance so all its methods can be used.
void	closeFile() Closes the Workbook.
java.lang.String	getColAsStr(Row oRow, ReadExcelFile.ColMap oCM) Return a stringized version of the Cell at the specified location.
Cell	getColCell(Row oRow, ReadExcelFile.ColMap oCM) Return Cell at the specified location.
java.lang.String	getPrintArea(java.lang.String sSheet) Returns the String version of a Sheet print area.
Sheet	getSheet(java.lang.String sSheet) Returns the named Sheet.
Workbook	getWorkbook() Gets the Workbook being accessed.
void	openFile(java.lang.String sFile) Opens the Workbook.
ReadExcelRecord[]	readExcel(java.lang.String sSheet, java.lang.Class<ReadExcelRecord> oRecCls) Iterate across a sheet and selectively create a set of readExcelRecord instances, one for each row selected.
ReadExcelRecord[]	readExcelSmart(java.lang.String sSheet, java.lang.Class<?> oRecCls, int nStartRow, boolean bSkipBlank) Iterate across a sheet and selectively create a set of readExcelRecord instances, one for each row selected.

Methods inherited from class java.lang.Object

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

Constructor Detail

ReadExcelFile

public ReadExcelFile()

Method Detail

openFile

public void openFile(java.lang.String sFile)
              throws java.lang.Exception

Opens the Workbook.

Parameters:

sFile - the name of the file.

Throws:

java.lang.Exception - file does not exist or is not a valid Workbook

attachWorkbook

public void attachWorkbook(Workbook oWB)

Attaches the Workbook to a ReadExcelFile instance so all its methods can be used. This method is used instead of the openFile method when a Workbook is known (such as in an ongoing WriteExcel operation.

closeFile

public void closeFile()
               throws java.lang.Exception

Closes the Workbook. It is present for comlpeteness but does nothing apart from releasing memory.

It is ignored if the Workbook is part of an active WriteExcel operation attached to this instance via attachWorkbook method.

Throws:

java.lang.Exception - An internal operation throws an Exception.

getSheet

public Sheet getSheet(java.lang.String sSheet)

Returns the named Sheet. It allows raw access to to Workbook objects.

Returns:

org.apache.poi.ss.usermodel.Sheet or null if the named sheet does not exist.

getPrintArea

public java.lang.String getPrintArea(java.lang.String sSheet)
                              throws java.lang.Exception

Returns the String version of a Sheet print area. It is obtained by calling Workbook.getPrintArea for the specified sheet.

Returns:

The internal string representation of the print area.

Throws:

java.lang.Exception - The sheet is not found

readExcel

public ReadExcelRecord[] readExcel(java.lang.String sSheet,
                                   java.lang.Class<ReadExcelRecord> oRecCls)
                            throws java.lang.Exception

Iterate across a sheet and selectively create a set of readExcelRecord instances, one for each row selected.

It is implemented by calling readExcelSmart with a value of 0 for nStartRow and the value of bSkipBlank set to false.

Parameters:

sSheet - the sheet name to process

oRecCls - The class type used to instantiate each row readExcelRecord instance.

Returns:

The array of ReadExcelRecord[] objects.

Throws:

java.lang.Exception - The sheet is not found

readExcelSmart

public ReadExcelRecord[] readExcelSmart(java.lang.String sSheet,
                                        java.lang.Class<?> oRecCls,
                                        int nStartRow,
                                        boolean bSkipBlank)
                                 throws java.lang.Exception

Iterate across a sheet and selectively create a set of readExcelRecord instances, one for each row selected.

The nStartRow parameter is used to indicate the first row to start processing at. Its primary use is to skip headers frequently found at the top of sheets.

The bSkipBlank will cause rows to be skipped in which the first field of the ColMap object has no Cell object.

Parameters:

sSheet - the sheet name to process

oRecCls - The class type used to instantiate each row readExcelRecord instance.

nStartRow - The first row to process relative to 0; This allows the

bSkipBlank - Rows where the first field is null are skipped.

Returns:

The array of ReadExcelRecord[] objects.

Throws:

java.lang.Exception - The sheet is not found

getColAsStr

public java.lang.String getColAsStr(Row oRow,
                                    ReadExcelFile.ColMap oCM)
                             throws java.lang.Exception

Return a stringized version of the Cell at the specified location. The Cell represented by the ReadExcelFile.ColMap parameter within the oRow parameter is the one processed.

Parameters:

oRow - The row to process which can be obtained via the getSheet method.

oCM - The column to select.

Returns:

a stringized version of the selected Cell.

Throws:

java.lang.Exception

getColCell

public Cell getColCell(Row oRow,
                       ReadExcelFile.ColMap oCM)
                throws java.lang.Exception

Return Cell at the specified location. The Cell represented by the ReadExcelFile.ColMap parameter within the oRow parameter is the returned.

Parameters:

oRow - The row to process which can be obtained via the getSheet method.

oCM - The column to select.

Returns:

The selected Cell or null.

Throws:

java.lang.Exception

getWorkbook

public Workbook getWorkbook()
                     throws java.lang.Exception

Gets the Workbook being accessed.

This allows inspection or modifications to be made to the Workbook using the POI library directly.

Returns:

Workbook.

Throws:

java.lang.Exception