/**
   * Import the model according to reversed dependency order among model objects: book, sheet,
   * defined name, cells, chart, pictures, validation.
   */
  @Override
  public SBook imports(InputStream is, String bookName) throws IOException {

    // clear cache for reuse
    importedStyle.clear();
    importedFont.clear();

    workbook = createPoiBook(is);
    book = SBooks.createBook(bookName);
    //		book.setDefaultCellStyle(importCellStyle(workbook.getCellStyleAt((short) 0), false));
    // //ZSS-780
    // ZSS-854
    importDefaultCellStyles();
    importNamedStyles();
    // ZSS-1140
    importExtraStyles();
    setBookType(book);

    // ZSS-715: Enforce internal Locale.US Locale so formula is in consistent internal format
    Locale old = Locales.setThreadLocal(Locale.US);
    SBookSeries bookSeries = book.getBookSeries();
    boolean isCacheClean = bookSeries.isAutoFormulaCacheClean();
    try {
      bookSeries.setAutoFormulaCacheClean(false); // disable it to avoid
      // unnecessary clean up
      // during importing

      importExternalBookLinks();
      int numberOfSheet = workbook.getNumberOfSheets();
      for (int i = 0; i < numberOfSheet; i++) {
        Sheet poiSheet = workbook.getSheetAt(i);
        importSheet(poiSheet, i);
        SSheet sheet = book.getSheet(i);
        importTables(poiSheet, sheet); // ZSS-855, ZSS-1011
      }
      importNamedRange();
      for (int i = 0; i < numberOfSheet; i++) {
        SSheet sheet = book.getSheet(i);
        Sheet poiSheet = workbook.getSheetAt(i);
        for (Row poiRow : poiSheet) {
          importRow(poiRow, sheet);
        }
        importColumn(poiSheet, sheet);
        importMergedRegions(poiSheet, sheet);
        importDrawings(poiSheet, sheet);
        importValidation(poiSheet, sheet);
        importAutoFilter(poiSheet, sheet);
        importSheetProtection(poiSheet, sheet); // ZSS-576
      }
    } finally {
      book.getBookSeries().setAutoFormulaCacheClean(isCacheClean);
      Locales.setThreadLocal(old);
    }

    return book;
  }
Ejemplo n.º 2
0
 public List<Object> readExcel(Workbook wb, Class clz, int readLine, int tailLine) {
   Sheet sheet = wb.getSheetAt(0); // 取第一张表
   List<Object> objs = null;
   try {
     Row row = sheet.getRow(readLine); // 开始行,主题栏
     objs = new ArrayList<Object>();
     Map<Integer, String> maps = getHeaderMap(row, clz); // 设定对应的字段顺序与方法名
     if (maps == null || maps.size() <= 0)
       throw new RuntimeException("要读取的Excel的格式不正确,检查是否设定了合适的行"); // 与order顺序不符
     for (int i = readLine + 1; i <= sheet.getLastRowNum() - tailLine; i++) { // 取数据
       row = sheet.getRow(i);
       Object obj = clz.newInstance(); //   调用无参结构
       for (Cell c : row) {
         int ci = c.getColumnIndex();
         String mn = maps.get(ci).substring(3); // 消除get
         mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
         Map<String, Object> params = new HashMap<String, Object>();
         if (!"enterDate".equals(mn)) c.setCellType(Cell.CELL_TYPE_STRING); // 设置单元格格式
         else c.setCellType(Cell.CELL_TYPE_NUMERIC);
         if (this.getCellValue(c).trim().equals("是")) {
           BeanUtils.copyProperty(obj, mn, 1);
         } else if (this.getCellValue(c).trim().equals("否")) {
           BeanUtils.copyProperty(obj, mn, 0);
         } else BeanUtils.copyProperty(obj, mn, this.getCellValue(c));
       }
       objs.add(obj);
     }
   } catch (InstantiationException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (IllegalAccessException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (InvocationTargetException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (NumberFormatException e) {
     e.printStackTrace();
     logger.error(e);
   }
   return objs;
 }
  public void readProcessSpreadSheet() {
    processSpreadSheet = System.getProperty("processSpreadSheet");
    System.out.println("processSpreadSheet: " + processSpreadSheet);
    if (processCodesList.size() == 0) {
      if (processSpreadSheet != null && !processSpreadSheet.equals("")) {
        Workbook wb1 = null;
        try {
          wb1 = new XSSFWorkbook(processSpreadSheet);
        } catch (IOException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
        Sheet sheet = wb1.getSheetAt(0);
        Row row;
        Cell cell;

        int rows; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        int cols = 0; // No of columns
        int tmp = 0;

        // This trick ensures that we get the data properly even if it
        // doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
          row = sheet.getRow(i);
          if (row != null) {
            tmp = sheet.getRow(i).getPhysicalNumberOfCells();
            // out.println("tmp value"+tmp);
            if (tmp > cols) {
              cols = tmp;
            }
          }
        }

        ProcessDefinition tempProcessCode;
        for (int r1 = 0; r1 < rows; r1++) {
          tempProcessCode = new ProcessDefinition();

          row = sheet.getRow(r1);
          if (row != null) {
            if (row.getCell(0) != null) {
              for (int counter = 0; counter < cols; counter++) {
                cell = row.getCell((short) counter);
                // cell = row.getCell(1);
                if (counter == 0) {
                  if (cell != null) {
                    tempProcessCode.setProcessName(cell.getStringCellValue());
                  } else {
                    tempProcessCode.setProcessName("");
                  }
                } else if (counter == 1) {
                  if (cell != null) {
                    tempProcessCode.setProcessCode(cell.getStringCellValue());
                  } else {
                    tempProcessCode.setProcessCode("");
                  }
                } else if (counter == 2) {
                  if (cell != null) {
                    tempProcessCode.setIaeaCode(cell.getStringCellValue());
                  } else {
                    tempProcessCode.setIaeaCode("");
                  }
                } else if (counter == 3) {
                  if (cell != null) {
                    tempProcessCode.setProcessDescription(cell.getStringCellValue());
                    // System.out.println(tempProcessCode.getProcessDescription());
                  } else {
                    tempProcessCode.setProcessDescription("");
                    // System.out.println("Process Description EMpty");
                  }
                }
              }
            }

          } else {
            rows++;
          }
          processCodesList.add(tempProcessCode);
        }
      }
    }
    System.out.println(processCodesList.size());
  }