Package jslx.excel

Class ExcelUtil

java.lang.Object

jslx.excel.ExcelUtil

public class ExcelUtil
extends java.lang.Object

A utility class for reading and writing to Excel from various formats.

Field Summary

Fields

Modifier and Type	Field	Description
static int	DEFAULT_MAX_CHAR_IN_CELL
static org.slf4j.Logger	LOG

Constructor Summary

Constructors

Constructor	Description
ExcelUtil()

Method Summary

Modifier and Type	Method	Description
static org.apache.poi.ss.usermodel.Sheet	createSheet(org.apache.poi.ss.usermodel.Workbook workbook, java.lang.String sheetName)	Creates a sheet within the workbook with the name.
static void	fillWorkbookFromDatabase(DatabaseIfc db, java.util.List<java.lang.String> tableNames, org.apache.poi.ss.usermodel.Workbook workbook)	Fills the supplied workbook from the database with one sheet for every table.
static int	getColumnSize(org.apache.poi.ss.usermodel.Sheet sheet, int columnIndex)	Starts as the last row number of the sheet and looks up in the column to find the first non-null cell
static int	getNextEnumConstant()	Should be used by subclasses to get the next constant so that unique constants can be used
static int	getNumberColumnsForCSVHeader(org.apache.poi.ss.usermodel.Sheet sheet)	Assumes that the first row is a header for a CSV like file and returns the number of columns (1 for each header)
static boolean	isCellEmpty(org.apache.poi.ss.usermodel.Cell cell)
static tech.tablesaw.api.Table	makeTable(java.nio.file.Path pathToWorkbook, java.lang.String sheetName)	Makes a Tablesaw table from a sheet within an Excel workbook.
static tech.tablesaw.api.Table	makeTable(java.nio.file.Path pathToWorkbook, java.lang.String sheetName, java.lang.String tableName)	Makes a Tablesaw table from a sheet within an Excel workbook.
static org.apache.poi.xssf.usermodel.XSSFWorkbook	openExistingXSSFWorkbookReadOnly(java.nio.file.Path pathToWorkbook)	IO exceptions are squelched in this method.
static java.lang.Object	readCellAsObject(org.apache.poi.ss.usermodel.Cell cell)	Reads the Excel cell and translates it into a Java object
static java.lang.String	readCellAsString(org.apache.poi.ss.usermodel.Cell cell)	Reads the Excel cell and translates it into a String
static java.lang.Object[]	readRowAsObjectArray(org.apache.poi.ss.usermodel.Row row, int numCol)
static java.lang.Object[]	readRowAsObjectArray(org.apache.poi.ss.usermodel.Row row, org.jooq.Field<?>[] fields)
static java.util.List<java.lang.Object>	readRowAsObjectList(org.apache.poi.ss.usermodel.Row row, int numCol)	Read a row assuming a fixed number of columns.
static java.util.List<java.lang.Object>	readRowAsObjectList(org.apache.poi.ss.usermodel.Row row, org.jooq.Field<?>[] fields)	Read a row assuming a fixed number of columns.
static java.lang.String[]	readRowAsStringArray(org.apache.poi.ss.usermodel.Row row, int numCol)	Read a row assuming a fixed number of columns.
static java.lang.String[]	readRowAsStringArray(org.apache.poi.ss.usermodel.Row row, int numCol, int maxChar)	Read a row assuming a fixed number of columns.
static java.util.List<java.lang.String>	readRowAsStringList(org.apache.poi.ss.usermodel.Row row, int numCol)	Read a row assuming a fixed number of columns.
static java.util.List<java.lang.String>	readRowAsStringList(org.apache.poi.ss.usermodel.Row row, int numCol, int maxChar)	Read a row assuming a fixed number of columns.
static java.util.List<java.lang.Object[]>	readSheetAsListOfObjects(org.apache.poi.ss.usermodel.Sheet sheet, org.jooq.Field<?>[] fields, boolean skipFirstRow)
static java.util.List<java.util.List<java.lang.Object>>	readSheetAsObjects(org.apache.poi.ss.usermodel.Sheet sheet, org.jooq.Field<?>[] fields, boolean skipFirstRow)
static void	runWriteDBAsExcelWorkbook(DatabaseIfc db, java.util.List<java.lang.String> tableNames)	Runs writeDBAsExcelWorkbook() to write the supplied database to an Excel workbook with one sheet for every table, squelching all exceptions.
static void	runWriteDBAsExcelWorkbook(DatabaseIfc db, java.util.List<java.lang.String> tableNames, java.nio.file.Path pathToWorkbook)	Runs writeDBAsExcelWorkbook() to write the supplied database to an Excel workbook with one sheet for every table, squelching all exceptions
static void	runWriteWorkbookToDatabase(java.nio.file.Path pathToWorkbook, boolean skipFirstRow, DatabaseIfc db, java.util.List<java.lang.String> tableNames)	Runs writeWorkbookToDatabase() squelching all exceptions.
static void	runWriteWorkbookToDatabase(java.nio.file.Path pathToWorkbook, DatabaseIfc db, java.util.List<java.lang.String> tableNames)	Runs writeWorkbookToDatabase() squelching all exceptions.
static void	writeCell(org.apache.poi.ss.usermodel.Cell cell, java.lang.Object object)	Writes the Java Object to the Excel cell
static void	writeDBAsExcelWorkbook(DatabaseIfc db, java.util.List<java.lang.String> tableNames)	Writes the supplied database to an Excel workbook with one sheet for every table.
static void	writeDBAsExcelWorkbook(DatabaseIfc db, java.util.List<java.lang.String> tableNames, java.nio.file.Path pathToWorkbook)	Writes the supplied database to an Excel workbook with one sheet for every table.
static void	writeRecordToSheet(org.jooq.Record record, org.apache.poi.ss.usermodel.Row row)	Writes a single row from the ResultSet to a row in an Excel Sheet
static void	writeResultRecordsAsExcelSheet(org.jooq.Result<org.jooq.Record> records, org.apache.poi.ss.usermodel.Sheet sheet)	Writes the results from a query to the Excel sheet.
static void	writeResultRecordsToExcelWorkbook(java.nio.file.Path pathToWb, java.lang.String sheetName, org.jooq.Result<org.jooq.Record> records)	If the workbook exists the sheet containing the results is added to the workbook.
static void	writeSheetToCSV(org.apache.poi.ss.usermodel.Sheet sheet)	Treats the columns as fields in a csv file, writes each row as a separate csv row in the resulting csv file.
static void	writeSheetToCSV(org.apache.poi.ss.usermodel.Sheet sheet, boolean skipFirstRow, java.nio.file.Path pathToCSV, int numCol)	Treats the columns as fields in a csv file, writes each row as a separate csv row in the resulting csv file.
static void	writeSheetToCSV(org.apache.poi.ss.usermodel.Sheet sheet, boolean skipFirstRow, java.nio.file.Path pathToCSV, int numCol, int maxChar)	Treats the columns as fields in a csv file, writes each row as a separate csv row in the resulting csv file
static void	writeSheetToCSV(org.apache.poi.ss.usermodel.Sheet sheet, int numCol)	Treats the columns as fields in a csv file, writes each row as a separate csv row in the resulting csv file.
static void	writeSheetToCSV(org.apache.poi.ss.usermodel.Sheet sheet, java.nio.file.Path pathToCSVFile)	Treats the columns as fields in a csv file, writes each row as a separate csv row in the resulting csv file.
static void	writeSheetToCSV(org.apache.poi.ss.usermodel.Sheet sheet, java.nio.file.Path pathToCSV, int numCol)	Treats the columns as fields in a csv file, writes each row as a separate csv row in the resulting csv file.
static void	writeSheetToTable(org.apache.poi.ss.usermodel.Sheet sheet, boolean skipFirstRow, java.lang.String tableName, DatabaseIfc db)	This method assumes that the tableName exists in the database or that a table with the same name as the sheet exists within the database and that the sheet has the appropriate structure to be placed within the table in the database.
static void	writeSheetToTable(org.apache.poi.ss.usermodel.Sheet sheet, java.lang.String tableName, DatabaseIfc db)	Writes the sheet to the named table.
static void	writeSheetToTable(org.apache.poi.ss.usermodel.Sheet sheet, DatabaseIfc db)	Writes the sheet to the named table.
static void	writeTableAsExcelSheet(DatabaseIfc db, java.lang.String tableName, org.apache.poi.ss.usermodel.Sheet sheet)	Writes a table from the database to the Excel sheet.
static void	writeTableAsExcelSheet(tech.tablesaw.api.Table table, org.apache.poi.ss.usermodel.Sheet sheet)	Writes the contents from a Tablesaw Table to the Excel sheet.
static void	writeTableToExcelWorkbook(tech.tablesaw.api.Table table, java.lang.String wbName)	Writes the table to a workbook.
static void	writeTableToExcelWorkbook(tech.tablesaw.api.Table table, java.nio.file.Path pathToWb)	Writes the table to a new workbook.
static void	writeWorkbookToDatabase(java.nio.file.Path pathToWorkbook, boolean skipFirstRow, DatabaseIfc db, java.util.List<java.lang.String> tableNames)	Opens the workbook for reading only and writes the sheets of the workbook into database tables.
static void	writeWorkbookToDatabase(java.nio.file.Path pathToWorkbook, DatabaseIfc db, java.util.List<java.lang.String> tableNames)	Writes the sheets of the workbook into database tables.
static void	writeWorkbookToDatabase(org.apache.poi.ss.usermodel.Workbook wb, boolean skipFirstRow, DatabaseIfc db, java.util.List<java.lang.String> tableNames)	Writes the sheets of the workbook into database tables.
static void	writeWorkbookToDatabase(org.apache.poi.ss.usermodel.Workbook wb, DatabaseIfc db, java.util.List<java.lang.String> tableNames)	Writes the sheets of the workbook into database tables.

Methods inherited from class java.lang.Object

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

Field Detail

LOG

public static final org.slf4j.Logger LOG

DEFAULT_MAX_CHAR_IN_CELL

public static final int DEFAULT_MAX_CHAR_IN_CELL

See Also:

Constant Field Values

Constructor Detail

ExcelUtil

public ExcelUtil()

Method Detail

runWriteDBAsExcelWorkbook

public static void runWriteDBAsExcelWorkbook(DatabaseIfc db,
                                             java.util.List<java.lang.String> tableNames)

Runs writeDBAsExcelWorkbook() to write the supplied database to an Excel workbook with one sheet for every table, squelching all exceptions. The workbook will have the same name as the database

Parameters:

db - the database to read data from

tableNames - the list of names of tables in the database to write to Excel, must not be null

runWriteDBAsExcelWorkbook

public static void runWriteDBAsExcelWorkbook(DatabaseIfc db,
                                             java.util.List<java.lang.String> tableNames,
                                             java.nio.file.Path pathToWorkbook)

Runs writeDBAsExcelWorkbook() to write the supplied database to an Excel workbook with one sheet for every table, squelching all exceptions

Parameters:

db - the database to read data from

tableNames - the list of names of tables in the database to write to Excel, must not be null

pathToWorkbook - the name of the workbook that is to be made

runWriteWorkbookToDatabase

public static void runWriteWorkbookToDatabase(java.nio.file.Path pathToWorkbook,
                                              DatabaseIfc db,
                                              java.util.List<java.lang.String> tableNames)

Runs writeWorkbookToDatabase() squelching all exceptions. Read the workbook and writes it into the database. The first row of each sheet is skipped.

Parameters:

pathToWorkbook - the path to the workbook. Must be valid workbook with .xlsx extension

db - the database to write to

tableNames - the names of the sheets and tables in the order that needs to be written

runWriteWorkbookToDatabase

public static void runWriteWorkbookToDatabase(java.nio.file.Path pathToWorkbook,
                                              boolean skipFirstRow,
                                              DatabaseIfc db,
                                              java.util.List<java.lang.String> tableNames)

Runs writeWorkbookToDatabase() squelching all exceptions. Read the workbook and writes it into the database. The first row of each sheet is skipped.

Parameters:

pathToWorkbook - the path to the workbook. Must be valid workbook with .xlsx extension

skipFirstRow - if true the first row of each sheet is skipped

db - the database to write to

tableNames - the names of the sheets and tables in the order that needs to be written

writeDBAsExcelWorkbook

public static void writeDBAsExcelWorkbook(DatabaseIfc db,
                                          java.util.List<java.lang.String> tableNames)
                                   throws java.io.IOException

Writes the supplied database to an Excel workbook with one sheet for every table. This will produce an Excel file with the same name as the database in the current working directory.

Parameters:

db - the database to read data from

tableNames - the list of names of tables in the database to write to Excel, must not be null

Throws:

java.io.IOException - io exception

writeDBAsExcelWorkbook

public static void writeDBAsExcelWorkbook(DatabaseIfc db,
                                          java.util.List<java.lang.String> tableNames,
                                          java.nio.file.Path pathToWorkbook)
                                   throws java.io.IOException

Writes the supplied database to an Excel workbook with one sheet for every table. This will produce an Excel file with the supplied name in the current working directory. Each sheet of the workbook will have the field names as the first row in the sheet.

Parameters:

db - the database to read data from, must not be null

tableNames - the list of names of tables in the database to write to Excel, must not be null

pathToWorkbook - the name of the workbook that was made

Throws:

java.io.IOException - io exception

fillWorkbookFromDatabase

public static void fillWorkbookFromDatabase(DatabaseIfc db,
                                            java.util.List<java.lang.String> tableNames,
                                            org.apache.poi.ss.usermodel.Workbook workbook)

Fills the supplied workbook from the database with one sheet for every table. Each sheet of the workbook will have the field names as the first row in the sheet. If none of the tables are in the database then no sheets are written. The workbook is just filled. It is not written to a file. The workbook object can continue to be used for additional development before being written to a file.

Parameters:

db - the database to read data from, must not be null

tableNames - the list of names of tables in the database to write to the workbook, must not be null

workbook - the workbook to fill, must not be null

writeWorkbookToDatabase

public static void writeWorkbookToDatabase(java.nio.file.Path pathToWorkbook,
                                           DatabaseIfc db,
                                           java.util.List<java.lang.String> tableNames)
                                    throws java.io.IOException

Writes the sheets of the workbook into database tables. The list of names is the names of the sheets in the workbook and the names of the tables that need to be written. They are in the order that is required for entering data so that no integrity constraints are violated.

The first row of every sheet is skipped.

Parameters:

pathToWorkbook - the path to the workbook. Must be valid workbook with .xlsx extension

db - the database to write to

tableNames - the names of the sheets and tables in the order that needs to be written

Throws:

java.io.IOException - an io exception

writeWorkbookToDatabase

public static void writeWorkbookToDatabase(java.nio.file.Path pathToWorkbook,
                                           boolean skipFirstRow,
                                           DatabaseIfc db,
                                           java.util.List<java.lang.String> tableNames)
                                    throws java.io.IOException

Opens the workbook for reading only and writes the sheets of the workbook into database tables. The list of names is the names of the sheets in the workbook and the names of the tables that need to be written. They are in the order that is required for entering data so that no integrity constraints are violated. The underlying workbook is closed after the operation.

Parameters:

pathToWorkbook - the path to the workbook. Must be valid workbook with .xlsx extension

skipFirstRow - if true the first row of each sheet is skipped

db - the database to write to

tableNames - the names of the sheets and tables in the order that needs to be written

Throws:

java.io.IOException - an io exception

openExistingXSSFWorkbookReadOnly

public static org.apache.poi.xssf.usermodel.XSSFWorkbook openExistingXSSFWorkbookReadOnly(java.nio.file.Path pathToWorkbook)

IO exceptions are squelched in this method. If there is a problem, then null is returned. Opens an Apache POI XSSFWorkbook instance. The user is responsible for closing the workbook when done. Do not try to write to the returned workbook.

Parameters:

pathToWorkbook - the path to a valid Excel xlsx workbook

Returns:

an Apache POI XSSFWorkbook or null if there was a problem opening the workbook.

writeWorkbookToDatabase

public static void writeWorkbookToDatabase(org.apache.poi.ss.usermodel.Workbook wb,
                                           DatabaseIfc db,
                                           java.util.List<java.lang.String> tableNames)
                                    throws java.io.IOException

Writes the sheets of the workbook into database tables. The list of names is the names of the sheets in the workbook and the names of the tables that need to be written. They are in the order that is required for entering data so that no integrity constraints are violated.

The first row of every sheet is skipped.

Parameters:

wb - the workbook to copy from

db - the database to write to

tableNames - the names of the sheets and tables in the order that needs to be written

Throws:

java.io.IOException - an io exception

writeWorkbookToDatabase

public static void writeWorkbookToDatabase(org.apache.poi.ss.usermodel.Workbook wb,
                                           boolean skipFirstRow,
                                           DatabaseIfc db,
                                           java.util.List<java.lang.String> tableNames)
                                    throws java.io.IOException

Writes the sheets of the workbook into database tables. The list of names is the names of the sheets in the workbook and the names of the tables that need to be written. They are in the order that is required for entering data so that no integrity constraints are violated.

Parameters:

wb - the workbook to copy from

skipFirstRow - if true the first row of each sheet is skipped

db - the database to write to

tableNames - the names of the sheets and tables in the order that needs to be written

Throws:

java.io.IOException - an io exception

writeSheetToTable

public static void writeSheetToTable(org.apache.poi.ss.usermodel.Sheet sheet,
                                     DatabaseIfc db)
                              throws java.io.IOException

Writes the sheet to the named table. Automatically skips the first row of the sheet. Uses the name of the sheet as the name of the table. The table must exist in the database with that name.

Parameters:

sheet - the sheet to get the data from

db - the database containing the table

Throws:

java.io.IOException - an io exception

writeSheetToTable

public static void writeSheetToTable(org.apache.poi.ss.usermodel.Sheet sheet,
                                     java.lang.String tableName,
                                     DatabaseIfc db)
                              throws java.io.IOException

Writes the sheet to the named table. Automatically skips the first row of the sheet

Parameters:

sheet - the sheet to get the data from

tableName - the name of the table to write to

db - the database containing the table

Throws:

java.io.IOException - an io exception

writeSheetToTable

public static void writeSheetToTable(org.apache.poi.ss.usermodel.Sheet sheet,
                                     boolean skipFirstRow,
                                     java.lang.String tableName,
                                     DatabaseIfc db)
                              throws java.io.IOException

This method assumes that the tableName exists in the database or that a table with the same name as the sheet exists within the database and that the sheet has the appropriate structure to be placed within the table in the database. If the table does not exist in the database the method returns and logs a warning.

Parameters:

sheet - the sheet to get the data from, must null be null

skipFirstRow - true means skip the first row of the Excel sheet

tableName - the name of the table to write to, can be null, if so the sheet name is used

db - the database containing the table, must not be null

Throws:

java.io.IOException - an io exception

getNumberColumnsForCSVHeader

public static int getNumberColumnsForCSVHeader(org.apache.poi.ss.usermodel.Sheet sheet)

Assumes that the first row is a header for a CSV like file and returns the number of columns (1 for each header)

Parameters:

sheet - the sheet to write, must not be null

Returns:

the number of header columns

writeSheetToCSV

public static void writeSheetToCSV(org.apache.poi.ss.usermodel.Sheet sheet)
                            throws java.io.IOException

Treats the columns as fields in a csv file, writes each row as a separate csv row in the resulting csv file. Uses default maximum cell size. Does not skip the first row. Writes to a CSV file with the same name as the sheet in the current working directory. The number of columns is determined by assuming that the first row contains the CSV header. If the sheet has no columns, then an exception is thrown.

Parameters:

sheet - the sheet to write, must not be null

Throws:

java.io.IOException - an IO exception

writeSheetToCSV

public static void writeSheetToCSV(org.apache.poi.ss.usermodel.Sheet sheet,
                                   java.nio.file.Path pathToCSVFile)
                            throws java.io.IOException

Treats the columns as fields in a csv file, writes each row as a separate csv row in the resulting csv file. Uses default maximum cell size. Does not skip the first row. Writes to a CSV file with the same name as the sheet in the current working directory. The number of columns is determined by assuming that the first row contains the CSV header. If the sheet has no columns, then an exception is thrown.

Parameters:

sheet - the sheet to write, must not be null

pathToCSVFile - a Path to the file to write as csv, must not be null

Throws:

java.io.IOException - an IO exception

writeSheetToCSV

public static void writeSheetToCSV(org.apache.poi.ss.usermodel.Sheet sheet,
                                   int numCol)
                            throws java.io.IOException

Treats the columns as fields in a csv file, writes each row as a separate csv row in the resulting csv file. Uses default maximum cell size. Does not skip the first row. Writes to a CSV file with the same name as the sheet in the current working directory.

Parameters:

sheet - the sheet to write, must not be null

numCol - the number of columns to write from each row, must be at least 1

Throws:

java.io.IOException - an IO exception

writeSheetToCSV

public static void writeSheetToCSV(org.apache.poi.ss.usermodel.Sheet sheet,
                                   java.nio.file.Path pathToCSV,
                                   int numCol)
                            throws java.io.IOException

Treats the columns as fields in a csv file, writes each row as a separate csv row in the resulting csv file. Uses default maximum cell size. Does not skip the first row

Parameters:

sheet - the sheet to write, must not be null

pathToCSV - a Path to the file to write as csv, must not be null

numCol - the number of columns to write from each row, must be at least 1

Throws:

java.io.IOException - an IO exception

writeSheetToCSV

public static void writeSheetToCSV(org.apache.poi.ss.usermodel.Sheet sheet,
                                   boolean skipFirstRow,
                                   java.nio.file.Path pathToCSV,
                                   int numCol)
                            throws java.io.IOException

Treats the columns as fields in a csv file, writes each row as a separate csv row in the resulting csv file. Uses default maximum cell size.

Parameters:

sheet - the sheet to write, must not be null

skipFirstRow - if true, the first row is skipped in the sheet

pathToCSV - a Path to the file to write as csv, must not be null

numCol - the number of columns to write from each row, must be at least 1

Throws:

java.io.IOException - an IO exception

writeSheetToCSV

public static void writeSheetToCSV(org.apache.poi.ss.usermodel.Sheet sheet,
                                   boolean skipFirstRow,
                                   java.nio.file.Path pathToCSV,
                                   int numCol,
                                   int maxChar)
                            throws java.io.IOException

Treats the columns as fields in a csv file, writes each row as a separate csv row in the resulting csv file

Parameters:

sheet - the sheet to write, must not be null

skipFirstRow - if true, the first row is skipped in the sheet

pathToCSV - a Path to the file to write as csv, must not be null

numCol - the number of columns to write from each row, must be at least 1

maxChar - the maximum number of characters that can be in any cell, must be at least 1

Throws:

java.io.IOException - an IO exception

readSheetAsObjects

public static java.util.List<java.util.List<java.lang.Object>> readSheetAsObjects(org.apache.poi.ss.usermodel.Sheet sheet,
                                                                                  org.jooq.Field<?>[] fields,
                                                                                  boolean skipFirstRow)

Parameters:

sheet - the sheet to process

fields - the fields associated with each row

skipFirstRow - true means first row is skipped

Returns:

a list of lists of the java objects representing each cell of each row of the sheet

readSheetAsListOfObjects

public static java.util.List<java.lang.Object[]> readSheetAsListOfObjects(org.apache.poi.ss.usermodel.Sheet sheet,
                                                                          org.jooq.Field<?>[] fields,
                                                                          boolean skipFirstRow)

Parameters:

sheet - the sheet to process

fields - the fields associated with each row

skipFirstRow - true means first row is skipped

Returns:

a list of the arrays of the java objects representing each cell of each row of the sheet

readRowAsObjectList

public static java.util.List<java.lang.Object> readRowAsObjectList(org.apache.poi.ss.usermodel.Row row,
                                                                   org.jooq.Field<?>[] fields)

Read a row assuming a fixed number of columns. Cells that are missing/null in the row are read as null objects.

Parameters:

row - the Excel row

fields - the fields associated with each row

Returns:

a list of java objects representing the contents of the cells

readRowAsObjectArray

public static java.lang.Object[] readRowAsObjectArray(org.apache.poi.ss.usermodel.Row row,
                                                      org.jooq.Field<?>[] fields)

Parameters:

row - the Excel row

fields - the fields associated with each row

Returns:

an array of java objects representing the contents of the cells within the row

readRowAsObjectList

public static java.util.List<java.lang.Object> readRowAsObjectList(org.apache.poi.ss.usermodel.Row row,
                                                                   int numCol)

Read a row assuming a fixed number of columns. Cells that are missing/null in the row are read as null objects.

Parameters:

row - the Excel row

numCol - the number of columns in the row

Returns:

a list of java objects representing the contents of the cells

readRowAsObjectArray

public static java.lang.Object[] readRowAsObjectArray(org.apache.poi.ss.usermodel.Row row,
                                                      int numCol)

Parameters:

row - the Excel row

numCol - the number of columns in the row

Returns:

an array of java objects representing the contents of the cells within the row

readRowAsStringList

public static java.util.List<java.lang.String> readRowAsStringList(org.apache.poi.ss.usermodel.Row row,
                                                                   int numCol)

Read a row assuming a fixed number of columns. Cells that are missing/null in the row are read as null Strings.

Parameters:

row - the Excel row

numCol - the number of columns in the row

Returns:

a list of java objects representing the contents of the cells

readRowAsStringList

public static java.util.List<java.lang.String> readRowAsStringList(org.apache.poi.ss.usermodel.Row row,
                                                                   int numCol,
                                                                   int maxChar)

Read a row assuming a fixed number of columns. Cells that are missing/null in the row are read as null Strings.

Parameters:

row - the Excel row

numCol - the number of columns in the row

maxChar - the maximum number of characters permitted for any string

Returns:

a list of java Strings representing the contents of the cells

readRowAsStringArray

public static java.lang.String[] readRowAsStringArray(org.apache.poi.ss.usermodel.Row row,
                                                      int numCol)

Read a row assuming a fixed number of columns. Cells that are missing/null in the row are read as null Strings.

Parameters:

row - the Excel row

numCol - the number of columns in the row

Returns:

an array of java Strings representing the contents of the cells

readRowAsStringArray

public static java.lang.String[] readRowAsStringArray(org.apache.poi.ss.usermodel.Row row,
                                                      int numCol,
                                                      int maxChar)

Read a row assuming a fixed number of columns. Cells that are missing/null in the row are read as null Strings.

Parameters:

row - the Excel row

numCol - the number of columns in the row

maxChar - the maximum number of characters permitted for any string

Returns:

an array of java Strings representing the contents of the cells

readCellAsString

public static java.lang.String readCellAsString(org.apache.poi.ss.usermodel.Cell cell)

Reads the Excel cell and translates it into a String

Parameters:

cell - the Excel cell to read data from

Returns:

the data in the form of a Java String

readCellAsObject

public static java.lang.Object readCellAsObject(org.apache.poi.ss.usermodel.Cell cell)

Reads the Excel cell and translates it into a Java object

Parameters:

cell - the Excel cell to read data from

Returns:

the data in the form of a Java object

writeTableAsExcelSheet

public static void writeTableAsExcelSheet(DatabaseIfc db,
                                          java.lang.String tableName,
                                          org.apache.poi.ss.usermodel.Sheet sheet)

Writes a table from the database to the Excel sheet. Includes the field names as the first row of the sheet.

Parameters:

db - the database containing the table, must not be null

tableName - the table to read from, must not be null

sheet - the Excel sheet to write to, must not be null

writeResultRecordsToExcelWorkbook

public static void writeResultRecordsToExcelWorkbook(java.nio.file.Path pathToWb,
                                                     java.lang.String sheetName,
                                                     org.jooq.Result<org.jooq.Record> records)

If the workbook exists the sheet containing the results is added to the workbook. If the sheet exists with the same name then a new sheet is made. See createSheet() method. If the workbook does not exist, then it is created and the sheet of results added.

Parameters:

pathToWb - the path to the workbook, must not be null. If the workbook exists it is used, if it does not exist at the path then a new workbook created at the location

sheetName - the name of the sheet to write to, must not be null, if it already exists then a new sheet with name sheetName_n is created where n is one more than the number of sheets

records - the jooq Result to write to the sheet, must not be null

createSheet

public static org.apache.poi.ss.usermodel.Sheet createSheet(org.apache.poi.ss.usermodel.Workbook workbook,
                                                            java.lang.String sheetName)

Creates a sheet within the workbook with the name. If a sheet already exists with the same name then a new sheet with name sheetName_n, where n is the current number of sheets in the workbook is created. Sheet names must follow Excel naming conventions.

Parameters:

workbook - the workbook, must not be null

sheetName - the name of the sheet

Returns:

the created sheet

writeResultRecordsAsExcelSheet

public static void writeResultRecordsAsExcelSheet(org.jooq.Result<org.jooq.Record> records,
                                                  org.apache.poi.ss.usermodel.Sheet sheet)

Writes the results from a query to the Excel sheet. Includes the field names as the first row of the sheet.

Parameters:

records - the records from a select query, must not be null

sheet - the Excel sheet to write to, must not be null

getColumnSize

public static int getColumnSize(org.apache.poi.ss.usermodel.Sheet sheet,
                                int columnIndex)

Starts as the last row number of the sheet and looks up in the column to find the first non-null cell

Parameters:

sheet - the sheet holding the column, must not be null

columnIndex - the column index, must be 0 or greater, since POI is 0 based columns

Returns:

the number of rows that have data in the particular column as defined by not having a null cell.

isCellEmpty

public static boolean isCellEmpty(org.apache.poi.ss.usermodel.Cell cell)

Parameters:

cell - the cell to check

Returns:

true if it null or blank or string and empty

writeRecordToSheet

public static void writeRecordToSheet(org.jooq.Record record,
                                      org.apache.poi.ss.usermodel.Row row)

Writes a single row from the ResultSet to a row in an Excel Sheet

Parameters:

record - the Record to get the data, must not be null

row - the Excel row, must not be null

writeCell

public static void writeCell(org.apache.poi.ss.usermodel.Cell cell,
                             java.lang.Object object)

Writes the Java Object to the Excel cell

Parameters:

cell - the cell to write

object - a Java object

writeTableToExcelWorkbook

public static void writeTableToExcelWorkbook(tech.tablesaw.api.Table table,
                                             java.lang.String wbName)

Writes the table to a workbook. A new workbook is created and a sheet with the same name as the table is created. The data is written to the sheet.

Parameters:

table - the Tablesaw Table to write out, must not be null

wbName - the name to the workbook, must not be null. If there already is a workbook file at the path location, then it is over written

writeTableToExcelWorkbook

public static void writeTableToExcelWorkbook(tech.tablesaw.api.Table table,
                                             java.nio.file.Path pathToWb)

Writes the table to a new workbook. A new workbook is created and a sheet with the same name as the table is created. The data is written to the sheet.

Parameters:

table - the Tablesaw Table to write out, must not be null

pathToWb - the path to the workbook, must not be null. If there already is a workbook file at the path location, then it is over written

writeTableAsExcelSheet

public static void writeTableAsExcelSheet(tech.tablesaw.api.Table table,
                                          org.apache.poi.ss.usermodel.Sheet sheet)

Writes the contents from a Tablesaw Table to the Excel sheet. Includes the column names as the first row of the sheet.

Parameters:

table - the Tablesaw Table, must not be null

sheet - the Excel sheet to write to, must not be null

getNextEnumConstant

public static int getNextEnumConstant()

Should be used by subclasses to get the next constant so that unique constants can be used

Returns:

the constant

makeTable

public static tech.tablesaw.api.Table makeTable(java.nio.file.Path pathToWorkbook,
                                                java.lang.String sheetName)

Makes a Tablesaw table from a sheet within an Excel workbook. There are some assumptions here. 1) the sheet contains columns of data with each column holding the same data type 2) that the sheet can be converted to a valid csv file format If something goes wrong an empty table is create and a warning is logged. If you have trouble with this, then you might first translate the Excel workbook to a csv file and then use the many options available within Tablesaw to import the data from csv.

Parameters:

pathToWorkbook - must not be null, the path to the Excel workbook

sheetName - must not be null, must be a valid sheet name within the workbook

Returns:

the table, will have the same name as the sheet

makeTable

public static tech.tablesaw.api.Table makeTable(java.nio.file.Path pathToWorkbook,
                                                java.lang.String sheetName,
                                                java.lang.String tableName)

Makes a Tablesaw table from a sheet within an Excel workbook. There are some assumptions here. 1) the sheet contains columns of data with each column holding the same data type 2) that the sheet can be converted to a valid csv file format. If something goes wrong an empty table is create and a warning is logged. If you have trouble with this, then you might first translate the Excel workbook to a csv file and then use the many options available within Tablesaw to import the data from csv.

Parameters:

pathToWorkbook - must not be null, the path to the Excel workbook

sheetName - must not be null, must be a valid sheet name within the workbook

tableName - the name that you want for the created table, if null it will have the sheet name

Returns:

the table