Example #1
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);
  }
  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;
  }
Example #3
0
 /**
  * 批量导出
  *
  * @param data
  * @throws FileNotFoundException
  */
 public void transform(Map<String, Object> data) throws FileNotFoundException {
   FileInputStream templateIs = new FileInputStream(template);
   try {
     XSSFWorkbook bookTmp = (XSSFWorkbook) transformer.transformXLS(templateIs, data);
     bookTmp.setSheetName(0, "第" + count + "页");
     if (count == 1) {
       book = new SXSSFWorkbook(bookTmp);
     } else {
       Sheet newSheet = book.createSheet(bookTmp.getSheetName(0));
       Sheet sheet = bookTmp.getSheetAt(0);
       Util.copySheets(newSheet, sheet);
     }
     count++;
   } catch (ParsePropertyException e) {
     e.printStackTrace();
   } catch (InvalidFormatException e) {
     e.printStackTrace();
   } finally {
     try {
       templateIs.close();
     } catch (IOException e) {
       e.printStackTrace();
     }
   }
 }
Example #4
0
 /**
  * 单个导出
  *
  * @param template
  * @param data
  * @param dest
  * @throws IOException
  */
 public void transform(String template, Map<String, Object> data, String dest) throws IOException {
   try {
     XLSTransformer transformer = new XLSTransformer();
     transformer.transformXLS(template, data, dest);
   } catch (ParsePropertyException e) {
     e.printStackTrace();
   } catch (InvalidFormatException e) {
     e.printStackTrace();
   }
 }
Example #5
0
  public MediaType detect(TikaInputStream input, Metadata metadata) throws IOException {
    ZipFile zip = new ZipFile(input.getFile());
    for (ZipEntry entry : Collections.list(zip.entries())) {
      // Is it an Open Document file?
      if (entry.getName().equals("mimetype")) {
        InputStream stream = zip.getInputStream(entry);
        try {
          return fromString(IOUtils.toString(stream, "UTF-8"));
        } finally {
          stream.close();
        }
      } else if (entry.getName().equals("_rels/.rels")
          || entry.getName().equals("[Content_Types].xml")) {
        // Office Open XML File
        // As POI to open and investigate it for us
        try {
          OPCPackage pkg = OPCPackage.open(input.getFile().toString());
          input.setOpenContainer(pkg);

          PackageRelationshipCollection core =
              pkg.getRelationshipsByType(ExtractorFactory.CORE_DOCUMENT_REL);
          if (core.size() != 1) {
            throw new IOException(
                "Invalid OOXML Package received - expected 1 core document, found " + core.size());
          }

          // Get the type of the core document part
          PackagePart corePart = pkg.getPart(core.getRelationship(0));
          String coreType = corePart.getContentType();

          // Turn that into the type of the overall document
          String docType = coreType.substring(0, coreType.lastIndexOf('.'));
          return fromString(docType);
        } catch (InvalidFormatException e) {
          throw new IOException("Office Open XML File detected, but corrupted - " + e.getMessage());
        }
      } else if (entry.getName().equals("buildVersionHistory.plist")) {
        // This is an iWork document

        // Reset and ask
        zip.close();
        zip = new ZipFile(input.getFile());
        return IWorkPackageParser.identifyType(zip);
      } else if (entry.getName().equals("META-INF/")) {
        // Java Jar
        return MediaType.application("java-archive");
      }
    }

    return MediaType.APPLICATION_ZIP;
  }
Example #6
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;
 }
Example #7
0
 /**
  * 从文件路径读取相应的Excel文件到对象列表
  *
  * @param path 文件路径下的path
  * @param clz 对象类型
  * @param readLine 开始行,注意是标题所在行
  * @param tailLine 底部有多少行,在读入对象时,会减去这些行
  * @return
  */
 public List<Object> readExcelByPath(String path, Class clz, int readLine, int tailLine) {
   Workbook wb = null;
   try {
     wb = WorkbookFactory.create(new File(path));
     return readExcel(wb, clz, readLine, tailLine);
   } catch (FileNotFoundException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (InvalidFormatException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (IOException e) {
     e.printStackTrace();
     logger.error(e);
   }
   return null;
 }
  private static String extractInvalidFormatMessage(String sampleNameSuffix) {

    InputStream is =
        OpenXML4JTestDataSamples.openComplianceSampleStream(
            "OPCCompliance_CoreProperties_" + sampleNameSuffix);
    OPCPackage pkg;
    try {
      pkg = OPCPackage.open(is);
    } catch (InvalidFormatException e) {
      // expected during successful test
      return e.getMessage();
    } catch (IOException e) {
      throw new RuntimeException(e);
    }
    pkg.revert();
    // Normally must thrown an InvalidFormatException exception.
    throw new AssertionFailedError("expected OPC compliance exception was not thrown");
  }
Example #9
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;
 }
Example #10
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();
   }
 }
  @Override
  public RecordSet getRecords() throws MalformedSourceException {
    try {
      openReader();
    } catch (IOException e) {
      logger_.info("Problem loading file: " + filename + " (" + e.getMessage() + ")");
      throw new MalformedSourceException(
          "Problem loading file: " + filename + " (" + e.getMessage() + ")");
    } catch (InvalidFormatException e) {
      logger_.info("Problem loading file: " + filename + " (" + e.getMessage() + ")");
      throw new MalformedSourceException(
          "Problem loading file: " + filename + " (" + e.getMessage() + ")");
    }

    RecordSet ret = new RecordSet();

    // Currently we need this flag in order for
    // TransformationEngine not to go into an infinite loop.
    if (!isRead_) {
      logger_.info("Opening file: " + filename);
      int nSheets = wb_.getNumberOfSheets();
      logger_.info("number of sheets: " + nSheets);
      for (int i = 0; i < nSheets; i++) {
        Sheet cSheet = wb_.getSheetAt(i);
        String cSheetName = cSheet.getSheetName();
        for (int j = skipLines_; j <= cSheet.getLastRowNum(); j++) {
          if (ignoreLinesAfter_ != 0 && j >= ignoreLinesAfter_) {
            break;
          }
          Row row = cSheet.getRow(j);
          MapRecord rec = new MapRecord();
          for (int k = 0; k < row.getLastCellNum(); k++) {
            if (!fieldMap_.keySet().contains(k)) {
              continue;
            }
            StringValue val;
            Cell cCell = row.getCell(k);
            if (cCell == null) {
              continue;
            }
            int cellType = cCell.getCellType();
            switch (cellType) {
              case Cell.CELL_TYPE_STRING:
                val = new StringValue(cCell.getStringCellValue());
                break;
              case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cCell)) {
                  SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
                  val = new StringValue(sdf.format(cCell.getDateCellValue()));
                } else {
                  val = new StringValue(String.valueOf(cCell.getNumericCellValue()));
                }
                break;
              case Cell.CELL_TYPE_BLANK:
                val = new StringValue("");
                break;
              case Cell.CELL_TYPE_BOOLEAN:
                val = new StringValue(String.valueOf(cCell.getBooleanCellValue()));
                break;
              default:
                val = new StringValue("Unsupported cell type");
            }

            rec.addValue(fieldMap_.get(k), val);
          }
          // TODO remove the hardcoded value
          rec.addValue("ExcelSheetName", new StringValue(cSheetName));
          ret.addRecord(rec);
        }
      }
      isRead_ = true;
    }
    return ret;
  }
Example #12
0
  /**
   * Read Excel format file.
   *
   * @param filename
   */
  private void readExcel(String filename) {
    try {
      FileInputStream fin = new FileInputStream(filename);
      if (flgDoubleQuotes) {
        delimiter = "\"" + delimiter + "\"";
      }
      try {
        Workbook wb = WorkbookFactory.create(fin);

        // HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
          Sheet sheet = wb.getSheetAt(i);
          String sheetname = sheet.getSheetName();

          String fname = sheetname;
          if (delimiter.equals("\t")) {
            fname = fname + ".tsv";
          } else {
            fname = fname + ".csv";
          }
          File file = new File(fname);
          FileOutputStream fout = new FileOutputStream(file);
          OutputStreamWriter ow = new OutputStreamWriter(fout, charset);
          BufferedWriter bw = new BufferedWriter(ow);

          for (Iterator<Row> rowIter = sheet.rowIterator(); rowIter.hasNext(); ) {
            Row row = rowIter.next();

            String tmp = "";
            if (flgDoubleQuotes) {
              tmp = "\"";
            }
            if (row != null) {
              for (int k = 0; k < row.getLastCellNum(); k++) {
                Cell cell = row.getCell(k);
                // CellValue celv = evaluator.evaluate(cell);
                if (cell == null) {
                  tmp = tmp + delimiter;
                  continue;
                }
                switch (cell.getCellType()) {
                  case Cell.CELL_TYPE_BLANK:
                    tmp = tmp + " " + delimiter;
                    break;
                  case Cell.CELL_TYPE_NUMERIC:
                    tmp = tmp + getNumericValue(cell) + delimiter;
                    break;
                  case Cell.CELL_TYPE_STRING:
                    tmp = tmp + getStringValue(cell) + delimiter;
                    break;
                  case Cell.CELL_TYPE_FORMULA:
                    try {
                      FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

                      CellValue value = evaluator.evaluate(cell);

                      if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        tmp = tmp + getNumericValue(cell) + delimiter;
                      } else if (value.getCellType() == Cell.CELL_TYPE_STRING) {
                        tmp = tmp + getStringValue(cell) + delimiter;
                      }
                    } catch (FormulaParseException e) {
                      // error
                      tmp = tmp + " " + delimiter;
                      System.err.println(e.getLocalizedMessage());
                    } catch (NotImplementedException e) {
                      // error
                      tmp = tmp + " " + delimiter;
                      System.err.println(e.getLocalizedMessage());
                    }
                    break;
                  default:
                    tmp = tmp + " " + delimiter;
                }
              }
              tmp = tmp.substring(0, tmp.length() - 1);
            }
            bw.write(tmp + "\n");
          }
          bw.flush();
          bw.close();
          ow.close();
          fout.close();
          System.gc();
        }
      } catch (InvalidFormatException e) {
        e.printStackTrace();
      } catch (IOException e) {
        e.printStackTrace();
      }

    } catch (FileNotFoundException e) {
      e.printStackTrace();
    }
  }