Package com.github.javaxcel.util

Class ExcelUtils

java.lang.Object
com.github.javaxcel.util.ExcelUtils
public final class ExcelUtils
extends Object
Utilities for spreadsheet excel with Apache POI.
See Also:
Workbook, Sheet, Row, Cell, CellStyle

  • Method Summary

    Modifier and Type Method Description
    static void autoResizeColumns​(org.apache.poi.ss.usermodel.Sheet sheet, int numOfColumns)
    Adjusts width of columns to fit the contents.
    static int getMaxColumns​(org.apache.poi.ss.usermodel.Sheet sheet)
    Returns maximum number of columns in a spreadsheet.
    static int getMaxColumns​(org.apache.poi.ss.usermodel.Workbook workbook)
    Returns maximum number of columns in a spreadsheet.
    static int getMaxRows​(org.apache.poi.ss.usermodel.Sheet sheet)
    Returns maximum number of rows in a spreadsheet.
    static int getMaxRows​(org.apache.poi.ss.usermodel.Workbook workbook)
    Returns maximum number of rows in a spreadsheet.
    static long getNumOfModels​(File file)
    Returns the number of models in all sheets.
    static int getNumOfModels​(org.apache.poi.ss.usermodel.Sheet sheet)
    Returns the number of models in a sheet.
    static long getNumOfModels​(org.apache.poi.ss.usermodel.Workbook workbook)
    Returns the number of models in all sheets.
    static long getNumOfRows​(File file)
    Returns the number of rows in all sheets.
    static int getNumOfRows​(org.apache.poi.ss.usermodel.Sheet sheet)
    Returns the number of rows in a sheet.
    static long getNumOfRows​(org.apache.poi.ss.usermodel.Workbook workbook)
    Returns the number of rows in all sheets.
    static int[] getSheetRange​(org.apache.poi.ss.usermodel.Workbook workbook)
    Returns range of the sheets.
    static List<org.apache.poi.ss.usermodel.Sheet> getSheets​(org.apache.poi.ss.usermodel.Workbook workbook)
    Returns all sheets in a workbook.
    static org.apache.poi.ss.usermodel.Workbook getWorkbook​(File file)
    Returns the instance of Workbook by reading file.
    static void hideExtraColumns​(org.apache.poi.ss.usermodel.Sheet sheet, int numOfColumns)
    Hides extraneous columns.
    static void hideExtraRows​(org.apache.poi.ss.usermodel.Sheet sheet, int numOfRows)
    Hides extraneous rows.
    static boolean isExcel97​(org.apache.poi.ss.usermodel.Cell cell)
    Checks if spreadsheet's version is 97.
    static boolean isExcel97​(org.apache.poi.ss.usermodel.Row row)
    Checks if spreadsheet's version is 97.
    static boolean isExcel97​(org.apache.poi.ss.usermodel.Sheet sheet)
    Checks if spreadsheet's version is 97.
    static boolean isExcel97​(org.apache.poi.ss.usermodel.Workbook workbook)
    Checks if spreadsheet's version is 97.
    static void setRangeAlias​(org.apache.poi.ss.usermodel.Workbook workbook, String alias, String ref)
    Sets alias for range.
    static void setValidation​(org.apache.poi.ss.usermodel.Sheet sheet, org.apache.poi.ss.usermodel.DataValidationHelper helper, String ref, String... values)
    Sets a validation to the cells on the reference.
    static org.apache.poi.ss.usermodel.CellStyle toCellStyle​(org.apache.poi.ss.usermodel.Workbook workbook, com.github.javaxcel.styler.ExcelStyleConfig config)
    Converts configuration to cell style.
    static org.apache.poi.ss.usermodel.CellStyle[] toCellStyles​(org.apache.poi.ss.usermodel.Workbook workbook, com.github.javaxcel.styler.ExcelStyleConfig... configs)
    Converts configurations to cell styles.
    static String toColumnRangeReference​(org.apache.poi.ss.usermodel.Sheet sheet, int columnIndex)
    Converts a reference for column range address except first row.
    static String toRangeReference​(org.apache.poi.ss.usermodel.Sheet sheet, int startColumnIndex, int startRowIndex, int endColumnIndex, int endRowIndex)
    Converts a reference for cell range address.
    static String toRangeReference​(org.apache.poi.ss.usermodel.Sheet sheet, org.apache.poi.ss.usermodel.Cell startCell, org.apache.poi.ss.usermodel.Cell endCell)
    Converts a reference for cell range address.

    Methods inherited from class java.lang.Object

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

  • Method Details

    • getWorkbook

      public static org.apache.poi.ss.usermodel.Workbook getWorkbook​(File file)
      Returns the instance of Workbook by reading file.
      Parameters:
      file - excel file
      Returns:
      excel workbook instance
      Throws:
      IllegalArgumentException - unless file extension is equal to 'xls' or 'xlsx'

    • getSheetRange

      public static int[] getSheetRange​(org.apache.poi.ss.usermodel.Workbook workbook)
      Returns range of the sheets.
      Parameters:
      workbook - excel workbook
      Returns:
      range that from 0 to (the number of sheets - 1)
      See Also:
      Workbook.getNumberOfSheets()

    • getSheets

      public static List<org.apache.poi.ss.usermodel.Sheet> getSheets​(org.apache.poi.ss.usermodel.Workbook workbook)
      Returns all sheets in a workbook.
      Parameters:
      workbook - excel workbook
      Returns:
      all sheets

    • getNumOfRows

      public static int getNumOfRows​(org.apache.poi.ss.usermodel.Sheet sheet)
      Returns the number of rows in a sheet.
      Parameters:
      sheet - sheet
      Returns:
      the number of rows
      Throws:
      UnsupportedWorkbookException - if instance of sheet is SXSSFSheet

    • getNumOfRows

      public static long getNumOfRows​(org.apache.poi.ss.usermodel.Workbook workbook)
      Returns the number of rows in all sheets.
      Parameters:
      workbook - excel workbook
      Returns:
      the number of rows
      Throws:
      UnsupportedWorkbookException - if instance of workbook is SXSSFWorkbook

    • getNumOfRows

      public static long getNumOfRows​(File file)
      Returns the number of rows in all sheets.
      Parameters:
      file - excel file
      Returns:
      the number of rows

    • getNumOfModels

      public static int getNumOfModels​(org.apache.poi.ss.usermodel.Sheet sheet)
      Returns the number of models in a sheet.

      This excludes header row. In other words, this returns the total number of rows minus 1.

      Parameters:
      sheet - sheet
      Returns:
      the number of models
      Throws:
      UnsupportedWorkbookException - if instance of sheet is SXSSFSheet

    • getNumOfModels

      public static long getNumOfModels​(org.apache.poi.ss.usermodel.Workbook workbook)
      Returns the number of models in all sheets.

      This excludes header row. In other words, this returns the total number of rows minus number of all headers.

      Parameters:
      workbook - excel workbook
      Returns:
      the number of models
      Throws:
      UnsupportedWorkbookException - if instance of workbook is SXSSFWorkbook

    • getNumOfModels

      public static long getNumOfModels​(File file)
      Returns the number of models in all sheets.

      This excludes header row. In other words, this returns the total number of rows minus number of all headers.

      Parameters:
      file - excel file
      Returns:
      the number of models

    • getMaxRows

      public static int getMaxRows​(org.apache.poi.ss.usermodel.Workbook workbook)
      Returns maximum number of rows in a spreadsheet.
      Parameters:
      workbook - excel workbook
      Returns:
      maximum number of rows in a spreadsheet

    • getMaxRows

      public static int getMaxRows​(org.apache.poi.ss.usermodel.Sheet sheet)
      Returns maximum number of rows in a spreadsheet.
      Parameters:
      sheet - sheet
      Returns:
      maximum number of rows in a spreadsheet

    • getMaxColumns

      public static int getMaxColumns​(org.apache.poi.ss.usermodel.Workbook workbook)
      Returns maximum number of columns in a spreadsheet.
      Parameters:
      workbook - excel workbook
      Returns:
      maximum number of columns in a spreadsheet

    • getMaxColumns

      public static int getMaxColumns​(org.apache.poi.ss.usermodel.Sheet sheet)
      Returns maximum number of columns in a spreadsheet.
      Parameters:
      sheet - sheet
      Returns:
      maximum number of columns in a spreadsheet

    • isExcel97

      public static boolean isExcel97​(org.apache.poi.ss.usermodel.Workbook workbook)
      Checks if spreadsheet's version is 97.
      Parameters:
      workbook - excel workbook
      Returns:
      whether spreadsheet's version is 97 or not

    • isExcel97

      public static boolean isExcel97​(org.apache.poi.ss.usermodel.Sheet sheet)
      Checks if spreadsheet's version is 97.
      Parameters:
      sheet - excel sheet
      Returns:
      whether spreadsheet's version is 97 or not

    • isExcel97

      public static boolean isExcel97​(org.apache.poi.ss.usermodel.Row row)
      Checks if spreadsheet's version is 97.
      Parameters:
      row - row
      Returns:
      whether spreadsheet's version is 97 or not

    • isExcel97

      public static boolean isExcel97​(org.apache.poi.ss.usermodel.Cell cell)
      Checks if spreadsheet's version is 97.
      Parameters:
      cell - cell
      Returns:
      whether spreadsheet's version is 97 or not

    • autoResizeColumns

      public static void autoResizeColumns​(org.apache.poi.ss.usermodel.Sheet sheet, int numOfColumns)
      Adjusts width of columns to fit the contents.

      This can be affected by font size and font family. If you want this process well, set up the same font family into all cells. This process will be perform in parallel, but if SXSSFSheet is, in single.

      If instance of sheet is SXSSFSheet, the columns may be inaccurately auto-resized compared to HSSFSheet and XSSFSheet.

      Parameters:
      sheet - excel sheet
      numOfColumns - number of the columns that wanted to make fit contents.
      See Also:
      Sheet.autoSizeColumn(int)

    • hideExtraRows

      public static void hideExtraRows​(org.apache.poi.ss.usermodel.Sheet sheet, int numOfRows)
      Hides extraneous rows.

      This process will be performed in single-thread. If change this code to be in parallel, this will throw ConcurrentModificationException.

      Parameters:
      sheet - excel sheet
      numOfRows - number of the rows that have contents.
      See Also:
      Row.setZeroHeight(boolean)

    • hideExtraColumns

      public static void hideExtraColumns​(org.apache.poi.ss.usermodel.Sheet sheet, int numOfColumns)
      Hides extraneous columns.

      This process shouldn't be performed in parallel. If try it, this is about 46% slower when handled in parallel than when handled in sequential. 

      
     +------------+----------+
     | sequential | parallel |
     +------------+----------+
     | 15s        | 22s      |
     +------------+----------+
      Parameters:
      sheet - excel sheet
      numOfColumns - number of the columns that have contents.
      See Also:
      Sheet.setColumnHidden(int, boolean)

    • toCellStyle

      public static org.apache.poi.ss.usermodel.CellStyle toCellStyle​(org.apache.poi.ss.usermodel.Workbook workbook, com.github.javaxcel.styler.ExcelStyleConfig config)
      Converts configuration to cell style.
      Parameters:
      workbook - excel workbook
      config - configuration of cell style
      Returns:
      cell style

    • toCellStyles

      public static org.apache.poi.ss.usermodel.CellStyle[] toCellStyles​(org.apache.poi.ss.usermodel.Workbook workbook, com.github.javaxcel.styler.ExcelStyleConfig... configs)
      Converts configurations to cell styles.
      Parameters:
      workbook - excel workbook
      configs - configurations of cell style
      Returns:
      cell styles

    • setRangeAlias

      public static void setRangeAlias​(org.apache.poi.ss.usermodel.Workbook workbook, String alias, String ref)
      Sets alias for range. 
      
     Workbook workbook = new XSSFWorkbook();
     Sheet sheet = workbook.createSheet("mySheet");
     String ref = sheet.getSheetName() + "!$A$1:$A$2";

     setRangeAlias(workbook, "MY_RANGE", ref);
      Parameters:
      workbook - excel workbook
      alias - alias for cell range address
      ref - reference for cell range address

    • toRangeReference

      public static String toRangeReference​(org.apache.poi.ss.usermodel.Sheet sheet, org.apache.poi.ss.usermodel.Cell startCell, org.apache.poi.ss.usermodel.Cell endCell)
      Converts a reference for cell range address. 
      
     Workbook workbook = new XSSFWorkbook();
     Sheet sheet = workbook.createSheet("mySheet");
     Cell startCell = sheet.createRow(0).createCell(0);
     Cell endCell = sheet.createRow(1).createCell(0);

     toRangeReference(sheet, startCell, endCell); // mySheet!$A$1:$A$2
      Parameters:
      sheet - excel sheet
      startCell - first cell in cell range address
      endCell - last cell in cell range address
      Returns:
      reference for cell range address
      Throws:
      IllegalArgumentException - if end cell precedes start cell.

    • toRangeReference

      public static String toRangeReference​(org.apache.poi.ss.usermodel.Sheet sheet, int startColumnIndex, int startRowIndex, int endColumnIndex, int endRowIndex)
      Converts a reference for cell range address. 
      
     Workbook workbook = new XSSFWorkbook();
     Sheet sheet = workbook.createSheet("mySheet");

     toRangeReference(sheet, 0, 0, 0, 1); // mySheet!$A$1:$A$2
     toRangeReference(sheet, 2, 1, 5, 4); // mySheet!$C$2:$F$5
      Parameters:
      sheet - excel sheet
      startColumnIndex - column index of first cell in cell range address
      startRowIndex - row index of first cell in cell range address
      endColumnIndex - column index of end cell in cell range address
      endRowIndex - row index of end cell in cell range address
      Returns:
      reference for cell range address
      Throws:
      IllegalArgumentException - if end cell's column/row index precedes start cell's

    • toColumnRangeReference

      public static String toColumnRangeReference​(org.apache.poi.ss.usermodel.Sheet sheet, int columnIndex)
      Converts a reference for column range address except first row. 
      
     Workbook hssfWorkbook = new HSSFWorkbook();
     Sheet hssfSheet = hssfWorkbook.createSheet("mySheet");
     toRangeReference(hssfSheet, 0); // mySheet!$A$2:$A$65536

     Workbook xssfWorkbook = new XSSFWorkbook();
     Sheet xssfSheet = xssfWorkbook.createSheet("mySheet");
     toRangeReference(xssfSheet, 2); // mySheet!$C$2:$A$1048576
      Parameters:
      sheet - excel sheet
      columnIndex - column index for cell range address
      Returns:
      reference for column range address except first row
      Throws:
      IllegalArgumentException - if column index is greater than max column index of the sheet

    • setValidation

      public static void setValidation​(org.apache.poi.ss.usermodel.Sheet sheet, org.apache.poi.ss.usermodel.DataValidationHelper helper, String ref, String... values)
      Sets a validation to the cells on the reference. 
      
     Workbook workbook = new XSSFWorkbook();
     Sheet sheet = workbook.createSheet("mySheet");
     DataValidationHelper helper = sheet.getDataValidationHelper();
     String ref = toRangeReference(sheet, 2);

     setValidation(sheet, helper, ref, "RED", "GREEN", "BLUE");
      Parameters:
      sheet - excel sheet
      helper - data validation helper
      ref - reference for cell range address
      values - constraint values
      See Also:
      Sheet.getDataValidationHelper()