private void ReadDataToArray(Workbook dataWb) {
   Connection con = DbConnection.getConnection();
   String DataSheetInsertQuery = "insert into DataSheet_tbl values(?,?,?);";
   for (int i = 1; i < dataWb.getSheet("Data").getPhysicalNumberOfRows(); i++) {
     if (!dataWb.getSheet("Data").getRow(i).getCell(0).getDateCellValue().equals(null)) {
       try {
         preparedStatement = con.prepareStatement(DataSheetInsertQuery);
         preparedStatement.setString(
             1,
             String.valueOf(dataWb.getSheet("Data").getRow(i).getCell(1).getNumericCellValue()));
         preparedStatement.setDate(
             2,
             new Date(
                 (dataWb.getSheet("Data").getRow(i).getCell(0).getDateCellValue()).getTime()));
         preparedStatement.setString(
             3,
             String.valueOf(dataWb.getSheet("Data").getRow(i).getCell(2).getNumericCellValue()));
         preparedStatement.executeUpdate();
       } catch (SQLException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
       }
     }
   }
   DbConnection.closePreparedStatement(preparedStatement);
   DbConnection.closeConnection(con);
   System.out.println("All the data in the sheet is analysed and updated to memory");
 }
 /**
  * @param data
  * @param fileType
  */
 private void buildWorkBook(byte[] data, FileType fileType) {
   ByteArrayInputStream bs = new ByteArrayInputStream(data);
   try {
     if (fileType == FileType.XLSX) workbook = new XSSFWorkbook(bs);
     else if (fileType == FileType.XLS) {
       workbook = new HSSFWorkbook(bs);
     }
     sheetEvent = workbook.getSheet("Event Details");
     sheetParticipants = workbook.getSheet("Participants Details");
   } catch (IOException e) {
     sLogger.error("Issues with files", e);
   }
 }
Ejemplo n.º 3
0
  public static void main(String args[]) {

    try {
      String fileName = "C:/Users/NITHA/Desktop/Task.xlsx";
      // Create the input stream from the xlsx/xls file
      FileInputStream fis = new FileInputStream(fileName);

      // Create Workbook instance for xlsx/xls file input stream
      Workbook workbook = null;
      if (fileName.toLowerCase().endsWith("xlsx")) {
        workbook = new XSSFWorkbook(fis);
      } else if (fileName.toLowerCase().endsWith("xls")) {
        workbook = new HSSFWorkbook(fis);
      }

      // Get the nth sheet from the workbook
      Sheet sheet = workbook.getSheet(HEADER_VALUES);

      retrieveSheetInformation(sheet);

      // close file input stream
      fis.close();

    } catch (IOException e) {
      e.printStackTrace();
    }
  }
Ejemplo n.º 4
0
  /**
   * get the specified excel sheet and put its value string to list.
   *
   * @param sheetName excel sheet name
   * @param ignoreRows first several rows not to read.
   * @param ignoreCols first several cols not to read.
   * @param readRows specified row count to read.
   * @param readColumns specified column count to read.
   * @throws RuntimeException
   */
  public List<String> excelToList(
      String sheetName, int ignoreRows, int ignoreCols, int readRows, int readColumns) {
    FileInputStream fso = null;
    List<String> paraList = new ArrayList<String>();

    try {
      fso = new FileInputStream(fileName);
      Workbook workBook = getWorkBook(fso, true);
      xlSheet = workBook.getSheet(sheetName);
      if (xlSheet == null) {
        LOG.error("sheet [" + sheetName + "] does not exist!");
        throw new RuntimeException("sheet [" + sheetName + "] does not exist!");
      }
      readRows = (readRows == 0) ? xlSheet.getPhysicalNumberOfRows() : readRows;
      for (int i = ignoreRows; i < ignoreRows + readRows; i++) {
        xlRow = xlSheet.getRow(i);
        readColumns = (readColumns == 0) ? xlRow.getPhysicalNumberOfCells() : readColumns;
        if (xlRow != null) {
          for (int j = ignoreCols; j < ignoreCols + readColumns; j++) {
            xlCell = xlRow.getCell(j);
            if (xlCell == null) {
              paraList.add("");
            } else {
              paraList.add(xlCell.toString());
            }
          }
        }
      }
      fso.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException("read excel failed:" + e.getMessage());
    }
    return paraList;
  }
  public void loadBeachLocations() {

    try {
      FileInputStream file = new FileInputStream(new File("FISHERFOLK TEAM DATA SUMMARY.xls"));
      workbook = new HSSFWorkbook(file);
      Sheet sheet = workbook.getSheet("BEACH  SUMMARY");
      Iterator<Row> rowIterator = sheet.iterator();
      rowIterator.next(); // Skip the header row.
      while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        String code = row.getCell(1).getStringCellValue().trim();
        System.out.println(code);
        if (!code.isEmpty()) {

          String beachname = row.getCell(1).getStringCellValue().trim();
          String county = row.getCell(2).getStringCellValue().trim();
          String description = row.getCell(1).getStringCellValue().trim() + "Beach";
          System.out.println(beachname + ":" + county + ":" + description);

          Beach beach = new Beach(beachname, description, county);
          beach.saveBeach();
        }
      }
      JOptionPane.showMessageDialog(null, "Done Adding Beaches Locations....");
    } catch (IOException ex) {
      ex.printStackTrace();
      Logger.getLogger(Reporting.class.getName()).log(Level.SEVERE, null, ex);
    }
  }
  public void parse() throws FileNotFoundException, IOException, InvalidFormatException {

    InputStream inp;
    inp = new FileInputStream(fileName);
    Workbook wb = WorkbookFactory.create(inp);

    Row row;
    Sheet sheet = wb.getSheet(sheetName);
    int startingRow = 0;
    boolean breakNow = false;
    for (int i = startingRow; i <= sheet.getLastRowNum(); i++) {
      if (breakNow) {
        break;
      }
      row = sheet.getRow(i);
      if (row == null) {
        break;
      }

      for (int j = 0; j < row.getLastCellNum(); j++) {
        if (row.getCell(j).getStringCellValue().isEmpty()
            || row.getCell(j).getStringCellValue() == null) {
          breakNow = true;
          break;
        }
        //                category.setCategoryName(row.getCell(j).getStringCellValue());
      }
    }

    inp.close();
  }
 private Transaction parseAsTransaction(Row row) {
   String savingsAccountIdCheck = readAsInt(SAVINGS_ACCOUNT_NO_COL, row);
   if (!savingsAccountIdCheck.equals("")) savingsAccountId = savingsAccountIdCheck;
   String transactionType = readAsString(TRANSACTION_TYPE_COL, row);
   String amount = readAsDouble(AMOUNT_COL, row).toString();
   String transactionDate = readAsDate(TRANSACTION_DATE_COL, row);
   String paymentType = readAsString(PAYMENT_TYPE_COL, row);
   String paymentTypeId = getIdByName(workbook.getSheet("Extras"), paymentType).toString();
   String accountNumber = readAsLong(ACCOUNT_NO_COL, row);
   String checkNumber = readAsLong(CHECK_NO_COL, row);
   String routingCode = readAsLong(ROUTING_CODE_COL, row);
   String receiptNumber = readAsLong(RECEIPT_NO_COL, row);
   String bankNumber = readAsLong(BANK_NO_COL, row);
   return new Transaction(
       amount,
       transactionDate,
       paymentTypeId,
       accountNumber,
       checkNumber,
       routingCode,
       receiptNumber,
       bankNumber,
       Integer.parseInt(savingsAccountId),
       transactionType,
       row.getRowNum());
 }
Ejemplo n.º 8
0
  private void initActiveSheet() {
    if (dataFile == null) return;

    if (sheetIndex > -1) {
      dataSheet = dataFile.getSheetAt(sheetIndex);
      if (dataSheet == null) {
        throw new IndexOutOfBoundsException(
            "Sheet with index "
                + sheetIndex
                + " does not exist in file: "
                + inputFile.getFullPath());
      }
    } else if (sheetName != null) {
      dataSheet = dataFile.getSheet(sheetName);
      if (dataSheet == null) {
        throw new IllegalArgumentException(
            "Sheet with name " + sheetName + " does not exist in file: " + inputFile.getFullPath());
      }
    } else {
      int index = dataFile.getActiveSheetIndex();
      dataSheet = dataFile.getSheetAt(index);
    }
    headerColumns = null;
    int numMergedRegions = dataSheet.getNumMergedRegions();
    mergedRegions = new ArrayList<>(numMergedRegions);
    for (int i = 0; i < numMergedRegions; i++) {
      mergedRegions.add(dataSheet.getMergedRegion(i));
    }
  }
Ejemplo n.º 9
0
 /**
  * write excel sheet, specified value to specified cell.
  *
  * @param sheetName excel sheet name
  * @param row row index which to be changed
  * @param col column index which to be changed
  * @param value value to be put into cell
  * @throws RuntimeException
  */
 public void setExcelValue(String sheetName, int row, int col, String value) {
   Workbook workBook = null;
   try {
     if (new File(fileName).exists()) {
       workBook = getWorkBook(new FileInputStream(fileName), false);
     } else {
       workBook = getWorkBook(null, false);
     }
     xlSheet = workBook.getSheet(sheetName);
     if (xlSheet == null) {
       xlSheet = workBook.createSheet(sheetName);
     }
     xlRow = xlSheet.getRow(row - 1);
     if (xlRow == null) {
       xlRow = xlSheet.createRow((short) row - 1);
     }
     xlCell = xlRow.getCell(col - 1);
     if (xlCell == null) {
       xlCell = xlRow.createCell(col - 1);
     }
     xlCell.setCellType(1); // set cell type as string
     xlCell.setCellValue(value);
     FileOutputStream fileOut = new FileOutputStream(fileName);
     workBook.write(fileOut);
     fileOut.flush();
     fileOut.close();
   } catch (Exception e) {
     LOG.error(e);
     throw new RuntimeException("set excel value failed:" + e.getMessage());
   }
 }
  private void generateEnforcementsRegion(Workbook wb, StringBuilder sb) {
    // Get the Enforcements Sheet
    Sheet sheet = wb.getSheet("Enforcements");
    Iterator<Row> rowIt = sheet.rowIterator();
    // Ignore the Header row
    rowIt.next();

    while (rowIt.hasNext()) {
      Row row = rowIt.next();
      Cell cellId = row.getCell(0);

      if (cellId != null) {
        int id = (int) cellId.getNumericCellValue();
        Cell cellName = row.getCell(1);
        String name = cellName.getStringCellValue().replaceAll("\"", "'");
        Cell cellDescription = row.getCell(2);
        String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
        Cell cellType = row.getCell(3);
        String type = cellType.getStringCellValue();
        sb.append("Enforcement En" + id + " {");
        sb.append("\n");
        sb.append("\tName \"" + name + "\"");
        sb.append("\n");
        sb.append("\tDescription \"" + description + "\"");
        sb.append("\n");
        sb.append("\tType " + type);
        sb.append("\n}");
        sb.append("\n\n");
      } else break;
    }
  }
Ejemplo n.º 11
0
 public void rowCount(String sheetName)
     throws EncryptedDocumentException, InvalidFormatException, IOException {
   FileInputStream fis = new FileInputStream(filepath);
   Workbook wb = WorkbookFactory.create(fis);
   Sheet sh = wb.getSheet(sheetName);
   int rowcont = sh.getLastRowNum() + 1;
   System.out.println(rowcont);
 }
  private void generateServicesRegion(Workbook wb, StringBuilder sb) {
    // Get the Services Sheet
    Sheet sheet = wb.getSheet("Services");
    Iterator<Row> rowIt = sheet.rowIterator();
    // Ignore the Header row
    rowIt.next();

    while (rowIt.hasNext()) {
      Row row = rowIt.next();
      Cell cellId = row.getCell(0);

      if (cellId != null) {
        int id = (int) cellId.getNumericCellValue();
        Cell cellName = row.getCell(1);
        String name = cellName.getStringCellValue().replaceAll("\"", "'");
        Cell cellDescription = row.getCell(2);
        String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
        Cell cellPrivateData = row.getCell(3);
        Cell cellPartOf = row.getCell(4);
        sb.append("Service S" + id + " {");
        sb.append("\n");
        sb.append("\tName \"" + name + "\"");
        sb.append("\n");
        sb.append("\tDescription \"" + description + "\"");
        sb.append("\n");

        if (cellPrivateData.getCellType() == Cell.CELL_TYPE_NUMERIC) {
          int privateData = (int) cellPrivateData.getNumericCellValue();
          sb.append("\tRefersTo PrivateData PD" + privateData);
          sb.append("\n");
        } else if (cellPrivateData.getCellType() == Cell.CELL_TYPE_STRING) {
          String privateData = cellPrivateData.getStringCellValue();

          if (privateData.equals("All")) {
            sb.append("\tRefersTo PrivateData All");
          } else {
            sb.append("\tRefersTo PrivateData ");

            for (String s : privateData.split(", ")) {
              sb.append("PD" + s + ",");
            }
            // Delete last ','
            sb.deleteCharAt(sb.length() - 1);
          }
          sb.append("\n");
        }

        if (cellPartOf.getCellType() == Cell.CELL_TYPE_NUMERIC) {
          int partOf = (int) cellPartOf.getNumericCellValue();
          sb.append("\tService_Part S" + partOf);
          sb.append("\n");
        }

        sb.append("}");
        sb.append("\n\n");
      } else break;
    }
  }
Ejemplo n.º 13
0
  public static Sheet readExcelFile(String filePath, String sheetName) throws Exception {

    File f = new File(filePath);
    FileInputStream in = new FileInputStream(f);
    Workbook wrkBook = (Workbook) WorkbookFactory.create(in);
    Sheet merchantsheet = wrkBook.getSheet(sheetName);

    return merchantsheet;
  }
  private void generateRecipientsRegion(Workbook wb, StringBuilder sb) {
    // Get the Recipients Sheet
    Sheet sheet = wb.getSheet("Recipients");
    Iterator<Row> rowIt = sheet.rowIterator();
    // Ignore the Header row
    rowIt.next();

    while (rowIt.hasNext()) {
      Row row = rowIt.next();
      Cell cellId = row.getCell(0);

      if (cellId != null) {
        if (cellId.getCellType() == Cell.CELL_TYPE_NUMERIC) {
          int id = (int) cellId.getNumericCellValue();
          Cell cellDescription = row.getCell(1);
          String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
          Cell cellScope = row.getCell(2);
          String scope = cellScope.getStringCellValue();

          if (scope.contains("/")) {
            scope = "Internal/External";
          } else {
            scope = scope.substring(0, 1).toUpperCase() + scope.substring(1);
          }

          Cell cellType = row.getCell(3);
          String type = cellType.getStringCellValue();

          if (type.contains("/")) {
            type = "Individual/Organization";
          } else {
            type = type.substring(0, 1).toUpperCase() + type.substring(1);
          }

          Cell cellPartOf = row.getCell(4);
          sb.append("Recipient R" + id + " {");
          sb.append("\n");
          sb.append("\tName \"" + description + "\"");
          sb.append("\n");
          sb.append("\tDescription \"" + description + "\"");
          sb.append("\n");

          if (cellPartOf.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            int partOf = (int) cellPartOf.getNumericCellValue();
            sb.append("\tRecipient_Part R" + partOf);
            sb.append("\n");
          }

          sb.append("\tScope " + scope);
          sb.append("\n");
          sb.append("\tType " + type);
          sb.append("\n}");
          sb.append("\n\n");
        }
      } else break;
    }
  }
Ejemplo n.º 15
0
 public String getTestData(String sheetName, int rowNum, int colNum)
     throws EncryptedDocumentException, InvalidFormatException, IOException {
   FileInputStream fis = new FileInputStream(filepath);
   Workbook wb = WorkbookFactory.create(fis);
   Sheet sh = wb.getSheet(sheetName);
   Row row = sh.getRow(rowNum);
   String data = row.getCell(colNum).getStringCellValue();
   return data;
 }
Ejemplo n.º 16
0
  private void openWorkbook(Workbook workbook) {
    ObservableList<String> sheets = new ObservableListWrapper<>(new ArrayList<>());
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
      worksheetSelector.getItems().add(workbook.getSheetName(i));
    }

    sheetReader = (String sheetName) -> readSheet(workbook.getSheet(sheetName));

    Platform.runLater(() -> worksheetSelector.getSelectionModel().selectFirst());
  }
 public Integer getColumnCount()
     throws FileNotFoundException, IOException, InvalidFormatException {
   InputStream inp;
   inp = new FileInputStream(fileName);
   Workbook wb = WorkbookFactory.create(inp);
   Sheet sheet = wb.getSheet(sheetName);
   Row row = sheet.getRow(0);
   int numColumns = row.getLastCellNum();
   inp.close();
   return numColumns;
 }
Ejemplo n.º 18
0
 /**
  * Opens a excel sheet
  *
  * @param fileName name of the file where you want data
  * @param sheetName name of the sheet in the excel file
  */
 public ParsingExcel(String fileName, String sheetName) {
   System.out.println(fileName);
   try {
     File file = new File(fileName);
     System.out.println(file.getAbsolutePath());
     if (fileName.indexOf("xlsx") < 0) { // for .xls format
       wb = new HSSFWorkbook(new FileInputStream(file));
       ws = wb.getSheet(sheetName);
     } else { // for .xlsx format
       wb = new XSSFWorkbook(fileName);
       ws = (XSSFSheet) wb.getSheet(sheetName);
     }
   } catch (IOException io) {
     throw new Error(
         "Invalid file '"
             + fileName
             + "' or incorrect sheet '"
             + sheetName
             + "', enter a valid one");
   }
 }
Ejemplo n.º 19
0
  /**
   * put dataList to excel sheets.
   *
   * @param sheetName excel sheet name.
   * @param dataList data list to be parsed and put into excel sheets.
   * @param rowNum row count of the sheet to be modified.
   * @param ignoreRows rows to skip when put value.
   * @param ignoreColumns columns to skip when put value.
   * @throws RuntimeException
   * @throws IllegalArgumentException
   */
  public void putListToExcelWithFullIgnore(
      String sheetName, List<String> dataList, int rowNum, int ignoreRows, int ignoreColumns) {
    if (dataList.size() % rowNum != 0) {
      LOG.error("dataList has wrong element count for excel!");
      throw new IllegalArgumentException("dataList has wrong element count for excel!");
    }

    String value = null;
    int index = 0;
    final int colCount = dataList.size() / rowNum;
    Workbook workBook = null;

    try {
      if (new File(fileName).exists()) {
        workBook = getWorkBook(new FileInputStream(fileName), false);
      } else {
        workBook = getWorkBook(null, false);
      }
      xlSheet = workBook.getSheet(sheetName);
      if (xlSheet == null) {
        xlSheet = workBook.createSheet(sheetName);
      }

      for (int j = ignoreRows; j < ignoreRows + rowNum; j++) {
        xlRow = xlSheet.getRow(j);
        if (xlRow == null) {
          xlRow = xlSheet.createRow(j);
        }
        for (int i = ignoreColumns; i < ignoreColumns + colCount; i++) {
          value = dataList.get(index);
          xlCell = xlRow.getCell(i);
          if (xlCell == null) {
            xlCell = xlRow.createCell(i);
          }

          xlCell.setCellType(1);
          if (value != null) {
            xlCell.setCellValue(value);
          }
          index++;
        }
      }

      FileOutputStream fileOut = new FileOutputStream(fileName);
      workBook.write(fileOut);
      fileOut.flush();
      fileOut.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException("set excel value failed:" + e.getMessage());
    }
  }
Ejemplo n.º 20
0
  /**
   * read excel Xls and add the result into arraylist.
   *
   * @param sheetName excel sheet name
   * @throws RuntimeException
   */
  public List<Map<String, String>> excelToList(String sheetName) {
    Row firstxlRow = null;
    FileInputStream fso = null;
    List<Map<String, String>> paraList = new ArrayList<Map<String, String>>();

    try {
      fso = new FileInputStream(fileName);
      Workbook workBook = getWorkBook(fso, true);
      xlSheet = workBook.getSheet(sheetName);
      if (xlSheet == null) {
        LOG.error("sheet [" + sheetName + "] does not exist!");
        return null;
      }
      firstxlRow = xlSheet.getRow(xlSheet.getFirstRowNum());
      int firstCell = firstxlRow.getFirstCellNum();
      int lastCell = firstxlRow.getLastCellNum();
      List<String> keyList = new ArrayList<String>();

      for (int cNum = firstCell; cNum < lastCell; cNum++) {
        if (firstxlRow.getCell(cNum).toString() == null) {
          break;
        }
        keyList.add(firstxlRow.getCell(cNum).toString());
      }

      for (int i = xlSheet.getFirstRowNum() + 1; i < xlSheet.getPhysicalNumberOfRows(); i++) {
        xlRow = xlSheet.getRow(i);
        List<String> valueList = new ArrayList<String>();
        if (xlRow == null) {
          break;
        }
        for (int j = firstCell; j < lastCell; j++) {
          xlCell = xlRow.getCell(j);
          if (xlCell == null) {
            valueList.add(null);
            continue;
          } else {
            valueList.add(xlCell.toString());
          }
        }
        paraList.add(creatMap(keyList, valueList));
      }
      fso.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException("read excel failed:" + e.getMessage());
    }
    return paraList;
  }
Ejemplo n.º 21
0
 public String setTestData(String sheetName, int rowNum, int colNum, String data)
     throws EncryptedDocumentException, InvalidFormatException, IOException {
   FileInputStream fis = new FileInputStream(filepath);
   Workbook wb = WorkbookFactory.create(fis);
   Sheet sh = wb.getSheet(sheetName);
   Row row = sh.getRow(rowNum);
   Cell cel = row.createCell(colNum);
   cel.setCellType(Cell.CELL_TYPE_STRING);
   cel.setCellValue(data);
   FileOutputStream fos = new FileOutputStream(filepath);
   cel.setCellValue(data);
   wb.write(fos);
   wb.close();
   return data;
 }
Ejemplo n.º 22
0
  @Test
  public void test() throws Throwable {
    Workbook wb =
        WorkbookFactory.create(new File(Resources.getResource("excel/employees.xlsx").getFile()));
    Sheet sheet = wb.getSheet("employees");
    Iterator<Row> itr = sheet.iterator();
    itr.next();
    List<Employee> employees = new LinkedList<>();
    while (itr.hasNext()) {
      employees.add(populate(itr.next()));
    }
    IOUtils.closeQuietly(wb);

    Assert.assertEquals(2, employees.size());
    TestUtils.print(employees);
  }
Ejemplo n.º 23
0
 public int getRowCount(String sheetName) {
   int retVal = 0;
   try {
     FileInputStream fis = new FileInputStream(path1);
     Workbook wb = WorkbookFactory.create(fis);
     Sheet s = wb.getSheet(sheetName);
     retVal = s.getLastRowNum();
   } catch (FileNotFoundException e) {
     e.printStackTrace();
   } catch (InvalidFormatException e) {
     e.printStackTrace();
   } catch (IOException e) {
     e.printStackTrace();
   }
   return retVal;
 }
 @Override
 public Result parse() {
   Result result = new Result();
   Sheet savingsTransactionSheet = workbook.getSheet("RecurringDepositTransaction");
   Integer noOfEntries = getNumberOfRows(savingsTransactionSheet, AMOUNT_COL);
   for (int rowIndex = 1; rowIndex < noOfEntries; rowIndex++) {
     Row row;
     try {
       row = savingsTransactionSheet.getRow(rowIndex);
       if (isNotImported(row, STATUS_COL)) savingsTransactions.add(parseAsTransaction(row));
     } catch (Exception e) {
       logger.error("row = " + rowIndex, e);
       result.addError("Row = " + rowIndex + " , " + e.getMessage());
     }
   }
   return result;
 }
  @Override
  public void run(TaskMonitor tm) throws Exception {
    tm.setTitle("Loading network from table");
    tm.setProgress(0.0);
    tm.setStatusMessage("Loading network...");

    Workbook workbook = null;

    // Load Spreadsheet data for preview.
    if (fileType != null
        && (fileType.equalsIgnoreCase(SupportedFileType.EXCEL.getExtension())
            || fileType.equalsIgnoreCase(SupportedFileType.OOXML.getExtension()))
        && workbook == null) {
      try {
        workbook = WorkbookFactory.create(is);
      } catch (InvalidFormatException e) {
        throw new IllegalArgumentException(
            "Could not read Excel file.  Maybe the file is broken?", e);
      } finally {
        if (is != null) is.close();
      }
    }

    try {
      if (this.fileType.equalsIgnoreCase(SupportedFileType.EXCEL.getExtension())
          || this.fileType.equalsIgnoreCase(SupportedFileType.OOXML.getExtension())) {
        String networkName = ntmp.getName();

        if (networkName == null) networkName = workbook.getSheetName(0);

        final Sheet sheet = workbook.getSheet(networkName);

        reader =
            new ExcelNetworkSheetReader(
                networkName, sheet, ntmp, nMap, rootNetwork, serviceRegistrar);
      } else {
        reader = new NetworkTableReader(inputName, is, ntmp, nMap, rootNetwork, serviceRegistrar);
      }
    } catch (Exception ioe) {
      tm.showMessage(TaskMonitor.Level.ERROR, "Unable to read table: " + ioe.getMessage());
      return;
    }

    loadNetwork(tm);
    tm.setProgress(1.0);
  }
  public void generateReport() throws IOException {
    System.out.println("Test Report Gen");
    Sheet sheet = workbook.getSheet("sheet1");

    try {
      String strSql = "Select * from participant;";
      Sql db = new Sql();
      ResultSet rs = db.executeQuery(strSql);
      ResultSetMetaData rsMeta = rs.getMetaData();

      Row row = sheet.createRow(0);
      Cell cell;
      // get table columns which will act as title columns
      for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
        String colName = rsMeta.getColumnName(i);
        int dataType = rsMeta.getColumnType(i);
        cell = row.createCell(i - 1);
        cell.setCellValue(colName);
      }

      int i = 1;
      while (rs.next()) {
        // create row to write data to
        row = sheet.createRow(i);
        // create cells for writing data to
        for (int c = 1; c <= rsMeta.getColumnCount(); c++) {
          cell = row.createCell(c - 1);
          String colName = rsMeta.getColumnName(c);
          cell.setCellValue(rs.getString(colName));
        }

        i++;
      }

      JOptionPane.showMessageDialog(null, "Excel Report of All Participants Generated");
    } catch (SQLException ex) {
      ex.printStackTrace();
      // log Error
      FacesFingerPrintProject.logger.log(Level.SEVERE, "ERROR", ex);
    }

    /// Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("reportTest.xls");
    workbook.write(fileOut);
    fileOut.close();
  }
Ejemplo n.º 27
0
 public String getExcelData(String sheetName, int rowNum, int colNum) {
   String retVal = null;
   try {
     FileInputStream fis = new FileInputStream(path1);
     Workbook wb = WorkbookFactory.create(fis);
     Sheet s = wb.getSheet(sheetName);
     Row r = s.getRow(rowNum);
     Cell c = r.getCell(colNum);
     retVal = c.getStringCellValue();
   } catch (FileNotFoundException e) {
     e.printStackTrace();
   } catch (InvalidFormatException e) {
     e.printStackTrace();
   } catch (IOException e) {
     e.printStackTrace();
   }
   return retVal;
 }
  private void generatePrivateDataRegion(Workbook wb, StringBuilder sb) {
    // Get the Private Data Sheet
    Sheet sheet = wb.getSheet("PrivateData");
    Iterator<Row> rowIt = sheet.rowIterator();
    // Ignore the Header row
    rowIt.next();

    while (rowIt.hasNext()) {
      Row row = rowIt.next();
      Cell cellId = row.getCell(0);

      if (cellId != null) {
        int id = (int) cellId.getNumericCellValue();
        Cell cellType = row.getCell(1);
        String type = cellType.getStringCellValue().replaceAll(" ", "");
        Cell cellDescription = row.getCell(2);
        String description = cellDescription.getStringCellValue().replaceAll("\"", "'");
        Cell cellAttributes = row.getCell(3);
        String attributes = cellAttributes.getStringCellValue();
        sb.append("PrivateData PD" + id + " {");
        sb.append("\n");
        sb.append("\tName \"PD" + id + "\"");
        sb.append("\n");
        sb.append("\tDescription \"" + description + "\"");
        sb.append("\n");
        sb.append("\tType " + type);
        sb.append("\n");

        for (String a : attributes.split(",\n")) {
          a = a.substring(0, 1).toUpperCase() + a.substring(1);
          sb.append("\tAttribute \"" + a + "\" {");
          sb.append("\n");
          sb.append("\t\tDescription \"" + a + "\"");
          sb.append("\n\t}");
          sb.append("\n");
        }
        // Delete last '\n'
        //	    		sb.deleteCharAt(sb.length() - 2);
        sb.deleteCharAt(sb.length() - 1);
        sb.append("\n}");
        sb.append("\n\n");
      } else break;
    }
  }
Ejemplo n.º 29
0
 public void writeToExcel(String sheetName, int rowNum, int colNum, String desc) {
   try {
     FileInputStream fis = new FileInputStream(path1);
     Workbook wb = WorkbookFactory.create(fis);
     Sheet s = wb.getSheet(sheetName);
     Row r = s.getRow(rowNum);
     Cell c = r.createCell(colNum);
     c.setCellValue(desc);
     FileOutputStream fos = new FileOutputStream(path1);
     wb.write(fos);
     fos.close();
   } catch (FileNotFoundException e) {
     e.printStackTrace();
   } catch (InvalidFormatException e) {
     e.printStackTrace();
   } catch (IOException e) {
     e.printStackTrace();
   }
 }
  public String[] getHeaders() throws FileNotFoundException, IOException, InvalidFormatException {
    InputStream inp;
    inp = new FileInputStream(fileName);
    Workbook wb = WorkbookFactory.create(inp);
    List<String> listHeaders = new ArrayList();

    Row row;
    Sheet sheet = wb.getSheet(sheetName);
    row = sheet.getRow(0);
    for (int j = 0; j < row.getLastCellNum(); j++) {
      if (row.getCell(j).getStringCellValue().isEmpty()
          || row.getCell(j).getStringCellValue() == null) {
        break;
      }
      listHeaders.add(row.getCell(j).getStringCellValue());
    }
    inp.close();
    return listHeaders.toArray(new String[listHeaders.size()]);
  }