POI初步学习1

转载 2015年11月18日 21:08:24

/*   
==================================================================== 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 my.exl; 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(3, 4);


    // 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();  }
    /**   * create a library of cell styles   */  private static    Map<String, CellStyle> createStyles(Workbook wb){
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    DataFormat df = wb.createDataFormat();

    CellStyle style;
    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    styles.put("header", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("header_date", style);

    Font font1 = wb.createFont();
    font1.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font1);
    styles.put("cell_b", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFont(font1);
    styles.put("cell_b_centered", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("cell_b_date", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("cell_g", style);

    Font font2 = wb.createFont();
    font2.setColor(IndexedColors.BLUE.getIndex());
    font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font2);
    styles.put("cell_bb", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font1);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.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.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font3);
    style.setWrapText(true);
    styles.put("cell_h", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setWrapText(true);
    styles.put("cell_normal", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    styles.put("cell_normal_centered", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setWrapText(true);
    style.setDataFormat(df.getFormat("d-mmm"));
    styles.put("cell_normal_date", style);

    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setIndention((short)1);
    style.setWrapText(true);
    styles.put("cell_indented", style);

    style = createBorderedStyle(wb);
    style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styles.put("cell_blue", style);

    return styles;  }
    private static CellStyle createBorderedStyle(Workbook wb){
    CellStyle style = wb.createCellStyle();
    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());
    return style;  } }

把数据导出到Excel表格中的方式

导出数据到Excel表格中,总的来说可以分为两种方式: 1、​Java导出(有很多成熟的开源类库可供使用,在此讲述由Apache提供的Wookbook) 2、JSP导出​ 首先说说JSP导...
  • u011897392
  • u011897392
  • 2015年09月24日 18:50
  • 1276

计算机专业初学者推荐书籍

以下这些书都是经典的经典的经典,每一本都值得细读、多读、读烂,特别是基础的书。 语言和编程基础: 1. 《C专家编程》  学C语言进阶的经典之作 http://item.jd.com...
  • dear_to_be
  • dear_to_be
  • 2014年05月19日 17:07
  • 890

OpenStreetMap学习(一)

前两天试着将离线地图导入了一个叫OSMand的APP里,发现还有 诸多的缺点,详见http://blog.csdn.net/u014712516/article/details/50480953 所以...
  • u014712516
  • u014712516
  • 2016年01月10日 11:12
  • 883

机器学习入门——线性代数简单回顾

本节课程回顾了一些简单但常用的线性代数知识,并对每个知识点列举具体例子,力求深入浅出。同时,最后还进行了编程实现,更利于实战应用。...
  • louishao
  • louishao
  • 2017年01月17日 17:40
  • 1442

JAVA POI的初步使用(持续更新)

下载地址https://archive.apache.org/dist/poi/release/bin/需要的jar包(我用的是3.10final) Poi-3.10-Final.jar ...
  • u013497977
  • u013497977
  • 2017年01月19日 00:05
  • 3000

基于机器学习方法的POI品类推荐算法

在美团商家数据中心(MDC),有超过100w的已校准审核的POI数据(我们一般将商家标示为POI,POI基础信息包括:门店名称、品类、电话、地址、坐标等)。如何使用这些已校准的POI数据,挖掘出有价值...
  • guohecang
  • guohecang
  • 2016年09月22日 12:31
  • 1101

java——POI学习——1

java POI学习
  • u010953706
  • u010953706
  • 2017年05月29日 23:44
  • 70

poi操作excel初步

一. 使用poi导出excelpublic class PoiWriteTest { public static void main(String[] args) { Stri...
  • zxc123e
  • zxc123e
  • 2015年04月28日 18:53
  • 311

HTML初步认识

HTML初步认识本篇博客给大家带来html最基础的东西,也就是初步认识html,打算从以下几个方面给大家介绍: 认识什么是纯文本文件txt HTML是负责描述文档语义的语言 HTML骨架和基本语法 可...
  • u010624986
  • u010624986
  • 2016年12月16日 23:46
  • 516

gSoap的初步学习——1

[1]VS2008+SP1  [2]WinSP+SP3  [3] gsoap_2.8.8.zip 正文 以实例形式描述,如何用gSOAP Toolkit实现Web Service功能,本文还捎...
  • u011981018
  • u011981018
  • 2014年11月17日 15:04
  • 541
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:POI初步学习1
举报原因:
原因补充:

(最多只允许输入30个字)