Пример #1
0
  @Override
  public List<String> getHeaderColumns() {
    if (headerColumns == null) {
      headerColumns = new ArrayList<>();
      Row row = dataSheet.getRow(0);

      int colCount = row != null ? row.getLastCellNum() : 0;

      if (row == null || colCount == 0) {
        LogMgr.logError(
            "ExcelReader.getHeaderColumns()",
            "Cannot retrieve column names because no data is available in the first row of the sheet: "
                + dataSheet.getSheetName(),
            null);
        String msg = ResourceMgr.getFormattedString("ErrExportNoCols", dataSheet.getSheetName());
        messages.append(msg);
        messages.appendNewLine();
        return headerColumns;
      }

      for (int i = 0; i < colCount; i++) {
        Cell cell = row.getCell(i);
        Object value = getCellValue(cell);

        if (value != null) {
          headerColumns.add(value.toString());
        } else {
          headerColumns.add("Col" + Integer.toString(i));
        }
      }
    }
    return headerColumns;
  }
Пример #2
0
  public void make(File source, DataSet header, DataSet master) throws Exception {
    this.header = header;
    this.master = master;

    if (header == null || master == null) throw new Exception("Dataset is empty");

    long t = System.currentTimeMillis();

    InputStream inp = new FileInputStream(source);
    Workbook oldBook = WorkbookFactory.create(inp);
    Sheet oldSheet = oldBook.getSheetAt(0);

    Workbook newBook = new HSSFWorkbook();
    Sheet newSheet = newBook.createSheet(oldSheet.getSheetName());

    init(newBook);
    process(oldSheet, newSheet);

    File target = File.createTempFile("libra", ".xls");
    target.deleteOnExit();
    FileOutputStream fileOut = new FileOutputStream(target);

    newBook.write(fileOut);
    fileOut.close();
    oldBook.close();
    inp.close();

    Desktop.getDesktop().open(target);

    System.out.println(System.currentTimeMillis() - t);
  }
Пример #3
0
  private boolean convertToXML() throws InvalidFormatException {
    Sheet sheet = null;

    System.out.println("Starter konvertering til SDC XML");
    int numSheets = this.workbook.getNumberOfSheets();
    boolean foundAtleastOneValid = false;

    for (int i = 0; i < numSheets; i++) {
      sheet = this.workbook.getSheetAt(i);
      String name = sheet.getSheetName();
      if (name.contains("innland")) {
        System.out.println("Fant innland-regneark, prosesserer");
        foundAtleastOneValid = true;
        this.processInnland(sheet);
      } else if (name.contains("utland")) {
        System.out.println("Fant utland-regneark, vet ikke hvordan dette prosesseres");
      } else {
        System.out.println("Fant ukjent regneark: " + name + ", ignorerer");
      }
    }
    if (!foundAtleastOneValid) {
      throw new InvalidFormatException(
          "Fant ikke innland-regneark, ingen output laget. (utland-konvertering støttes ikke atm.)");
    }
    return true;
  }
 /**
  * 出力対象のシートか判定する
  *
  * @param sheet テンプレートシート
  * @param reportBook 帳票ワークブック情報
  * @return
  */
 private boolean isOutputSheet(Sheet sheet, ReportBook reportBook) {
   for (ReportSheet reportSheet : reportBook.getReportSheets()) {
     if (reportSheet != null && sheet.getSheetName().equals(reportSheet.getSheetName())) {
       return true;
     }
   }
   return false;
 }
Пример #5
0
  //  traversal cell
  public void traversalCell(String filePath) {
    try {
      Workbook workBook = null;
      try {
        workBook = new XSSFWorkbook(filePath); // 支持2007
      } catch (Exception ex) {
        workBook = new HSSFWorkbook(new FileInputStream(filePath)); // 支持2003及以前
      }

      // 获得Excel中工作表个数
      System.out.println("工作表个数 :" + workBook.getNumberOfSheets());

      // 循环每个工作表
      for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
        // 创建工作表
        Sheet sheet = workBook.getSheetAt(i);

        int rows = sheet.getPhysicalNumberOfRows(); // 获得行数

        System.out.println(
            "工作表" + sheet.getSheetName() + " 行数 :" + sheet.getPhysicalNumberOfRows());

        if (rows > 0) {
          sheet.getMargin(Sheet.TopMargin);
          for (int r = 0; r < rows; r++) { // 行循环
            Row row = sheet.getRow(r);
            if (row != null) {

              int cells = row.getLastCellNum(); // 获得列数
              for (short c = 0; c < cells; c++) { // 列循环
                Cell cell = row.getCell(c);

                if (cell != null) {
                  String value = getCellData(cell);
                  System.out.println("第" + r + "行 " + "第" + c + "列:" + value);
                }
              }
            }
          }
        }

        // 查询合并的单元格
        for (i = 0; i < sheet.getNumMergedRegions(); i++) {
          System.out.println("第" + i + "个合并单元格");
          CellRangeAddress region = sheet.getMergedRegion(i);
          int row = region.getLastRow() - region.getFirstRow() + 1;
          int col = region.getLastColumn() - region.getFirstColumn() + 1;
          System.out.println("起始行:" + region.getFirstRow());
          System.out.println("起始列:" + region.getFirstColumn());
          System.out.println("所占行:" + row);
          System.out.println("所占列:" + col);
        }
      }

    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }
Пример #6
0
  private void writeDataToCell(Sheet sheet, int rowNum, int columnNum, Object value) {
    LOG.debug(
        "writeDataToCell started:"
            + sheet.getSheetName()
            + ",rowNum:"
            + rowNum
            + ",columnNum:"
            + columnNum
            + ",value:"
            + value);
    Row row = sheet.getRow(rowNum);
    if (row == null) {
      row = sheet.createRow(rowNum);
    }
    Cell cell = row.getCell(columnNum);
    if (cell == null) {
      int lastColumn = row.getLastCellNum();
      if (lastColumn < 0) lastColumn = 0;
      // if there are any blank cells, then last cell number will return value higher than actual
      // column number.
      // so set that value back to your required column number.
      if (lastColumn > columnNum) {
        lastColumn = columnNum;
      }
      for (int i = lastColumn; i <= columnNum; i++) {
        cell = row.createCell(i);
      }
    }

    if (value instanceof String) {
      cell.setCellType(Cell.CELL_TYPE_STRING);
      cell.setCellValue((String) trimActualResult(value.toString()));
    } else if (value instanceof Double) {
      cell.setCellType(Cell.CELL_TYPE_NUMERIC);
      cell.setCellValue((Double) value);
    } else if (value instanceof Integer) {
      cell.setCellType(Cell.CELL_TYPE_NUMERIC);
      cell.setCellValue((Integer) value);
    } else if (value instanceof Long) {
      cell.setCellType(Cell.CELL_TYPE_NUMERIC);
      cell.setCellValue((Long) value);
    } else if (value instanceof Float) {
      cell.setCellType(Cell.CELL_TYPE_NUMERIC);
      cell.setCellValue((Float) value);
    } else if (value != null) {
      cell.setCellType(Cell.CELL_TYPE_STRING);
      cell.setCellValue((String) trimActualResult(value.toString()));
    }
  }
Пример #7
0
  @Override
  public List<Object> getRowValues(int rowIndex) {
    Row row = dataSheet.getRow(rowIndex);
    ArrayList<Object> values = new ArrayList<>();

    if (row == null) return values;

    int nullCount = 0;
    int colCount = row.getLastCellNum();

    for (int col = 0; col < colCount; col++) {
      Cell cell = row.getCell(col);

      // treat rows with merged cells as "empty"
      if (isMerged(cell)) {
        LogMgr.logDebug(
            "ExcelReader.getRowValues()",
            dataSheet.getSheetName()
                + ": column:"
                + cell.getColumnIndex()
                + ", row:"
                + cell.getRowIndex()
                + " is merged. Ignoring row!");
        return Collections.emptyList();
      }

      Object value = getCellValue(cell);

      if (value == null) {
        nullCount++;
      }
      values.add(value);
    }

    if (nullCount == values.size()) {
      // return an empty list if all columns are null
      values.clear();
    }

    return values;
  }
Пример #8
0
  // sheet DN
  private void MapTableOrganization(Sheet sheet) throws IOException {
    System.out.println("Table : " + sheet.getSheetName());
    String tableName = sheet.getSheetName();
    List<String> list = new ArrayList<String>();
    if (tableName.equals("DN")) {
      Iterator<Row> rowIterator = sheet.iterator();
      while (rowIterator.hasNext()) {
        // Get the row object
        account = new Account();
        account.setLoginId(new Date().getTime() + "");
        account.setPassword("0000");
        account.setType(Type.ORGANIZATION);
        account.setLastLoginTime(new Date());
        organizationBasic = new OrganizationBasic();

        contact = new Contact();

        customer = new Customer();
        customer.setOrganizationLoginId(ManagerAuthenticate.getInstance().getOrganizationLoginId());

        Row row = rowIterator.next(); //
        // Every row has columns, get the column iterator and iterate over
        // them
        Iterator<Cell> cellIterator = row.cellIterator();
        if (row.getRowNum() > 0) {
          while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            if (cell.getRow().getRowNum() == 0) {
              System.out.println("---- Column name:" + cell.getStringCellValue());
            } else {
              if (cell.getColumnIndex() == 0) {
                // enterpriseName

                try {
                  System.out.println("STT " + cell.getRowIndex());
                  System.out.println("---- Enterprise Name: " + cell.getStringCellValue());
                  if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
                    customer.setName(cell.getStringCellValue());
                    organizationBasic.setName(cell.getStringCellValue());
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              if (cell.getColumnIndex() == 1) {
                // enterpriseCode

                try {

                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.println("---- Enterprise code: " + cell.getNumericCellValue());
                    if (list.indexOf(String.valueOf(cell.getNumericCellValue())) > 0) {
                      customer.setCode(String.valueOf(new Date().getTime()));
                    } else {
                      customer.setCode(String.valueOf(cell.getNumericCellValue()));
                      list.add(customer.getCode());
                    }
                  } else {
                    System.out.println("---- Enterprise code: " + cell.getStringCellValue());
                    if (list.indexOf(cell.getStringCellValue()) > 0) {
                      customer.setCode(String.valueOf(new Date().getTime()));
                    } else {
                      customer.setCode(cell.getStringCellValue());
                      list.add(customer.getCode());
                    }
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // Slogan
              if (cell.getColumnIndex() == 2) {
                try {
                  System.out.println("---- Slogan: " + cell.getStringCellValue());
                  organizationBasic.setSlogan(cell.getStringCellValue());

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // Address
              if (cell.getColumnIndex() == 3) {
                try {
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.println("---- Address: " + cell.getNumericCellValue());
                    contact.setAddressNumber(String.valueOf(cell.getNumericCellValue()));
                  } else {
                    System.out.println("---- Address: " + cell.getStringCellValue());
                    contact.setAddressNumber(cell.getStringCellValue());
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // Telephone
              if (cell.getColumnIndex() == 4) {
                try {
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.println("---- Telephone: " + cell.getNumericCellValue());

                    String[] arrayPhone = String.valueOf(cell.getNumericCellValue()).split(",");
                    contact.setPhone(arrayPhone);
                    if (arrayPhone.length > 0) {
                      customer.setMobile(arrayPhone[0]);
                    }

                  } else {
                    System.out.println("---- Telephone: " + cell.getStringCellValue());
                    String[] arrayPhone = cell.getStringCellValue().split(",");
                    contact.setPhone(arrayPhone);
                    if (arrayPhone.length > 0) {
                      customer.setMobile(arrayPhone[0]);
                    }
                  }
                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // Email
              if (cell.getColumnIndex() == 6) {
                try {
                  String str = new Date().getTime() + "";
                  int str1 = cell.getStringCellValue().length();
                  String str2 = cell.getStringCellValue();

                  System.out.println("---- Email: " + cell.getStringCellValue());
                  if (str1 > 4) {
                    account.setEmail(str2);
                  } else {
                    account.setEmail(str + "@gmail.com");
                  }
                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // Website
              if (cell.getColumnIndex() == 7) {
                try {
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    customer.setDescription(String.valueOf(cell.getNumericCellValue()));
                  } else {
                    if (cell.getStringCellValue() != null) {
                      customer.setDescription(cell.getStringCellValue());
                    }
                  }
                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // Description
              if (cell.getColumnIndex() == 8) {
                try {
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.println("---- Description: " + cell.getNumericCellValue());
                    organizationBasic.setDescription(String.valueOf(cell.getNumericCellValue()));
                  } else {
                    System.out.println("---- Description: " + cell.getStringCellValue());
                    if (cell.getStringCellValue() != null) {
                      if (cell.getStringCellValue().length() > 100) {
                        organizationBasic.setDescription(
                            cell.getStringCellValue().substring(0, 100));
                      } else {
                        organizationBasic.setDescription(cell.getStringCellValue());
                      }
                    }
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }
            }
          }
          try {
            if (checkAccount(account)) {
              Profile profileOrgBasic = new Profile();
              OrganizationBasic orgBasic = this.organizationBasic;
              profileOrgBasic.put(OrganizationBasic.NAME, orgBasic.getName());
              profileOrgBasic.put(OrganizationBasic.SLOGAN, orgBasic.getSlogan());
              profileOrgBasic.put(OrganizationBasic.DESCRIPTION, orgBasic.getDescription());

              Profiles profiles = new Profiles();
              profiles.setBasic(profileOrgBasic);
              account.setProfiles(profiles);

              List<Contact> contacts = new ArrayList<Contact>();
              contacts.add(contact);
              account.setContacts(contacts);

              Account acc = AccountModelManager.getInstance().saveAccount(account);
              customer.setLoginId(acc.getLoginId());
              customer.setType("Doanh nghiệp");
              customer.setAddress(contact.getAddressNumber());
              CustomerModelManager.getInstance().saveCustomer(customer);
            }
          } catch (Exception e) {

          }
        }
      }
    }
  }
  @Override
  public RecordSet getRecords() throws MalformedSourceException {
    try {
      openReader();
    } catch (IOException e) {
      logger_.info("Problem loading file: " + filename + " (" + e.getMessage() + ")");
      throw new MalformedSourceException(
          "Problem loading file: " + filename + " (" + e.getMessage() + ")");
    } catch (InvalidFormatException e) {
      logger_.info("Problem loading file: " + filename + " (" + e.getMessage() + ")");
      throw new MalformedSourceException(
          "Problem loading file: " + filename + " (" + e.getMessage() + ")");
    }

    RecordSet ret = new RecordSet();

    // Currently we need this flag in order for
    // TransformationEngine not to go into an infinite loop.
    if (!isRead_) {
      logger_.info("Opening file: " + filename);
      int nSheets = wb_.getNumberOfSheets();
      logger_.info("number of sheets: " + nSheets);
      for (int i = 0; i < nSheets; i++) {
        Sheet cSheet = wb_.getSheetAt(i);
        String cSheetName = cSheet.getSheetName();
        for (int j = skipLines_; j <= cSheet.getLastRowNum(); j++) {
          if (ignoreLinesAfter_ != 0 && j >= ignoreLinesAfter_) {
            break;
          }
          Row row = cSheet.getRow(j);
          MapRecord rec = new MapRecord();
          for (int k = 0; k < row.getLastCellNum(); k++) {
            if (!fieldMap_.keySet().contains(k)) {
              continue;
            }
            StringValue val;
            Cell cCell = row.getCell(k);
            if (cCell == null) {
              continue;
            }
            int cellType = cCell.getCellType();
            switch (cellType) {
              case Cell.CELL_TYPE_STRING:
                val = new StringValue(cCell.getStringCellValue());
                break;
              case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cCell)) {
                  SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
                  val = new StringValue(sdf.format(cCell.getDateCellValue()));
                } else {
                  val = new StringValue(String.valueOf(cCell.getNumericCellValue()));
                }
                break;
              case Cell.CELL_TYPE_BLANK:
                val = new StringValue("");
                break;
              case Cell.CELL_TYPE_BOOLEAN:
                val = new StringValue(String.valueOf(cCell.getBooleanCellValue()));
                break;
              default:
                val = new StringValue("Unsupported cell type");
            }

            rec.addValue(fieldMap_.get(k), val);
          }
          // TODO remove the hardcoded value
          rec.addValue("ExcelSheetName", new StringValue(cSheetName));
          ret.addRecord(rec);
        }
      }
      isRead_ = true;
    }
    return ret;
  }
Пример #10
0
 /**
  * 将excel内的内容读取到xml文件中,并添加dtd验证
  *
  * @param xmlFile
  * @param sheetNum
  * @return 1代表成功,0失败,-1超过最大sheet,2跳过当前失败的xml
  */
 public int excelToXml(String xmlFile, int sheetNum) {
   if (sheetNum >= workBook.getNumberOfSheets()) return -1;
   else sheet = workBook.getSheetAt(sheetNum);
   xmlFile = xmlFile + ".xml";
   try {
     Document document = DocumentHelper.createDocument();
     // 使用sheet名称命名跟节点
     String rootName = sheet.getSheetName().replaceAll(" ", "");
     Element root = document.addElement(rootName);
     // 添加dtd文件说明
     DocumentType documentType = new DOMDocumentType();
     documentType.setElementName(rootName);
     List<ElementDecl> declList = new ArrayList<>();
     declList.add(new ElementDecl(rootName, "(row*)"));
     // 判断sheet是否为空,为空则不执行任何操作
     if (sheet.getRow(0) == null) return 1;
     // 遍历sheet第一行,获取元素名称
     row = sheet.getRow(0);
     String rowString = null;
     List<String> pcdataList = new ArrayList<>();
     for (int y = 0; y < row.getPhysicalNumberOfCells(); y++) {
       Object object = this.getCellValueObject(0, y);
       // 判断是否有合并单元格,有的话跳过
       if (object == null) return 2;
       // 去除表头字符串中的空格
       String objectStr = object.toString().replaceAll(" ", "");
       if (rowString != null) rowString += "|" + objectStr;
       else rowString = objectStr;
       pcdataList.add(objectStr);
     }
     // 设置行节点
     declList.add(new ElementDecl("row", "(" + rowString + ")*"));
     // 遍历list设置行的下级节点
     for (String tmp : pcdataList) {
       declList.add(new ElementDecl(tmp, "(#PCDATA)"));
     }
     documentType.setInternalDeclarations(declList);
     // 遍历读写excel数据到xml中
     for (int x = 1; x < sheet.getLastRowNum(); x++) {
       row = sheet.getRow(x);
       Element rowElement = root.addElement("row");
       for (int y = 0; y < row.getPhysicalNumberOfCells(); y++) {
         // cell = row.getCell(y);
         Object object = this.getCellValueObject(x, y);
         if (object != null) {
           // 将sheet第一行的行首元素当作元素名称
           String pcdataString = pcdataList.get(y);
           Element element = rowElement.addElement(pcdataString);
           // Element element = rowElement.addElement("name");
           element.setText(object.toString());
         }
       }
     }
     // 写入文件和dtd
     document.setDocType(documentType);
     this.docToXmlFile(document, xmlFile);
   } catch (Exception e) {
     e.printStackTrace();
   }
   return 1;
 }
Пример #11
0
  public void parse(Sheet sheet) {
    logger.debug("开始解析Sheet页:" + sheet.getSheetName());
    startSheet(sheet);

    int rowCount = sheet.getPhysicalNumberOfRows();
    logger.debug("行数:" + rowCount);

    // 遇到表头区域的时候记录开始列
    int headerStartCol = 0;

    for (Row r : sheet) {
      HSSFRow row = (HSSFRow) r;
      if (row == null) continue;

      this.rowIndex = row.getRowNum();
      if (rowIndex < startRow) {
        continue;
      }
      // sundl: row.getFirstCellNum()不是很稳定,第一列的空格有时候会是null,有时候又是空字符串,这样导致这个值不可信
      // 目前强制写成1, 后续可以考虑改成自己写找第一个非空列。
      int firstCellNum = row.getFirstCellNum();
      if (firstCellNum == -1) continue;
      else firstCellNum = 1;

      HSSFCell firstCell = row.getCell(firstCellNum);
      String firstcellString = getCellStringValue(firstCell, evaluator);

      if (!firstcellString.isEmpty()) {
        if (aresStart.contains(rowIndex)) {
          endArea();
          logger.debug("Area开始,行号: " + rowIndex);
          startArea(firstcellString);
        } else if (areaTags.indexOf(firstcellString) != -1) {
          endArea();
          logger.debug("Area开始,行号: " + rowIndex);
          startArea(firstcellString);
        }

        if (blockStart.keySet().contains(rowIndex)) {
          endBlock();
          startBlock(firstcellString, blockStart.get(rowIndex));
        } else if (blocks.containsKey(firstcellString)) {
          endBlock();
          startBlock(firstcellString, blocks.get(firstcellString));
          // Text区域第一行暂时不解析
          if (currentBlockType == BlockTypes.TEXT) {
            continue;
          }
        }
      }

      String[] strings = readRowStrings(row, 1, evaluator);
      switch (currentBlockType) {
        case BlockTypes.KEY_VALUE:
          parseKeyValue(row, 1);
          break;
        case BlockTypes.TABLE:
          if (!headerFound) {
            // header按理说不应该存在空字符串,开头应该不会,但结尾可能存在
            while (StringUtils.isEmpty(strings[strings.length - 1])) {
              strings = (String[]) ArrayUtils.remove(strings, strings.length - 1);
            }
            headerStartCol = firstCellNum;
            header(strings);
            headerFound = true;
          } else {
            strings = POIUtils.readRowStrings(row, headerStartCol, evaluator);
            row(strings);
          }
          break;
        case BlockTypes.TEXT:
          text.append(StringUtils.join(strings));
          text.append('\n');
          break;
      }
    }

    endBlock();
    endArea();
    endSheet();
  }
Пример #12
0
  /** 将 excel中的sheet解析成对应的实体 */
  @Override
  public <T> Collection<T> getFinanceReprotEntitysByConfig(
      Sheet sheet,
      Map<String, FinanceReportBean> financebean,
      FinanceReportParamBean financeParam,
      BaseFile bf,
      CustInfo ci)
      throws Exception {
    // TODO Auto-generated method stub
    String targetClass = financeParam.getTargetClass();
    Class<?> targetObject = Class.forName(targetClass);
    String sheaderCell = financeParam.getHeaderCell();
    String sdataCell = financeParam.getDataCell();
    String vheaderCell[] = sheaderCell.split(",");
    String vdataCell[] = sdataCell.split(",");
    String headerCell = "";
    String dataCell = "";
    int headerCellRowIndex = 0;
    int headerCellCellIndex = 0;
    int dataCellRowIndex = 0;
    int dataCellCellIndex = 0;
    int sheetRowNum = sheet.getLastRowNum();
    int datalength = 0;
    String cellvalue = "";
    String headerValue = "";
    String field = "";
    Boolean flag = true;
    Boolean empty = false;
    Map<String, JSONObject> FinanceEntiies = new HashMap<String, JSONObject>();
    List finance = new ArrayList();
    String year = "";
    try {
      for (int i = 0; i < vheaderCell.length; i++) {
        datalength = 0;
        headerCellRowIndex = 0;
        headerCellCellIndex = 0;
        dataCellRowIndex = 0;
        dataCellCellIndex = 0;
        headerCell = vheaderCell[i];
        dataCell = vdataCell[i];
        ExeclBean headerInfo = PoiExcelUtil.getExcelBean(headerCell);
        ExeclBean dataInfo = PoiExcelUtil.getExcelBean(dataCell);
        headerCellRowIndex = headerInfo.getRow_num() + 1;
        headerCellCellIndex = headerInfo.getCol_num();
        dataCellRowIndex = dataInfo.getRow_num();
        dataCellCellIndex = dataInfo.getCol_num();
        flag = true;

        if (headerCellRowIndex > sheetRowNum || dataCellRowIndex > sheetRowNum) {
          throw new BusinessException(
              "解析excel出错:配置的标题cell("
                  + headerCellRowIndex
                  + ")或数据cell("
                  + dataCellRowIndex
                  + ")大于实际 sheet("
                  + sheet.getSheetName()
                  + ")的row长度");
        } else {
          Row datarow = sheet.getRow(dataCellRowIndex);
          datalength = 0;
          // 获得数据列的长度
          for (int j = dataCellCellIndex; j < datarow.getLastCellNum(); j++) {
            Cell datacell = datarow.getCell(j);
            cellvalue = PoiExcelUtil.getCellValue(datacell);
            if (flag == true) {
              if (null != cellvalue && (!"".equals(cellvalue))) {
                datalength = datalength + 1;
              } else {
                flag = false;
              }
            }
          }
          List<JSONObject> entity = new ArrayList<JSONObject>();
          for (int j = 0; j < datalength; j++) {
            cellvalue = "";
            empty = true;
            JSONObject financeEntityJson = new JSONObject();
            datarow = sheet.getRow(dataCellRowIndex);
            Cell cell = datarow.getCell(dataCellCellIndex + j);
            year = PoiExcelUtil.getCellValue(datarow.getCell(dataCellCellIndex + j));
            // 隐藏列则不写入数据库
            if (!sheet.isColumnHidden(dataCellCellIndex + j)) {
              if (!"".equals(year.trim())) {
                for (int h = dataCellRowIndex; h < sheetRowNum; h++) {
                  datarow = sheet.getRow(h);
                  if (null != datarow) {
                    if (headerCellCellIndex < datarow.getLastCellNum()) {
                      if (null != datarow.getCell(headerCellCellIndex)) {
                        headerValue =
                            PoiExcelUtil.getCellValue(datarow.getCell(headerCellCellIndex));
                      }
                      if (dataCellCellIndex + j < datarow.getLastCellNum()) {
                        if (null != datarow.getCell(dataCellCellIndex + j)) {
                          cell = datarow.getCell(dataCellCellIndex + j);
                          cellvalue =
                              PoiExcelUtil.getCellValue(datarow.getCell(dataCellCellIndex + j));
                          if (null != cellvalue && (!"".equals(cellvalue))) {
                            field = "";
                            flag = true;
                            if (h == dataCellRowIndex) {
                              flag = false;
                            }
                            field =
                                this.getExcelTableField(
                                    headerValue,
                                    (cell.getRowIndex() + 1) + "_" + i,
                                    financebean,
                                    flag);
                            System.out.println(
                                headerValue
                                    + ":"
                                    + ((cell.getRowIndex() + 1) + "_" + i)
                                    + ":"
                                    + cellvalue);
                            if (!"".equals(field)) {
                              financeEntityJson.put(field, cellvalue);
                              empty = false;
                            }
                          }
                        }
                      }
                    }
                  }
                }
                if (empty == false) {
                  // 根据年进行合并
                  if (FinanceEntiies.containsKey(year)) {
                    JSONObject temp = FinanceEntiies.get(year);
                    Iterator it = financeEntityJson.keys();
                    while (it.hasNext()) {
                      String key = (String) it.next();
                      temp.put(key, financeEntityJson.get(key).toString());
                    }
                    FinanceEntiies.put(year, temp);
                  } else {
                    FinanceEntiies.put(year, financeEntityJson);
                  }
                }
              }
            }
          }
        }
      }
      System.out.println(FinanceEntiies);
      if (!FinanceEntiies.isEmpty()) {
        Set<String> key = FinanceEntiies.keySet();
        for (Iterator it = key.iterator(); it.hasNext(); ) {
          year = (String) it.next();
          JSONObject jsonObj = FinanceEntiies.get(year);
          Object sourectObject = targetObject.newInstance();
          this.tableService.copyAndOverrideExistedValueFromJSONObject(jsonObj, sourectObject, null);
          try {
            BeanUtils.getPropertyDescriptor(sourectObject.getClass(), "upLoadId")
                .getWriteMethod()
                .invoke(sourectObject, bf);
            BeanUtils.getPropertyDescriptor(sourectObject.getClass(), "custId")
                .getWriteMethod()
                .invoke(sourectObject, ci);
          } catch (Exception e) {
            // TODO Auto-generated catch block
            throw new BusinessException(
                "在记录上传文件的ID和客户时出请检查实体类中有没有upLoadId和custId字段" + e.getMessage());
          }
          finance.add(sourectObject);
        }

        return finance;
      }

    } catch (BusinessException b) {
      b.printStackTrace();
      throw new BusinessException(b.getMessage());
    } catch (Exception e) {
      e.printStackTrace();
      // TODO Auto-generated catch block
      throw new BusinessException(e.getMessage());
    }
    return null;
  }
Пример #13
0
  // sheet Project
  private void MapTableProject(Sheet sheet) {
    System.out.println("Table : " + sheet.getSheetName());
    String tableName = sheet.getSheetName();
    if (tableName.equals("Du an")) {
      Iterator<Row> rowIterator = sheet.iterator();
      while (rowIterator.hasNext()) {
        // Get the row object

        Project project = new Project();

        Row row = rowIterator.next(); //
        // Every row has columns, get the column iterator and iterate over
        // them
        Iterator<Cell> cellIterator = row.cellIterator();
        if (row.getRowNum() > 0) {
          while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getRow().getRowNum() == 0) {
              System.out.println("---- Column name:" + cell.getNumericCellValue());
            } else {
              if (cell.getColumnIndex() == 2) {

                try {

                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.println("STT " + cell.getRowIndex());
                    System.out.println("---- Parent Project: " + cell.getNumericCellValue());
                    if (String.valueOf(cell.getNumericCellValue()) != null) {
                      project.setParentCode(String.valueOf(cell.getNumericCellValue()));
                    }
                  } else {
                    System.out.println("STT " + cell.getRowIndex());
                    System.out.println("---- Parent Project: " + cell.getStringCellValue());
                    if (cell.getStringCellValue() != null) {
                      project.setParentCode(cell.getStringCellValue());
                    }
                  }
                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              if (cell.getColumnIndex() == 1) {
                System.out.println("---- Name Project: " + cell.getStringCellValue());
                project.setName(cell.getStringCellValue());
                if (project.getName().trim().length() == 0) {
                  continue;
                }
              }

              if (cell.getColumnIndex() == 0) {
                try {

                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

                    project.setCode(String.valueOf(cell.getNumericCellValue()));

                  } else {
                    project.setCode(cell.getStringCellValue());
                  }
                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              if (cell.getColumnIndex() == 3) {
                System.out.println("---- Description: " + cell.getStringCellValue());
                project.setDepartmentPart(cell.getStringCellValue());
              }

              if (cell.getColumnIndex() == 4) {
                System.out.println("---- Description: " + cell.getStringCellValue());
                project.setDescription(cell.getStringCellValue());
              }
            }
          }
          try {
            if (checkProject(project)) {
              project.setStatus("Đã hoàn thành");
              RestaurantModelManager.getInstance().saveProject(project);
            }
          } catch (Exception e) {
            e.printStackTrace();
          }
        }
      }
    }
  }
Пример #14
0
  public String process2xml() {
    String result = "";
    // String sourcefilenametag = config.getString("conversion.tags.sourcefilename");
    String sourcefilename = file.getAbsolutePath();
    try {
      sourcefilename = file.getCanonicalPath();
    } catch (IOException ioe) {
      log.warn("Could not get cannonical path for file!", ioe);
    }
    String templatedir = config.getString("conversion.template.path");

    // result += "<" + sourcefilenametag + "><![CDATA[" + file.toString() + "]]></" +
    // sourcefilenametag + ">";
    try {
      Workbook input = WorkbookFactory.create(file);

      log.debug("Processing " + file.toString() + " as an Excel file.");

      FormulaEvaluator evaluator = input.getCreationHelper().createFormulaEvaluator();

      String templateName = "";

      STGroup g = new STRawGroupDir(templatedir, '$', '$');

      // Go through each sheet
      for (int sheetno = 0; sheetno < input.getNumberOfSheets(); sheetno++) {

        Sheet sheet = input.getSheetAt(sheetno);

        log.debug("Processing sheet #" + sheetno + ": " + sheet.getSheetName());

        LandmarkMatchList lml = new LandmarkMatchList(landmarks.size());

        for (Row row : sheet) {

          // Go through each cell
          for (Cell cell : row) {
            String cellvalue = lml.getCellValue(cell, evaluator);

            if (!cellvalue.equals("")) {
              log.trace(
                  "Cell value is: "
                      + cellvalue
                      + " [Row,Col]=["
                      + cell.getRowIndex()
                      + ","
                      + cell.getColumnIndex()
                      + "]");
              log.trace("Matching landmarks: " + landmarks.getLandmarksFor(cellvalue));

              // Does Cell contents match a landmark?
              lml.addMatches(landmarks.getLandmarksFor(cellvalue), cell);
            } else {
              log.trace(
                  "Cell value is blank. [Row,Col]=["
                      + cell.getRowIndex()
                      + ","
                      + cell.getColumnIndex()
                      + "]");
            }
          }
        }

        templateName = lml.getTemplateName(landmarks);

        if (!templateName.equals("")) {

          ST st = g.getInstanceOf(templateName);

          if (st != null) {

            // Set landmark name to value of cell given direction and distance

            Hashtable templateValues =
                lml.getCellTemplateValues(
                    templateName, sheet, landmarks, evaluator, sourcefilename, sheetno);
            Enumeration templateValuesKeys = templateValues.keys();

            while (templateValuesKeys.hasMoreElements()) {
              String key = (String) templateValuesKeys.nextElement();
              st.add(key, (String) templateValues.get(key));
            }

            ArrayList<String> sectionNames =
                lml.getSectionNamesForTemplate(templateName, landmarks);
            for (String sectionName : sectionNames) {

              ArrayList<Hashtable> sectionrows =
                  lml.getSectionRows(templateName, sheet, landmarks, evaluator, sectionName);

              st.add(sectionName, sectionrows);
            }

            result += st.render();
          } else {
            log.error(
                "Unable to load template "
                    + templateName
                    + ".st! Cannot render data to template while processing "
                    + file.toString()
                    + " sheet number "
                    + sheetno);
          }
        }
      }

    } catch (IOException ioe) {
      log.error("Unable to open " + file.toString() + " as an Excel file.", ioe);
    } catch (InvalidFormatException ife) {
      log.error("Unable to open " + file.toString() + ". Format not recognized as Excel. ", ife);
    } catch (IllegalArgumentException iae) {
      log.error("Unable to open " + file.toString() + " as an Excel file.", iae);
    } catch (Exception e) {
      log.error("Unable to open " + file.toString() + " as an Excel file.", e);
    }
    // If cannot render, make sure don't reprocess unnecessarily
    if (result.equals("")) {
      result = "<Notemplatedata></Notemplatedata>";
    }
    return result;
  }
Пример #15
0
  // sheet Invoice
  private void MapTableInvoice(Sheet sheet) {

    System.out.println("Table : " + sheet.getSheetName());
    String tableName = sheet.getSheetName();
    if (tableName.equals("invoice")) {
      try {
        AccountGroup acc1 = new AccountGroup();
        acc1.setName("Hành Chính");
        acc1.setLabel("Hành Chính");
        acc1.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId());
        acc1.setParent(HRModelManager.getInstance().getRootDepartment());
        AccountModelManager.getInstance().saveGroup(acc1);

        AccountGroup acc2 = new AccountGroup();
        acc2.setName("Hội Đồng Quản Trị");
        acc2.setLabel("Hội Đồng Quản Trị");
        acc2.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId());
        acc2.setParent(HRModelManager.getInstance().getRootDepartment());
        AccountModelManager.getInstance().saveGroup(acc2);

        AccountGroup acc3 = new AccountGroup();
        acc3.setName("Kinh Doanh");
        acc3.setLabel("Kinh Doanh");
        acc3.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId());
        acc3.setParent(HRModelManager.getInstance().getRootDepartment());
        AccountModelManager.getInstance().saveGroup(acc3);

        AccountGroup acc4 = new AccountGroup();
        acc4.setName("Ban Lãnh Đạo");
        acc4.setLabel("Ban Lãnh Đạo");
        acc4.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId());
        acc4.setParent(HRModelManager.getInstance().getRootDepartment());
        AccountModelManager.getInstance().saveGroup(acc4);

        AccountGroup acc5 = new AccountGroup();
        acc5.setName("Nghiên Cứu");
        acc5.setLabel("Nghiên Cứu");
        acc5.setOwner(ManagerAuthenticate.getInstance().getOrganizationLoginId());
        acc5.setParent(HRModelManager.getInstance().getRootDepartment());
        AccountModelManager.getInstance().saveGroup(acc5);

      } catch (Exception e1) {
        e1.printStackTrace();
      }
      Iterator<Row> rowIterator = sheet.iterator();
      while (rowIterator.hasNext()) {
        // Get the row object
        InvoiceDetail invoicedetail = new InvoiceDetail();
        invoicedetail.setType(AccountingModelManager.typeThuChi);
        invoicedetail.setStatus(Status.Paid);
        invoicedetail.setCurrencyUnit("VND");
        invoicedetail.setCurrencyRate(1);

        Row row = rowIterator.next(); //
        // Every row has columns, get the column iterator and iterate over
        // them
        Iterator<Cell> cellIterator = row.cellIterator();
        if (row.getRowNum() > 0) {
          while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            if (cell.getRow().getRowNum() == 0) {
              System.out.println("---- Column name:" + cell.getNumericCellValue());
            } else {
              if (cell.getColumnIndex() == 0) {
                // Tên phòng

                try {
                  // System.out.println("STT " + cell.getRowIndex());
                  // System.out.println("---- Accountgroup name: " +
                  // cell.getStringCellValue());
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    // AccountGroup accountGroup =
                    // AccountModelManager.getInstance().getGroupByName(String.valueOf(cell.getNumericCellValue()));
                    // System.out.println("---- aaaaaaaaaaaa:" +
                    // cell.getNumericCellValue()+"      "+accountGroup.getPath());
                    String path = "hkt/Phòng ban/" + String.valueOf(cell.getNumericCellValue());
                    invoicedetail.setDepartmentCode(path);

                  } else {
                    // AccountGroup accountGroup =
                    // AccountModelManager.getInstance().getGroupByName(cell.getStringCellValue());
                    // System.out.println("---- aaaaaaaaaaaa:" +
                    // cell.getStringCellValue()+"      "+accountGroup.getPath());
                    String path = "hkt/Phòng ban/" + cell.getStringCellValue();
                    invoicedetail.setDepartmentCode(path);
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              if (cell.getColumnIndex() == 1) {
                // Mã dự án

                try {
                  if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
                    // System.out.println("---- Project code: " +
                    // cell.getStringCellValue());
                    invoicedetail.setProjectCode("/" + cell.getStringCellValue());
                  }
                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // Operation Name
              if (cell.getColumnIndex() == 2) {
                try {
                  System.out.println("---- Operation Name: " + cell.getStringCellValue());
                  if (cell.getStringCellValue().length() > 100) {
                    invoicedetail.setInvoiceName(cell.getStringCellValue().substring(0, 100));
                  } else {
                    invoicedetail.setInvoiceName(cell.getStringCellValue());
                  }

                  if (invoicedetail.getInvoiceName().trim().length() == 0) {
                    continue;
                  }
                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // Operation code
              if (cell.getColumnIndex() == 3) {
                try {
                  String str = DateUtil.asCompactDateId(new Date()) + ":";
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    // System.out.println("---- Operation Code: " +
                    // cell.getNumericCellValue());
                    invoicedetail.setInvoiceCode(
                        "TC" + str + "TC" + String.valueOf(cell.getNumericCellValue()));

                  } else {
                    // System.out.println("---- Operation Code2: " +
                    // cell.getStringCellValue());
                    invoicedetail.setInvoiceCode("TC" + str + "TC" + cell.getStringCellValue());
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // description
              if (cell.getColumnIndex() == 4) {
                // System.out.println("---- Description: " +
                // cell.getStringCellValue());
                // invoicedetail.setDescription(String.valueOf(cell.getStringCellValue()));
              }

              // idOperationType
              if (cell.getColumnIndex() == 5) {
                try {
                  // System.out.println("---- ActivityType: " +
                  // cell.getStringCellValue());
                  if (cell.getStringCellValue().equals("5")) {
                    invoicedetail.setActivityType(ActivityType.Receipt);
                  } else if (cell.getStringCellValue().equals("6")) {
                    invoicedetail.setActivityType(ActivityType.Payment);
                  } else {
                    invoicedetail.setActivityType(ActivityType.Payment);
                    invoicedetail.setType(AccountingModelManager.typeSanXuat);
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // TotalBeforeDiscount
              if (cell.getColumnIndex() == 6) {
                try {
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    // System.out.println("---- Total: " +
                    // cell.getNumericCellValue());
                    invoicedetail.setTotal(cell.getNumericCellValue());

                  } else {
                    // System.out.println("---- Total: " +
                    // cell.getStringCellValue());
                    invoicedetail.setTotal(Double.parseDouble(cell.getStringCellValue()));
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // PercenDiscount
              if (cell.getColumnIndex() == 7) {
                try {
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    // System.out.println("---- Percent Discount: " +
                    // cell.getNumericCellValue());
                    invoicedetail.setDiscountRate(cell.getNumericCellValue());

                  } else {
                    // System.out.println("---- Percent Discount: " +
                    // cell.getStringCellValue());
                    invoicedetail.setDiscountRate(Double.parseDouble(cell.getStringCellValue()));
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // MoneyDiscount
              if (cell.getColumnIndex() == 8) {
                try {
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    // System.out.println("---- MoneyDiscount: " +
                    // cell.getNumericCellValue());
                    invoicedetail.setDiscount(cell.getNumericCellValue());

                  } else {
                    // System.out.println("---- MoneyDiscount: " +
                    // cell.getStringCellValue());
                    invoicedetail.setDiscount(Double.parseDouble(cell.getStringCellValue()));
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // MoneyTax
              if (cell.getColumnIndex() == 9) {
                try {
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    // System.out.println("---- Total Money After Tax " +
                    // cell.getNumericCellValue());
                    invoicedetail.setTotalTax(cell.getNumericCellValue());

                  } else {
                    // System.out.println("---- Total Money After Tax " +
                    // cell.getStringCellValue());
                    invoicedetail.setTotalTax(Double.parseDouble(cell.getStringCellValue()));
                  }
                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // TotalMoneyAfterDiscount
              if (cell.getColumnIndex() == 10) {}

              // TotalMoneyAfterTax
              if (cell.getColumnIndex() == 11) {
                try {
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    // System.out.println("---- Total Money After tax " +
                    // cell.getNumericCellValue());
                    invoicedetail.setFinalCharge(cell.getNumericCellValue());

                  } else {
                    // System.out.println("---- Total Money After tax " +
                    // cell.getStringCellValue());
                    invoicedetail.setFinalCharge(Double.parseDouble(cell.getStringCellValue()));
                  }
                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // DateExcute
              if (cell.getColumnIndex() == 12) {
                try {

                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    // System.out.println("---- Date Execute " +
                    // cell.getNumericCellValue());

                    Date startDate = df.parse(String.valueOf(cell.getNumericCellValue()));
                    // System.out.println(startDate);
                    invoicedetail.setStartDate(startDate);
                    invoicedetail.setEndDate(startDate);
                  } else {
                    Date startDate = df.parse(cell.getStringCellValue());
                    // System.out.println(startDate);
                    invoicedetail.setStartDate(startDate);
                    invoicedetail.setEndDate(startDate);
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }
            }
          }
          try {
            if (checkInvoiDetail(invoicedetail)) {
              // invoicedetail.calculate(new DefaultInvoiceCalculator());
              invoicedetail.setTotalPaid(invoicedetail.getFinalCharge());
              InvoiceTransaction transactions = new InvoiceTransaction();
              transactions.setTransactionType(TransactionType.CreditCard);
              transactions.setDepartmentCode(invoicedetail.getDepartmentCode());
              transactions.setLocationCode(invoicedetail.getLocationCode());
              transactions.setTableCode(invoicedetail.getTableCode());
              transactions.setCustomerCode(invoicedetail.getCustomerCode());
              transactions.setProjectCode(invoicedetail.getProjectCode());
              transactions.setCreatedBy(invoicedetail.getDepartmentCode());
              transactions.setCurrencyRate(1);
              transactions.setCurrencyUnit(invoicedetail.getCurrencyUnit());
              transactions.setTotal(invoicedetail.getFinalCharge());
              transactions.setTransactionDate(invoicedetail.getStartDate());
              if (invoicedetail.getActivityType().equals(ActivityType.Receipt)) {
                transactions.setActivityType(InvoiceTransaction.ActivityType.Receipt);
              } else if (invoicedetail.getActivityType().equals(ActivityType.Payment)) {
                transactions.setActivityType(InvoiceTransaction.ActivityType.Payment);
              } else {
                transactions.setActivityType(null);
              }

              invoicedetail.add(transactions);
              invoicedetail.setLocationCode("other");
              invoicedetail.setTableCode("other");
              invoicedetail.setCustomerCode("hkt/Khách hàng/groupCustomer-other");
              // if
              // (invoicedetail.getType().equals(AccountingModelManager.typeSanXuat))
              // {
              AccountingModelManager.getInstance().saveInvoice(invoicedetail);
              // }

            }
          } catch (Exception e) {
            e.printStackTrace();
          }
        }
      }
    }
  }
  private void rebuildFormula(Sheet sheet) {
    ProductType type = ProductType.get(sheet.getSheetName());
    SizeInfo sizeInfo = SizeInfo.getByType(type);

    // size -> list<columnIndex>
    Map<String, List<Integer>> columns = new HashMap<String, List<Integer>>();

    for (int colIndex = 6 + sizeInfo.getSizeNumber() + 1;
        colIndex < sheet.getRow(2).getLastCellNum();
        colIndex++) {
      Row row = sheet.getRow(2);
      String value = row.getCell(colIndex).getStringCellValue().trim();
      if ("数量".equals(value) || StringUtils.isBlank(value)) {
        continue;
      }

      if (!columns.containsKey(value)) {
        columns.put(value, new ArrayList<Integer>());
      }

      columns.get(value).add(colIndex);
    }

    //        for(int rowIndex = 3; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
    //            Row row = sheet.getRow(rowIndex);
    //
    //            for(int colIndex = 7; colIndex <= 7 + sizeRange - 1; colIndex++) {
    //                String size = sheet.getRow(2).getCell(colIndex).getStringCellValue().trim();
    //
    //                List<Integer> cols = columns.get(size);
    //                row.getCell(colIndex).setCellType(Cell.CELL_TYPE_FORMULA);
    //                row.getCell(colIndex).setCellFormula(this.buildSumFormula(rowIndex, cols));
    //            }
    //        }

    for (int rowIndex = 4; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
      Row row = sheet.getRow(rowIndex);
      if (row == null) {
        continue;
      }

      for (int colIndex = 7; colIndex <= row.getLastCellNum(); colIndex++) {
        if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(colIndex)))) {
          Cell cell = row.getCell(colIndex);

          cell.setCellType(Cell.CELL_TYPE_FORMULA);
          String formula =
              String.format(
                  "SUM(%s%s:%s%s)",
                  CellUtils.convertToABC(colIndex - sizeInfo.getSizeNumber() + 1),
                  rowIndex + 1,
                  CellUtils.convertToABC(colIndex - 1 + 1),
                  rowIndex + 1);
          cell.setCellFormula(formula);
        }
      }
    }

    for (int rowIndex = 4; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
      Row row = sheet.getRow(rowIndex);
      if ("合计".equals(CellUtils.getStringValue(row.getCell(0)))) {
        for (int colIndex = 7; colIndex <= sheet.getLastRowNum(); colIndex++) {
          Cell cell = row.getCell(colIndex);
          if (cell == null) {
            continue;
          }
          cell.setCellType(Cell.CELL_TYPE_FORMULA);
          String formula =
              String.format(
                  "SUM(%s%s:%s%s)",
                  CellUtils.convertToABC(colIndex + 1),
                  5,
                  CellUtils.convertToABC(colIndex + 1),
                  rowIndex);
          cell.setCellFormula(formula);
        }
      }
    }
  }
 /**
  * Discription:[读取xls或者xlsx文档的所有页,并且以map格式输出:sheet名:sheet内容。
  * 其中sheet内容是以列表存储,列表中存储的是String数组,sheet中的每一行则是一个String数组]
  *
  * @param wb 工作表格,文档
  * @return
  * @author:[代超]
  * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
  */
 public Map<String, Object> readExcelFile(Workbook wb) {
   if (wb == null) {
     return null;
   }
   DecimalFormat df = new DecimalFormat("#.##");
   int sheetNumber = wb.getNumberOfSheets();
   List list = new ArrayList();
   Map<String, Object> excelMap = new HashMap<String, Object>();
   for (int i = 0; i < sheetNumber; i++) {
     Sheet sheet = wb.getSheetAt(i);
     List<String[]> strs = new ArrayList<String[]>();
     // 注意得到的行数是基于0的索引 遍历所有的行
     for (int k = 0; k <= sheet.getLastRowNum(); k++) {
       Row rows = sheet.getRow(k);
       if (rows == null) {
         continue;
       }
       String[] str = new String[rows.getLastCellNum()];
       // 遍历每一列
       for (int l = 0; l < rows.getLastCellNum(); l++) {
         Cell cell = rows.getCell(l);
         // 单元格类型
         if (cell == null) {
           continue;
         }
         int cellType = cell.getCellType();
         switch (cellType) {
           case 0: // 数字类型
             str[l] = df.format(cell.getNumericCellValue());
             break;
           case 1: // String类型
             str[l] = cell.getStringCellValue();
             break;
           case 2: // Formula Cell type 公式类型
             FormulaEvaluator he = null;
             try {
               he = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
             } catch (Exception e) {
               he = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
             }
             if (he != null && he.evaluateFormulaCell(cell) == 0) {
               str[l] = df.format(he.evaluate(cell).getNumberValue());
             } else {
               str[l] = he.evaluate(cell).getStringValue();
             }
             break;
           case 3: // 空格
             break;
           case 4: // Boolean Cell type
             str[l] = BooleanUtils.toStringTrueFalse(cell.getBooleanCellValue());
             break;
           case 5: // Errors
             break;
           default: // 其它格式的数据
             break;
         }
       }
       strs.add(str);
     }
     // list.add(strs);
     excelMap.put(sheet.getSheetName(), strs);
   }
   return excelMap;
 }
Пример #18
0
  /**
   * Read Excel format file.
   *
   * @param filename
   */
  private void readExcel(String filename) {
    try {
      FileInputStream fin = new FileInputStream(filename);
      if (flgDoubleQuotes) {
        delimiter = "\"" + delimiter + "\"";
      }
      try {
        Workbook wb = WorkbookFactory.create(fin);

        // HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
          Sheet sheet = wb.getSheetAt(i);
          String sheetname = sheet.getSheetName();

          String fname = sheetname;
          if (delimiter.equals("\t")) {
            fname = fname + ".tsv";
          } else {
            fname = fname + ".csv";
          }
          File file = new File(fname);
          FileOutputStream fout = new FileOutputStream(file);
          OutputStreamWriter ow = new OutputStreamWriter(fout, charset);
          BufferedWriter bw = new BufferedWriter(ow);

          for (Iterator<Row> rowIter = sheet.rowIterator(); rowIter.hasNext(); ) {
            Row row = rowIter.next();

            String tmp = "";
            if (flgDoubleQuotes) {
              tmp = "\"";
            }
            if (row != null) {
              for (int k = 0; k < row.getLastCellNum(); k++) {
                Cell cell = row.getCell(k);
                // CellValue celv = evaluator.evaluate(cell);
                if (cell == null) {
                  tmp = tmp + delimiter;
                  continue;
                }
                switch (cell.getCellType()) {
                  case Cell.CELL_TYPE_BLANK:
                    tmp = tmp + " " + delimiter;
                    break;
                  case Cell.CELL_TYPE_NUMERIC:
                    tmp = tmp + getNumericValue(cell) + delimiter;
                    break;
                  case Cell.CELL_TYPE_STRING:
                    tmp = tmp + getStringValue(cell) + delimiter;
                    break;
                  case Cell.CELL_TYPE_FORMULA:
                    try {
                      FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

                      CellValue value = evaluator.evaluate(cell);

                      if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        tmp = tmp + getNumericValue(cell) + delimiter;
                      } else if (value.getCellType() == Cell.CELL_TYPE_STRING) {
                        tmp = tmp + getStringValue(cell) + delimiter;
                      }
                    } catch (FormulaParseException e) {
                      // error
                      tmp = tmp + " " + delimiter;
                      System.err.println(e.getLocalizedMessage());
                    } catch (NotImplementedException e) {
                      // error
                      tmp = tmp + " " + delimiter;
                      System.err.println(e.getLocalizedMessage());
                    }
                    break;
                  default:
                    tmp = tmp + " " + delimiter;
                }
              }
              tmp = tmp.substring(0, tmp.length() - 1);
            }
            bw.write(tmp + "\n");
          }
          bw.flush();
          bw.close();
          ow.close();
          fout.close();
          System.gc();
        }
      } catch (InvalidFormatException e) {
        e.printStackTrace();
      } catch (IOException e) {
        e.printStackTrace();
      }

    } catch (FileNotFoundException e) {
      e.printStackTrace();
    }
  }
Пример #19
0
 @Override
 public String toString() {
   return getClass().getSimpleName() + ": " + sheet.getSheetName();
 }
Пример #20
0
  // sheet Ca nhan
  private void MapTableUser(Sheet sheet) throws IOException {
    System.out.println("Table : " + sheet.getSheetName());
    String tableName = sheet.getSheetName();
    if (tableName.equals("Ca nhan")) {
      Iterator<Row> rowIterator = sheet.iterator();
      while (rowIterator.hasNext()) {
        // Get the row object
        account = new Account();
        account.setLoginId(new Date().getTime() + "");
        account.setPassword("0000");
        account.setType(Type.USER);
        account.setLastLoginTime(new Date());
        basicInformation = new BasicInformation();

        contact = new Contact();

        customer = new Customer();
        customer.setOrganizationLoginId(ManagerAuthenticate.getInstance().getOrganizationLoginId());
        String str = new Date().getTime() + "";
        customer.setCode(str);

        Row row = rowIterator.next(); //
        // Every row has columns, get the column iterator and iterate over
        // them
        Iterator<Cell> cellIterator = row.cellIterator();
        if (row.getRowNum() > 0) {
          while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getRow().getRowNum() == 0) {
              System.out.println("---- Column name:" + cell.getStringCellValue());
            } else {
              if (cell.getColumnIndex() == 0) {
                // firstName

                try {
                  System.out.println("STT " + cell.getRowIndex());
                  System.out.println("---- firstName: " + cell.getStringCellValue());
                  if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
                    basicInformation.setFirstName(cell.getStringCellValue());
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              if (cell.getColumnIndex() == 1) {
                // lastName
                try {
                  System.out.println("---- lastName: " + cell.getStringCellValue());
                  if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {

                    basicInformation.setLastName(
                        basicInformation.getFirstName() + " " + cell.getStringCellValue());
                    customer.setName(basicInformation.getLastName());
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              if (cell.getColumnIndex() == 2) {
                // birthday
                try {

                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.println("---- birthday " + cell.getNumericCellValue());
                    basicInformation.setBirthday(String.valueOf(cell.getNumericCellValue()));
                  } else {
                    System.out.println("---- birthday " + cell.getStringCellValue());
                    basicInformation.setBirthday(cell.getStringCellValue().toString());
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              if (cell.getColumnIndex() == 3) {
                // gender
                try {
                  System.out.println("---- gender: " + cell.getStringCellValue());
                  if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
                    basicInformation.setGender(cell.getStringCellValue());
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              if (cell.getColumnIndex() == 4) {
                // indentityCard

                try {
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.println("---- indentityCard: " + cell.getNumericCellValue());
                    basicInformation.setPersonalId(String.valueOf(cell.getNumericCellValue()));
                  } else {
                    System.out.println("---- indentityCard: " + cell.getStringCellValue());
                    basicInformation.setPersonalId((cell.getStringCellValue()));
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // address
              if (cell.getColumnIndex() == 5) {
                System.out.println("---- address: " + cell.getStringCellValue());
                contact.setAddressNumber(cell.getStringCellValue());
              }

              // telephone
              if (cell.getColumnIndex() == 6) {

                try {
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.println("---- telephone1: " + cell.getNumericCellValue());
                    String[] phone = String.valueOf(cell.getNumericCellValue()).split(",");
                    contact.setPhone(phone);
                  } else {
                    System.out.println("---- telephone2: " + cell.getStringCellValue());
                    String[] phone = cell.getStringCellValue().split(",");
                    contact.setPhone(phone);
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              if (cell.getColumnIndex() == 7) {
                // mobile
                try {
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.println("---- mobile1: " + cell.getNumericCellValue());
                    String[] arraymobile = String.valueOf(cell.getNumericCellValue()).split(",");
                    contact.setMobile(arraymobile);

                  } else {
                    System.out.println("---- mobile2: " + cell.getStringCellValue());
                    String[] arraymobile = cell.getStringCellValue().split(",");
                    contact.setMobile(arraymobile);
                  }
                } catch (Exception e) {

                }
              }

              // Email
              if (cell.getColumnIndex() == 8) {
                try {
                  String string = new Date().getTime() + "";
                  int str1 = cell.getStringCellValue().length();
                  String str2 = cell.getStringCellValue();

                  System.out.println("---- Email: " + cell.getStringCellValue());
                  if (str1 > 4) {
                    account.setEmail(str2);
                  } else {
                    account.setEmail(string + "@gmail.com");
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }

              // Description
              if (cell.getColumnIndex() == 9) {
                try {
                  if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.println("---- telephone1: " + cell.getNumericCellValue());
                    String phone = String.valueOf(cell.getNumericCellValue());
                    customer.setDescription(phone);
                  } else {
                    System.out.println("---- telephone2: " + cell.getStringCellValue());
                    customer.setDescription(cell.getStringCellValue());
                  }

                } catch (Exception e) {
                  e.printStackTrace();
                }
              }
            }
          }

          try {

            Profile profileBasic = new Profile();
            BasicInformation basic = this.basicInformation;
            profileBasic.put(BasicInformation.LAST_NAME, basic.getLastName());
            profileBasic.put(BasicInformation.GENDER, basic.getGender());
            profileBasic.put(BasicInformation.BIRTHDAY, basic.getBirthday());
            profileBasic.put(BasicInformation.PERSONAL_ID, basic.getPersonalId());

            Profiles profiles = new Profiles();
            profiles.setBasic(profileBasic);
            account.setProfiles(profiles);

            List<Contact> contacts = new ArrayList<Contact>();
            contacts.add(contact);
            account.setContacts(contacts);

            Account acc = AccountModelManager.getInstance().saveAccount(account);
            customer.setLoginId(acc.getLoginId());
            customer.setType("Cá nhân");
            if (contact.getMobile().length > 0) {
              customer.setMobile(contact.getMobile()[0]);
            }
            try {
              customer.setBirthDay(new SimpleDateFormat("dd/MM/yyyy").parse(basic.getBirthday()));
            } catch (Exception e) {
            }

            CustomerModelManager.getInstance().saveCustomer(customer);

          } catch (Exception e) {
          }
        }
      }
    }
  }