Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE

Interacting with an Excel Workbook via Silk4J

Interacting with an Excel Workbook via Silk4J

As of Java 8, the Java ODBC bridge was removed along with the capabilities to read and write values to an Excel Workbook via the Excel ODBC driver. Silk4J tests which leveraged this capability, must now look for an alternative API for working with Excel. The most widely used of these alternatives is the open source Apache POI library. For more information and to download the Apache POI library, please click here.

As it can be difficult to learn and implement a solution with a custom API, such as the Apach POI library, you can find a utility class attached to this article which provides a number of helper methods for interacting with Excel Workbooks which are saved in the XLSX format. The following methods are provided:

 

Constructor:

  • ExcelUtils(String xlsx_file) :- Creates an instance of ExcelUtils and opens the workbook specified.

Methods:

  • selectSheet(int index) :- Select a specific sheet by its index within the workbook
  • selectSheet(String name) :- Select a specific sheet by its name within the workbook
  • getRowCount() :- Returns the number of rows in the currently selected sheet
  • getColumnCount() :- Returns the number of columns based on the current header index
  • getCellValueAsString(int row, int col) :- Returns the value of the specified cell as a string
  • getRowStringValues(int row) :- Returns a string array with the row values
  • getSheetDataAsString() :- Returns an ArrayList containing string arrays with the row data
  • setDateFormat(String format) :- Sets the date format that should be returned from DATE cells
  • addRow() :- Creates an empty row at the end of the workbook
  • addRow(Object... row_values) Adds a row to the current sheet with the specified values
  • getRow(int row) :- Returns a XSSFRow object for the given row index
  • setCellValue(int row, int col, String value) :- Sets the specified cell to the string value
  • setCellValue(int row, int col, Date value) :- Sets the specified cell to the date value
  • setCellValue(int row, int col, double value) :- Sets the specified cell to the double value
  • setCellValue(int row, int col, boolean value) :- Sets the specified cell to the boolean value
  • setCellValue(int row, int col, RichTextString value) :- Sets the specified cell to the RichTextString value
  • setCellValue(int row, int col, Calendar value) :- Sets the specified cell to the Calendar value
  • setHeaderRow(int row) :- Sets the header row which contains the column names
  • getHeaderText(int col) :- Returns the header text for the given column
  • getSheetName(int sheetNum) :- Returns the sheet name at the specified index
  • save() :- Saves any changes to the workbook
  • closeWorkbook() :- Closes the Excel workbook

 

Download ExcelUtils.java

ExcelUtils.zip

Example Usage:

     @Test
     public void textExcel(){
          ExcelUtils excel = new ExcelUtils("C:\\demo_workbook.xlsx");
          excel.selectSheet("Sheet1");
          excel.setHeaderRow(0);

          excel.setDateFormat("dd-MM-yy");

          //Print the sheet data
          ArrayList<String[]> data = excel.getSheetDataAsString();
          for(String[] row: data){
               for(int i=0; i<row.length; i++){
                    System.out.print(row);
                    if(i!=row.length-1){
                         System.out.print(", ");
                    }
               }
               System.out.println("");
          }

          //Add a new row
          Date d = new Date();
          excel.addRow("Test", d, " 20 address test", 27.5);

          //Save changes
          excel.save();

          //Close the workbook
          excel.closeWorkbook();
     }

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
1 of 1
Last update:
‎2017-04-25 13:14
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.