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

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

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

Introduction

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

Prototype

void setHorizontallyCenter(boolean value);

Source Link

Document

Determines whether the output is horizontally centered on the page.

Usage

From source file:de.fraunhofer.sciencedataamanager.datamanager.SearchDefinitonExecutionDataManager.java

/**
 *
 * @param searchDefinitonExecutionList/*from  w ww  .  j a va 2 s .c  om*/
 * @param outputStream
 * @throws Exception
 */
public void exportToExcel(LinkedList<SearchDefinitonExecution> searchDefinitonExecutionList,
        OutputStream outputStream) throws Exception {

    Workbook currentWorkBook = new HSSFWorkbook();
    int currenSheetCount = 0;
    for (SearchDefinitonExecution searchDefinitonExecution : searchDefinitonExecutionList) {

        Sheet currentSheet = currentWorkBook.createSheet();
        currentSheet.setFitToPage(true);
        currentSheet.setHorizontallyCenter(true);
        currentSheet.createFreezePane(0, 1);
        currentWorkBook.setSheetName(currenSheetCount, searchDefinitonExecution.getSystemInstance().getName());

        Row headerRow = currentSheet.createRow(0);
        headerRow.setHeightInPoints(12.75f);

        headerRow.createCell(0).setCellValue("ID");
        headerRow.createCell(1).setCellValue("Title");

        headerRow.createCell(2).setCellValue("Identifier 1");
        headerRow.createCell(3).setCellValue("Identifier 2");
        headerRow.createCell(4).setCellValue("Identifier 3");
        headerRow.createCell(5).setCellValue("Identifier 4");
        headerRow.createCell(6).setCellValue("Url 1");
        headerRow.createCell(7).setCellValue("Url 2");
        headerRow.createCell(8).setCellValue("Text 1");
        headerRow.createCell(9).setCellValue("Publication Name");
        headerRow.createCell(10).setCellValue("Issue Name");
        headerRow.createCell(11).setCellValue("Publish Date");
        headerRow.createCell(12).setCellValue("Volume");
        headerRow.createCell(13).setCellValue("Start Page");
        headerRow.createCell(14).setCellValue("Issue Identifier");

        CellStyle style = currentWorkBook.createCellStyle();
        Font headerFont = currentWorkBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(headerFont);

        headerRow.setRowStyle(style);

        Row currentRow = null;
        int rowNum = 1;
        for (ScientificPaperMetaInformation scientificPaperMetaInformation : searchDefinitonExecution
                .getScientificPaperMetaInformation()) {
            currentRow = currentSheet.createRow(rowNum);
            currentRow.createCell(0).setCellValue(scientificPaperMetaInformation.getID());
            currentRow.createCell(1).setCellValue(scientificPaperMetaInformation.getTitle());
            currentRow.createCell(2).setCellValue(scientificPaperMetaInformation.getIdentifier_1());
            currentRow.createCell(3).setCellValue(scientificPaperMetaInformation.getIdentifier_2());
            currentRow.createCell(4).setCellValue(scientificPaperMetaInformation.getIdentifier_3());
            currentRow.createCell(5).setCellValue(scientificPaperMetaInformation.getIdentifier_4());
            currentRow.createCell(6).setCellValue(scientificPaperMetaInformation.getUrl_1());
            currentRow.createCell(7).setCellValue(scientificPaperMetaInformation.getUrl_2());
            currentRow.createCell(8).setCellValue(scientificPaperMetaInformation.getText_1());

            currentRow.createCell(9).setCellValue(scientificPaperMetaInformation.getSrcTitle());
            currentRow.createCell(10).setCellValue(scientificPaperMetaInformation.getScrPublisherName());
            currentRow.createCell(11).setCellValue(scientificPaperMetaInformation.getSrcPublicationDate());
            currentRow.createCell(12).setCellValue(scientificPaperMetaInformation.getSrcVolume());
            currentRow.createCell(13).setCellValue(scientificPaperMetaInformation.getSrcStartPage());
            currentRow.createCell(14).setCellValue(scientificPaperMetaInformation.getScrIdentifier_1());

            rowNum++;

        }
        currenSheetCount++;
    }
    currentWorkBook.write(outputStream);

    outputStream.close();

}

From source file:de.fraunhofer.sciencedataamanager.exampes.export.ExcelDataExport.java

/**
 *
 * @param dataToExport The objects gets all the values, which should
 * exported.//from  w w w .  ja v a  2s .c  o m
 * @param outputStream
 * @throws Exception
 */
@Override
public void export(Map<String, Map<String, List<Object>>> allConnectorsToExport, OutputStream outputStream)
        throws Exception {
    Workbook currentWorkBook = new HSSFWorkbook();
    int currenSheetCount = 0;

    for (String currentKey : allConnectorsToExport.keySet()) {
        Map<String, List<Object>> dataToExport = allConnectorsToExport.get(currentKey);
        List<String> columns = new ArrayList<String>(dataToExport.keySet());
        List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
        int size = dataToExport.values().iterator().next().size();

        for (int i = 0; i < size; i++) {
            Map<String, Object> row = new HashMap<String, Object>();

            for (String column : columns) {
                row.put(column, dataToExport.get(column).get(i));
            }

            rows.add(row);
        }

        //for (SearchDefinitonExecution searchDefinitonExecution : searchDefinitonExecutionList) {
        Sheet currentSheet = currentWorkBook.createSheet();
        currentSheet.setFitToPage(true);
        currentSheet.setHorizontallyCenter(true);
        currentSheet.createFreezePane(0, 1);
        currentWorkBook.setSheetName(currenSheetCount, currentKey);

        Row headerRow = currentSheet.createRow(0);
        headerRow.setHeightInPoints(12.75f);
        int headerColumnIndex = 0;
        for (String currentColumn : columns) {
            headerRow.createCell(headerColumnIndex).setCellValue(currentColumn);
            headerColumnIndex++;
        }
        CellStyle style = currentWorkBook.createCellStyle();
        Font headerFont = currentWorkBook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());

        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(headerFont);

        headerRow.setRowStyle(style);

        Row currentRow = null;
        int rowNum = 1;
        int currentColum = 0;

        for (Map<String, Object> currentRow2 : rows) {
            currentRow = currentSheet.createRow(rowNum);
            for (String column : columns) {
                if (currentRow2.get(column) != null) {
                    currentRow.createCell(currentColum).setCellValue(currentRow2.get(column).toString());
                }
                currentColum++;

            }
            currentColum = 0;
            rowNum++;
        }
        currenSheetCount++;
    }
    currentWorkBook.write(outputStream);

    outputStream.close();

}

From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportExcelXLSX.java

License:Open Source License

public void write() throws FileNotFoundException, IOException {
    FileOutputStream out = new FileOutputStream(new File(filename));
    Workbook wb;//from  w  w w  . ja v a 2s .co m

    wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet(sheetName);

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

    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);

    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);

    int[][] width = new int[titles.length][titles.length];

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

        width[i][0] = titles[i].length();
    }

    Row row;
    Cell cell;
    int rownum = 1;

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

        for (int j = 0; j < data[i].length; j++) {
            cell = row.createCell((short) j);
            if (data[i][j] == null)
                data[i][j] = "";

            cell.setCellValue(data[i][j].toString());

            if (data[i][j].toString().length() > width[j][0])
                width[j][0] = data[i][j].toString().length();
        }
    }

    for (short i = 0; i < titles.length; i++) {
        short widthShort = (short) (256 * (width[i][0] + 3));

        sheet.setColumnWidth(i, widthShort);
    }

    int position = (titles.length / 2) - 1;

    row = sheet.createRow(rownum + 3);
    cell = row.createCell((short) position);

    if (footName == null) {
        SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm");
        cell.setCellValue("Export MaklerPoint vom " + df.format(new Date(System.currentTimeMillis()))
                + " - www.maklerpoint.de");
    } else {
        cell.setCellValue(footName);
    }

    sheet.setZoom(3, 4);

    wb.write(out);
    out.close();
}

From source file:de.tudarmstadt.ukp.csniper.webapp.statistics.page.export.ExportExcelTask.java

License:Apache License

@Override
protected void run() {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("Summary");

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);//  ww  w  . j a  v a2 s. com
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    contextProvider.setOutputPos(exportModel.includePos);

    outputFile = null;
    OutputStream os = null;
    try {
        List<AggregatedEvaluationResult> results = repository.listAggregatedResults(formModel.getCollections(),
                formModel.getTypes(), formModel.getUsers(), formModel.getUserThreshold(),
                formModel.getConfidenceThreshold());

        List<AdditionalColumn> ac = exportModel.additionalColumns;

        Collections.sort(results, new Comparator<AggregatedEvaluationResult>() {
            @Override
            public int compare(AggregatedEvaluationResult aO1, AggregatedEvaluationResult aO2) {
                String id1 = aO1.getItem().getCollectionId() + "|" + aO1.getItem().getDocumentId();
                String id2 = aO2.getItem().getCollectionId() + "|" + aO2.getItem().getDocumentId();
                return id1.compareTo(id2);
            }
        });

        // Write header row
        List<String> colIds = new ArrayList<String>(Arrays.asList("User", "Collection", "Document", "Begin",
                "End", "Left", "Unit", "Right", "Type", "Class", "Confidence", "Correct", "Wrong"));
        for (int i = 0; i < ac.size(); i++) {
            colIds.add(ac.get(i).getName());
        }
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < colIds.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(colIds.get(i));
        }

        // Write rest
        setTotal(results.size());
        int rowNum = 1;
        for (AggregatedEvaluationResult aer : results) {
            ResultFilter classification = aer.getClassification();
            if (formModel.getFilters().contains(classification)) {
                ItemContext context = contextProvider.getContext(aer.getItem(), exportModel.contextSize,
                        exportModel.contextSize);

                // only differentiate between users if additional columns are being exported
                Set<String> users;
                if (ac.isEmpty()) {
                    users = new HashSet<String>(Arrays.asList(""));
                } else {
                    users = aer.getUsers(false);
                }

                // output the AggregatedEvaluationResult for every user (because the additional
                // columns entries might differ)
                for (String user : users) {
                    Row row = sheet.createRow(rowNum);
                    row.createCell(0).setCellValue(user);
                    row.createCell(1).setCellValue(aer.getItem().getCollectionId());
                    row.createCell(2).setCellValue(aer.getItem().getDocumentId());
                    row.createCell(3).setCellValue(aer.getItem().getBeginOffset());
                    row.createCell(4).setCellValue(aer.getItem().getEndOffset());
                    row.createCell(5).setCellValue(context.getLeft());
                    row.createCell(6).setCellValue(context.getUnit());
                    row.createCell(7).setCellValue(context.getRight());
                    row.createCell(8).setCellValue(aer.getItem().getType());
                    row.createCell(9).setCellValue(classification.toString());
                    row.createCell(10).setCellValue(aer.getConfidence());
                    row.createCell(11).setCellValue(aer.getCorrect());
                    row.createCell(12).setCellValue(aer.getWrong());
                    for (int i = 0; i < ac.size(); i++) {
                        String cellValue = repository.getEvaluationResult(aer.getItem().getId(), user)
                                .getAdditionalColumns().get(ac.get(i));
                        if (cellValue == null) {
                            cellValue = "";
                        }
                        row.createCell(colIds.size() - ac.size() + i).setCellValue(cellValue);

                    }
                    rowNum++;
                }
            }

            // Make sure we do not get to 100% before we did the classification, because
            // otherwise ProgressBar.onFinish() will trigger!!!
            increment();
            if (isCancelled()) {
                break;
            }
        }

        outputFile = File.createTempFile("date", ".csv");
        os = new FileOutputStream(outputFile);
        wb.write(os);
    } catch (IOException e) {
        e.printStackTrace();
        error("Export failed: " + ExceptionUtils.getRootCauseMessage(e));
        cancel();
    } finally {
        IOUtils.closeQuietly(os);
        if (isCancelled()) {
            clean();
        }
    }
}

From source file:de.tudarmstadt.ukp.dkpro.lab.reporting.FlexTable.java

License:Apache License

public StreamWriter getExcelWriter() {
    return new StreamWriter() {
        @Override/*www .  ja  v a2s .c o m*/
        public void write(OutputStream aStream) throws Exception {
            String[] colIds = 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);
        }
    };
}

From source file:demo.poi.BusinessPlan.java

License:Apache License

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

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

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

    // turn off gridlines
    sheet.setDisplayGridlines(false);/*  w  ww.jav  a2s  .  co  m*/
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

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

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

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

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

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

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

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

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

    // Write the output to a file
    String file = "target/businessplan.xls";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:demo.poi.LoanCalculator.java

License:Apache License

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

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

    Map<String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);

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

    sheet.setColumnWidth(0, 3 * 256);
    sheet.setColumnWidth(1, 3 * 256);
    sheet.setColumnWidth(2, 11 * 256);
    sheet.setColumnWidth(3, 14 * 256);
    sheet.setColumnWidth(4, 14 * 256);
    sheet.setColumnWidth(5, 14 * 256);
    sheet.setColumnWidth(6, 14 * 256);

    createNames(wb);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35);
    for (int i = 1; i <= 7; i++) {
        titleRow.createCell(i).setCellStyle(styles.get("title"));
    }
    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple Loan Calculator");
    sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1"));

    Row row = sheet.createRow(2);
    Cell cell = row.createCell(4);
    cell.setCellValue("Enter values");
    cell.setCellStyle(styles.get("item_right"));

    row = sheet.createRow(3);
    cell = row.createCell(2);
    cell.setCellValue("Loan amount");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_$"));
    cell.setAsActiveCell();

    row = sheet.createRow(4);
    cell = row.createCell(2);
    cell.setCellValue("Annual interest rate");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_%"));

    row = sheet.createRow(5);
    cell = row.createCell(2);
    cell.setCellValue("Loan period in years");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_i"));

    row = sheet.createRow(6);
    cell = row.createCell(2);
    cell.setCellValue("Start date of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellStyle(styles.get("input_d"));

    row = sheet.createRow(8);
    cell = row.createCell(2);
    cell.setCellValue("Monthly payment");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(9);
    cell = row.createCell(2);
    cell.setCellValue("Number of payments");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")");
    cell.setCellStyle(styles.get("formula_i"));

    row = sheet.createRow(10);
    cell = row.createCell(2);
    cell.setCellValue("Total interest");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    row = sheet.createRow(11);
    cell = row.createCell(2);
    cell.setCellValue("Total cost of loan");
    cell.setCellStyle(styles.get("item_left"));
    cell = row.createCell(4);
    cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")");
    cell.setCellStyle(styles.get("formula_$"));

    // Write the output to a file
    String file = "target/loan-calculator.xls";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:demo.poi.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//w  w  w. jav a2s .co m

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

    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 = "target/timesheet.xls";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:Demos.CalendarDemo.java

License:Apache License

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

    Calendar calendar = Calendar.getInstance();
    boolean xlsx = true;
    for (int i = 0; i < args.length; i++) {
        if (args[i].charAt(0) == '-') {
            xlsx = args[i].equals("-xlsx");
        } else {// w w w.  ja v a  2s  . c o  m
            calendar.set(Calendar.YEAR, Integer.parseInt(args[i]));
        }
    }
    int year = calendar.get(Calendar.YEAR);

    Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook();

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

    for (int month = 0; month < 12; month++) {
        calendar.set(Calendar.MONTH, month);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        // create a sheet for each month
        Sheet sheet = wb.createSheet(months[month]);

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

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

        // the header row: centered text in 48pt font
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(80);
        Cell titleCell = headerRow.createCell(0);
        titleCell.setCellValue(months[month] + " " + year);
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

        // header with month titles
        Row monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            // set column widths, the width is measured in units of 1/256th
            // of a character width
            sheet.setColumnWidth(i * 2, 5 * 256); // the column is 5
            // characters wide
            sheet.setColumnWidth(i * 2 + 1, 13 * 256); // the column is 13
            // characters wide
            sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1));
            Cell monthCell = monthRow.createCell(i * 2);
            monthCell.setCellValue(days[i]);
            monthCell.setCellStyle(styles.get("month"));
        }

        int cnt = 1, day = 1;
        int rownum = 2;
        for (int j = 0; j < 6; j++) {
            Row row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                Cell dayCell_1 = row.createCell(i * 2);
                Cell dayCell_2 = row.createCell(i * 2 + 1);

                int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                    dayCell_1.setCellValue(day);
                    calendar.set(Calendar.DAY_OF_MONTH, ++day);

                    if (i == 0 || i == days.length - 1) {
                        dayCell_1.setCellStyle(styles.get("weekend_left"));
                        dayCell_2.setCellStyle(styles.get("weekend_right"));
                    } else {
                        dayCell_1.setCellStyle(styles.get("workday_left"));
                        dayCell_2.setCellStyle(styles.get("workday_right"));
                    }
                } else {
                    dayCell_1.setCellStyle(styles.get("grey_left"));
                    dayCell_2.setCellStyle(styles.get("grey_right"));
                }
                cnt++;
            }
            if (calendar.get(Calendar.MONTH) > month)
                break;
        }
    }

    // Write the output to a file
    String file = "C:\\Users\\BaldiniHP\\Desktop\\calendar.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:itpreneurs.itp.report.archive.BusinessPlan.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/* w  w w  . jav a2s  .com*/

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

    wb = new XSSFWorkbook();

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

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

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

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

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

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

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

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

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

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

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

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