Exemplo n.º 1
0
 public static List<ConsoleVo> readExcel(String fileName) {
   List<ConsoleVo> list = new ArrayList<ConsoleVo>();
   try {
     Workbook book = Workbook.getWorkbook(new File(fileName));
     Sheet[] sheets = book.getSheets();
     for (Sheet s : sheets) {
       for (int i = 1; i < s.getRows(); i++) {
         ConsoleVo m = new ConsoleVo();
         Cell[] cells = s.getRow(i);
         if (cells != null) {
           System.out.println(cells.length);
           m.setLoginName(StringUtil.getNotNullValueString(cells[0].getContents()));
           m.setUserName(StringUtil.getNotNullValueString(cells[1].getContents()));
           m.setDeptId(StringUtil.getNotNullValueString(cells[2].getContents()));
           m.setOrders(StringUtil.getNotNullValueString(cells[3].getContents()));
           list.add(m);
         } else {
           System.out.print("excel格式错误导入数据失败!");
           return null;
         }
       }
     }
     book.close();
     return list;
   } catch (Exception e) {
     e.printStackTrace();
   }
   return null;
 }
Exemplo n.º 2
0
  public static void generateTrainingDataFromFile(
      String
          fileLocation) // Requires that the original file had the metadata and requires that this
        // file is formated the same in first sheet
      {
    testDataLL = (LinkedList<String[]>) dataLL.clone();
    actualClassifications = (LinkedList<String>) classificationsLL.clone();

    FileInputStream file;
    try {
      file = new FileInputStream(new File(fileLocation));
      Workbook excelFile = new HSSFWorkbook(file);
      Sheet sheet1 = excelFile.getSheetAt(0); // Data sheet
      for (Row row : sheet1) {
        String data[] = new String[row.getPhysicalNumberOfCells() - 1];
        String classification = "";

        int offset =
            0; // Used so that we can declare an array of the size of the attributes without the
        // classification
        for (Cell cell : row) {
          int index = cell.getColumnIndex();
          if (classificationLocation != index) {
            data[index - offset] = cell.toString();
          } else {
            classification = cell.toString();
            offset++;
          }
        }

        // Even though data and classifications are not really used add it onto the end so it is
        // still complete for in the event they end up being used in a later version
        dataLL.add(data);
        classificationsLL.add(classification);

        trainingDataLL.add(data);
        knownClassifications.add(classification);

        // Check to see if we have seen that classification yet
        int occurrences = 0;
        for (int i = 0; i < classificationTypes.size() && occurrences == 0; i++) {
          if (classificationTypes.get(i).compareTo(classification) == 0) {
            occurrences = 1;
          }
        }
        if (occurrences == 0) {
          classificationTypes.add(classification);
        }
      }
      excelFile.close();
    } catch (FileNotFoundException e) {
      System.out.println("Error file not found");
      System.exit(0);
    } catch (IOException e) {
      System.out.println("Unable to read file, disk drive may be failing");
      e.printStackTrace();
      System.exit(0);
    }
  }
  /**
   * 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;
  }
Exemplo n.º 4
0
 /**
  * 导出对象到Excel,不是基于模板的,直接新建一个Excel完成导出,基于流
  *
  * @param os 输出流
  * @param objs 对象列表
  * @param clz 对象类型
  * @param isXssf 是否是2007版本
  */
 public void exportExcelByPath(
     OutputStream os, List objs, Class clz, boolean isXssf, String message) {
   try {
     Workbook wb = handleExcel(objs, clz, isXssf, message);
     wb.write(os);
   } catch (FileNotFoundException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (IOException e) {
     e.printStackTrace();
     logger.error(e);
   }
 }
 /**
  * Name should be created after sheets created. A special defined name, _xlnm._FilterDatabase
  * (xlsx) or _FilterDatabase (xls), stores the selected cells for auto-filter
  */
 protected void importNamedRange() {
   for (int i = 0; i < workbook.getNumberOfNames(); i++) {
     Name definedName = workbook.getNameAt(i);
     if (skipName(definedName)) {
       continue;
     }
     SName namedRange = null;
     if (definedName.getSheetIndex() == -1) { // workbook scope
       namedRange = book.createName(definedName.getNameName());
     } else {
       namedRange = book.createName(definedName.getNameName(), definedName.getSheetName());
     }
     namedRange.setRefersToFormula(definedName.getRefersToFormula());
   }
 }
  protected org.zkoss.poi.ss.usermodel.Font getPoiFontFromRichText(
      Workbook book, Cell cell, RichTextString rstr, int run) {
    if (run < 0) return null; // ZSS-1138

    org.zkoss.poi.ss.usermodel.Font font =
        rstr instanceof HSSFRichTextString
            ? book.getFontAt(((HSSFRichTextString) rstr).getFontOfFormattingRun(run))
            : ((XSSFRichTextString) rstr).getFontOfFormattingRun((XSSFWorkbook) book, run);
    if (font == null) {
      CellStyle style = cell.getCellStyle();
      short fontIndex = style != null ? style.getFontIndex() : (short) 0;
      return book.getFontAt(fontIndex);
    }
    return font;
  }
Exemplo n.º 7
0
  public static void main(String args[]) {
    try {
      // 构建Workbook对象, 只读Workbook对象
      // 直接从本地文件创建Workbook
      // 从输入流创建Workbook

      System.out.println("start load file-------------------------");
      InputStream is = new FileInputStream("E:/account.xls"); // 创建输入

      jxl.Workbook rwb = Workbook.getWorkbook(is);
      Sheet rs = rwb.getSheet(0); // 读取第一个sheet
      int colNum = rs.getColumns(); // 列数
      int rowNum = rs.getRows(); // 行数
      // System.out.println(colNum+" "+rowNum);
      // 创建account  bean
      ApplicationContext ctx =
          new ClassPathXmlApplicationContext(
              new String[] {
                "spring/applicationContext-model-account.xml", "spring/applicationContext.xml"
              });
      AccountService g = (AccountService) ctx.getBean("accountService");
      for (int i = 1; i < rowNum; i++) {
        String no = rs.getCell(0, i).getContents();
        String pass = rs.getCell(1, i).getContents();
        String name = rs.getCell(2, i).getContents();
        String maj = rs.getCell(3, i).getContents();
        String grade = rs.getCell(4, i).getContents();
        String cla = rs.getCell(5, i).getContents();
        String idn = rs.getCell(6, i).getContents();
        String email = rs.getCell(7, i).getContents();
        String phone = rs.getCell(8, i).getContents();
        String status = rs.getCell(9, i).getContents();
        String role = rs.getCell(10, i).getContents();

        ModelAccount ma = new ModelAccount();
        ma.setAccountNo(no);
        ma.setAccountPassword(pass);
        ma.setAccountRealName(name);
        ma.setAccountMajority(maj);
        ma.setAccountGrade(grade);
        ma.setAccountClass(cla);
        ma.setAccountIdentification(idn);
        ma.setAccountEmail(email);
        ma.setAccountMobilePhone(phone);
        ma.setAccountStatus(0);
        ModelRoles ac = new ModelRoles();
        ac.setRole_id(role);
        ma.setAccountRole(ac);
        g.saveOrUpdate(ma);
        // ma.setAccount_role(account_role)
        // System.out.println(no);
      }

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Exemplo n.º 8
0
  /** @param args */
  public static void main(String[] args) {
    // TODO Auto-generated method stub
    try {
      Workbook book = Workbook.getWorkbook(new File("测试.xls"));
      Sheet sheet = book.getSheet(0);
      int x = sheet.getRows();
      int y = sheet.getColumns();
      System.out.println("表格的列数为:" + x);
      System.out.println("表格的行数为:" + y);

    } catch (BiffException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
 // ZSS-854
 protected void importDefaultCellStyles() {
   ((AbstractBookAdv) book).clearDefaultCellStyles();
   for (CellStyle poiStyle : workbook.getDefaultCellStyles()) {
     book.addDefaultCellStyle(importCellStyle(poiStyle, false));
   }
   // in case of XLS files which we have not support defaultCellStyles
   if (book.getDefaultCellStyles().isEmpty()) {
     ((AbstractBookAdv) book).initDefaultCellStyles();
   }
   // ZSS-1132: setup default font
   ((AbstractBookAdv) book).initDefaultFont();
 }
  public static void main(String[] args) throws Exception {
    // The path to the documents directory.
    String dataDir = Utils.getDataDir(ExportingDataFromWorksheets.class);

    // Creating a file stream containing the Excel file to be opened
    FileInputStream fstream = new FileInputStream(dataDir + "book1.xls");

    // Instantiating a Workbook object
    Workbook workbook = new Workbook(fstream);

    // Accessing the first worksheet in the Excel file
    Worksheet worksheet = workbook.getWorksheets().get(0);

    // Exporting the contents of 7 rows and 2 columns starting from 1st cell to Array.
    Object dataTable[][] = worksheet.getCells().exportArray(0, 0, 7, 2);

    // Printing the name of the cell found after searching worksheet
    System.out.println("No. Of Rows Imported: " + dataTable.length);

    // Closing the file stream to free all resources
    fstream.close();
  }
Exemplo n.º 11
0
 // ZSS-854
 protected void importNamedStyles() {
   ((AbstractBookAdv) book).clearNamedStyles();
   for (NamedStyle poiStyle : workbook.getNamedStyles()) {
     SNamedStyle namedStyle =
         new NamedStyleImpl(
             poiStyle.getName(),
             poiStyle.isCustomBuiltin(),
             poiStyle.getBuiltinId(),
             book,
             poiStyle.getIndex());
     book.addNamedCellstyle(namedStyle);
   }
 }
Exemplo n.º 12
0
  protected SFont importFont(CellStyle poiCellStyle) {
    SFont font = null;

    final short fontIndex = poiCellStyle.getFontIndex();
    if (importedFont.containsKey(fontIndex)) {
      font = importedFont.get(fontIndex);
    } else {
      Font poiFont = workbook.getFontAt(fontIndex);
      font = createZssFont(poiFont);
      importedFont.put(fontIndex, font); // ZSS-677
    }
    return font;
  }
Exemplo n.º 13
0
 /**
  * 导出对象到Excel,直接新建一个Excel完成导出,基于路径的导出,不基于模板
  *
  * @param outPath 导出路径
  * @param objs 对象列表
  * @param clz 对象类型
  * @param isXssf 是否是2007版本
  */
 public void exportExcelByPath(
     String outPath, List objs, Class clz, boolean isXssf, String message) {
   Workbook wb = handleExcel(objs, clz, isXssf, message);
   FileOutputStream fos = null;
   try {
     fos = new FileOutputStream(outPath);
     wb.toString().getBytes("GB2312");
     wb.write(fos);
   } catch (FileNotFoundException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (IOException e) {
     e.printStackTrace();
     logger.error(e);
   } finally {
     try {
       if (fos != null) fos.close();
     } catch (IOException e) {
       e.printStackTrace();
       logger.error(e);
     }
   }
 }
Exemplo n.º 14
0
 /**
  * 转换excel中的所有sheet
  *
  * @param xmlFile
  * @return
  */
 public boolean allSheetToXml(String xmlFile) {
   for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
     int result = excelToXml(xmlFile + "_" + (i + 1), i);
     if (result == 0) {
       System.out.println("转换出错,程序退出!");
       return false;
     }
     if (result == 2) {
       Util.msgToLog("error.log", xmlFile + "_" + (i + 1) + ".xml" + " 转换出错!");
       System.out.println(xmlFile + "_" + (i + 1) + ".xml" + " 转换出错!");
     }
   }
   return true;
 }
Exemplo n.º 15
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;
 }
Exemplo n.º 16
0
  private void saveExcelPoject(File file) throws IOException {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("timeplan");
    // Заголовок в 0 строке
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("Филиал");
    cell = row.createCell(1);
    cell.setCellValue("Город");
    Calendar cal = Calendar.getInstance();
    cal.set(2017, 0, 5); // Начальная дата проекта
    SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yy");
    for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
      cell = row.createCell(i + 2);
      cell.setCellValue(sdf.format(cal.getTime()));
      cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
    }

    // sheet.setColumnWidth(0, 256);

    // Цвета ячеек
    CellStyle[] styles = new CellStyle[6];
    styles[0] = wb.createCellStyle();
    styles[0].setFillForegroundColor(HSSFColor.RED.index);
    styles[0].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[1] = wb.createCellStyle();
    styles[1].setFillForegroundColor(HSSFColor.GREEN.index);
    styles[1].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[2] = wb.createCellStyle();
    styles[2].setFillForegroundColor(HSSFColor.BLUE.index);
    styles[2].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[3] = wb.createCellStyle();
    styles[3].setFillForegroundColor(HSSFColor.ROSE.index);
    styles[3].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[4] = wb.createCellStyle();
    styles[4].setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
    styles[4].setFillPattern(FillPatternType.SOLID_FOREGROUND);
    styles[5] = wb.createCellStyle();
    styles[5].setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    styles[5].setFillPattern(FillPatternType.SOLID_FOREGROUND);

    short rowIdx = 0;
    for (Region region : this.regions) {
      row = sheet.createRow(++rowIdx);
      cell = row.createCell(0);
      cell.setCellValue(region.filial);
      cell = row.createCell(1);
      cell.setCellValue(region.name);

      cal = Calendar.getInstance();
      cal.set(2017, 0, 5); // Начальная дата проекта
      for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
        short color = region.getDateColorIndex(cal.getTime());
        if (color >= 0) {
          cell = row.createCell(i + 2);
          cell.setCellStyle(styles[color]);
        }

        cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
      }
    }

    try (FileOutputStream fileOut = new FileOutputStream(file)) {
      wb.write(fileOut);
    }
  }
Exemplo n.º 17
0
  /*
   * import sheet scope content from POI Sheet.
   */
  protected SSheet importSheet(Sheet poiSheet, int poiSheetIndex) {
    SSheet sheet = book.createSheet(poiSheet.getSheetName());
    sheet.setDefaultRowHeight(UnitUtil.twipToPx(poiSheet.getDefaultRowHeight()));
    // ZSS-952
    importSheetDefaultColumnWidth(poiSheet, sheet);
    // reference FreezeInfoLoaderImpl.getRowFreeze()
    sheet.getViewInfo().setNumOfRowFreeze(BookHelper.getRowFreeze(poiSheet));
    sheet.getViewInfo().setNumOfColumnFreeze(BookHelper.getColumnFreeze(poiSheet));
    sheet
        .getViewInfo()
        .setDisplayGridlines(
            poiSheet
                .isDisplayGridlines()); // Note isDisplayGridlines() and isPrintGridlines() are
                                        // different
    sheet.getViewInfo().setColumnBreaks(poiSheet.getColumnBreaks());
    sheet.getViewInfo().setRowBreaks(poiSheet.getRowBreaks());

    SPrintSetup sps = sheet.getPrintSetup();

    SHeader header = sheet.getViewInfo().getHeader();
    if (header != null) {
      header.setCenterText(poiSheet.getHeader().getCenter());
      header.setLeftText(poiSheet.getHeader().getLeft());
      header.setRightText(poiSheet.getHeader().getRight());
      sps.setHeader(header);
    }

    SFooter footer = sheet.getViewInfo().getFooter();
    if (footer != null) {
      footer.setCenterText(poiSheet.getFooter().getCenter());
      footer.setLeftText(poiSheet.getFooter().getLeft());
      footer.setRightText(poiSheet.getFooter().getRight());
      sps.setFooter(footer);
    }

    if (poiSheet.isDiffOddEven()) {
      Header poiEvenHeader = poiSheet.getEvenHeader();
      if (poiEvenHeader != null) {
        SHeader evenHeader = new HeaderFooterImpl();
        evenHeader.setCenterText(poiEvenHeader.getCenter());
        evenHeader.setLeftText(poiEvenHeader.getLeft());
        evenHeader.setRightText(poiEvenHeader.getRight());
        sps.setEvenHeader(evenHeader);
      }
      Footer poiEvenFooter = poiSheet.getEvenFooter();
      if (poiEvenFooter != null) {
        SFooter evenFooter = new HeaderFooterImpl();
        evenFooter.setCenterText(poiEvenFooter.getCenter());
        evenFooter.setLeftText(poiEvenFooter.getLeft());
        evenFooter.setRightText(poiEvenFooter.getRight());
        sps.setEvenFooter(evenFooter);
      }
    }

    if (poiSheet.isDiffFirst()) {
      Header poiFirstHeader = poiSheet.getFirstHeader();
      if (poiFirstHeader != null) {
        SHeader firstHeader = new HeaderFooterImpl();
        firstHeader.setCenterText(poiFirstHeader.getCenter());
        firstHeader.setLeftText(poiFirstHeader.getLeft());
        firstHeader.setRightText(poiFirstHeader.getRight());
        sps.setFirstHeader(firstHeader);
      }
      Footer poiFirstFooter = poiSheet.getFirstFooter();
      if (poiFirstFooter != null) {
        SFooter firstFooter = new HeaderFooterImpl();
        firstFooter.setCenterText(poiFirstFooter.getCenter());
        firstFooter.setLeftText(poiFirstFooter.getLeft());
        firstFooter.setRightText(poiFirstFooter.getRight());
        sps.setFirstFooter(firstFooter);
      }
    }

    PrintSetup poips = poiSheet.getPrintSetup();

    sps.setBottomMargin(poiSheet.getMargin(Sheet.BottomMargin));
    sps.setTopMargin(poiSheet.getMargin(Sheet.TopMargin));
    sps.setLeftMargin(poiSheet.getMargin(Sheet.LeftMargin));
    sps.setRightMargin(poiSheet.getMargin(Sheet.RightMargin));
    sps.setHeaderMargin(poiSheet.getMargin(Sheet.HeaderMargin));
    sps.setFooterMargin(poiSheet.getMargin(Sheet.FooterMargin));

    sps.setAlignWithMargins(poiSheet.isAlignMargins());
    sps.setErrorPrintMode(poips.getErrorsMode());
    sps.setFitHeight(poips.getFitHeight());
    sps.setFitWidth(poips.getFitWidth());
    sps.setHCenter(poiSheet.getHorizontallyCenter());
    sps.setLandscape(poips.getLandscape());
    sps.setLeftToRight(poips.getLeftToRight());
    sps.setPageStart(poips.getUsePage() ? poips.getPageStart() : 0);
    sps.setPaperSize(PoiEnumConversion.toPaperSize(poips.getPaperSize()));
    sps.setCommentsMode(poips.getCommentsMode());
    sps.setPrintGridlines(poiSheet.isPrintGridlines());
    sps.setPrintHeadings(poiSheet.isPrintHeadings());

    sps.setScale(poips.getScale());
    sps.setScaleWithDoc(poiSheet.isScaleWithDoc());
    sps.setDifferentOddEvenPage(poiSheet.isDiffOddEven());
    sps.setDifferentFirstPage(poiSheet.isDiffFirst());
    sps.setVCenter(poiSheet.getVerticallyCenter());

    Workbook poiBook = poiSheet.getWorkbook();
    String area = poiBook.getPrintArea(poiSheetIndex);
    if (area != null) {
      sps.setPrintArea(area);
    }

    CellRangeAddress rowrng = poiSheet.getRepeatingRows();
    if (rowrng != null) {
      sps.setRepeatingRowsTitle(rowrng.getFirstRow(), rowrng.getLastRow());
    }

    CellRangeAddress colrng = poiSheet.getRepeatingColumns();
    if (colrng != null) {
      sps.setRepeatingColumnsTitle(colrng.getFirstColumn(), colrng.getLastColumn());
    }

    sheet.setPassword(poiSheet.getProtect() ? "" : null);

    // import hashed password directly
    importPassword(poiSheet, sheet);

    // ZSS-832
    // import sheet visible
    if (poiBook.isSheetHidden(poiSheetIndex)) {
      sheet.setSheetVisible(SheetVisible.HIDDEN);
    } else if (poiBook.isSheetVeryHidden(poiSheetIndex)) {
      sheet.setSheetVisible(SheetVisible.VERY_HIDDEN);
    } else {
      sheet.setSheetVisible(SheetVisible.VISIBLE);
    }

    // ZSS-1130
    // import conditionalFormatting
    importConditionalFormatting(sheet, poiSheet);
    return sheet;
  }
Exemplo n.º 18
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;
 }
Exemplo n.º 19
0
  public static void readExcelFile(String fileName) {
    FileInputStream file;
    try {
      file = new FileInputStream(new File(fileName));
      Workbook excelFile = new HSSFWorkbook(file);

      Sheet sheet1 = excelFile.getSheetAt(0); // Data sheet
      // Set just in case metadata is incomplete or malformed
      classificationLocation =
          sheet1.getRow(0).getPhysicalNumberOfCells()
              - 1; // Minus one since classificationLocation includes 0 and getPhysicalNumberOfCells
      // does not

      Sheet sheet2 = excelFile.getSheetAt(1); // Metadata sheet
      // Loop based on number of attribute names
      for (int i = 0; i < sheet2.getRow(0).getPhysicalNumberOfCells(); i++) {
        String[] metadata = new String[METADATASIZE];

        // Construct metadata
        Row currRow = sheet2.getRow(0); // This should be a row of names
        metadata[0] = currRow.getCell(i).toString();
        currRow = sheet2.getRow(1); // This should be a row of data types (discrete or continuous)
        metadata[1] = currRow.getCell(i).toString();
        currRow = sheet2.getRow(2); // This should say which one is the classifier
        if (currRow.getCell(i) == null
            || currRow.getCell(i).getCellType() == Cell.CELL_TYPE_BLANK) {
          metadata[2] = "attribute";
        } else {
          metadata[2] = "classifier";
          classificationLocation = i;
        }

        metadataLL.add(metadata);
      }

      for (Row row : sheet1) {
        String data[] = new String[row.getPhysicalNumberOfCells() - 1];
        int offset =
            0; // Used so that we can declare an array of the size of the attributes without the
        // classification
        for (Cell cell : row) {
          int index = cell.getColumnIndex();
          if (classificationLocation != index) {
            data[index - offset] = cell.toString();
          } else {
            classificationsLL.add(cell.toString());

            // Moved to generate training data so that we do not get possible classifications from
            // unknown data since some denote unknown by saying ?

            //						//Check to see if we have seen it yet
            //
            //						int occurrences = 0;
            //						for(int i = 0; i < classificationTypes.size(); i++)
            //						{
            //							if(classificationTypes.get(i).compareTo(cell.toString()) == 0)
            //							{
            //								occurrences++;
            //							}
            //						}
            //						if(occurrences == 0)
            //						{
            //							classificationTypes.add(cell.toString());
            //						}
            offset++;
          }
        }
        dataLL.add(data);
        // classCount = temp.length;
      }

      excelFile.close();
    } catch (FileNotFoundException e) {
      System.out.println("Error file not found");
      System.exit(0);
    } catch (IOException e) {
      System.out.println("Unable to read file, disk drive may be failing");
      e.printStackTrace();
      System.exit(0);
    }
  }