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

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetAt

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-7]    :  ?  ?? //from   ww  w.ja  va  2s .c  om
 */
@Test
public void testUseTemplate1() throws Exception {

    StringBuffer sb = new StringBuffer();
    StringBuffer sbResult = new StringBuffer();

    sb.append(fileLocation).append("/template/").append("template.xls");
    sbResult.append(fileLocation).append("/").append("testUseTemplate1.xls");

    Object[][] sample_data = { { "Yegor Kozlov", "YK", 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 },
            { "Gisella Bronzetti", "GB", 4.0, 3.0, 1.0, 3.5, null, null, 4.0 }, };

    try {

        Workbook wb = excelService.loadExcelTemplate(sb.toString());
        Sheet sheet = wb.getSheetAt(0);

        // set data
        for (int i = 0; i < sample_data.length; i++) {
            Row row = sheet.getRow(2 + i);
            for (int j = 0; j < sample_data[i].length; j++) {
                if (sample_data[i][j] == null)
                    continue;

                Cell cell = row.getCell(j);

                if (sample_data[i][j] instanceof String) {
                    cell.setCellValue(new HSSFRichTextString((String) sample_data[i][j]));
                } else {
                    cell.setCellValue((Double) sample_data[i][j]);
                }
            }
        }

        // ? 
        sheet.setForceFormulaRecalculation(true);

        excelService.createWorkbook(wb, sbResult.toString());

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wbT = excelService.loadWorkbook(sbResult.toString());
        Sheet sheetT = wbT.getSheetAt(0);

        for (int i = 0; i < sample_data.length; i++) {
            Row row = sheetT.getRow(2 + i);
            for (int j = 0; j < sample_data[i].length; j++) {
                Cell cell = row.getCell(j);

                LOGGER.debug("sample_data[i][j] : {}", sample_data[i][j]);

                if (sample_data[i][j] == null) {
                    assertEquals(cell.getCellType(), Cell.CELL_TYPE_BLANK);
                } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    assertEquals((Double) sample_data[i][j], Double.valueOf(cell.getNumericCellValue()));
                } else {
                    assertEquals((String) sample_data[i][j], cell.getRichStringCellValue().getString());
                }
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testUseTemplate end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-7-1]    :  ?  ?? //  w w w  .j  a  va  2  s.co  m
 * jXLS 
 */
@Test
public void testUseTemplate2() throws Exception {
    StringBuffer sb = new StringBuffer();
    StringBuffer sbResult = new StringBuffer();

    sb.append(fileLocation).append("/template/").append("template2.xls");
    sbResult.append(fileLocation).append("/").append("testUseTemplate2.xls");

    try {
        //  ? .
        List<PersonHourVO> persons = new ArrayList<PersonHourVO>();
        PersonHourVO person = new PersonHourVO();
        person.setName("Yegor Kozlov");
        person.setId("YK");
        person.setMon(5.0);
        person.setTue(8.0);
        person.setWed(10.0);
        person.setThu(5.0);
        person.setFri(5.0);
        person.setSat(7.0);
        person.setSun(6.0);

        persons.add(person);

        PersonHourVO person1 = new PersonHourVO();
        person1.setName("Gisella Bronzetti");
        person1.setId("GB");
        person1.setMon(4.0);
        person1.setTue(3.0);
        person1.setWed(1.0);
        person1.setThu(3.5);
        person1.setSun(4.0);

        persons.add(person1);

        Map<String, Object> beans = new HashMap<String, Object>();
        beans.put("persons", persons);
        XLSTransformer transformer = new XLSTransformer();

        transformer.transformXLS(sb.toString(), beans, sbResult.toString());

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wb = excelService.loadWorkbook(sbResult.toString());
        Sheet sheet = wb.getSheetAt(0);

        Double[][] value = { { 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 }, { 4.0, 3.0, 1.0, 3.5, null, null, 4.0 } };

        for (int i = 0; i < 2; i++) {
            Row row2 = sheet.getRow(i + 2);

            for (int j = 0; j < 7; j++) {
                Cell cellValue = row2.getCell((j + 2));
                if (cellValue.getCellType() == Cell.CELL_TYPE_BLANK)
                    continue;
                LOGGER.debug("cellTot.getNumericCellValue() : {}", cellValue.getNumericCellValue());
                assertEquals(value[i][j], Double.valueOf(cellValue.getNumericCellValue()));
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testUseTemplate end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 *  Data type //from  w w w.ja  va2 s.  c  o m
 *   ? ? Null ? ?
 */
@Test
public void testCellDataFormat() throws Exception {
    StringBuffer sb = new StringBuffer();
    sb.append(fileLocation).append("/").append("testDataFormat.xls");

    Workbook wb = excelService.loadWorkbook(sb.toString());
    Sheet sheet = wb.getSheetAt(0);

    Row row = sheet.getRow(7);
    Cell cell = row.getCell(0);

    assertEquals("2009/04/01", EgovExcelUtil.getValue(cell));

    row = sheet.getRow(8);
    cell = row.getCell(0);

    assertEquals("2009/04/02", EgovExcelUtil.getValue(cell));
}

From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-3]  ? ?  :  ?? ?(? ?, Border? ?, ? ?,  )? 
 *//* ww  w.  ja v a2 s  .  co m*/
@Test
public void testWriteExcelFileAttribute() throws Exception {

    try {
        log.debug("testWriteExcelFileAttribute start....");

        short rowheight = 40;
        int columnwidth = 30;

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx");

        // delete file
        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            log.debug("Delete file...." + sb.toString());
        }

        SXSSFWorkbook wb = new SXSSFWorkbook();

        Sheet sheet1 = wb.createSheet("new sheet");
        wb.createSheet("second sheet");

        // ? ?
        sheet1.setDefaultRowHeight(rowheight);
        sheet1.setDefaultColumnWidth(columnwidth);

        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        cs.setWrapText(true);

        // 
        cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

        cs.setFillPattern(HSSFCellStyle.DIAMONDS); //  ?

        // ? ?
        cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); //  
        cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // 

        sheet1.setDefaultColumnStyle((short) 0, cs);

        Workbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString());

        Sheet sheetTmp1 = tmp.getSheetAt(0);

        assertEquals(rowheight, sheetTmp1.getDefaultRowHeight());
        assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth());

        CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1));

        log.debug("getAlignment : " + cs1.getAlignment());
        assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment());

        log.debug("getFillPattern : " + cs1.getFillPattern());
        assertEquals(HSSFCellStyle.DIAMONDS, cs1.getFillPattern());

        log.debug("getFillForegroundColor : " + cs1.getFillForegroundColor());
        log.debug("getFillBackgroundColor : " + cs1.getFillBackgroundColor());
        assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor());
        assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor());

    } catch (Exception e) {
        log.error(e.toString());
        throw new Exception(e);
    } finally {
        log.debug("testWriteExcelFileAttribute end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-2]  ?  : ? ?  ? ?    ?// w  w w  . j a  v a  2 s . c o  m
 */
@Test
public void testModifyCellContents() throws Exception {

    try {
        String content = "Use \n with word wrap on to create a new line";
        short rownum = 2;
        int cellnum = 2;

        LOGGER.debug("testModifyCellContents start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testModifyCellContents.xlsx");

        if (!EgovFileUtil.isExistsFile(sb.toString())) {
            Workbook wbT = new XSSFWorkbook();
            wbT.createSheet();

            //  ? ?
            excelService.createWorkbook(wbT, sb.toString());
        }

        //  ? 
        XSSFWorkbook wb = null;
        wb = excelService.loadWorkbook(sb.toString(), wb);
        LOGGER.debug("testModifyCellContents after loadWorkbook....");

        Sheet sheet = wb.getSheetAt(0);
        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        //Word Wrap MUST be turned on
        cs.setWrapText(true);

        Row row = sheet.createRow(rownum);
        row.setHeight((short) 0x349);
        Cell cellx = row.createCell(cellnum);
        cellx.setCellType(XSSFCell.CELL_TYPE_STRING);
        cellx.setCellValue(new XSSFRichTextString(content));
        cellx.setCellStyle(cs);

        sheet.setColumnWidth(20, (int) ((50 * 8) / ((double) 1 / 20)));

        //excelService.writeWorkbook(wb);

        FileOutputStream outx = new FileOutputStream(sb.toString());
        wb.write(outx);
        outx.close();

        //  ? 
        Workbook wb1 = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook());

        Sheet sheet1 = wb1.getSheetAt(0);
        Row row1 = sheet1.getRow(rownum);
        Cell cell1 = row1.getCell(cellnum);

        // ? ?  ?
        LOGGER.debug("cell ###{}###", cell1.getRichStringCellValue());
        LOGGER.debug("cont ###{}###", content);

        assertNotSame("TEST", cell1.getRichStringCellValue().toString());
        assertEquals(content, cell1.getRichStringCellValue().toString());

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testModifyCellContents end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-3]  ? ?  :  ?? ?(? ?, Border? ?, ? ?,  )? 
 *///from ww  w.  ja  va 2s .c  om
@Test
public void testWriteExcelFileAttribute() throws Exception {

    try {
        LOGGER.debug("testWriteExcelFileAttribute start....");

        short rowheight = 40 * 10;
        int columnwidth = 30;

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx");

        // delete file
        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            LOGGER.debug("Delete file....{}", sb.toString());
        }

        XSSFWorkbook wb = new XSSFWorkbook();

        XSSFSheet sheet1 = wb.createSheet("new sheet");
        wb.createSheet("second sheet");

        // ? ?
        sheet1.setDefaultRowHeight(rowheight);
        sheet1.setDefaultColumnWidth(columnwidth);

        Font f2 = wb.createFont();
        XSSFCellStyle cs = wb.createCellStyle();

        cs.setFont(f2);
        cs.setWrapText(true);

        // 
        cs.setAlignment(CellStyle.ALIGN_RIGHT);
        cs.setFillPattern(CellStyle.DIAMONDS); //  ?

        XSSFRow r1 = sheet1.createRow(0);
        r1.createCell(0);

        // ? ?
        cs.setFillForegroundColor(IndexedColors.BLUE.getIndex()); //  
        cs.setFillBackgroundColor(IndexedColors.RED.getIndex()); // 

        sheet1.setDefaultColumnStyle((short) 0, cs);

        Workbook tmp = excelService.createWorkbook(wb, sb.toString());

        Sheet sheetTmp1 = tmp.getSheetAt(0);

        assertEquals(rowheight, sheetTmp1.getDefaultRowHeight());
        assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth());

        CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1));

        LOGGER.debug("getAlignment : {}", cs1.getAlignment());
        assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment());

        LOGGER.debug("getFillPattern : {}", cs1.getFillPattern());
        assertEquals(XSSFCellStyle.DIAMONDS, cs1.getFillPattern());

        LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor());
        LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor());

        LOGGER.debug(
                "XSSFWorkbook.getFillBackgroundColor(), XSSFColor().getIndexed() ? ? 0 ? ?");

        assertEquals(IndexedColors.BLUE.getIndex(), cs1.getFillForegroundColor());
        assertEquals(IndexedColors.RED.getIndex(), cs1.getFillBackgroundColor());

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testWriteExcelFileAttribute end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-7]    :  ?  ?? /*from  w  w w  .j  av  a 2 s .  co m*/
 */
@Test
public void testUseTemplate1() throws Exception {

    StringBuffer sb = new StringBuffer();
    StringBuffer sbResult = new StringBuffer();

    sb.append(fileLocation).append("/template/").append("template.xlsx");
    sbResult.append(fileLocation).append("/").append("testUseTemplate1.xlsx");

    Object[][] sample_data = { { "Yegor Kozlov", "YK", 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 },
            { "Gisella Bronzetti", "GB", 4.0, 3.0, 1.0, 3.5, null, null, 4.0 }, };

    try {

        XSSFWorkbook wb = null;
        wb = excelService.loadExcelTemplate(sb.toString(), wb);
        Sheet sheet = wb.getSheetAt(0);

        // set data
        for (int i = 0; i < sample_data.length; i++) {
            Row row = sheet.getRow(2 + i);
            for (int j = 0; j < sample_data[i].length; j++) {
                if (sample_data[i][j] == null)
                    continue;

                Cell cell = row.getCell(j);

                if (sample_data[i][j] instanceof String) {
                    cell.setCellValue(new XSSFRichTextString((String) sample_data[i][j]));
                } else {
                    cell.setCellValue((Double) sample_data[i][j]);
                }
            }
        }

        // ? 
        sheet.setForceFormulaRecalculation(true);

        excelService.createWorkbook(wb, sbResult.toString());

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wbT = excelService.loadWorkbook(sbResult.toString(), new XSSFWorkbook());
        Sheet sheetT = wbT.getSheetAt(0);

        for (int i = 0; i < sample_data.length; i++) {
            Row row = sheetT.getRow(2 + i);
            for (int j = 0; j < sample_data[i].length; j++) {
                Cell cell = row.getCell(j);

                LOGGER.debug("sample_data[i][j] : {}", sample_data[i][j]);

                if (sample_data[i][j] == null) {
                    assertEquals(cell.getCellType(), XSSFCell.CELL_TYPE_BLANK);
                } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                    assertEquals((Double) sample_data[i][j], Double.valueOf(cell.getNumericCellValue()));
                } else {
                    assertEquals((String) sample_data[i][j], cell.getRichStringCellValue().getString());
                }
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testUseTemplate end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-7-1]    :  ?  ?? /* ww  w .  j  a v  a2 s . c o  m*/
 * jXLS 
 */
@Test
public void testUseTemplate2() throws Exception {
    StringBuffer sb = new StringBuffer();
    StringBuffer sbResult = new StringBuffer();

    sb.append(fileLocation).append("/template/").append("template2.xlsx");
    sbResult.append(fileLocation).append("/").append("testUseTemplate2.xlsx");

    try {
        //  ? .
        List<PersonHourVO> persons = new ArrayList<PersonHourVO>();
        PersonHourVO person = new PersonHourVO();
        person.setName("Yegor Kozlov");
        person.setId("YK");
        person.setMon(5.0);
        person.setTue(8.0);
        person.setWed(10.0);
        person.setThu(5.0);
        person.setFri(5.0);
        person.setSat(7.0);
        person.setSun(6.0);

        persons.add(person);

        PersonHourVO person1 = new PersonHourVO();
        person1.setName("Gisella Bronzetti");
        person1.setId("GB");
        person1.setMon(4.0);
        person1.setTue(3.0);
        person1.setWed(1.0);
        person1.setThu(3.5);
        person1.setSun(4.0);

        persons.add(person1);

        Map<String, Object> beans = new HashMap<String, Object>();
        beans.put("persons", persons);
        XLSTransformer transformer = new XLSTransformer();

        transformer.transformXLS(sb.toString(), beans, sbResult.toString());

        //////////////////////////////////////////////////////////////////////////
        // ?
        Workbook wb = excelService.loadWorkbook(sbResult.toString(), new XSSFWorkbook());
        Sheet sheet = wb.getSheetAt(0);

        Double[][] value = { { 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0 }, { 4.0, 3.0, 1.0, 3.5, null, null, 4.0 } };

        for (int i = 0; i < 2; i++) {
            Row row2 = sheet.getRow(i + 2);

            for (int j = 0; j < 7; j++) {
                Cell cellValue = row2.getCell((j + 2));
                if (cellValue.getCellType() == Cell.CELL_TYPE_BLANK)
                    continue;
                LOGGER.debug("cellTot.getNumericCellValue() : {}", cellValue.getNumericCellValue());
                assertEquals(value[i][j], Double.valueOf(cellValue.getNumericCellValue()));
            }
        }

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testUseTemplate end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java

License:Apache License

/**
 *  Data type //  www  .  j av a 2 s.  c o m
 *   ? ? Null ? ?
 */
@Test
public void testCellDataFormat() throws Exception {
    StringBuffer sb = new StringBuffer();
    sb.append(fileLocation).append("/").append("testDataFormat.xlsx");

    Workbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook());
    Sheet sheet = wb.getSheetAt(0);

    Row row = sheet.getRow(7);
    Cell cell = row.getCell(0);

    assertEquals("2009/04/01", EgovExcelUtil.getValue(cell));

    row = sheet.getRow(8);
    cell = row.getCell(0);

    assertEquals("2009/04/02", EgovExcelUtil.getValue(cell));
}

From source file:egovframework.rte.fdl.excel.impl.EgovExcelServiceImpl.java

License:Apache License

/**
 * ?? ? DB upload ./*ww w . j  a v  a 2 s  .c o  m*/
 *
* @param queryId
* @param fileIn
* @param start(default : 0)
* @param commitCnt(default : 0)
* @return
* @throws Exception
*/
public Integer uploadExcel(String queryId, InputStream fileIn, int start, long commitCnt)
        throws BaseException, Exception {
    Workbook wb = loadWorkbook(fileIn);
    Sheet sheet = wb.getSheetAt(0);

    return uploadExcel(queryId, sheet, start, commitCnt);
}