/**
   * get the specified excel sheet and put its value string to list.
   *
   * @param sheetName excel sheet name
   * @param ignoreRows first several rows not to read.
   * @param ignoreCols first several cols not to read.
   * @param readRows specified row count to read.
   * @param readColumns specified column count to read.
   * @throws RuntimeException
   */
  public List<String> excelToList(
      String sheetName, int ignoreRows, int ignoreCols, int readRows, int readColumns) {
    FileInputStream fso = null;
    List<String> paraList = new ArrayList<String>();

    try {
      fso = new FileInputStream(fileName);
      Workbook workBook = getWorkBook(fso, true);
      xlSheet = workBook.getSheet(sheetName);
      if (xlSheet == null) {
        LOG.error("sheet [" + sheetName + "] does not exist!");
        throw new RuntimeException("sheet [" + sheetName + "] does not exist!");
      }
      readRows = (readRows == 0) ? xlSheet.getPhysicalNumberOfRows() : readRows;
      for (int i = ignoreRows; i < ignoreRows + readRows; i++) {
        xlRow = xlSheet.getRow(i);
        readColumns = (readColumns == 0) ? xlRow.getPhysicalNumberOfCells() : readColumns;
        if (xlRow != null) {
          for (int j = ignoreCols; j < ignoreCols + readColumns; j++) {
            xlCell = xlRow.getCell(j);
            if (xlCell == null) {
              paraList.add("");
            } else {
              paraList.add(xlCell.toString());
            }
          }
        }
      }
      fso.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException("read excel failed:" + e.getMessage());
    }
    return paraList;
  }
  /**
   * put dataList to excel sheets.
   *
   * @param sheetName excel sheet name.
   * @param dataList data list to be parsed and put into excel sheets.
   * @param rowNum row count of the sheet to be modified.
   * @param ignoreRows rows to skip when put value.
   * @param ignoreColumns columns to skip when put value.
   * @throws RuntimeException
   * @throws IllegalArgumentException
   */
  public void putListToExcelWithFullIgnore(
      String sheetName, List<String> dataList, int rowNum, int ignoreRows, int ignoreColumns) {
    if (dataList.size() % rowNum != 0) {
      LOG.error("dataList has wrong element count for excel!");
      throw new IllegalArgumentException("dataList has wrong element count for excel!");
    }

    String value = null;
    int index = 0;
    final int colCount = dataList.size() / rowNum;
    Workbook workBook = null;

    try {
      if (new File(fileName).exists()) {
        workBook = getWorkBook(new FileInputStream(fileName), false);
      } else {
        workBook = getWorkBook(null, false);
      }
      xlSheet = workBook.getSheet(sheetName);
      if (xlSheet == null) {
        xlSheet = workBook.createSheet(sheetName);
      }

      for (int j = ignoreRows; j < ignoreRows + rowNum; j++) {
        xlRow = xlSheet.getRow(j);
        if (xlRow == null) {
          xlRow = xlSheet.createRow(j);
        }
        for (int i = ignoreColumns; i < ignoreColumns + colCount; i++) {
          value = dataList.get(index);
          xlCell = xlRow.getCell(i);
          if (xlCell == null) {
            xlCell = xlRow.createCell(i);
          }

          xlCell.setCellType(1);
          if (value != null) {
            xlCell.setCellValue(value);
          }
          index++;
        }
      }

      FileOutputStream fileOut = new FileOutputStream(fileName);
      workBook.write(fileOut);
      fileOut.flush();
      fileOut.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException("set excel value failed:" + e.getMessage());
    }
  }
  /**
   * read excel Xls and add the result into arraylist.
   *
   * @param sheetName excel sheet name
   * @throws RuntimeException
   */
  public List<Map<String, String>> excelToList(String sheetName) {
    Row firstxlRow = null;
    FileInputStream fso = null;
    List<Map<String, String>> paraList = new ArrayList<Map<String, String>>();

    try {
      fso = new FileInputStream(fileName);
      Workbook workBook = getWorkBook(fso, true);
      xlSheet = workBook.getSheet(sheetName);
      if (xlSheet == null) {
        LOG.error("sheet [" + sheetName + "] does not exist!");
        return null;
      }
      firstxlRow = xlSheet.getRow(xlSheet.getFirstRowNum());
      int firstCell = firstxlRow.getFirstCellNum();
      int lastCell = firstxlRow.getLastCellNum();
      List<String> keyList = new ArrayList<String>();

      for (int cNum = firstCell; cNum < lastCell; cNum++) {
        if (firstxlRow.getCell(cNum).toString() == null) {
          break;
        }
        keyList.add(firstxlRow.getCell(cNum).toString());
      }

      for (int i = xlSheet.getFirstRowNum() + 1; i < xlSheet.getPhysicalNumberOfRows(); i++) {
        xlRow = xlSheet.getRow(i);
        List<String> valueList = new ArrayList<String>();
        if (xlRow == null) {
          break;
        }
        for (int j = firstCell; j < lastCell; j++) {
          xlCell = xlRow.getCell(j);
          if (xlCell == null) {
            valueList.add(null);
            continue;
          } else {
            valueList.add(xlCell.toString());
          }
        }
        paraList.add(creatMap(keyList, valueList));
      }
      fso.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException("read excel failed:" + e.getMessage());
    }
    return paraList;
  }
 /**
  * write excel sheet, specified value to specified cell.
  *
  * @param sheetName excel sheet name
  * @param row row index which to be changed
  * @param col column index which to be changed
  * @param value value to be put into cell
  * @throws RuntimeException
  */
 public void setExcelValue(String sheetName, int row, int col, String value) {
   Workbook workBook = null;
   try {
     if (new File(fileName).exists()) {
       workBook = getWorkBook(new FileInputStream(fileName), false);
     } else {
       workBook = getWorkBook(null, false);
     }
     xlSheet = workBook.getSheet(sheetName);
     if (xlSheet == null) {
       xlSheet = workBook.createSheet(sheetName);
     }
     xlRow = xlSheet.getRow(row - 1);
     if (xlRow == null) {
       xlRow = xlSheet.createRow((short) row - 1);
     }
     xlCell = xlRow.getCell(col - 1);
     if (xlCell == null) {
       xlCell = xlRow.createCell(col - 1);
     }
     xlCell.setCellType(1); // set cell type as string
     xlCell.setCellValue(value);
     FileOutputStream fileOut = new FileOutputStream(fileName);
     workBook.write(fileOut);
     fileOut.flush();
     fileOut.close();
   } catch (Exception e) {
     LOG.error(e);
     throw new RuntimeException("set excel value failed:" + e.getMessage());
   }
 }
 /**
  * put data into map.
  *
  * @param keys map key names
  * @param parms map key values
  * @throws RuntimeException
  */
 public Map<String, String> creatMap(List<String> keys, List<String> parms) {
   if (keys.size() != parms.size()) {
     LOG.error("incorrect parameters, the size of list not equals!");
     throw new RuntimeException("incorrect parameters, the size of list not equals!");
   }
   Map<String, String> paraMap = new HashMap<String, String>();
   for (int i = 0; i < keys.size(); i++) {
     paraMap.put(keys.get(i), parms.get(i));
   }
   return paraMap;
 }
 /**
  * get excel cell value of specified cell.
  *
  * @param sheetName excel sheet name
  * @param row row index which to be changed
  * @param col column index which to be changed
  * @return excel cell value string
  * @throws RuntimeException
  */
 public String getExcelValue(String sheetName, int row, int col) {
   String text = null;
   FileInputStream fso = null;
   try {
     fso = new FileInputStream(fileName);
     Workbook workBook = getWorkBook(fso, true);
     xlSheet = workBook.getSheet(sheetName);
     if (xlSheet == null) {
       LOG.error("sheet [" + sheetName + "] does not exist!");
       throw new RuntimeException("sheet [" + sheetName + "] does not exist!");
     }
     xlRow = xlSheet.getRow(row - 1);
     xlCell = (xlRow == null) ? null : xlRow.getCell(col - 1);
     text = (xlCell == null) ? "" : xlCell.toString();
     fso.close();
   } catch (Exception e) {
     LOG.error(e);
     throw new RuntimeException("read excel failed:" + e.getMessage());
   }
   return text;
 }
 private Workbook getWorkBook(FileInputStream fso, Boolean readOnly) {
   String fileExt = fileName.substring(fileName.lastIndexOf(".") + 1);
   try {
     if (new File(fileName).exists()) {
       return getWorkBookStyle(fso);
     } else {
       readFileOnNotExist(readOnly);
       return getWorkBookStyle(fileExt);
     }
   } catch (Exception e) {
     LOG.error(e);
     throw new RuntimeException(e);
   }
 }
  /**
   * Description: get the txt file content.
   *
   * @return the txt file content list.
   */
  public List<String> readTXTToList() {
    String line = null;
    BufferedReader br = null;

    List<String> List = new ArrayList<String>();
    try {
      br = new BufferedReader(new InputStreamReader(new FileInputStream(fileName), charSet));
      while (br.ready() && (line = br.readLine()) != null) {
        List.add(line);
      }
      br.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException(e);
    } finally {
      System.gc();
    }
    return List;
  }
  /**
   * Description: change value of the specified position.
   *
   * @param row the row position.
   * @param col the column position.
   * @param input the text value to input.
   */
  public void inputTxtValue(int row, int col, String input) {
    List<String> original = readTXTToList();
    BufferedWriter bw = null;
    StringBuffer sb = new StringBuffer();
    String[] text = null;
    String temp = null;
    String txtMark = original.get(0).contains("\t") ? "\t" : _mark;

    try {
      bw =
          new BufferedWriter(
              new OutputStreamWriter(new FileOutputStream(fileName, false), charSet));

      for (int irow = 0; irow < original.size(); irow++) {
        if (irow != row - 1) {
          bw.write(original.get(irow));
        } else {
          temp = original.get(irow).replaceAll("\\t", _mark);
          text = temp.split(_mark);
          for (int icol = 0; icol < text.length; icol++) {
            if (icol != col - 1) {
              sb.append(text[icol]);
              sb.append(txtMark);
            } else {
              sb.append(input);
              sb.append(txtMark);
            }
          }
          bw.write(sb.toString());
        }
        if (irow != original.size() - 1) {
          bw.newLine();
        }
      }
      bw.flush();
      bw.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException(e);
    } finally {
      System.gc();
    }
  }
 /**
  * add map to arraylist.
  *
  * @throws RuntimeException
  */
 @SuppressWarnings("resource")
 public List<Map<String, String>> configList() {
   List<Map<String, String>> paraList = new ArrayList<Map<String, String>>();
   try {
     BufferedReader br =
         new BufferedReader(new InputStreamReader(new FileInputStream(fileName), charSet));
     String strKey = br.readLine();
     String[] keys = strKey.replaceAll("\\t", _mark).split(_mark);
     String strParm = null;
     while ((strParm = br.readLine()) != null && strParm.length() > 0) {
       String[] parms = strParm.replaceAll("\\t", _mark).split(_mark);
       paraList.add(creatParaMap(keys, parms));
     }
   } catch (Exception e) {
     LOG.error(e);
     throw new RuntimeException("execute extern file failed:" + e.getMessage());
   } finally {
     System.gc();
   }
   return paraList;
 }
  /**
   * Description: get the specified row and column value of the txt file.
   *
   * @param row the row position.
   * @param col the column position.
   * @return text value of the file specified position.
   */
  public String getTxtValue(int row, int col) {
    BufferedReader br = null;
    int irow = 0;
    String[][] value = new String[1024][1024];

    try {
      br = new BufferedReader(new InputStreamReader(new FileInputStream(fileName), charSet));
      String temp;
      while ((temp = br.readLine()) != null) {
        temp = temp.replaceAll("\\t", _mark);
        value[irow] = temp.split(_mark);
        irow++;
      }
      br.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException(e);
    } finally {
      System.gc();
    }
    return value[row - 1][col - 1];
  }
Example #12
0
 private void readFileOnNotExist(Boolean readOnly) {
   if (readOnly) {
     LOG.error("file [" + fileName + "] does not exist!");
     throw new RuntimeException("file [" + fileName + "] does not exist!");
   }
 }