Example #1
0
  /**
   * Test get new id for shapes from existing file File already have for 1 shape on each sheet,
   * because document must contain EscherDgRecord for each sheet
   */
  public void testAllocateNewIds() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("empty.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFPatriarch patriarch = sheet.getDrawingPatriarch();

    /** 2048 - main SpContainer id 2049 - existing shape id */
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2050);
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2051);
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2052);

    sheet = wb.getSheetAt(1);
    patriarch = sheet.getDrawingPatriarch();

    /** 3072 - main SpContainer id 3073 - existing shape id */
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3074);
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3075);
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3076);

    sheet = wb.getSheetAt(2);
    patriarch = sheet.getDrawingPatriarch();

    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1026);
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1027);
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1028);
  }
 /**
  * POI:解析Excel文件中的数据并把每行数据封装成一个实体
  *
  * @param fis 文件输入流
  * @return List<EmployeeInfo> Excel中数据封装实体的集合
  */
 public static List<String> importControlsByPoi(InputStream fis) {
   // 这里是解析出来的Excel的数据存放的List集合
   List<String> controls = new ArrayList<String>();
   try {
     // 创建Excel工作薄
     HSSFWorkbook hwb = new HSSFWorkbook(fis);
     // 得到第一个工作表
     HSSFSheet sheet = hwb.getSheetAt(0);
     HSSFRow row = null;
     // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
     for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
       sheet = hwb.getSheetAt(i);
       // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
       for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
         row = sheet.getRow(j);
         // 此方法调用getCellValue(HSSFCell cell)对解析出来的数据进行判断,并做相应的处理
         if (ImportExcelByPoi.getCellValue(row.getCell(0)) != null
             && !"".equals(ImportExcelByPoi.getCellValue(row.getCell(0)))) {
           controls.add(String.valueOf(ImportExcelByPoi.getCellValue(row.getCell(0))));
         }
       }
     }
   } catch (IOException e) {
     e.printStackTrace();
   }
   return controls;
 }
  public void exportExcel(
      String title,
      Collection<T> dataset,
      HttpServletResponse out,
      Map<String, String> argsMap,
      InputStream is,
      Map<String, String> titleMap) {
    try {
      if (is != null && workbook == null) {
        initworkbook(is);
      }

      if (dataset == null || dataset.isEmpty()) {
        throw new Exception("传入的数据是null");
      }
      Iterator<T> it = dataset.iterator();
      if ((dataset == null) || (!it.hasNext()) || (title == null) || (out == null)) {
        throw new Exception(
            "传入的数据不对!(dataset == null) || (!its.hasNext()) || (title == null) || (out == null)");
      }

      HSSFSheet sheet = workbook.getSheetAt(0);
      if (argsMap.containsKey("sheetnum")) {
        sheet = workbook.getSheetAt(Integer.parseInt(argsMap.get("sheetnum")));
      }

      int index = this.startrow;
      // 更改标题等
      this.setCellsByMap(sheet, titleMap);
      // 循环行 从起始行startrow开始
      while (it.hasNext()) {

        HSSFRow row = sheet.createRow(index);
        // 如果参数map中有rowHeight
        if (argsMap.containsKey("rowHeight")) {
          row.setHeight(Short.parseShort(argsMap.get("rowHeight")));
        } else { // 如果参数map中没有有rowHeight 则默认为上一行的高度
          row.setHeight(getlastHeight(row, sheet));
        }

        Map<String, Method> fieldmap = this.initFieldMap();
        T t = it.next();
        // 为一行的每一cell赋值
        this.setCell(row, sheet, fieldmap, t);

        index++;
      }

      // 屏幕输出
      this.output(out, title, workbook);

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Example #4
0
 public void testReadCell() throws Exception {
   FileInputStream in = new FileInputStream("src/test/resources/date.xls");
   HSSFWorkbook wb = new HSSFWorkbook(in);
   HSSFCell dateCell = wb.getSheetAt(0).getRow(0).getCell(0);
   HSSFCell dateTimeCell = wb.getSheetAt(0).getRow(0).getCell(1);
   Object value = ExcelItemReader.getCellValue(dateCell);
   Assert.assertTrue(value instanceof Date);
   Object value2 = ExcelItemReader.getCellValue(dateTimeCell);
   Assert.assertTrue(value2 instanceof Date);
   in.close();
 }
Example #5
0
  public void testClearShapesForPatriarch() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

    patriarch.createSimpleShape(new HSSFClientAnchor());
    patriarch.createSimpleShape(new HSSFClientAnchor());
    patriarch.createCellComment(new HSSFClientAnchor());

    EscherAggregate agg = HSSFTestHelper.getEscherAggregate(patriarch);

    assertEquals(agg.getShapeToObjMapping().size(), 6);
    assertEquals(agg.getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 3);

    patriarch.clear();

    assertEquals(agg.getShapeToObjMapping().size(), 0);
    assertEquals(agg.getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 0);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    assertEquals(agg.getShapeToObjMapping().size(), 0);
    assertEquals(agg.getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 0);
  }
Example #6
0
  public void testRotation() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

    HSSFSimpleShape rectangle =
        patriarch.createSimpleShape(
            new HSSFClientAnchor(0, 0, 100, 100, (short) 0, 0, (short) 5, 5));
    rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);

    assertEquals(rectangle.getRotationDegree(), 0);
    rectangle.setRotationDegree((short) 45);
    assertEquals(rectangle.getRotationDegree(), 45);
    rectangle.setFlipHorizontal(true);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0);
    assertEquals(rectangle.getRotationDegree(), 45);
    rectangle.setRotationDegree((short) 30);
    assertEquals(rectangle.getRotationDegree(), 30);

    patriarch.setCoordinates(0, 0, 10, 10);
    rectangle.setString(new HSSFRichTextString("1234"));
  }
Example #7
0
  public void testShapeIds() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet();
    HSSFPatriarch patriarch1 = sheet1.createDrawingPatriarch();
    for (int i = 0; i < 2; i++) {
      patriarch1.createSimpleShape(new HSSFClientAnchor());
    }

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet1 = wb.getSheetAt(0);
    patriarch1 = sheet1.getDrawingPatriarch();

    EscherAggregate agg1 = HSSFTestHelper.getEscherAggregate(patriarch1);
    // last shape ID cached in EscherDgRecord
    EscherDgRecord dg1 = agg1.getEscherContainer().getChildById(EscherDgRecord.RECORD_ID);
    assertEquals(1026, dg1.getLastMSOSPID());

    // iterate over shapes and check shapeId
    EscherContainerRecord spgrContainer = agg1.getEscherContainer().getChildContainers().get(0);
    // root spContainer + 2 spContainers for shapes
    assertEquals(3, spgrContainer.getChildRecords().size());

    EscherSpRecord sp0 =
        ((EscherContainerRecord) spgrContainer.getChild(0)).getChildById(EscherSpRecord.RECORD_ID);
    assertEquals(1024, sp0.getShapeId());

    EscherSpRecord sp1 =
        ((EscherContainerRecord) spgrContainer.getChild(1)).getChildById(EscherSpRecord.RECORD_ID);
    assertEquals(1025, sp1.getShapeId());

    EscherSpRecord sp2 =
        ((EscherContainerRecord) spgrContainer.getChild(2)).getChildById(EscherSpRecord.RECORD_ID);
    assertEquals(1026, sp2.getShapeId());
  }
  private void process_other(int idMo, ArrayList<String> queries) {

    sheet = wb.getSheetAt(4);
    queries.add(
        String.format("DELETE FROM offers_other WHERE id_mo = '%d' AND year='%d';", idMo, YEAR));

    int numberOfProfile = 0;
    for (int i = 6; i < 16; i++) {

      if (i == 8) continue;

      row = sheet.getRow(i);

      int offer = 0;
      if (i < 10) {
        offer = (int) row.getCell(2).getNumericCellValue();
      } else {
        offer = (int) row.getCell(1).getNumericCellValue();
      }

      int idprofile = Constants.planPatOther[numberOfProfile++];

      queries.add(
          String.format(
              "INSERT INTO offers_other VALUES(NULL, '%d', '%d', '%d', '%d');",
              idMo, idprofile, offer, YEAR));
    }
  }
 /**
  * 测试单元格样式
  *
  * @author David
  * @param wb
  * @param cell
  * @param td
  */
 private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) {
   Attribute typeAttr = td.getAttribute("type");
   String type = typeAttr.getValue();
   HSSFDataFormat format = wb.createDataFormat();
   HSSFCellStyle cellStyle = wb.createCellStyle();
   if ("NUMERIC".equalsIgnoreCase(type)) {
     cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
     Attribute formatAttr = td.getAttribute("format");
     String formatValue = formatAttr.getValue();
     formatValue = StringUtils.isNotBlank(formatValue) ? formatValue : "#,##0.00";
     cellStyle.setDataFormat(format.getFormat(formatValue));
   } else if ("STRING".equalsIgnoreCase(type)) {
     cell.setCellValue("");
     cell.setCellType(HSSFCell.CELL_TYPE_STRING);
     cellStyle.setDataFormat(format.getFormat("@"));
   } else if ("DATE".equalsIgnoreCase(type)) {
     cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
     cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
   } else if ("ENUM".equalsIgnoreCase(type)) {
     CellRangeAddressList regions =
         new CellRangeAddressList(
             cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex());
     Attribute enumAttr = td.getAttribute("format");
     String enumValue = enumAttr.getValue();
     // 加载下拉列表内容
     DVConstraint constraint = DVConstraint.createExplicitListConstraint(enumValue.split(","));
     // 数据有效性对象
     HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
     wb.getSheetAt(0).addValidationData(dataValidation);
   }
   cell.setCellStyle(cellStyle);
 }
 private void autoSizeColumns() {
   int sheetCount = workbook.getNumberOfSheets();
   for (int i = 0; i < sheetCount; i++) {
     HSSFSheet sheet = workbook.getSheetAt(i);
     for (int colnum = 0; colnum <= sheet.getLastRowNum(); colnum++) sheet.autoSizeColumn(colnum);
   }
 }
Example #11
0
  public static void read(int rowxi, int rowxj) {
    try {
      POIFSFileSystem fs;
      fs =
          new POIFSFileSystem(
              new FileInputStream(
                  "F:\\工作\\1_Java资料\\5_workspace\\hhw\\poi\\src\\com\\hhw\\poi\\temp1.xls"));

      HSSFWorkbook wb = new HSSFWorkbook(fs);

      HSSFSheet sheet = wb.getSheetAt(0);

      for (int i = rowxi - 1; i < rowxj; i++) {
        String name = sheet.getRow(i).getCell(0).getStringCellValue();
        String bankName = name.substring(0, name.indexOf("(") - 1);
        String bankNo = name.substring(name.indexOf("(") + 1, name.indexOf(")"));
        sheet.getRow(i).getCell(13).setCellType(Cell.CELL_TYPE_STRING);
        String bin = sheet.getRow(i).getCell(13).getStringCellValue();
        String cardType = sheet.getRow(i).getCell(15).getStringCellValue();
        System.out.println(bankName + "," + bankNo + "," + bin + "," + cardType);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Example #12
0
  public static String readExcel(String excelPath, int sheetNu, int rowNu, int cellNu)
      throws Exception {
    /** read Excel file with the Path get cell in sheetNu, rowNu and cellNu */
    String cellValue = null;

    FileInputStream fos = new FileInputStream(excelPath);
    HSSFWorkbook wb = new HSSFWorkbook(fos);

    HSSFSheet sheet = wb.getSheetAt(sheetNu);
    HSSFRow row = sheet.getRow(rowNu);

    try {
      HSSFCell cell = row.getCell(cellNu);

      if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
        cellValue = cell.getStringCellValue();
        // System.out.println("_________________"+cellValue);
      }
      if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
        cellValue = Long.toString((long) (cell.getNumericCellValue()));
        // System.out.println("_________________"+cellValue);
      }

      return cellValue;
    } catch (NullPointerException e) {
      return null;
    }
  }
Example #13
0
  /**
   * 根据模板初始化样式 注意:module.xls模板文件跟该类同一路径
   *
   * @param wb
   * @return
   */
  private HashMap<String, HSSFCellStyle> initStyles(HSSFWorkbook wb) {
    HashMap<String, HSSFCellStyle> ret = new HashMap<String, HSSFCellStyle>();
    try {
      ClassPathResource res =
          new ClassPathResource("module.xls", this.getClass()); // 注意:module.xls模板文件跟该类同一路径
      InputStream is = res.getInputStream();
      POIFSFileSystem fs = new POIFSFileSystem(is);

      HSSFWorkbook src = new HSSFWorkbook(fs);
      HSSFSheet sheet = src.getSheetAt(0);

      buildStyle(wb, src, sheet, 0, ret, "TITLE"); // 标题样式
      buildStyle(wb, src, sheet, 1, ret, "SUB_TITLE"); // 副标题样式
      buildStyle(wb, src, sheet, 2, ret, "SUB_TITLE2"); // 副标题2样式

      buildStyle(wb, src, sheet, 4, ret, "TABLE_HEADER"); // 表头样式
      buildStyle(wb, src, sheet, 5, ret, "STRING"); // 字符串单元格样式
      buildStyle(wb, src, sheet, 6, ret, "INT"); // 整数单元格样式
      buildStyle(wb, src, sheet, 7, ret, "D2"); // 2位小数单元格样式
      buildStyle(wb, src, sheet, 8, ret, "D3"); // 3位小数单元格样式

      buildStyle(wb, src, sheet, 10, ret, "STRING_C"); // 字符串单元格样式(带背景色)
      buildStyle(wb, src, sheet, 11, ret, "INT_C"); // 整数单元格样式(带背景色)
      buildStyle(wb, src, sheet, 12, ret, "D2_C"); // 2位小数单元格样式(带背景色)
      buildStyle(wb, src, sheet, 13, ret, "D3_C"); // 3位小数单元格样式(带背景色)

      buildStyle(wb, src, sheet, 15, ret, "RED_BG"); // 红色单元格背景
      buildStyle(wb, src, sheet, 16, ret, "YELLOW_BG"); // 黄色单元格背景
      buildStyle(wb, src, sheet, 17, ret, "GREEN_BG"); // 绿色单元格背景
    } catch (Exception e) {
      e.printStackTrace();
    }
    return ret;
  }
 private HSSFRowSource(HSSFWorkbook workbook) {
   this.workbook = workbook;
   this.formatter = new HSSFDataFormatter();
   this.evaluator = new HSSFFormulaEvaluator(workbook);
   this.stringCache = new ArrayList<String>();
   initSheetIterator(workbook.getSheetAt(0));
 }
  /**
   * Sometimes the 'shared formula' flag (<tt>FormulaRecord.isSharedFormula()</tt>) is set when
   * there is no corresponding SharedFormulaRecord available. SharedFormulaRecord definitions do not
   * span multiple sheets. They are are only defined within a sheet, and thus they do not have a
   * sheet index field (only row and column range fields).<br>
   * So it is important that the code which locates the SharedFormulaRecord for each FormulaRecord
   * does not allow matches across sheets.</br>
   *
   * <p>Prior to bugzilla 44449 (Feb 2008), POI <tt>ValueRecordsAggregate.construct(int, List)</tt>
   * allowed <tt>SharedFormulaRecord</tt>s to be erroneously used across sheets. That incorrect
   * behaviour is shown by this test.
   *
   * <p><b>Notes on how to produce the test spreadsheet</b>: The setup for this test
   * (AbnormalSharedFormulaFlag.xls) is rather fragile, insomuchas re-saving the file (either with
   * Excel or POI) clears the flag.<br>
   *
   * <ol>
   *   <li>A new spreadsheet was created in Excel (File | New | Blank Workbook).
   *   <li>Sheet3 was deleted.
   *   <li>Sheet2!A1 formula was set to '="second formula"', and fill-dragged through A1:A8.
   *   <li>Sheet1!A1 formula was set to '="first formula"', and also fill-dragged through A1:A8.
   *   <li>Four rows on Sheet1 "5" through "8" were deleted ('delete rows' alt-E D, not 'clear'
   *       Del).
   *   <li>The spreadsheet was saved as AbnormalSharedFormulaFlag.xls.
   * </ol>
   *
   * Prior to the row delete action the spreadsheet has two <tt>SharedFormulaRecord</tt>s. One for
   * each sheet. To expose the bug, the shared formulas have been made to overlap.<br>
   * The row delete action (as described here) seems to to delete the <tt>SharedFormulaRecord</tt>
   * from Sheet1 (but not clear the 'shared formula' flags.<br>
   * There are other variations on this theme to create the same effect.
   */
  public void testSpuriousSharedFormulaFlag() {

    long actualCRC =
        getFileCRC(
            HSSFTestDataSamples.openSampleFileStream(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE));
    long expectedCRC = 2277445406L;
    if (actualCRC != expectedCRC) {
      System.err.println("Expected crc " + expectedCRC + " but got " + actualCRC);
      throw failUnexpectedTestFileChange();
    }
    HSSFWorkbook wb =
        HSSFTestDataSamples.openSampleWorkbook(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE);

    HSSFSheet s = wb.getSheetAt(0); // Sheet1

    String cellFormula;
    cellFormula = getFormulaFromFirstCell(s, 0); // row "1"
    // the problem is not observable in the first row of the shared formula
    if (!cellFormula.equals("\"first formula\"")) {
      throw new RuntimeException("Something else wrong with this test case");
    }

    // but the problem is observable in rows 2,3,4
    cellFormula = getFormulaFromFirstCell(s, 1); // row "2"
    if (cellFormula.equals("\"second formula\"")) {
      throw new AssertionFailedError("found bug 44449 (Wrong SharedFormulaRecord was used).");
    }
    if (!cellFormula.equals("\"first formula\"")) {
      throw new RuntimeException("Something else wrong with this test case");
    }
  }
  /**
   * This method is used to display the Excel content to command line.
   *
   * @param xlsPath
   */
  @SuppressWarnings("unchecked")
  public void displayFromExcel(InputStream inputStream) {

    POIFSFileSystem fileSystem = null;

    try {
      fileSystem = new POIFSFileSystem(inputStream);

      Map headerTable = null;
      HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
      HSSFSheet sheet = workBook.getSheetAt(0);
      System.out.println("LastRowNum:" + sheet.getLastRowNum());

      Iterator<HSSFRow> rows = sheet.rowIterator();

      while (rows.hasNext()) {
        HSSFRow row = rows.next();

        // display row number in the console.
        System.out.println("Row No.: " + row.getRowNum());

        Contact contact = null;
        if (row.getRowNum() == 0) {
          headerTable = headerTable(row);
        } else {
          contact = processRow(row, headerTable);
          if (contact != null) contactManager.saveContact(contact);
        }
      }
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
Example #17
0
  /**
   * 读取 Excel文件内容
   *
   * @param excel_name
   * @return
   * @throws Exception
   */
  public static List<List<Object>> readExcelByInputStream(InputStream inputstream)
      throws Exception {
    // 结果集
    List<List<Object>> list = new ArrayList<List<Object>>();

    HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream);

    // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
    HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);

    // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数

    // //System.out.println("excel行数: "+hssfsheet.getPhysicalNumberOfRows());
    for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
      HSSFRow hssfrow = hssfsheet.getRow(j);
      if (hssfrow != null) {
        int col = hssfrow.getPhysicalNumberOfCells();
        // 单行数据
        List<Object> arrayString = new ArrayList<Object>();
        for (int i = 0; i < col; i++) {
          HSSFCell cell = hssfrow.getCell(i);
          if (cell == null) {
            arrayString.add("");
          } else if (cell.getCellType() == 0) {
            arrayString.add(new Double(cell.getNumericCellValue()).toString());
          } else { // 如果EXCEL表格中的数据类型为字符串型
            arrayString.add(cell.getStringCellValue().trim());
          }
        }
        list.add(arrayString);
      }
    }
    return list;
  }
  /**
   * Reads userdata from all sheets in workbook.
   *
   * @return {@link List} of {@link User}.
   */
  public List<User> getUsers() {
    List<User> vUsers = new ArrayList<User>();

    if (mFileType == FileTypes.EXCEL && mWorkbook != null) {

      // go through every sheet as house
      logger.info("Importing user data from excel file");
      int vNumberOfSheets = mWorkbook.getNumberOfSheets();
      for (int i = 0; i < vNumberOfSheets; i++) {
        HSSFSheet vSheet = mWorkbook.getSheetAt(i);
        String vSheetName = vSheet.getSheetName();
        logger.info("Sheet name: " + vSheetName);

        // try to figure out columns to use
        int[] vColumns = getUserdataColumnsFromSheet(vSheet);

        // get userdata from sheet
        vUsers.addAll(getUsers(vSheet, vColumns));
      }

    } else if (mFileType == FileTypes.CSV && mExcelFile != null) {
      logger.info("Importing user data from csv file.");
      vUsers.addAll(getUsers(mExcelFile));
    } else {
      logger.error("Cannot get userdata from workbook. No workbook loaded!");
    }

    return vUsers;
  }
Example #19
0
  public Collection<Tree> read(final String fileName) throws IOException {
    List<Tree> result = new ArrayList<Tree>();
    InputStream file = getClass().getResourceAsStream(fileName);
    if (file == null) {
      throw new IllegalArgumentException("File " + fileName + " not found");
    }
    try {

      // Get the workbook instance for XLS file
      HSSFWorkbook workbook = new HSSFWorkbook(file);

      // Get first sheet from the workbook
      HSSFSheet sheet = workbook.getSheetAt(0);

      // Get iterator to all the rows in current sheet
      Iterator<Row> rowIterator = sheet.iterator();

      rowIterator.next(); // ignore column title

      while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        TreeBuilder reader = new TreeBuilder(row);
        Tree tree = reader.buildTree();
        result.add(tree);
        System.out.println(tree.toString());
      }
    } finally {
      file.close();
    }
    return result;
  }
  /** tests <tt>NameXPtg.toFormulaString(Workbook)</tt> and logic in Workbook below that */
  public void testReadFormulaContainingExternalFunction() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls");

    String expectedFormula = "YEARFRAC(B1,C1)";
    HSSFSheet sht = wb.getSheetAt(0);
    String cellFormula = sht.getRow(0).getCell(0).getCellFormula();
    assertEquals(expectedFormula, cellFormula);
  }
  public int getIdMo() {
    sheet = wb.getSheetAt(0);
    row = sheet.getRow(6);
    cell = row.getCell(0);

    DBWorkerMO dbWorkerMO = new DBWorkerMO();
    return dbWorkerMO.getIdMo((int) cell.getNumericCellValue());
  }
Example #22
0
  /**
   * @param filepath //文件路径
   * @param filename //文件名
   * @param startrow //开始行号
   * @param startcol //开始列号
   * @param sheetnum //sheet
   * @return list
   */
  public static List<Object> readExcel(
      String filepath, String filename, int startrow, int startcol, int sheetnum) {
    List<Object> varList = new ArrayList<Object>();

    try {
      File target = new File(filepath, filename);
      FileInputStream fi = new FileInputStream(target);
      HSSFWorkbook wb = new HSSFWorkbook(fi);
      HSSFSheet sheet = wb.getSheetAt(sheetnum); // sheet 从0开始
      int rowNum = sheet.getLastRowNum() + 1; // 取得最后一行的行号

      for (int i = startrow; i < rowNum; i++) { // 行循环开始

        PageData varpd = new PageData();
        HSSFRow row = sheet.getRow(i); // 行
        int cellNum = row.getLastCellNum(); // 每行的最后一个单元格位置

        for (int j = startcol; j < cellNum; j++) { // 列循环开始

          HSSFCell cell = row.getCell(Short.parseShort(j + ""));
          String cellValue = null;
          if (null != cell) {
            switch (cell.getCellType()) { // 判断excel单元格内容的格式,并对其进行转换,以便插入数据库
              case 0:
                cellValue = String.valueOf((int) cell.getNumericCellValue());
                break;
              case 1:
                cellValue = cell.getStringCellValue();
                break;
              case 2:
                cellValue = cell.getNumericCellValue() + "";
                // cellValue = String.valueOf(cell.getDateCellValue());
                break;
              case 3:
                cellValue = "";
                break;
              case 4:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
              case 5:
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;
            }
          } else {
            cellValue = "";
          }

          varpd.put("var" + j, cellValue);
        }
        varList.add(varpd);
      }

    } catch (Exception e) {
      System.out.println(e);
    }

    return varList;
  }
    @DataProvider(name = "placeOrderCredentials")
    public Iterator<Object[]> readFromExcelIterator() throws FileNotFoundException, IOException {
      //	prop.load(getClass().getResourceAsStream("AutomationEnv.properties"));
      // String sourceXlsFileName=(String)prop.get("signUpAndOrderCard");
      String fileName =
          "C:\\Users\\WINQA\\workspace\\NewHkAutomationSuite\\Excel\\signUpandPlace.xls";
      int sheetNo = 0;
      ArrayList<Object[]> excelDataArray = new ArrayList<Object[]>();
      int cnt = 0;
      try {

        InputStream input = new BufferedInputStream(new FileInputStream(fileName));

        POIFSFileSystem fs = new POIFSFileSystem(input);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(sheetNo);

        Iterator rows = sheet.rowIterator();

        while (rows.hasNext()) {
          int cellCount = 0;
          int flagStop = 0;
          HSSFRow row = (HSSFRow) rows.next();
          System.out.println("\n");
          Iterator cells = row.cellIterator();

          List<String> readExcelData = new LinkedList<String>();

          while (cells.hasNext()) {
            if (cellCount <= 10) {
              int cellValueInt;
              String CellValue;
              HSSFCell cell = (HSSFCell) cells.next();

              if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                // System.out.print( cell.getNumericCellValue()+"     " );
                cellValueInt = (int) cell.getNumericCellValue();
                CellValue = Integer.toString(cellValueInt);
              } else {
                CellValue = cell.getStringCellValue();
              }
              readExcelData.add(CellValue);
              cnt++;

              cellCount++;
            }

            if (cellCount == 11) break;
          }
          excelDataArray.add(new Object[] {readExcelData});
        }

      } catch (IOException ex) {
        ex.printStackTrace();
      }
      return excelDataArray.iterator();
    }
Example #24
0
  @Override
  public String executeAction(HttpServletRequest request, List<FileItem> sessionFiles)
      throws UploadActionException {
    StringBuffer response = new StringBuffer();

    for (FileItem item : sessionFiles) {
      if (false == item.isFormField()) {

        try {

          if (item.getName().endsWith(".xls")) {
            POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            System.out.println("Sheet Num:" + wb.getNumberOfSheets());
            // only get first sheet,ignore others
            HSSFSheet sheet = wb.getSheetAt(0);

            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
              HSSFRow row = (HSSFRow) rows.next();
              Iterator<Cell> cells = row.cellIterator();
              while (cells.hasNext()) {
                HSSFCell cell = (HSSFCell) cells.next();
                response.append(cell.toString() + ":");
              }
              response.append("\n");
            }

          } else if (item.getName().endsWith(".xlsx")) {
            // POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
            XSSFWorkbook wb = new XSSFWorkbook(item.getInputStream());
            System.out.println("Sheet Num:" + wb.getNumberOfSheets());
            // only get first sheet,ignore others
            XSSFSheet sheet = wb.getSheetAt(0);

            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
              XSSFRow row = (XSSFRow) rows.next();
              Iterator<Cell> cells = row.cellIterator();
              while (cells.hasNext()) {
                XSSFCell cell = (XSSFCell) cells.next();
                response.append(cell.toString() + ":");
              }
              response.append("\n");
            }
          }

        } catch (Exception e) {
          throw new UploadActionException(e);
        }
      }
    }
    // / Remove files from session because we have a copy of them
    removeSessionFileItems(request);
    // / Send your customized message to the client.
    return response.toString();
  }
Example #25
0
  /** Creates a new XlsDataSet object that loads the specified Excel document. */
  public XlsDataSet(InputStream in) throws IOException, DataSetException {
    _tables = super.createTableNameMap();

    HSSFWorkbook workbook = new HSSFWorkbook(in);
    int sheetCount = workbook.getNumberOfSheets();
    for (int i = 0; i < sheetCount; i++) {
      ITable table = new XlsTable(workbook.getSheetName(i), workbook.getSheetAt(i));
      _tables.add(table.getTableMetaData().getTableName(), table);
    }
  }
 public void testEvaluate() {
   HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls");
   HSSFSheet sheet = wb.getSheetAt(0);
   HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
   confirmCellEval(sheet, 0, 0, fe, "YEARFRAC(B1,C1)", 29.0 / 90.0);
   confirmCellEval(sheet, 1, 0, fe, "YEARFRAC(B2,C2)", 0.0);
   confirmCellEval(sheet, 2, 0, fe, "YEARFRAC(B3,C3,D3)", 0.0);
   confirmCellEval(sheet, 3, 0, fe, "IF(ISEVEN(3),1.2,1.6)", 1.6);
   confirmCellEval(sheet, 4, 0, fe, "IF(ISODD(3),1.2,1.6)", 1.2);
 }
Example #27
0
 /**
  * Load a specific page of the given Excel Spreadsheet
  *
  * @param bytes Excel content as byte array
  * @param page page to use
  * @return true on success
  */
 public boolean load(final InputStream inputStream, final int page) {
   try {
     fs = new POIFSFileSystem(inputStream);
     HSSFWorkbook wb = new HSSFWorkbook(fs);
     sheet = wb.getSheetAt(page);
     return true;
   } catch (Exception ex) {
     return false;
   }
 }
Example #28
0
  public List<List<Map>> readSheetData(
      int fileIdx, int startRow, String[][] fields, Class<? extends Map>[] classnames)
      throws Exception {
    List<List<Map>> results = new ArrayList<List<Map>>();
    HSSFWorkbook workb = workbooks.get(fileIdx);

    for (int i = 0; i < classnames.length; i++)
      results.add(readSheet(workb.getSheetAt(i), startRow, fields[i], classnames[i]));
    return results;
  }
  /** @param args */
  public static void main(String[] args) {
    // TODO Auto-generated method stub
    // Directory path here
    String path = "./plantillas";

    String files;
    File folder = new File(path);
    File[] listOfFiles = folder.listFiles();

    for (int i = 0; i < listOfFiles.length; i++) {

      if (listOfFiles[i].isFile()) {
        files = listOfFiles[i].getName();
        System.out.println(files);
      }
    }

    InputStream input;
    try {
      input = new BufferedInputStream(new FileInputStream("./plantillas/Panama.xls"));
      POIFSFileSystem fs;

      fs = new POIFSFileSystem(input);

      HSSFWorkbook wb = new HSSFWorkbook(fs);
      HSSFSheet sheet = wb.getSheetAt(0);

      Iterator rows = sheet.rowIterator();

      while (rows.hasNext()) {
        HSSFRow row = (HSSFRow) rows.next();
        System.out.println("\n");
        Iterator cells = row.cellIterator();
        while (cells.hasNext()) {

          HSSFCell cell = (HSSFCell) cells.next();
          if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType())
            System.out.print(cell.getNumericCellValue() + "     ");
          else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType())
            System.out.print(cell.getStringCellValue() + "     ");
          else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType())
            System.out.print(cell.getBooleanCellValue() + "     ");
          else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) System.out.print("BLANK     ");
          else System.out.print("Unknown cell type");
        }
      }

    } catch (FileNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
Example #30
0
 /**
  * Load a specific page of the given Excel Spreadsheet
  *
  * @param file filename of the Excel file
  * @param page page to use
  * @return true on success
  * @deprecated use load(InputStream) instead
  */
 @Deprecated
 public boolean load(final String file, final int page) {
   try {
     fs = new POIFSFileSystem(new FileInputStream(file));
     HSSFWorkbook wb = new HSSFWorkbook(fs);
     sheet = wb.getSheetAt(page);
     return true;
   } catch (Exception ex) {
     return false;
   }
 }