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));
    }
  }
Exemple #2
0
  public void insertDataToExcel(int numRow, Object[] object) {

    try {

      if (null != wb.getSheetAt(0)) {
        Sheet aSheet = wb.getSheetAt(0);
        Row row = aSheet.getRow((short) numRow);

        if (row == null) row = aSheet.createRow((short) numRow);

        for (int i = 0; i < object.length; i++) {
          Cell csCell = row.createCell((short) i);

          CellStyle style = wb.createCellStyle();
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          style.setRightBorderColor(HSSFColor.BLACK.index);
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          style.setTopBorderColor(HSSFColor.BLACK.index);

          csCell.setCellStyle(style);

          if (object[i] != null) csCell.setCellValue(object[i].toString());
          else csCell.setCellValue("0");
        }
      }

    } catch (Exception e) {

      System.out.println("insertDataToExcel" + e);
    }
  }
  public void removerCliente(String cpf) {

    int count = 1;
    boolean achou = false;
    int ultimaLinha = wb.getSheetAt(0).getLastRowNum();

    while (count < ultimaLinha && !achou) {

      if (wb.getSheetAt(0).getRow(count).getCell(1).equals(cpf)) {
        Row row = wb.getSheetAt(0).getRow(count);
        wb.getSheetAt(0).removeRow(row);
        achou = true;
      } else {
        count++;
      }
    }
    try {
      this.fos = new FileOutputStream("RepositorioCliente.xls");
      wb.write(fos);
      fos.close();

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  public void inserirCliente(Cliente cliente) {

    int count = 0;
    boolean achou = false;
    int ultimaLinha = wb.getSheetAt(0).getLastRowNum();
    // verifica se existe um espaço em null entre as linhas da planilha
    while (count <= ultimaLinha && !achou) {

      if (wb.getSheetAt(0).getRow(count) == null) {
        Row selectedRow = wb.getSheetAt(0).createRow(count);
        this.createRow(selectedRow, cliente);
        achou = true;
      } else if (count == ultimaLinha) {
        Row selectedRow = wb.getSheetAt(0).createRow(wb.getSheetAt(0).getLastRowNum() + 1);
        this.createRow(selectedRow, cliente);
        achou = true;
      }
      count++;
    }

    try {
      this.fos = new FileOutputStream("RepositorioCliente.xls");
      wb.write(fos);
      fos.close();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
Exemple #5
0
  public int getExcelLastCellNum() {

    int count = 0;
    if (null != wb.getSheetAt(0)) {
      Sheet aSheet = wb.getSheetAt(0);
      Row aRow = aSheet.getRow(0);
      count = aRow.getLastCellNum();
    }
    return count;
  }
Exemple #6
0
 public void insertFormula(int numRow, int numCol, String formula) {
   try {
     if (wb.getSheetAt(0) != null) {
       Sheet aSheet = wb.getSheetAt(0);
       Row row = aSheet.createRow((short) numRow);
       Cell csCell = row.createCell((short) numCol);
       csCell.setCellFormula(formula);
     }
   } catch (Exception e) {
     System.out.println("insertDataToExcel" + e);
   }
 }
  private void addSumBottom() {
    for (int i = 0; i < book.getNumberOfSheets(); i++) {
      Sheet sheet = book.getSheetAt(i);

      Row row = sheet.createRow(sheet.getLastRowNum() + 1);
      row.setHeight((short) (ROW_HEIGHT + 100));

      for (int j = 0; j < 1000000; j++) {
        if (StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(0).getCell(j)))
            && StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(2).getCell(j)))) {
          break;
        }
        Cell cell = row.createCell(j);
        cell.setCellStyle(Style.get(book).SUM);
        if (j == 0) {
          cell.setCellValue("合计");
        } else {
          cell.setCellValue(0);
        }

        if (j >= 7) {
          cell.setCellType(Cell.CELL_TYPE_FORMULA);
          cell.setCellFormula(
              String.format(
                  "SUM(%s%s:%s%s)",
                  CellUtils.convertToABC(j + 1),
                  5,
                  CellUtils.convertToABC(j + 1),
                  sheet.getLastRowNum()));
        }
      }
      sheet.addMergedRegion(
          new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 0, 6));
    }

    for (int i = 0; i < book.getNumberOfSheets(); i++) {
      Sheet sheet = book.getSheetAt(i);
      for (int j = 4; j <= sheet.getLastRowNum(); j++) {
        Row row = sheet.getRow(j);
        for (int k = 0; k <= row.getLastCellNum(); k++) {
          Cell cell = row.getCell(k);
          if (cell == null) {
            continue;
          }

          if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(k)))) {
            cell.setCellStyle(Style.get(book).SUM);
          }
        }
      }
    }
  }
Exemple #8
0
  public void insertNumToCell(int numRow, int numCol, Double num) {
    try {
      if (wb.getSheetAt(0) != null) {
        Sheet aSheet = wb.getSheetAt(0);
        Row row = aSheet.createRow((short) numRow);
        Cell csCell = row.createCell((short) numCol);
        csCell.setCellValue(num.doubleValue());
      }
    } catch (Exception e) {

      System.out.println("insertDataToExcel" + e);
    }
  }
Exemple #9
0
  public void insertStringToCell(int numRow, int numCol, String strval) {
    try {
      if (wb.getSheetAt(0) != null) {
        Sheet aSheet = wb.getSheetAt(0);
        Row row = aSheet.createRow((short) numRow);
        Cell csCell = row.createCell((short) numCol);
        //  csCell.setEncoding(HSSFCell.ENCODING_UTF_16);
        csCell.setCellValue(strval);
      }
    } catch (Exception e) {

      System.out.println("insertDataToExcel" + e);
    }
  }
  public <T> List<T> map() throws Throwable {
    InputStream inputStream = null;
    List<T> items = new LinkedList<>();

    try {
      Iterator<Row> rowIterator;
      inputStream = new FileInputStream(excelFile);
      Workbook workbook = createWorkbook(inputStream);
      int numberOfSheets = workbook.getNumberOfSheets();

      for (int index = 0; index < numberOfSheets; index++) {
        Sheet sheet = workbook.getSheetAt(index);
        rowIterator = sheet.iterator();

        Map<String, Integer> nameIndexMap = new HashMap<>();
        while (rowIterator.hasNext()) {
          Row row = rowIterator.next();
          if (row.getRowNum() == 0) {
            readExcelHeader(row, nameIndexMap);
          } else {
            items.add((T) readExcelContent(row, nameIndexMap));
          }
        }
      }
    } finally {
      if (inputStream != null) {
        inputStream.close();
      }
    }

    return items;
  }
Exemple #11
0
  @Override
  public void parse(InputStream in, ICallback callback) {
    Workbook wb = null;
    try {
      callback.begin();

      wb = WorkbookFactory.create(in);
      int sheetNumber = wb.getNumberOfSheets();
      for (int i = 0; i < sheetNumber; i++) {
        Sheet sheet = wb.getSheetAt(i);
        int line = this.startLine;
        Row row = null;
        while ((row = sheet.getRow(line)) != null) {
          Object[] data = this.readLineData(row);
          if (data != null) callback.visit(data, line);
          line++;
        }
      }

      callback.end();
    } catch (Exception e) {
      Log.logError(e.getMessage());
    } finally {
      try {
        in.close();
      } catch (Exception ex) {
      }
    }
  }
  public void rebuildFormula() {
    for (int i = 0; i < book.getNumberOfSheets(); i++) {
      rebuildFormula(book.getSheetAt(i));
    }

    addSumBottom();
  }
 /** Reads excel file and matches cells to values */
 public Stock readFile(int firstItem, int lastItem)
     throws IllegalStateException, InvalidFormatException, IOException {
   List<Item> stockList = new ArrayList();
   Workbook book = WorkbookFactory.create(this.sourceFile);
   Sheet workSheet = book.getSheetAt(sheet);
   Item emptyItem = new Item("Системе не удалось разпознать элемент", 0, 0, 0, 0, 0, 0, 0);
   for (int n = firstItem - 1; n < lastItem; n++) {
     try {
       Row row = workSheet.getRow(n);
       Item item =
           new Item(
               row.getCell(cellCodes[0]).toString(),
               row.getCell(cellCodes[1]).getNumericCellValue(),
               row.getCell(cellCodes[2]).getNumericCellValue(),
               row.getCell(cellCodes[3]).getNumericCellValue(),
               row.getCell(cellCodes[4]).getNumericCellValue(),
               row.getCell(cellCodes[5]).getNumericCellValue(),
               row.getCell(cellCodes[6]).getNumericCellValue(),
               row.getCell(cellCodes[7]).getNumericCellValue());
       stockList.add(item);
     } catch (NullPointerException ex) {
       stockList.add(emptyItem);
     }
   }
   // pkg.close();
   Stock stock = new Stock(stockList);
   return stock;
 }
  /**
   * Called to update the embedded Excel workbook. As the format and structire of the workbook are
   * known in advance, all this code attempts to do is write a new value into the first cell on the
   * first row of the first worksheet. Prior to executing this method, that cell will contain the
   * value 1.
   *
   * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException Rather than use the specific
   *     classes (HSSF/XSSF) to handle the embedded workbook this method uses those defeined in the
   *     SS stream. As a result, it might be the case that a SpreadsheetML file is opened for
   *     processing, throwing this exception if that file is invalid.
   * @throws java.io.IOException Thrown if a problem occurs in the underlying file system.
   */
  public void updateEmbeddedDoc() throws OpenXML4JException, IOException {
    Workbook workbook = null;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    PackagePart pPart = null;
    Iterator<PackagePart> pIter = null;
    List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
    if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
      pIter = embeddedDocs.iterator();
      while (pIter.hasNext()) {
        pPart = pIter.next();
        if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
            || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {

          // Get an InputStream from the pacage part and pass that
          // to the create method of the WorkbookFactory class. Update
          // the resulting Workbook and then stream that out again
          // using an OutputStream obtained from the same PackagePart.
          workbook = WorkbookFactory.create(pPart.getInputStream());
          sheet = workbook.getSheetAt(SHEET_NUM);
          row = sheet.getRow(ROW_NUM);
          cell = row.getCell(CELL_NUM);
          cell.setCellValue(NEW_VALUE);
          workbook.write(pPart.getOutputStream());
        }
      }

      // Finally, write the newly modified Word document out to file.
      this.doc.write(new FileOutputStream(this.docFile));
    }
  }
  @Test
  public void report_FacilityUsage_asOperator2() {
    final ReportParameters params = baseParams();
    params.interval = 3 * 60;
    registerMockFacilityUsages(facility1, apiUser);
    registerMockFacilityUsages(facility2, apiUser2);

    final Response whenPostingToReportUrl = postToReportUrl(params, "FacilityUsage", operator2User);

    // If this succeeds, the response was a valid excel file
    final Workbook workbook = readWorkbookFrom(whenPostingToReportUrl);
    assertThat(getSheetNames(workbook)).containsExactly("Käyttöasteraportti", "Selite");

    final Sheet usages = workbook.getSheetAt(0);
    // Header and one for each usage type
    assertThat(usages.getPhysicalNumberOfRows()).isEqualTo(3);

    // Only operator2 visible
    assertThat(getDataFromColumn(usages, 3))
        .containsOnly("Operaattori", operator2.name.fi)
        .doesNotContain(operator1.name.fi);

    final List<String> headers = getDataFromRow(usages, 0);
    assertThat(headers.subList(FACILITYUSAGE_FIRST_TIME_COLUMN, headers.size()))
        .containsExactly("00:00", "03:00", "06:00", "09:00", "12:00", "15:00", "18:00", "21:00");

    // Get the hourly utilizations for CAR
    // Results are not interpolated.
    final List<String> row = getDataFromRow(usages, 1);
    assertThat(row.subList(FACILITYUSAGE_FIRST_TIME_COLUMN, row.size()))
        .containsExactly("24", "24", "24", "24", "0", "0", "0", "24");
  }
  public static Date getBaseDateFromExcelWithPoi(File file) {
    InputStream in = null;
    try {
      in = new FileInputStream(file);
      Workbook workbook = WorkbookFactory.create(in);
      Sheet sheet = workbook.getSheetAt(0);
      Name name = workbook.getName("雷線基準日");
      CellReference cellRef = new CellReference(name.getRefersToFormula());
      Row row = sheet.getRow(cellRef.getRow());
      Cell baseDateCell = row.getCell(cellRef.getCol());
      // System.out.println("cellが日付か:"
      // + PoiUtil.isCellDateFormatted(baseDateCell));
      Date baseDate = baseDateCell.getDateCellValue();
      return baseDate;

    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (InvalidFormatException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      if (in != null)
        try {
          in.close();
        } catch (IOException e) {
          e.printStackTrace();
        }
    }
    return null;
  }
Exemple #17
0
  public void replaceDataToCell(int numRow, int numCol, String temstr, String strval) {
    try {
      if (wb.getSheetAt(0) != null) {
        Sheet aSheet = wb.getSheetAt(0);
        Row row = aSheet.getRow((short) numRow);
        Cell csCell = row.getCell((short) numCol);
        String temp = "";
        temp = csCell.getStringCellValue();
        temp = temp.replaceAll(temstr, strval);
        csCell.setCellValue(temp);
      }
    } catch (Exception e) {

      System.out.println("insertDataToExcel" + e);
    }
  }
Exemple #18
0
  public void make(File source, DataSet header, DataSet master) throws Exception {
    this.header = header;
    this.master = master;

    if (header == null || master == null) throw new Exception("Dataset is empty");

    long t = System.currentTimeMillis();

    InputStream inp = new FileInputStream(source);
    Workbook oldBook = WorkbookFactory.create(inp);
    Sheet oldSheet = oldBook.getSheetAt(0);

    Workbook newBook = new HSSFWorkbook();
    Sheet newSheet = newBook.createSheet(oldSheet.getSheetName());

    init(newBook);
    process(oldSheet, newSheet);

    File target = File.createTempFile("libra", ".xls");
    target.deleteOnExit();
    FileOutputStream fileOut = new FileOutputStream(target);

    newBook.write(fileOut);
    fileOut.close();
    oldBook.close();
    inp.close();

    Desktop.getDesktop().open(target);

    System.out.println(System.currentTimeMillis() - t);
  }
Exemple #19
0
  public List<ImportNeolixVO> extractNeolixInfo(File file) {

    List<ImportNeolixVO> neolix = new LinkedList<>();
    // 解析文件
    Workbook workBook = null;
    FileInputStream fis = null;
    try {
      fis = new FileInputStream(file);
    } catch (FileNotFoundException e) {
      throw new RuntimeException("没有找到对应的文件", e);
    }
    try {
      workBook = WorkbookFactory.create(fis);
      Sheet sheet = workBook.getSheetAt(0);
      int lastRowNumber = sheet.getLastRowNum();
      Row rowTitle = sheet.getRow(0);
      if (rowTitle == null) {
        neolix = null;
        return neolix;
      }
      // 从第1行开始(不算标题)
      for (int i = 1; i <= lastRowNumber; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
          continue;
        }
        ImportNeolixVO vo = convert(row);
        neolix.add(vo);
      }
    } catch (Exception e) {
      System.out.println(e.toString());
      neolix = null;
    }
    return neolix;
  }
  private static Sheet loadWorksheet(String filepath)
      throws InvalidFormatException, FileNotFoundException, IOException, ParseException {
    // open the excel file containing the interview
    FileInputStream file = new FileInputStream(new File(filepath));

    // have the WorkbookFactory interpret the file and an xlsx file

    Workbook workbook = WorkbookFactory.create(file);

    // retrieve the first sheet from the workbook
    Sheet sheet = workbook.getSheetAt(0);

    file.close();

    return sheet;
  }
  public void testRead() throws IOException, InvalidFormatException {
    InputStream inputXLS =
        new BufferedInputStream(getClass().getResourceAsStream(departmentDataXLS));
    Workbook hssfInputWorkbook = WorkbookFactory.create(inputXLS);
    Sheet sheet = hssfInputWorkbook.getSheetAt(0);
    List mappings = new ArrayList();
    Department department = new Department();
    Map beans = new HashMap();
    beans.put("department", department);
    mappings.add(new BeanCellMapping(7, (short) 0, "employee", "name"));
    mappings.add(new BeanCellMapping(7, (short) 1, "employee", "age"));
    mappings.add(new BeanCellMapping(7, (short) 3, "employee", "payment"));
    mappings.add(new BeanCellMapping(7, (short) 4, "employee", "bonus"));
    XLSBlockReader reader = new SimpleBlockReaderImpl(7, 7, mappings);
    XLSRowCursor cursor = new XLSRowCursorImpl(sheet);
    XLSLoopBlockReader forEachReader =
        new XLSForEachBlockReaderImpl(7, 7, "department.staff", "employee", Employee.class);
    forEachReader.addBlockReader(reader);
    SectionCheck loopBreakCheck = getLoopBreakCheck();
    forEachReader.setLoopBreakCondition(loopBreakCheck);
    cursor.setCurrentRowNum(7);

    forEachReader.read(cursor, beans);
    assertEquals(4, department.getStaff().size());
    Employee employee = (Employee) department.getStaff().get(0);
    checkEmployee(employee, "Oleg", new Integer(32), new Double(2000.0), new Double(0.20));
    employee = (Employee) department.getStaff().get(1);
    checkEmployee(employee, "Yuri", new Integer(29), new Double(1800.0), new Double(0.15));
    employee = (Employee) department.getStaff().get(2);
    checkEmployee(employee, "Leonid", new Integer(30), new Double(1700.0), new Double(0.20));
    employee = (Employee) department.getStaff().get(3);
    checkEmployee(employee, "Alex", new Integer(28), new Double(1600.0), new Double(0.20));
  }
Exemple #22
0
  public void loadData(Data data)
      throws EncryptedDocumentException, InvalidFormatException, IOException {

    FileInputStream file = new FileInputStream(new File(path));
    org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(file);
    org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);
    try {
      Iterator<Row> rowIterator = sheet.iterator();
      while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        ArrayList<String> arrayRow = new ArrayList<String>();
        while (cellIterator.hasNext()) {
          Cell cell = cellIterator.next();
          testDataType(cell, arrayRow);
        }
        data.getData().add(arrayRow);
      }

    } catch (Exception e) {
      e.printStackTrace();
    }
    workbook.close();
    file.close();
    sheet = null;
    workbook = null;
    file = null;
  }
  public Sheet getSheet(String fileName) throws IOException, InvalidFormatException {
    InputStream inp = new FileInputStream(fileName);

    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);
    return sheet;
  }
  @Override
  protected Workbook indexExcel(Map<String, String> params, Workbook excel) {
    List<Map<String, Object>> list = service.financialIndexData_02(params);
    Sheet sheet = excel.getSheetAt(0);

    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);
    cell.setCellValue(params.get("statMonth") + reportName);
    row = sheet.getRow(2);
    if (list != null && list.size() > 0) {
      for (Map<String, Object> map : list) {
        sheet = PoiUtil.copyLastRow(sheet, false);
        row = sheet.getRow(sheet.getLastRowNum() - 1);
        row.getCell(0).setCellValue(map.get("ENT_NAME").toString());
        row.getCell(1).setCellValue(StringUitl.obj2doule(map.get("TJ1")));
        row.getCell(2).setCellValue(StringUitl.obj2doule(map.get("TB1")));
        row.getCell(3).setCellValue(StringUitl.obj2doule(map.get("TJ2")));
        row.getCell(4).setCellValue(StringUitl.obj2doule(map.get("TB2")));
        row.getCell(5).setCellValue(StringUitl.obj2doule(map.get("TJ3")));
        row.getCell(6).setCellValue(StringUitl.obj2doule(map.get("TB3")));
      }
    }
    sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
    return excel;
  }
  @Test
  public void overrideRowsInTemplate() throws IOException {
    XSSFWorkbook template = new XSSFWorkbook();
    template.createSheet().createRow(1);

    Workbook wb = new SXSSFWorkbook(template);
    try {
      Sheet sheet = wb.getSheetAt(0);

      try {
        sheet.createRow(1);
        fail("expected exception");
      } catch (Throwable e) {
        assertEquals(
            "Attempting to write a row[1] in the range [0,1] that is already written to disk.",
            e.getMessage());
      }
      try {
        sheet.createRow(0);
        fail("expected exception");
      } catch (Throwable e) {
        assertEquals(
            "Attempting to write a row[0] in the range [0,1] that is already written to disk.",
            e.getMessage());
      }
      sheet.createRow(2);
    } finally {
      wb.close();
      template.close();
    }
  }
Exemple #26
0
  /**
   * public Result getDataBinding(String postcode) throws PostcodeNotFoundException {
   *
   * <p>Result response = null; try { String dir =
   * this.getClass().getClassLoader().getResource("").getPath(); response = mapper.readValue(new
   * File(dir.toString()+POSTCODE_FOLDER+File.separator+postcode+".json"), Result.class);
   * if(response == null){ throw new PostcodeNotFoundException("Postcode " +
   * postcode.toUpperCase()+" cannot be found."); } } catch (JsonParseException e) {
   * System.out.println("JsonParseException: "+ e.toString()); } catch (JsonMappingException e) {
   * System.out.println("JsonMappingException: "+ e.toString()); } catch (IOException e) {
   * System.out.println("IOException: "+ e.toString()); } countNum++; return response; }
   *
   * <p>public Result getDataBindingFirstTry(String postcode) {
   *
   * <p>Result response = null; try { String dir =
   * this.getClass().getClassLoader().getResource("").getPath(); response = mapper.readValue(new
   * File(dir.toString()+POSTCODE_FOLDER+File.separator+postcode+".json"), Result.class); }catch
   * (JsonParseException e) { System.out.println("JsonParseException: "+ e.toString()); } catch
   * (JsonMappingException e) { System.out.println("JsonMappingException: "+ e.toString()); } catch
   * (IOException e) { // System.out.println("IOException: "+ e.toString()); } return response; }
   *
   * <p>public Result getDataBindingReTry(String postcode, String bindingName){
   *
   * <p>Result response = null; try { String dir =
   * this.getClass().getClassLoader().getResource("").getPath(); response = mapper.readValue(new
   * File(dir.toString()+POSTCODE_FOLDER+File.separator+bindingName+".json"), Result.class); }catch
   * (JsonParseException e) { System.out.println("JsonParseException: "+ e.toString()); } catch
   * (JsonMappingException e) { System.out.println("JsonMappingException: "+ e.toString()); } catch
   * (IOException e) { System.out.println("IOException: "+ e.toString()); } countNum++; return
   * response;
   *
   * <p>}
   *
   * <p>public boolean saveJsonFileByPostcode(String postcode, String url) throws
   * PostcodeNotFoundException{
   *
   * <p>// System.setProperty("http.proxySet", "true"); //
   * System.setProperty("http.proxyHost","proxy.abdn.ac.uk"); //
   * System.setProperty("http.proxyPort", "8080"); URL website = null; ReadableByteChannel rbc =
   * null; FileOutputStream fos = null; try { website = new URL(url); rbc =
   * Channels.newChannel(website.openStream()); String dir =
   * this.getClass().getClassLoader().getResource("").getPath(); // System.out.println("dir:
   * "+dir+""+ POSTCODE_FOLDER+File.separator+postcode); File directory = new File(dir+""+
   * POSTCODE_FOLDER); if(!directory.exists()){ directory.mkdirs(); } fos = new FileOutputStream(new
   * File(dir.toString()+POSTCODE_FOLDER+File.separator+postcode+".json"));
   * fos.getChannel().transferFrom(rbc, 0, Long.MAX_VALUE); fos.flush(); fos.close(); return true;
   * }catch (MalformedURLException e) { e.printStackTrace(); }catch(FileNotFoundException e){ //
   * e.printStackTrace(); throw new PostcodeNotFoundException("Postcode "+postcode.toUpperCase()+"
   * cannot be found."); }catch (IOException e) { e.printStackTrace(); } return false; }
   *
   * <p>public String getURLByPostcode(String postcode){
   *
   * <p>String url = "http://uk-postcodes.com/postcode/"; url += postcode; url += ".json"; return
   * url; }
   */
  public void readExcel() {

    String path = this.getClass().getClassLoader().getResource("").getPath();
    File file = new File(path + File.separator + POSTCODE_TABLE);
    OPCPackage opcPackage = null;
    try {
      opcPackage = OPCPackage.open(file);
    } catch (InvalidFormatException e) {
      e.printStackTrace();
    }
    // Create Workbook instance for xlsx/xls file input stream
    Workbook workbook = null;
    Sheet sheet = null;
    if (POSTCODE_TABLE.toLowerCase().endsWith("xlsx")) {
      try {
        workbook = new XSSFWorkbook(opcPackage);
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
    // Get the number of sheets in the xlsx file
    int numberOfSheets = workbook.getNumberOfSheets();
    if (numberOfSheets > 0) {
      //			System.out.println("read the first sheet from the workbook...");
      sheet = workbook.getSheetAt(0);
    }
    read(sheet);
  }
  /**
   * @param excelType
   * @param inputStream
   * @param clazz
   * @param processor
   * @param listeners
   * @return
   */
  public <T extends PoiBaseBo> ExcelImportResult importExcelNoValid(
      ExcelType excelType,
      InputStream inputStream,
      Class<T> clazz,
      BatchProcessor<T> processor,
      List<ExcelImportListener> listeners) {
    visitorStart(listeners, clazz);
    ValidateMeta validateMeta = getClassValiateMeta(clazz);
    Workbook workbook = getTempleteWorkBook(excelType, inputStream);
    int sheetAt = PoiAnnotationResolver.getSheetAt(clazz);
    Sheet sheet = workbook.getSheetAt(sheetAt);
    int rowStart = PoiAnnotationResolver.getRowStart(clazz);
    int columnStart = PoiAnnotationResolver.getColumnStart(clazz);
    int rowEnd = PoiAnnotationResolver.getRowEnd(clazz);
    int columnEnd = PoiAnnotationResolver.getColumnEnd(clazz);
    rowEnd = rowEnd == -1 ? sheet.getLastRowNum() : rowEnd;
    columnEnd = columnEnd == -1 ? Integer.MAX_VALUE : columnEnd;

    ExcelImportResult processValidateResults = new ExcelImportResult(super.getFileRepository());

    int recordCount = rowEnd - rowStart + 1;
    int realCount = recordCount;
    int batchCount = (int) Math.ceil((double) realCount / super.getBatchSize());
    if (batchCount == 1) {
      super.setBatchSize(realCount);
    }
    this.setCountDownLatch(new CountDownLatch(batchCount));
    List<ValidateResult<T>> batchValidates = new ArrayList<ValidateResult<T>>();
    for (int i = 0; i < batchCount; i++) {
      int batchStart = (i) * super.getBatchSize() + rowStart;
      int batchEnd = (i + 1) * super.getBatchSize() + rowStart - 1;
      if (batchEnd - rowStart + 1 > realCount) {
        batchEnd = realCount + rowStart - 1;
      }
      List<T> batchRecord = batchRecord(columnStart, columnEnd, batchStart, batchEnd, sheet, clazz);
      List<ValidateResult<T>> batchValidate =
          batchValidate(
              validateMeta,
              columnStart,
              columnEnd,
              batchStart,
              batchEnd,
              sheet,
              clazz,
              batchRecord,
              0);
      batchValidates.addAll(batchValidate);
      int batchEndFlag = (i + 1) * super.getBatchSize();
      if (batchEndFlag > recordCount) {
        batchEndFlag = recordCount;
      }
      ;
      visitorImporting(listeners, (i) * super.getBatchSize() + 1, batchEndFlag, recordCount, clazz);
      super.getRecordProcessor().processRecords(batchValidate, processor);
    }
    processValidateResults.merge(
        processValidateResults(excelType, sheet, batchValidates, processor));
    visitorEnd(listeners, clazz);
    return processValidateResults;
  }
 private org.apache.poi.ss.usermodel.Row getRowForCoordinate(
     RowIndex rowIndex, SheetIndex sheetIndex) {
   Sheet sheet = workbook.getSheetAt(sheetIndex.value());
   org.apache.poi.ss.usermodel.Row row = sheet.getRow(rowIndex.value());
   if (row == null) row = sheet.createRow(rowIndex.value());
   return row;
 }
Exemple #29
0
  public static void generateTrainingDataFromFile(
      String
          fileLocation) // Requires that the original file had the metadata and requires that this
        // file is formated the same in first sheet
      {
    testDataLL = (LinkedList<String[]>) dataLL.clone();
    actualClassifications = (LinkedList<String>) classificationsLL.clone();

    FileInputStream file;
    try {
      file = new FileInputStream(new File(fileLocation));
      Workbook excelFile = new HSSFWorkbook(file);
      Sheet sheet1 = excelFile.getSheetAt(0); // Data sheet
      for (Row row : sheet1) {
        String data[] = new String[row.getPhysicalNumberOfCells() - 1];
        String classification = "";

        int offset =
            0; // Used so that we can declare an array of the size of the attributes without the
        // classification
        for (Cell cell : row) {
          int index = cell.getColumnIndex();
          if (classificationLocation != index) {
            data[index - offset] = cell.toString();
          } else {
            classification = cell.toString();
            offset++;
          }
        }

        // Even though data and classifications are not really used add it onto the end so it is
        // still complete for in the event they end up being used in a later version
        dataLL.add(data);
        classificationsLL.add(classification);

        trainingDataLL.add(data);
        knownClassifications.add(classification);

        // Check to see if we have seen that classification yet
        int occurrences = 0;
        for (int i = 0; i < classificationTypes.size() && occurrences == 0; i++) {
          if (classificationTypes.get(i).compareTo(classification) == 0) {
            occurrences = 1;
          }
        }
        if (occurrences == 0) {
          classificationTypes.add(classification);
        }
      }
      excelFile.close();
    } catch (FileNotFoundException e) {
      System.out.println("Error file not found");
      System.exit(0);
    } catch (IOException e) {
      System.out.println("Unable to read file, disk drive may be failing");
      e.printStackTrace();
      System.exit(0);
    }
  }
  public void atualizar(Cliente cliente) {
    String cpf = cliente.getCpf();
    int count = 1;
    boolean achou = false;
    int ultimaLinha = wb.getSheetAt(0).getLastRowNum();

    while (count < ultimaLinha && !achou) {

      if (wb.getSheetAt(0).getRow(count).getCell(0).equals(cpf)) {
        Row selectedRow = wb.getSheetAt(0).getRow(count);
        wb.getSheetAt(0).removeRow(selectedRow);
        this.createRow(selectedRow, cliente);
      } else {
        count++;
      }
    }
  }