Business Plan

The BusinessPlan application creates a sample business plan with three phases, weekly iterations and time highlighting. Demonstrates advanced cell formatting (number and date formats, alignments, fills, borders) and various settings for organizing data in a sheet (freezed panes, grouped rows).

/* ====================================================================

   Licensed to the Apache Software Foundation (ASF) under one or more

   contributor license agreements.  See the NOTICE file distributed with

   this work for additional information regarding copyright ownership.

   The ASF licenses this file to You under the Apache License, Version 2.0

   (the "License"); you may not use this file except in compliance with

   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software

   distributed under the License is distributed on an "AS IS" BASIS,

   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

   See the License for the specific language governing permissions and

   limitations under the License.

==================================================================== */package org.apache.poi.ss.examples;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.util.Map;

import java.util.HashMap;

import java.util.Calendar;

import java.io.FileOutputStream;

import java.text.SimpleDateFormat;

/**

 * A business plan demo

 * Usage:

 *  BusinessPlan -xls|xlsx

 *

 * @author Yegor Kozlov

 */

public class BusinessPlan {

    private static SimpleDateFormat fmt = new SimpleDateFormat("dd-MMM");

    private static final String[] titles = {

            "ID", "Project Name", "Owner", "Days", "Start", "End"};

    //sample data to fill the sheet.

    private static final String[][] data = {

            {"1.0", "Marketing Research Tactical Plan", "J. Dow", "70", "9-Jul", null,

                "x", "x", "x", "x", "x", "x", "x", "x", "x", "x", "x"},

            null,

            {"1.1", "Scope Definition Phase", "J. Dow", "10", "9-Jul", null,

                "x", "x", null, null,  null, null, null, null, null, null, null},

            {"1.1.1", "Define research objectives", "J. Dow", "3", "9-Jul", null,

                    "x", null, null, null,  null, null, null, null, null, null, null},

            {"1.1.2", "Define research requirements", "S. Jones", "7", "10-Jul", null,

                "x", "x", null, null,  null, null, null, null, null, null, null},

            {"1.1.3", "Determine in-house resource or hire vendor", "J. Dow", "2", "15-Jul", null,

                "x", "x", null, null,  null, null, null, null, null, null, null},

            null,

            {"1.2", "Vendor Selection Phase", "J. Dow", "19", "19-Jul", null,

                null, "x", "x", "x",  "x", null, null, null, null, null, null},

            {"1.2.1", "Define vendor selection criteria", "J. Dow", "3", "19-Jul", null,

                null, "x", null, null,  null, null, null, null, null, null, null},

            {"1.2.2", "Develop vendor selection questionnaire", "S. Jones, T. Wates", "2", "22-Jul", null,

                null, "x", "x", null,  null, null, null, null, null, null, null},

            {"1.2.3", "Develop Statement of Work", "S. Jones", "4", "26-Jul", null,

                null, null, "x", "x",  null, null, null, null, null, null, null},

            {"1.2.4", "Evaluate proposal", "J. Dow, S. Jones", "4", "2-Aug", null,

                null, null, null, "x",  "x", null, null, null, null, null, null},

            {"1.2.5", "Select vendor", "J. Dow", "1", "6-Aug", null,

                null, null, null, null,  "x", null, null, null, null, null, null},

            null,

            {"1.3", "Research Phase", "G. Lee", "47", "9-Aug", null,

                null, null, null, null,  "x", "x", "x", "x", "x", "x", "x"},

            {"1.3.1", "Develop market research information needs questionnaire", "G. Lee", "2", "9-Aug", null,

                null, null, null, null,  "x", null, null, null, null, null, null},

            {"1.3.2", "Interview marketing group for market research needs", "G. Lee", "2", "11-Aug", null,

                null, null, null, null,  "x", "x", null, null, null, null, null},

            {"1.3.3", "Document information needs", "G. Lee, S. Jones", "1", "13-Aug", null,

                null, null, null, null,  null, "x", null, null, null, null, null},

    };

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

        Workbook wb;

        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("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(75); //75% scale

        // Write the output to a file

        String file = "businessplan.xls";

        if(wb instanceof XSSFWorkbook) file += "x";

        FileOutputStream out = new FileOutputStream(file);

        wb.write(out);

        out.close();

        wb.close();

    }

    /**

     * create a library of cell styles

     */

    private static Map<String, CellStyle> createStyles(Workbook wb){

        Map<String, CellStyle> styles = new HashMap<>();

        DataFormat df = wb.createDataFormat();

        CellStyle style;

        Font headerFont = wb.createFont();
　　　　　// 黑体

        headerFont.setBold(true);

        style = createBorderedStyle(wb);

        style.setAlignment(HorizontalAlignment.CENTER);

        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());

        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        style.setFont(headerFont);

        styles.put("header", style);

        style = createBorderedStyle(wb);

        style.setAlignment(HorizontalAlignment.CENTER);

        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());

        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        style.setFont(headerFont);

        style.setDataFormat(df.getFormat("d-mmm"));

        styles.put("header_date", style);

        Font font1 = wb.createFont();

        font1.setBold(true);

        style = createBorderedStyle(wb);

        style.setAlignment(HorizontalAlignment.LEFT);

        style.setFont(font1);

        styles.put("cell_b", style);

        style = createBorderedStyle(wb);

        style.setAlignment(HorizontalAlignment.CENTER);

        style.setFont(font1);

        styles.put("cell_b_centered", style);

        style = createBorderedStyle(wb);

        style.setAlignment(HorizontalAlignment.RIGHT);

        style.setFont(font1);

        style.setDataFormat(df.getFormat("d-mmm"));

        styles.put("cell_b_date", style);

        style = createBorderedStyle(wb);

        style.setAlignment(HorizontalAlignment.RIGHT);

        style.setFont(font1);

        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        style.setDataFormat(df.getFormat("d-mmm"));

        styles.put("cell_g", style);

        Font font2 = wb.createFont();
　　　　　// 字体颜色
        font2.setColor(IndexedColors.BLUE.getIndex());

        font2.setBold(true);

        style = createBorderedStyle(wb);

        style.setAlignment(HorizontalAlignment.LEFT);

        style.setFont(font2);

        styles.put("cell_bb", style);

        style = createBorderedStyle(wb);

        style.setAlignment(HorizontalAlignment.RIGHT);

        style.setFont(font1);

        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        style.setDataFormat(df.getFormat("d-mmm"));

        styles.put("cell_bg", style);

        Font font3 = wb.createFont();
        // 字号

        font3.setFontHeightInPoints((short)14);

        font3.setColor(IndexedColors.DARK_BLUE.getIndex());

        font3.setBold(true);

        style = createBorderedStyle(wb);

        style.setAlignment(HorizontalAlignment.LEFT);

        style.setFont(font3);

        style.setWrapText(true);

        styles.put("cell_h", style);

        style = createBorderedStyle(wb);

        style.setAlignment(HorizontalAlignment.LEFT);

        style.setWrapText(true);

        styles.put("cell_normal", style);

        style = createBorderedStyle(wb);

        style.setAlignment(HorizontalAlignment.CENTER);

        style.setWrapText(true);

        styles.put("cell_normal_centered", style);

        style = createBorderedStyle(wb);

        style.setAlignment(HorizontalAlignment.RIGHT);

        style.setWrapText(true);

        style.setDataFormat(df.getFormat("d-mmm"));

        styles.put("cell_normal_date", style);

        style = createBorderedStyle(wb);

        style.setAlignment(HorizontalAlignment.LEFT);

　　　　　　// 缩进

        style.setIndention((short)1);

        style.setWrapText(true);

        styles.put("cell_indented", style);

        style = createBorderedStyle(wb);

        style.setFillForegroundColor(IndexedColors.BLUE.getIndex());

        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        styles.put("cell_blue", style);

        return styles;

    }

    private static CellStyle createBorderedStyle(Workbook wb){

        BorderStyle thin = BorderStyle.THIN;

        short black = IndexedColors.BLACK.getIndex();

        CellStyle style = wb.createCellStyle();

        style.setBorderRight(thin);

        style.setRightBorderColor(black);

        style.setBorderBottom(thin);

        style.setBottomBorderColor(black);

        style.setBorderLeft(thin);

        style.setLeftBorderColor(black);

        style.setBorderTop(thin);

        style.setTopBorderColor(black);

        return style;

    }

}

最新文章

  1. laravel下的数据序列化
  2. android窗口泄漏,isInEditMode解决可视化编辑器无法识别自定义控件的问题
  3. 装了虚拟机，但是没有虚拟网卡vmnet0 vmnet1 vmnet8
  4. Jquery手册
  5. 转 SQL Server中关于的checkpoint使用说明
  6. 【HDOJ】1720 A+B coming
  7. CSharp设计模式读书笔记（19）：备忘录模式(学习难度：★★☆☆☆，使用频率：★★☆☆☆)
  8. java泛型学习（一）
  9. 一道bfs与邻接表应用题
  10. 所谓 Spinner 组件
  11. MySQL 笔记整理（19） --为什么我只查一行的语句，也执行这么慢？
  12. JAVAEE 第六周
  13. 使用css的类名交集复合选择器 《转》
  14. MFC入门（一）-- 第一个简单的windows图形化界面小程序（打开计算器，记事本，查IP）
  15. mysql设置允许外网访问
  16. 【DeepLearning】Exercise: Implement deep networks for digit classification
  17. chrome Sources选项卡 设置JavaScript事件断点
  18. Unity消息简易框架 Advanced C# messenger
  19. e643. 以匿名类处理事件
  20. Hyperledger Chaincode启动过程

热门文章

  1. JSP页面通过c:forEach标签循环遍历List集合
  2. 888. Fair Candy Swap@python
  3. js 简单制作键盘模拟
  4. bzoj3774 最优选择
  5. [LUOGU] P2187 小Z的笔记
  6. 7. 配置undo表空间
  7. 04 Beautiful Soup
  8. 《算法导论》— Chapter 11 散列表
  9. manjaro xfce 18.0 踩坑记录
  10. Run-time Settings--General--Run Logic