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

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

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

Introduction

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

Prototype

CreationHelper getCreationHelper();

Source Link

Document

Returns an object that handles instantiating concrete classes of the various instances one needs for HSSF and XSSF.

Usage

From source file:pl.softech.knf.ofe.opf.members.xls.export.XlsMembersWritter.java

License:Apache License

private void buildHeader(final List<Date> dates, final Sheet sheet, final int rowIdx, final int colIdx) {

    final Workbook wb = sheet.getWorkbook();
    final CreationHelper createHelper = wb.getCreationHelper();
    final CellStyle dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm-yyyy"));

    Row row = sheet.createRow(rowIdx);/*ww w.  j  a v  a2s .c  om*/

    Cell cell = row.createCell(colIdx);
    cell.setCellValue("Open Pension Fund");

    final CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    cellStyle.setFont(createHeaderFont(wb, (short) 12));
    cell.setCellStyle(cellStyle);

    cell = row.createCell(colIdx + 1);
    cell.setCellValue("Number of members");
    cell.setCellStyle(cellStyle);
    row = sheet.createRow(rowIdx + 1);
    sheet.addMergedRegion(new CellRangeAddress(// merge Open Pension Fund
            rowIdx, // first row (0-based)
            rowIdx + 1, // last row (0-based)
            colIdx, // first column (0-based)
            colIdx // last column (0-based)
    ));

    sheet.addMergedRegion(new CellRangeAddress(// merge Number of members
            rowIdx, // first row (0-based)
            rowIdx, // last row (0-based)
            colIdx + 1, // first column (0-based)
            colIdx + dates.size() // last column (0-based)
    ));

    int colIt = colIdx + 1;
    for (final Date date : dates) {
        cell = row.createCell(colIt++);
        cell.setCellValue(date);
        cell.setCellStyle(dateCellStyle);
    }

}

From source file:poi.xssf.usermodel.examples.CellComments.java

License:Apache License

public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook();

    CreationHelper factory = wb.getCreationHelper();

    Sheet sheet = wb.createSheet();//from   ww  w. j  a  v a  2s  . c om

    Cell cell1 = sheet.createRow(3).createCell(5);
    cell1.setCellValue("F4");

    Drawing drawing = sheet.createDrawingPatriarch();

    ClientAnchor anchor = factory.createClientAnchor();

    Comment comment1 = drawing.createCellComment(anchor);
    RichTextString str1 = factory.createRichTextString("Hello, World!");
    comment1.setString(str1);
    comment1.setAuthor("Apache POI");
    cell1.setCellComment(comment1);

    Cell cell2 = sheet.createRow(2).createCell(2);
    cell2.setCellValue("C3");

    Comment comment2 = drawing.createCellComment(anchor);
    RichTextString str2 = factory.createRichTextString("XSSF can set cell comments");
    //apply custom font to the text in the comment
    Font font = wb.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 14);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(IndexedColors.RED.getIndex());
    str2.applyFont(font);

    comment2.setString(str2);
    comment2.setAuthor("Apache POI");
    comment2.setColumn(2);
    comment2.setRow(2);

    String fname = "comments.xlsx";
    FileOutputStream out = new FileOutputStream(fname);
    wb.write(out);
    out.close();

}

From source file:poi.xssf.usermodel.examples.WorkingWithPictures.java

License:Apache License

public static void main(String[] args) throws IOException {

    //create a new workbook
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    CreationHelper helper = wb.getCreationHelper();

    //add a picture in this workbook.
    InputStream is = new FileInputStream(args[0]);
    byte[] bytes = IOUtils.toByteArray(is);
    is.close();/*  w  w w . j  av  a2  s . c  o m*/
    int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);

    //create sheet
    Sheet sheet = wb.createSheet();

    //create drawing
    Drawing drawing = sheet.createDrawingPatriarch();

    //add a picture shape
    ClientAnchor anchor = helper.createClientAnchor();
    anchor.setCol1(1);
    anchor.setRow1(1);
    Picture pict = drawing.createPicture(anchor, pictureIdx);

    //auto-size picture
    pict.resize(2);

    //save workbook
    String file = "picture.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream fileOut = new FileOutputStream(file);
    wb.write(fileOut);
    fileOut.close();
}

From source file:qmul.align.AlignmentTester.java

License:Open Source License

/**
 * Process a single dialogue// ww w.  j a  v a2  s.c om
 * 
 * @param d
 *            the dialogue to process
 * @param wb
 *            the XLS workbook to write to, or null not to bother
 * @return a list of {@link Double} scores, one per {@link DialogueWindower} step (e.g. dialogue turn)
 */
public List<Double> processDialogue(Dialogue d, Workbook wb, HashMap<String, ArrayList<Double>> speakerScores,
        HashMap<String, String> originalSpks, HashMap<String, ArrayList<Double>> speakerN,
        MetricsMap spkMetrics, MetricsMap totMetrics, Workbook wbcounts,
        HashMap<String, HashMap<Object, Integer>> allCounts,
        HashMap<String, HashMap<Object, Integer>> commonCounts, HashMap<Object, Integer> diaAllCounts,
        HashMap<Object, Integer> diaCommonCounts) {

    CreationHelper creationHelper = wb.getCreationHelper();

    win.setDialogue(d);
    sim.reset();
    ArrayList<DialogueSpeaker> spks = new ArrayList<DialogueSpeaker>(d.getSpeakers());
    Collections.sort(spks);
    Sheet sheet = (wb == null ? null : wb.createSheet(d.getId().replaceAll(":", "-")));
    Sheet sheetcounts = (wbcounts == null ? null : wbcounts.createSheet(d.getId().replaceAll(":", "-")));
    int iRow = 0;
    if (sheet != null) {
        iRow = writeSheetHeader(creationHelper, sheet, iRow, d, spks);
    }
    int iCRow = 0;
    if (sheetcounts != null) {
        iCRow = writeSheetHeader(creationHelper, sheet, iCRow, d, spks);
    }
    ArrayList<Double> scores = new ArrayList<Double>();
    HashSet<X> counted = new HashSet<X>();
    do {
        List<X> left = win.getLeftWindow();
        Collections.reverse(left); // windowers return things in dialogue order: we'll look progressively backwards
        List<X> right = win.getRightWindow();
        // System.out.println("lengthS " + left.size() + " " + right.size());
        double score = 0.0;
        double n = 0.0;
        for (X r : right) {
            String spkKey = makeSpkKey(r.getSpeaker(), d);
            String originalSpkKey = "";
            if (r.getOriginalSpeaker() != null) {
                originalSpkKey = r.getOriginalSpeaker().getId();
                // fix for the fact that BNC speakers are not currently given SUBdialogue ID in their ID - TODO
                // change that?
                Dialogue od = r.getOriginalDialogue();
                if ((od == null) && (r instanceof DialogueSentence)) {
                    od = ((DialogueSentence) r).getTurn().getOriginalDialogue();
                }
                String originalDia;
                if (od != null) {
                    originalDia = od.getId();
                } else {
                    originalDia = d.getId().replaceFirst("-\\d+$", "");
                }
                if (!originalSpkKey.contains(originalDia)) {
                    if (!originalDia.contains(":")) {
                        throw new RuntimeException("can't find super-dialogue, no : in " + originalDia);
                    }
                    String originalSuperDia = originalDia.substring(0, originalDia.lastIndexOf(":"));
                    if (originalSpkKey.contains(originalSuperDia)) {
                        originalSpkKey = originalSpkKey.replace(originalSuperDia, originalDia);
                    } else {
                        throw new RuntimeException("spk key without super-dialogue " + spkKey + ", "
                                + originalSpkKey + ", " + originalDia);
                    }
                }
            }
            Row row = (wb == null ? null : sheet.createRow(iRow++));
            int iCol = 0;
            Cell cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_STRING));
            if (cell != null) {
                cell.setCellValue(creationHelper.createRichTextString(r.getSpeaker().getId()));
                cell = row.createCell(iCol++, Cell.CELL_TYPE_STRING);
                cell.setCellValue(creationHelper.createRichTextString(originalSpkKey));
                // cell = row.createCell(iCol++, Cell.CELL_TYPE_STRING);
                // cell.setCellValue(creationHelper.createRichTextString(r.getId()));
                cell = row.createCell(iCol++, Cell.CELL_TYPE_STRING);
                cell.setCellValue(creationHelper.createRichTextString(r.toString()));
                row.setHeightInPoints(12);
                sheet.setColumnWidth(iCol - 1, 2560);
            }
            if (!speakerScores.containsKey(spkKey)) {
                speakerScores.put(spkKey, new ArrayList<Double>());
                speakerN.put(spkKey, new ArrayList<Double>());
                originalSpks.put(spkKey, originalSpkKey);
                for (int i = 0; i < win.getLeftWindowSize(); i++) {
                    speakerScores.get(spkKey).add(0.0);
                    speakerN.get(spkKey).add(0.0);
                }
                Boolean isTurns = null;
                if (left.size() > 0) {
                    isTurns = (left.get(0) instanceof DialogueTurn);
                } else if (right.size() > 0) {
                    isTurns = (right.get(0) instanceof DialogueTurn);
                }
                spkMetrics.setNumUnits(spkKey, 0);
                totMetrics.setNumUnits(d.getId(), (isTurns ? d.numTurns() : d.numSents()));
                spkMetrics.setNumWords(spkKey, 0);
                totMetrics.setNumWords(d.getId(), d.numWords());
                spkMetrics.setNumTokens(spkKey, 0);
                totMetrics.setNumTokens(d.getId(), d.numTokens());
            }
            int iLeft = 0;
            double offset = Double.NaN;
            boolean gotOffset = false;
            for (X l : left) {
                double s = sim.similarity(l, r);
                // System.out.println("Siml = " + s + " for l:" + l.getId() + " r:" + r.getId());
                if ((l.getOriginalId() != null) && (r.getOriginalId() != null)
                        && l.getOriginalId().equals(r.getOriginalId())) {
                    System.out.println("Equal IDs sim = " + s + " for l:" + l.getId() + " " + l.getOriginalId()
                            + " r:" + r.getId() + " " + r.getOriginalId() + " d " + d.getId() + " nturns "
                            + d.numTurns());
                }
                if (wbcounts != null) {
                    if (!counted.contains(l)) {
                        MapUtil.addAll(diaAllCounts, sim.rawCountsA());
                        MapUtil.addAll(allCounts.get(""), sim.rawCountsA());
                        MapUtil.addAll(allCounts.get(d.getGenre()), sim.rawCountsA());
                        counted.add(l);
                    }
                    if (!counted.contains(r)) {
                        MapUtil.addAll(diaAllCounts, sim.rawCountsB());
                        MapUtil.addAll(allCounts.get(""), sim.rawCountsB());
                        MapUtil.addAll(allCounts.get(d.getGenre()), sim.rawCountsB());
                        counted.add(r);
                    }
                    MapUtil.addAll(diaCommonCounts, sim.rawCountsAB());
                    MapUtil.addAll(commonCounts.get(""), sim.rawCountsAB());
                    MapUtil.addAll(commonCounts.get(d.getGenre()), sim.rawCountsAB());
                }
                cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC));
                if (cell != null) {
                    cell.setCellValue(s);
                }
                score += s;
                n++;
                speakerScores.get(spkKey).set(iLeft, speakerScores.get(spkKey).get(iLeft) + s);
                speakerN.get(spkKey).set(iLeft, speakerN.get(spkKey).get(iLeft) + 1);
                if (!win.getClass().toString().contains("AllOther")) { // for "all other" windowers, actually
                    // average over "window"
                    iLeft++;
                }
                if (!gotOffset) {
                    offset = r.getStartTime() - l.getEndTime();
                    gotOffset = true;
                    // if (!Double.isNaN(offset)) {
                    // System.out.println("Offset = " + offset + " for l:" + l.getId() + " r:" + r.getId());
                    // }
                }
            }
            // print number sents/words/tokens
            iCol += (win.getLeftWindowSize() - left.size() + 1);
            if (wb != null) { // if we are writing to a workbook
                cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC));
                cell.setCellValue(r instanceof DialogueTurn ? ((DialogueTurn) r).getSents().size() : 1);
                cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC));
                cell.setCellValue(r.numWords());
                cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC));
                cell.setCellValue(r.numTokens());
            }
            iCol += 1;
            if (!Double.isNaN(offset)) {
                cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC));
                cell.setCellValue(offset);
            } else {
                iCol++;
            }
            double wordRate = (double) (r.getEndTime() - r.getStartTime()) / (double) r.numWords();
            if (r.numWords() == 0) {
                wordRate = Double.NaN; // on some OSs this doesn't happen in the calc above
            }
            if (!Double.isNaN(wordRate)) {
                cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC));
                cell.setCellValue(wordRate);
            } else {
                iCol++;
            }
            // make sure we counted this one - the first one can get missed if leftWindow empty
            if ((wbcounts != null) && !counted.contains(r)) {
                sim.similarity(r, r);
                MapUtil.addAll(diaAllCounts, sim.rawCountsA());
                MapUtil.addAll(allCounts.get(""), sim.rawCountsA());
                MapUtil.addAll(allCounts.get(d.getGenre()), sim.rawCountsA());
                counted.add(r);
            }
            spkMetrics.setNumUnits(spkKey, spkMetrics.getNumUnits(spkKey) + 1);
            spkMetrics.setNumWords(spkKey, spkMetrics.getNumWords(spkKey) + r.numWords());
            spkMetrics.setNumTokens(spkKey, spkMetrics.getNumTokens(spkKey) + r.numTokens());
            if (!Double.isNaN(offset)) {
                spkMetrics.setTurnOffset(spkKey, spkMetrics.getTurnOffset(spkKey) + offset);
                spkMetrics.setNumTurnOffsets(spkKey, spkMetrics.getNumTurnOffsets(spkKey) + 1);
                totMetrics.setTurnOffset(d.getId(), totMetrics.getTurnOffset(d.getId()) + offset);
                totMetrics.setNumTurnOffsets(d.getId(), totMetrics.getNumTurnOffsets(d.getId()) + 1);
            }
            if (!Double.isNaN(wordRate)) {
                spkMetrics.setWordRate(spkKey, spkMetrics.getWordRate(spkKey) + wordRate);
                spkMetrics.setNumWordRates(spkKey, spkMetrics.getNumWordRates(spkKey) + 1);
                totMetrics.setWordRate(d.getId(), totMetrics.getWordRate(d.getId()) + wordRate);
                totMetrics.setNumWordRates(d.getId(), totMetrics.getNumWordRates(d.getId()) + 1);
            }
        }
        scores.add((n == 0.0) ? 0.0 : (score / n));
    } while (win.advance());
    if (wb != null) {
        iRow++;
        for (DialogueSpeaker spk : spks) {
            String spkKey = makeSpkKey(spk, d);
            Row row = sheet.createRow(iRow++);
            int iCol = 0;
            row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                    .setCellValue(creationHelper.createRichTextString(spk.getId()));
            row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                    .setCellValue(creationHelper.createRichTextString(originalSpks.get(spkKey)));
            row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                    .setCellValue(creationHelper.createRichTextString("Mean"));
            for (int i = 0; i < win.getLeftWindowSize(); i++) {
                if (speakerN.get(spkKey).get(i) > 0) {
                    row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)
                            .setCellValue(speakerScores.get(spkKey).get(i) / speakerN.get(spkKey).get(i));
                } else {
                    iCol++;
                }
                // System.out
                // .println("score " + i + " for speaker " + spkKey + "=" + speakerScores.get(spkKey).get(i));
                // System.out.println("N " + i + " for speaker " + spkKey + "=" + speakerN.get(spkKey).get(i));
                // System.out.println("mean " + i + " for speaker " + spkKey + "="
                // + (speakerScores.get(spkKey).get(i) / speakerN.get(spkKey).get(i)));
            }
            iCol++;
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(
                    (double) spkMetrics.getNumUnits(spkKey) / (double) spkMetrics.getNumUnits(spkKey));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(
                    (double) spkMetrics.getNumWords(spkKey) / (double) spkMetrics.getNumUnits(spkKey));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(
                    (double) spkMetrics.getNumTokens(spkKey) / (double) spkMetrics.getNumUnits(spkKey));
            iCol++;
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(
                    (double) spkMetrics.getTurnOffset(spkKey) / (double) spkMetrics.getNumTurnOffsets(spkKey));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(
                    (double) spkMetrics.getWordRate(spkKey) / (double) spkMetrics.getNumWordRates(spkKey));
        }
    }
    if (wbcounts != null) {
        iCRow++;
        ArrayList<Object> keys = new ArrayList<Object>(diaAllCounts.keySet());
        Collections.sort(keys, new DescendingComparator<Object>(diaAllCounts));
        for (Object key : keys) {
            Row row = sheetcounts.createRow(iCRow++);
            int iCol = 0;
            row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                    .setCellValue(creationHelper.createRichTextString(key.toString()));
            Cell cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC);
            if (diaAllCounts.get(key) != null) {
                cell.setCellValue(diaAllCounts.get(key));
            }
            cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC);
            if (diaCommonCounts.get(key) != null) {
                cell.setCellValue(diaCommonCounts.get(key));
            }
        }
    }
    return scores;
}

From source file:qmul.align.AlignmentTester.java

License:Open Source License

/**
 * Print a summary sheet on the (gulp) excel spreadsheet
 * /*from ww  w .j  a v a  2s .  c o m*/
 * @param wb
 * @param sheetName
 * @param speakerScores
 * @param originalSpks
 * @param speakerN
 */
private void printSummarySheet(Workbook wb, String sheetName, HashMap<String, ArrayList<Double>> speakerScores,
        HashMap<String, String> originalSpks, HashMap<String, ArrayList<Double>> speakerN,
        MetricsMap spkMetrics, MetricsMap totMetrics, boolean pairedCorpus) {

    CreationHelper creationHelper = wb.getCreationHelper();
    sheetName = (sheetName == null ? "Summary" : shorten(sheetName));
    System.out.println("Checking workbook " + wb + " for sheet " + sheetName);
    Sheet sheet = wb.getSheet(sheetName);
    if (sheet != null) {
        System.out.println("Exists, removing sheet " + sheetName);
        wb.removeSheetAt(wb.getSheetIndex(sheet));
    }
    sheet = wb.createSheet(sheetName);
    wb.setSheetOrder(sheetName, 0);
    int iRow = 0;
    // first general identifying stuff
    Row row = sheet.createRow(iRow++);
    row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Corpus"));
    row.createCell(1, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString(getCorpus().getId()));
    row = sheet.createRow(iRow++);
    row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Windower"));
    row.createCell(1, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString(getWin().toString()));
    row = sheet.createRow(iRow++);
    row.createCell(0, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Similarity Measure"));
    row.createCell(1, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString(getSim().toString()));
    // now header
    row = sheet.createRow(iRow++);
    row = sheet.createRow(iRow++);
    int iCol = 0;
    row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Speaker"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Genre"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Orig Speaker"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Orig Genre"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Speaker #units"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Dialogue #units"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Speaker #words"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Dialogue #words"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Speaker #tokens"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Dialogue #tokens"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Speaker avg offset"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Dialogue avg offset"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Speaker avg wordrate"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Dialogue avg wordrate"));
    iCol++;
    for (int i = 0; i < getWin().getLeftWindowSize(); i++) {
        row.createCell(i + iCol, Cell.CELL_TYPE_STRING)
                .setCellValue(creationHelper.createRichTextString("Mean i-" + (i + 1)));
    }
    // now means per speaker
    List<String> spks = new ArrayList<String>(speakerScores.keySet());
    Collections.sort(spks);
    List<Double> means = new ArrayList<Double>();
    List<Double> nums = new ArrayList<Double>();
    for (int i = 0; i < getWin().getLeftWindowSize(); i++) {
        means.add(0.0);
        nums.add(0.0);
    }
    int nAll = 0;
    int nMatch = 0;
    for (String spk : spks) {
        // System.out.println("org chk [" + originalSpks.get(spk) + "][" + spk + "]");
        boolean matching = false;
        if ((originalSpks.get(spk) != null) && originalSpks.get(spk).contains(":")) {
            int li = originalSpks.get(spk).lastIndexOf(":");
            String pre = originalSpks.get(spk).substring(0, li);
            String suf = originalSpks.get(spk).substring(li);
            matching = spk.startsWith(pre) && spk.endsWith(suf);
        }
        nAll++;
        if (!pairedCorpus || matching) {
            nMatch++;
            // System.out.println("match " + pre + " " + suf);
            row = sheet.createRow(iRow++);
            iCol = 0;
            String dId = spk.replaceFirst("(.*)_.*", "$1");
            row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                    .setCellValue(creationHelper.createRichTextString(spk));
            row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(
                    creationHelper.createRichTextString(corpus.getGenreMap().get(spk.split(":")[0])));
            row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                    .setCellValue(creationHelper.createRichTextString(originalSpks.get(spk)));
            row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper
                    .createRichTextString(corpus.getGenreMap().get(originalSpks.get(spk).split(":")[0])));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(spkMetrics.getNumUnits(spk));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(totMetrics.getNumUnits(dId));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(spkMetrics.getNumWords(spk));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(totMetrics.getNumWords(dId));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(spkMetrics.getNumTokens(spk));
            row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(totMetrics.getNumTokens(dId));
            if (Double.isNaN(spkMetrics.getTurnOffset(spk)) || spkMetrics.getNumTurnOffsets(spk) == 0) {
                iCol++;
            } else {
                row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(
                        spkMetrics.getTurnOffset(spk) / (double) spkMetrics.getNumTurnOffsets(spk));
            }
            if (Double.isNaN(totMetrics.getTurnOffset(dId)) || totMetrics.getNumTurnOffsets(dId) == 0) {
                iCol++;
            } else {
                row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue(
                        totMetrics.getTurnOffset(dId) / (double) totMetrics.getNumTurnOffsets(dId));
            }
            if (Double.isNaN(spkMetrics.getWordRate(spk)) || spkMetrics.getNumWordRates(spk) == 0) {
                iCol++;
            } else {
                row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)
                        .setCellValue(spkMetrics.getWordRate(spk) / (double) spkMetrics.getNumWordRates(spk));
            }
            if (Double.isNaN(totMetrics.getWordRate(dId)) || totMetrics.getNumWordRates(dId) == 0) {
                iCol++;
            } else {
                row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)
                        .setCellValue(totMetrics.getWordRate(dId) / (double) totMetrics.getNumWordRates(dId));
            }
            iCol++;
            for (int i = 0; i < speakerScores.get(spk).size(); i++) {
                if (speakerN.get(spk).get(i) > 0.0) {
                    double mean = speakerScores.get(spk).get(i) / speakerN.get(spk).get(i);
                    row.createCell(i + iCol, Cell.CELL_TYPE_NUMERIC).setCellValue(mean);
                    means.set(i, means.get(i) + mean);
                    nums.set(i, nums.get(i) + 1);
                }
            }
        }
    }
    System.out.println("Matched " + nMatch + " of " + nAll);
    // and a final row for overall means
    row = sheet.createRow(iRow++);
    iCol = 14;
    row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Overall"));
    for (int i = 0; i < getWin().getLeftWindowSize(); i++) {
        means.set(i, means.get(i) / nums.get(i));
        row.createCell(i + iCol, Cell.CELL_TYPE_NUMERIC).setCellValue(means.get(i));
    }
}

From source file:qmul.align.AlignmentTester.java

License:Open Source License

/**
 * Print a summary sheet on the (gulp) excel spreadsheet
 */// ww  w  . j a  v  a2  s  .  c  o  m
private void printSummaryCountSheet(Workbook wb, String sheetName,
        HashMap<String, HashMap<Object, Integer>> allCounts,
        HashMap<String, HashMap<Object, Integer>> commonCounts) {

    CreationHelper creationHelper = wb.getCreationHelper();
    sheetName = (sheetName == null ? "Summary" : shorten(sheetName));
    System.out.println("Checking workbook " + wb + " for sheet " + sheetName);
    Sheet sheet = wb.getSheet(sheetName);
    if (sheet != null) {
        System.out.println("Exists, removing sheet " + sheetName);
        wb.removeSheetAt(wb.getSheetIndex(sheet));
    }
    sheet = wb.createSheet(sheetName);
    wb.setSheetOrder(sheetName, 0);
    int iRow = 0;
    // first general identifying stuff
    Row row = sheet.createRow(iRow++);
    row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Corpus"));
    row.createCell(1, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString(getCorpus().getId()));
    row = sheet.createRow(iRow++);
    row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Windower"));
    row.createCell(1, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString(getWin().toString()));
    row = sheet.createRow(iRow++);
    row.createCell(0, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Similarity Measure"));
    row.createCell(1, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString(getSim().toString()));
    // now header
    row = sheet.createRow(iRow++);
    row = sheet.createRow(iRow++);
    int iCol = 0;
    row.createCell(iCol++, Cell.CELL_TYPE_STRING).setCellValue(creationHelper.createRichTextString("Type"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Overall count"));
    row.createCell(iCol++, Cell.CELL_TYPE_STRING)
            .setCellValue(creationHelper.createRichTextString("Common count"));
    for (String genre : allCounts.keySet()) {
        if (genre.isEmpty())
            continue;
        row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                .setCellValue(creationHelper.createRichTextString(genre + " overall count"));
        row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                .setCellValue(creationHelper.createRichTextString(genre + " common count"));
    }
    ArrayList<Object> keys = new ArrayList<Object>(allCounts.get("").keySet());
    Collections.sort(keys, new DescendingComparator<Object>(allCounts.get("")));
    for (Object key : keys) {
        row = sheet.createRow(iRow++);
        iCol = 0;
        row.createCell(iCol++, Cell.CELL_TYPE_STRING)
                .setCellValue(creationHelper.createRichTextString(key.toString()));
        Cell cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC);
        if (allCounts.get("").get(key) != null) {
            cell.setCellValue(allCounts.get("").get(key));
        }
        cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC);
        if (commonCounts.get("").get(key) != null) {
            cell.setCellValue(commonCounts.get("").get(key));
        }
        for (String genre : allCounts.keySet()) {
            if (genre.isEmpty())
                continue;
            cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC);
            if (allCounts.get(genre).get(key) != null) {
                cell.setCellValue(allCounts.get(genre).get(key));
            }
            cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC);
            if (commonCounts.get(genre).get(key) != null) {
                cell.setCellValue(commonCounts.get(genre).get(key));
            }
        }
    }
}

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

License:Open Source License

public final Workbook convert(Workbook wb) {
    Sheet sheet = wb.createSheet("Liste echipe");

    Row row = sheet.createRow(0);// w w  w  .  ja  va  2  s  .com
    row.createCell(0).setCellValue("ID");
    row.createCell(1).setCellValue("Email manager");
    row.createCell(2).setCellValue("Nume manager");
    row.createCell(3).setCellValue("Comun\u04d1 manager");
    row.createCell(4).setCellValue("Jude\u0163 manager");
    row.createCell(5).setCellValue("Cod acces");
    row.createCell(6).setCellValue("Nume");
    row.createCell(7).setCellValue("Num\u04d1r membri");
    row.createCell(8).setCellValue("Multi-manager");
    row.createCell(9).setCellValue("Nume organiza\u0163ie");
    row.createCell(10).setCellValue("Comun\u04d1 organiza\u0163ie");
    row.createCell(11).setCellValue("Jude\u0163 organiza\u0163ie");
    row.createCell(12).setCellValue("Tip organiza\u0163ie");
    row.createCell(13).setCellValue("Nume persoan\u04d1 de contact");
    row.createCell(14).setCellValue("Email persoan\u04d1 de contact");
    row.createCell(15).setCellValue("Num\u04d1r membri organiza\u0163ie");
    row.createCell(16).setCellValue("Num\u04d1r GPS");
    row.createCell(17).setCellValue("Transport");
    row.createCell(18).setCellValue("Num\u04d1r saci");
    row.createCell(19).setCellValue("Num\u04d1r m\u04d1nu\015Fi");
    row.createCell(20).setCellValue("Num\u04d1r lope\u0163i");
    row.createCell(21).setCellValue("Utilaje");
    row.createCell(22).setCellValue("Num\u04d1r mormane alocate");
    row.createCell(23).setCellValue("List\u04d1 mormane alocate");
    row.createCell(24).setCellValue("Sum\u04d1 volum mormane alocate");
    row.createCell(25).setCellValue("Activitati");
    row.createCell(26).setCellValue("Data inregistrarii");

    int i = 0;
    CreationHelper createHelper = wb.getCreationHelper();
    for (Team team : teams) {
        if (team.getOrganizationMembers() == null || team.getOrganizationMembers().size() == 0) {
            i++;
            row = sheet.createRow(i);
            teamHeader(row, team);
            teamFooter(row, team);
            continue;
        }
        for (Organization org : team.getOrganizationMembers()) {
            i++;
            row = sheet.createRow(i);
            teamHeader(row, team);
            organization(row, org);
            teamFooter(row, team);
        }

        if (team.getTeamManager() != null) {
            StringBuffer ab = new StringBuffer();

            List<User.Activity> activities = team.getTeamManager().getActivities();
            if (activities != null && activities.size() > 0) {
                for (User.Activity activity : activities)

                    if (activity != null)
                        if (activity.getReportName() != null)
                            ab.append(activity.getReportName() + ", ");
                        else
                            ab.append("  " + ", ");

                if (ab.length() > 1)
                    row.createCell(25).setCellValue(ab.substring(0, ab.length() - 2));
                else
                    row.createCell(25).setCellValue(" ");
            }
            if (team.getTeamManager().getRecordDate() != null) {
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
                Cell dateCell = row.createCell(26);
                dateCell.setCellValue(team.getTeamManager().getRecordDate());
                dateCell.setCellStyle(cellStyle);
            }

        }

    }

    return wb;
}

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

License:Open Source License

public final Workbook convert(Workbook wb) {
    Sheet sheet = wb.createSheet("Utilizatori");
    CreationHelper createHelper = wb.getCreationHelper();
    teams = new ArrayList<Team>();

    Row row = sheet.createRow(0);/*from  ww  w .  j  a  v  a 2  s  .c  o  m*/
    row.createCell(0).setCellValue("Prenume");
    row.createCell(1).setCellValue("Nume");
    row.createCell(2).setCellValue("Email");
    row.createCell(3).setCellValue("Telefon");
    row.createCell(4).setCellValue("Rol");
    row.createCell(5).setCellValue("Jude\u0163");
    row.createCell(6).setCellValue("Oras");
    row.createCell(7).setCellValue("Data \u00eenregistr\u04d1rii");
    row.createCell(8).setCellValue("ID");
    row.createCell(9).setCellValue("Nr. mormane");
    row.createCell(10).setCellValue("Nr. zone");
    row.createCell(11).setCellValue("Activitate");

    for (int i = 0; i < users.size(); i++) {
        row = sheet.createRow(i + 1);
        User user = users.get(i);
        if (user == null)
            continue;

        row.createCell(0).setCellValue(user.getFirstName());
        row.createCell(1).setCellValue(user.getLastName());
        row.createCell(2).setCellValue(user.getEmail());
        row.createCell(3).setCellValue(user.getPhone());
        row.createCell(4).setCellValue(user.getRole());
        row.createCell(5).setCellValue(user.getCounty());
        row.createCell(6).setCellValue(user.getTown());

        if (user.getRecordDate() != null) {
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
            Cell dateCell = row.createCell(7);
            dateCell.setCellValue(user.getRecordDate());
            dateCell.setCellStyle(cellStyle);
        }

        row.createCell(8, Cell.CELL_TYPE_NUMERIC).setCellValue(user.getUserId());

        if (user.getGarbages() == null)
            row.createCell(9, Cell.CELL_TYPE_NUMERIC).setCellValue(0);
        else
            row.createCell(9, Cell.CELL_TYPE_NUMERIC).setCellValue(user.getGarbages().size());

        if (user.getMemberOf() == null || user.getMemberOf().getChartedAreas() == null)
            row.createCell(10, Cell.CELL_TYPE_NUMERIC).setCellValue(0);
        else
            row.createCell(10, Cell.CELL_TYPE_NUMERIC)
                    .setCellValue(user.getMemberOf().getChartedAreas().size());

        StringBuffer ab = new StringBuffer();
        List<User.Activity> activities = user.getActivities();
        if (activities != null && activities.size() > 0) {
            for (User.Activity activity : activities)

                if (activity != null)
                    if (activity.getReportName() != null)
                        ab.append(activity.getReportName() + ", ");
                    else
                        ab.append("  " + ", ");

            if (ab.length() > 1)
                row.createCell(11).setCellValue(ab.substring(0, ab.length() - 2));
            else
                row.createCell(11).setCellValue(" ");

        }

        List<Team> managedTeams = user.getManagedTeams();
        if (managedTeams != null && managedTeams.size() > 0) {
            for (Team team : managedTeams)
                teams.add(team);
        }

    }

    TeamExcelFormatter teamWb = new TeamExcelFormatter(teams);
    wb = teamWb.convert(wb);

    return wb;
}

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 ww . j  a  va  2  s .  c  o  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:sql.fredy.sqltools.XLSExport.java

License:Open Source License

/**
 * Create the XLS-File named fileName/*  w  w  w  .  j  a v a 2  s  . 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;

}