Example usage for org.apache.poi.ss.usermodel Sheet setFitToPage

List of usage examples for org.apache.poi.ss.usermodel Sheet setFitToPage

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. setFitToPage

Introduction

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

Prototype

void setFitToPage(boolean value);

Source Link

Document

Flag indicating whether the Fit to Page print option is enabled.

Usage

From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java

License:Open Source License

/**
 * Creates a Microsoft Excel Workbook containing Topup activity provided in
 * a CSV text file. The format of the created file will be Office Open XML
 * (OOXML).//ww w  .  ja v a2  s .  c  o  m
 * <p>
 * It expects the CSV to have the following columns from left to right:<br
 * />
 * topup.uuid, topup.msisdn, topup.amount, network.name, topupStatus.status,
 * topup.topupTime
 * <p>
 * This method has been created to allow for large Excel files to be created
 * without overwhelming memory.
 *
 *
 * @param topupCSVFile a valid CSV text file. It should contain the full
 * path and name of the file e.g. "/tmp/export/topups.csv"
 * @param delimiter the delimiter used in the CSV file
 * @param excelFile the Microsoft Excel file to be created. It should
 * contain the full path and name of the file e.g. "/tmp/export/topups.xlsx"
 * @return whether the creation of the Excel file was successful or not
 */
public static boolean createExcelExport(final String topupCSVFile, final String delimiter,
        final String excelFile) {
    boolean success = true;

    int rowCount = 0; // To keep track of the row that we are on

    Row row;
    Map<String, CellStyle> styles;

    SXSSFWorkbook wb = new SXSSFWorkbook(5000); // keep 5000 rows in memory, exceeding rows will be flushed to disk
    // Each line of the file is approximated to be 200 bytes in size, 
    // therefore 5000 lines are approximately 1 MB in memory
    // wb.setCompressTempFiles(true); // temporary files will be gzipped on disk

    Sheet sheet = wb.createSheet("Airtime Topup");
    styles = createStyles(wb);

    PrintSetup printSetupTopup = sheet.getPrintSetup();
    printSetupTopup.setLandscape(true);
    sheet.setFitToPage(true);

    // Set up the heading to be seen in the Excel sheet
    row = sheet.createRow(rowCount);

    Cell titleCell;

    row.setHeightInPoints(45);
    titleCell = row.createCell(0);
    titleCell.setCellValue("Airtime Topups");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
    titleCell.setCellStyle(styles.get("title"));

    rowCount++;
    row = sheet.createRow(rowCount);
    row.setHeightInPoints(12.75f);

    for (int i = 0; i < TOPUP_TITLES.length; i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(TOPUP_TITLES[i]);
        cell.setCellStyle(styles.get("header"));
    }

    rowCount++;

    FileUtils.deleteQuietly(new File(excelFile));
    FileOutputStream out;

    try {
        FileUtils.touch(new File(excelFile));

        // Read the CSV file and populate the Excel sheet with it
        LineIterator lineIter = FileUtils.lineIterator(new File(topupCSVFile));
        String line;
        String[] lineTokens;
        int size;

        while (lineIter.hasNext()) {
            row = sheet.createRow(rowCount);
            line = lineIter.next();
            lineTokens = StringUtils.split(line, delimiter);
            size = lineTokens.length;

            for (int cellnum = 0; cellnum < size; cellnum++) {
                Cell cell = row.createCell(cellnum);
                cell.setCellValue(lineTokens[cellnum]);
            }

            rowCount++;
        }

        out = new FileOutputStream(excelFile);
        wb.write(out);
        out.close();

    } catch (FileNotFoundException e) {
        logger.error("FileNotFoundException while trying to create Excel file '" + excelFile
                + "' from CSV file '" + topupCSVFile + "'.");
        logger.error(ExceptionUtils.getStackTrace(e));
        success = false;

    } catch (IOException e) {
        logger.error("IOException while trying to create Excel file '" + excelFile + "' from CSV file '"
                + topupCSVFile + "'.");
        logger.error(ExceptionUtils.getStackTrace(e));
        success = false;
    }

    wb.dispose(); // dispose of temporary files backup of this workbook on disk

    return success;
}

From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java

License:Open Source License

/**
 * Used to create a MS Excel file from a list of
 *
 * @param topups//from  w ww.ja  v  a  2 s .  c o m
 * @param networkHash a map with an UUID as the key and the name of the
 * network as the value
 * @param statusHash a map with an UUID as the key and the name of the
 * transaction status as the value
 * @param delimiter
 * @param excelFile the Microsoft Excel file to be created. It should
 * contain the full path and name of the file e.g. "/tmp/export/topups.xlsx"
 * @return whether the creation of the Excel file was successful or not
 */
public static boolean createExcelExport(final List<IncomingLog> topups,
        final HashMap<String, String> networkHash, final HashMap<String, String> statusHash,
        final String delimiter, final String excelFile) {
    boolean success = true;

    int rowCount = 0; // To keep track of the row that we are on

    Row row;
    Map<String, CellStyle> styles;

    SXSSFWorkbook wb = new SXSSFWorkbook(5000); // keep 5000 rows in memory, exceeding rows will be flushed to disk
    // Each line of the file is approximated to be 200 bytes in size, 
    // therefore 5000 lines are approximately 1 MB in memory
    // wb.setCompressTempFiles(true); // temporary files will be gzipped on disk

    Sheet sheet = wb.createSheet("Airtime Topup");
    styles = createStyles(wb);

    PrintSetup printSetupTopup = sheet.getPrintSetup();
    printSetupTopup.setLandscape(true);
    sheet.setFitToPage(true);

    // Set up the heading to be seen in the Excel sheet
    row = sheet.createRow(rowCount);

    Cell titleCell;

    row.setHeightInPoints(45);
    titleCell = row.createCell(0);
    titleCell.setCellValue("Airtime Topups");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
    titleCell.setCellStyle(styles.get("title"));

    rowCount++;
    row = sheet.createRow(rowCount);
    row.setHeightInPoints(12.75f);

    for (int i = 0; i < TOPUP_TITLES.length; i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(TOPUP_TITLES[i]);
        cell.setCellStyle(styles.get("header"));
    }

    rowCount++;

    FileUtils.deleteQuietly(new File(excelFile));
    FileOutputStream out;

    try {
        FileUtils.touch(new File(excelFile));

        Cell cell;

        for (IncomingLog topup : topups) {
            row = sheet.createRow(rowCount);

            cell = row.createCell(0);
            cell.setCellValue(topup.getUuid());

            //cell = row.createCell(1);
            //cell.setCellValue(topup.getMessageid());

            cell = row.createCell(2);
            cell.setCellValue(topup.getDestination());

            cell = row.createCell(3);
            cell.setCellValue(networkHash.get(topup.getOrigin()));

            cell = row.createCell(4);
            cell.setCellValue(statusHash.get(topup.getMessage()));

            cell = row.createCell(5);
            cell.setCellValue(topup.getLogTime().toString());

            rowCount++;
        }

        out = new FileOutputStream(excelFile);
        wb.write(out);
        out.close();

    } catch (IOException e) {
        logger.error("IOException while trying to create Excel file '" + excelFile + "' from list of topups.");
        logger.error(ExceptionUtils.getStackTrace(e));
        success = false;
    }

    wb.dispose(); // dispose of temporary files backup of this workbook on disk

    return success;
}

From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java

License:Open Source License

public static boolean createExcelExport2(final List<OutgoingLog> topups,
        final HashMap<String, String> networkHash, final HashMap<String, String> statusHash,
        final String delimiter, final String excelFile) {
    boolean success = true;

    int rowCount = 0; // To keep track of the row that we are on

    Row row;//from   w  w w  .  j a v  a 2s  . co m
    Map<String, CellStyle> styles;

    SXSSFWorkbook wb = new SXSSFWorkbook(5000); // keep 5000 rows in memory, exceeding rows will be flushed to disk
    // Each line of the file is approximated to be 200 bytes in size, 
    // therefore 5000 lines are approximately 1 MB in memory
    // wb.setCompressTempFiles(true); // temporary files will be gzipped on disk

    Sheet sheet = wb.createSheet("Airtime Topup");
    styles = createStyles(wb);

    PrintSetup printSetupTopup = sheet.getPrintSetup();
    printSetupTopup.setLandscape(true);
    sheet.setFitToPage(true);

    // Set up the heading to be seen in the Excel sheet
    row = sheet.createRow(rowCount);

    Cell titleCell;

    row.setHeightInPoints(45);
    titleCell = row.createCell(0);
    titleCell.setCellValue("Airtime Topups");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
    titleCell.setCellStyle(styles.get("title"));

    rowCount++;
    row = sheet.createRow(rowCount);
    row.setHeightInPoints(12.75f);

    for (int i = 0; i < TOPUP_TITLES.length; i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(TOPUP_TITLES[i]);
        cell.setCellStyle(styles.get("header"));
    }

    rowCount++;

    FileUtils.deleteQuietly(new File(excelFile));
    FileOutputStream out;

    try {
        FileUtils.touch(new File(excelFile));

        Cell cell;

        for (OutgoingLog topup : topups) {
            row = sheet.createRow(rowCount);

            cell = row.createCell(0);
            cell.setCellValue(topup.getUuid());

            //cell = row.createCell(1);
            //cell.setCellValue(topup.getMessageid());

            cell = row.createCell(2);
            cell.setCellValue(topup.getDestination());

            cell = row.createCell(3);
            cell.setCellValue(networkHash.get(topup.getOrigin()));

            cell = row.createCell(4);
            cell.setCellValue(statusHash.get(topup.getMessage()));

            cell = row.createCell(5);
            cell.setCellValue(topup.getLogTime().toString());

            rowCount++;
        }

        out = new FileOutputStream(excelFile);
        wb.write(out);
        out.close();

    } catch (IOException e) {
        logger.error("IOException while trying to create Excel file '" + excelFile + "' from list of topups.");
        logger.error(ExceptionUtils.getStackTrace(e));
        success = false;
    }

    wb.dispose(); // dispose of temporary files backup of this workbook on disk

    return success;
}

From source file:mn.tsagaangeruud.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from  ww w .j  av a2  s  .c  om

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

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

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    //row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    //set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    //finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); //30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); //6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); //10 characters wide

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

From source file:net.algem.planning.export.PlanningExportService.java

License:Open Source License

/**
 * Export to Excel destination file./*from   www  .  j a  v a 2s. com*/
 *
 * @param dayPlan list of day schedules
 * @param destFile destination file
 * @throws IOException
 */
public void exportPlanning(List<DayPlan> dayPlan, File destFile) throws IOException {
    GemLogger.info("Exporting planning to " + destFile);

    Hour defStartTime = new Hour(ConfigUtil.getConf(ConfigKey.START_TIME.getKey()));
    int offset = defStartTime.getHour();
    int totalh = 24 - offset; // total time length in hours

    HSSFWorkbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("Planning");
    if (dayPlan.size() > 0) {
        DateFormat df = new SimpleDateFormat("EEEE dd MMM yyyy");
        Header header = sheet.getHeader();
        String hd = df.format(dayPlan.get(0).getSchedule().get(0).getDate().getDate());
        header.setCenter(HSSFHeader.fontSize((short) 12) + HSSFHeader.startBold() + hd + HSSFHeader.endBold());
    }

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setPaperSize(paperSize);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(false);// was true before 2.15.8
    sheet.setMargin(Sheet.TopMargin, 0.75); // 1.905
    sheet.setMargin(Sheet.BottomMargin, 0.4); // 0.4 inch = 1.016 cm
    sheet.setMargin(Sheet.LeftMargin, 0.4);
    sheet.setMargin(Sheet.RightMargin, 0.4);

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

    Row headerRow = sheet.createRow(0);
    for (int i = 0; i < dayPlan.size(); i++) {
        Cell roomCell = headerRow.createCell(i + 1);
        // Set the width (in units of 1/256th of a character width)
        //sheet.setColumnWidth(i + 1, totalh * 256);// max number of characters must not depend of time length
        sheet.setColumnWidth(i + 1, 24 * 256); // cours.titre character varying(32)
        roomCell.setCellValue(dayPlan.get(i).getLabel());
        roomCell.setCellStyle(styles.get("header"));
    }
    int offsetMn = offset * 60;// offset in minutes
    List<Row> rows = new ArrayList<>();
    System.out.println(" offset = " + offset + " totalh = " + totalh);
    for (int t = 0, rowNumber = 1; t < totalh * 60; t += 5, rowNumber++) { // 1 row = 5mn
        Hour hour = new Hour(offsetMn + t);
        Row row = sheet.createRow(rowNumber);
        //row.setHeightInPoints(25);
        row.setHeightInPoints(PrintSetup.A3_PAPERSIZE == paperSize ? 12 : 6);
        // TIME SUBDIVISIONS
        if (t % 15 == 0) {
            Cell cell = row.createCell(0);
            if (t % 30 == 0) {
                cell.setCellValue(hour.toString());//show time
                if (t % 60 == 0) {
                    cell.setCellStyle(styles.get("hour"));
                } else {
                    cell.setCellStyle(styles.get("hour-half"));
                }
            } else {
                cell.setCellStyle(styles.get("hour-quarter"));
            }
        } else { // BETWEEN SUBDIVISION
            Cell cell = row.createCell(0);
            if ("23:55".equals(hour.toString())) { // last slice
                cell.setCellStyle(styles.get("hour-last"));
            } else {
                cell.setCellStyle(styles.get("hour"));
            }
            if (rowNumber % 3 == 0) { // merge every 3 rows
                sheet.addMergedRegion(new CellRangeAddress(rowNumber - 2, rowNumber, 0, 0));
            }
        }
        rows.add(row);
    }

    Map<java.awt.Color, CellStyle> coursStyleCache = new HashMap<>();

    for (int i = 0; i < dayPlan.size(); i++) {
        DayPlan plan = dayPlan.get(i);
        int col = i + 1;
        for (ScheduleObject event : plan.getSchedule()) {
            // if event starts before default starting time
            if (event.getStart().toMinutes() < offsetMn) {
                event.setStart(new Hour(offset * 60));
            }
            int startRowPos = (event.getStart().toMinutes() - offsetMn) / 5 + 1;
            int endRowPos = (event.getEnd().toMinutes() - offsetMn) / 5;

            Cell courseCell = rows.get(startRowPos - 1).createCell(col);
            courseCell.setCellValue(getLabel(event, workbook));// title text

            CellStyle style = getCourseStyle(workbook, event, coursStyleCache);
            courseCell.setCellStyle(style);
            if (startRowPos != endRowPos) {
                sheet.addMergedRegion(new CellRangeAddress(startRowPos, endRowPos, col, col));
                for (int row = startRowPos; row < endRowPos; row++) {
                    rows.get(row).createCell(col).setCellStyle(style);
                }
            }
        }

    }

    try (FileOutputStream out = new FileOutputStream(destFile)) {
        workbook.write(out);
    }

}

From source file:org.aio.handy.poi.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from  w ww .  j  av a 2 s.c om

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

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

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    // title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    // header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                // the 10th cell contains sum over week days, e.g.
                // SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    // row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    // set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    // finally set column widths, the width is measured in units of 1/256th
    // of a character width
    sheet.setColumnWidth(0, 30 * 256); // 30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); // 6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); // 10 characters wide

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

From source file:org.cerberus.service.export.ExportServiceFactory.java

License:Open Source License

private void createReportByTagExport(Workbook workbook) {
    //handles the export of the execution by tag data
    HashMap<String, SummaryStatisticsDTO> summaryMap = new HashMap<String, SummaryStatisticsDTO>();

    HashMap<String, HashMap<String, List<TestCaseExecution>>> mapList = new HashMap<String, HashMap<String, List<TestCaseExecution>>>();
    List<String> mapCountries = new ArrayList<String>();
    List<CellStyle> stylesList = new LinkedList<CellStyle>();

    if (exportOptions.contains("chart") || exportOptions.contains("list")) {
        //then we need to create the default colors for each cell
        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFPalette palette = hwb.getCustomPalette();

        CellStyle okStyle = workbook.createCellStyle();

        // get the color which most closely matches the color you want to use
        // code to get the style for the cell goes here
        okStyle.setFillForegroundColor(palette.findSimilarColor(92, 184, 0).getIndex());
        okStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //okStyle.setFont();

        stylesList.add(okStyle);/*ww w.j  a v a 2s  .  c om*/

    }
    for (TestCaseExecution execution : (List<TestCaseExecution>) list) {
        //check if the country and application shows

        if (exportOptions.contains("chart") || exportOptions.contains("summary")) {
            String keySummaryTable = execution.getApplication() + " " + execution.getCountry() + " "
                    + execution.getEnvironment();
            SummaryStatisticsDTO stats;

            String status = execution.getControlStatus();

            if (summaryMap.containsKey(keySummaryTable)) {
                stats = summaryMap.get(keySummaryTable);
            } else {
                stats = new SummaryStatisticsDTO();
                stats.setApplication(execution.getApplication());
                stats.setCountry(execution.getCountry());
                stats.setEnvironment(execution.getEnvironment());
            }
            stats.updateStatisticByStatus(status);
            summaryMap.put(keySummaryTable, stats); //updates the map
        }
        if (exportOptions.contains("list")) {
            if (exportOptions.contains("filter")) {
                //filter active
            } else {
                //all data is saved

            }
            HashMap<String, List<TestCaseExecution>> listExecution;
            List<TestCaseExecution> testCaseList;
            String testKey = execution.getTest();
            String testCaseKey = execution.getTestCase();

            if (mapList.containsKey(testKey)) {
                listExecution = mapList.get(testKey);
            } else {
                listExecution = new HashMap<String, List<TestCaseExecution>>();
            }
            if (listExecution.containsKey(testCaseKey)) {
                testCaseList = listExecution.get(testCaseKey);
            } else {
                testCaseList = new ArrayList<TestCaseExecution>();
            }
            testCaseList.add(execution);
            listExecution.put(testCaseKey, testCaseList);
            mapList.put(testKey, listExecution);

            if (mapCountries.indexOf(execution.getCountry()) == -1) {
                mapCountries.add(execution.getCountry());
            }

        }

    }
    int rowCount = -1;

    //Create a blank sheet
    Sheet sheet = workbook.createSheet("Report by Tag");
    sheet.getPrintSetup().setLandscape(true);

    PrintSetup ps = sheet.getPrintSetup();

    sheet.setAutobreaks(true);

    //ps.setFitHeight((short) 1);
    ps.setFitWidth((short) 1);
    sheet.setFitToPage(true);
    sheet.setColumnWidth(0, 9000);

    if (exportOptions.contains("chart")) {
        SummaryStatisticsDTO sumsTotal = calculateTotalValues(summaryMap);

        Row row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Report By Status");

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Status");
        row.createCell(1).setCellValue("Total");
        row.createCell(2).setCellValue("Percentage");

        row = sheet.createRow(++rowCount);
        CellStyle okStyle = stylesList.get(0);
        Cell cellOk = row.createCell(0);
        cellOk.setCellValue("OK");
        cellOk.setCellStyle(okStyle);

        row.createCell(1).setCellValue(sumsTotal.getOk());
        row.createCell(2).setCellValue(sumsTotal.getPercOk());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("KO");
        row.createCell(1).setCellValue(sumsTotal.getKo());
        row.createCell(2).setCellValue(sumsTotal.getPercKo());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("FA");
        row.createCell(1).setCellValue(sumsTotal.getFa());
        row.createCell(2).setCellValue(sumsTotal.getPercFa());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("NA");
        row.createCell(1).setCellValue(sumsTotal.getNa());
        row.createCell(2).setCellValue(sumsTotal.getPercNa());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("NE");
        row.createCell(1).setCellValue(sumsTotal.getNe());
        row.createCell(2).setCellValue(sumsTotal.getPercNe());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("PE");
        row.createCell(1).setCellValue(sumsTotal.getPe());
        row.createCell(2).setCellValue(sumsTotal.getPercPe());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("CA");
        row.createCell(1).setCellValue(sumsTotal.getCa());
        row.createCell(2).setCellValue(sumsTotal.getPercCa());

        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Total");
        row.createCell(1).setCellValue(sumsTotal.getTotal());

        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");

    }
    if (exportOptions.contains("summary")) {
        //draw the table with data

        Row row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Summary Table");

        //start creating data
        row = sheet.createRow(++rowCount);

        row.createCell(0).setCellValue("Application");
        row.createCell(1).setCellValue("Country");
        row.createCell(2).setCellValue("Environment");
        row.createCell(3).setCellValue("OK");
        row.createCell(4).setCellValue("KO");
        row.createCell(5).setCellValue("FA");
        row.createCell(6).setCellValue("NA");
        row.createCell(7).setCellValue("NE");
        row.createCell(8).setCellValue("PE");
        row.createCell(9).setCellValue("CA");
        row.createCell(10).setCellValue("NOT OK");
        row.createCell(11).setCellValue("Total");

        /*temporary styles*/
        CellStyle styleBlue = workbook.createCellStyle();
        CellStyle styleGreen = workbook.createCellStyle();
        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFPalette palette = hwb.getCustomPalette();
        // get the color which most closely matches the color you want to use
        HSSFColor myColor = palette.findSimilarColor(66, 139, 202);

        // get the palette index of that color 
        short palIndex = myColor.getIndex();
        // code to get the style for the cell goes here
        styleBlue.setFillForegroundColor(palIndex);
        styleBlue.setFillPattern(CellStyle.SPARSE_DOTS);

        HSSFColor myColorGreen = palette.findSimilarColor(92, 184, 0);
        styleGreen.setFillForegroundColor(myColorGreen.getIndex());
        styleGreen.setFillPattern(CellStyle.SPARSE_DOTS);

        int startRow = (rowCount + 2);
        TreeMap<String, SummaryStatisticsDTO> sortedSummaryMap = new TreeMap<String, SummaryStatisticsDTO>(
                summaryMap);
        for (String key : sortedSummaryMap.keySet()) {
            row = sheet.createRow(++rowCount);
            SummaryStatisticsDTO sumStats = summaryMap.get(key);
            //application
            row.createCell(0).setCellValue((String) sumStats.getApplication());
            //country
            row.createCell(1).setCellValue((String) sumStats.getCountry());
            //environment
            row.createCell(2).setCellValue((String) sumStats.getEnvironment());

            //OK
            row.createCell(3).setCellValue(sumStats.getOk());
            //KO
            row.createCell(4).setCellValue(sumStats.getKo());
            //FA
            row.createCell(5).setCellValue(sumStats.getFa());
            //NA
            row.createCell(6).setCellValue(sumStats.getNa());
            //NE
            row.createCell(7).setCellValue(sumStats.getNe());
            //PE
            row.createCell(8).setCellValue(sumStats.getPe());
            //CA
            row.createCell(9).setCellValue(sumStats.getCa());
            int rowNumber = row.getRowNum() + 1;
            //NOT OK
            //row.createCell(11).setCellValue(sumStats.getNotOkTotal());
            row.createCell(10).setCellFormula("SUM(E" + rowNumber + ":J" + rowNumber + ")");
            //Total
            row.createCell(11).setCellFormula("SUM(D" + rowNumber + ",K" + rowNumber + ")");
            //row.createCell(12).setCellValue(sumStats.getTotal());

            if (sumStats.getOk() == sumStats.getTotal()) {
                for (int i = 0; i < 12; i++) {
                    row.getCell(i).setCellStyle(styleGreen);
                }
            }
        }
        //TODO:FN percentages missing
        //Total row
        row = sheet.createRow(++rowCount);

        row.createCell(0).setCellValue("Total");
        row.createCell(1).setCellValue("");
        row.createCell(2).setCellValue("");
        //OK
        row.createCell(3).setCellFormula("SUM(D" + startRow + ":D" + rowCount + ")");
        //KO
        row.createCell(4).setCellFormula("SUM(E" + startRow + ":E" + rowCount + ")");
        //FA
        row.createCell(5).setCellFormula("SUM(F" + startRow + ":F" + rowCount + ")");
        //NA
        row.createCell(6).setCellFormula("SUM(G" + startRow + ":G" + rowCount + ")");
        //NE
        row.createCell(7).setCellFormula("SUM(H" + startRow + ":H" + rowCount + ")");
        //PE
        row.createCell(8).setCellFormula("SUM(I" + startRow + ":I" + rowCount + ")");
        //CA
        row.createCell(9).setCellFormula("SUM(J" + startRow + ":J" + rowCount + ")");

        int rowNumberTotal = row.getRowNum() + 1;
        //NOT OK
        row.createCell(10).setCellFormula("SUM(E" + rowNumberTotal + ":J" + rowNumberTotal + ")");
        //Total
        row.createCell(11).setCellFormula("SUM(D" + rowNumberTotal + ",K" + rowNumberTotal + ")");
        for (int i = 0; i < 12; i++) {
            row.getCell(i).setCellStyle(styleBlue);
        }

        //add some empty rows
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");

    }

    if (exportOptions.contains("list")) {
        //exports the data from test cases' executions
        Row r = sheet.createRow(++rowCount);
        r.createCell(0).setCellValue("Test");
        r.createCell(1).setCellValue("Test Case");
        r.createCell(2).setCellValue("Description");
        r.createCell(3).setCellValue("Application");
        r.createCell(4).setCellValue("Environment");
        r.createCell(5).setCellValue("Browser");
        //creates the country list

        Collections.sort(mapCountries);//sorts the list of countries
        int startIndexForCountries = 6;
        for (String country : mapCountries) {
            r.createCell(startIndexForCountries).setCellValue(country);
            startIndexForCountries++;
        }

        TreeMap<String, HashMap<String, List<TestCaseExecution>>> sortedKeys = new TreeMap<String, HashMap<String, List<TestCaseExecution>>>(
                mapList);
        rowCount++;
        for (String keyMapList : sortedKeys.keySet()) {
            rowCount = createRow(keyMapList, mapList.get(keyMapList), sheet, rowCount, mapCountries);
        }
    }
}

From source file:org.dashbuilder.dataset.backend.DataSetBackendServicesImpl.java

License:Apache License

@Override
public String exportDataSetExcel(DataSet dataSet) {
    // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters
    if (dataSet == null)
        throw new IllegalArgumentException("Null dataSet specified!");
    int columnCount = dataSet.getColumns().size();
    int rowCount = dataSet.getRowCount() + 1; //Include header row;
    int row = 0;//from  w w w. jav a  2s  .c o  m

    SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sh = wb.createSheet("Sheet 1");

    // General setup
    sh.setDisplayGridlines(true);
    sh.setPrintGridlines(false);
    sh.setFitToPage(true);
    sh.setHorizontallyCenter(true);
    PrintSetup printSetup = sh.getPrintSetup();
    printSetup.setLandscape(true);

    // Create header
    Row header = sh.createRow(row++);
    header.setHeightInPoints(20f);
    for (int i = 0; i < columnCount; i++) {
        Cell cell = header.createCell(i);
        cell.setCellStyle(styles.get("header"));
        cell.setCellValue(dataSet.getColumnByIndex(i).getId());
    }

    // Create data rows
    for (; row < rowCount; row++) {
        Row _row = sh.createRow(row);
        for (int cellnum = 0; cellnum < columnCount; cellnum++) {
            Cell cell = _row.createCell(cellnum);
            Object value = dataSet.getValueAt(row - 1, cellnum);
            if (value instanceof Short || value instanceof Long || value instanceof Integer
                    || value instanceof BigInteger) {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(styles.get("integer_number_cell"));
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellStyle(styles.get("decimal_number_cell"));
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("date_cell"));
                cell.setCellValue((Date) value);
            } else if (value instanceof Interval) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("text_cell"));
                cell.setCellValue(((Interval) value).getName());
            } else {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellStyle(styles.get("text_cell"));
                cell.setCellValue(value.toString());
            }
        }
    }

    // Adjust column size
    for (int i = 0; i < columnCount; i++) {
        sh.autoSizeColumn(i);
    }

    Path tempExcelFilePath = null;
    try {
        tempExcelFilePath = ioService.createTempFile("export", "xlsx", null);
        OutputStream os = Files.newOutputStream(tempExcelFilePath);
        wb.write(os);
        os.flush();
        os.close();
    } catch (Exception e) {
        log.error("Error in excel export: ", e);
    }

    // Dispose of temporary files backing this workbook on disk
    if (!wb.dispose())
        log.warn("Could not dispose of temporary file associated to data export!");

    return tempExcelFilePath.toString();
}

From source file:org.dashbuilder.dataset.backend.DataSetExportServicesImpl.java

License:Apache License

@Override
public org.uberfire.backend.vfs.Path exportDataSetExcel(DataSet dataSet) {
    try {//from  w  w w .  j a va  2s.  co m
        // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters
        if (dataSet == null)
            throw new IllegalArgumentException("Null dataSet specified!");
        int columnCount = dataSet.getColumns().size();
        int rowCount = dataSet.getRowCount() + 1; //Include header row;
        int row = 0;

        SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Map<String, CellStyle> styles = createStyles(wb);
        Sheet sh = wb.createSheet("Sheet 1");

        // General setup
        sh.setDisplayGridlines(true);
        sh.setPrintGridlines(false);
        sh.setFitToPage(true);
        sh.setHorizontallyCenter(true);
        PrintSetup printSetup = sh.getPrintSetup();
        printSetup.setLandscape(true);

        // Create header
        Row header = sh.createRow(row++);
        header.setHeightInPoints(20f);
        for (int i = 0; i < columnCount; i++) {
            Cell cell = header.createCell(i);
            cell.setCellStyle(styles.get("header"));
            cell.setCellValue(dataSet.getColumnByIndex(i).getId());
        }

        // Create data rows
        for (; row < rowCount; row++) {
            Row _row = sh.createRow(row);
            for (int cellnum = 0; cellnum < columnCount; cellnum++) {
                Cell cell = _row.createCell(cellnum);
                Object value = dataSet.getValueAt(row - 1, cellnum);
                if (value instanceof Short || value instanceof Long || value instanceof Integer
                        || value instanceof BigInteger) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(styles.get("integer_number_cell"));
                    cell.setCellValue(((Number) value).doubleValue());
                } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellStyle(styles.get("decimal_number_cell"));
                    cell.setCellValue(((Number) value).doubleValue());
                } else if (value instanceof Date) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellStyle(styles.get("date_cell"));
                    cell.setCellValue((Date) value);
                } else if (value instanceof Interval) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellStyle(styles.get("text_cell"));
                    cell.setCellValue(((Interval) value).getName());
                } else {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellStyle(styles.get("text_cell"));
                    cell.setCellValue(value.toString());
                }
            }
        }

        // Adjust column size
        for (int i = 0; i < columnCount; i++) {
            sh.autoSizeColumn(i);
        }

        String tempXlsFile = uuidGenerator.newUuid() + ".xlsx";
        Path tempXlsPath = gitStorage.createTempFile(tempXlsFile);
        OutputStream os = Files.newOutputStream(tempXlsPath);
        wb.write(os);
        os.flush();
        os.close();

        // Dispose of temporary files backing this workbook on disk
        if (!wb.dispose()) {
            log.warn("Could not dispose of temporary file associated to data export!");
        }
        return Paths.convert(tempXlsPath);
    } catch (Exception e) {
        throw exceptionManager.handleException(e);
    }
}

From source file:org.dkpro.lab.reporting.FlexTable.java

License:Apache License

public StreamWriter getExcelWriter() {
    return new StreamWriter() {
        @Override//from  w  w w.j a va  2s. c o  m
        public void write(OutputStream aStream) throws Exception {
            String[] colIds = FlexTable.this.compact ? getCompactColumnIds(false) : getColumnIds();

            Workbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("Summary");

            PrintSetup printSetup = sheet.getPrintSetup();
            printSetup.setLandscape(true);
            sheet.setFitToPage(true);
            sheet.setHorizontallyCenter(true);

            // Header row
            {
                Row row = sheet.createRow(0);
                Cell rowIdCell = row.createCell(0);
                rowIdCell.setCellValue("ID");

                int colNum = 1;
                for (String colId : colIds) {
                    Cell cell = row.createCell(colNum);
                    cell.setCellValue(colId);
                    colNum++;
                }
            }

            // Body rows
            {
                int rowNum = 1;
                for (String rowId : getRowIds()) {
                    Row row = sheet.createRow(rowNum);
                    Cell rowIdCell = row.createCell(0);
                    rowIdCell.setCellValue(rowId);

                    int colNum = 1;
                    for (String colId : colIds) {
                        Cell cell = row.createCell(colNum);
                        String value = getValueAsString(rowId, colId);
                        try {
                            cell.setCellValue(Double.valueOf(value));
                        } catch (NumberFormatException e) {
                            cell.setCellValue(value);
                        }
                        colNum++;
                    }
                    rowNum++;
                }
            }

            wb.write(aStream);
        }
    };
}