Example usage for org.apache.poi.ss.usermodel Row getCell

List of usage examples for org.apache.poi.ss.usermodel Row getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getCell

Introduction

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

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

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

public void limpaStepsPlanilha(XSSFSheet sheet, Row row) {
    for (int cont = 0; cont < 24; cont++) {

        Cell celNomeStep = row.getCell(16);
        Cell celDescStep = row.getCell(17);
        Cell celResultadoStep = row.getCell(18);

        celNomeStep.setCellValue("");
        celDescStep.setCellValue("");
        celResultadoStep.setCellValue("");

        cont = cont + 1;//w  ww . j av  a 2  s  .  c o m

        row = sheet.getRow(cont);

    }

}

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

public void geraNovaPlanilhaTS(String dir, List<TesteCaseTSBean> listTS)
        throws FileNotFoundException, IOException {

    FileInputStream arquivo = new FileInputStream(new File(dir));
    HSSFWorkbook workbook = new HSSFWorkbook(arquivo);
    HSSFSheet sheetTS = workbook.getSheetAt(0);

    HSSFDataFormat format = workbook.createDataFormat();
    HSSFCellStyle 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);/*  ww w .ja v  a 2 s  .  c om*/
    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);

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

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

        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);

        descriptionPlan.setCellValue(listTS.get(i).getTestPlan());
        prj.setCellValue(listTS.get(i).getSTIPRJ());
        fase.setCellValue(listTS.get(i).getFASE());
        testPhase.setCellValue(listTS.get(i).getTestPhase());
        testScriptName.setCellValue(listTS.get(i).getTestScriptName());
        testScriptDescription.setCellValue(listTS.get(i).getTestScriptDescription());
        stepNo.setCellValue(listTS.get(i).getSTEP_NUMERO());
        stepDescription.setCellValue(listTS.get(i).getStepDescription());
        expectedResults.setCellValue(listTS.get(i).getExpectedResults());
        product.setCellValue(listTS.get(i).getProduct());
        dataPlanejada.setCellValue(listTS.get(i).getDataPlanejada());

        dataPlanejada.setCellStyle(estilo);

        linha = linha + 2;

    }

    FileOutputStream fileOut = new FileOutputStream(new File(dir));
    workbook.write(fileOut);
    fileOut.close();
    arquivo.close();

}

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

public int getQtdDadosConf(String diretorio) throws FileNotFoundException, IOException {

    FileInputStream arquivo;// w  w  w. j  ava 2  s  . c o m
    arquivo = new FileInputStream(new File(diretorio));
    //
    XSSFWorkbook workbook = new XSSFWorkbook(arquivo);
    //Selecionando a planilha de configuraes
    XSSFSheet sheetConf = workbook.getSheetAt(2);
    int linha = 7;
    int numCelula = 1;
    int i = 1;

    //Caontando COmplexidade
    CellReference cellReference = new CellReference("A" + i);
    Row row = sheetConf.getRow(cellReference.getRow());
    Cell cell = row.getCell(cellReference.getCol());

    while (cell != null) {
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        System.out.println(cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());
        texto1.setText("Importando itens de configurao: " + cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());

        i++;
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());

        qtdConf++;

    }

    i = 7;
    cellReference = new CellReference("A" + i);
    row = sheetConf.getRow(cellReference.getRow());
    cell = row.getCell(cellReference.getCol());
    System.out.println(cell.getStringCellValue());

    i++;
    while (cell != null) {
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        System.out.println(cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());
        texto1.setText("Importando itens de configurao: " + cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());

        i++;
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());

        qtdConf++;

    }

    //Capturando dados type
    i = 12;
    cellReference = new CellReference("A" + i);
    row = sheetConf.getRow(cellReference.getRow());
    cell = row.getCell(cellReference.getCol());
    System.out.println(cell.getStringCellValue());

    i++;
    while (cell != null) {
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        System.out.println(cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());
        texto1.setText("Importando itens de configurao: " + cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());

        i++;
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        qtdConf++;
    }

    //Capturando dados TRG
    i = 17;
    cellReference = new CellReference("A" + i);
    row = sheetConf.getRow(cellReference.getRow());
    cell = row.getCell(cellReference.getCol());
    System.out.println(cell.getStringCellValue());

    i++;
    while (cell != null) {
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        System.out.println(cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());
        texto1.setText("Importando itens de configurao: " + cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());

        i++;
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        qtdConf++;
    }

    //Capturando dados CADEIA
    i = 17;
    cellReference = new CellReference("A" + i);
    row = sheetConf.getRow(cellReference.getRow());
    cell = row.getCell(cellReference.getCol());
    System.out.println(cell.getStringCellValue());

    i++;
    while (cell != null) {
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        System.out.println(cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());
        texto1.setText("Importando itens de configurao: " + cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());

        i++;
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        qtdConf++;
    }

    //Capturando dados TP REQUISITO
    i = 32;
    cellReference = new CellReference("A" + i);
    row = sheetConf.getRow(cellReference.getRow());
    cell = row.getCell(cellReference.getCol());
    System.out.println(cell.getStringCellValue());

    i++;
    while (cell != null) {
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        System.out.println(cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());
        texto1.setText("Importando itens de configurao: " + cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());

        i++;
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        qtdConf++;
    }

    //Capturando dados criao/alterao
    i = 27;
    cellReference = new CellReference("A" + i);
    row = sheetConf.getRow(cellReference.getRow());
    cell = row.getCell(cellReference.getCol());
    System.out.println(cell.getStringCellValue());

    i++;
    while (cell != null) {
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        System.out.println(cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());
        texto1.setText("Importando itens de configurao: " + cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());

        i++;
        cellReference = new CellReference("A" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        qtdConf++;
    }

    //Capturando dados SISTEMA MASTER
    i = 1;
    cellReference = new CellReference("C" + i);
    row = sheetConf.getRow(cellReference.getRow());
    cell = row.getCell(cellReference.getCol());
    System.out.println(cell.getStringCellValue());

    i++;
    while (cell != null) {
        cellReference = new CellReference("C" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        System.out.println(cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());
        texto1.setText("Importando itens de configurao: " + cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());

        i++;
        cellReference = new CellReference("C" + i);
        row = sheetConf.getRow(cellReference.getRow());
        qtdConf++;

        if (row == null) {
            cell = null;

        } else {
            cell = row.getCell(cellReference.getCol(), row.RETURN_BLANK_AS_NULL);
        }

    }

    //Capturando dados FUNCIONALIDADE
    i = 1;
    cellReference = new CellReference("E" + i);
    row = sheetConf.getRow(cellReference.getRow());
    cell = row.getCell(cellReference.getCol());
    System.out.println(cell.getStringCellValue());

    i++;
    while (cell != null) {
        cellReference = new CellReference("E" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        System.out.println(cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());
        texto1.setText("Importando itens de configurao: " + cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());

        qtdConf++;
        i++;
        cellReference = new CellReference("E" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());

    }

    //Capturando dados CENARIO INTEGRADO
    i = 1;
    cellReference = new CellReference("G" + i);
    row = sheetConf.getRow(cellReference.getRow());
    cell = row.getCell(cellReference.getCol());
    System.out.println(cell.getStringCellValue());

    i++;
    while (cell != null) {
        cellReference = new CellReference("G" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        System.out.println(cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());
        texto1.setText("Importando itens de configurao: " + cell.getStringCellValue());
        texto1.paintAll(texto1.getGraphics());

        i++;
        cellReference = new CellReference("G" + i);
        row = sheetConf.getRow(cellReference.getRow());
        cell = row.getCell(cellReference.getCol());
        qtdConf++;
    }

    return qtdConf;
}

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

public List<StepPadrao> getStepPadraoPlanilha(String planilha)
        throws SQLException, ClassNotFoundException, FileNotFoundException, IOException {
    int linha = 1;
    List<StepPadrao> listSp = new ArrayList<StepPadrao>();

    // fazendo uma instacia do banco
    ManipulaDadosSQLite bdLite = 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 segunda planilha
    XSSFSheet aba = workbook.getSheetAt(1);
    //Selecionando a segunda linha (1)
    Row row = aba.getRow(linha);
    //selecionando a celula A2
    Cell celVersao = row.getCell(4);

    //Selecionando a segunda linha (1)
    row = aba.getRow(linha);/*from   w w  w. jav a2  s.c o m*/
    //selecionando a celula A2(descrio)
    Cell celDesc = row.getCell(0);
    //selecionando a celula A2(Resultado esperado)
    Cell celResultado = row.getCell(1);
    //selecionando a celula A2(tipo)
    Cell celTipo = row.getCell(2);
    //selecionando a celula A2(sistema)
    Cell celSistema = row.getCell(3);

    //loop para capturar todos os steps padrao da planilha
    while (celDesc != null) {
        StepPadrao sp = new StepPadrao();
        row = aba.getRow(linha);
        celDesc = row.getCell(0);
        celResultado = row.getCell(1);
        celTipo = row.getCell(2);
        celSistema = row.getCell(3);
        celVersao = row.getCell(4);

        sp.setDescStep(celDesc.getStringCellValue());
        sp.setResultadoStep(celResultado.getStringCellValue());
        sp.setTipoStepPadrao(celTipo.getStringCellValue());
        sp.setSistema(celSistema.getStringCellValue());
        sp.setVersao(celVersao.getNumericCellValue());
        linha++;
        listSp.add(sp);
        row = aba.getRow(linha);
        celDesc = row.getCell(0);
        celResultado = row.getCell(1);
        celTipo = row.getCell(2);
        celSistema = row.getCell(3);
        celVersao = row.getCell(4);
    }

    return listSp;
}

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
 *//*  w  w w. j a  v a2s  . com*/
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  ww  w .j  a  v a 2s . com
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 ww.j  a  v a2 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();/*  w w w .  jav a 2 s.  c  o  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 .j  ava2  s . com*/
    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();//w ww . j a  va2s  . co 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;
}