Exemplo n.º 1
0
  /**
   * 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;
  }
Exemplo n.º 2
0
  /**
   * 获取销售记录
   *
   * @param productSheet
   * @return
   */
  private int getSaleRecordsFormXSheet(Sheet salesSheet) {
    int count = 0;
    if (salesSheet == null) return 0;
    try {
      int rowStart = salesSheet.getFirstRowNum();
      int rowEnd = salesSheet.getLastRowNum();
      for (int i = rowStart + 1; i <= rowEnd; i++) {
        Row row = salesSheet.getRow(i);

        SaleRecord sr = new SaleRecord();

        // 日期
        String datestr = String.valueOf(getXssCellData(row.getCell(0)));
        if ("".equals(datestr)) continue;
        Date date = null;
        try {
          date = new SimpleDateFormat("yyyy-MM-dd").parse(datestr);
        } catch (Exception e) {
          continue;
        }

        String struser = String.valueOf(getXssCellData(row.getCell(1)));
        // if have not user info discard this record
        if ("".equals(struser)) continue;
        int user = Integer.parseInt(struser);

        String strproduct = String.valueOf(getXssCellData(row.getCell(2)));
        // if have not user info discard this record
        if ("".equals(strproduct)) continue;
        int product = Integer.parseInt(strproduct);

        // 购买价格
        String pricestr = (String) getXssCellData(row.getCell(3));
        double price = 0d;
        if (!"".equals(pricestr)) {
          price = Double.parseDouble(pricestr);
        }

        // 购买量
        String numstr = (String) getXssCellData(row.getCell(3));
        double num = 0d;
        if (!"".equals(numstr)) {
          num = Double.parseDouble(numstr);
        }

        sr.setNum(num);
        sr.setPrice(price);
        sr.setProduct(product);
        sr.setUser(user);
        sr.setSaledate(date);

        simpleDao.createEntity(sr);
        count++;
      }
    } catch (Exception e) {
      e.printStackTrace();
      log.error("getSaleRecordsFormXSheet exception !");
    }
    return count;
  }
 @Override
 public int getColumnCount() {
   if (config != null) {
     return config.getColumnLast() - config.getColumnOffset() + 1;
   } else {
     Row row = sheet.getRow(sheet.getFirstRowNum());
     if (row == null) {
       return 0;
     } else {
       return row.getLastCellNum();
     }
   }
 }
Exemplo n.º 4
0
  private int getUserFormXSheet(Sheet userSheet) {
    int count = 0;
    if (userSheet == null) return 0;
    try {
      int rowStart = userSheet.getFirstRowNum();
      int rowEnd = userSheet.getLastRowNum();
      for (int i = rowStart + 1; i <= rowEnd; i++) {
        Row row = userSheet.getRow(i);

        User user = new User();

        String strid = String.valueOf(getXssCellData(row.getCell(0)));
        if ("".equals(strid)) continue;
        int id = Integer.parseInt(strid);

        String name = (String) getXssCellData(row.getCell(1));
        String address = (String) getXssCellData(row.getCell(2));

        String str_borthday = getXssCellData(row.getCell(3)).toString();
        Date borthday = null;
        if (!"".equals(str_borthday)) {
          try {
            borthday = new SimpleDateFormat("yyyy-MM-dd").parse(str_borthday);
          } catch (Exception e) {

          }
        }

        String gender = (String) getXssCellData(row.getCell(4));

        user.setId(id);
        user.setName(name);
        user.setAddress(address);
        user.setBirthday(borthday);
        if ("男".equals(gender)) {
          user.setGender("男");
        }

        simpleDao.createEntity(user);
        count++;
      }

    } catch (Exception e) {

      e.printStackTrace();
      log.error("getUserFormXSheet exception !");
    }
    return count;
  }
Exemplo n.º 5
0
  /**
   * 从excel表格中获取产品数据
   *
   * @param productSheet
   * @return
   */
  private int getProductsFormXSheet(Sheet productSheet) {
    int count = 0;
    if (productSheet == null) return 0;
    try {

      int rowStart = productSheet.getFirstRowNum();
      int rowEnd = productSheet.getLastRowNum();
      for (int i = rowStart + 1; i <= rowEnd; i++) {
        Row row = productSheet.getRow(i);

        Product product = new Product();

        String strid = String.valueOf(getXssCellData(row.getCell(0)));
        if ("".equals(strid)) strid = "0";
        int id = Integer.parseInt(strid);

        String name = (String) getXssCellData(row.getCell(1));

        String type = (String) getXssCellData(row.getCell(2));

        String coststr = (String) getXssCellData(row.getCell(3));
        double cost = 0d;
        if (!"".equals(coststr)) {
          cost = Double.parseDouble(coststr);
        }
        String unit = (String) getXssCellData(row.getCell(4));
        product.setId(id);
        product.setPname(name);
        product.setType(type);
        product.setCost(cost);
        product.setUnit(unit);

        simpleDao.createEntity(product);
        count++;
      }
    } catch (Exception e) {

      e.printStackTrace();
      log.error("getProductsFormXSheet exception !");
    }
    return count;
  }
  @Override
  public List<Entity> parse(File file) throws Exception {
    List<Entity> retVal = new ArrayList<Entity>();
    Workbook wb = null;
    FileInputStream fileIn = new FileInputStream(file);

    try {
      wb = WorkbookFactory.create(fileIn);
      Map<String, Integer> headers = new HashMap<String, Integer>();

      for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        RowDataAccessor dataAccessor = new RowDataAccessor();

        for (Row row : sheet) {
          if (row.getRowNum() == sheet.getFirstRowNum()) {
            for (Cell cell : row) {
              headers.put(cell.getStringCellValue(), cell.getColumnIndex());
            }

            dataAccessor.setHeaders(headers);
          } else {
            dataAccessor.setDataRow(row);

            Entity entity = Parser.EntityFiller.fillEntity(dataAccessor, dbHelper);

            if (entity != null) {
              retVal.add(entity);
            }
          }
        }
      }
    } catch (Exception ex) {
      throw ex;
    } finally {
      fileIn.close();
    }

    return retVal;
  }
Exemplo n.º 7
0
  public static List<List<List<String>>> readExcel(File file, Rule rule) {
    int start = rule.getStart();
    int end = rule.getEnd();
    List<List<List<String>>> result = Lists.newArrayList();
    Workbook wb;
    try {
      wb = WorkbookFactory.create(file);
    } catch (Exception e) {
      throw new ExcelException(e);
    }
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
      Sheet sheet = wb.getSheetAt(i);
      List<List<String>> sheetList = Lists.newArrayList();
      int rows = sheet.getLastRowNum();
      if (start <= sheet.getFirstRowNum()) {
        start = sheet.getFirstRowNum();
      }
      if (end >= rows) {
        end = rows;
      } else if (end <= 0) {
        end = rows + end;
      }
      for (int rowIndex = start; rowIndex <= end; rowIndex++) {
        Row row = sheet.getRow(rowIndex);
        List<String> columns = Lists.newArrayList();
        int cellNum = row.getLastCellNum();
        System.out.println(row.getLastCellNum());
        System.out.println(row.getPhysicalNumberOfCells());
        for (int cellIndex = row.getFirstCellNum(); cellIndex < cellNum; cellIndex++) {
          Cell cell = row.getCell(cellIndex);
          int cellType = cell.getCellType();
          String column = "";
          switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC:
              //                            DecimalFormat format = new DecimalFormat();
              //                            format.setGroupingUsed(false);
              column = String.valueOf(cell.getDateCellValue());
              break;
            case Cell.CELL_TYPE_STRING:
              column = cell.getStringCellValue();
              break;
            case Cell.CELL_TYPE_BOOLEAN:
              column = cell.getBooleanCellValue() + "";
              break;
            case Cell.CELL_TYPE_FORMULA:
              column = cell.getCellFormula();
              break;
            case Cell.CELL_TYPE_ERROR:

            case Cell.CELL_TYPE_BLANK:
              column = " ";
              break;
            default:
          }
          columns.add(column.trim());
        }

        List<Boolean> rowFilterFlagList = Lists.newArrayList();
        List<RowFilter> rowFilterList = Lists.newArrayList();
        for (int k = 0; k < rowFilterList.size(); k++) {
          RowFilter rowFilter = rowFilterList.get(k);
          rowFilterFlagList.add(rowFilter.doFilter(rowIndex, columns));
        }
        if (!rowFilterFlagList.contains(false)) {
          sheetList.add(columns);
        }
      }
      result.add(sheetList);
    }
    return result;
  }
Exemplo n.º 8
0
  public void read(Sheet sheet) {

    final int LAST_ROW_COUNT = 5000;
    final int POSTCODE_PREFIX_COLUMN_INDEX = 5;
    final int COUNTY_COLUMN = 2;
    int count = 0;
    int ind = 0;
    String value = null;
    int rowStart = Math.min(1, sheet.getFirstRowNum());
    int rowEnd = Math.max(LAST_ROW_COUNT, sheet.getLastRowNum());
    String county = null;
    for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
      Row r = sheet.getRow(rowNum);
      if (r == null) {
        continue;
      }
      int lastColumn = r.getLastCellNum();
      /* check if there is a county in this row*/
      //     	CellReference cellReference = new CellReference("C"+rowNum);
      Cell countyCell = r.getCell(COUNTY_COLUMN, Row.RETURN_BLANK_AS_NULL);
      if (countyCell != null && countyCell.getRichStringCellValue().length() > 0) {
        county = countyCell.getStringCellValue();
      }
      ind = 0;
      for (int cn = 0; cn < lastColumn; cn++) {
        Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);

        if (c == null) {
          // The spreadsheet is empty in this cell
          value = null;
          ind++;
        } else {
          // Do something useful with the cell's contents
          switch (c.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
              value = "" + c.getNumericCellValue();
              ind++;
              break;
            case Cell.CELL_TYPE_STRING:
              value = c.getStringCellValue().trim();
              ind++;
              break;
          }
        }

        if (cn > POSTCODE_PREFIX_COLUMN_INDEX && value != null && value.length() > 0) {
          if (county != null) {
            char dc = value.charAt(0);
            if (!(dc >= 'A' && dc <= 'Z')) {
              // 						System.out.print(value+" not starts with letter");
              value = value.substring(1);
              // 						System.out.println(" "+value);
            }
            dc = value.charAt(value.length() - 1);
            if (!(dc >= '0' && dc <= '9' || dc >= 'A' && dc <= 'Z')) {
              value = value.substring(0, value.length() - 1);
            }
            mapping.put(value.trim(), county);
          }
        }
      }
    }
  }