Exemple #1
0
  /**
   * Create a new row within the sheet and return the high level representation
   *
   * @param rownum row number
   * @return high level Row object representing a row in the sheet
   * @throws IllegalArgumentException If the max. number of rows is exceeded or a rownum is provided
   *     where the row is already flushed to disk.
   * @see #removeRow(Row)
   */
  public Row createRow(int rownum) {
    int maxrow = SpreadsheetVersion.EXCEL2007.getLastRowIndex();
    if (rownum < 0 || rownum > maxrow) {
      throw new IllegalArgumentException(
          "Invalid row number (" + rownum + ") outside allowable range (0.." + maxrow + ")");
    }

    // attempt to overwrite a row that is already flushed to disk
    if (rownum <= _writer.getLastFlushedRow()) {
      throw new IllegalArgumentException(
          "Attempting to write a row["
              + rownum
              + "] "
              + "in the range [0,"
              + _writer.getLastFlushedRow()
              + "] that is already written to disk.");
    }

    // attempt to overwrite a existing row in the input template
    if (_sh.getPhysicalNumberOfRows() > 0 && rownum <= _sh.getLastRowNum()) {
      throw new IllegalArgumentException(
          "Attempting to write a row["
              + rownum
              + "] "
              + "in the range [0,"
              + _sh.getLastRowNum()
              + "] that is already written to disk.");
    }

    // Make the initial allocation as big as the row above.
    Row previousRow = rownum > 0 ? getRow(rownum - 1) : null;
    int initialAllocationSize = 0;
    // have previous row in memory -> take that value.
    if (previousRow != null) initialAllocationSize = previousRow.getLastCellNum();
    // are we called after a flush(0)? If yes, ask the writer for the value.
    if (initialAllocationSize <= 0 && _writer.getNumberOfFlushedRows() > 0)
      initialAllocationSize = _writer.getNumberOfCellsOfLastFlushedRow();
    // default to 10 on the first row.
    if (initialAllocationSize <= 0) initialAllocationSize = 10;
    SXSSFRow newRow = new SXSSFRow(this, initialAllocationSize);
    _rows.put(new Integer(rownum), newRow);
    if (_randomAccessWindowSize >= 0 && _rows.size() > _randomAccessWindowSize) {
      try {
        flushRows(_randomAccessWindowSize);
      } catch (IOException ioe) {
        throw new RuntimeException(ioe);
      }
    }
    return newRow;
  }
Exemple #2
0
 public static void WriteCol(String QueryType, String num) throws IOException {
   Map<String, Object[]> data = new HashMap<String, Object[]>();
   List<String> sqlDML = new ArrayList<String>();
   File myfile = new File("C://Users/Administrator/Documents/Heckathon/Result.xlsx");
   FileInputStream fis = new FileInputStream(myfile);
   XSSFWorkbook myworkbook = new XSSFWorkbook(fis);
   XSSFSheet mysheet = myworkbook.getSheetAt(2);
   data.put(num, new Object[] {QueryType, num});
   Set<String> newRows = data.keySet();
   int rownum = mysheet.getPhysicalNumberOfRows();
   for (String Key : newRows) {
     Row row = mysheet.createRow(rownum++);
     Object[] objArr = data.get(Key);
     int cellnum = 0;
     for (Object obj : objArr) {
       Cell cell = row.createCell(cellnum++);
       cell.setCellValue((String) obj);
     }
   }
   FileOutputStream os = new FileOutputStream(myfile);
   myworkbook.write(os);
   System.out.println("Record Entered");
 }
    /**
     * 读取2007-2013格式
     *
     * @param filePath 文件路径
     * @return
     * @throws java.io.IOException
     */
    @SuppressWarnings("rawtypes")
    public static List<Map> readExcel2007(String filePath) throws IOException {
      List<Map> valueList = new ArrayList<Map>();
      FileInputStream fis = null;
      try {
        fis = new FileInputStream(filePath);
        XSSFWorkbook xwb = new XSSFWorkbook(fis); // 构造 XSSFWorkbook 对象,strPath 传入文件路径
        XSSFSheet sheet = xwb.getSheetAt(0); // 读取第一章表格内容
        // 定义 row、cell
        XSSFRow row;
        // 循环输出表格中的第一行内容   表头
        Map<Integer, String> keys = new HashMap<Integer, String>();
        row = sheet.getRow(0);
        if (row != null) {
          // System.out.println("j = row.getFirstCellNum()::"+row.getFirstCellNum());
          // System.out.println("row.getPhysicalNumberOfCells()::"+row.getPhysicalNumberOfCells());
          for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
            // 通过 row.getCell(j).toString() 获取单元格内容,
            if (row.getCell(j) != null) {
              if (!row.getCell(j).toString().isEmpty()) {
                keys.put(j, row.getCell(j).toString());
              }
            } else {
              keys.put(j, "K-R1C" + j + "E");
            }
          }
        }
        // 循环输出表格中的从第二行开始内容
        for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
          row = sheet.getRow(i);
          if (row != null) {
            boolean isValidRow = false;
            Map<String, Object> val = new HashMap<String, Object>();
            for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
              XSSFCell cell = row.getCell(j);
              if (cell != null) {
                String cellValue = null;
                if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                  if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue =
                        new DataFormatter()
                            .formatRawCellContents(
                                cell.getNumericCellValue(), 0, "yyyy-MM-dd HH:mm:ss");
                  } else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                  }
                } else {
                  cellValue = cell.toString();
                }
                if (cellValue != null && cellValue.trim().length() <= 0) {
                  cellValue = null;
                }
                val.put(keys.get(j), cellValue);
                if (!isValidRow && cellValue != null && cellValue.trim().length() > 0) {
                  isValidRow = true;
                }
              }
            }

            // 第I行所有的列数据读取完毕,放入valuelist
            if (isValidRow) {
              valueList.add(val);
            }
          }
        }
      } catch (IOException e) {
        e.printStackTrace();
      } finally {
        fis.close();
      }

      return valueList;
    }