Property Detail

• static org.slf4j.Logger log

  A logger to use

Constructor Detail

• ExcelUtil()

Method Detail

• static java.util.Map indexMap(java.util.List<java.lang.String> vals)

  Return a map of index to value for the provided list of string values.

  Parameters:

  vals - A list of strings

  Returns:

  A map of integer to Strings

• static java.util.List<java.util.Map> readExcelFile(java.io.File inputFile)

  Read an Excel file into a list of maps.

  Parameters:

  inputFile - Th Excel file.

  Returns:

  A list of maps containing the data in the input file.

• static java.util.List<java.util.Map> readExcelFile(java.io.InputStream input)

  Read an Excel file in to a list of maps.

  Parameters:

  input - An input stream of the Excel file data.

  Returns:

  A list of maps containing the data in the input file.

• static java.util.List<ExcelRow> readExcelSheet(java.io.File inputFile, java.lang.String sheetName, java.util.Map params)

  Read an individual sheet from an Excel file. Reading from an Excel is not straightforward or easy. These utility methods might might help you out if: - The Excel sheet is formatted like a data table where a header row contains the field names and the remainder of the sheet contains rows of data. - There is only a single format for dates in the sheet. The default behavior is to be tolerant of read failures, writing warning messages to the log while ignoring the value. To fail on error, see the failOnError parameter.

  Parameters:

  inputFile - The Excel file to read.

  sheetName - The name of the sheet.

  params.raws - Optional list of fields to treat as raw values.

  params.integers - Optional list of fields to interpret as integers.

  params.dates - Optional map of data parsing params.

  params.dates.sourceFormat - The format (DateFormat) used for dates in the Excel file.

  params.dates.outputFormat - The formatter (DateFormat) used to output dates as Strings.

  params.dates.fields - A list of fields that should be parsed as dates.

  failOnError - If there is an error reading the file, throw a ParseException.

  firstRowNum - Integer The first row to read. Use this parameter to skip one or more rows at the start of the document before the header row.

  skipRowNums - Set Rownums to skip. Use this parameter to skip rows that are buried within data rows.

  skipBlankRows - Boolean If true, skip over rows that contain no data.

  skipNullRows - Boolean If true, skip over rowNums that have a null row. readExcelSheet attempts to read the entire sheet by accessing the rows by rowNum in sequential order. There is no guarantee that there will be no skipped rowNums in an Excel sheet, even if there are no apparent empty rows. skipNullRows should probably be the default, but it is an explicit switch for now.

• static java.util.List<ExcelRow> readExcelSheet(java.io.InputStream input, java.lang.String sheetName, java.util.Map params)

  Read the a sheet of the provided name from the provided input stream.

  Parameters:

  input - The input stream for the Excel file

  sheetName - The name of the sheet to read

  params - Optional additional parameters

  Returns:

  A list of Excel rows

• static java.util.List<java.lang.String> readRow(org.apache.poi.xssf.usermodel.XSSFRow row)

  Read the provided row and return the data as a list of strings.

  Parameters:

  row - The Excel row

  Returns:

  The data as a list of strings

• static java.util.List<java.lang.String> readRow(org.apache.poi.xssf.usermodel.XSSFRow row, java.util.Map params)

  Read the provided row and return the data as a list of strings.

  Parameters:

  row - The Excel row

  params.colNameToIdx - Map of column names to index

  params.dates.sourceFormat - The format of dates in the Excel file

  params.dates.outputFormat - The target format of dates

  params.dates.fields - The fields that contain dates

  params.raws - The raw indexes in the data

  params.integerIndexes - The indexes in the data as integers

  params.failOnError - If true, fail if an error is encountered

  Returns:

  The data in a list of strings

• static java.util.List<ExcelRow> readSheet(org.apache.poi.xssf.usermodel.XSSFWorkbook wb, java.lang.String sheetName, java.util.Map params)

  Read a sheet named as the provided name from the provided workbook.

  Parameters:

  wb - The Excel workbook

  sheetName - The name of the sheet to read

  params - Optional additional parameters

  Returns:

  A list of Excel rows