Examples of org.apache.poi.ss.usermodel.Sheet

org.apache.poi.ss.usermodel.Sheet
High level representation of a Excel worksheet.
Sheets are the central structures within a workbook, and are where a user does most of his spreadsheet work. The most common type of sheet is the worksheet, which is represented as a grid of cells. Worksheet cells can contain text, numbers, dates, and formulas. Cells can also be formatted.

        File.separator + "WithMoreVariousData.xlsx"
    );
    assertTrue(xml.exists());
      
    XSSFWorkbook workbook = new XSSFWorkbook(xml.toString());
    Sheet sheet1 = workbook.getSheetAt(0);
    XSSFSheet sheet2 = (XSSFSheet)workbook.getSheetAt(1);
    
    assertTrue( ((XSSFSheet)sheet1).hasComments() );
    assertFalse( ((XSSFSheet)sheet2).hasComments() );
    
    assertEquals("Nick Burch", 
        sheet1.getRow(4).getCell(2).getCellComment().getAuthor());
    assertEquals("Nick Burch", 
        sheet1.getRow(6).getCell(2).getCellComment().getAuthor());
    assertEquals("Torchbox", 
        sheet1.getRow(12).getCell(2).getCellComment().getAuthor());
    
    // Save, and re-load the file
        workbook = XSSFTestDataSamples.writeOutAndReadBack(workbook);


    // Check we still have comments where we should do
    sheet1 = workbook.getSheetAt(0);
    assertNotNull(sheet1.getRow(4).getCell(2).getCellComment());
    assertNotNull(sheet1.getRow(6).getCell(2).getCellComment());
    assertNotNull(sheet1.getRow(12).getCell(2).getCellComment());
    
    // And check they still have the contents they should do
    assertEquals("Nick Burch", 
        sheet1.getRow(4).getCell(2).getCellComment().getAuthor());
    assertEquals("Nick Burch", 
        sheet1.getRow(6).getCell(2).getCellComment().getAuthor());
    assertEquals("Torchbox", 
        sheet1.getRow(12).getCell(2).getCellComment().getAuthor());
    
    // Todo - check text too, once bug fixed
  }

View Full Code Here

        ExcelParser parser = new ExcelParser((Map<String, List<DataListener>>) null);


        CellRangeAddress[] ranges = new CellRangeAddress[1];


        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        Cell cell = sheet.createRow(2).createCell(2);
        ranges[0] = new CellRangeAddress(2, 7, 2, 5);
        cell.setCellValue(FIRST_CELL_CONTENT);


        cell = sheet.createRow(7).createCell(5);
        cell.setCellValue(LAST_CELL_VALUE);


        cell = sheet.createRow(1).createCell(1);
        assertNull(parser.getRangeIfMerged(cell, ranges));


        cell = sheet.getRow(2).createCell(5);
        cell.setCellValue("wrong");


        CellRangeAddress rangeIfMerged = parser.getRangeIfMerged(cell, ranges);
        assertEquals(FIRST_CELL_CONTENT, sheet.getRow(rangeIfMerged.getFirstRow()).getCell(rangeIfMerged.getFirstColumn()).getStringCellValue());
    }

View Full Code Here

  }
  
  private void readSimpleBlock(Workbook wb, int sheetNo, ExcelBlock blockDefinition,
      OgnlStack stack, ReadStatus readStatus){
    //Simple Block will only care about cells in these Block
    Sheet sheet = wb.getSheetAt(sheetNo);
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    
    for(ExcelCell cellDefinition: blockDefinition.getCells()){
      Row row = sheet.getRow(cellDefinition.getRow());
      Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());      
      try{
        Object value = getCellValue(cell,evaluator);
        value = checkValue(sheetNo, ExcelUtil.getCellIndex(cellDefinition.getRow(),cellDefinition.getCol()),
            value, cellDefinition,

View Full Code Here

   * @throws Exception 
   */
  private Object readBlock(Workbook wb, int sheetNo, ExcelBlock blockDefinition, 
      int startRow, ReadStatus readStatus) 
    throws Exception{
    Sheet sheet = wb.getSheetAt(sheetNo);
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    
    if(blockDefinition.getLoopClass() == null){
      Map<String, Object> result = new HashMap<String, Object>();
      
      for(ExcelCell cellDefinition: blockDefinition.getCells()){    
        int rowOffSet = cellDefinition.getRow() - blockDefinition.getStartRow();
        Row row = sheet.getRow(startRow + rowOffSet);
        Cell cell = row == null ? null: row.getCell(cellDefinition.getCol());      
        try{
          Object value = getCellValue(cell,evaluator);
          value = checkValue(sheetNo, ExcelUtil.getCellIndex(startRow + rowOffSet ,cellDefinition.getCol()),
              value, cellDefinition,
              getPropertyType(result, cellDefinition));
          logger.debug("{}[Checked]:{}", ExcelUtil.getCellIndex(startRow + rowOffSet ,cellDefinition.getCol()), value);          
          result.put(cellDefinition.getDataName(), value);
        }catch(ExcelManipulateException e){
          if(readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
            readStatus.setStatus(ReadStatus.STATUS_DATA_COLLECTION_ERROR);
          readStatus.addException(e);
        }      
      }
      return result;
    }else{
      Object result = blockDefinition.getLoopClass().newInstance();    
      OgnlStack ognlStack = new OgnlStack(result);
      for(ExcelCell cellDefinition: blockDefinition.getCells()){    
        int rowOffSet = cellDefinition.getRow() - blockDefinition.getStartRow();
        Row row = sheet.getRow(startRow + rowOffSet);
        Cell cell = row == null ? null: row.getCell(cellDefinition.getCol());      
        try{
          Object value = getCellValue(cell,evaluator);
          value = checkValue(sheetNo, ExcelUtil.getCellIndex(startRow + rowOffSet ,cellDefinition.getCol()),
              value, cellDefinition,

View Full Code Here

      //remove sheets except the first one
      for(int i=wb.getNumberOfSheets() -1 ; i > 0; i--){
        wb.removeSheetAt(i);      
      }
      for(int i=0; i< beansList.size(); i++){
        Sheet newSheet = wb.createSheet("Auto Generated Sheet " + i);
        ExcelUtil.copySheet(wb.getSheetAt(0), newSheet);
      writeSheet(newSheet, 
            definition.getExcelSheets().iterator().next(), 
            new OgnlStack(beansList.get(i)), styleMap, writeStatus);
      }

View Full Code Here

  }
  
  private void reCalculateWorkbook(Workbook wb){
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
        Sheet sheet = wb.getSheetAt(sheetNum);
        for(Row r : sheet) {
            for(Cell c : r) {
                if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    evaluator.evaluateFormulaCell(c);
                }

View Full Code Here


  private void validateExcelSheet(final ByteArrayOutputStream boutSlow, final TableModel data)
      throws IOException, InvalidFormatException
  {
    Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(boutSlow.toByteArray()));
    Sheet sheet = workbook.getSheetAt(0);
    Assert.assertEquals(0, sheet.getFirstRowNum());
    Assert.assertEquals(data.getRowCount() - 1, sheet.getLastRowNum());


    for (int r = 0; r < data.getRowCount(); r += 1)
    {
      Row row = sheet.getRow(r);
      for (int c = 0; c < data.getColumnCount(); c += 1)
      {
        Cell cell = row.getCell(c);


        Object valueAt = data.getValueAt(r, c);

View Full Code Here

    return row.createCell(x);
  }


  protected Row getRowAt(final int y)
  {
    Sheet sheet = getSheet();
    final Row row = sheet.getRow(y);
    if (row != null)
    {
      return row;
    }
    return sheet.createRow(y);
  }

View Full Code Here

    public void parseFile( InputStream inStream ) {
        try {
            Workbook workbook = WorkbookFactory.create( inStream );


            if ( _useFirstSheet ) {
                Sheet sheet = workbook.getSheetAt( 0 );
                processSheet( sheet, _listeners.get( DEFAULT_RULESHEET_NAME ) );
            } else {
                for ( String sheetName : _listeners.keySet() ) {
                    Sheet sheet = workbook.getSheet( sheetName );
                    if ( sheet == null ) {
                        throw new IllegalStateException( "Could not find the sheetName (" + sheetName
                                                                 + ") in the workbook sheetNames." );
                    }
                    processSheet( sheet,

View Full Code Here

    try
    {
      //
      InputStream inputStream = new BufferedInputStream( new FileInputStream( this.file ) );
      Workbook wb = this.newWorkbookFrom( inputStream );
      Sheet sheet = wb.getSheet( MAINSHEETPAGENAME );
      
      //
      this.clear();
      for ( Row iRow : sheet )
      {

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of org.apache.poi.ss.usermodel.Sheet

accounts.web.ReportPoiController

accounts.web.ReportPoiControllerTest

bad.robot.excel.matchers.StubCell

bad.robot.excel.row.Row

br.com.objectos.comuns.io.xls.XlsFile

br.com.objectos.way.io.POIWorkbook

br.com.objectos.way.io.WayIO

br.com.starcode.trex.spreadsheet.PoiSheetParser

com.asakusafw.testdata.generator.excel.SheetBuilderTest

com.asakusafw.testdata.generator.excel.SheetEditor

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.