Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. XSSFWorkbook

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook.

Prototype

public XSSFWorkbook(PackagePart part) throws IOException

Source Link

Document

Constructs a XSSFWorkbook object using Package Part.

Usage

From source file:com.accenture.control.ImportaStepPadrao.java

/**
 * Verifica se a verso que existe no banco  maior do que a verso da planilha
 *
 * @return caso a verso da planilha seja maior retorna true, caso no retorna falso
 *///from   w ww  . ja  v  a2 s .c  o  m
public boolean verificaVersao(String planilha)
        throws SQLException, ClassNotFoundException, FileNotFoundException, IOException {
    int linha = 1;
    StepPadrao sp = new StepPadrao();
    double versao = 0;

    // fazendo uma instacia do banco
    ManipulaDadosSQLite bd = new ManipulaDadosSQLite();
    //Setando caminho da planilha para o arquivo
    FileInputStream arquivo = new FileInputStream(new File(planilha));
    // Instnciando um workbook e passando o arquivo como parametro
    XSSFWorkbook workbook = new XSSFWorkbook(arquivo);
    //Selecionando a primeira planilha
    XSSFSheet aba = workbook.getSheetAt(0);
    //Selecionando a segunda linha (1)
    Row row = aba.getRow(linha);
    //selecionando a celula A2
    Cell celVersao = row.getCell(0);

    //loop para capturar a ltima verso da planilha
    while (celVersao.getNumericCellValue() != 0) {
        //           double versao = 0;

        versao = celVersao.getNumericCellValue();
        linha = linha + 1;
        row = aba.getRow(linha);
        celVersao = row.getCell(0);
    }

    if (versao <= bd.getVersaoStepPadrao()) {
        return false;
    } else {
        return true;
    }

}

From source file:com.accenture.control.ImportaStepPadrao.java

/**
 * Verifica se a verso que existe no banco  maior do que a verso da planilha
 *
 * @return caso a verso da planilha seja maior retorna true, caso no retorna falso
 *///from  www  .j  av  a 2s .  c om
public double getVersaoPlanilha(String planilha)
        throws SQLException, ClassNotFoundException, FileNotFoundException, IOException {
    int linha = 1;
    StepPadrao sp = new StepPadrao();
    double versao = 0;

    // fazendo uma instacia do banco
    ManipulaDadosSQLite bd = new ManipulaDadosSQLite();
    //Setando caminho da planilha para o arquivo
    FileInputStream arquivo = new FileInputStream(new File(planilha));
    // Instnciando um workbook e passando o arquivo como parametro
    XSSFWorkbook workbook = new XSSFWorkbook(arquivo);
    //Selecionando a primeira planilha
    XSSFSheet aba = workbook.getSheetAt(0);
    //Selecionando a segunda linha (1)
    Row row = aba.getRow(linha);
    //selecionando a celula A2
    Cell celVersao = row.getCell(0);

    //loop para capturar a ltima verso da planilha
    while (celVersao.getNumericCellValue() != 0) {
        //           double versao = 0;

        versao = celVersao.getNumericCellValue();
        linha = linha + 1;
        row = aba.getRow(linha);
        celVersao = row.getCell(0);
    }

    return versao;

}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean newTsSheet(String pathSheet, String nameSheet, List<TesteCaseTSBean> listTestCase)
        throws FileNotFoundException, IOException {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();//from  w  w w.  j a  v  a 2 s .  c  om
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);

    if (listTestCase.size() > 0) {
        descriptionPlan.setCellValue(listTestCase.get(0).getTestPlan());
        prj.setCellValue(listTestCase.get(0).getSTIPRJ());
        fase.setCellValue(listTestCase.get(0).getFASE());
        testPhase.setCellValue(listTestCase.get(0).getTestPhase());
    }

    for (int i = 0; i < listTestCase.size(); i++) {

        //            estilo.setDataFormat(format.getFormat(formatData));
        //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

        row = sheetTS.getRow(linha);

        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);

        testScriptName.setCellValue(listTestCase.get(i).getTestScriptName());
        testScriptDescription.setCellValue(listTestCase.get(i).getTestScriptDescription());
        stepNo.setCellValue(listTestCase.get(i).getSTEP_NUMERO());
        stepDescription.setCellValue(listTestCase.get(i).getStepDescription());
        expectedResults.setCellValue(listTestCase.get(i).getExpectedResults());
        product.setCellValue(listTestCase.get(i).getProduct());
        estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
        dataPlanejada.setCellValue(listTestCase.get(i).getDataPlanejada());
        dataPlanejada.setCellStyle(estilo);

        linha = linha + 2;

    }

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean newTsSheet(String pathSheet, String nameSheet, TesteCaseTSBean testCase)
        throws FileNotFoundException, IOException {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();/*www.  ja  v  a  2  s .  co  m*/
    destinationSheet = new File(pathSheet + "\\" + nameSheet + ".xlsx");
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);
    Cell automatizado = row.getCell(12);

    descriptionPlan.setCellValue(testCase.getTestPlan());
    prj.setCellValue(testCase.getSTIPRJ());
    fase.setCellValue(testCase.getFASE());
    testPhase.setCellValue(testCase.getTestPhase());

    //            estilo.setDataFormat(format.getFormat(formatData));
    //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

    row = sheetTS.getRow(linha);

    prj = row.getCell(1);
    fase = row.getCell(2);
    testPhase = row.getCell(3);
    testScriptName = row.getCell(4);
    testScriptDescription = row.getCell(5);
    stepNo = row.getCell(6);
    stepDescription = row.getCell(7);
    expectedResults = row.getCell(8);
    product = row.getCell(9);
    dataPlanejada = row.getCell(10);
    complexidade = row.getCell(11);
    automatizado = row.getCell(12);

    testScriptName.setCellValue(testCase.getTestScriptName());
    testScriptDescription.setCellValue(testCase.getTestScriptDescription());
    stepNo.setCellValue(testCase.getSTEP_NUMERO());
    stepDescription.setCellValue(testCase.getStepDescription());
    expectedResults.setCellValue(testCase.getExpectedResults());
    product.setCellValue(testCase.getProduct());
    estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
    dataPlanejada.setCellValue(FunctiosDates.getDateActual());
    dataPlanejada.setCellStyle(estilo);
    complexidade.setCellValue(testCase.getComplexidade());
    automatizado.setCellValue(testCase.isAutomatizado());
    linha = linha + 2;

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean createSpreadsheetTS(String pathSheet, String nameSheet, TestPlanTSBean testPlan)
        throws Exception {

    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();/*from  w w  w  .jav  a2s . c  o  m*/
    String sheetTI = pathSheet + "\\" + nameSheet;
    nameSheet = nameSheet.replace("xlsx", "xlsm");
    ;
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File("C:\\FastPlan\\sheets\\TS_NEW.xlsm");
    logger.info("Realizando cpia da planilha");
    boolean existInList = false;

    List<TesteCaseTSBean> testCasesAutomatizados = new ArrayList<TesteCaseTSBean>();

    //run macro
    String cmd = "C:\\FastPlan\\runMacro.vbs";
    Runtime.getRuntime().exec("cmd /c" + cmd);

    Thread.sleep(2000);

    copySheet(sourceStheet, destinationSheet);
    logger.info("Planilha copiada");
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);

    XSSFSheet sheetTS = workbook.getSheetAt(0);
    XSSFCellStyle estilo = workbook.createCellStyle();

    //        workbook.setSheetName(workbook.getSheetIndex(sheetTS), FunctiosDates.dateToString(FunctiosDates.getDateActual(), "yyyy-MM-dd-HH-mm-ss"));
    int linha = 2;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell release = row.getCell(1);
    Cell prj = row.getCell(2);
    Cell fase = row.getCell(3);
    Cell testPhase = row.getCell(4);
    Cell testScriptName = row.getCell(5);
    Cell testScriptDescription = row.getCell(6);
    Cell stepNo = row.getCell(7);
    Cell stepDescription = row.getCell(8);
    Cell expectedResults = row.getCell(9);
    Cell product = row.getCell(10);
    Cell dataPlanejada = row.getCell(11);
    Cell qtdSteps = row.getCell(12);
    Cell complexidade = row.getCell(13);
    Cell automatizado = row.getCell(14);

    logger.info("Inserindo dados do plano");
    descriptionPlan.setCellValue(testPlan.getName());
    release.setCellValue(testPlan.getRelease());

    for (int i = 0; i < testPlan.getTestCase().size(); i++) {
        row = sheetTS.getRow(linha);

        descriptionPlan = row.getCell(0);
        release = row.getCell(1);
        prj = row.getCell(2);
        fase = row.getCell(3);
        testPhase = row.getCell(4);
        testScriptName = row.getCell(5);
        testScriptDescription = row.getCell(6);
        stepNo = row.getCell(7);
        stepDescription = row.getCell(8);
        expectedResults = row.getCell(9);
        product = row.getCell(10);
        dataPlanejada = row.getCell(11);
        qtdSteps = row.getCell(12);
        complexidade = row.getCell(13);
        automatizado = row.getCell(14);

        logger.info("Inserindo dados dos TCs");
        System.out.println("com.accenture.ts.dao.TesteCaseTSDAO.createSpreadsheetTS() - " + testPlan.getSti()
                + " - " + "row:" + linha);
        prj.setCellValue(testPlan.getSti());
        fase.setCellValue(testPlan.getCrFase());
        testPhase.setCellValue(testPlan.getTestPhase());
        testScriptName.setCellValue(testPlan.getTestCase().get(i).getTestScriptName());
        testScriptDescription.setCellValue(testPlan.getTestCase().get(i).getTestScriptDescription());
        product.setCellValue(testPlan.getTestCase().get(i).getProduct());
        estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
        dataPlanejada.setCellValue(testPlan.getTestCase().get(i).getDataPlanejada());
        dataPlanejada.setCellStyle(estilo);
        qtdSteps.setCellValue(testPlan.getTestCase().get(i).getListStep().size());
        complexidade.setCellValue(testPlan.getTestCase().get(i).getComplexidade());

        //set colors 
        if (i % 2 == 0) {

            System.out.println("com.accenture.ts.dao.TesteCaseTSDAO.createSpreadsheetTS() - entrou");
            //                setColorCells(new Cell[]{descriptionPlan, release, prj, fase, testPhase, testScriptName, testScriptDescription, stepNo,
            //                    stepDescription, expectedResults, product, dataPlanejada, qtdSteps, complexidade}, workbook);

            XSSFCellStyle styleColor = (XSSFCellStyle) product.getCellStyle();
            styleColor.setFillBackgroundColor(HSSFColor.LIGHT_GREEN.index);
            product.setCellStyle(styleColor);

        }

        for (int j = 0; j < testPlan.getTestCase().get(i).getListStep().size(); j++) {

            row = sheetTS.getRow(linha);
            stepNo = row.getCell(7);
            stepDescription = row.getCell(8);
            expectedResults = row.getCell(9);
            //                
            //                stepNo.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getNomeStep());
            logger.info("Inserindo dados dos Steps");
            stepNo.setCellValue(j + 1);
            stepDescription.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getDescStep());
            expectedResults.setCellValue(testPlan.getTestCase().get(i).getListStep().get(j).getResultadoStep());

            linha = linha + 1;

            row = sheetTS.getRow(linha);

            stepNo = row.getCell(7);
            stepDescription = row.getCell(8);
            expectedResults = row.getCell(9);

        }

        linha = linha + 1;
        row = sheetTS.getRow(linha);

        descriptionPlan = row.getCell(0);
        release = row.getCell(1);
        prj = row.getCell(2);
        fase = row.getCell(3);
        testPhase = row.getCell(4);
        testScriptName = row.getCell(5);
        testScriptDescription = row.getCell(6);
        stepNo = row.getCell(7);
        stepDescription = row.getCell(8);
        expectedResults = row.getCell(9);
        product = row.getCell(10);
        dataPlanejada = row.getCell(11);
        qtdSteps = row.getCell(12);
        complexidade = row.getCell(13);
        automatizado = row.getCell(14);
        logger.info("Dados inseridos na planilha");

        if (testPlan.getTestCase().get(i).isAutomatizado()) {

            for (int j = 0; j < testCasesAutomatizados.size(); j++) {
                if (testPlan.getTestCase().get(i).equals(testCasesAutomatizados.get(j).getTestScriptName())) {
                    existInList = true;
                }
            }
            if (!existInList) {
                testCasesAutomatizados.add(testPlan.getTestCase().get(i));
                existInList = true;
            }
        }

    }

    ExtraiPlanilha extraiPlanilha = new ExtraiPlanilha();
    extraiPlanilha.exportTStoTI(testCasesAutomatizados, sheetTI);

    logger.info("Preparando para salvar planilha");
    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    logger.info("Fim mtodo - new FileOutputStream(destinationSheet) ");
    logger.info("Tentando gravar na planilha.");
    workbook.write(fileOut);
    logger.info("Fim mtodo - workbook.write(fileOut)");
    fileOut.close();
    fileSheet.close();
    sucess = true;
    logger.info("Planilha gerada.");
    return sucess;

}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean newTsSheet(String pathSheet, String nameSheet, TestPlanTSBean testPlan) throws Exception {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();//from   w  w w  .j a  v a2  s.  c  o  m
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);
    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);

    descriptionPlan.setCellValue(testPlan.getName());
    prj.setCellValue(testPlan.getSti());
    fase.setCellValue(testPlan.getCrFase());
    testPhase.setCellValue(testPlan.getTestPhase());

    //            estilo.setDataFormat(format.getFormat(formatData));
    //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);
    for (int i = 0; i < testPlan.getTestCase().size(); i++) {
        row = sheetTS.getRow(linha);

        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);
        complexidade = row.getCell(11);

        testScriptName.setCellValue(testPlan.getTestCase().get(i).getTestScriptName());
        testScriptDescription.setCellValue(testPlan.getTestCase().get(i).getTestScriptDescription());
        stepNo.setCellValue(testPlan.getTestCase().get(i).getSTEP_NUMERO());
        stepDescription.setCellValue(testPlan.getTestCase().get(i).getStepDescription());
        expectedResults.setCellValue(testPlan.getTestCase().get(i).getExpectedResults());
        product.setCellValue(testPlan.getTestCase().get(i).getProduct());
        estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
        dataPlanejada.setCellValue(testPlan.getTestCase().get(i).getDataPlanejada());
        dataPlanejada.setCellStyle(estilo);
        complexidade.setCellValue(testPlan.getTestCase().get(i).getComplexidade());

        linha = linha + 2;

        row = sheetTS.getRow(linha);

        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);
        complexidade = row.getCell(11);

    }

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public boolean updateTsSheet(String pathSheet, String nameSheet, TesteCaseTSBean testCase)
        throws FileNotFoundException, IOException {
    boolean sucess = false;
    destinationSheet = new File(pathSheet);
    destinationSheet.mkdirs();//w w w.  j a v  a  2s .c  o  m
    destinationSheet = new File(pathSheet + "\\" + nameSheet);
    sourceStheet = new File(sheetDefault);
    copySheet(sourceStheet, destinationSheet);

    FileInputStream fileSheet = new FileInputStream(destinationSheet);

    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    XSSFDataFormat format = workbook.createDataFormat();
    XSSFCellStyle estilo = workbook.createCellStyle();
    //        String formatData = "aaaa-mm-dd\"T12:00:00-03:00\"";

    int linha = 1;

    Row row = sheetTS.getRow(linha);

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);
    Cell automatizado = row.getCell(12);

    descriptionPlan.setCellValue(testCase.getTestPlan());
    prj.setCellValue(testCase.getSTIPRJ());
    fase.setCellValue(testCase.getFASE());
    testPhase.setCellValue(testCase.getTestPhase());

    //            estilo.setDataFormat(format.getFormat(formatData));
    //            estilo.setFillBackgroundColor(HSSFColor.GREEN.index);

    row = sheetTS.getRow(linha);

    prj = row.getCell(1);
    fase = row.getCell(2);
    testPhase = row.getCell(3);
    testScriptName = row.getCell(4);
    testScriptDescription = row.getCell(5);
    stepNo = row.getCell(6);
    stepDescription = row.getCell(7);
    expectedResults = row.getCell(8);
    product = row.getCell(9);
    dataPlanejada = row.getCell(10);
    complexidade = row.getCell(11);
    automatizado = row.getCell(12);

    testScriptName.setCellValue(testCase.getTestScriptName());
    testScriptDescription.setCellValue(testCase.getTestScriptDescription());
    stepNo.setCellValue(testCase.getSTEP_NUMERO());
    stepDescription.setCellValue(testCase.getStepDescription());
    expectedResults.setCellValue(testCase.getExpectedResults());
    product.setCellValue(testCase.getProduct());
    estilo = (XSSFCellStyle) dataPlanejada.getCellStyle();
    dataPlanejada.setCellValue(testCase.getDataPlanejada());
    dataPlanejada.setCellStyle(estilo);
    complexidade.setCellValue(testCase.getComplexidade());
    automatizado.setCellValue(testCase.isAutomatizado());
    linha = linha + 2;

    FileOutputStream fileOut = new FileOutputStream(destinationSheet);
    workbook.write(fileOut);
    fileOut.close();
    fileSheet.close();
    sucess = true;

    return sucess;
}

From source file:com.accenture.ts.dao.TesteCaseTSDAO.java

public List<TesteCaseTSBean> readSheet(String pathSheetFull) throws FileNotFoundException, IOException {

    List<TesteCaseTSBean> listTS = new ArrayList<TesteCaseTSBean>();

    FileInputStream fileSheet = new FileInputStream(new File(pathSheetFull));
    XSSFWorkbook workbook = new XSSFWorkbook(fileSheet);
    XSSFSheet sheetTS = workbook.getSheetAt(0);

    int linha = 1;

    Row row = sheetTS.getRow(linha);//from   ww w  . ja v a  2  s.  co  m

    Cell descriptionPlan = row.getCell(0);
    Cell prj = row.getCell(1);
    Cell fase = row.getCell(2);
    Cell testPhase = row.getCell(3);
    Cell testScriptName = row.getCell(4);
    Cell testScriptDescription = row.getCell(5);
    Cell stepNo = row.getCell(6);
    Cell stepDescription = row.getCell(7);
    Cell expectedResults = row.getCell(8);
    Cell product = row.getCell(9);
    Cell dataPlanejada = row.getCell(10);
    Cell complexidade = row.getCell(11);
    Cell automatizado = row.getCell(12);

    String descPlan = null;
    String project = null;
    String phase = null;

    if (!testScriptName.equals("")) {
        descPlan = descriptionPlan.getStringCellValue();
        project = prj.getStringCellValue();
        phase = testPhase.getStringCellValue();
    }

    while (!testScriptName.getStringCellValue().equals("") && testScriptName != null) {

        testCase = new TesteCaseTSBean();

        testCase.setTestScriptName(testScriptName.getStringCellValue());
        testCase.setTestScriptDescription(testScriptDescription.getStringCellValue());
        testCase.setStepDescription(stepDescription.getStringCellValue());
        testCase.setExpectedResults(expectedResults.getStringCellValue());
        testCase.setProduct(product.getStringCellValue());
        //            testCase.setDataPlanejada(dataPlanejada.getDateCellValue());
        testCase.setFase(fase.getStringCellValue());
        testCase.setTestPlan(descPlan);
        testCase.setSTIPRJ(project);
        testCase.setTestPhase(phase);
        testCase.setComplexidade(complexidade.getStringCellValue());
        testCase.setAutomatizado(automatizado.getBooleanCellValue());
        listTS.add(testCase);

        linha = linha + 2;

        row = sheetTS.getRow(linha);
        descriptionPlan = row.getCell(0);
        prj = row.getCell(1);
        fase = row.getCell(2);
        testPhase = row.getCell(3);
        testScriptName = row.getCell(4);
        testScriptDescription = row.getCell(5);
        stepNo = row.getCell(6);
        stepDescription = row.getCell(7);
        expectedResults = row.getCell(8);
        product = row.getCell(9);
        dataPlanejada = row.getCell(10);
        complexidade = row.getCell(11);

    }

    return listTS;
}

From source file:com.accounting.mbeans.SmsController.java

public void handleFileUpload(FileUploadEvent event) {
    //        FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded.");
    //        FacesContext.getCurrentInstance().addMessage(null, message);
    //        System.out.println("entering into excel upload ");
    if (!PhaseId.INVOKE_APPLICATION.equals(event.getPhaseId())) {
        event.setPhaseId(PhaseId.INVOKE_APPLICATION);
        event.queue();//  w w w. ja v a  2s.  c  o  m
    } else {
        //do stuff here, #{ngoPhotoBean.description} is set
        System.out.println("smsMessage " + smsMessage);
        if (smsMessage.isEmpty() || smsMessage.length() < 5) {
            System.out.println("Please write sms message first.");
            HelperUtil.showErrorMessage("Please write sms message first.");
        } else {
            try {

                UploadedFile uploadedFile = (UploadedFile) event.getFile();
                String filename = uploadedFile.getFileName();
                //            System.out.println("file name is " + filename);
                InputStream input = uploadedFile.getInputstream();

                if (filename.endsWith("xlsx")) {

                    XSSFWorkbook wb = new XSSFWorkbook(input);
                    XSSFSheet sheet = wb.getSheetAt(0);
                    Iterator rows = sheet.rowIterator();
                    setupSmsInfoData(rows);
                } else if (filename.endsWith("xls")) {

                    HSSFWorkbook wb = new HSSFWorkbook(input);
                    HSSFSheet sheet = wb.getSheetAt(0);
                    Iterator rows = sheet.rowIterator();
                    setupSmsInfoData(rows);
                }

            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }

        checkBalance();
    }

}

From source file:com.adobe.acs.commons.data.Spreadsheet.java

License:Apache License

/**
 * Parse out the input file synchronously for easier unit test validation
 *
 * @return List of files that will be imported, including any renditions
 * @throws IOException if the file couldn't be read
 *///from w  ww.  j a  v a 2 s .co m
private void parseInputFile(InputStream file) throws IOException {

    XSSFWorkbook workbook = new XSSFWorkbook(file);

    final XSSFSheet sheet = workbook.getSheetAt(0);
    rowCount = sheet.getLastRowNum();
    final Iterator<Row> rows = sheet.rowIterator();

    Row firstRow = rows.next();
    headerRow = readRow(firstRow).stream().map(v -> v != null ? convertHeaderName(v.toString()) : null)
            .collect(Collectors.toList());
    headerTypes = readRow(firstRow).stream().map(Variant::toString)
            .collect(Collectors.toMap(this::convertHeaderName, this::detectTypeFromName, this::upgradeToArray));

    Iterable<Row> remainingRows = () -> rows;
    dataRows = StreamSupport.stream(remainingRows.spliterator(), false).map(this::buildRow)
            .filter(Optional::isPresent).map(Optional::get).collect(Collectors.toList());
}