예제 #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);
  }
예제 #2
0
  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;
  }
예제 #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();
     }
   }
 }
예제 #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();
   }
 }
예제 #5
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;
 }
예제 #6
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;
 }
예제 #7
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;
 }
예제 #8
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();
   }
 }
예제 #9
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();
    }
  }