Example #1
0
  public void executeJob(Connection con) throws Exception {
    // input ����
    String fileName = "./기초자료/기초자료.xls";
    String sheetName = "GHOST_TABLES";
    FileInputStream fis = null;
    HSSFWorkbook wb = null;
    HSSFSheet sheet = null;

    // output ����
    String deleteSQL = "delete from ghost_tables";
    String insertSQL = "insert into ghost_tables values(?,?,?)";
    PreparedStatement pstmt = null;
    Statement stmt = null;

    try {
      // Input �ڷ� �غ�
      fis = new FileInputStream(fileName);
      wb = new HSSFWorkbook(fis);
      sheet = wb.getSheet(sheetName);

      // �����ڷ� All ����
      stmt = con.createStatement();
      stmt.execute(deleteSQL);

      // Insert�� SQL Statement ����
      pstmt = con.prepareStatement(insertSQL);

      HSSFRow row = null;
      Bean bean = null;

      for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        readCount++;

        if (row == null) continue;

        bean = getBean(row);
        pstmt.setString(1, bean.tableName);
        pstmt.setString(2, bean.tableDesc);
        pstmt.setString(3, bean.shortName);
        pstmt.executeUpdate();
        writeCount++;

        System.out.printf("[%d][%s][%s][%s]\n", i, bean.tableName, bean.tableDesc, bean.shortName);
      }
    } finally {
      if (wb != null) wb.close();
      if (fis != null) fis.close();
      JDBCUtil.close(stmt);
      JDBCUtil.close(pstmt);
    }
  }
Example #2
0
  public void testFormulaGeneral() throws IOException {
    // perhaps this testcase belongs on TestHSSFName
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFName name = wb.createName();
    wb.createSheet("Sheet1");
    name.setNameName("test");
    name.setRefersToFormula("Sheet1!A1+Sheet1!A2");
    assertEquals("Sheet1!A1+Sheet1!A2", name.getRefersToFormula());
    name.setRefersToFormula("5*6");
    assertEquals("5*6", name.getRefersToFormula());

    wb.close();
  }
Example #3
0
  public void testDgRecordNumShapes() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
      HSSFSheet sheet = wb.createSheet();
      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

      EscherAggregate aggregate = HSSFTestHelper.getEscherAggregate(patriarch);
      EscherDgRecord dgRecord = (EscherDgRecord) aggregate.getEscherRecord(0).getChild(0);
      assertEquals(dgRecord.getNumShapes(), 1);
    } finally {
      wb.close();
    }
  }
Example #4
0
 public void testFormulaRelAbs_bug46174() throws IOException {
   // perhaps this testcase belongs on TestHSSFName
   HSSFWorkbook wb = new HSSFWorkbook();
   HSSFName name = wb.createName();
   wb.createSheet("Sheet1");
   name.setNameName("test");
   name.setRefersToFormula("Sheet1!$B$3");
   if (name.getRefersToFormula().equals("Sheet1!B3")) {
     fail("Identified bug 46174");
   }
   assertEquals("Sheet1!$B$3", name.getRefersToFormula());
   wb.close();
 }
Example #5
0
  public void testBug45312() throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
      HSSFSheet sheet = wb.createSheet();
      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

      {
        HSSFClientAnchor a1 = new HSSFClientAnchor();
        a1.setAnchor((short) 1, 1, 0, 0, (short) 1, 1, 512, 100);
        HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
        shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
      }
      {
        HSSFClientAnchor a1 = new HSSFClientAnchor();
        a1.setAnchor((short) 1, 1, 512, 0, (short) 1, 1, 1024, 100);
        HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
        shape1.setFlipVertical(true);
        shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
      }

      {
        HSSFClientAnchor a1 = new HSSFClientAnchor();
        a1.setAnchor((short) 2, 2, 0, 0, (short) 2, 2, 512, 100);
        HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
        shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
      }
      {
        HSSFClientAnchor a1 = new HSSFClientAnchor();
        a1.setAnchor((short) 2, 2, 0, 100, (short) 2, 2, 512, 200);
        HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
        shape1.setFlipHorizontal(true);
        shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
      }

      /*OutputStream stream = new FileOutputStream("/tmp/45312.xls");
      try {
          wb.write(stream);
      } finally {
          stream.close();
      }*/

      checkWorkbookBack(wb);
    } finally {
      wb.close();
    }
  }
Example #6
0
  public void testOpt() throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();

    try {
      // create a sheet with a text box
      HSSFSheet sheet = wb.createSheet();
      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

      HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor());
      EscherOptRecord opt1 = HSSFTestHelper.getOptRecord(textbox);
      EscherOptRecord opt2 =
          HSSFTestHelper.getEscherContainer(textbox).getChildById(EscherOptRecord.RECORD_ID);
      assertSame(opt1, opt2);
    } finally {
      wb.close();
    }
  }
Example #7
0
  public void testShapeContainerImplementsIterable() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();

    try {
      HSSFSheet sheet = wb.createSheet();
      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

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

      int i = 2;

      for (HSSFShape shape : patriarch) {
        i--;
      }
      assertEquals(i, 0);
    } finally {
      wb.close();
    }
  }
Example #8
0
  public String getZhFrom4GYsb() throws IOException {
    // 预算表中  第三行 B列的名称为: 单项工程名称:SXZH001TL新建、共址2G、共址其他运营商的(F)(D)宏站基站
    // path1: 预算表   path2 3g4g基础信息
    String results = "";
    String result2 = "";
    FileInputStream fise = new FileInputStream(excelPath);
    HSSFWorkbook wb = new HSSFWorkbook(fise);
    HSSFWorkbook wb2 = new HSSFWorkbook(new FileInputStream(excelPath2));

    HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(wb);
    HSSFFormulaEvaluator e2 = new HSSFFormulaEvaluator(wb2);

    // TODO: 将此处的文件名替换为从参数读取的文件名
    String[] strArray = new String[2];
    String[] paths = this.excelPath.split("\\\\");
    strArray[0] = paths[paths.length - 1];
    // System.out.println(strArray[0]);
    String[] paths1 = this.excelPath2.split("\\\\");
    // strArray[1] = paths1[paths1.length-1];
    // System.out.println(strArray[1]);
    HSSFFormulaEvaluator[] evals = new HSSFFormulaEvaluator[2];
    evals[0] = e;
    evals[1] = e2;

    Sheet sheet = wb.getSheetAt(7);
    Row r = null;
    r = sheet.getRow(2);
    Cell cell2 = r.getCell(1);
    Cell cell8 = r.getCell(8);
    // System.out.println(cell8.toString());
    String[] path1 = cell8.toString().split("\'");
    String path12 = path1[3];
    String[] path2 = path12.split("]");
    String path21 = path2[0];
    String path3 = path21.substring(1);
    strArray[1] = path3;
    // System.out.println(path3);
    HSSFFormulaEvaluator.setupEnvironment(strArray, evals);

    if (cell2.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
      results = e.evaluate(cell2).getStringValue();
      // results = cell2.getStringCellValue();
    } else if (cell2.getCellType() == HSSFCell.CELL_TYPE_STRING) {
      results = cell2.toString();
    }
    System.out.println(result2);
    // TL TLD TLFD    SXZH017TL   前面有7位   TL- conditions
    // TL-1、TL-2、TL-3  TLD-1 TLD-2 TLD-3
    if (results.contains("TLFD")) {
      int end = results.indexOf("TLFD");
      end = end + 4;
      int begin = end - 12;
      result2 = results.substring(begin + 1, end);
    } else if (results.contains("TL-")) {
      int end = results.indexOf("TL-");
      int count = 11;
      boolean flag = true;
      end = end + 3;
      System.out.println(results.substring(end - 1, end));
      for (int k = 1; k < 5; k++) {
        end = end + 1;
        System.out.println(results.substring(end - 1, end));
        flag = isNumeric(results.substring(end - 1, end));
        if (flag) {
          count++;
        } else {
          break;
        }
      }
      int begin = end - count;
      result2 = results.substring(begin + 1, end);
    } else if (results.contains("TLD-")) {
      int end = results.indexOf("TLD-");
      int count = 12;
      boolean flag = true;
      end = end + 4;
      System.out.println(results.substring(end - 1, end));
      for (int k = 1; k < 5; k++) {
        end = end + 1;
        System.out.println(results.substring(end - 1, end));
        flag = isNumeric(results.substring(end - 1, end));
        if (flag) {
          count++;
        } else {
          break;
        }
      }
      int begin = end - count;
      result2 = results.substring(begin + 1, end);
    } else if (results.contains("TLD")) {
      int end = results.indexOf("TLD");
      end = end + 3;
      int begin = end - 11;
      result2 = results.substring(begin + 1, end);
    } else if (results.contains("TL")) {
      // System.out.println(results+"!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
      int end = results.indexOf("TL");
      //			System.out.println(end);
      end = end + 2;
      //			System.out.println(results.substring(end-1, end));
      int begin = end - 10;
      result2 = results.substring(begin + 1, end);
    }
    wb2.close();
    wb.close();
    fise.close();
    return result2;
  }
Example #9
0
  @Override
  public List<Artifact> getArtifactList(Project project, File spreadsheet) throws IOException {
    xlsFile = spreadsheet;

    BufferedInputStream bis = null;
    HSSFWorkbook workBook = null;
    List<Artifact> artifacts = new ArrayList<Artifact>();

    try {
      bis = new BufferedInputStream(new FileInputStream(xlsFile));
      workBook = new HSSFWorkbook(bis);

      HSSFSheet sheet = workBook.getSheetAt(0);

      Map<ColumnType, Integer> columnHeaders = getColumnInfo(sheet);
      Integer typeColumn = columnHeaders.get(ColumnType.TYPE_COLUMN);
      Integer classifierColumn = columnHeaders.get(ColumnType.CLASSIFIER_COLUMN);
      Integer digestColumn = columnHeaders.get(ColumnType.DIGEST_COLUMN);
      String groupId = "";
      String artifactId = "";
      String type = JAR;
      String version = "";
      String classifier = "";
      String digest = "";

      for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); ++i) {
        HSSFRow row = sheet.getRow(i);
        if (row != null) {
          HSSFCell cell = row.getCell(columnHeaders.get(ColumnType.GROUP_COLUMN).intValue());
          if (cell != null) {
            String gId = cell.getStringCellValue().trim();
            if (!gId.isEmpty()) {
              groupId = gId;
            }
          }

          cell = row.getCell(columnHeaders.get(ColumnType.ARTIFACT_COLUMN).intValue());
          if (cell != null) {
            String aId = cell.getStringCellValue().trim();
            if (!aId.isEmpty()) {
              artifactId = aId;
            }
          }

          cell = row.getCell(columnHeaders.get(ColumnType.VERSION_COLUMN).intValue());
          if (cell != null) {
            String v;
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              v = String.valueOf(cell.getNumericCellValue());
            } else {
              v = cell.getStringCellValue().trim();
            }
            if (!v.isEmpty()) {
              version = v;
            }
          }

          cell = (typeColumn != null) ? row.getCell(typeColumn.intValue()) : null;
          if (cell != null) {
            type = cell.getStringCellValue().trim();
          }

          cell = (classifierColumn != null) ? row.getCell(classifierColumn.intValue()) : null;
          if (cell != null) {
            classifier = cell.getStringCellValue().trim();
          }

          cell = (digestColumn != null) ? row.getCell(digestColumn.intValue()) : null;
          if (cell != null) {
            digest = cell.getStringCellValue().trim();
          }

          if (groupId.isEmpty() || artifactId.isEmpty() || version.isEmpty()) {
            if (groupId.isEmpty() || version.isEmpty()) {
              project.log(
                  "Row "
                      + row.getRowNum()
                      + ": Invalid artifact specified: [groupId: "
                      + groupId
                      + ", artifactId: "
                      + artifactId
                      + ", classifier: "
                      + classifier
                      + ", version: "
                      + version
                      + ", digest: "
                      + digest
                      + "]");
            }
          } else {
            artifacts.add(new Artifact(groupId, artifactId, type, classifier, version, digest));
          }
        }

        artifactId = "";
        classifier = "";
        digest = "";
        type = JAR;
      }

      project.log(sheet.getLastRowNum() + " rows read from " + xlsFile, Project.MSG_VERBOSE);
    } finally {
      if (workBook != null) {
        workBook.close();
      }
      Closer.close(bis);
    }

    return artifacts;
  }
Example #10
0
  public static void writeExcelFile(String fileLocation) {
    try {
      FileOutputStream fileOut = new FileOutputStream(fileLocation);

      HSSFWorkbook workbook = new HSSFWorkbook();

      Font bold = workbook.createFont(); // Create font
      bold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Make font bold

      CellStyle correctCell = workbook.createCellStyle();
      correctCell.setFillForegroundColor(HSSFColor.GREEN.index);
      correctCell.setFillBackgroundColor(HSSFColor.GREEN.index);
      correctCell.setFillPattern(CellStyle.SOLID_FOREGROUND);

      CellStyle incorrectCell = workbook.createCellStyle();
      incorrectCell.setFillForegroundColor(HSSFColor.RED.index);
      incorrectCell.setFillBackgroundColor(HSSFColor.RED.index);
      incorrectCell.setFillPattern(CellStyle.SOLID_FOREGROUND);

      CellStyle classificationCells = workbook.createCellStyle();
      classificationCells.setFillForegroundColor(HSSFColor.YELLOW.index);
      classificationCells.setFillBackgroundColor(HSSFColor.YELLOW.index);
      classificationCells.setFillPattern(CellStyle.SOLID_FOREGROUND);

      CellStyle attributeNameCells = workbook.createCellStyle();
      attributeNameCells.setFont(bold);

      CellStyle classificationAttributeCell = workbook.createCellStyle();
      classificationAttributeCell.setFillForegroundColor(HSSFColor.YELLOW.index);
      classificationAttributeCell.setFillBackgroundColor(HSSFColor.YELLOW.index);
      classificationAttributeCell.setFillPattern(CellStyle.SOLID_FOREGROUND);
      classificationAttributeCell.setFont(bold);

      Sheet worksheet = workbook.createSheet("Results");
      Row currRow = worksheet.createRow(0);
      for (int attribute = 0; attribute < metadataLL.size() + 1; attribute++) {
        Cell currCell = currRow.createCell(attribute);
        if (attribute < metadataLL.size()) {
          currCell.setCellValue(metadataLL.get(attribute)[0]);
          if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) {
            currCell.setCellStyle(classificationAttributeCell);
          } else {
            currCell.setCellStyle(attributeNameCells);
          }
        } else {
          currCell.setCellValue("Guessed Classification");
          currCell.setCellStyle(attributeNameCells);
        }
      }

      int correct = 0;
      int incorrect = 0;
      for (int node = 0; node < testDataLL.size(); node++) {
        currRow = worksheet.createRow(node + 1); // Offset by one since first row is header data
        int classifierCompleted = 0; // Necessary for if data does not end in classifier
        for (int attribute = 0;
            attribute < metadataLL.size() + 2;
            attribute++) // +1 for the row for guessed data +1 for the row that contains
        {
          Cell currCell = currRow.createCell(attribute);

          if (attribute < metadataLL.size()) // Print testingData
          {
            if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) {
              currCell.setCellValue(actualClassifications.get(node));
              currCell.setCellStyle(classificationCells);
              classifierCompleted++;
            } else {
              currCell.setCellValue(testDataLL.get(node)[attribute - classifierCompleted]);
            }
          } else if (attribute == metadataLL.size()) // Print guessed classification
          {
            currCell.setCellValue(guessedClassifications.get(node));
            if (guessedClassifications.get(node).compareTo(actualClassifications.get(node)) == 0) {
              currCell.setCellStyle(correctCell);
              correct++;
            } else {
              currCell.setCellStyle(incorrectCell);
              incorrect++;
            }

            if (node == testDataLL.size() - 1) // If this is the last loop
            {
              double precentRight = (double) correct / (correct + incorrect);

              currRow = worksheet.createRow(node + 2);
              currCell = currRow.createCell(attribute);

              currCell.setCellValue(precentRight);
              if (precentRight > .90) {
                correctCell.setDataFormat(workbook.createDataFormat().getFormat("0.000%"));
                currCell.setCellStyle(correctCell);
              } else {
                incorrectCell.setDataFormat(workbook.createDataFormat().getFormat("0.000%"));
                currCell.setCellStyle(incorrectCell);
              }
            }
          } else if (attribute
              == metadataLL.size() + 1) // Print potential bad training data if the flag is true
          {
            if (unseenDataFlag.get(node)) {
              currCell.setCellValue(
                  "This node an attribute value not in the training set, classifier selected is based on most frequent classifier. If laplacian smoothing is 1 or more this likely wont happen"); // TODO make this a bit shorter
            }
          }
        }
      }

      worksheet = workbook.createSheet("Training Data");
      currRow = worksheet.createRow(0);
      for (int attribute = 0; attribute < metadataLL.size(); attribute++) {
        Cell currCell = currRow.createCell(attribute);
        currCell.setCellValue(metadataLL.get(attribute)[0]);
        currCell.setCellStyle(attributeNameCells);
      }

      for (int node = 0; node < trainingDataLL.size(); node++) {
        currRow = worksheet.createRow(node + 1); // Offset by one since first row is header data
        int classifierCompleted = 0; // Necessary for if data does not end in classifier
        for (int attribute = 0; attribute < metadataLL.size(); attribute++) {
          Cell currCell = currRow.createCell(attribute);

          if (metadataLL.get(attribute)[2].compareTo("classifier") == 0) {
            currCell.setCellValue(knownClassifications.get(node));
            classifierCompleted++;
          } else {
            currCell.setCellValue(trainingDataLL.get(node)[attribute - classifierCompleted]);
          }
        }
      }

      worksheet = workbook.createSheet("Likelihood");
      currRow = worksheet.createRow(0);

      int largestAttributeSize = 0;

      for (int attribute = 0; attribute < classifier.size(); attribute++) {
        if (classifier.get(attribute).size() > largestAttributeSize) {
          largestAttributeSize = classifier.get(attribute).size();
        }
      }

      // Label attributes along the top
      for (int i = 0; i < metadataLL.size(); i++) {
        if (i == 0) {
          Cell currCell = currRow.createCell(i);
          // currCell.setCellValue("Attributes");
          currCell.setCellStyle(attributeNameCells);
        } else {
          Cell currCell = currRow.createCell(i);
          currCell.setCellValue(
              metadataLL
                  .get(i - 1)[0]); // -1 since the first cell does not contain a attribute name
          currCell.setCellStyle(attributeNameCells);
        }
      }

      // List possible classifications on the side and classification likelihoods at the end
      for (int i = 0;
          i
              < (largestAttributeSize * (classificationTypes.size() + 1)
                  + classificationTypes.size()
                  + 1);
          i++) // +1 since the first row of each stride lists each attributes string of what
      // occurrence the likelihoods are displaying
      { // +classificationTypes.size() so we can list the classification types likelihood at the end
        currRow = worksheet.createRow(i + 1); // +1 since first row is attribute names
        Cell currCell = currRow.createCell(0);

        int currentClassificationType =
            i
                % (classificationTypes.size()
                    + 1); // +1 since the first row of each stride lists each attributes string of
        // what occurrence the likelihoods are displaying

        // List the classification type of each row along the side
        if (i
            < largestAttributeSize
                * (classificationTypes.size()
                    + 1)) // +1 since the first row of each stride lists each attributes string of
        // what occurrence the likelihoods are displaying
        {
          for (int j = 0;
              j < classificationTypes.size() + 1;
              j++) // +1 since the first row of each stride lists each attributes string of what
          // occurrence the likelihoods are displaying
          {
            if (currentClassificationType == 0) {
              // Do nothing for now may have it say something later
            } else if (currentClassificationType == j) {
              currCell.setCellValue(
                  classificationTypes.get(
                      j - 1)); // -1 since the first cell does not contain a classification type
            }
          }
        } else // List the likelihood of each classification at the end
        {

          for (int j = 0;
              j < classificationTypes.size() + 1;
              j++) // +1 since the first row of each stride lists each attributes string of what
          // occurrence the likelihoods are displaying
          {
            if (currentClassificationType == 0) {
              // Do nothing for now may have it say value later
            } else if (currentClassificationType == j) {
              currCell.setCellValue(
                  "Likelihood of: "
                      + classificationTypes.get(j - 1)
                      + " is "
                      + classificationLikelihood.get(
                          j - 1)); // -1 since the first cell does not contain a classification type
            }
          }
        }
        currCell.setCellStyle(attributeNameCells);
      }

      // List the data
      for (int attribute = 0; attribute < classifier.size(); attribute++) {
        for (int occurrences = 0; occurrences < classifier.get(attribute).size(); occurrences++) {
          for (int classification = 0;
              classification < classifier.get(attribute).get(occurrences).length;
              classification++) {
            currRow =
                worksheet.getRow(
                    (occurrences * classifier.get(attribute).get(occurrences).length
                            + classification)
                        + 1); // +1 since first row is attribute names
            Cell currCell =
                currRow.createCell(
                    (attribute) + 1); // TODO figure out why this errors out at i:0 j:4 k:0
            // largestAttributeSize:105 on kidney dataset
            currCell.setCellValue(classifier.get(attribute).get(occurrences)[classification]);
          }
        }
      }

      workbook.write(fileOut);
      workbook.close();
      workbook.close();
    } catch (FileNotFoundException e) {
      System.out.println("Error file not found");
      e.printStackTrace();
      System.exit(0);
    } catch (IOException e) {
      System.out.println("Unable to output file, is the output destination writelocked?");
      e.printStackTrace();
      System.exit(0);
    }
  }
Example #11
-1
  public void testCorrectOrderInOptRecord() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();

    try {
      HSSFSheet sheet = wb.createSheet();
      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

      HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor());
      EscherOptRecord opt = HSSFTestHelper.getOptRecord(textbox);

      String opt1Str = opt.toXml();

      textbox.setFillColor(textbox.getFillColor());
      EscherContainerRecord container = HSSFTestHelper.getEscherContainer(textbox);
      EscherOptRecord optRecord = container.getChildById(EscherOptRecord.RECORD_ID);
      assertEquals(opt1Str, optRecord.toXml());
      textbox.setLineStyle(textbox.getLineStyle());
      assertEquals(opt1Str, optRecord.toXml());
      textbox.setLineWidth(textbox.getLineWidth());
      assertEquals(opt1Str, optRecord.toXml());
      textbox.setLineStyleColor(textbox.getLineStyleColor());
      assertEquals(opt1Str, optRecord.toXml());
    } finally {
      wb.close();
    }
  }