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

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

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

Introduction

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

Prototype

void setCellFormula(String formula) throws FormulaParseException, IllegalStateException;

Source Link

Document

Sets formula for this cell.

Usage

From source file:co.turnus.analysis.data.bottlenecks.io.XlsAlgoBottlenecksDataWriter.java

License:Open Source License

private void writeActionActorClassTable(HSSFWorkbook workbook, HotspotsDataAnalyser analyzer) {
    HSSFSheet sheet = workbook.createSheet("<Action, ActorClass>");
    // Action Actor Class Results
    Cell cell = sheet.createRow(0).createCell(0);
    HSSFRichTextString title = new HSSFRichTextString("Action and Actor Class Results");
    title.applyFont(titleFont);//from ww  w  . j a v a 2s.c  om
    cell.setCellValue(title);

    Row row = sheet.createRow(1);
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));
    row.createCell(0).setCellValue("Action");
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1));
    row.createCell(1).setCellValue("Actor Class");

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 7));
    row.createCell(2).setCellValue("Total Executions");
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 8, 12));
    row.createCell(8).setCellValue("Non Deferrable Executions");
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 13, 17));
    row.createCell(13).setCellValue("Critical Executions");

    row = sheet.createRow(2);
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 3));
    row.createCell(2).setCellValue("Executions");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 6));
    row.createCell(4).setCellValue("Clock Cycles");
    row.createCell(7).setCellValue("Slack");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 8, 9));
    row.createCell(8).setCellValue("Executions");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 10, 12));
    row.createCell(10).setCellValue("Clock Cycles");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 13, 14));
    row.createCell(13).setCellValue("Executions");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 15, 17));
    row.createCell(15).setCellValue("Clock Cycles");

    row = sheet.createRow(3);
    row.createCell(2).setCellValue("%");
    row.createCell(3).setCellValue("abs");
    row.createCell(4).setCellValue("%");
    row.createCell(5).setCellValue("mean");
    row.createCell(6).setCellValue("variance");
    row.createCell(7).setCellValue("min");
    row.createCell(8).setCellValue("%");
    row.createCell(9).setCellValue("abs");
    row.createCell(10).setCellValue("%");
    row.createCell(11).setCellValue("mean");
    row.createCell(12).setCellValue("variance");
    row.createCell(13).setCellValue("%");
    row.createCell(14).setCellValue("abs");
    row.createCell(15).setCellValue("%");
    row.createCell(16).setCellValue("mean");
    row.createCell(17).setCellValue("variance");

    Table<ActorClass, Action, ExtendExecData> cTable = analyzer.getSumDataTable(ActorClass.class,
            Key.CRITICAL_CLOCKCYCLES, Order.DECREASING);

    // row index
    int rowi = 4;
    for (Table.Cell<ActorClass, Action, ExtendExecData> entry : cTable.cellSet()) {

        row = sheet.createRow(rowi);
        rowi++;

        row.createCell(0).setCellValue(entry.getColumnKey().getId());
        row.createCell(1).setCellValue(entry.getRowKey().getName());

        ExtendExecData exec = entry.getValue();
        cell = row.createCell(2);
        cell.setCellFormula("D" + rowi + "/Summary!A14*100");
        row.createCell(3).setCellValue(exec.getTotalExec().getExecutions());
        cell = row.createCell(4);
        cell.setCellFormula("F" + rowi + "/Summary!B14*100");
        row.createCell(5).setCellValue(exec.getTotalExec().getClockCyclesMean());
        row.createCell(6).setCellValue(exec.getTotalExec().getClockCyclesVariance());

        double slack = exec.getSlack().getMin();
        slack = slack > AnalysisUtil.PRECISION_EPS ? slack : 0.0;
        row.createCell(7).setCellValue(slack);
        cell = row.createCell(8);
        cell.setCellFormula("J" + rowi + "/Summary!E14*100");
        row.createCell(9).setCellValue(exec.getNotDeferrableExec().getExecutions());
        cell = row.createCell(10);
        cell.setCellFormula("L" + rowi + "/Summary!G14*100");
        row.createCell(11).setCellValue(exec.getNotDeferrableExec().getClockCyclesMean());
        row.createCell(12).setCellValue(exec.getNotDeferrableExec().getClockCyclesVariance());
        cell = row.createCell(13);
        cell.setCellFormula("O" + rowi + "/Summary!J14*100");
        row.createCell(14).setCellValue(exec.getCriticalExec().getExecutions());
        cell = row.createCell(15);
        cell.setCellFormula("Q" + rowi + "/Summary!L14*100");
        row.createCell(16).setCellValue(exec.getCriticalExec().getClockCyclesMean());
        row.createCell(17).setCellValue(exec.getCriticalExec().getClockCyclesVariance());
    }
}

From source file:co.turnus.analysis.data.bottlenecks.io.XlsAlgoBottlenecksDataWriter.java

License:Open Source License

private void writeActionActorTable(HSSFWorkbook workbook, HotspotsDataAnalyser analyzer) {
    HSSFSheet sheet = workbook.createSheet("<Action, Actor>");
    // Action Actor Results
    Cell cell = sheet.createRow(0).createCell(0);
    HSSFRichTextString title = new HSSFRichTextString("Action and Actor Results");
    title.applyFont(titleFont);/*from ww  w  .  j  av a  2s.  co  m*/
    cell.setCellValue(title);
    Row row = sheet.createRow(1);
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));
    row.createCell(0).setCellValue("Action");
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1));
    row.createCell(1).setCellValue("Actor");

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 9));
    row.createCell(2).setCellValue("Total Executions");
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 14));
    row.createCell(10).setCellValue("Non Deferrable Executions");
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 15, 19));
    row.createCell(15).setCellValue("Critical Executions");

    row = sheet.createRow(2);
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 3));
    row.createCell(2).setCellValue("Executions");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 6));
    row.createCell(4).setCellValue("Clock Cycles");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 7, 9));
    row.createCell(7).setCellValue("Slack");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 10, 11));
    row.createCell(10).setCellValue("Executions");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 12, 14));
    row.createCell(12).setCellValue("Clock Cycles");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 15, 16));
    row.createCell(15).setCellValue("Executions");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 17, 19));
    row.createCell(17).setCellValue("Clock Cycles");

    row = sheet.createRow(3);
    row.createCell(2).setCellValue("%");
    row.createCell(3).setCellValue("abs");
    row.createCell(4).setCellValue("%");
    row.createCell(5).setCellValue("mean");
    row.createCell(6).setCellValue("variance");
    row.createCell(7).setCellValue("min");
    row.createCell(8).setCellValue("average");
    row.createCell(9).setCellValue("variance");
    row.createCell(10).setCellValue("%");
    row.createCell(11).setCellValue("abs");
    row.createCell(12).setCellValue("%");
    row.createCell(13).setCellValue("mean");
    row.createCell(14).setCellValue("variance");
    row.createCell(15).setCellValue("%");
    row.createCell(16).setCellValue("abs");
    row.createCell(17).setCellValue("%");
    row.createCell(18).setCellValue("mean");
    row.createCell(19).setCellValue("variance");

    Table<Actor, Action, ExtendExecData> aTable = analyzer.getSumDataTable(Actor.class,
            Key.CRITICAL_CLOCKCYCLES, Order.DECREASING);

    // row index
    int rowi = 4;
    for (Table.Cell<Actor, Action, ExtendExecData> entry : aTable.cellSet()) {
        row = sheet.createRow(rowi);
        rowi++;

        row.createCell(0).setCellValue(entry.getColumnKey().getId());
        row.createCell(1).setCellValue(entry.getRowKey().getId());

        ExtendExecData exec = entry.getValue();
        cell = row.createCell(2);
        cell.setCellFormula("D" + rowi + "/Summary!A14*100");
        row.createCell(3).setCellValue(exec.getTotalExec().getExecutions());
        cell = row.createCell(4);
        cell.setCellFormula("F" + rowi + "/Summary!B14*100");
        row.createCell(5).setCellValue(exec.getTotalExec().getClockCyclesMean());
        row.createCell(6).setCellValue(exec.getTotalExec().getClockCyclesVariance());

        double slack = exec.getSlack().getMin();
        slack = slack > AnalysisUtil.PRECISION_EPS ? slack : 0.0;
        row.createCell(7).setCellValue(slack);
        double slackAvg = exec.getSlack().getMean();
        slackAvg = slackAvg > AnalysisUtil.PRECISION_EPS ? slackAvg : 0.0;
        row.createCell(8).setCellValue(slackAvg);
        double slackVar = exec.getSlack().getVariance();
        slackVar = slackVar > AnalysisUtil.PRECISION_EPS ? slackVar : 0.0;
        row.createCell(9).setCellValue(slackVar);
        cell = row.createCell(10);
        cell.setCellFormula("L" + rowi + "/Summary!E14*100");
        row.createCell(11).setCellValue(exec.getNotDeferrableExec().getExecutions());
        cell = row.createCell(12);
        cell.setCellFormula("N" + rowi + "/Summary!G14*100");
        row.createCell(13).setCellValue(exec.getNotDeferrableExec().getClockCyclesMean());
        row.createCell(14).setCellValue(exec.getNotDeferrableExec().getClockCyclesVariance());
        cell = row.createCell(15);
        cell.setCellFormula("Q" + rowi + "/Summary!J14*100");
        row.createCell(16).setCellValue(exec.getCriticalExec().getExecutions());
        cell = row.createCell(17);
        cell.setCellFormula("S" + rowi + "/Summary!L14*100");
        row.createCell(18).setCellValue(exec.getCriticalExec().getClockCyclesMean());
        row.createCell(19).setCellValue(exec.getCriticalExec().getClockCyclesVariance());

    }

}

From source file:co.turnus.analysis.data.bottlenecks.io.XlsAlgoBottlenecksDataWriter.java

License:Open Source License

private void writeActorClassesTable(HSSFWorkbook workbook, HotspotsDataAnalyser analyzer) {
    HSSFSheet sheet = workbook.createSheet("ActorClasses");

    // Actor classes Results
    Cell cell = sheet.createRow(0).createCell(0);
    HSSFRichTextString title = new HSSFRichTextString("ActorClasses Results");
    title.applyFont(titleFont);/*from w ww  . j  av a  2 s .  c om*/
    cell.setCellValue(title);

    Row row = sheet.createRow(1);
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));
    row.createCell(0).setCellValue("Class");
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1));
    row.createCell(1).setCellValue("File");
    sheet.addMergedRegion(new CellRangeAddress(1, 2, 2, 3));
    row.createCell(2).setCellValue("Version");

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 8));
    row.createCell(4).setCellValue("Total Executions");
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 9, 13));
    row.createCell(9).setCellValue("Non Deferrable Executions");
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 14, 18));
    row.createCell(14).setCellValue("Critical Executions");

    row = sheet.createRow(2);
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 5));
    row.createCell(4).setCellValue("Executions");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 6, 8));
    row.createCell(6).setCellValue("Clock Cycles");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 10));
    row.createCell(9).setCellValue("Executions");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 11, 13));
    row.createCell(11).setCellValue("Clock Cycles");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 14, 15));
    row.createCell(14).setCellValue("Executions");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 16, 18));
    row.createCell(16).setCellValue("Clock Cycles");

    row = sheet.createRow(3);
    row.createCell(2).setCellValue("ID");
    row.createCell(3).setCellValue("Last Modification");
    row.createCell(4).setCellValue("%");
    row.createCell(5).setCellValue("abs");
    row.createCell(6).setCellValue("%");
    row.createCell(7).setCellValue("mean");
    row.createCell(8).setCellValue("variance");
    row.createCell(9).setCellValue("%");
    row.createCell(10).setCellValue("abs");
    row.createCell(11).setCellValue("%");
    row.createCell(12).setCellValue("mean");
    row.createCell(13).setCellValue("variance");
    row.createCell(14).setCellValue("%");
    row.createCell(15).setCellValue("abs");
    row.createCell(16).setCellValue("%");
    row.createCell(17).setCellValue("mean");
    row.createCell(18).setCellValue("variance");

    Map<ActorClass, ExtendExecData> cMap = analyzer.getSumDataMap(ActorClass.class, Key.CRITICAL_CLOCKCYCLES,
            Order.DECREASING);

    // row index
    int rowi = 4;
    for (Entry<ActorClass, ExtendExecData> entry : cMap.entrySet()) {
        ActorClass clazz = entry.getKey();
        ExtendExecData exec = entry.getValue();

        row = sheet.createRow(rowi);
        rowi++;

        row.createCell(0).setCellValue(clazz.getName());
        row.createCell(1).setCellValue(clazz.getSourceFile());
        row.createCell(2).setCellValue(clazz.getVersion().getId());
        cell = row.createCell(3);
        cell.setCellValue(clazz.getVersion().getLastModificationDate());
        cell.setCellStyle(cellStyle);
        cell = row.createCell(4);
        cell.setCellFormula("F" + rowi + "/Summary!A14*100");
        row.createCell(5).setCellValue(exec.getTotalExec().getExecutions());
        cell = row.createCell(6);
        cell.setCellFormula("H" + rowi + "/Summary!B14*100");
        row.createCell(7).setCellValue(exec.getTotalExec().getClockCyclesMean());
        row.createCell(8).setCellValue(exec.getTotalExec().getClockCyclesVariance());
        cell = row.createCell(9);
        cell.setCellFormula("K" + rowi + "/Summary!E14*100");
        row.createCell(10).setCellValue(exec.getNotDeferrableExec().getExecutions());
        cell = row.createCell(11);
        cell.setCellFormula("M" + rowi + "/Summary!G14*100");
        row.createCell(12).setCellValue(exec.getNotDeferrableExec().getClockCyclesMean());
        row.createCell(13).setCellValue(exec.getNotDeferrableExec().getClockCyclesVariance());
        cell = row.createCell(14);
        cell.setCellFormula("P" + rowi + "/Summary!J14*100");
        row.createCell(15).setCellValue(exec.getCriticalExec().getExecutions());
        cell = row.createCell(16);
        cell.setCellFormula("R" + rowi + "/Summary!L14*100");
        row.createCell(17).setCellValue(exec.getCriticalExec().getClockCyclesMean());
        row.createCell(18).setCellValue(exec.getCriticalExec().getClockCyclesVariance());

    }

}

From source file:co.turnus.analysis.data.bottlenecks.io.XlsAlgoBottlenecksDataWriter.java

License:Open Source License

private void writeActorsTable(HSSFWorkbook workbook, HotspotsDataAnalyser analyzer) {
    HSSFSheet sheet = workbook.createSheet("Actors");

    // Actors Results
    Cell cell = sheet.createRow(0).createCell(0);
    HSSFRichTextString title = new HSSFRichTextString("Actors Results");
    title.applyFont(titleFont);/*  w w w  .  jav  a  2  s  . c  o  m*/
    cell.setCellValue(title);

    Row row = sheet.createRow(1);
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));
    row.createCell(0).setCellValue("Actor");
    sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1));
    row.createCell(1).setCellValue("Class");

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 6));
    row.createCell(2).setCellValue("Total Executions");
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 7, 11));
    row.createCell(7).setCellValue("Non Deferrable Executions");
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 12, 16));
    row.createCell(12).setCellValue("Critical Executions");

    row = sheet.createRow(2);
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 3));
    row.createCell(2).setCellValue("Executions");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 4, 6));
    row.createCell(4).setCellValue("Clock Cycles");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 7, 8));
    row.createCell(7).setCellValue("Executions");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 11));
    row.createCell(9).setCellValue("Clock Cycles");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 12, 13));
    row.createCell(12).setCellValue("Executions");
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 14, 16));
    row.createCell(14).setCellValue("Clock Cycles");

    row = sheet.createRow(3);
    row.createCell(2).setCellValue("%");
    row.createCell(3).setCellValue("abs");
    row.createCell(4).setCellValue("%");
    row.createCell(5).setCellValue("mean");
    row.createCell(6).setCellValue("variance");
    row.createCell(7).setCellValue("%");
    row.createCell(8).setCellValue("abs");
    row.createCell(9).setCellValue("%");
    row.createCell(10).setCellValue("mean");
    row.createCell(11).setCellValue("variance");
    row.createCell(12).setCellValue("%");
    row.createCell(13).setCellValue("abs");
    row.createCell(14).setCellValue("%");
    row.createCell(15).setCellValue("mean");
    row.createCell(16).setCellValue("variance");

    Map<Actor, ExtendExecData> aMap = analyzer.getSumDataMap(Actor.class, Key.CRITICAL_CLOCKCYCLES,
            Order.DECREASING);

    // row index
    int rowi = 4;
    for (Entry<Actor, ExtendExecData> entry : aMap.entrySet()) {
        Actor actor = entry.getKey();
        ExtendExecData exec = entry.getValue();

        row = sheet.createRow(rowi);
        rowi++;

        row.createCell(0).setCellValue(actor.getId());
        row.createCell(1).setCellValue(actor.getActorClass().getName());
        cell = row.createCell(2);
        cell.setCellFormula("D" + rowi + "/Summary!A14*100");
        row.createCell(3).setCellValue(exec.getTotalExec().getExecutions());
        cell = row.createCell(4);
        cell.setCellFormula("F" + rowi + "/Summary!B14*100");
        row.createCell(5).setCellValue(exec.getTotalExec().getClockCyclesMean());
        row.createCell(6).setCellValue(exec.getTotalExec().getClockCyclesVariance());
        cell = row.createCell(7);
        cell.setCellFormula("I" + rowi + "/Summary!E14*100");
        row.createCell(8).setCellValue(exec.getNotDeferrableExec().getExecutions());
        cell = row.createCell(9);
        cell.setCellFormula("K" + rowi + "/Summary!G14*100");
        row.createCell(10).setCellValue(exec.getNotDeferrableExec().getClockCyclesMean());
        row.createCell(11).setCellValue(exec.getNotDeferrableExec().getClockCyclesVariance());
        cell = row.createCell(12);
        cell.setCellFormula("N" + rowi + "/Summary!J14*100");
        row.createCell(13).setCellValue(exec.getCriticalExec().getExecutions());
        cell = row.createCell(14);
        cell.setCellFormula("P" + rowi + "/Summary!L14*100");
        row.createCell(15).setCellValue(exec.getCriticalExec().getClockCyclesMean());
        row.createCell(16).setCellValue(exec.getCriticalExec().getClockCyclesVariance());

    }

}

From source file:co.turnus.analysis.data.bottlenecks.io.XlsAlgoBottlenecksDataWriter.java

License:Open Source License

private void writeSummary(HSSFWorkbook workbook, Network network, HotspotsDataAnalyser analyzer) {
    HSSFSheet sheet = workbook.createSheet("Summary");

    // Network Results
    Cell cell = sheet.createRow(0).createCell(0);
    HSSFRichTextString title = new HSSFRichTextString("Hotspots and Algorithmic Bottlenecks Report Summary");
    title.applyFont(titleFont);/*from  www  .j  a  v a2  s .c o  m*/
    cell.setCellValue(title);

    // network name
    Row row = sheet.createRow(2);
    row.createCell(0).setCellValue("Network");
    row.createCell(1).setCellValue(network.getName());

    // network cal project
    row = sheet.createRow(3);
    row.createCell(0).setCellValue("CAL Project");
    row.createCell(1).setCellValue(network.getCalProject());

    // network source file
    row = sheet.createRow(4);
    row.createCell(0).setCellValue("Source File");
    row.createCell(1).setCellValue(network.getSourceFile());

    // network version
    sheet.addMergedRegion(new CellRangeAddress(5, 7, 0, 0));
    row = sheet.createRow(5);
    row.createCell(0).setCellValue("Version");
    row.createCell(1).setCellValue("Id");
    row.createCell(2).setCellValue(network.getVersion().getId());
    row = sheet.createRow(6);
    row.createCell(1).setCellValue("Last Modification Date");
    cell = row.createCell(2);
    cell.setCellValue(network.getVersion().getLastModificationDate());
    cell.setCellStyle(cellStyle);
    row = sheet.createRow(7);
    row.createCell(1).setCellValue("Versioning Date");
    cell = row.createCell(2);
    cell.setCellValue(network.getVersion().getVersioningDate());
    cell.setCellStyle(cellStyle);

    // [total | non deferrable | critical]
    row = sheet.createRow(10);
    sheet.addMergedRegion(new CellRangeAddress(10, 10, 0, 2));
    cell = row.createCell(0);
    cell.setCellValue("Total Executions");

    sheet.addMergedRegion(new CellRangeAddress(10, 10, 3, 7));
    cell = row.createCell(3);
    cell.setCellValue("Non Deferrable Executions");

    sheet.addMergedRegion(new CellRangeAddress(10, 10, 8, 12));
    cell = row.createCell(8);
    cell.setCellValue("Critical Executions");

    // [Executions | Workload | slack || Executions | workload || Executions
    // | workload ]
    row = sheet.createRow(11);
    sheet.addMergedRegion(new CellRangeAddress(11, 12, 0, 0));
    cell = row.createCell(0);
    cell.setCellValue("Executions");

    sheet.addMergedRegion(new CellRangeAddress(11, 11, 1, 2));
    cell = row.createCell(1);
    cell.setCellValue("Clock Cycles");

    sheet.addMergedRegion(new CellRangeAddress(11, 11, 3, 4));
    cell = row.createCell(3);
    cell.setCellValue("Executions");

    sheet.addMergedRegion(new CellRangeAddress(11, 11, 5, 7));
    cell = row.createCell(5);
    cell.setCellValue("Clock Cycles");

    sheet.addMergedRegion(new CellRangeAddress(11, 11, 8, 9));
    cell = row.createCell(8);
    cell.setCellValue("Executions");

    sheet.addMergedRegion(new CellRangeAddress(11, 11, 10, 12));
    cell = row.createCell(10);
    cell.setCellValue("Clock Cycles");

    // [* | mean , variance, % ... ]
    row = sheet.createRow(12);
    row.createCell(1).setCellValue("mean");
    row.createCell(2).setCellValue("variance");
    row.createCell(3).setCellValue("%");
    row.createCell(4).setCellValue("abs");
    row.createCell(5).setCellValue("%");
    row.createCell(6).setCellValue("mean");
    row.createCell(7).setCellValue("variance");
    row.createCell(8).setCellValue("%");
    row.createCell(9).setCellValue("abs");
    row.createCell(10).setCellValue("%");
    row.createCell(11).setCellValue("mean");
    row.createCell(12).setCellValue("variance");

    // write down data
    ExtendExecData sumData = analyzer.getSumData();
    row = sheet.createRow(13);

    row.createCell(0).setCellValue(sumData.getTotalExec().getExecutions());
    row.createCell(1).setCellValue(sumData.getTotalExec().getClockCyclesMean());
    row.createCell(2).setCellValue(sumData.getTotalExec().getClockCyclesVariance());
    cell = row.createCell(3);
    cell.setCellFormula("E14/A14*100");
    row.createCell(4).setCellValue(sumData.getNotDeferrableExec().getExecutions());
    cell = row.createCell(5);
    cell.setCellFormula("G14/B14*100");
    row.createCell(6).setCellValue(sumData.getNotDeferrableExec().getClockCyclesMean());
    row.createCell(7).setCellValue(sumData.getNotDeferrableExec().getClockCyclesVariance());
    cell = row.createCell(8);
    cell.setCellFormula("J14/A14*100");
    row.createCell(9).setCellValue(sumData.getCriticalExec().getExecutions());
    cell = row.createCell(10);
    cell.setCellFormula("L14/B14*100");
    row.createCell(11).setCellValue(sumData.getCriticalExec().getClockCyclesMean());
    row.createCell(12).setCellValue(sumData.getCriticalExec().getClockCyclesVariance());
}

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

public static void gravaCTPlanilha(List<Plano> plano, String panilha, int linhaCelula)
        throws FileNotFoundException, IOException, InvalidFormatException, SQLException,
        ClassNotFoundException {//  ww  w . j a  v a 2s. co  m
    ManipulaDadosSQLite banco = new ManipulaDadosSQLite();
    //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
    XSSFWorkbook workbook = new XSSFWorkbook(arquivo);

    XSSFSheet sheetPlano = workbook.getSheetAt(1);
    String cadeia = "B", segmento = "C", produto = "D", funcionalidade = "E", cenarioItegrado = "F",
            sistemaMaster = "G", sistemasEnvolvidos = "H", fornecedor = "I", tpRequisito = "J", requisito = "K",
            cenarioTeste = "L", casoTeste = "M", descricao = "N", nomeStep = "P", descricaoStep = "Q",
            resultadoEsperado = "R", cenarioAuto = "U", type = "V", trg = "W", subject = "X", criacao = "Y";
    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();

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

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

        celStep = row.getCell(16);

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

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

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

        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.get(i).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(IndexedColors.YELLOW.getIndex());
                //                    
                //                    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.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  ww  w . j a v  a 2s . com
    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 *//  w w w.  j av  a2 s  .co m
 *
 * @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.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

protected Cell setFormula(Sheet sheet, int row, int col, String text, Style style) {
    Row r = sheet.getRow(row);/*from   w w w. j  av  a2 s.  c  o m*/
    if (r == null)
        r = sheet.createRow(row);
    Cell c = r.getCell(col);
    if (c == null)
        c = r.createCell(col);
    c.setCellStyle(styles.get(style));
    c.setCellType(Cell.CELL_TYPE_STRING);
    try {
        c.setCellFormula(text);
    } catch (Exception e) {
        e.printStackTrace();
        c.setCellValue("Err. " + e.getMessage());
    }
    return c;
}

From source file:com.b510.excel.client.BusinessPlan.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;// w ww .j a va2  s .co m

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Business Plan");

    //turn off gridlines
    sheet.setDisplayGridlines(false);
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    //the following three statements are required only for HSSF
    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    //the header row: centered text in 48pt font
    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    for (int i = 0; i < titles.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(styles.get("header"));
    }
    //columns for 11 weeks starting from 9-Jul
    Calendar calendar = Calendar.getInstance();
    int year = calendar.get(Calendar.YEAR);

    calendar.setTime(fmt.parse("9-Jul"));
    calendar.set(Calendar.YEAR, year);
    for (int i = 0; i < 11; i++) {
        Cell cell = headerRow.createCell(titles.length + i);
        cell.setCellValue(calendar);
        cell.setCellStyle(styles.get("header_date"));
        calendar.roll(Calendar.WEEK_OF_YEAR, true);
    }
    //freeze the first row
    sheet.createFreezePane(0, 1);

    Row row;
    Cell cell;
    int rownum = 1;
    for (int i = 0; i < data.length; i++, rownum++) {
        row = sheet.createRow(rownum);
        if (data[i] == null)
            continue;

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell(j);
            String styleName;
            boolean isHeader = i == 0 || data[i - 1] == null;
            switch (j) {
            case 0:
                if (isHeader) {
                    styleName = "cell_b";
                    cell.setCellValue(Double.parseDouble(data[i][j]));
                } else {
                    styleName = "cell_normal";
                    cell.setCellValue(data[i][j]);
                }
                break;
            case 1:
                if (isHeader) {
                    styleName = i == 0 ? "cell_h" : "cell_bb";
                } else {
                    styleName = "cell_indented";
                }
                cell.setCellValue(data[i][j]);
                break;
            case 2:
                styleName = isHeader ? "cell_b" : "cell_normal";
                cell.setCellValue(data[i][j]);
                break;
            case 3:
                styleName = isHeader ? "cell_b_centered" : "cell_normal_centered";
                cell.setCellValue(Integer.parseInt(data[i][j]));
                break;
            case 4: {
                calendar.setTime(fmt.parse(data[i][j]));
                calendar.set(Calendar.YEAR, year);
                cell.setCellValue(calendar);
                styleName = isHeader ? "cell_b_date" : "cell_normal_date";
                break;
            }
            case 5: {
                int r = rownum + 1;
                String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")";
                cell.setCellFormula(fmla);
                styleName = isHeader ? "cell_bg" : "cell_g";
                break;
            }
            default:
                styleName = data[i][j] != null ? "cell_blue" : "cell_normal";
            }

            cell.setCellStyle(styles.get(styleName));
        }
    }

    //group rows for each phase, row numbers are 0-based
    sheet.groupRow(4, 6);
    sheet.groupRow(9, 13);
    sheet.groupRow(16, 18);

    //set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 256 * 6);
    sheet.setColumnWidth(1, 256 * 33);
    sheet.setColumnWidth(2, 256 * 20);
    sheet.setZoom(3, 4);

    // Write the output to a file
    String file = "businessplan.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}