You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2009/06/07 17:39:55 UTC
svn commit: r782402 [3/8] - in /poi/tags/REL_3_5_BETA6: ./
src/contrib/src/org/apache/poi/contrib/poibrowser/ src/documentation/
src/documentation/content/xdocs/ src/documentation/content/xdocs/hpsf/
src/documentation/content/xdocs/news/ src/documentat...
Modified: poi/tags/REL_3_5_BETA6/src/examples/src/org/apache/poi/ss/examples/BusinessPlan.java
URL: http://svn.apache.org/viewvc/poi/tags/REL_3_5_BETA6/src/examples/src/org/apache/poi/ss/examples/BusinessPlan.java?rev=782402&r1=782401&r2=782402&view=diff
==============================================================================
--- poi/tags/REL_3_5_BETA6/src/examples/src/org/apache/poi/ss/examples/BusinessPlan.java (original)
+++ poi/tags/REL_3_5_BETA6/src/examples/src/org/apache/poi/ss/examples/BusinessPlan.java Sun Jun 7 15:39:51 2009
@@ -1,324 +1,325 @@
-/* ====================================================================
- 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(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;
- }
-}
+/* ====================================================================
+ 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(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;
+ }
+}
Modified: poi/tags/REL_3_5_BETA6/src/examples/src/org/apache/poi/ss/examples/CalendarDemo.java
URL: http://svn.apache.org/viewvc/poi/tags/REL_3_5_BETA6/src/examples/src/org/apache/poi/ss/examples/CalendarDemo.java?rev=782402&r1=782401&r2=782402&view=diff
==============================================================================
--- poi/tags/REL_3_5_BETA6/src/examples/src/org/apache/poi/ss/examples/CalendarDemo.java (original)
+++ poi/tags/REL_3_5_BETA6/src/examples/src/org/apache/poi/ss/examples/CalendarDemo.java Sun Jun 7 15:39:51 2009
@@ -1,242 +1,243 @@
-/* ====================================================================
- 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.util.CellRangeAddress;
-import org.apache.poi.ss.usermodel.*;
-import org.apache.poi.ss.usermodel.Font;
-import org.apache.poi.hssf.usermodel.HSSFWorkbook;
-
-import java.io.FileOutputStream;
-import java.util.Calendar;
-import java.util.Map;
-import java.util.HashMap;
-
-/**
- * A monthly calendar created using Apache POI. Each month is on a separate sheet.
- * <pre>
- * Usage:
- * CalendarDemo -xls|xlsx <year>
- * </pre>
- *
- * @author Yegor Kozlov
- */
-public class CalendarDemo {
-
- private static final String[] days = {
- "Sunday", "Monday", "Tuesday",
- "Wednesday", "Thursday", "Friday", "Saturday"};
-
- private static final String[] months = {
- "January", "February", "March","April", "May", "June","July", "August",
- "September","October", "November", "December"};
-
- 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 {
- 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 = "calendar.xls";
- if(wb instanceof XSSFWorkbook) file += "x";
- FileOutputStream out = new FileOutputStream(file);
- wb.write(out);
- out.close();
- }
-
- /**
- * cell styles used for formatting calendar sheets
- */
- private static Map<String, CellStyle> createStyles(Workbook wb){
- Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
-
- short borderColor = IndexedColors.GREY_50_PERCENT.getIndex();
-
- CellStyle style;
- Font titleFont = wb.createFont();
- titleFont.setFontHeightInPoints((short)48);
- titleFont.setColor(IndexedColors.DARK_BLUE.getIndex());
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_CENTER);
- style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
- style.setFont(titleFont);
- styles.put("title", style);
-
- Font monthFont = wb.createFont();
- monthFont.setFontHeightInPoints((short)12);
- monthFont.setColor(IndexedColors.WHITE.getIndex());
- monthFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_CENTER);
- style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
- style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- style.setFont(monthFont);
- styles.put("month", style);
-
- Font dayFont = wb.createFont();
- dayFont.setFontHeightInPoints((short)14);
- dayFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_LEFT);
- style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
- style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- style.setBorderLeft(CellStyle.BORDER_THIN);
- style.setLeftBorderColor(borderColor);
- style.setBorderBottom(CellStyle.BORDER_THIN);
- style.setBottomBorderColor(borderColor);
- style.setFont(dayFont);
- styles.put("weekend_left", style);
-
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_CENTER);
- style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
- style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- style.setBorderRight(CellStyle.BORDER_THIN);
- style.setRightBorderColor(borderColor);
- style.setBorderBottom(CellStyle.BORDER_THIN);
- style.setBottomBorderColor(borderColor);
- styles.put("weekend_right", style);
-
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_LEFT);
- style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
- style.setBorderLeft(CellStyle.BORDER_THIN);
- style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- style.setLeftBorderColor(borderColor);
- style.setBorderBottom(CellStyle.BORDER_THIN);
- style.setBottomBorderColor(borderColor);
- style.setFont(dayFont);
- styles.put("workday_left", style);
-
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_CENTER);
- style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
- style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- style.setBorderRight(CellStyle.BORDER_THIN);
- style.setRightBorderColor(borderColor);
- style.setBorderBottom(CellStyle.BORDER_THIN);
- style.setBottomBorderColor(borderColor);
- styles.put("workday_right", style);
-
- style = wb.createCellStyle();
- style.setBorderLeft(CellStyle.BORDER_THIN);
- style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- style.setBorderBottom(CellStyle.BORDER_THIN);
- style.setBottomBorderColor(borderColor);
- styles.put("grey_left", style);
-
- style = wb.createCellStyle();
- style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- style.setBorderRight(CellStyle.BORDER_THIN);
- style.setRightBorderColor(borderColor);
- style.setBorderBottom(CellStyle.BORDER_THIN);
- style.setBottomBorderColor(borderColor);
- styles.put("grey_right", style);
-
- return styles;
- }
-}
+/* ====================================================================
+ 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.util.CellRangeAddress;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.usermodel.Font;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+
+import java.io.FileOutputStream;
+import java.util.Calendar;
+import java.util.Map;
+import java.util.HashMap;
+
+/**
+ * A monthly calendar created using Apache POI. Each month is on a separate sheet.
+ * <pre>
+ * Usage:
+ * CalendarDemo -xls|xlsx <year>
+ * </pre>
+ *
+ * @author Yegor Kozlov
+ */
+public class CalendarDemo {
+
+ private static final String[] days = {
+ "Sunday", "Monday", "Tuesday",
+ "Wednesday", "Thursday", "Friday", "Saturday"};
+
+ private static final String[] months = {
+ "January", "February", "March","April", "May", "June","July", "August",
+ "September","October", "November", "December"};
+
+ 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 {
+ 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 = "calendar.xls";
+ if(wb instanceof XSSFWorkbook) file += "x";
+ FileOutputStream out = new FileOutputStream(file);
+ wb.write(out);
+ out.close();
+ }
+
+ /**
+ * cell styles used for formatting calendar sheets
+ */
+ private static Map<String, CellStyle> createStyles(Workbook wb){
+ Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
+
+ short borderColor = IndexedColors.GREY_50_PERCENT.getIndex();
+
+ CellStyle style;
+ Font titleFont = wb.createFont();
+ titleFont.setFontHeightInPoints((short)48);
+ titleFont.setColor(IndexedColors.DARK_BLUE.getIndex());
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_CENTER);
+ style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
+ style.setFont(titleFont);
+ styles.put("title", style);
+
+ Font monthFont = wb.createFont();
+ monthFont.setFontHeightInPoints((short)12);
+ monthFont.setColor(IndexedColors.WHITE.getIndex());
+ monthFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_CENTER);
+ style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
+ style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
+ style.setFillPattern(CellStyle.SOLID_FOREGROUND);
+ style.setFont(monthFont);
+ styles.put("month", style);
+
+ Font dayFont = wb.createFont();
+ dayFont.setFontHeightInPoints((short)14);
+ dayFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_LEFT);
+ style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
+ style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
+ style.setFillPattern(CellStyle.SOLID_FOREGROUND);
+ style.setBorderLeft(CellStyle.BORDER_THIN);
+ style.setLeftBorderColor(borderColor);
+ style.setBorderBottom(CellStyle.BORDER_THIN);
+ style.setBottomBorderColor(borderColor);
+ style.setFont(dayFont);
+ styles.put("weekend_left", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_CENTER);
+ style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
+ style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
+ style.setFillPattern(CellStyle.SOLID_FOREGROUND);
+ style.setBorderRight(CellStyle.BORDER_THIN);
+ style.setRightBorderColor(borderColor);
+ style.setBorderBottom(CellStyle.BORDER_THIN);
+ style.setBottomBorderColor(borderColor);
+ styles.put("weekend_right", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_LEFT);
+ style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
+ style.setBorderLeft(CellStyle.BORDER_THIN);
+ style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
+ style.setFillPattern(CellStyle.SOLID_FOREGROUND);
+ style.setLeftBorderColor(borderColor);
+ style.setBorderBottom(CellStyle.BORDER_THIN);
+ style.setBottomBorderColor(borderColor);
+ style.setFont(dayFont);
+ styles.put("workday_left", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_CENTER);
+ style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
+ style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
+ style.setFillPattern(CellStyle.SOLID_FOREGROUND);
+ style.setBorderRight(CellStyle.BORDER_THIN);
+ style.setRightBorderColor(borderColor);
+ style.setBorderBottom(CellStyle.BORDER_THIN);
+ style.setBottomBorderColor(borderColor);
+ styles.put("workday_right", style);
+
+ style = wb.createCellStyle();
+ style.setBorderLeft(CellStyle.BORDER_THIN);
+ style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
+ style.setFillPattern(CellStyle.SOLID_FOREGROUND);
+ style.setBorderBottom(CellStyle.BORDER_THIN);
+ style.setBottomBorderColor(borderColor);
+ styles.put("grey_left", style);
+
+ style = wb.createCellStyle();
+ style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
+ style.setFillPattern(CellStyle.SOLID_FOREGROUND);
+ style.setBorderRight(CellStyle.BORDER_THIN);
+ style.setRightBorderColor(borderColor);
+ style.setBorderBottom(CellStyle.BORDER_THIN);
+ style.setBottomBorderColor(borderColor);
+ styles.put("grey_right", style);
+
+ return styles;
+ }
+}
Modified: poi/tags/REL_3_5_BETA6/src/examples/src/org/apache/poi/ss/examples/LoanCalculator.java
URL: http://svn.apache.org/viewvc/poi/tags/REL_3_5_BETA6/src/examples/src/org/apache/poi/ss/examples/LoanCalculator.java?rev=782402&r1=782401&r2=782402&view=diff
==============================================================================
--- poi/tags/REL_3_5_BETA6/src/examples/src/org/apache/poi/ss/examples/LoanCalculator.java (original)
+++ poi/tags/REL_3_5_BETA6/src/examples/src/org/apache/poi/ss/examples/LoanCalculator.java Sun Jun 7 15:39:51 2009
@@ -1,304 +1,305 @@
-/* ====================================================================
- 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.ss.util.CellRangeAddress;
-import org.apache.poi.hssf.usermodel.HSSFWorkbook;
-
-import java.util.Map;
-import java.util.HashMap;
-import java.io.FileOutputStream;
-
-/**
- * Simple Loan Calculator. Demonstrates advance usage of cell formulas and named ranges.
- *
- * Usage:
- * LoanCalculator -xls|xlsx
- *
- * @author Yegor Kozlov
- */
-public class LoanCalculator {
-
- 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("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 = "loan-calculator.xls";
- if(wb instanceof XSSFWorkbook) file += "x";
- FileOutputStream out = new FileOutputStream(file);
- wb.write(out);
- out.close();
- }
-
- /**
- * cell styles used for formatting calendar sheets
- */
- private static Map<String, CellStyle> createStyles(Workbook wb){
- Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
-
- CellStyle style;
- Font titleFont = wb.createFont();
- titleFont.setFontHeightInPoints((short)14);
- titleFont.setFontName("Trebuchet MS");
- style = wb.createCellStyle();
- style.setFont(titleFont);
- style.setBorderBottom(CellStyle.BORDER_DOTTED);
- style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- styles.put("title", style);
-
- Font itemFont = wb.createFont();
- itemFont.setFontHeightInPoints((short)9);
- itemFont.setFontName("Trebuchet MS");
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_LEFT);
- style.setFont(itemFont);
- styles.put("item_left", style);
-
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_RIGHT);
- style.setFont(itemFont);
- styles.put("item_right", style);
-
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_RIGHT);
- style.setFont(itemFont);
- style.setBorderRight(CellStyle.BORDER_DOTTED);
- style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderBottom(CellStyle.BORDER_DOTTED);
- style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderLeft(CellStyle.BORDER_DOTTED);
- style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderTop(CellStyle.BORDER_DOTTED);
- style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setDataFormat(wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
- styles.put("input_$", style);
-
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_RIGHT);
- style.setFont(itemFont);
- style.setBorderRight(CellStyle.BORDER_DOTTED);
- style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderBottom(CellStyle.BORDER_DOTTED);
- style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderLeft(CellStyle.BORDER_DOTTED);
- style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderTop(CellStyle.BORDER_DOTTED);
- style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setDataFormat(wb.createDataFormat().getFormat("0.000%"));
- styles.put("input_%", style);
-
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_RIGHT);
- style.setFont(itemFont);
- style.setBorderRight(CellStyle.BORDER_DOTTED);
- style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderBottom(CellStyle.BORDER_DOTTED);
- style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderLeft(CellStyle.BORDER_DOTTED);
- style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderTop(CellStyle.BORDER_DOTTED);
- style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setDataFormat(wb.createDataFormat().getFormat("0"));
- styles.put("input_i", style);
-
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_CENTER);
- style.setFont(itemFont);
- style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy"));
- styles.put("input_d", style);
-
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_RIGHT);
- style.setFont(itemFont);
- style.setBorderRight(CellStyle.BORDER_DOTTED);
- style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderBottom(CellStyle.BORDER_DOTTED);
- style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderLeft(CellStyle.BORDER_DOTTED);
- style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderTop(CellStyle.BORDER_DOTTED);
- style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00"));
- style.setBorderBottom(CellStyle.BORDER_DOTTED);
- style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- styles.put("formula_$", style);
-
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_RIGHT);
- style.setFont(itemFont);
- style.setBorderRight(CellStyle.BORDER_DOTTED);
- style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderBottom(CellStyle.BORDER_DOTTED);
- style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderLeft(CellStyle.BORDER_DOTTED);
- style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setBorderTop(CellStyle.BORDER_DOTTED);
- style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setDataFormat(wb.createDataFormat().getFormat("0"));
- style.setBorderBottom(CellStyle.BORDER_DOTTED);
- style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- styles.put("formula_i", style);
-
- return styles;
- }
-
- //define named ranges for the inputs and formulas
- public static void createNames(Workbook wb){
- Name name;
-
- name = wb.createName();
- name.setNameName("Interest_Rate");
- name.setRefersToFormula("'Loan Calculator'!$E$5");
-
- name = wb.createName();
- name.setNameName("Loan_Amount");
- name.setRefersToFormula("'Loan Calculator'!$E$4");
-
- name = wb.createName();
- name.setNameName("Loan_Start");
- name.setRefersToFormula("'Loan Calculator'!$E$7");
-
- name = wb.createName();
- name.setNameName("Loan_Years");
- name.setRefersToFormula("'Loan Calculator'!$E$6");
-
- name = wb.createName();
- name.setNameName("Number_of_Payments");
- name.setRefersToFormula("'Loan Calculator'!$E$10");
-
- name = wb.createName();
- name.setNameName("Monthly_Payment");
- name.setRefersToFormula("-PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)");
-
- name = wb.createName();
- name.setNameName("Total_Cost");
- name.setRefersToFormula("'Loan Calculator'!$E$12");
-
- name = wb.createName();
- name.setNameName("Total_Interest");
- name.setRefersToFormula("'Loan Calculator'!$E$11");
-
- name = wb.createName();
- name.setNameName("Values_Entered");
- name.setRefersToFormula("IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Start>0,1,0)");
- }
-}
+/* ====================================================================
+ 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.ss.util.CellRangeAddress;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+
+import java.util.Map;
+import java.util.HashMap;
+import java.io.FileOutputStream;
+
+/**
+ * Simple Loan Calculator. Demonstrates advance usage of cell formulas and named ranges.
+ *
+ * Usage:
+ * LoanCalculator -xls|xlsx
+ *
+ * @author Yegor Kozlov
+ */
+public class LoanCalculator {
+
+ 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("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 = "loan-calculator.xls";
+ if(wb instanceof XSSFWorkbook) file += "x";
+ FileOutputStream out = new FileOutputStream(file);
+ wb.write(out);
+ out.close();
+ }
+
+ /**
+ * cell styles used for formatting calendar sheets
+ */
+ private static Map<String, CellStyle> createStyles(Workbook wb){
+ Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
+
+ CellStyle style;
+ Font titleFont = wb.createFont();
+ titleFont.setFontHeightInPoints((short)14);
+ titleFont.setFontName("Trebuchet MS");
+ style = wb.createCellStyle();
+ style.setFont(titleFont);
+ style.setBorderBottom(CellStyle.BORDER_DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ styles.put("title", style);
+
+ Font itemFont = wb.createFont();
+ itemFont.setFontHeightInPoints((short)9);
+ itemFont.setFontName("Trebuchet MS");
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_LEFT);
+ style.setFont(itemFont);
+ styles.put("item_left", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_RIGHT);
+ style.setFont(itemFont);
+ styles.put("item_right", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(CellStyle.BORDER_DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(CellStyle.BORDER_DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(CellStyle.BORDER_DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(CellStyle.BORDER_DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
+ styles.put("input_$", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(CellStyle.BORDER_DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(CellStyle.BORDER_DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(CellStyle.BORDER_DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(CellStyle.BORDER_DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("0.000%"));
+ styles.put("input_%", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(CellStyle.BORDER_DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(CellStyle.BORDER_DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(CellStyle.BORDER_DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(CellStyle.BORDER_DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("0"));
+ styles.put("input_i", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_CENTER);
+ style.setFont(itemFont);
+ style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy"));
+ styles.put("input_d", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(CellStyle.BORDER_DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(CellStyle.BORDER_DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(CellStyle.BORDER_DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(CellStyle.BORDER_DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00"));
+ style.setBorderBottom(CellStyle.BORDER_DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
+ style.setFillPattern(CellStyle.SOLID_FOREGROUND);
+ styles.put("formula_$", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(CellStyle.ALIGN_RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(CellStyle.BORDER_DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(CellStyle.BORDER_DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(CellStyle.BORDER_DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(CellStyle.BORDER_DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("0"));
+ style.setBorderBottom(CellStyle.BORDER_DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
+ style.setFillPattern(CellStyle.SOLID_FOREGROUND);
+ styles.put("formula_i", style);
+
+ return styles;
+ }
+
+ //define named ranges for the inputs and formulas
+ public static void createNames(Workbook wb){
+ Name name;
+
+ name = wb.createName();
+ name.setNameName("Interest_Rate");
+ name.setRefersToFormula("'Loan Calculator'!$E$5");
+
+ name = wb.createName();
+ name.setNameName("Loan_Amount");
+ name.setRefersToFormula("'Loan Calculator'!$E$4");
+
+ name = wb.createName();
+ name.setNameName("Loan_Start");
+ name.setRefersToFormula("'Loan Calculator'!$E$7");
+
+ name = wb.createName();
+ name.setNameName("Loan_Years");
+ name.setRefersToFormula("'Loan Calculator'!$E$6");
+
+ name = wb.createName();
+ name.setNameName("Number_of_Payments");
+ name.setRefersToFormula("'Loan Calculator'!$E$10");
+
+ name = wb.createName();
+ name.setNameName("Monthly_Payment");
+ name.setRefersToFormula("-PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)");
+
+ name = wb.createName();
+ name.setNameName("Total_Cost");
+ name.setRefersToFormula("'Loan Calculator'!$E$12");
+
+ name = wb.createName();
+ name.setNameName("Total_Interest");
+ name.setRefersToFormula("'Loan Calculator'!$E$11");
+
+ name = wb.createName();
+ name.setNameName("Values_Entered");
+ name.setRefersToFormula("IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Start>0,1,0)");
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org