Example usage for org.apache.poi.ss.usermodel Workbook getSheet

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook getSheet.

Prototype

Sheet getSheet(String name);

Source Link

Document

Get sheet with the given name

Usage

From source file:org.powertools.engine.sources.ExcelTestSource.java

License:Open Source License

protected ExcelTestSource(String fileName, String sheetName, Scope scope, TestRunResultPublisher publisher) {
    super(scope, publisher);
    mFileName = fileName;/*  ww  w.  j  av  a  2 s  .com*/
    Workbook workbook = createWorkbook(fileName);
    mSheetName = sheetName.isEmpty() ? workbook.getSheetName(0) : sheetName;
    Sheet sheet = workbook.getSheet(mSheetName);
    if (sheet == null) {
        throw new ExecutionException("sheet '" + mSheetName + "' does not exist");
    }
    mRowIter = sheet.rowIterator();
}

---

From source file:org.rhq.helpers.perftest.support.reporting.ExcelExporter.java

License:Open Source License

/**
 * Create a sheet per test to show the individual timings we recorded
 * @param wb Workbook to attach the new sheet to
 * @param timings The map with timings from the test
 * @param result TestNG results of the test
 *//*from   ww  w  . j a  v  a 2s.co  m*/
private void createDetailsSheet(Workbook wb, Map<String, Long> timings, ITestResult result) {

    String name = result.getTestClass().getName();
    if (name.contains("."))
        name = name.substring(name.lastIndexOf(".") + 1);
    name += "." + result.getName();

    Sheet sheet = wb.getSheet(name);
    if (sheet == null)
        sheet = wb.createSheet(name);

    Row row = appendRow(sheet);
    Cell cell = row.createCell(0);
    cell.setCellStyle(boldText);
    cell.setCellValue("Class");
    name = result.getTestClass().getName();
    if (name.contains("."))
        name = name.substring(name.lastIndexOf(".") + 1);
    row.createCell(1).setCellValue(name);
    row = appendRow(sheet);

    cell = row.createCell(0);
    cell.setCellStyle(boldText);
    cell.setCellValue("Method");
    row.createCell(1).setCellValue(result.getName());

    cell = row.createCell(0);
    cell.setCellStyle(boldText);
    cell.setCellValue("Success:");
    row.createCell(1).setCellValue(result.isSuccess());

    row = appendRow(sheet);
    cell = row.createCell(0);
    cell.setCellStyle(boldText);
    cell.setCellValue("TestNG timing");
    row.createCell(1).setCellValue(result.getEndMillis() - result.getStartMillis());

    row = appendRow(sheet);
    cell = row.createCell(0);
    cell.setCellStyle(boldText);
    cell.setCellValue("Perf test timing");
    row.createCell(1).setCellValue(getTotalTime(timings));

    row = appendRow(sheet); // Empty row
    row = appendRow(sheet);
    cell = row.createCell(0);
    cell.setCellStyle(boldText);
    cell.setCellValue("Individual Timings");

    // Now the timings
    row = appendRow(sheet);
    cell = row.createCell(0);
    cell.setCellStyle(boldText);
    cell.setCellValue("Name");
    cell = row.createCell(1);
    cell.setCellStyle(boldText);
    cell.setCellValue("Duration");

    Set<Map.Entry<String, Long>> data = timings.entrySet();
    SortedSet<Map.Entry<String, Long>> sorted = new TreeSet<Map.Entry<String, Long>>(
            new Comparator<Map.Entry<String, Long>>() {

                public int compare(Map.Entry<String, Long> item1, Map.Entry<String, Long> item2) {

                    return item1.getKey().compareTo(item2.getKey());
                }
            });
    sorted.addAll(data);

    for (Map.Entry<String, Long> entry : sorted) {
        row = appendRow(sheet);
        cell = row.createCell(0);
        cell.setCellValue(entry.getKey());
        cell = row.createCell(1);
        cell.setCellStyle(integerStyle);
        cell.setCellValue(entry.getValue());
    }

    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);
}

---

From source file:org.sakaiproject.tool.assessment.ui.bean.evaluation.ExportResponsesBeanTest.java

License:Educational Community License

public void testGetAsWorkbook() {
    ExportResponsesBean bean = new ExportResponsesBean();
    byte[] xlsData = null;
    List<List<Object>> spreadsheetData = null;
    Workbook wb = null;

    // small test
    spreadsheetData = new ArrayList<List<Object>>();
    List<Object> row1 = new ArrayList<Object>();
    row1.add("A");
    row1.add("B");
    row1.add("C");
    row1.add("D");
    spreadsheetData.add(row1);/*from   www  .  ja  v a 2 s .c om*/
    addSheetHeader(spreadsheetData);

    wb = bean.getAsWorkbook(spreadsheetData);
    assertNotNull(wb);
    assertNotNull(wb.getSheet("responses"));
    xlsData = wbToBytes(wb);
    assertNotNull(xlsData);

    // medium test (100 columns x 200 rows)
    spreadsheetData = new ArrayList<List<Object>>();
    for (int i = 0; i < 200; i++) {
        List<Object> row = new ArrayList<Object>();
        for (int j = 0; j < 100; j++) {
            row.add("Item:" + i + ":" + j);
        }
        spreadsheetData.add(row);
    }
    addSheetHeader(spreadsheetData);

    wb = bean.getAsWorkbook(spreadsheetData);
    assertNotNull(wb);
    assertNotNull(wb.getSheet("responses"));
    xlsData = wbToBytes(wb);
    assertNotNull(xlsData);
}

---

From source file:org.sakaiproject.tool.assessment.ui.bean.evaluation.ExportResponsesBeanTest.java

License:Educational Community License

public void testGetAsWorkbookWide() {
    ExportResponsesBean bean = new ExportResponsesBean();
    byte[] xlsData = null;
    List<List<Object>> spreadsheetData = null;
    Workbook wb = null;

    // huge test (300 columns x 5 rows)
    spreadsheetData = new ArrayList<List<Object>>();
    for (int i = 0; i < 5; i++) {
        List<Object> row = new ArrayList<Object>();
        for (int j = 0; j < 300; j++) {
            row.add("Item:" + i + ":" + j);
        }//  w w w  .j  a v a 2s  .co  m
        spreadsheetData.add(row);
    }
    addSheetHeader(spreadsheetData);

    wb = bean.getAsWorkbook(spreadsheetData);
    assertNotNull(wb);
    assertNotNull(wb.getSheet("responses"));
    xlsData = wbToBytes(wb);
    assertNotNull(xlsData);
}

---

From source file:org.smart.migrate.dao.impl.MetaExcelDao.java

@Override
public List<Field> getFieldsOfTable(Connection connection, String table) {
    List<Field> fields = new ArrayList<Field>();
    try {//w w  w . ja v  a2  s  . co m
        InputStream inp = new FileInputStream(dbfile);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheet(table);
        if (sheet == null) {
            inp.close();
            return fields;
        }
        Row row = sheet.getRow(sheet.getFirstRowNum());
        if (row != null) {
            for (Cell cell : row) {
                Field field = new Field();
                field.setDefaultValue(null);
                field.setName(cell.getStringCellValue());
                field.setNullable(true);
                field.setType("varchar");
                fields.add(field);
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InvalidFormatException ex) {
        Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex);
    }
    return fields;
}

---

From source file:org.smart.migrate.util.ExcelUtils.java

/**
 * Add indendity column data for excel sheet
 * @param filename Excel name/*from   w  ww.  j  a va 2  s  .com*/
 * @param sheetName Sheet name
 */
public static void addIndendityColumnData(String filename, String sheetName) {
    try {
        InputStream inp = new FileInputStream(filename);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheet(sheetName);
        if (sheet != null && sheet.getPhysicalNumberOfRows() > 0) {
            Row row = sheet.getRow(sheet.getFirstRowNum());

            if (row != null) {
                int idColumn = cellIndexInRow("id", row);
                if (idColumn == -1) {
                    idColumn = row.getLastCellNum();
                    row.createCell(idColumn).setCellValue("id");
                    for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                        Row r = sheet.getRow(i);
                        Cell cell = r.getCell(idColumn);
                        if (cell == null) {
                            cell = r.createCell(idColumn);
                        }
                        cell.setCellValue(i);
                    }
                    FileOutputStream fileOut = new FileOutputStream(filename);
                    wb.write(fileOut);
                    fileOut.close();
                }
                inp.close();
            }
        }

    } catch (IOException ex) {
        Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InvalidFormatException ex) {
        Logger.getLogger(MetaExcelDao.class.getName()).log(Level.SEVERE, null, ex);
    }
}

---

From source file:org.spdx.spdxspreadsheet.AbstractSheet.java

License:Apache License

/**
 * @param workbook//from  ww  w.jav a 2 s .  co  m
 * @param sheetName
 */
public AbstractSheet(Workbook workbook, String sheetName) {
    sheet = workbook.getSheet(sheetName);
    if (sheet != null) {
        firstRowNum = sheet.getFirstRowNum();
        Row firstRow = sheet.getRow(firstRowNum);
        if (firstRow == null) {
            firstCellNum = 1;
        } else {
            firstCellNum = firstRow.getFirstCellNum();
        }
        findLastRow();
    } else {
        firstRowNum = 0;
        lastRowNum = 0;
        firstCellNum = 0;
    }
    createStyles(workbook);
}

---

From source file:org.springframework.web.servlet.view.document.XlsViewTests.java

License:Apache License

@Test
public void testXls() throws Exception {
    View excelView = new AbstractXlsView() {
        @Override//  w  ww .ja  va2s .c om
        protected void buildExcelDocument(Map<String, Object> model, Workbook workbook,
                HttpServletRequest request, HttpServletResponse response) throws Exception {
            Sheet sheet = workbook.createSheet("Test Sheet");
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue("Test Value");
        }
    };

    excelView.render(new HashMap<String, Object>(), request, response);

    Workbook wb = new HSSFWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
    assertEquals("Test Sheet", wb.getSheetName(0));
    Sheet sheet = wb.getSheet("Test Sheet");
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);
    assertEquals("Test Value", cell.getStringCellValue());
}

---

From source file:org.springframework.web.servlet.view.document.XlsViewTests.java

License:Apache License

@Test
public void testXlsxView() throws Exception {
    View excelView = new AbstractXlsxView() {
        @Override/*from   w ww  .  j  a va2s .  c  om*/
        protected void buildExcelDocument(Map<String, Object> model, Workbook workbook,
                HttpServletRequest request, HttpServletResponse response) throws Exception {
            Sheet sheet = workbook.createSheet("Test Sheet");
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue("Test Value");
        }
    };

    excelView.render(new HashMap<String, Object>(), request, response);

    Workbook wb = new XSSFWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
    assertEquals("Test Sheet", wb.getSheetName(0));
    Sheet sheet = wb.getSheet("Test Sheet");
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);
    assertEquals("Test Value", cell.getStringCellValue());
}

---

From source file:org.springframework.web.servlet.view.document.XlsViewTests.java

License:Apache License

@Test
public void testXlsxStreamingView() throws Exception {
    View excelView = new AbstractXlsxStreamingView() {
        @Override//from ww w .  j  av  a 2s  .  c  o  m
        protected void buildExcelDocument(Map<String, Object> model, Workbook workbook,
                HttpServletRequest request, HttpServletResponse response) throws Exception {
            Sheet sheet = workbook.createSheet("Test Sheet");
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue("Test Value");
        }
    };

    excelView.render(new HashMap<String, Object>(), request, response);

    Workbook wb = new XSSFWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
    assertEquals("Test Sheet", wb.getSheetName(0));
    Sheet sheet = wb.getSheet("Test Sheet");
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);
    assertEquals("Test Value", cell.getStringCellValue());
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• »