public int getCcIndexForCell(Cell cell, ArrayList<ConnexComposant> ccs) {
   for (int i = 0; i < ccs.size(); i++) {
     if (cell.getRow() >= ccs.get(i).getStartY()
         && cell.getRow() <= ccs.get(i).getEndY()
         && cell.getColumn() >= ccs.get(i).getStartX()
         && cell.getColumn() <= ccs.get(i).getEndX()) {
       return i;
     }
   }
   return -1;
 }
Example #2
0
  public String[][] getTableArray() throws Exception {
    String[][] tabArray = null;

    Workbook workbook = Workbook.getWorkbook(new File(inputFile));
    Sheet sheet = workbook.getSheet(sheetName);
    int startRow, startCol, endRow, endCol, ci, cj;
    Cell tableStart = sheet.findCell(tableName);
    startRow = tableStart.getRow();
    startCol = tableStart.getColumn();
    Cell tableEnd = sheet.findCell(tableName, startCol + 1, startRow + 1, 400, 64000, false);
    // Cell tableEnd= sheet.findCell(tableName, startCol+1,startRow+1, 100, 64000,  false);

    endRow = tableEnd.getRow();
    endCol = tableEnd.getColumn();
    System.out.println(
        "startRow="
            + startRow
            + ", endRow="
            + endRow
            + ", "
            + "startCol="
            + startCol
            + ", endCol="
            + endCol);
    tabArray = new String[endRow - startRow - 1][endCol - startCol - 1];
    ci = 0;

    for (int i = startRow + 1; i < endRow; i++, ci++) {
      cj = 0;
      for (int j = startCol + 1; j < endCol; j++, cj++) {
        tabArray[ci][cj] = sheet.getCell(j, i).getContents();
      }
    }

    return (tabArray);
  }
  /**
   * Récupère la liste des étudiants dans un xls
   *
   * @param uri : l'uri du fichier xls
   * @return la liste remplie
   * @author Foulon Alain
   */
  public static GestionEtudiants getValsFromXls(String uri) throws BiffException, IOException {
    GestionEtudiants liste = new GestionEtudiants();

    try {
      // Récupère le fichier
      File fich = new File(uri);
      WorkbookSettings workSet = new WorkbookSettings(); // config de fichier .xls
      workSet.setLocale(new Locale("fr", "FR")); // vars locales
      Workbook workbook =
          Workbook.getWorkbook(fich, workSet); // ouvre un fichier existant en fct des settings
      Sheet feuille = workbook.getSheet(0); // ouvre la première feuille du fichier

      Cell cellNom = feuille.findCell("NOM"); // trouve la cellule "NOM" dans la feuille
      // soit : REGEX sur "nom, Nom, noms, NOM, NOMS..."
      // soit : fct qui teste les possibilités; (note: la CELL est null si non trouvée)

      int colNom = cellNom.getColumn(); // colonne noms
      int colPrenom = colNom + 1; // colonne prénoms
      int firstRow = cellNom.getRow() + 1; // premiere ligne
      int lastRow = feuille.getRows(); // derniere ligne

      // boucle de remplissage
      for (int i = firstRow, j = 0; i < lastRow; i++) {
        // je récupère les infors
        String nom = feuille.getCell(colNom, i).getContents();
        String prenom = feuille.getCell(colPrenom, i).getContents();

        // j'ajoute l'étudiant dans la liste
        j++;
        liste.add(new Etudiant(j, nom, prenom));
      }
    } catch (IOException ioe) {
      System.out.println("Erreur : " + ioe); // Erreur lors de l'ouverture du fichier Excel
    }

    // je retourne la liste finie
    return liste;
  }
Example #4
0
  /**
   * Check all the merged cells for borders. If the merge record has borders, then we need to rejig
   * the cell formats to take account of this. This is called by the write method of the
   * WritableWorkbookImpl, so that any new XFRecords that are created may be written out with the
   * others
   */
  void checkMergedBorders() {
    Range[] mcells = mergedCells.getMergedCells();
    ArrayList borderFormats = new ArrayList();
    for (int mci = 0; mci < mcells.length; mci++) {
      Range range = mcells[mci];
      Cell topLeft = range.getTopLeft();
      XFRecord tlformat = (XFRecord) topLeft.getCellFormat();

      if (tlformat != null && tlformat.hasBorders() == true && !tlformat.isRead()) {
        try {
          CellXFRecord cf1 = new CellXFRecord(tlformat);
          Cell bottomRight = range.getBottomRight();

          cf1.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
          cf1.setBorder(
              Border.LEFT,
              tlformat.getBorderLine(Border.LEFT),
              tlformat.getBorderColour(Border.LEFT));
          cf1.setBorder(
              Border.TOP, tlformat.getBorderLine(Border.TOP), tlformat.getBorderColour(Border.TOP));

          if (topLeft.getRow() == bottomRight.getRow()) {
            cf1.setBorder(
                Border.BOTTOM,
                tlformat.getBorderLine(Border.BOTTOM),
                tlformat.getBorderColour(Border.BOTTOM));
          }

          if (topLeft.getColumn() == bottomRight.getColumn()) {
            cf1.setBorder(
                Border.RIGHT,
                tlformat.getBorderLine(Border.RIGHT),
                tlformat.getBorderColour(Border.RIGHT));
          }

          int index = borderFormats.indexOf(cf1);
          if (index != -1) {
            cf1 = (CellXFRecord) borderFormats.get(index);
          } else {
            borderFormats.add(cf1);
          }
          ((WritableCell) topLeft).setCellFormat(cf1);

          // Handle the bottom left corner
          if (bottomRight.getRow() > topLeft.getRow()) {
            // Handle the corner cell
            if (bottomRight.getColumn() != topLeft.getColumn()) {
              CellXFRecord cf2 = new CellXFRecord(tlformat);
              cf2.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf2.setBorder(
                  Border.LEFT,
                  tlformat.getBorderLine(Border.LEFT),
                  tlformat.getBorderColour(Border.LEFT));
              cf2.setBorder(
                  Border.BOTTOM,
                  tlformat.getBorderLine(Border.BOTTOM),
                  tlformat.getBorderColour(Border.BOTTOM));

              index = borderFormats.indexOf(cf2);
              if (index != -1) {
                cf2 = (CellXFRecord) borderFormats.get(index);
              } else {
                borderFormats.add(cf2);
              }

              sheet.addCell(new Blank(topLeft.getColumn(), bottomRight.getRow(), cf2));
            }

            // Handle the cells down the left hand side (and along the
            // right too, if necessary)
            for (int i = topLeft.getRow() + 1; i < bottomRight.getRow(); i++) {
              CellXFRecord cf3 = new CellXFRecord(tlformat);
              cf3.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf3.setBorder(
                  Border.LEFT,
                  tlformat.getBorderLine(Border.LEFT),
                  tlformat.getBorderColour(Border.LEFT));

              if (topLeft.getColumn() == bottomRight.getColumn()) {
                cf3.setBorder(
                    Border.RIGHT,
                    tlformat.getBorderLine(Border.RIGHT),
                    tlformat.getBorderColour(Border.RIGHT));
              }

              index = borderFormats.indexOf(cf3);
              if (index != -1) {
                cf3 = (CellXFRecord) borderFormats.get(index);
              } else {
                borderFormats.add(cf3);
              }

              sheet.addCell(new Blank(topLeft.getColumn(), i, cf3));
            }
          }

          // Handle the top right corner
          if (bottomRight.getColumn() > topLeft.getColumn()) {
            if (bottomRight.getRow() != topLeft.getRow()) {
              // Handle the corner cell
              CellXFRecord cf6 = new CellXFRecord(tlformat);
              cf6.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf6.setBorder(
                  Border.RIGHT,
                  tlformat.getBorderLine(Border.RIGHT),
                  tlformat.getBorderColour(Border.RIGHT));
              cf6.setBorder(
                  Border.TOP,
                  tlformat.getBorderLine(Border.TOP),
                  tlformat.getBorderColour(Border.TOP));
              index = borderFormats.indexOf(cf6);
              if (index != -1) {
                cf6 = (CellXFRecord) borderFormats.get(index);
              } else {
                borderFormats.add(cf6);
              }

              sheet.addCell(new Blank(bottomRight.getColumn(), topLeft.getRow(), cf6));
            }

            // Handle the cells along the right
            for (int i = topLeft.getRow() + 1; i < bottomRight.getRow(); i++) {
              CellXFRecord cf7 = new CellXFRecord(tlformat);
              cf7.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf7.setBorder(
                  Border.RIGHT,
                  tlformat.getBorderLine(Border.RIGHT),
                  tlformat.getBorderColour(Border.RIGHT));

              index = borderFormats.indexOf(cf7);
              if (index != -1) {
                cf7 = (CellXFRecord) borderFormats.get(index);
              } else {
                borderFormats.add(cf7);
              }

              sheet.addCell(new Blank(bottomRight.getColumn(), i, cf7));
            }

            // Handle the cells along the top, and along the bottom too
            for (int i = topLeft.getColumn() + 1; i < bottomRight.getColumn(); i++) {
              CellXFRecord cf8 = new CellXFRecord(tlformat);
              cf8.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf8.setBorder(
                  Border.TOP,
                  tlformat.getBorderLine(Border.TOP),
                  tlformat.getBorderColour(Border.TOP));

              if (topLeft.getRow() == bottomRight.getRow()) {
                cf8.setBorder(
                    Border.BOTTOM,
                    tlformat.getBorderLine(Border.BOTTOM),
                    tlformat.getBorderColour(Border.BOTTOM));
              }

              index = borderFormats.indexOf(cf8);
              if (index != -1) {
                cf8 = (CellXFRecord) borderFormats.get(index);
              } else {
                borderFormats.add(cf8);
              }

              sheet.addCell(new Blank(i, topLeft.getRow(), cf8));
            }
          }

          // Handle the bottom right corner
          if (bottomRight.getColumn() > topLeft.getColumn()
              || bottomRight.getRow() > topLeft.getRow()) {
            // Handle the corner cell
            CellXFRecord cf4 = new CellXFRecord(tlformat);
            cf4.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
            cf4.setBorder(
                Border.RIGHT,
                tlformat.getBorderLine(Border.RIGHT),
                tlformat.getBorderColour(Border.RIGHT));
            cf4.setBorder(
                Border.BOTTOM,
                tlformat.getBorderLine(Border.BOTTOM),
                tlformat.getBorderColour(Border.BOTTOM));

            if (bottomRight.getRow() == topLeft.getRow()) {
              cf4.setBorder(
                  Border.TOP,
                  tlformat.getBorderLine(Border.TOP),
                  tlformat.getBorderColour(Border.TOP));
            }

            if (bottomRight.getColumn() == topLeft.getColumn()) {
              cf4.setBorder(
                  Border.LEFT,
                  tlformat.getBorderLine(Border.LEFT),
                  tlformat.getBorderColour(Border.LEFT));
            }

            index = borderFormats.indexOf(cf4);
            if (index != -1) {
              cf4 = (CellXFRecord) borderFormats.get(index);
            } else {
              borderFormats.add(cf4);
            }

            sheet.addCell(new Blank(bottomRight.getColumn(), bottomRight.getRow(), cf4));

            // Handle the cells along the bottom (and along the top
            // as well, if appropriate)
            for (int i = topLeft.getColumn() + 1; i < bottomRight.getColumn(); i++) {
              CellXFRecord cf5 = new CellXFRecord(tlformat);
              cf5.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf5.setBorder(
                  Border.BOTTOM,
                  tlformat.getBorderLine(Border.BOTTOM),
                  tlformat.getBorderColour(Border.BOTTOM));

              if (topLeft.getRow() == bottomRight.getRow()) {
                cf5.setBorder(
                    Border.TOP,
                    tlformat.getBorderLine(Border.TOP),
                    tlformat.getBorderColour(Border.TOP));
              }

              index = borderFormats.indexOf(cf5);
              if (index != -1) {
                cf5 = (CellXFRecord) borderFormats.get(index);
              } else {
                borderFormats.add(cf5);
              }

              sheet.addCell(new Blank(i, bottomRight.getRow(), cf5));
            }
          }
        } catch (WriteException e) {
          // just log e.toString(), not the whole stack trace
          logger.warn(e.toString());
        }
      }
    }
  }
Example #5
0
  /**
   * @param is 要导入Excel的输入流
   * @param sheetName 导入的工作表名称
   * @param entityClass List中对象的类型(Excel中的每一行都要转化为该类型的对象)
   * @param fieldMap 类的英文属性和Excel中的中文列名的对应关系 例:{id=编号}
   * @param uniqueFields 指定业务主键组合(即复合主键),这些列的组合不能重复
   * @return List
   * @throws ExcelException @Description 将Excel转化成实体对象List
   */
  public static <T> List<T> excelToList(
      InputStream is,
      String sheetName,
      Class<T> entityClass,
      LinkedHashMap<String, String> fieldMap,
      String[] uniqueFields)
      throws ExcelException {
    // 定义要返回的list
    List<T> resultList = new ArrayList<T>();

    try {
      // 根据excel数据源创建WorkBook
      Workbook wb = Workbook.getWorkbook(is);

      // 获取工作表
      Sheet sheet = wb.getSheet(sheetName);

      // 获取工作表的有效行数
      int realRows = 0;
      for (int i = 0; i < sheet.getRows(); i++) {

        int nullCols = 0;
        for (int j = 0; j < sheet.getColumns(); j++) {
          Cell CurrentCell = sheet.getCell(j, i);
          if (CurrentCell == null || "".equals(CurrentCell.getContents().toString())) {
            nullCols++;
          }
        }

        if (nullCols == sheet.getColumns()) {
          break;
        } else {
          realRows++;
        }
      }

      // 如果Excel中没有任何数据则提示错误信息
      if (realRows <= 1) {
        throw new ExcelException("Excel文件中没有任何数据");
      }

      Cell[] firstRow = sheet.getRow(0);
      String[] excelFieldNames = new String[firstRow.length];
      // 获取Excel的列名
      for (int i = 0; i < firstRow.length; i++) {
        excelFieldNames[i] = firstRow[i].getContents().toString().trim();
      }
      // 判断需要的字段在Excel中是否都存在
      boolean isExist = true;
      List<String> excelFieldList = Arrays.asList(excelFieldNames);
      for (String cnName : fieldMap.values()) {
        if (!excelFieldList.contains(cnName)) {
          isExist = false;
          break;
        }
      }

      // 如果有列名不存在或不匹配,则抛出异常并提示错误
      if (!isExist) {
        throw new ExcelException("Excel中缺少必要的字段,或字段名称有误");
      }

      // 将列名和列号放入Map中,这样通过列名就可以拿到列号
      LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
      for (int i = 0; i < excelFieldNames.length; i++) {
        colMap.put(excelFieldNames[i], firstRow[i].getColumn());
      }

      // 判断是否有重复行
      // 1.获取uniqueFields指定的列
      Cell[][] uniqueCells = new Cell[uniqueFields.length][];
      for (int i = 0; i < uniqueFields.length; i++) {
        int col = colMap.get(uniqueFields[i]);
        uniqueCells[i] = sheet.getColumn(col);
      }
      // 2.从指定列中寻找重复行
      for (int i = 1; i < realRows; i++) {
        int nullCols = 0;
        int length = uniqueFields.length;
        for (int j = 0; j < length; j++) {
          Cell currentCell = uniqueCells[j][i];
          String currentContent = currentCell.getContents().toString().trim();
          Cell sameCell =
              sheet.findCell(
                  currentContent,
                  currentCell.getColumn(),
                  currentCell.getRow() + 1,
                  currentCell.getColumn(),
                  uniqueCells[j][realRows - 1].getRow(),
                  true);
          if (sameCell != null) {
            nullCols++;
          }
        }
        // 复合主键,意味着这些列的组合不能重复,
        // 只有当所有的列都有重复的时候,才被认为是有重复行
        if (nullCols == length) {
          throw new Exception("Excel中有重复行,请检查");
        }
      }

      // 将sheet转换为list
      for (int i = 1; i < realRows; i++) {
        // 新建要转换的对象
        T entity = entityClass.newInstance();

        // 给对象中的字段赋值
        for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
          // 获取英文字段名
          String enNormalName = entry.getKey();
          // 获取中文字段名
          String cnNormalName = entry.getValue();
          // 根据中文字段名获取列号
          int col = colMap.get(cnNormalName);

          // 获取当前单元格中的内容
          String content = sheet.getCell(col, i).getContents().toString().trim();

          // 给对象赋值
          setFieldValueByName(enNormalName, content, entity);
        }

        resultList.add(entity);
      }
    } catch (Exception e) {
      e.printStackTrace();
      // 如果是ExcelException,则直接抛出
      if (e instanceof ExcelException) {
        throw (ExcelException) e;
      } else {
        // 否则将其包装成ExcelException,再将其抛出
        throw new ExcelException("导入ExceL失败");
      }
    }

    return resultList;
  }