Example usage for org.apache.poi.ss.usermodel Cell getCellFormula

List of usage examples for org.apache.poi.ss.usermodel Cell getCellFormula

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getCellFormula

Introduction

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

Prototype

String getCellFormula();

Source Link

Document

Return a formula for the cell, for example, SUM(C4:E4)

Usage

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

public void exportTStoTI(List<TesteCaseTSBean> testCases, String planilha) throws FileNotFoundException,
        IOException, InvalidFormatException, SQLException, ClassNotFoundException {

    copySheet(new File("C:\\FastPlan\\sheets\\TI.xlsx"), new File(planilha));

    //recebe a planilha e atribui a variavel arquivo
    FileInputStream arquivo = new FileInputStream(new File(planilha));
    System.out.println(planilha);
    //instacia um workbook passando arquivo como paramentro
    XSSFWorkbook workbook = new XSSFWorkbook(arquivo);

    XSSFSheet sheetPlano = workbook.getSheetAt(1);

    //     Row row = sheetPlano.getRow(cellReference.getRow());
    //     Cell cell = row.getCell(cellReference.getCol());

    int tamanhoLista = testCases.size();
    int numeroCelula = 8;

    int linha = 7;
    int celula = 12;

    Row row = sheetPlano.getRow(linha);//from   w  w w .j a  v  a2  s .  co  m
    Cell cell;//= row.getCell(celula);
    Cell celCadeia = row.getCell(1);
    Cell celSegmento = row.getCell(2);
    Cell celProduto = row.getCell(3);
    Cell celFuncionalidade = row.getCell(4);
    Cell celCenarioIntegracao = row.getCell(5);
    Cell celSistemaMaster = row.getCell(6);
    Cell celSistemaEnvolvidos = row.getCell(7);
    Cell celFornecedor = row.getCell(8);
    Cell celTpRequisito = row.getCell(9);
    Cell celRequisito = row.getCell(10);
    Cell celCenario = row.getCell(11);
    Cell celCasoTeste = row.getCell(12);
    Cell celDescricao = row.getCell(13);
    Cell celQtdSistemas = row.getCell(19);
    Cell celCenarioAuto = row.getCell(21);
    Cell celType = row.getCell(22);
    Cell celTrg = row.getCell(23);
    Cell celSubject = row.getCell(24);
    Cell celCriacao = row.getCell(25);
    Cell celStep = row.getCell(16);

    Step steps = new Step();

    for (int i = 0; i < tamanhoLista; i++) {

        testCases.get(i)
                .setTestScriptName(testCases.get(i).getTestScriptName().replaceAll("\\d\\d.\\d\\d-", ""));

        for (int j = 0; j < testCases.get(i).getParameters().size(); j++) {

            if (testCases.get(i).getTestScriptDescription()
                    .contains(testCases.get(i).getParameters().get(j).getParameterValue())) {
                testCases.get(i)
                        .setTestScriptDescription(testCases.get(i).getTestScriptDescription().replace(
                                "<" + testCases.get(i).getParameters().get(j).getParameterValue() + ">",
                                "<<<" + testCases.get(i).getParameters().get(j).getParameterName() + ">>>"));
            }
        }

        row = sheetPlano.getRow(linha);

        celCadeia = row.getCell(1);
        celSegmento = row.getCell(2);
        celProduto = row.getCell(3);
        celFuncionalidade = row.getCell(4);
        celCenarioIntegracao = row.getCell(5);
        celSistemaMaster = row.getCell(6);
        celSistemaEnvolvidos = row.getCell(7);
        celFornecedor = row.getCell(8);
        celTpRequisito = row.getCell(9);
        celRequisito = row.getCell(10);
        celCenario = row.getCell(11);
        celCasoTeste = row.getCell(12);
        celDescricao = row.getCell(13);
        Cell celComplexidade = row.getCell(15);
        celQtdSistemas = row.getCell(19);
        Cell celQtdStep = row.getCell(20);
        celCenarioAuto = row.getCell(21);
        celType = row.getCell(22);
        celTrg = row.getCell(23);
        celSubject = row.getCell(24);
        celCriacao = row.getCell(25);

        celCadeia.setCellValue("");
        celSegmento.setCellValue("");
        celProduto.setCellValue("");
        celFuncionalidade.setCellValue("");
        celCenarioIntegracao.setCellValue("");
        celSistemaMaster.setCellValue(testCases.get(i).getProduct());
        celSistemaEnvolvidos.setCellValue(testCases.get(i).getProduct());
        celFornecedor.setCellValue("Accenture");
        celTpRequisito.setCellValue("");
        celRequisito.setCellValue("");
        celCenario.setCellValue(testCases.get(i).getTestScriptName());
        celCasoTeste.setCellValue(testCases.get(i).getTestScriptName());
        celDescricao.setCellValue(testCases.get(i).getTestScriptDescription());
        String formulaComplexibilidade = celComplexidade.getCellFormula();
        celComplexidade.setCellFormula(formulaComplexibilidade);
        celQtdSistemas.setCellValue(1);
        String formulaQtdStep = celQtdStep.getCellFormula();
        celQtdStep.setCellFormula(formulaQtdStep);
        celCenarioAuto.setCellValue("Sim");
        celType.setCellValue("Manual");
        celTrg.setCellValue("No");
        celSubject.setCellValue("");
        celCriacao.setCellValue("Criao");

        celStep = row.getCell(16);

        row = sheetPlano.getRow(linha);
        //            celCasoTeste = row.getCell(12);
        int linhaStep = linha;

        row = sheetPlano.getRow(linha);
        int tamanho = testCases.get(i).getListStep().size();
        int idTemp = 0;
        for (int cont = 0; cont < tamanho; cont++) {

            //                   if(idTemp != listPlanos.get(cont).getStep().getId()){
            //                    model.addRow(new String[]{String.valueOf(listPlanos.get(cont).getStep().getNomeStep()), listPlanos.get(cont).getStep().getDescStep(),
            //                        listPlanos.get(cont).getStep().getResultadoStep(), String.valueOf(listPlanos.get(cont).getStep().getId())});
            //                   }
            Cell celNomeStep = row.getCell(16);
            Cell celDescStep = row.getCell(17);
            Cell celResultadoStep = row.getCell(18);

            for (int j = 0; j < testCases.get(i).getParameters().size(); j++) {

                if (testCases.get(i).getListStep().get(cont).getDescStep()
                        .contains(testCases.get(i).getParameters().get(j).getParameterValue())) {
                    testCases.get(i).getListStep().get(cont)
                            .setDescStep(testCases.get(i).getListStep().get(cont).getDescStep().replace(
                                    "<" + testCases.get(i).getParameters().get(j).getParameterValue() + ">",
                                    "<<<" + testCases.get(i).getParameters().get(j).getParameterName()
                                            + ">>>"));
                }

                if (testCases.get(i).getListStep().get(cont).getResultadoStep()
                        .contains(testCases.get(i).getParameters().get(j).getParameterValue())) {
                    testCases.get(i).getListStep().get(cont).setResultadoStep(
                            testCases.get(i).getListStep().get(cont).getResultadoStep().replace(
                                    "<" + testCases.get(i).getParameters().get(j).getParameterValue() + ">",
                                    "<<<" + testCases.get(i).getParameters().get(j).getParameterName()
                                            + ">>>"));
                }

            }

            testCases.get(i).getListStep().get(cont).setNomeStep("Step " + (cont + 1));

            celNomeStep.setCellValue(testCases.get(i).getListStep().get(cont).getNomeStep());
            celDescStep.setCellValue(testCases.get(i).getListStep().get(cont).getDescStep());
            celResultadoStep.setCellValue(testCases.get(i).getListStep().get(cont).getResultadoStep());

            linhaStep = linhaStep + 1;

            row = sheetPlano.getRow(linhaStep);
            celStep = row.getCell(16);

        }

        linha = linha + 25;
        row = sheetPlano.getRow(linha);
        cell = celCadeia;

    }

    FileOutputStream fileOut = new FileOutputStream(new File(planilha));
    workbook.write(fileOut);
    fileOut.close();
}

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

/**
 * Mtodo para sobrescrever ct na planilha * com o ct existente em uma
 * determinada linha ser substituido */*from   w  ww .j a  v  a 2  s  . c om*/
 *
 * @author Raphael Coelho
 *
 * @param Plano - objeto plano
 * @param String - caminho da planilha
 * @param int - nmero da linha que est o ct na planilha
 *
 * @return void
 *
 */
public static void gravaCTPlanilha(Plano plano, String panilha, int linhaCelula) throws FileNotFoundException,
        IOException, InvalidFormatException, SQLException, ClassNotFoundException {

    /**
     * Exemplo bsico de um comentrio em JavaDoc
     */
    //recebe a planilha e atribui a variavel arquivo
    FileInputStream arquivo = new FileInputStream(new File(panilha));
    System.out.println(panilha);
    //instacia um workbook passando arquivo como paramentro
    ManipulaDadosSQLite banco = new ManipulaDadosSQLite();
    XSSFWorkbook workbook = new XSSFWorkbook(arquivo);

    XSSFSheet sheetPlano = workbook.getSheetAt(1);

    CellReference cellReference = new CellReference("B8");
    //     Row row = sheetPlano.getRow(cellReference.getRow());
    //     Cell cell = row.getCell(cellReference.getCol());

    //        int tamanhoLista = plano.size();
    int numeroCelula = 8;

    int linha = linhaCelula;
    int celula = 12;

    Row row = sheetPlano.getRow(linha);
    Cell cell;//= row.getCell(celula);
    Cell celCadeia = row.getCell(1);
    Cell celSegmento = row.getCell(2);
    Cell celProduto = row.getCell(3);
    Cell celFuncionalidade = row.getCell(4);
    Cell celCenarioIntegracao = row.getCell(5);
    Cell celSistemaMaster = row.getCell(6);
    Cell celSistemaEnvolvidos = row.getCell(7);
    Cell celFornecedor = row.getCell(8);
    Cell celTpRequisito = row.getCell(9);
    Cell celRequisito = row.getCell(10);
    Cell celCenario = row.getCell(11);
    Cell celCasoTeste = row.getCell(12);
    Cell celDescricao = row.getCell(13);
    Cell celQtdSistemas = row.getCell(19);
    Cell celCenarioAuto = row.getCell(21);
    Cell celType = row.getCell(22);
    Cell celTrg = row.getCell(23);
    Cell celSubject = row.getCell(24);
    Cell celCriacao = row.getCell(25);
    Cell celStep = row.getCell(16);

    Step steps = new Step();

    row = sheetPlano.getRow(linha);

    celCadeia = row.getCell(1);
    celSegmento = row.getCell(2);
    celProduto = row.getCell(3);
    celFuncionalidade = row.getCell(4);
    celCenarioIntegracao = row.getCell(5);
    celSistemaMaster = row.getCell(6);
    celSistemaEnvolvidos = row.getCell(7);
    celFornecedor = row.getCell(8);
    celTpRequisito = row.getCell(9);
    celRequisito = row.getCell(10);
    celCenario = row.getCell(11);
    celCasoTeste = row.getCell(12);
    celDescricao = row.getCell(13);
    Cell celComplexidade = row.getCell(15);
    celQtdSistemas = row.getCell(19);
    Cell celQtdStep = row.getCell(20);
    celCenarioAuto = row.getCell(21);
    celType = row.getCell(22);
    celTrg = row.getCell(23);
    celSubject = row.getCell(24);
    celCriacao = row.getCell(25);

    celCadeia.setCellValue(plano.getCadeia());
    celSegmento.setCellValue(plano.getSegmento());
    celProduto.setCellValue(plano.getProduto());
    celFuncionalidade.setCellValue(plano.getFuncionalidade());
    celCenarioIntegracao.setCellValue(plano.getCenarioIntegrado());
    celSistemaMaster.setCellValue(plano.getSistemaMaster());
    celSistemaEnvolvidos.setCellValue(plano.getSistemasEnvolvidos());
    celFornecedor.setCellValue(plano.getFornecedor());
    celTpRequisito.setCellValue(plano.getTpRequisito());
    celRequisito.setCellValue(plano.getRequisito());
    celCenario.setCellValue(plano.getCenarioTeste());
    celCasoTeste.setCellValue(plano.getCasoTeste());
    celDescricao.setCellValue(plano.getDescCasoTeste());
    String formulaComplexibilidade = celComplexidade.getCellFormula();
    celComplexidade.setCellFormula(formulaComplexibilidade);
    celQtdSistemas.setCellValue(plano.getQtdSistemas());
    String formulaQtdStep = celQtdStep.getCellFormula();
    celQtdStep.setCellFormula(formulaQtdStep);
    celCenarioAuto.setCellValue(plano.getCenarioAutomatizavel());
    celType.setCellValue(plano.getType());
    celTrg.setCellValue(plano.getTrg());
    celSubject.setCellValue(plano.getSubject());
    celCriacao.setCellValue(plano.getCriacaoAlteracao());

    celStep = row.getCell(16);

    row = sheetPlano.getRow(linha);
    //            celCasoTeste = row.getCell(12);
    int linhaStep = linha;

    List<Plano> listPlanos = banco.selectPlanoPorId(plano);

    List<Step> listStep = banco.getStepPorPlano(plano);

    int linhaLimpeza = linha;
    //limpa as clulas de step
    for (int cont = 0; cont <= 24; cont++) {

        Cell celNomeStep = row.getCell(16);
        Cell celDescStep = row.getCell(17);
        Cell celResultadoStep = row.getCell(18);
        String valor = null;
        celNomeStep.setCellValue(valor);
        celDescStep.setCellValue(valor);
        celResultadoStep.setCellValue(valor);

        linhaLimpeza = linhaLimpeza + 1;

        row = sheetPlano.getRow(linhaLimpeza);

    }
    //fim
    row = sheetPlano.getRow(linha);
    int tamanho = listStep.size();
    int idTemp = 0;
    for (int cont = 0; cont < tamanho; cont++) {

        //                   if(idTemp != listPlanos.get(cont).getStep().getId()){
        //                    model.addRow(new String[]{String.valueOf(listPlanos.get(cont).getStep().getNomeStep()), listPlanos.get(cont).getStep().getDescStep(),
        //                        listPlanos.get(cont).getStep().getResultadoStep(), String.valueOf(listPlanos.get(cont).getStep().getId())});
        //                   }
        Cell celNomeStep = row.getCell(16);
        Cell celDescStep = row.getCell(17);
        Cell celResultadoStep = row.getCell(18);

        celNomeStep.setCellValue(listStep.get(cont).getNomeStep());
        celDescStep.setCellValue(listStep.get(cont).getDescStep());
        celResultadoStep.setCellValue(listStep.get(cont).getResultadoStep());
        //caso o ct seja alterao pinta os steps de amarelo - inicio
        if (plano.getCriacaoAlteracao().equals("Alterao")) {
            Color color = new XSSFColor(java.awt.Color.yellow);
            //                    XSSFCellStyle style = workbook.createCellStyle();
            //                    style.setBorderTop((short) 6); // double lines border
            //                    style.setBorderBottom((short) 1); // single line border
            //                    style.setFillBackgroundColor((XSSFColor) color);
            //                    
            //                    celNomeStep.setCellStyle(style);
        }
        //fim
        linhaStep = linhaStep + 1;

        row = sheetPlano.getRow(linhaStep);
        celStep = row.getCell(16);

        idTemp = listPlanos.get(cont).getId();
    }

    linha = linha + 25;
    row = sheetPlano.getRow(linha);
    cell = celCadeia;

    FileOutputStream fileOut = new FileOutputStream(new File(panilha));
    workbook.write(fileOut);
    fileOut.close();
}

From source file:com.alibaba.ims.platform.util.ExcelUtil.java

License:Open Source License

private static String getCellValue(Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_NUMERIC:
        if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
            return DateUtil.format(cell.getDateCellValue(), "yyyy-MM-dd");
        } else {//  w  w  w.  j  ava 2  s.com
            return new DecimalFormat("0").format(cell.getNumericCellValue());
        }
    case Cell.CELL_TYPE_BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    default:
        return null;
    }
}

From source file:com.aurel.track.exchange.excel.ExcelImportBL.java

License:Open Source License

/**
 * Gets the string value of a cell//  w  w w .java 2  s  . c  o m
 * 
 * @param cell
 * @return
 */
static String getStringCellValue(Cell cell) {
    try {
        int cellType = cell.getCellType();
        switch (cellType) {
        case Cell.CELL_TYPE_BLANK:
            return "";
        case Cell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        case Cell.CELL_TYPE_ERROR:
            return String.valueOf(cell.getErrorCellValue());
        case Cell.CELL_TYPE_FORMULA:
            return cell.getCellFormula();
        case Cell.CELL_TYPE_NUMERIC:
            try {
                double doubleValue = cell.getNumericCellValue();
                int intValue = (int) doubleValue;
                double fracPart = doubleValue - intValue;
                if (Math.abs(fracPart) <= Double.MIN_VALUE) {
                    return String.valueOf(intValue);
                } else {
                    return String.valueOf(doubleValue);
                }
            } catch (Exception e) {
            }
        case Cell.CELL_TYPE_STRING:
            RichTextString richTextString = cell.getRichStringCellValue();
            if (richTextString != null) {
                return richTextString.getString();
            }
        }
    } catch (Exception e) {
        LOGGER.debug("Getting the string value failed with " + e.getMessage());
        LOGGER.debug(ExceptionUtils.getStackTrace(e));
    }
    return "";
}

From source file:com.b2international.snowowl.snomed.core.refset.automap.XlsParser.java

License:Apache License

/**
 * @param cell/* w w  w .j  a v  a2  s .  co m*/
 * @return the textual representation of the cell or empty string if the cell is empty (null)
 */
private String getStringValue(Cell cell) {
    String value = "";
    if (cell != null) {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            value = cell.getRichStringCellValue().getString();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                value = cell.getDateCellValue().toString();
            } else {
                value = Integer.toString((int) cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            value = Boolean.toString(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            value = cell.getCellFormula();
            break;
        }
    }
    return value;
}

From source file:com.b2international.snowowl.snomed.importer.net4j.SnomedSubsetImportUtil.java

License:Apache License

private String getStringValue(final Cell cell) {
    String value = "";

    //   empty cell
    if (cell == null) {
        return "";
    }/*from  w w w . j a  va  2 s .com*/

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        value = cell.getRichStringCellValue().getString();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            value = cell.getDateCellValue().toString();
        } else {
            value = Integer.toString((int) cell.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        value = Boolean.toString(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        value = cell.getCellFormula();
        break;
    }

    return value;
}

From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java

License:Apache License

public static String getCellString(Cell cell, HSSFFormulaEvaluator eval, DateFormat dfmt) {
    if (cell == null) {
        return null;
    }/*from  w  w w  .j  a  v  a  2s  .c  o  m*/
    String ret = null;
    eval.evaluate(cell);
    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_NUMERIC:
    case HSSFCell.CELL_TYPE_FORMULA: // ?
        if (isCellDateFormatted(cell)) {
            if (dfmt == null) {
                dfmt = new SimpleDateFormat("yyyy-MM-dd");
            }
            Date d = cell.getDateCellValue();
            if (d != null) {
                ret = dfmt.format(d);
            } else {
                ret = "";
            }
        } else {
            try {
                ret = "" + cell.getNumericCellValue();
            } catch (IllegalStateException ise) {
                int errVal = cell.getErrorCellValue();
                String formula = cell.getCellFormula();
                int cacheType = cell.getCachedFormulaResultType();
                throw new RuntimeException(ise.getMessage() + ": errVal=" + errVal + " formula='" + formula
                        + "' cacheType=" + cacheType);
            }
        }
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        ret = null;
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        ret = "" + cell.getBooleanCellValue();
        break;
    case HSSFCell.CELL_TYPE_STRING:
    default:
        ret = cell.getRichStringCellValue().getString();
    }
    return ret;
}

From source file:com.beyondb.io.ExcelControl.java

@Override
public Object[][] readTableContent() throws IOException, InvalidFormatException, Exception {
    try {/*from www .  j a v  a 2s .  co m*/
        //OPCPackage pkg = OPCPackage.open(file);
        //            InputStream m_InputStream = new FileInputStream(m_File);
        Sheet sheet = null;
        //            if (!m_InputStream.markSupported()) {
        //                m_InputStream = new PushbackInputStream(m_InputStream, 8);
        //            } 
        //            if (POIFSFileSystem.hasPOIFSHeader(m_InputStream)) {
        //                HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(m_InputStream);
        //                 sheet  = (Sheet)hSSFWorkbook.getSheetAt(0);
        //            
        //             } else if (POIXMLDocument.hasOOXMLHeader(m_InputStream)) {
        //                XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(OPCPackage.open(m_File));
        //               sheet  = (Sheet)xSSFWorkbook.getSheetAt(0);
        //             }
        //             else {
        //                throw new IllegalArgumentException("excel?poi??");
        //            }
        sheet = getSheet();
        if (sheet != null) {
            if (sheet.getLastRowNum() == 0) {
                throw new Exception("Excel");
            }
            //?
            m_RowNum = sheet.getLastRowNum() + 1;

            //                m_ColumnNum = sheet.getRow(0).getPhysicalNumberOfCells();
            m_ColumnNum = sheet.getRow(0).getLastCellNum();
            m_TableStr = new Object[m_RowNum][m_ColumnNum];

            for (int rindex = 0; rindex < m_RowNum; rindex++) {
                Row row = sheet.getRow(rindex);
                for (int cindex = 0; cindex < m_ColumnNum; cindex++) {
                    Cell cell = row.getCell(cindex);

                    if (cell == null) {
                        m_TableStr[rindex][cindex] = "";
                    } else {
                        String value = "";
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            //                          System.out.println(cell.getRichStringCellValue().getString());                          
                            value = cell.getRichStringCellValue().getString().replace("\n", "");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                //                                System.out.println(cell.getDateCellValue());
                                value = cell.getDateCellValue().toString();
                            } else {

                                DecimalFormat df = new DecimalFormat("#");
                                value = String.valueOf(cell.getNumericCellValue());
                                double d = cell.getNumericCellValue();
                                int dInt = (int) d;
                                BigDecimal b1 = new BigDecimal(value);
                                BigDecimal b2 = new BigDecimal(Integer.toString(dInt));
                                double dPoint = b1.subtract(b2).doubleValue();
                                if (dPoint == 0) {
                                    //?
                                    value = df.format(cell.getNumericCellValue());
                                }
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            //                            System.out.println(cell.getBooleanCellValue());
                            value = cell.getBooleanCellValue() + "";
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            //                            System.out.println(cell.getCellFormula());
                            value = cell.getCellFormula();
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            value = "";
                        default:
                            //                            System.out.println();
                            value = "";
                        }
                        m_TableStr[row.getRowNum()][cell.getColumnIndex()] = value;
                    }
                }
            }
        }

    } catch (IOException | InvalidFormatException e) {
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", e);
        throw e;

    } catch (Exception ex) {
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", ex);

        throw ex;
    } finally {
        m_InputStream.close();
    }

    return m_TableStr;
}

From source file:com.bizosys.dataservice.dao.ReadXLS.java

License:Apache License

@Override
protected List<String> populate() throws SQLException {

    checkCondition();//from  ww w  .  j  a  v  a  2  s.  c o  m

    Workbook workbook = getWorkbook();
    Sheet sheet = workbook.createSheet();

    ResultSetMetaData md = rs.getMetaData();
    int totalCol = md.getColumnCount();
    String[] cols = createLabels(md, totalCol);

    try {

        if (null != templateFile) {
            File templateFileObject = new File(templateFile);
            if (templateFileObject.exists()) {
                Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
                Sheet templatesheet = templateWorkbook.getSheetAt(0);
                Iterator<Row> rowIterator = templatesheet.iterator();

                while (rowIterator.hasNext()) {
                    Row templateRow = rowIterator.next();
                    Row row = sheet.createRow(startRowIndex++);

                    Iterator<Cell> cellIterator = templateRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell templateCell = cellIterator.next();
                        Cell cell = row.createCell(templateCell.getColumnIndex());
                        cell.setCellType(templateCell.getCellType());
                        switch (templateCell.getCellType()) {
                        case Cell.CELL_TYPE_BLANK:
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(templateCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            cell.setCellValue(templateCell.getErrorCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(templateCell.getCellFormula());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            cell.setCellValue(templateCell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(templateCell.getStringCellValue());
                            break;
                        }
                    }
                }
            } else {
                System.err.println("Can not read " + templateFileObject.getAbsolutePath());
            }

        }

        while (this.rs.next()) {
            createRecord(totalCol, cols, sheet);
        }
        workbook.write(out);
    } catch (IOException ex) {
        throw new SQLException(ex);
    }
    return null;
}

From source file:com.bizosys.dataservice.dao.WriteToXls.java

License:Apache License

public void write(List<Object[]> records) throws Exception {
    Workbook workbook = getWorkbook();//ww w  .ja va2s .  c o m
    Sheet sheet = workbook.createSheet();

    if (null != templateFile) {
        File templateFileObject = new File(templateFile);
        if (templateFileObject.exists()) {
            Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
            Sheet templatesheet = templateWorkbook.getSheetAt(0);
            Iterator<Row> rowIterator = templatesheet.iterator();

            while (rowIterator.hasNext()) {
                Row templateRow = rowIterator.next();
                Row row = sheet.createRow(startRowIndex++);

                Iterator<Cell> cellIterator = templateRow.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell templateCell = cellIterator.next();
                    Cell cell = row.createCell(templateCell.getColumnIndex());
                    cell.setCellType(templateCell.getCellType());
                    switch (templateCell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        cell.setCellValue(templateCell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cell.setCellValue(templateCell.getErrorCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cell.setCellValue(templateCell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        cell.setCellValue(templateCell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cell.setCellValue(templateCell.getStringCellValue());
                        break;
                    }
                }
            }
        } else {
            System.err.println("Can not read " + templateFileObject.getAbsolutePath());
        }
    }

    for (Object[] cols : records) {
        createRecord(cols, sheet);
    }
    workbook.write(out);

}