Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheet

Introduction

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

Prototype

@Override
public HSSFSheet getSheet(String name) 

Source Link

Document

Get sheet with the given name (case insensitive match)

Usage

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

License:Apache License

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

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

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testGetCellContents.xls");

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

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

        HSSFWorkbook wbTmp = new HSSFWorkbook();
        wbTmp.createSheet();

        //  ? ?
        excelService.createWorkbook(wbTmp, sb.toString());

        //  ? 
        HSSFWorkbook wb = excelService.loadWorkbook(sb.toString());
        log.debug("testGetCellContents after loadWorkbook....");

        HSSFSheet sheet = wb.createSheet("cell test sheet");

        HSSFCellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();
        cs.setWrapText(true);

        for (int i = 0; i < 100; i++) {
            HSSFRow row = sheet.createRow(i);
            for (int j = 0; j < 5; j++) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j));
                cell.setCellStyle(cs);
            }
        }

        //  ? 
        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        //////////////////////////////////////////////////////////////////////////
        // ?
        HSSFWorkbook wbT = excelService.loadWorkbook(sb.toString());
        HSSFSheet sheetT = wbT.getSheet("cell test sheet");

        for (int i = 0; i < 100; i++) {
            HSSFRow row1 = sheetT.getRow(i);
            for (int j = 0; j < 5; j++) {
                HSSFCell cell1 = row1.getCell(j);
                log.debug("row " + i + ", cell " + j + " : " + cell1.getRichStringCellValue());
                assertEquals("row " + i + ", cell " + j, cell1.getRichStringCellValue().toString());
            }
        }

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

---

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

License:Apache License

/**
 * [Flow #-6]  ?  :  ? ?(?, ? )? /*from w w w .j  a  va  2  s  .c o  m*/
 */
@Test
public void testModifyCellAttribute() throws Exception {

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

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testModifyCellAttribute.xls");

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

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

        HSSFWorkbook wbTmp = new HSSFWorkbook();
        wbTmp.createSheet();

        //  ? ?
        excelService.createWorkbook(wbTmp, sb.toString());

        //  ? 
        HSSFWorkbook wb = excelService.loadWorkbook(sb.toString());
        log.debug("testModifyCellAttribute after loadWorkbook....");

        HSSFSheet sheet = wb.createSheet("cell test sheet2");
        //           sheet.setColumnWidth((short) 3, (short) 200);   // column Width

        HSSFCellStyle cs = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setFontHeight((short) 16);
        font.setBoldweight((short) 3);
        font.setFontName("fixedsys");

        cs.setFont(font);
        cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // cell 
        cs.setWrapText(true);

        for (int i = 0; i < 100; i++) {
            HSSFRow row = sheet.createRow(i);
            //              row.setHeight((short)300); // row? height 

            for (int j = 0; j < 5; j++) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(new HSSFRichTextString("row " + i + ", cell " + j));
                cell.setCellStyle(cs);
            }
        }

        //  ? 
        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        //////////////////////////////////////////////////////////////////////////
        // ?
        HSSFWorkbook wbT = excelService.loadWorkbook(sb.toString());
        HSSFSheet sheetT = wbT.getSheet("cell test sheet2");
        log.debug("getNumCellStyles : " + wbT.getNumCellStyles());

        HSSFCellStyle cs1 = wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1));

        HSSFFont fontT = cs1.getFont(wbT);
        log.debug("font getFontHeight : " + fontT.getFontHeight());
        log.debug("font getBoldweight : " + fontT.getBoldweight());
        log.debug("font getFontName : " + fontT.getFontName());
        log.debug("getAlignment : " + cs1.getAlignment());
        log.debug("getWrapText : " + cs1.getWrapText());

        for (int i = 0; i < 100; i++) {
            HSSFRow row1 = sheetT.getRow(i);
            for (int j = 0; j < 5; j++) {
                HSSFCell cell1 = row1.getCell(j);
                log.debug("row " + i + ", cell " + j + " : " + cell1.getRichStringCellValue());
                assertEquals(16, fontT.getFontHeight());
                assertEquals(3, fontT.getBoldweight());
                assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment());
                assertTrue(cs1.getWrapText());
            }
        }

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

---

From source file:energy.usef.pbcfeeder.PbcFeeder.java

License:Apache License

/**
 * This method reads the excel file based on the location given in the config file. The method started when the during
 * deployment and fills the field stubColInputMap and stubRowInputList;
 *///from w  w  w  .  j a  va 2s  .  c  o m
public void readFile(Path pathToExcelSheet) {
    LOGGER.debug("Looking for excel sheet filename: {}", pathToExcelSheet);
    try (InputStream pbcInput = new FileInputStream(pathToExcelSheet.toFile())) {
        HSSFWorkbook pbcWorkbook = new HSSFWorkbook(pbcInput);
        HSSFSheet pbcDataSheet = pbcWorkbook.getSheet(DATA_SHEET);
        HSSFSheet pbcCongestionPointLimitsSheet = pbcWorkbook.getSheet(CPLIMITS_SHEET);
        fillColStubInputMap(pbcDataSheet);
        fillCongestionPointLimitsMaps(pbcCongestionPointLimitsSheet);
        fillStubRowInputList(pbcDataSheet);
        pbcWorkbook.close();
        LOGGER.debug("Created step data based on excel sheet.");
    } catch (IOException e) {
        LOGGER.error("Caught exception while parsing the PBC feeder data sheet.", e);
    }
}

---

From source file:eu.squadd.timesheets.eolas.TimeTemplate.java

public String prepareTimesheet(String[] args) {
    String response = null;//from  w  ww.ja va2  s .c o m
    try {
        String[] ym = args[0].split("/");
        month = Integer.parseInt(ym[0]);
        year = Integer.parseInt(ym[1]);

        Calendar cal = Calendar.getInstance(TimeZone.getDefault());
        cal.set(Calendar.YEAR, year);
        cal.set(Calendar.MONTH, month - 1);
        int days = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
        monthName = cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.ENGLISH);
        String periodName = monthName + "-" + year;
        cal.set(Calendar.DATE, 1);
        String dayOfWeek = new SimpleDateFormat("EE").format(cal.getTime());

        System.out.println("Month: " + periodName);
        System.out.println("Days in month: " + days);
        System.out.println("Month starts in: " + dayOfWeek);

        Map<String, String> bankHolidays = year == 2016 ? publicHolidays2016 : publicHolidays2017;
        Map<String, String> holidays = this.extractHolidays(args);

        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(template));
        HSSFSheet sheet = wb.getSheet("timesheet"); //getSheetAt(0);
        HSSFRow currentRow;
        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        sheet.getRow(4).getCell(1).setCellValue(periodName);
        int row = 7;
        int startRow = 0;
        int i = 1;
        while (i <= days) {
            currentRow = sheet.getRow(row);
            if (currentRow.getRowNum() > 47)
                break;
            String day = currentRow.getCell(0).getStringCellValue();

            if (day.startsWith("Total")) {
                evaluator.evaluateFormulaCell(currentRow.getCell(2));
                evaluator.evaluateFormulaCell(currentRow.getCell(4));
                row++;
                continue;
            }

            if (startRow == 0) {
                if (dayOfWeek.equals(day.substring(0, 3))) {
                    startRow = currentRow.getRowNum();
                    System.out.println("Starting row found: " + startRow + 1);
                } else {
                    row++;
                    continue;
                }
            }
            cal.set(Calendar.DATE, i);
            String date = sdf.format(cal.getTime());
            if (!day.equals("Saturday") && !day.equals("Sunday") && bankHolidays.get(date) == null
                    && holidays.get(date) == null) {
                currentRow.getCell(1).setCellValue(date);
                currentRow.getCell(2).setCellValue(defaultHours); // regular hours
                //currentRow.getCell(3).setCellValue(defaultHours);   // overtime hours
                currentRow.getCell(4).setCellValue(defaultHours); // total hours                    
            }
            i++;
            row++;
        }
        currentRow = sheet.getRow(46);
        evaluator.evaluateFormulaCell(currentRow.getCell(2));
        evaluator.evaluateFormulaCell(currentRow.getCell(4));
        currentRow = sheet.getRow(47);
        evaluator.evaluateFormulaCell(currentRow.getCell(2));
        evaluator.evaluateFormulaCell(currentRow.getCell(4));
        response = outFilePath.replace("#MONTH#", periodName);
        wb.write(new FileOutputStream(response));

    } catch (IOException ex) {
        Logger.getLogger(Timesheets.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Timesheet created.");
    return response;
}

---

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelObjectBinder.java

License:BSD License

/**
 * Create objects of type (target) populated from the given Excel file 
 * @param excelFile/*from  w  w  w .  j  av a2 s  . co m*/
 * @param target
 * @return Map of (target) objects keyed by worksheet name.  Each map entry value is a List
 *     of objects.
 * @throws IOException
 */
public Map bind(InputStream excelFile, Class target) throws IOException {

    HSSFWorkbook wb = ExcelUtility.createWorkbook(excelFile);
    int cnt = wb.getNumberOfSheets();
    Map wsMap = new HashMap();

    for (int i = 0; i < cnt; i++) {
        String name = wb.getSheetName(i);
        if (log.isDebugEnabled()) {
            log.debug("Worksheet binding => " + i + " (" + name + ")");
        }
        List objects = bindWorksheet(target, wb.getSheet(name));
        wsMap.put(name, objects);
    }
    return wsMap;

}

---

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.excel.ExcelUtility.java

License:BSD License

public static HSSFSheet createHSSFSheet(HSSFWorkbook wb, String sheetname) {
    HSSFSheet hssfsheet = wb.getSheet(sheetname);

    return hssfsheet;
}

---

From source file:info.toegepaste.www.service.ProjectServiceImpl.java

public IngelezenFile getExcelScores(InputStream fs) {
    ArrayList<String> lijstNr = new ArrayList<String>();
    ArrayList<String> lijstNaam = new ArrayList<String>();
    ArrayList<String> lijstScore = new ArrayList<String>();
    IngelezenFile file = null;/*from  w w w .  j a v  a  2 s . c om*/
    //String fileContent;
    //Part filePart;
    try {

        //FileInputStream fileInputStream = new FileInputStream("C:\\Users\\Jeroen\\Desktop\\resultaten.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(fs);
        HSSFSheet worksheet = workbook.getSheet("Blad1");
        HSSFRow row1 = worksheet.getRow(0);

        HSSFCell cel = worksheet.getRow(3).getCell((short) 1);
        cel.setCellType(Cell.CELL_TYPE_STRING);

        String klas = worksheet.getRow(0).getCell((short) 1).getStringCellValue();
        String vak = worksheet.getRow(1).getCell((short) 1).getStringCellValue();
        String test = worksheet.getRow(2).getCell((short) 1).getStringCellValue();
        String totaalPunt = cel.getStringCellValue();

        int i = 6;
        // deze lus werkt niet als je regels weg doet uit een excel file, dus minder dan 3 studenten kan niet, meer wel!
        while (worksheet.getRow(i) != null && worksheet.getRow(i).getCell((short) 1) != null) {
            HSSFCell scorecel = worksheet.getRow(i).getCell((short) 2);
            scorecel.setCellType(Cell.CELL_TYPE_STRING);
            HSSFCell naamcel = worksheet.getRow(i).getCell((short) 1);
            naamcel.setCellType(Cell.CELL_TYPE_STRING);
            HSSFCell nrcel = worksheet.getRow(i).getCell((short) 0);
            nrcel.setCellType(Cell.CELL_TYPE_STRING);

            lijstNr.add(worksheet.getRow(i).getCell((short) 0).getStringCellValue());
            lijstNaam.add(worksheet.getRow(i).getCell((short) 1).getStringCellValue());
            lijstScore.add(worksheet.getRow(i).getCell((short) 2).getStringCellValue());

            i++;
        }

        file = new IngelezenFile(klas, vak, test, Integer.parseInt(totaalPunt), lijstNr, lijstNaam, lijstScore);

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    //return lijst;

    // upload scores
    insertTest(file);
    return file;
}

---

From source file:io.lavagna.service.ExcelExportServiceTest.java

License:Open Source License

@Test
public void testExportMilestoneToExcel() throws IOException {

    HSSFWorkbook w = excelExportService.exportMilestoneToExcel(project.getShortName(), "1.0", user);

    Assert.assertNotNull(w);//from   w w  w. ja  va 2  s  . c  o  m
    Assert.assertEquals(1, w.getSheet("1.0").getLastRowNum()); // 0 based -> 1 means 2 rows (header + 1 card)

}

---

From source file:io.lavagna.service.ExcelExportServiceTest.java

License:Open Source License

@Test
public void testExportProjectToExcel() throws IOException {

    HSSFWorkbook w = excelExportService.exportProjectToExcel(project.getShortName(), user);

    Assert.assertNotNull(w);/*from   ww  w . j a va2 s  . co m*/
    Assert.assertEquals(1, w.getSheet(project.getName()).getLastRowNum()); // 0 based -> 1 means 2 rows (header + 1 card)

}

---

From source file:it.filippovitale.fineco2qif.logic.ExcelSheetAnalysisLogic.java

License:Apache License

public static HSSFSheet getSheetFromFile(String filename, String sheetname) {
    HSSFSheet sheet = null;//ww w .  jav  a2s  .  c  om
    try {
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        sheet = wb.getSheet(sheetname);
    } catch (Exception e) {
        log.error(e);
    }

    dumpSheet(sheet);

    return sheet;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• »