jxl.read.biff.BiffException Java Examples

private WikiPane setActions() {
  fileName.textProperty().addListener((observable, oldValue, newValue) -> {
    createButton.setDisable(newValue.isEmpty());
  });

  createButton.setOnAction(event -> {
    try {
      createSpreadsheet();
      showOpenFileButton();
      Settings.saveProperties();
    } catch (IOException | BiffException | WriteException ex) {
      addElement(new WikiLabel("create-file-error"));
      Session.LOGGER.log(Level.WARNING, 
          "Error occurred during creation of spreadsheet file: {0}",
          new String[]{ex.getLocalizedMessage()}
      );
    }
  });

  return this;
}
 

/**
 * Reads spreadsheet stored in Session.FILE.
 */
private void readSpreadSheet() throws BiffException, IOException, Exception {
  infoContainer.getChildren().clear();
  Session.SCENES.remove("CheckPane");

  WorkbookSettings ws = new WorkbookSettings();
  ws.setEncoding("Cp1252");

  try {
    Workbook workbook = Workbook.getWorkbook(Session.FILE, ws);
    Sheet dataSheet = workbook.getSheet(0);
    Sheet templateSheet = workbook.getSheet(1);
    readHeaders(dataSheet);
    addFilesToUpload(readDescriptions(dataSheet), readTemplate(templateSheet));
  } catch (IndexOutOfBoundsException ex) {
    throw new Exception("Error: your spreadsheet should have minimum two tabs.");
  }

  reloadButton.setDisable(false);
}
 

/**
 * Creates an excel table model (either {@link ExcelSheetTableModel} or
 * {@link XlsxSheetTableModel}, depending on file).
 *
 * @param sheetSelection
 *            the Sheet Selection method
 * @param readMode
 *            the read mode for {@link XlsxSheetTableModel} creation. It defines whether only a
 *            preview or the whole sheet content will be loaded
 * @param progressListener
 *            the progress listener to report progress to
 * @return
 * @throws BiffException
 * @throws IOException
 * @throws InvalidFormatException
 */
public AbstractTableModel createExcelTableModel(ExcelSheetSelection sheetSelection, XlsxReadMode readMode, ProgressListener progressListener)
		throws BiffException, IOException, InvalidFormatException, OperatorException, ParseException {
	if (getFile().getAbsolutePath().toLowerCase(Locale.ENGLISH).endsWith(XLSX_FILE_ENDING)) {
		// excel 2007 file
		return new XlsxSheetTableModel(this, sheetSelection, readMode, getFile().getAbsolutePath(), progressListener);
	} else {
		// excel pre 2007 file
		progressListener.setCompleted(50);

		try {
			return new ExcelSheetTableModel(sheetSelection.selectSheetFrom(getOrCreateWorkbookJXL()));
		} catch (ExcelSheetSelection.SheetNotFoundException e) {
			throw new IOException(e);
		}
	}
}
 

public Map<Integer, List<String>> readJExcel(String fileLocation) throws IOException, BiffException {
    Map<Integer, List<String>> data = new HashMap<>();

    Workbook workbook = Workbook.getWorkbook(new File(fileLocation));
    Sheet sheet = workbook.getSheet(0);
    int rows = sheet.getRows();
    int columns = sheet.getColumns();

    for (int i = 0; i < rows; i++) {
        data.put(i, new ArrayList<String>());
        for (int j = 0; j < columns; j++) {
            data.get(i).add(sheet.getCell(j, i).getContents());
        }
    }
    return data;
}
 

private boolean isTemplateContained( Workbook templateWorkbook, File targetFile )
  throws IOException, BiffException {
  Workbook targetFileWorkbook = Workbook.getWorkbook( targetFile );
  int templateWorkbookNumberOfSheets = templateWorkbook.getNumberOfSheets();
  int targetWorkbookNumberOfSheets = targetFileWorkbook.getNumberOfSheets();
  if ( templateWorkbookNumberOfSheets > targetWorkbookNumberOfSheets ) {
    return false;
  }

  for ( int worksheetNumber = 0; worksheetNumber < templateWorkbookNumberOfSheets; worksheetNumber++ ) {
    Sheet templateWorkbookSheet = templateWorkbook.getSheet( worksheetNumber );
    Sheet targetWorkbookSheet = targetFileWorkbook.getSheet( worksheetNumber );
    int templateWorkbookSheetColumns = templateWorkbookSheet.getColumns();
    int targetWorkbookSheetColumns = targetWorkbookSheet.getColumns();
    if ( templateWorkbookSheetColumns > targetWorkbookSheetColumns ) {
      return false;
    }
    int templateWorkbookSheetRows = templateWorkbookSheet.getRows();
    int targetWorkbookSheetRows = targetWorkbookSheet.getRows();
    if ( templateWorkbookSheetRows > targetWorkbookSheetRows ) {
      return false;
    }
    for ( int currentRowNumber = 0; currentRowNumber < templateWorkbookSheetRows; currentRowNumber++ ) {
      Cell[] templateWorkbookSheetRow = templateWorkbookSheet.getRow( currentRowNumber );
      Cell[] targetWorkbookSheetRow = targetWorkbookSheet.getRow( currentRowNumber );
      if ( templateWorkbookSheetRow.length > targetWorkbookSheetRow.length ) {
        return false;
      }
      for ( int currentCellNumber = 0; currentCellNumber < templateWorkbookSheetRow.length; currentCellNumber++ ) {
        Cell templateWorksheetCell = templateWorkbookSheetRow[ currentCellNumber ];
        Cell targetWorksheetCell = targetWorkbookSheetRow[ currentCellNumber ];
        if ( !templateWorksheetCell.getContents().equals( targetWorksheetCell.getContents() ) ) {
          return false;
        }
      }
    }
  }
  return true;
}
 

private void createSpreadsheet() throws IOException, BiffException, WriteException {
  File f = new File(Session.DIRECTORY, fileName.getText() + ".xls");
  WritableWorkbook workbook = Workbook.createWorkbook(f);

  createDataSheet(workbook);
  createTemplateSheet(workbook);

  workbook.write();
  workbook.close();
  Session.FILE = f;
}
 

/**
 * Returns the number of sheets in the excel file
 *
 * @return
 * @throws IOException
 * @throws BiffException
 * @throws InvalidFormatException
 */
public int getNumberOfSheets() throws BiffException, IOException, InvalidFormatException, UserError {
	if (getFile().getAbsolutePath().toLowerCase(Locale.ENGLISH).endsWith(XLSX_FILE_ENDING)) {
		// excel 2007 file
		try (ZipFile zipFile = new ZipFile(getFile().getAbsolutePath())) {
			return parseWorkbook(zipFile).xlsxWorkbookSheets.size();
		} catch (ParserConfigurationException | SAXException e) {
			throw new UserError(null, e, "xlsx_content_malformed");
		}
	} else {
		// excel pre 2007 file
		return getOrCreateWorkbookJXL().getNumberOfSheets();
	}
}
 

/**
 * Returns the names of all sheets in the excel file
 *
 * @return
 * @throws IOException
 * @throws BiffException
 * @throws InvalidFormatException
 */
public String[] getSheetNames() throws BiffException, IOException, InvalidFormatException, UserError {
	if (getFile().getAbsolutePath().toLowerCase(Locale.ENGLISH).endsWith(XLSX_FILE_ENDING)) {
		// excel 2007 file
		try (ZipFile zipFile = new ZipFile(getFile().getAbsolutePath())) {
			return parseWorkbook(zipFile).xlsxWorkbookSheets.stream().map(s -> s.name).toArray(String[]::new);
		} catch (ParserConfigurationException | SAXException e) {
			throw new UserError(null, e, "xlsx_content_malformed");
		}
	} else {
		// excel pre 2007 file
		return getOrCreateWorkbookJXL().getSheetNames();
	}
}
 

/**
 * Creates the Workbook if needed
 *
 * @return the existing or freshly created workbook
 * @throws IOException
 * @throws BiffException
 */
private jxl.Workbook getOrCreateWorkbookJXL() throws IOException, BiffException {
	if (!hasWorkbook()) {
		WorkbookSettings workbookSettings = new WorkbookSettings();
		Optional.ofNullable(encoding).map(Charset::name).ifPresent(workbookSettings::setEncoding);
		workbookJXL = Workbook.getWorkbook(getFile(), workbookSettings);
	}
	return workbookJXL;
}
 

@Test
public void whenParsingJExcelFile_thenCorrect() throws IOException, BiffException {
    Map<Integer, List<String>> data = jExcelHelper.readJExcel(fileLocation);

    assertEquals("Name", data.get(0)
        .get(0));
    assertEquals("Age", data.get(0)
        .get(1));

    assertEquals("John Smith", data.get(2)
        .get(0));
    assertEquals("20", data.get(2)
        .get(1));
}
 

private boolean isTemplateContained( Workbook templateWorkbook, File targetFile )
     throws IOException, BiffException {
  Workbook targetFileWorkbook = Workbook.getWorkbook( targetFile );
  int templateWorkbookNumberOfSheets = templateWorkbook.getNumberOfSheets();
  int targetWorkbookNumberOfSheets = targetFileWorkbook.getNumberOfSheets();
  if ( templateWorkbookNumberOfSheets > targetWorkbookNumberOfSheets ) {
    return false;
  }

  for ( int worksheetNumber = 0; worksheetNumber < templateWorkbookNumberOfSheets; worksheetNumber++ ) {
    Sheet templateWorkbookSheet = templateWorkbook.getSheet( worksheetNumber );
    Sheet targetWorkbookSheet = targetFileWorkbook.getSheet( worksheetNumber );
    int templateWorkbookSheetColumns = templateWorkbookSheet.getColumns();
    int targetWorkbookSheetColumns = targetWorkbookSheet.getColumns();
    if ( templateWorkbookSheetColumns > targetWorkbookSheetColumns ) {
      return false;
    }
    int templateWorkbookSheetRows = templateWorkbookSheet.getRows();
    int targetWorkbookSheetRows = targetWorkbookSheet.getRows();
    if ( templateWorkbookSheetRows > targetWorkbookSheetRows ) {
      return false;
    }
    for ( int currentRowNumber = 0; currentRowNumber < templateWorkbookSheetRows; currentRowNumber++ ) {
      Cell[] templateWorkbookSheetRow = templateWorkbookSheet.getRow( currentRowNumber );
      Cell[] targetWorkbookSheetRow = targetWorkbookSheet.getRow( currentRowNumber );
      if ( templateWorkbookSheetRow.length > targetWorkbookSheetRow.length ) {
        return false;
      }
      for ( int currentCellNumber = 0; currentCellNumber < templateWorkbookSheetRow.length; currentCellNumber++ ) {
        Cell templateWorksheetCell = templateWorkbookSheetRow[currentCellNumber];
        Cell targetWorksheetCell = targetWorkbookSheetRow[currentCellNumber];
        if ( !templateWorksheetCell.getContents().equals( targetWorksheetCell.getContents() ) ) {
          return false;
        }
      }
    }
  }
  return true;
}
 

/**
 * Creates an excel table model (either {@link ExcelSheetTableModel} or
 * {@link XlsxSheetTableModel}, depending on file).
 *
 * @param sheetIndex
 *            the index of the sheet (0-based)
 * @param readMode
 *            the read mode for {@link XlsxSheetTableModel} creation. It defines whether only a
 *            preview or the whole sheet content will be loaded
 * @param progressListener
 *            the progress listener to report progress to
 * @return
 * @throws BiffException
 * @throws IOException
 * @throws InvalidFormatException
 */
public AbstractTableModel createExcelTableModel(int sheetIndex, XlsxReadMode readMode, ProgressListener progressListener)
		throws BiffException, IOException, InvalidFormatException, OperatorException, ParseException {
	return createExcelTableModel(ExcelSheetSelection.byIndex(sheetIndex), readMode, progressListener);
}