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;  } }

相关文章推荐

java 利用POI 导入导出初步学习

一、HSSF  97-2003       需要jar:poi-3.9.jar 简单示例:生成EXCEL                     //93---2003             St...

一起学习 POI (使用 maven 构建)(1)-HelloWorld

本学习笔记参考了 曹锋老师的《一头扎进 POI 视频教程》,特此说明,并向曹锋老师表示感谢。 一句话了解什么是POI。 Apache POI是Apache软件基金会的开放源码函式库,POI提供AP...

Hash Algorithm 初步 & BZOJ2081 POI2010 Beads

Hash Algorithm & BZOJ 2081 POI 2010 Breads

图论欧拉回路初步 & BZOJ2095 POI2010 Bridges

图论欧拉回路初步 & BZOJ2095 POI2010 Bridges

poi 3.17-beta1.jar

  • 2017年09月05日 17:05
  • 28.69MB
  • 下载

用到poi对word的读写,现在我想从1.doc中读取内容,然后修给其中的内容,再把内容写到2.doc中,要保留内容的样式,比如字体大小,内容排版,表格

import java.util.HashMap; import java.util.Map; import com.jacob.activeX.ActiveXComponent; ...

poi-bin-3.17-beta1-20170701

  • 2017年09月17日 09:57
  • 29.08MB
  • 下载

使用poi操作excel--1

1,常见用法 public class qq { public static void main(String args[]) throws Exception{ Workbook wb = ...
  • asdfzjs
  • asdfzjs
  • 2013年12月06日 13:56
  • 504
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:POI初步学习1
举报原因:
原因补充:

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