ExcelUtil

object ExcelUtil

Properties

Link copied to clipboard
const val DEFAULT_MAX_CHAR_IN_CELL: Int = 512
Link copied to clipboard
val logger: KLogger

Functions

Link copied to clipboard
fun columnSize(sheet: Sheet, columnIndex: Int): Int

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

Link copied to clipboard
fun createSheet(workbook: Workbook, sheetName: String): Sheet

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.

Link copied to clipboard
fun exportAsWorkSheet(resultSet: ResultSet, sheet: Sheet, writeHeader: Boolean = true)

Exports the data in the ResultSet to an Excel worksheet. The ResultSet is assumed to be forward only and each row is processed until all rows are exported. The ResultSet is closed after the processing.

Link copied to clipboard
fun exportTablesToExcel(db: DatabaseIfc, path: Path, tableNames: List<String>, schemaName: String?)

Writes each table in the list to an Excel workbook with each table being placed in a new sheet with the sheet name equal to the name of the table. The column names for each table are written as the first row of each sheet.

Link copied to clipboard
fun importSheetToTable(    db: DatabaseIfc,     sheet: Sheet,     tableName: String,     numColumns: Int,     schemaName: String?,     numRowsToSkip: Int,     rowBatchSize: Int = 100,     unCompatibleRows: PrintWriter): Boolean

Copies the rows from the sheet to the table. The copy is assumed to start at row 1, column 1 (i.e. cell A1) and proceed to the right for the number of columns in the table and the number of rows of the sheet. The copy is from the perspective of the table. That is, all columns of a row of the table are attempted to be filled from a corresponding row of the sheet. If the row of the sheet does not have cell values for the corresponding column, then the cell is interpreted as a null value when being placed in the corresponding column. It is up to the client to ensure that the cells in a row of the sheet are data type compatible with the corresponding column in the table. Any rows that cannot be transfer in their entirety are logged to the supplied PrintWriter

Link copied to clipboard
fun importWorkbookToSchema(db: DatabaseIfc, pathToWorkbook: Path, tableNames: List<String>, schemaName: String?, skipFirstRow: Boolean)

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.

Link copied to clipboard
fun isCellEmpty(cell: Cell): Boolean
Link copied to clipboard
fun numberColumns(row: Row): Int

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

Link copied to clipboard
fun numberColumnsForCSVHeader(sheet: Sheet): Int

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

Link copied to clipboard
fun openExistingXSSFWorkbookReadOnly(pathToWorkbook: Path): XSSFWorkbook?

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.

Link copied to clipboard
fun readCellAsObject(cell: Cell): Any?

Reads the Excel cell and translates it into a Java object

Link copied to clipboard
fun readCellAsString(cell: Cell): String

Reads the Excel cell and translates it into a String

Link copied to clipboard
fun readRowAsObjectList(row: Row, numColumns: Int = numberColumns(row)): List<Any?>

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

Link copied to clipboard
fun readRowAsStringArray(row: Row, numCol: Int, maxChar: Int = DEFAULT_MAX_CHAR_IN_CELL): Array<String?>

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

Link copied to clipboard
fun readRowAsStringList(row: Row, numCol: Int, maxChar: Int = DEFAULT_MAX_CHAR_IN_CELL): List<String?>

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

Link copied to clipboard
fun readSheetAsObjects(sheet: Sheet, numColumns: Int = numberColumnsForCSVHeader(sheet), skipFirstRow: Boolean = false): List<List<Any?>>
Link copied to clipboard
fun readToMap(    sheetName: String,     pathToWorkbook: Path = KSL.excelDir.resolve("${sheetName}.xlsx"),     skipFirstRow: Boolean = false): Map<String, Double>

This is the reverse operation to the function writeToExcel() for a Map The strings "+Infinity", "-Infinity", and "NaN" in the value column are correctly converted to the appropriate double representation. Any rows that have empty cells (null) are skipped in the processing.

Link copied to clipboard
fun writeCell(cell: Cell, value: Any?)

Writes the Object to the Excel cell

Link copied to clipboard
fun writeSheetToCSV(    sheet: Sheet,     numCol: Int = numberColumnsForCSVHeader(sheet),     skipFirstRow: Boolean = false,     pathToCSV: Path = KSL.outDir.resolve("${sheet.sheetName}.csv"),     maxChar: Int = DEFAULT_MAX_CHAR_IN_CELL)

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

Link copied to clipboard
fun writeToExcel(    map: Map<String, Double>,     sheetName: String,     wbName: String = sheetName,     wbDirectory: Path = KSL.excelDir,     header: Boolean = false)

Writes each entry in the map to an Excel workbook. The mapping of problematic double values is as follows: