edu.odu.cs.sheetManip

Class Spreadsheet

java.lang.Object

edu.odu.cs.sheetManip.Spreadsheet

public class Spreadsheet
extends java.lang.Object

A grading spreadsheet/workbook from which individual sheets can be extracted as CSV or have date loaded from CSV.

Constructor Summary

Constructors

Constructor and Description
Spreadsheet(java.io.File excelFile) Create a grade spreadsheet object mapping onto an existing Excel spreadsheet file (xls or xslx format).

Method Summary

Modifier and Type	Method and Description
void	breakOutByRow(java.lang.String sheetName, java.io.File outputDir, java.lang.String studentNameColumn, java.lang.String totalsColumn) Scans the given spreadsheet.
java.util.List<java.lang.String[]>	evaluateSheet(java.lang.String sheetName, boolean skipInvalidDataRows) Fetch the contents (values only) of a sheet as a list (by rows) of arrays (by column) of strings.
java.lang.String	getCellValue(java.lang.String sheetName, int rowNum, int column) Retrieve the value of a spreadsheet cell, expressed as a string.
void	loadCSV(java.io.File csvFile, java.lang.String sheetName) Replace the contents of a sheet by the contents of a CSV file, updating calculations elsewhere in the spreadsheet afterwards.
void	storeCSV(java.io.File csvFile, java.lang.String sheetName, boolean skipInvalidDataRows) Write the contents (values only) of a sheet by into a CSV file.

Methods inherited from class java.lang.Object

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

Constructor Detail

Spreadsheet

public Spreadsheet(java.io.File excelFile)
            throws org.apache.poi.EncryptedDocumentException,
                   org.apache.poi.openxml4j.exceptions.InvalidFormatException,
                   java.io.IOException

Create a grade spreadsheet object mapping onto an existing Excel spreadsheet file (xls or xslx format).

Parameters:

excelFile - location of an Excel spreadsheet. Creates a new spreadsheet if no such file exists.

Throws:

java.io.IOException - if file cannot be read

org.apache.poi.openxml4j.exceptions.InvalidFormatException - if file is a not a spreadsheet

org.apache.poi.EncryptedDocumentException - if file is encrypted?

Method Detail

getCellValue

public java.lang.String getCellValue(java.lang.String sheetName,
                                     int rowNum,
                                     int column)

Retrieve the value of a spreadsheet cell, expressed as a string.

Parameters:

sheetName - name of the sheet from which to retrieve

rowNum - index of the row from which to retrieve

column - index of the column from which to retrieve

Returns:

formula string in that cell, or null if cell does not exist

loadCSV

public void loadCSV(java.io.File csvFile,
                    java.lang.String sheetName)
             throws org.apache.poi.EncryptedDocumentException,
                    org.apache.poi.openxml4j.exceptions.InvalidFormatException,
                    java.io.IOException

Replace the contents of a sheet by the contents of a CSV file, updating calculations elsewhere in the spreadsheet afterwards.

Parameters:

csvFile - a CSV file with one sheet's worth of spreadsheet data.

sheetName - the name of the sheet into which to place that data. Normally this sheet should already exist (other wise there could be no calculations upon it,

Throws:

java.io.IOException

org.apache.poi.openxml4j.exceptions.InvalidFormatException

org.apache.poi.EncryptedDocumentException

storeCSV

public void storeCSV(java.io.File csvFile,
                     java.lang.String sheetName,
                     boolean skipInvalidDataRows)
              throws org.apache.poi.EncryptedDocumentException,
                     org.apache.poi.openxml4j.exceptions.InvalidFormatException,
                     java.io.IOException

Write the contents (values only) of a sheet by into a CSV file.

Parameters:

csvFile - location of the desired CSV file.

sheetName - the name of the sheet from which to obtain the data.

skipInvalidDataRows - if true, omit any row containing a non-empty value that is not a valid number or string.

Throws:

java.io.IOException

org.apache.poi.openxml4j.exceptions.InvalidFormatException

org.apache.poi.EncryptedDocumentException

evaluateSheet

public java.util.List<java.lang.String[]> evaluateSheet(java.lang.String sheetName,
                                                        boolean skipInvalidDataRows)
                                                 throws org.apache.poi.EncryptedDocumentException,
                                                        org.apache.poi.openxml4j.exceptions.InvalidFormatException,
                                                        java.io.IOException

Fetch the contents (values only) of a sheet as a list (by rows) of arrays (by column) of strings.

Parameters:

sheetName - the name of the sheet from which to obtain the data.

skipInvalidDataRows - if true, omit any row containing a non-empty value that is not a valid number or string.

Returns:

list of rows

Throws:

java.io.IOException

org.apache.poi.openxml4j.exceptions.InvalidFormatException

org.apache.poi.EncryptedDocumentException

breakOutByRow

public void breakOutByRow(java.lang.String sheetName,
                          java.io.File outputDir,
                          java.lang.String studentNameColumn,
                          java.lang.String totalsColumn)
                   throws org.apache.poi.EncryptedDocumentException,
                          org.apache.poi.openxml4j.exceptions.InvalidFormatException,
                          java.io.IOException

Scans the given spreadsheet. The column identified as studentNameColumn is presumed to contain names/identifiers for distinct students. For each such name N encountered, a new spreadsheet outputDir/N.xls is created containing 1) all rows of this sheet that have an empty cell in that identifier column, 2) all rows of this sheet having N in that column, and 3) a new row with "Total:" in column A and the last entry from the totalColumn in a row having N in the identifier column.

Parameters:

sheetName - which sheet of this spreadsheet to process

outputDir - directory to contain the newly generated spreadsheets

studentNameColumn - column containing student identifiers

totalsColumn - column containing entry for the Total: row

Throws:

java.io.IOException

org.apache.poi.openxml4j.exceptions.InvalidFormatException

org.apache.poi.EncryptedDocumentException