Example #1
0
 /**
  * 取得当前sheet的sheet名
  *
  * @return
  */
 public String getSheetName() {
   if (readOnlyWBook == true) {
     return currentSheet.getName();
   } else {
     return wrCurrentSheet.getName();
   }
 }
  private List<String> getInvestigationNameFromExcel(File excelFile) throws Exception {

    File tmpInvestigation =
        new File(System.getProperty("java.io.tmpdir") + File.separator + "tmpInvestigation.txt");
    if (tmpInvestigation.exists()) {
      boolean deleteSuccess = tmpInvestigation.delete();
      if (!deleteSuccess) {
        throw new Exception("Deletion of tmp file 'tmpInvestigation.txt' failed, cannot proceed.");
      }
    }
    boolean createSuccess = tmpInvestigation.createNewFile();
    if (!createSuccess) {
      throw new Exception("Creation of tmp file 'tmpInvestigation.txt' failed, cannot proceed.");
    }

    Workbook workbook = Workbook.getWorkbook(excelFile);
    for (Sheet sheet : workbook.getSheets()) {
      if (sheet.getName().toLowerCase().equals("investigation")) {
        writeSheetToFile(sheet, tmpInvestigation);
      }
    }

    List<String> names = XgapCommonImport.getInvestigationNameFromFile(tmpInvestigation);

    return names;
  }
Example #3
0
  /** Writes out the workbook data as XML, without formatting information */
  private void writeXML() throws IOException {
    try {
      OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
      BufferedWriter bw = new BufferedWriter(osw);

      bw.write("<?xml version=\"1.0\" ?>");
      bw.newLine();
      bw.write("<!DOCTYPE workbook SYSTEM \"workbook.dtd\">");
      bw.newLine();
      bw.newLine();
      bw.write("<workbook>");
      bw.newLine();
      for (int sheet = 0; sheet < workbook.getNumberOfSheets(); sheet++) {
        Sheet s = workbook.getSheet(sheet);

        bw.write("  <sheet>");
        bw.newLine();
        bw.write("    <name><![CDATA[" + s.getName() + "]]></name>");
        bw.newLine();

        Cell[] row = null;

        for (int i = 0; i < s.getRows(); i++) {
          bw.write("    <row number=\"" + i + "\">");
          bw.newLine();
          row = s.getRow(i);

          for (int j = 0; j < row.length; j++) {
            if (row[j].getType() != CellType.EMPTY) {
              bw.write("      <col number=\"" + j + "\">");
              bw.write("<![CDATA[" + row[j].getContents() + "]]>");
              bw.write("</col>");
              bw.newLine();
            }
          }
          bw.write("    </row>");
          bw.newLine();
        }
        bw.write("  </sheet>");
        bw.newLine();
      }

      bw.write("</workbook>");
      bw.newLine();

      bw.flush();
      bw.close();
    } catch (UnsupportedEncodingException e) {
      System.err.println(e.toString());
    }
  }
Example #4
0
  /**
   * Constructor
   *
   * @param w The workbook to interrogate
   * @param out The output stream to which the CSV values are written
   * @param encoding The encoding used by the output stream. Null or unrecognized values cause the
   *     encoding to default to UTF8
   * @param hide Suppresses hidden cells
   * @exception java.io.IOException
   */
  public CSV(Workbook w, OutputStream out, String encoding, boolean hide) throws IOException {
    if (encoding == null || !encoding.equals("UnicodeBig")) {
      encoding = "UTF8";
    }

    try {
      OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
      BufferedWriter bw = new BufferedWriter(osw);

      for (int sheet = 0; sheet < w.getNumberOfSheets(); sheet++) {
        Sheet s = w.getSheet(sheet);

        if (!(hide && s.getSettings().isHidden())) {
          bw.write("*** " + s.getName() + " ****");
          bw.newLine();

          Cell[] row = null;

          for (int i = 0; i < s.getRows(); i++) {
            row = s.getRow(i);

            if (row.length > 0) {
              if (!(hide && row[0].isHidden())) {
                bw.write(row[0].getContents());
                // Java 1.4 code to handle embedded commas
                // bw.write("\"" +
                // row[0].getContents().replaceAll("\"","\"\"")
                // + "\"");
              }

              for (int j = 1; j < row.length; j++) {
                bw.write(',');
                if (!(hide && row[j].isHidden())) {
                  bw.write(row[j].getContents());
                  // Java 1.4 code to handle embedded quotes
                  // bw.write("\"" +
                  // row[j].getContents().replaceAll("\"","\"\"")
                  // + "\"");
                }
              }
            }
            bw.newLine();
          }
        }
      }
      bw.flush();
      bw.close();
    } catch (UnsupportedEncodingException e) {
      System.err.println(e.toString());
    }
  }
  public static List<Map<String, String>> getXlsUserInfoData(String path) {
    try {
      File xlsFile = new File(path);
      Workbook book = Workbook.getWorkbook(xlsFile);
      Sheet[] sheet = book.getSheets();
      if (sheet.length > 0) {
        Sheet page = sheet[0];
        int columns = page.getColumns();
        System.out.println(columns);
        int rows = page.getRows();
        System.out.println(page.getName());
        if (rows < 2) return null;

        int columnsCount = 0;

        List<String> titleList = new ArrayList<String>();
        for (int i = 0; i < columns; i++) {
          // 列    行
          Cell cell = page.getCell(i, 0);
          String key = cell.getContents();
          if (key == null || key.equals("")) {
            continue;
          }
          columnsCount++;
          logger.debug(key);
          titleList.add(key);
        }
        columns = columnsCount;
        List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
        for (int i = 1; i < rows; i++) {
          logger.info("ii:" + i);
          Map<String, String> dataMap = new HashMap<String, String>();
          for (int j = 0; j < columns; j++) {
            String key = titleList.get(j);
            String value = page.getCell(j, i).getContents();
            dataMap.put(key, value);
            logger.debug(key + "  " + value);
          }
          dataList.add(dataMap);
        }
        logger.debug("xls length:" + dataList.size());
        return dataList;
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    }
    return null;
  }
  /**
   * Gets the sheet index in this workbook. Used when importing a sheet
   *
   * @param sheet the sheet
   * @return the 0-based sheet index, or -1 if it is not found
   */
  public int getIndex(Sheet sheet) {
    String name = sheet.getName();
    int index = -1;
    int pos = 0;

    for (Iterator i = boundsheets.iterator(); i.hasNext() && index == -1; ) {
      BoundsheetRecord br = (BoundsheetRecord) i.next();

      if (br.getName().equals(name)) {
        index = pos;
      } else {
        pos++;
      }
    }

    return index;
  }
Example #7
0
  /** Writes out the workbook data as XML, with formatting information */
  private void writeFormattedXML() throws IOException {
    try {
      OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
      BufferedWriter bw = new BufferedWriter(osw);

      bw.write("<?xml version=\"1.0\" ?>");
      bw.newLine();
      bw.write("<!DOCTYPE workbook SYSTEM \"formatworkbook.dtd\">");
      bw.newLine();
      bw.newLine();
      bw.write("<workbook>");
      bw.newLine();
      for (int sheet = 0; sheet < workbook.getNumberOfSheets(); sheet++) {
        Sheet s = workbook.getSheet(sheet);

        bw.write("  <sheet>");
        bw.newLine();
        bw.write("    <name><![CDATA[" + s.getName() + "]]></name>");
        bw.newLine();

        Cell[] row = null;
        CellFormat format = null;
        Font font = null;

        for (int i = 0; i < s.getRows(); i++) {
          bw.write("    <row number=\"" + i + "\">");
          bw.newLine();
          row = s.getRow(i);

          for (int j = 0; j < row.length; j++) {
            // Remember that empty cells can contain format information
            if ((row[j].getType() != CellType.EMPTY) || (row[j].getCellFormat() != null)) {
              format = row[j].getCellFormat();
              bw.write("      <col number=\"" + j + "\">");
              bw.newLine();
              bw.write("        <data>");
              bw.write("<![CDATA[" + row[j].getContents() + "]]>");
              bw.write("</data>");
              bw.newLine();

              if (row[j].getCellFormat() != null) {
                bw.write("        <format wrap=\"" + format.getWrap() + "\"");
                bw.newLine();
                bw.write(
                    "                align=\"" + format.getAlignment().getDescription() + "\"");
                bw.newLine();
                bw.write(
                    "                valign=\""
                        + format.getVerticalAlignment().getDescription()
                        + "\"");
                bw.newLine();
                bw.write(
                    "                orientation=\""
                        + format.getOrientation().getDescription()
                        + "\"");
                bw.write(">");
                bw.newLine();

                // The font information
                font = format.getFont();
                bw.write("          <font name=\"" + font.getName() + "\"");
                bw.newLine();
                bw.write("                point_size=\"" + font.getPointSize() + "\"");
                bw.newLine();
                bw.write("                bold_weight=\"" + font.getBoldWeight() + "\"");
                bw.newLine();
                bw.write("                italic=\"" + font.isItalic() + "\"");
                bw.newLine();
                bw.write(
                    "                underline=\""
                        + font.getUnderlineStyle().getDescription()
                        + "\"");
                bw.newLine();
                bw.write("                colour=\"" + font.getColour().getDescription() + "\"");
                bw.newLine();
                bw.write(
                    "                script=\"" + font.getScriptStyle().getDescription() + "\"");
                bw.write(" />");
                bw.newLine();

                // The cell background information
                if (format.getBackgroundColour() != Colour.DEFAULT_BACKGROUND
                    || format.getPattern() != Pattern.NONE) {
                  bw.write(
                      "          <background colour=\""
                          + format.getBackgroundColour().getDescription()
                          + "\"");
                  bw.newLine();
                  bw.write(
                      "                      pattern=\""
                          + format.getPattern().getDescription()
                          + "\"");
                  bw.write(" />");
                  bw.newLine();
                }

                // The cell border, if it has one
                if (format.getBorder(Border.TOP) != BorderLineStyle.NONE
                    || format.getBorder(Border.BOTTOM) != BorderLineStyle.NONE
                    || format.getBorder(Border.LEFT) != BorderLineStyle.NONE
                    || format.getBorder(Border.RIGHT) != BorderLineStyle.NONE) {

                  bw.write(
                      "          <border top=\""
                          + format.getBorder(Border.TOP).getDescription()
                          + "\"");
                  bw.newLine();
                  bw.write(
                      "                  bottom=\""
                          + format.getBorder(Border.BOTTOM).getDescription()
                          + "\"");
                  bw.newLine();
                  bw.write(
                      "                  left=\""
                          + format.getBorder(Border.LEFT).getDescription()
                          + "\"");
                  bw.newLine();
                  bw.write(
                      "                  right=\""
                          + format.getBorder(Border.RIGHT).getDescription()
                          + "\"");
                  bw.write(" />");
                  bw.newLine();
                }

                // The cell number/date format
                if (!format.getFormat().getFormatString().equals("")) {
                  bw.write("          <format_string string=\"");
                  bw.write(format.getFormat().getFormatString());
                  bw.write("\" />");
                  bw.newLine();
                }

                bw.write("        </format>");
                bw.newLine();
              }

              bw.write("      </col>");
              bw.newLine();
            }
          }
          bw.write("    </row>");
          bw.newLine();
        }
        bw.write("  </sheet>");
        bw.newLine();
      }

      bw.write("</workbook>");
      bw.newLine();

      bw.flush();
      bw.close();
    } catch (UnsupportedEncodingException e) {
      System.err.println(e.toString());
    }
  }