Example usage for org.apache.poi.ss.usermodel Workbook createDataFormat

List of usage examples for org.apache.poi.ss.usermodel Workbook createDataFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createDataFormat

Introduction

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

Prototype

DataFormat createDataFormat();

Source Link

Document

Returns the instance of DataFormat for this workbook.

Usage

From source file:ro.ldir.report.formatter.GarbageExcelFormatter.java

License:Open Source License

public final Workbook convert(Workbook wb) {
    Sheet sheet = wb.createSheet("Lista Mormane gunoi");

    Row row = sheet.createRow(0);/*  w  w  w.  j av  a  2  s .  c o m*/
    int k = 0;
    row.createCell(k).setCellValue("ID");
    k++;
    row.createCell(k).setCellValue("Jude\u0163");
    k++;
    row.createCell(k).setCellValue("Comun\u04d1");
    k++;
    row.createCell(k).setCellValue("Latitudine");
    k++;
    row.createCell(k).setCellValue("Longitudine");
    k++;
    row.createCell(k).setCellValue("Precizie GPS (metri)");
    k++;
    row.createCell(k).setCellValue("Dispersat");
    k++;
    row.createCell(k).setCellValue("Num\u04d1r saci");
    k++;

    row.createCell(k).setCellValue("Marime TrashOut (1=mic;2=medium;3=mare)");
    k++;
    row.createCell(k).setCellValue("Compozitie TrashOut");
    k++;

    row.createCell(k).setCellValue("Plastic");
    k++;
    row.createCell(k).setCellValue("Metal");
    k++;
    row.createCell(k).setCellValue("Sticl\u04d1");
    k++;
    row.createCell(k).setCellValue("Nereciclabil");
    k++;
    row.createCell(k).setCellValue("Greu de transportat");
    k++;
    row.createCell(k).setCellValue("Descriere");
    k++;
    row.createCell(k).setCellValue("Stare");
    k++;
    row.createCell(k).setCellValue("Zon\u04d1 cartare");
    k++;

    row.createCell(k).setCellValue("Numele mormanului");
    k++;
    row.createCell(k).setCellValue("Raza");
    k++;
    row.createCell(k).setCellValue("Numar de voturi");
    k++;

    row.createCell(k).setCellValue("Data introducerii");
    k++;
    row.createCell(k).setCellValue("Nominalizat pentru Votare");
    k++;
    row.createCell(k).setCellValue("Nominalizat pentru Curatare");
    k++;

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

        k = 0;
        row = sheet.createRow(i + 1);
        Garbage garbage = garbages.get(i);

        row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getGarbageId());
        k++;
        row.createCell(k).setCellValue(garbage.getCounty().getName());
        k++;
        if (garbage.getTown() != null)
            row.createCell(k).setCellValue(garbage.getTown().getName());
        k++;
        row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getY());
        k++;
        row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getX());
        k++;

        row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getAccuracy());
        k++;

        row.createCell(k, Cell.CELL_TYPE_BOOLEAN).setCellValue(garbage.isDispersed());
        k++;
        row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getBagCount());
        k++;

        row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getTrashOutSize());
        k++;

        if (garbage.getTrashOutTypes() != null)
            row.createCell(k, Cell.CELL_TYPE_STRING).setCellValue(garbage.getTrashOutTypes());
        k++;

        row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getPercentagePlastic());
        k++;
        row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getPercentageMetal());
        k++;
        row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getPercentageGlass());
        k++;
        row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getPercentageWaste());
        k++;
        row.createCell(k).setCellValue(garbage.getBigComponentsDescription());
        k++;

        if (garbage.getDescription() != null)
            row.createCell(k, Cell.CELL_TYPE_STRING)
                    .setCellValue(garbage.getDescription().replaceAll("\\r\\n|\\r|\\n", " "));
        k++;
        if (garbage.getStatus() != null)
            row.createCell(k).setCellValue(garbage.getStatus().getTranslation());
        k++;
        if (garbage.getChartedArea() != null)
            row.createCell(k).setCellValue(garbage.getChartedArea().getName());
        k++;
        try {
            if (garbage.getName() != null)
                row.createCell(k).setCellValue(garbage.getName());
        } catch (Exception e) {
            // TODO: handle exception
        }
        k++;
        try {
            row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getRadius());
        } catch (Exception e1) {
            // TODO: handle exception
        }
        k++;
        try {
            if (garbage.getVotes() != null)
                row.createCell(k, Cell.CELL_TYPE_NUMERIC).setCellValue(garbage.getVoteCount());
        } catch (Exception ee) {

        }
        k++;
        try {
            Cell cell;
            DataFormat df = wb.createDataFormat();
            CellStyle cs = wb.createCellStyle();
            cs.setDataFormat(df.getFormat("dd-mm-yyyy"));

            if (garbage.getRecordDate() != null) {
                cell = row.createCell(k, Cell.CELL_TYPE_STRING);
                cell.setCellValue(garbage.getRecordDate());
                cell.setCellStyle(cs);
            }
        } catch (Exception ee) {

        }
        k++;
        row.createCell(k, Cell.CELL_TYPE_STRING).setCellValue(garbage.isToVote());
        k++;
        row.createCell(k, Cell.CELL_TYPE_STRING).setCellValue(garbage.isToClean());
        k++;

    }
    return wb;
}

From source file:savio_estadisticas.clases.Control.Estadisticas.Table_DataBase.java

public void GenerateStatistis(Workbook libro, int total, int Ninguno) {
    Sheet estadisticas = libro.createSheet("Estadisticas");

    for (int i = 0; i < 22; i++) {
        Row fila_esta = estadisticas.createRow(i);
        for (int j = 0; j < 4; j++) {
            Cell celda_esta = fila_esta.createCell(j);
            switch (i) {
            case 0:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Recurso");
                    break;
                case 1:
                    celda_esta.setCellValue("Cursos");
                    break;
                case 2:
                    celda_esta.setCellValue("Promedio (%)");
                    break;
                case 3:
                    celda_esta.setCellValue("Total Cursos");
                    break;
                }/*from   w  w w. j a  v  a 2  s.  com*/
                break;
            case 1:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Tareas");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!D:D,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 2:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Consultas");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!E:E,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 3:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Etiquetas");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!F:F,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 4:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Foros");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!G:G,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 5:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Chats");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!H:H,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 6:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Lecciones");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!I:I,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 7:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Wikis");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!J:J,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 8:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Bases de Datos");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!K:K,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 9:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Paquetes SCORM");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!L:L,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 10:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Archivos");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!M:M,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 11:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("URLs");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!N:N,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 12:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Paginas");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!O:O,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 13:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Cuestionarios");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!P:P,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 14:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Talleres");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!Q:Q,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 15:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("VPL");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!R:R,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 16:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Libros");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!S:S,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 17:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Glosario");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!T:T,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 18:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Portafolio");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!U:U,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 19:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Innovadores");
                    break;
                case 1:
                    celda_esta.setCellFormula("COUNTIF(Tabla!V:V,\">0\")");
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            case 20:
                switch (j) {
                case 0:
                    celda_esta.setCellValue("Ninguno");
                    break;
                case 1:
                    celda_esta.setCellValue(Ninguno);
                    break;
                case 2:
                    celda_esta.setCellFormula("B" + (i + 1) + "/D2");
                    CellStyle style = libro.createCellStyle();
                    style.setDataFormat(libro.createDataFormat().getFormat("0.0%"));
                    celda_esta.setCellStyle(style);
                    break;
                case 3:
                    celda_esta.setCellValue(total);
                    break;
                }
                break;
            }
        }
    }

    //celda_esta.setCellFormula("COUNTIF(Tabla!S:S,\">0\")");
}

From source file:se.mithlond.services.content.impl.ejb.report.ExcelReportServiceBean.java

License:Apache License

/**
 * {@inheritDoc}/*from w  w w .j  av a 2s  .co  m*/
 */
@Override
@SuppressWarnings("all")
public CellStyle getCellStyle(final ExcelElement el, final Workbook workbook) {

    // Check sanity
    Validate.notNull(workbook, "workbook");
    Validate.notNull(el, "el");

    // Acquire the el and Font as expected
    final CellStyle toReturn = workbook.createCellStyle();
    final Font theFont = workbook.createFont();

    switch (el) {

    case TITLE:
        theFont.setFontHeightInPoints((short) 18);
        theFont.setBold(true);
        theFont.setColor(IndexedColors.BLUE_GREY.getIndex());

        toReturn.setAlignment(HorizontalAlignment.CENTER);
        toReturn.setVerticalAlignment(VerticalAlignment.CENTER);
        break;

    case HEADER:
        theFont.setFontHeightInPoints((short) 11);
        theFont.setColor(IndexedColors.WHITE.getIndex());

        toReturn.setAlignment(HorizontalAlignment.CENTER);
        toReturn.setVerticalAlignment(VerticalAlignment.CENTER);
        toReturn.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
        toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        toReturn.setWrapText(true);
        break;

    case CELL:

        toReturn.setAlignment(HorizontalAlignment.LEFT);
        toReturn.setWrapText(true);
        toReturn.setBorderRight(BORDER_THIN);
        toReturn.setRightBorderColor(GREY_25_PERCENT);
        toReturn.setBorderLeft(BORDER_THIN);
        toReturn.setLeftBorderColor(GREY_25_PERCENT);
        toReturn.setBorderTop(BORDER_THIN);
        toReturn.setTopBorderColor(GREY_25_PERCENT);
        toReturn.setBorderBottom(BORDER_THIN);
        toReturn.setBottomBorderColor(GREY_25_PERCENT);
        break;

    case NON_WRAPPING:
        toReturn.setAlignment(HorizontalAlignment.LEFT);
        toReturn.setWrapText(false);
        toReturn.setBorderRight(BORDER_THIN);
        toReturn.setRightBorderColor(GREY_25_PERCENT);
        toReturn.setBorderLeft(BORDER_THIN);
        toReturn.setLeftBorderColor(GREY_25_PERCENT);
        toReturn.setBorderTop(BORDER_THIN);
        toReturn.setTopBorderColor(GREY_25_PERCENT);
        toReturn.setBorderBottom(BORDER_THIN);
        toReturn.setBottomBorderColor(GREY_25_PERCENT);
        break;

    case FORMULA:
        toReturn.setAlignment(HorizontalAlignment.CENTER);
        toReturn.setVerticalAlignment(VerticalAlignment.CENTER);
        toReturn.setFillForegroundColor(GREY_25_PERCENT);
        toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        toReturn.setDataFormat(workbook.createDataFormat().getFormat("0.00"));
        break;

    case ALT_FORMULA:
        toReturn.setAlignment(HorizontalAlignment.CENTER);
        toReturn.setVerticalAlignment(VerticalAlignment.CENTER);
        toReturn.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        toReturn.setDataFormat(workbook.createDataFormat().getFormat("0.00"));
        break;

    default:
        throw new IllegalArgumentException(
                "Style [" + el.name() + "] was not defined. " + "Blame the programmer.");
    }

    // All done.
    toReturn.setFont(theFont);
    return toReturn;
}

From source file:servlet.exportScoreSheet.java

private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;//from  w  w w . ja  va 2  s.co m
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 11);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(monthFont);
    style.setWrapText(true);
    styles.put("header", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula_2", style);

    return styles;
}

From source file:test.poi.MyExcelDemo.java

License:Apache License

/**
 * Create a library of cell styles//w  w  w  . j a v  a2 s . c  om
 */
private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font headerFont = wb.createFont();
    headerFont.setFontHeightInPoints((short) 11);
    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerFont.setColor(IndexedColors.BLACK.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    //        style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());

    styles.put("header", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula_2", style);

    return styles;
}

From source file:uk.co.spudsoft.birt.emitters.excel.StyleManagerUtils.java

License:Open Source License

/**
 * Apply a BIRT number/date/time format to a POI CellStyle.
 * @param workbook//from  www . java 2s .  c  o m
 * The workbook containing the CellStyle (needed to create a new DataFormat).
 * @param birtStyle
 * The BIRT style which may contain a number format.
 * @param poiStyle
 * The CellStyle that is to receive the number format.
 */
public void applyNumberFormat(Workbook workbook, BirtStyle birtStyle, CellStyle poiStyle, Locale locale) {
    String dataFormat = null;
    String format = getNumberFormat(birtStyle);
    if (format != null) {
        log.debug("BIRT number format == ", format);
        dataFormat = poiNumberFormatFromBirt(format);
    } else {
        format = getDateTimeFormat(birtStyle);
        if (format != null) {
            log.debug("BIRT date/time format == ", format);
            dataFormat = poiDateTimeFormatFromBirt(format, locale);
        } else {
            format = getTimeFormat(birtStyle);
            if (format != null) {
                log.debug("BIRT time format == ", format);
                dataFormat = poiDateTimeFormatFromBirt(format, locale);
            } else {
                format = getDateFormat(birtStyle);
                if (format != null) {
                    log.debug("BIRT date format == ", format);
                    dataFormat = poiDateTimeFormatFromBirt(format, locale);
                }
            }
        }
    }
    if (dataFormat != null) {
        DataFormat poiFormat = workbook.createDataFormat();
        log.debug("Setting POI data format to ", dataFormat);
        poiStyle.setDataFormat(poiFormat.getFormat(dataFormat));
    }
}

From source file:vistas.reportes.procesos.rptVacacionesExcel.java

private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;//  w w  w. j a  v a 2s  .  c  om
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 11);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(monthFont);
    style.setWrapText(true);
    styles.put("header", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula_2", style);

    return styles;
}

From source file:y.graphs.XLSHelper.java

License:Open Source License

public static boolean saveElfData(String filename, ElfDb db, double sensibilita, boolean save_grafico) {
    final DateTime[] times = db.getPeriods();
    final ElfValue[][] dayvalues = db.getSampledData();
    final int[] mediane = db.getOpValues();
    final int[] maxs = db.getOpMaxDay();
    final int[] counts = db.getOpValueCount();
    final int maxi = db.getMaxidx();

    Workbook wb = null;

    try {/*from   ww w.j  ava2 s .c o  m*/
        if (Utils.abortOnExistingAndDontOverwrite(filename))
            return false;

        wb = new XSSFWorkbook();

        CreationHelper createHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet(Config.getResource("TitleStats"));

        int rown = 0;
        Row row = sheet.createRow(rown++);
        Cell cell = row.createCell(0);
        cell.setCellValue(Config.getResource("TitleDate"));
        cell = row.createCell(1);
        cell.setCellValue(Config.getResource("TitleMediana"));
        cell = row.createCell(2);
        cell.setCellValue(Config.getResource("TitleMaxM"));
        cell = row.createCell(3);
        cell.setCellValue(Config.getResource("TitleNumberOfData"));

        CellStyle dateStyle1 = wb.createCellStyle();
        dateStyle1.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy"));
        CellStyle doubleFormat1 = wb.createCellStyle();
        DataFormat format1 = wb.createDataFormat();
        doubleFormat1.setDataFormat(format1.getFormat("0.00"));

        for (int i = 0; i < mediane.length; i++) {
            row = sheet.createRow(rown++);

            cell = row.createCell(0);
            cell.setCellStyle(dateStyle1);
            cell.setCellValue(Utils.toDateString(dayvalues[i][0].getTime()));

            cell = row.createCell(1);
            cell.setCellStyle(doubleFormat1);
            cell.setCellValue(ElfValue.valueIntToDouble(mediane[i]));

            cell = row.createCell(2);
            cell.setCellStyle(doubleFormat1);
            cell.setCellValue(ElfValue.valueIntToDouble(maxs[i]));

            cell = row.createCell(3);
            cell.setCellValue(counts[i]);
        }

        // line with DataFunction max
        row = sheet.createRow(rown++);
        row = sheet.createRow(rown++);
        cell = row.createCell(0);
        cell.setCellValue(Config.getResource("MsgMax") + "(" + db.getOperationPerformed().getName() + ") - "
                + Utils.toDateString(times[maxi]));

        cell = row.createCell(1);
        cell.setCellStyle(doubleFormat1);
        cell.setCellValue(ElfValue.valueIntToDouble(mediane[maxi]));

        cell = row.createCell(2);
        cell.setCellStyle(doubleFormat1);
        cell.setCellValue(ElfValue.valueIntToDouble(maxs[maxi]));

        cell = row.createCell(3);
        cell.setCellValue(counts[maxi]);

        // line with max
        final ElfValue maxvalue = db.getSelectedElfValue(new Comparator<ElfValue>() {
            @Override
            public int compare(ElfValue o1, ElfValue o2) {
                return o1.getValue() - o2.getValue();
            }
        });
        row = sheet.createRow(rown++);
        cell = row.createCell(0);
        cell.setCellValue(Config.getResource("MsgMax") + "(" + Utils.toDateString(maxvalue.getTime()) + ")");

        cell = row.createCell(1);
        cell.setCellStyle(doubleFormat1);
        cell.setCellValue(MeasurementValue.valueIntToDouble(maxvalue.getValue()));

        cell = row.createCell(2);
        cell.setCellStyle(doubleFormat1);
        cell.setCellValue(MeasurementValue.valueIntToDouble(maxvalue.getMax()));

        cell = row.createCell(3);
        cell.setCellValue(counts[maxi]);

        // sheet containing all raw data
        Sheet sheetdata = wb.createSheet(Config.getResource("TitleSheetDatas"));
        CellStyle dateTimeStyle2 = wb.createCellStyle();
        dateTimeStyle2.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));

        CellStyle doubleFormat2 = wb.createCellStyle();
        DataFormat format2 = wb.createDataFormat();
        doubleFormat2.setDataFormat(format2.getFormat("0.00"));

        rown = 0;
        row = sheetdata.createRow(rown++);
        cell = row.createCell(0);
        cell.setCellValue(Config.getResource("TitleDate"));
        cell = row.createCell(1);
        cell.setCellValue(Config.getResource("TitleValue"));
        cell = row.createCell(2);
        cell.setCellValue(Config.getResource("TitlePeak"));
        cell = row.createCell(3);
        cell.setCellValue(Config.getResource("TitleMediana"));
        cell = row.createCell(4);
        cell.setCellValue(Config.getResource("TitleDayMax"));
        cell = row.createCell(5);
        cell.setCellValue(Config.getResource("TitleMedianaMax"));
        cell = row.createCell(6);
        cell.setCellValue(Config.getResource("TitleSens"));
        cell = row.createCell(7);
        cell.setCellValue(Config.getResource("TitleQualityTarget"));
        cell = row.createCell(8);
        cell.setCellValue(Config.getResource("TitleAttentionValue"));

        for (int i = 0; i < dayvalues.length; i++) {
            final ElfValue[] day = dayvalues[i];

            for (int k = 0; k < day.length; k++) {
                final ElfValue value = day[k];
                final DateTime time = value.getTime();

                row = sheetdata.createRow(rown++);
                cell = row.createCell(0);
                cell.setCellStyle(dateTimeStyle2);
                cell.setCellValue(Utils.toDateString(time));

                cell = row.createCell(1);
                cell.setCellStyle(doubleFormat2);

                if (value.isValid())
                    cell.setCellValue(ElfValue.valueIntToDouble(value.getValue()));
                else
                    cell.setCellValue("");

                cell = row.createCell(2);
                cell.setCellStyle(doubleFormat2);
                if (value.isValid())
                    cell.setCellValue(ElfValue.valueIntToDouble(value.getMax()));
                else
                    cell.setCellValue("");

                cell = row.createCell(3);
                cell.setCellStyle(doubleFormat2);
                cell.setCellValue(ElfValue.valueIntToDouble(mediane[i]));

                cell = row.createCell(4);
                cell.setCellStyle(doubleFormat2);
                cell.setCellValue(ElfValue.valueIntToDouble(maxs[i]));

                cell = row.createCell(5);
                cell.setCellStyle(doubleFormat2);
                cell.setCellValue(ElfValue.valueIntToDouble(mediane[maxi]));

                cell = row.createCell(6);
                cell.setCellStyle(doubleFormat2);
                cell.setCellValue(sensibilita);
                cell = row.createCell(7);
                cell.setCellStyle(doubleFormat2);
                cell.setCellValue(3);
                cell = row.createCell(8);
                cell.setCellStyle(doubleFormat2);
                cell.setCellValue(10);
            }
        }

        if (save_grafico) {
            final int maxline = rown - 1;

            sheet = wb.createSheet(Config.getResource("TitleChart"));

            Drawing drawing = sheet.createDrawingPatriarch();
            ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 18, 25);

            Chart chart = drawing.createChart(anchor);
            ChartLegend legend = chart.getOrCreateLegend();
            legend.setPosition(LegendPosition.TOP_RIGHT);

            ScatterChartData data = chart.getChartDataFactory().createScatterChartData();
            //           LineChartData data = chart.getChartDataFactory().createLineChartData();

            ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
            ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);

            leftAxis.setMinimum(0.0);
            leftAxis.setMaximum(10.0);
            leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

            ChartDataSource<String> xs = DataSources.fromStringCellRange(sheetdata,
                    new CellRangeAddress(1, maxline, 0, 0));
            ChartDataSource<Number> ys_val = DataSources.fromNumericCellRange(sheetdata,
                    new CellRangeAddress(1, maxline, 1, 1));
            ChartDataSource<Number> ys_sens = DataSources.fromNumericCellRange(sheetdata,
                    new CellRangeAddress(1, maxline, 6, 6));
            ChartDataSource<Number> ys_qual = DataSources.fromNumericCellRange(sheetdata,
                    new CellRangeAddress(1, maxline, 7, 7));
            ChartDataSource<Number> ys_att = DataSources.fromNumericCellRange(sheetdata,
                    new CellRangeAddress(1, maxline, 8, 8));

            ScatterChartSeries data_val = data.addSerie(xs, ys_val);
            data_val.setTitle(Config.getResource("TitleMeasuredValues"));

            ScatterChartSeries data_sens = data.addSerie(xs, ys_sens);
            data_sens.setTitle(Config.getResource("TitleInstrumentSens"));

            ScatterChartSeries data_qual = data.addSerie(xs, ys_qual);
            data_qual.setTitle(Config.getResource("TitleQualityTarget"));

            ScatterChartSeries data_att = data.addSerie(xs, ys_att);
            data_att.setTitle(Config.getResource("TitleAttentionValue"));

            chart.plot(data, bottomAxis, leftAxis);
        }

        FileOutputStream fileOut = new FileOutputStream(filename);
        wb.write(fileOut);
        fileOut.close();
        return true;
    } catch (Exception e) {
        Utils.MessageBox(Config.getResource("MsgErrorXlsx") + "\n" + e.toString(),
                Config.getResource("TitleError"));
        return false;
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
            }
    }
}

From source file:y.graphs.XLSHelper.java

License:Open Source License

public static void saveCorrelationsCurrents(String filename, CurrentElfDb db, final double imax,
        final double ui, final double ub) throws IOException {

    if (Utils.abortOnExistingAndDontOverwrite(filename))
        return;/*from  www.ja v a2s .co  m*/

    Workbook wb = new XSSFWorkbook();

    final String nDati = Config.getResource("TitleSheetDatas");
    final String nDelta = Config.getResource("TitleSheetDelta");
    final String nCalcs = Config.getResource("TitleSheetCalcs");

    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet(nDati);

    CellStyle dateStyle1 = wb.createCellStyle();
    dateStyle1.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy"));

    CellStyle timeStyle1 = wb.createCellStyle();
    timeStyle1.setDataFormat(createHelper.createDataFormat().getFormat("HH:mm"));

    CellStyle doubleFormat1 = wb.createCellStyle();
    DataFormat format1 = wb.createDataFormat();
    doubleFormat1.setDataFormat(format1.getFormat("0.00"));

    CellStyle percFormat1 = wb.createCellStyle();
    percFormat1.setDataFormat(format1.getFormat("0.00%"));

    int rown = 0;
    Row row = sheet.createRow(rown++);

    final String[] CorrentiColonne = Config.getInstance().getXLSColumnNames();

    for (int i = 0; i < CorrentiColonne.length; i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(CorrentiColonne[i]);
    }

    final List<ElfValue> elfs = db.getElfDb();
    final List<CurrentValue> currs = db.getCurrentDb();

    for (int i = 0, maxi = Math.max(elfs.size(), currs.size()); i < maxi; i++) {
        final ElfValue e = i <= elfs.size() ? elfs.get(i) : null;
        final CurrentValue c = i <= currs.size() ? currs.get(i) : null;

        row = sheet.createRow(rown++);
        int columnnn = 0;

        if (c == null) {
            Cell cell = row.createCell(columnnn++);
            cell.setCellValue("");
            cell = row.createCell(columnnn++);
            cell.setCellValue("");
            cell = row.createCell(columnnn++);
            cell.setCellValue("");
        } else {
            Cell cell = row.createCell(columnnn++);
            cell.setCellValue(Utils.toDateString(c.getTime())); // data corrente
            cell.setCellStyle(dateStyle1);
            cell = row.createCell(columnnn++);
            cell.setCellStyle(timeStyle1);
            cell.setCellValue(Utils.toTimeString(c.getTime())); // ora corrente
            cell = row.createCell(columnnn++);
            cell.setCellStyle(doubleFormat1);
            cell.setCellValue(ElfValue.valueIntToDouble(c.getValue()));
        }

        if (e == null) {
            Cell cell = row.createCell(columnnn++);
            cell.setCellValue("");
            cell = row.createCell(columnnn++);
            cell.setCellValue("");
            cell = row.createCell(columnnn++);
            cell.setCellValue("");
        } else {
            Cell cell = row.createCell(columnnn++);
            cell.setCellStyle(dateStyle1);
            cell.setCellValue(Utils.toDateString(e.getTime())); // data corrente
            cell = row.createCell(columnnn++);
            cell.setCellStyle(timeStyle1);
            cell.setCellValue(Utils.toTimeString(e.getTime())); // ora corrente
            cell = row.createCell(columnnn++);
            cell.setCellStyle(doubleFormat1);
            cell.setCellValue(ElfValue.valueIntToDouble(e.getValue())); // ora corrente
        }
    }

    final int total_rown = rown;

    // intermedi
    {
        sheet = wb.createSheet(nDelta);
        rown = 0;
        int columnnn;

        columnnn = 0;
        row = sheet.createRow(rown++);
        Cell cell = row.createCell(columnnn++);
        cell.setCellValue("dI");
        cell = row.createCell(columnnn++);
        cell.setCellValue("dB");
        cell = row.createCell(columnnn++);
        cell.setCellValue("dIdB");
        cell = row.createCell(columnnn++);
        cell.setCellValue("dI^2");
        cell = row.createCell(columnnn++);
        cell.setCellValue("dB^2");
        cell = row.createCell(columnnn++);
        cell.setCellValue("Ri = Bi/Ii");
        cell = row.createCell(columnnn++);
        cell.setCellValue("Ri^2");

        for (int i = 2; i <= total_rown; i++) {
            columnnn = 0;
            row = sheet.createRow(rown++);
            cell = row.createCell(columnnn++);
            cell.setCellFormula(nDati + "!C" + i + "-" + nCalcs + "!$B$2");
            cell = row.createCell(columnnn++);
            cell.setCellFormula(nDati + "!F" + i + "-" + nCalcs + "!$B$3");
            cell = row.createCell(columnnn++);
            cell.setCellFormula("A" + i + "*B" + i);
            cell = row.createCell(columnnn++);
            cell.setCellFormula("A" + i + "*A" + i);
            cell = row.createCell(columnnn++);
            cell.setCellFormula("B" + i + "*B" + i);
            cell = row.createCell(columnnn++);
            cell.setCellFormula(nDati + "!F" + i + "/" + nDati + "!C" + i);
            cell = row.createCell(columnnn++);
            cell.setCellFormula("F" + i + "*F" + i);
        }
    }

    // correlazioni e calcoli finali
    {
        sheet = wb.createSheet(nCalcs);
        rown = 0;
        int columnnn;

        columnnn = 0;
        row = sheet.createRow(rown++);
        Cell cell = row.createCell(columnnn++);
        cell.setCellValue(Config.getResource("TitleNumberOfData"));
        cell = row.createCell(columnnn++);
        cell.setCellFormula("COUNT(" + nDati + "!C:C)"); // B1

        columnnn = 0;
        row = sheet.createRow(rown++);
        cell = row.createCell(columnnn++);
        cell.setCellValue(Config.getResource("TitleCurAvg"));
        cell = row.createCell(columnnn++);
        cell.setCellFormula("AVERAGE(" + nDati + "!C:C)"); // B2

        columnnn = 0;
        row = sheet.createRow(rown++);
        cell = row.createCell(columnnn++);
        cell.setCellValue(Config.getResource("TitleFieldAvg"));
        cell = row.createCell(columnnn++);
        cell.setCellFormula("AVERAGE(" + nDati + "!F:F)"); // B3

        columnnn = 0;
        row = sheet.createRow(rown++);
        cell = row.createCell(columnnn++);
        cell.setCellValue(Config.getResource("TitleRm"));
        cell = row.createCell(columnnn++);
        cell.setCellFormula("AVERAGE(" + nDelta + "!F:F)"); // B4

        columnnn = 0;
        row = sheet.createRow(rown++);
        cell = row.createCell(columnnn++);
        cell.setCellValue(Config.getResource("TitleImax"));
        cell = row.createCell(columnnn++);
        cell.setCellStyle(doubleFormat1);
        cell.setCellValue(imax); // B5

        columnnn = 0;
        row = sheet.createRow(rown++);
        cell = row.createCell(columnnn++);
        cell.setCellValue(Config.getResource("TitleUI"));
        cell = row.createCell(columnnn++);
        cell.setCellStyle(percFormat1);
        cell.setCellValue(ui); // B6

        columnnn = 0;
        row = sheet.createRow(rown++);
        cell = row.createCell(columnnn++);
        cell.setCellValue(Config.getResource("TitleUB"));
        cell = row.createCell(columnnn++);
        cell.setCellStyle(percFormat1);
        cell.setCellValue(ub); // B7

        columnnn = 0;
        row = sheet.createRow(rown++);
        cell = row.createCell(columnnn++);
        cell.setCellValue(Config.getResource("TitleURm"));
        cell = row.createCell(columnnn++);
        cell.setCellStyle(percFormat1);
        cell.setCellFormula("$B$6*$B$6+$B$7*$B$7-$B$6*$B$7*$B$9"); // B8

        columnnn = 0;
        row = sheet.createRow(rown++);
        cell = row.createCell(columnnn++);
        cell.setCellValue(Config.getResource("TitleCorrelation"));
        cell = row.createCell(columnnn++);
        cell.setCellFormula("SUM(" + nDelta + "!C:C)/SQRT(SUM(" + nDelta + "!D:D)*SUM(" + nDelta + "!E:E))"); // B9

        columnnn = 0;
        row = sheet.createRow(rown++);
        cell = row.createCell(columnnn++);
        cell.setCellValue(Config.getResource("TitleBmax"));
        cell = row.createCell(columnnn++);
        cell.setCellFormula("$B$4*$B$5"); // B10

        columnnn = 0;
        row = sheet.createRow(rown++);
        cell = row.createCell(columnnn++);
        cell.setCellValue(Config.getResource("TitleEperc"));
        cell = row.createCell(columnnn++);
        cell.setCellFormula("$B$8*SUM(delta!G:G)/$B$1/$B$1"); // B11 = u(Rm)^2

        columnnn = 0;
        row = sheet.createRow(rown++);
        cell = row.createCell(columnnn++);
        cell.setCellValue(Config.getResource("TitleUBmax"));
        cell = row.createCell(columnnn++);
        cell.setCellFormula("$B$5 * SQRT($B$11 + $B$4*$B$4*$B$6*$B$6)"); // B12          

        columnnn = 0;
        row = sheet.createRow(rown++);
        cell = row.createCell(columnnn++);
        cell.setCellValue(Config.getResource("TitleEBmax"));
        cell = row.createCell(columnnn++);
        cell.setCellStyle(percFormat1);
        cell.setCellFormula("2*$B$12/$B$10"); // B13
    }

    FileOutputStream fileOut = new FileOutputStream(filename);
    wb.write(fileOut);
    wb.close();
    fileOut.close();
}

From source file:y.graphs.XLSHelper.java

License:Open Source License

public static boolean saveCurrentsData(String filename, CurrentDb db, boolean save_grafico) {
    final DateTime[] times = db.getPeriods();
    final CurrentValue[][] dayvalues = db.getSampledData();
    final int[] mediane = db.getOpValues();
    final int[] maxs = db.getOpMaxDay();
    final int[] counts = db.getOpValueCount();
    final int maxi = db.getMaxidx();

    Workbook wb = null;

    try {/*from  w w  w. j  a v  a 2  s .  c o  m*/
        if (Utils.abortOnExistingAndDontOverwrite(filename))
            return false;

        wb = new XSSFWorkbook();

        CreationHelper createHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet(Config.getResource("TitleStats"));

        int rown = 0;
        Row row = sheet.createRow(rown++);
        Cell cell = row.createCell(0);
        cell.setCellValue(Config.getResource("TitleDate"));
        cell = row.createCell(1);
        cell.setCellValue(Config.getResource("TitleMediana"));
        cell = row.createCell(2);
        cell.setCellValue(Config.getResource("TitleMaxM"));
        cell = row.createCell(3);
        cell.setCellValue(Config.getResource("TitleNumberOfData"));

        CellStyle dateStyle1 = wb.createCellStyle();
        dateStyle1.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy"));
        CellStyle doubleFormat1 = wb.createCellStyle();
        DataFormat format1 = wb.createDataFormat();
        doubleFormat1.setDataFormat(format1.getFormat("0.00"));

        for (int i = 0; i < mediane.length; i++) {
            row = sheet.createRow(rown++);

            cell = row.createCell(0);
            cell.setCellStyle(dateStyle1);
            cell.setCellValue(Utils.toDateString(dayvalues[i][0].getTime()));

            cell = row.createCell(1);
            cell.setCellStyle(doubleFormat1);
            cell.setCellValue(ElfValue.valueIntToDouble(mediane[i]));

            cell = row.createCell(2);
            cell.setCellStyle(doubleFormat1);
            cell.setCellValue(ElfValue.valueIntToDouble(maxs[i]));

            cell = row.createCell(3);
            cell.setCellValue(counts[i]);
        }

        // line with DataFunction max
        row = sheet.createRow(rown++);
        row = sheet.createRow(rown++);
        cell = row.createCell(0);
        cell.setCellValue(Config.getResource("MsgMax") + "(" + db.getOperationPerformed().getName() + ") - "
                + Utils.toDateString(times[maxi]));

        cell = row.createCell(1);
        cell.setCellStyle(doubleFormat1);
        cell.setCellValue(ElfValue.valueIntToDouble(mediane[maxi]));

        cell = row.createCell(2);
        cell.setCellStyle(doubleFormat1);
        cell.setCellValue(ElfValue.valueIntToDouble(maxs[maxi]));

        cell = row.createCell(3);
        cell.setCellValue(counts[maxi]);

        // line with max
        final CurrentValue maxvalue = db.getSelectedCurrentValue(new Comparator<CurrentValue>() {
            @Override
            public int compare(CurrentValue o1, CurrentValue o2) {
                return o1.getValue() - o2.getValue();
            }
        });
        row = sheet.createRow(rown++);
        cell = row.createCell(0);
        cell.setCellValue(Config.getResource("MsgMax") + "(" + Utils.toDateString(maxvalue.getTime()) + ")");

        cell = row.createCell(1);
        cell.setCellStyle(doubleFormat1);
        cell.setCellValue(MeasurementValue.valueIntToDouble(maxvalue.getValue()));

        cell = row.createCell(2);
        cell.setCellStyle(doubleFormat1);
        cell.setCellValue("");

        cell = row.createCell(3);
        cell.setCellValue(counts[maxi]);

        // sheet containing all raw data
        Sheet sheetdata = wb.createSheet(Config.getResource("TitleSheetDatas"));
        CellStyle dateTimeStyle2 = wb.createCellStyle();
        dateTimeStyle2.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));

        CellStyle doubleFormat2 = wb.createCellStyle();
        DataFormat format2 = wb.createDataFormat();
        doubleFormat2.setDataFormat(format2.getFormat("0.00"));

        rown = 0;
        row = sheetdata.createRow(rown++);
        cell = row.createCell(0);
        cell.setCellValue(Config.getResource("TitleDate"));
        cell = row.createCell(1);
        cell.setCellValue(Config.getResource("TitleValue"));
        cell = row.createCell(2);
        cell.setCellValue(Config.getResource("TitlePeak"));
        cell = row.createCell(3);
        cell.setCellValue(Config.getResource("TitleMediana"));
        cell = row.createCell(4);
        cell.setCellValue(Config.getResource("TitleDayMax"));
        cell = row.createCell(5);
        cell.setCellValue(Config.getResource("TitleMedianaMax"));

        for (int i = 0; i < dayvalues.length; i++) {
            final CurrentValue[] day = dayvalues[i];

            for (int k = 0; k < day.length; k++) {
                final CurrentValue value = day[k];
                final DateTime time = value.getTime();

                row = sheetdata.createRow(rown++);
                cell = row.createCell(0);
                cell.setCellStyle(dateTimeStyle2);
                cell.setCellValue(Utils.toDateString(time));

                cell = row.createCell(1);
                cell.setCellStyle(doubleFormat2);

                cell.setCellValue(ElfValue.valueIntToDouble(value.getValue()));

                cell = row.createCell(2);
                //                cell.setCellStyle(doubleFormat2);
                cell.setCellValue("");

                cell = row.createCell(3);
                cell.setCellStyle(doubleFormat2);
                cell.setCellValue(ElfValue.valueIntToDouble(mediane[i]));

                cell = row.createCell(4);
                cell.setCellStyle(doubleFormat2);
                cell.setCellValue(ElfValue.valueIntToDouble(maxs[i]));

                cell = row.createCell(5);
                cell.setCellStyle(doubleFormat2);
                cell.setCellValue(ElfValue.valueIntToDouble(mediane[maxi]));
            }
        }

        if (save_grafico) {
            final int maxline = rown - 1;

            sheet = wb.createSheet(Config.getResource("TitleChart"));

            Drawing drawing = sheet.createDrawingPatriarch();
            ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 18, 25);

            Chart chart = drawing.createChart(anchor);
            ChartLegend legend = chart.getOrCreateLegend();
            legend.setPosition(LegendPosition.TOP_RIGHT);

            ScatterChartData data = chart.getChartDataFactory().createScatterChartData();
            //           LineChartData data = chart.getChartDataFactory().createLineChartData();

            ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
            ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);

            leftAxis.setMinimum(0.0);
            leftAxis.setMaximum(10.0);
            leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

            ChartDataSource<String> xs = DataSources.fromStringCellRange(sheetdata,
                    new CellRangeAddress(1, maxline, 0, 0));
            ChartDataSource<Number> ys_val = DataSources.fromNumericCellRange(sheetdata,
                    new CellRangeAddress(1, maxline, 1, 1));
            ChartDataSource<Number> ys_sens = DataSources.fromNumericCellRange(sheetdata,
                    new CellRangeAddress(1, maxline, 6, 6));

            ScatterChartSeries data_val = data.addSerie(xs, ys_val);
            data_val.setTitle(Config.getResource("TitleMeasuredValues"));

            ScatterChartSeries data_sens = data.addSerie(xs, ys_sens);
            data_sens.setTitle(Config.getResource("TitleInstrumentSens"));

            chart.plot(data, bottomAxis, leftAxis);
        }

        FileOutputStream fileOut = new FileOutputStream(filename);
        wb.write(fileOut);
        fileOut.close();
        return true;
    } catch (Exception e) {
        Utils.MessageBox(Config.getResource("MsgErrorXlsx") + "\n" + e.toString(),
                Config.getResource("TitleError"));
        return false;
    } finally {
        if (wb != null)
            try {
                wb.close();
            } catch (IOException e) {
            }
    }
}