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

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

Introduction

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

Prototype

CellStyle createCellStyle();

Source Link

Document

Create a new Cell style and add it to the workbook's style table

Usage

From source file:rpt.GUI.ProgramStrategist.CyclePlans.CompareDialogController.java

private int writeRow(Workbook wb, Sheet sheet, Row row, TableVariant variant,
        Map<String, Map<String, String>> diffList, Boolean colorChanges, Boolean addOldSOP) {
    //Used for placing comment at the right position
    CreationHelper factory = wb.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = factory.createClientAnchor();

    //Create new style
    XSSFCellStyle styleRed = (XSSFCellStyle) wb.createCellStyle();
    XSSFCellStyle styleBlack = (XSSFCellStyle) wb.createCellStyle();
    XSSFFont fontRed = (XSSFFont) wb.createFont();
    fontRed.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
    XSSFFont fontBlack = (XSSFFont) wb.createFont();
    fontBlack.setColor(new XSSFColor(new java.awt.Color(0, 0, 0)));
    styleRed.setFont(fontRed);/*from  w w w .  j  a va  2  s.co  m*/
    styleBlack.setFont(fontBlack);

    //xEtract differences to highlight
    Map<String, String> differences;

    if (diffList != null) {
        differences = diffList.get(variant.getVariantID());
    } else {
        differences = new HashMap<String, String>();
    }

    //Start with column 0
    int cols = 0;

    //Create string with columns to print
    String[] columns = { "Plant", "Platform", "Vehicle", "Propulsion", "Denomination", "Fuel", "EngineFamily",
            "Generation", "EngineCode", "Displacement", "EnginePower", "ElMotorPower", "Torque",
            "TorqueOverBoost", "GearboxType", "Gears", "Gearbox", "Driveline", "TransmissionCode", "CertGroup",
            "EmissionClass", "StartOfProd", "EndOfProd" };

    Cell cell;

    for (int i = 0; i < columns.length; i++) {
        cell = row.createCell(i);

        if (differences.containsKey(columns[i])) {
            cell.setCellStyle(styleRed);

            // position the comment
            anchor.setCol1(cell.getColumnIndex());
            anchor.setCol2(cell.getColumnIndex() + 1);
            anchor.setRow1(row.getRowNum());
            anchor.setRow2(row.getRowNum() + 3);

            // Create the comment and set the text+author
            Comment comment = drawing.createCellComment(anchor);
            RichTextString str = factory.createRichTextString(differences.get(columns[i]));
            comment.setString(str);
            comment.setAuthor("RPT");

            // Assign the comment to the cell
            cell.setCellComment(comment);
        } else {
            cell.setCellStyle(styleBlack);
        }
        cell.setCellValue(variant.getValue(columns[i]));
        cols++;
    }

    if (addOldSOP) {
        cell = row.createCell(23);
        cell.setCellValue(variant.getOldSOP());
        cols++;
    }

    if (addOldSOP) {
        cell = row.createCell(24);
        cell.setCellValue(variant.getOldEOP());
        cols++;
    }

    return cols;
}

From source file:ru.wmbdiff.ExportIntoExcel.java

License:Apache License

public void export(File file, WMBDiffNoRootTreeTableModel model) {
    logger.info("export begin");
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("WMBDiff");
    int rowNum = 0;
    //Create Header
    CellStyle style;//from  w  w  w . j av a  2 s .co  m
    Font headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    headerFont.setFontHeightInPoints((short) 10);
    style = workbook.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.WHITE.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.WHITE.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.WHITE.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.WHITE.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);

    Row row = sheet.createRow(rowNum++);
    Cell cell;
    cell = row.createCell(0);
    cell.setCellValue("Result");
    cell.setCellStyle(style);
    cell = row.createCell(1);
    cell.setCellValue("Broker");
    cell.setCellStyle(style);
    cell = row.createCell(2);
    cell.setCellValue("Execution Group");
    cell.setCellStyle(style);
    cell = row.createCell(3);
    cell.setCellValue("Name");
    cell.setCellStyle(style);
    cell = row.createCell(4);
    cell.setCellValue("Type");
    cell.setCellStyle(style);
    cell = row.createCell(5);
    cell.setCellValue("Last Modification");
    cell.setCellStyle(style);
    cell = row.createCell(6);
    cell.setCellValue("Deployment Date");
    cell.setCellStyle(style);
    cell = row.createCell(7);
    cell.setCellValue("Bar File");
    cell.setCellStyle(style);
    cell = row.createCell(8);
    cell.setCellValue("Result Description");
    cell.setCellStyle(style);
    sheet.createFreezePane(0, 1);

    List<DiffExecutionGroup> dEG = model.getDiffExecutionGroupList();
    ListIterator<DiffExecutionGroup> litr = dEG.listIterator();
    while (litr.hasNext()) {
        DiffExecutionGroup element = litr.next();
        element.getDiffResultList();
        ListIterator<DiffDeployedObjectResult> litr2 = element.getDiffResultList().listIterator();
        while (litr2.hasNext()) {
            DiffDeployedObjectResult res = litr2.next();
            switch (res.getResult()) {
            case ONLY_IN_A:
                createRow(rowNum++, sheet, res.getAObject(), "A", res.getResultDesc());
                break;
            case ONLY_IN_B:
                createRow(rowNum++, sheet, res.getBObject(), "B", res.getResultDesc());
                break;
            case EQUAL:
                createRow(rowNum++, sheet, res.getAObject(), "=", res.getResultDesc());
                createRow(rowNum++, sheet, res.getBObject(), "=", res.getResultDesc());
                sheet.groupRow(rowNum - 2, rowNum - 2);
                break;
            case DIFF:
                createRow(rowNum++, sheet, res.getAObject(), "!=", res.getResultDesc());
                createRow(rowNum++, sheet, res.getBObject(), "!=", res.getResultDesc());
                sheet.groupRow(rowNum - 2, rowNum - 2);
                break;
            }
            ;

        }
        ;
    }
    ;
    //Adjust column width to fit the contents
    for (int i = 0; i < 9; i++)
        sheet.autoSizeColumn(i);
    //set Filter
    sheet.setAutoFilter(new CellRangeAddress(0, rowNum - 1, 0, 8));
    try {
        FileOutputStream out = new FileOutputStream(file);
        workbook.write(out);
        workbook.close();
        out.close();
    } catch (Exception e) {
        logger.error("export", e);
    }
    logger.info("export end");
}

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;
                }/* 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 ww .  j  a  va2 s.c om*/
 */
@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;//w w w  .ja  v  a  2s .  c  o 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:sql.fredy.sqltools.XLSExport.java

License:Open Source License

/**
 * Create the XLS-File named fileName// w w w. jav a  2s.c  o  m
 *
 * @param fileName is the Name (incl. Path) of the XLS-file to create
 *
 *
 */
public int createXLS(String fileName) {

    // I need to have a query to process
    if ((getQuery() == null) && (getPstmt() == null)) {
        logger.log(Level.WARNING, "Need to have a query to process");
        return 0;
    }

    // I also need to have a file to write into
    if (fileName == null) {
        logger.log(Level.WARNING, "Need to know where to write into");
        return 0;
    }
    fileName = fixFileName(fileName);
    checkXlsx(fileName);

    // I need to have a connection to the RDBMS
    if (getCon() == null) {
        logger.log(Level.WARNING, "Need to have a connection to process");
        return 0;
    }

    //Statement stmt = null;
    ResultSet resultSet = null;
    ResultSetMetaData rsmd = null;
    try {

        // first we have to create the Statement
        if (getPstmt() == null) {
            pstmt = getCon().prepareStatement(getQuery());
        }

        //stmt = getCon().createStatement();
    } catch (SQLException sqle1) {
        setException(sqle1);
        logger.log(Level.WARNING, "Can not create Statement. Message: " + sqle1.getMessage().toString());
        return 0;
    }

    logger.log(Level.FINE, "FileName: " + fileName);
    logger.log(Level.FINE, "Query   : " + getQuery());

    logger.log(Level.FINE, "Starting export...");

    // create an empty sheet
    Workbook wb;
    Sheet sheet;
    Sheet sqlsheet;
    CreationHelper createHelper = null;
    //XSSFSheet xsheet; 
    //HSSFSheet sheet;

    if (isXlsx()) {
        wb = new SXSSFWorkbook();
        createHelper = wb.getCreationHelper();
    } else {
        wb = new HSSFWorkbook();
        createHelper = wb.getCreationHelper();
    }
    sheet = wb.createSheet("Data Export");

    // create a second sheet just containing the SQL Statement
    sqlsheet = wb.createSheet("SQL Statement");
    Row sqlrow = sqlsheet.createRow(0);
    Cell sqltext = sqlrow.createCell(0);
    try {
        if (getQuery() != null) {
            sqltext.setCellValue(getQuery());
        } else {
            sqltext.setCellValue(pstmt.toString());
        }
    } catch (Exception lex) {

    }
    CellStyle style = wb.createCellStyle();
    style.setWrapText(true);

    sqltext.setCellStyle(style);

    Row r = null;

    int row = 0; // row    number
    int col = 0; // column number
    int columnCount = 0;

    try {
        //resultSet = stmt.executeQuery(getQuery());
        resultSet = pstmt.executeQuery();
        logger.log(Level.FINE, "query executed");
    } catch (SQLException sqle2) {
        setException(sqle2);
        logger.log(Level.WARNING, "Can not execute query. Message: " + sqle2.getMessage().toString());
        return 0;
    }

    // create Header in XLS-file
    ArrayList<String> head = new ArrayList();
    try {
        rsmd = resultSet.getMetaData();
        logger.log(Level.FINE, "Got MetaData of the resultset");

        columnCount = rsmd.getColumnCount();
        logger.log(Level.FINE, Integer.toString(columnCount) + " Columns in this resultset");

        r = sheet.createRow(row); // titlerow

        if ((!isXlsx()) && (columnCount > 255)) {
            columnCount = 255;
        }

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

            // we create the cell
            Cell cell = r.createCell(col);

            // set the value of the cell
            cell.setCellValue(rsmd.getColumnName(i + 1));
            head.add(rsmd.getColumnName(i + 1));

            // then we align center
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

            // now we make it bold
            //HSSFFont f = wb.createFont();
            Font headerFont = wb.createFont();
            headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            cellStyle.setFont(headerFont);

            //cellStyle.setFont(f);
            // adapt this font to the cell
            cell.setCellStyle(cellStyle);

            col++;
        }
    } catch (SQLException sqle3) {
        setException(sqle3);
        logger.log(Level.WARNING, "Can not create XLS-Header. Message: " + sqle3.getMessage().toString());
        return 0;
    }

    // looping the resultSet
    int wbCounter = 0;
    try {
        while (resultSet.next()) {

            // this is the next row
            col = 0; // put column counter back to 0 to start at the next row
            row++; // next row

            // create a new sheet if more then 60'000 Rows and xls file
            if ((!isXlsx()) && (row % 65530 == 0)) {
                wbCounter++;
                row = 0;

                sheet = wb.createSheet("Data Export " + Integer.toString(wbCounter));
                logger.log(Level.INFO, "created a further page because of a huge amount of data");

                // create the head
                r = sheet.createRow(row); // titlerow
                for (int i = 0; i < head.size(); i++) {

                    // we create the cell
                    Cell cell = r.createCell(col);

                    // set the value of the cell
                    cell.setCellValue((String) head.get(i));

                    // then we align center
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

                    // now we make it bold
                    //HSSFFont f = wb.createFont();
                    Font headerFont = wb.createFont();
                    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
                    cellStyle.setFont(headerFont);

                    //cellStyle.setFont(f);
                    // adapt this font to the cell
                    cell.setCellStyle(cellStyle);

                    col++;
                }

                row++;
            }

            try {
                r = sheet.createRow(row);
            } catch (Exception e) {
                logger.log(Level.WARNING, "Error while creating row number " + row + " " + e.getMessage());

                wbCounter++;
                row = 0;

                sheet = wb.createSheet("Data Export " + Integer.toString(wbCounter));
                logger.log(Level.WARNING, "created a further page in the hope it helps...");

                // create the head
                r = sheet.createRow(row); // titlerow
                for (int i = 0; i < head.size(); i++) {

                    // we create the cell
                    Cell cell = r.createCell(col);

                    // set the value of the cell
                    cell.setCellValue((String) head.get(i));

                    // then we align center
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

                    // now we make it bold
                    //HSSFFont f = wb.createFont();
                    Font headerFont = wb.createFont();
                    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
                    cellStyle.setFont(headerFont);

                    //cellStyle.setFont(f);
                    // adapt this font to the cell
                    cell.setCellStyle(cellStyle);

                    col++;
                }

                row++;

            }

            col = 0; // put column counter back to 0 to start at the next row
            String previousMessage = "";
            for (int i = 0; i < columnCount; i++) {
                try {
                    // depending on the type, create the cell
                    switch (rsmd.getColumnType(i + 1)) {
                    case java.sql.Types.INTEGER:
                        r.createCell(col).setCellValue(resultSet.getInt(i + 1));
                        break;
                    case java.sql.Types.FLOAT:
                        r.createCell(col).setCellValue(resultSet.getFloat(i + 1));
                        break;
                    case java.sql.Types.DOUBLE:
                        r.createCell(col).setCellValue(resultSet.getDouble(i + 1));
                        break;
                    case java.sql.Types.DECIMAL:
                        r.createCell(col).setCellValue(resultSet.getFloat(i + 1));
                        break;
                    case java.sql.Types.NUMERIC:
                        r.createCell(col).setCellValue(resultSet.getFloat(i + 1));
                        break;
                    case java.sql.Types.BIGINT:
                        r.createCell(col).setCellValue(resultSet.getInt(i + 1));
                        break;
                    case java.sql.Types.TINYINT:
                        r.createCell(col).setCellValue(resultSet.getInt(i + 1));
                        break;
                    case java.sql.Types.SMALLINT:
                        r.createCell(col).setCellValue(resultSet.getInt(i + 1));
                        break;

                    case java.sql.Types.DATE:
                        // first we get the date
                        java.sql.Date dat = resultSet.getDate(i + 1);
                        java.util.Date date = new java.util.Date(dat.getTime());
                        r.createCell(col).setCellValue(date);
                        break;

                    case java.sql.Types.TIMESTAMP:
                        // first we get the date
                        java.sql.Timestamp ts = resultSet.getTimestamp(i + 1);

                        Cell c = r.createCell(col);
                        try {
                            c.setCellValue(ts);
                            // r.createCell(col).setCellValue(ts);

                            // Date Format
                            CellStyle cellStyle = wb.createCellStyle();
                            cellStyle.setDataFormat(
                                    createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:mm:ss"));
                            c.setCellStyle(cellStyle);
                        } catch (Exception e) {
                            c.setCellValue(" ");
                        }
                        break;

                    case java.sql.Types.TIME:
                        // first we get the date
                        java.sql.Time time = resultSet.getTime(i + 1);
                        r.createCell(col).setCellValue(time);
                        break;

                    case java.sql.Types.BIT:
                        boolean b1 = resultSet.getBoolean(i + 1);
                        r.createCell(col).setCellValue(b1);
                        break;
                    case java.sql.Types.BOOLEAN:
                        boolean b2 = resultSet.getBoolean(i + 1);
                        r.createCell(col).setCellValue(b2);
                        break;
                    case java.sql.Types.CHAR:
                        r.createCell(col).setCellValue(resultSet.getString(i + 1));
                        break;
                    case java.sql.Types.NVARCHAR:
                        r.createCell(col).setCellValue(resultSet.getString(i + 1));
                        break;

                    case java.sql.Types.VARCHAR:
                        try {
                            r.createCell(col).setCellValue(resultSet.getString(i + 1));
                        } catch (Exception e) {
                            r.createCell(col).setCellValue(" ");
                            logger.log(Level.WARNING,
                                    "Exception while writing column {0} row {3} type: {1} Message: {2}",
                                    new Object[] { col, rsmd.getColumnType(i + 1), e.getMessage(), row });
                        }
                        break;
                    default:
                        r.createCell(col).setCellValue(resultSet.getString(i + 1));
                        break;
                    }
                } catch (Exception e) {
                    //e.printStackTrace();
                    if (resultSet.wasNull()) {
                        r.createCell(col).setCellValue(" ");
                    } else {
                        logger.log(Level.WARNING,
                                "Unhandled type at column {0}, row {3} type: {1}. Filling up with blank {2}",
                                new Object[] { col, rsmd.getColumnType(i + 1), e.getMessage(), row });
                        r.createCell(col).setCellValue(" ");
                    }
                }
                col++;
            }
        }
        //pstmt.close();
    } catch (SQLException sqle3) {
        setException(sqle3);
        logger.log(Level.WARNING,
                "Exception while writing data into sheet. Message: " + sqle3.getMessage().toString());
    }

    try {

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream(fileName);
        wb.write(fileOut);
        fileOut.close();

        logger.log(Level.INFO, "File created");
        logger.log(Level.INFO, "Wrote: {0} lines into XLS-File", Integer.toString(row));

    } catch (Exception e) {
        logger.log(Level.WARNING, "Exception while writing xls-File: " + e.getMessage().toString());
    }
    return row;

}

From source file:test.poi.MyExcelDemo.java

License:Apache License

/**
 * Create a library of cell styles/*  w w  w .  j a va 2s.  c o m*/
 */
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:test.XExcel.java

public static void main(String[] args) throws FileNotFoundException, IOException {
    Properties bundle = new Properties();
    bundle.load(new FileInputStream(new File("configuration.properties")));
    Integer counter = Integer.parseInt(bundle.getProperty("loop.counter"));

    String titles[] = { "Firstname", "Lastname", "Country", "Language" };
    String data[][] = { { "noman ali", "abbasi", "PK", "EN" }, { "ahsan", "shaikh", "PK", "EN" },
            { "abdul jalil", "ahmed", "PK", "EN" }, { "umair", "khan", "PK", "EN" },
            { "abdul rahim", "khan", "PK", "EN" } };
    int rowCount = 0;

    try {//  ww  w .j a v  a 2  s.c  o m
        File xlsxFile = new File("C:/workbook.xlsx");

        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;

        if (xlsxFile.exists()) {
            FileInputStream fileInputStream = new FileInputStream(xlsxFile);
            wb = new XSSFWorkbook(fileInputStream);
            sheet = wb.getSheet("Test");
            rowCount = sheet.getPhysicalNumberOfRows();

            System.err.println("Writingxisting file ....");
            for (int i = 0; i < counter; i++) {
                row = sheet.createRow(rowCount++);
                int rndNumber = new Random().nextInt(3);
                System.out.println(rndNumber);
                for (int c = 0; c < titles.length; c++) {
                    Cell cell = row.createCell(c);
                    cell.setCellValue(data[rndNumber][c]);
                }
            }

            System.err.println(xlsxFile.delete());
        } else {
            System.err.println("Creatingl file ....");

            wb = new XSSFWorkbook();
            sheet = wb.createSheet("Test");
            row = sheet.createRow(rowCount++);
            CellStyle cellStyle = wb.createCellStyle();
            Font font = wb.createFont();
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);

            cellStyle.setFont(font);

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

            for (int i = 0; i < counter; i++) {
                row = sheet.createRow(rowCount++);
                int rndNumber = new Random().nextInt(3);

                for (int c = 0; c < titles.length; c++) {
                    Cell cell = row.createCell(c);
                    cell.setCellValue(data[rndNumber][c]);
                }
            }
        }

        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream("C:/workbook.xlsx", true);
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        System.out.println("Last " + sheet.getLastRowNum() + ", " + sheet.getPhysicalNumberOfRows());
    } catch (java.lang.IllegalArgumentException illegalArgumentException) {
        System.err.println(illegalArgumentException.getMessage());
    }
}

From source file:tools.IOHelper.java

public static void plate1ResultSheet(ANAPlate plate, File outputFolder) throws IOException {

    File outputFile = new File(outputFolder, plate.getPlateId() + "_" + TIME + ".xlsx");
    Workbook excelFile = null;
    //        if (outputFile.exists()) {
    //            try {
    //                excelFile = WorkbookFactory.create(outputFile);
    //            } catch (EncryptedDocumentException ex) {
    //                System.out.println("file with assigned name already exists but is encrypted...");
    //                Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex);
    //            } catch (InvalidFormatException ex) {
    //                Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex);
    //            }
    //        } else {
    //            outputFile.createNewFile();
    //            excelFile = new XSSFWorkbook();
    //        }/*from w  w w  .  ja  v a  2 s .  c om*/
    if (outputFile.exists()) {
        outputFile.delete();
    }
    outputFile.createNewFile();
    excelFile = new XSSFWorkbook();
    if (excelFile == null) {
        throw new RuntimeException("fail to create the xlsx file");
    }
    int rowCount = plate.getSampleNumber(); //not including the 2 control samples + "_"+time
    String sheetName = plate.getPlateId();
    //create a working sheet 
    Sheet sheet = excelFile.createSheet(sheetName);
    //starting row & col
    int rowIndex = 0;
    int colIndex = 0;
    int totalCol = 0;
    int pos = 0, neg = 0, all = 0;
    XSSFFont fontTitle = (XSSFFont) excelFile.createFont();
    fontTitle.setFontHeightInPoints((short) 10);
    fontTitle.setFontName("Arial");
    fontTitle.setColor(IndexedColors.GREEN.getIndex());
    fontTitle.setBold(true);
    fontTitle.setItalic(false);
    XSSFCellStyle styleTitle = (XSSFCellStyle) excelFile.createCellStyle();
    styleTitle.setAlignment(CellStyle.ALIGN_CENTER);
    styleTitle.setFont(fontTitle);
    Cell cell0 = sheet.createRow(rowIndex++).createCell(0);
    cell0.setCellValue(plate.getPlateId() + " Summary"); //title
    cell0.setCellStyle(styleTitle);

    //        //optional set Cell Style
    //        CellStyle styleTitle = null;
    //        CellStyle style = null;
    Row row = sheet.createRow(rowIndex++); //names
    Cell column = row.createCell(colIndex++);
    column.setCellValue("Sample ID");
    totalCol++;
    column = row.createCell(colIndex++);
    column.setCellValue("Chip Location");
    totalCol++;
    column = row.createCell(colIndex++);
    column.setCellValue("Result");
    totalCol++;

    //        column = row.createCell(colIndex++);
    //        column.setCellValue("Positivity 0.3P");
    //        totalCol++;
    //        column = row.createCell(colIndex++);
    //        column.setCellValue("Positivity0.275P+0.5N");
    //        totalCol++;

    column = row.createCell(colIndex++);
    column.setCellValue("Signal");
    totalCol++;
    column = row.createCell(colIndex++);
    column.setCellValue("Comments");
    totalCol++;

    for (ANATestResult result : plate.getTestResultList()) {
        row = sheet.createRow(rowIndex++); //data
        colIndex = 0;
        column = row.createCell(colIndex++);
        column.setCellValue(result.getJulien_barcode());
        column = row.createCell(colIndex++);
        column.setCellValue(result.getPillarPosition());
        column = row.createCell(colIndex++);
        if (result.getPositivity() == null) {
            column.setCellValue("Null Result");
        } else {
            if (ANA_Result.POSITIVE.equals(result.getPositivity())) {
                pos++;
            } else if (ANA_Result.NEGATIVE.equals(result.getPositivity())) {
                neg++;
            }
            all++;
            column.setCellValue(result.getPositivity().name());
        } //make sure all fis has not-null pn result

        //            column = row.createCell(colIndex++);
        //            if (result.positivity30 == null) {
        //                column.setCellValue("Null Result");
        //            } else {
        //                if (ANA_Result.POSITIVE.equals(result.positivity30)) {
        ////                    pos++;
        //                } else if (ANA_Result.NEGATIVE.equals(result.positivity30)) {
        ////                    neg++;
        //                }
        ////                all++;
        //                column.setCellValue(result.positivity30.name());
        //            }  //make sure all fis has not-null pn result
        //            column = row.createCell(colIndex++);
        //            if (result.positivityCombined == null) {
        //                column.setCellValue("Null Result");
        //            } else {
        //                if (ANA_Result.POSITIVE.equals(result.positivityCombined)) {
        ////                    pos++;
        //                } else if (ANA_Result.NEGATIVE.equals(result.positivityCombined)) {
        ////                    neg++;
        //                }
        ////                all++;
        //                column.setCellValue(result.positivityCombined.name());
        //            }  //make sure all fis has not-null pn result

        column = row.createCell(colIndex++);
        column.setCellValue(result.getFirstPlateSignal());
        //            if(result.getFirstPlateSignal()<0){
        //                column.setCellValue("ROI exception: unable to get signal for this sample");
        //            }else{
        //                column.setCellValue(result.getFirstPlateSignal());
        //            }
        column = row.createCell(colIndex++); //warning msg concat mthd; merge plateErr to sampErr
        column.setCellValue(result.concatWarningMsgs());
    }
    if (rowIndex - rowCount == 2) {
        for (int i = 0; i < totalCol; i++) {
            sheet.autoSizeColumn(i);
        }
    } else {
        System.out
                .println((rowIndex - 2) + " records are writen into file while " + rowCount + " are expected");
    }
    row = sheet.createRow(rowIndex++); //total
    colIndex = 0;
    column = row.createCell(colIndex++);
    column.setCellValue("all samples");
    column = row.createCell(colIndex++);
    column.setCellValue(all);
    column = row.createCell(colIndex++);
    column.setCellValue("positive samples");
    column = row.createCell(colIndex++);
    column.setCellValue(pos);
    column = row.createCell(colIndex++);
    column.setCellValue("negative samples");
    column = row.createCell(colIndex++);
    column.setCellValue(neg);

    column = row.createCell(colIndex++);
    column = row.createCell(colIndex++);
    column = row.createCell(colIndex++);
    column.setCellValue("PosCtrl");
    column = row.createCell(colIndex++);
    column.setCellValue(plate.getPosCtrl().getFirstPlateSignal());
    column = row.createCell(colIndex++);
    column.setCellValue("NegCtrl");
    column = row.createCell(colIndex++);
    column.setCellValue(plate.getNegCtrlSignal());

    FileOutputStream fos = null;
    try {
        fos = new FileOutputStream(outputFile.getAbsolutePath());//,true
        excelFile.write(fos);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if (fos != null) {
            try {
                fos.close();
            } catch (IOException ex) {
                Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}

From source file:tools.IOHelper.java

public static void plate2ResultSheet(ANAPlate plate, File outputFolder) throws IOException {

    File outputFile = new File(outputFolder, plate.getPlateId() + "_" + TIME + ".xlsx");
    Workbook excelFile = null;
    if (outputFile.exists()) {
        outputFile.delete();/*from ww w  .  j a  va2 s  .  c om*/
    }
    outputFile.createNewFile();
    excelFile = new XSSFWorkbook();
    if (excelFile == null) {
        throw new RuntimeException("fail to create the xlsx file");
    }
    int rowCount = plate.getSampleNumber(); //not including the 2 control samples + "_"+time
    String sheetName = plate.getPlateId();
    //create a working sheet 
    Sheet sheet = excelFile.createSheet(sheetName);
    //starting row & col
    int rowIndex = 0;
    int colIndex = 0;
    int totalCol = 0;
    int pos = 0, neg = 0, all = 0;
    XSSFFont fontTitle = (XSSFFont) excelFile.createFont();
    fontTitle.setFontHeightInPoints((short) 10);
    fontTitle.setFontName("Arial");
    fontTitle.setColor(IndexedColors.GREEN.getIndex());
    fontTitle.setBold(true);
    fontTitle.setItalic(false);
    XSSFCellStyle styleTitle = (XSSFCellStyle) excelFile.createCellStyle();
    styleTitle.setAlignment(CellStyle.ALIGN_CENTER);
    styleTitle.setFont(fontTitle);
    Cell cell0 = sheet.createRow(rowIndex++).createCell(0);
    cell0.setCellValue(plate.getPlateId() + " Summary"); //title
    cell0.setCellStyle(styleTitle);

    //        //optional set Cell Style
    //        CellStyle styleTitle = null;
    //        CellStyle style = null;

    //            add column:Sample,Chip Location,Signal,Positivity,sample titer,plate titer, pattern, No of Cells, enableWatershed,comment
    Row row = sheet.createRow(rowIndex++); //names
    Cell column = row.createCell(colIndex++);
    column.setCellValue("Sample ID");
    totalCol++;
    column = row.createCell(colIndex++);
    column.setCellValue("Chip Location");
    totalCol++;
    column = row.createCell(colIndex++);
    column.setCellValue("Signal");
    totalCol++;
    column = row.createCell(colIndex++);
    column.setCellValue("Positivity");
    totalCol++;

    column = row.createCell(colIndex++);
    column.setCellValue("Sample Titer");
    totalCol++;
    column = row.createCell(colIndex++);
    column.setCellValue("Plate Titer");
    totalCol++;

    column = row.createCell(colIndex++);
    column.setCellValue("Pattern");
    totalCol++;
    column = row.createCell(colIndex++);
    column.setCellValue("Number of Cells");
    totalCol++;
    column = row.createCell(colIndex++);
    column.setCellValue("Comments");
    totalCol++;

    for (ANATestResult result : plate.getTestResultList()) {
        row = sheet.createRow(rowIndex++); //data
        colIndex = 0;
        column = row.createCell(colIndex++);
        column.setCellValue(result.getJulien_barcode());
        column = row.createCell(colIndex++);
        column.setCellValue(result.getPillarPosition());
        column = row.createCell(colIndex++);
        column.setCellValue(result.getSecondPlateSignal());
        column = row.createCell(colIndex++);
        if (result.getPositivity() == null) {
            column.setCellValue("Null Result");
        } else {
            if (ANA_Result.POSITIVE.equals(result.getPositivity())) {
                pos++;
            } else if (ANA_Result.NEGATIVE.equals(result.getPositivity())) {
                neg++;
            }
            all++;
            column.setCellValue(result.getPositivity().name());
        } //make sure all fis has not-null pn result

        column = row.createCell(colIndex++);
        if (result.getTiter() != null) {
            column.setCellValue(result.getTiter().name());
        }
        column = row.createCell(colIndex++);
        if (plate.getPosCtrl().getTiter() != null) {
            column.setCellValue(plate.getPosCtrl().getTiter().name());
        }
        //pattern, No of Cells, enableWatershed,comment
        column = row.createCell(colIndex++);
        if (result.getPattern() != null) {
            column.setCellValue(result.getPattern().name());
        }
        column = row.createCell(colIndex++);
        column.setCellValue(result.cellCount());
        column = row.createCell(colIndex++); //warning msg concat mthd; merge plateErr to sampErr
        column.setCellValue(result.concatWarningMsgs());
    }
    if (rowIndex - rowCount == 2) {
        for (int i = 0; i < totalCol; i++) {
            sheet.autoSizeColumn(i);
        }
    } else {
        System.out
                .println((rowIndex - 2) + " records are writen into file while " + rowCount + " are expected");
    }
    row = sheet.createRow(rowIndex++); //total
    colIndex = 0;
    column = row.createCell(colIndex++);
    column.setCellValue("all samples");
    column = row.createCell(colIndex++);
    column.setCellValue(all);
    column = row.createCell(colIndex++);
    column.setCellValue("positive samples");
    column = row.createCell(colIndex++);
    column.setCellValue(pos);
    column = row.createCell(colIndex++);
    column.setCellValue("negative samples");
    column = row.createCell(colIndex++);
    column.setCellValue(neg);

    FileOutputStream fos = null;
    try {
        fos = new FileOutputStream(outputFile.getAbsolutePath());//,true
        excelFile.write(fos);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if (fos != null) {
            try {
                fos.close();
            } catch (IOException ex) {
                Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}