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 2008/10/29 20:12:48 UTC
svn commit: r708982 [1/3] - in /poi/branches/ooxml/src:
examples/src/org/apache/poi/xssf/usermodel/examples/
java/org/apache/poi/ss/usermodel/
ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/
ooxml/java/org/apache/poi/xssf/model/ ooxml/java/org/apac...
Author: yegor
Date: Wed Oct 29 12:12:47 2008
New Revision: 708982
URL: http://svn.apache.org/viewvc?rev=708982&view=rev
Log:
more cleanup and refactoring of ooxml code,added more unit test and 3 rich examples: LoanCalculator, CalendarDemo and TimesheetDemo, numerous odds and ends improvements
Added:
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CalendarDemo.java (with props)
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/LoanCalculator.java (with props)
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/NewLinesInCells.java
- copied, changed from r708505, poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CellNewlines.java
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/TimesheetDemo.java (with props)
poi/branches/ooxml/src/java/org/apache/poi/ss/usermodel/DataFormatter.java (with props)
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFColor.java
- copied, changed from r708505, poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFColor.java
poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetHiding.java (with props)
poi/branches/ooxml/src/testcases/org/apache/poi/hssf/data/TwoSheetsNoneHidden.xlsx (with props)
poi/branches/ooxml/src/testcases/org/apache/poi/hssf/data/TwoSheetsOneHidden.xlsx (with props)
Removed:
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CellNewlines.java
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateNewSpreadsheet.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFColor.java
Modified:
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/AligningCells.java
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateCell.java
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/FillsAndColors.java
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/IterateCells.java
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/MergingCells.java
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithBorders.java
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithPageSetup.java
poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithRichText.java
poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/CellStyle.java
poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Sheet.java
poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFActiveXData.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFont.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPrintSetup.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellBorder.java
poi/branches/ooxml/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellFill.java
poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/eventusermodel/TestXSSFReader.java
poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/model/TestStylesTable.java
poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCellStyle.java
poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFont.java
poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java
poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java
poi/branches/ooxml/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
Modified: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/AligningCells.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/AligningCells.java?rev=708982&r1=708981&r2=708982&view=diff
==============================================================================
--- poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/AligningCells.java (original)
+++ poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/AligningCells.java Wed Oct 29 12:12:47 2008
@@ -23,49 +23,51 @@
import java.io.FileOutputStream;
/**
- * Demonstrates various alignment options
+ * Shows how various alignment options work.
*/
public class AligningCells {
- public static void main(String[] args)
- throws Exception
- {
- Workbook wb = new XSSFWorkbook();
-
- Sheet sheet = wb.createSheet("new sheet");
- Row row = sheet.createRow((short) 2);
-
- createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM);
- createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM);
- createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER);
- createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER);
- createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY);
- createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP);
- createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP);
-
- // Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("aligning.xlsx");
- wb.write(fileOut);
- fileOut.close();
+ public static void main(String[] args) throws Exception {
+ Workbook wb = new XSSFWorkbook();
+ Sheet sheet = wb.createSheet();
+ Row row = sheet.createRow((short) 2);
+ row.setHeightInPoints(30);
+ for (int i = 0; i < 8; i++) {
+ //column width is set in units of 1/256th of a character width
+ sheet.setColumnWidth(i, 256*15);
}
- /**
- * Creates a cell and aligns it a certain way.
- *
- * @param wb the workbook
- * @param row the row to create the cell in
- * @param column the column number to create the cell in
- * @param halign the horizontal alignment for the cell.
- */
- private static void createCell(Workbook wb, Row row, short column, short halign, short valign)
- {
- Cell cell = row.createCell(column);
- cell.setCellValue(new XSSFRichTextString("Align It"));
- CellStyle cellStyle = wb.createCellStyle();
- cellStyle.setAlignment(halign);
- cellStyle.setVerticalAlignment(valign);
- cell.setCellStyle(cellStyle);
- }
+ createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM);
+ createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM);
+ createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER);
+ createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER);
+ createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY);
+ createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP);
+ createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP);
+
+ // Write the output to a file
+ FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx");
+ wb.write(fileOut);
+ fileOut.close();
+
+ }
+
+ /**
+ * Creates a cell and aligns it a certain way.
+ *
+ * @param wb the workbook
+ * @param row the row to create the cell in
+ * @param column the column number to create the cell in
+ * @param halign the horizontal alignment for the cell.
+ */
+ private static void createCell(Workbook wb, Row row, short column, short halign, short valign) {
+ Cell cell = row.createCell(column);
+ cell.setCellValue(new XSSFRichTextString("Align It"));
+ CellStyle cellStyle = wb.createCellStyle();
+ cellStyle.setAlignment(halign);
+ cellStyle.setVerticalAlignment(valign);
+ cell.setCellStyle(cellStyle);
+ }
}
Added: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CalendarDemo.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CalendarDemo.java?rev=708982&view=auto
==============================================================================
--- poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CalendarDemo.java (added)
+++ poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CalendarDemo.java Wed Oct 29 12:12:47 2008
@@ -0,0 +1,224 @@
+/* ====================================================================
+ 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.xssf.usermodel.examples;
+
+import org.apache.poi.xssf.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.ss.usermodel.*;
+
+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 <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();
+ if(args.length > 0) calendar.set(Calendar.YEAR, Integer.parseInt(args[0]));
+
+ int year = calendar.get(Calendar.YEAR);
+
+ XSSFWorkbook wb = new XSSFWorkbook();
+ Map<String, XSSFCellStyle> 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
+ XSSFSheet sheet = wb.createSheet(months[month]);
+
+ //turn off gridlines
+ sheet.setDisplayGridlines(false);
+ sheet.setPrintGridlines(false);
+ XSSFPrintSetup printSetup = sheet.getPrintSetup();
+ printSetup.setOrientation(PrintOrientation.LANDSCAPE);
+ sheet.setFitToPage(true);
+ sheet.setHorizontallyCenter(true);
+
+ //the header row: centered text in 48pt font
+ XSSFRow headerRow = sheet.createRow(0);
+ headerRow.setHeightInPoints(80);
+ XSSFCell 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
+ XSSFRow monthRow = sheet.createRow(1);
+ for (int i = 0; i < days.length; i++) {
+ //for compatibility with HSSF we have to set column width 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));
+ XSSFCell 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++) {
+ XSSFRow row = sheet.createRow(rownum++);
+ row.setHeightInPoints(100);
+ for (int i = 0; i < days.length; i++) {
+ XSSFCell dayCell_1 = row.createCell(i*2);
+ XSSFCell 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
+ FileOutputStream out = new FileOutputStream("calendar-"+year+".xlsx");
+ wb.write(out);
+ out.close();
+ }
+
+ /**
+ * cell styles used for formatting calendar sheets
+ */
+ public static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
+ Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
+
+ XSSFCellStyle style;
+ XSSFFont titleFont = wb.createFont();
+ titleFont.setFontHeightInPoints((short)48);
+ titleFont.setColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFont(titleFont);
+ styles.put("title", style);
+
+ XSSFFont monthFont = wb.createFont();
+ monthFont.setFontHeightInPoints((short)12);
+ monthFont.setColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
+ monthFont.setBold(true);
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setFont(monthFont);
+ styles.put("month", style);
+
+ XSSFFont dayFont = wb.createFont();
+ dayFont.setFontHeightInPoints((short)14);
+ dayFont.setBold(true);
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.LEFT);
+ style.setVerticalAlignment(VerticalAlignment.TOP);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(228, 232, 243)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setBorderLeft(BorderStyle.THIN);
+ style.setLeftBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setFont(dayFont);
+ styles.put("weekend_left", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.TOP);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(228, 232, 243)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setBorderRight(BorderStyle.THIN);
+ style.setRightBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ styles.put("weekend_right", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.LEFT);
+ style.setVerticalAlignment(VerticalAlignment.TOP);
+ style.setBorderLeft(BorderStyle.THIN);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setLeftBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setFont(dayFont);
+ styles.put("workday_left", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.TOP);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setBorderRight(BorderStyle.THIN);
+ style.setRightBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ styles.put("workday_right", style);
+
+ style = wb.createCellStyle();
+ style.setBorderLeft(BorderStyle.THIN);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ styles.put("grey_left", style);
+
+ style = wb.createCellStyle();
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setBorderRight(BorderStyle.THIN);
+ style.setRightBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ styles.put("grey_right", style);
+
+ return styles;
+ }
+}
Propchange: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CalendarDemo.java
------------------------------------------------------------------------------
svn:executable = *
Modified: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateCell.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateCell.java?rev=708982&r1=708981&r2=708982&view=diff
==============================================================================
--- poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateCell.java (original)
+++ poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateCell.java Wed Oct 29 12:12:47 2008
@@ -25,7 +25,7 @@
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
- *
+ * Illustrates how to create cell and set values of different types.
*/
public class CreateCell {
Modified: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/FillsAndColors.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/FillsAndColors.java?rev=708982&r1=708981&r2=708982&view=diff
==============================================================================
--- poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/FillsAndColors.java (original)
+++ poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/FillsAndColors.java Wed Oct 29 12:12:47 2008
@@ -29,31 +29,31 @@
public class FillsAndColors {
public static void main(String[] args) throws Exception {
Workbook wb = new XSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
+ Sheet sheet = wb.createSheet("new sheet");
- // Create a row and put some cells in it. Rows are 0 based.
- Row row = sheet.createRow((short) 1);
+ // Create a row and put some cells in it. Rows are 0 based.
+ Row row = sheet.createRow((short) 1);
- // Aqua background
- CellStyle style = wb.createCellStyle();
- style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
- style.setFillPattern(CellStyle.BIG_SPOTS);
- Cell cell = row.createCell((short) 1);
- cell.setCellValue(new XSSFRichTextString("X"));
- cell.setCellStyle(style);
+ // Aqua background
+ CellStyle style = wb.createCellStyle();
+ style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
+ style.setFillPattern(CellStyle.BIG_SPOTS);
+ Cell cell = row.createCell((short) 1);
+ cell.setCellValue(new XSSFRichTextString("X"));
+ cell.setCellStyle(style);
- // Orange "foreground", foreground being the fill foreground not the font color.
- style = wb.createCellStyle();
- style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- cell = row.createCell((short) 2);
- cell.setCellValue(new XSSFRichTextString("X"));
- cell.setCellStyle(style);
+ // Orange "foreground", foreground being the fill foreground not the font color.
+ style = wb.createCellStyle();
+ style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
+ style.setFillPattern(CellStyle.SOLID_FOREGROUND);
+ cell = row.createCell((short) 2);
+ cell.setCellValue(new XSSFRichTextString("X"));
+ cell.setCellStyle(style);
- // Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("fill_colors.xlsx");
- wb.write(fileOut);
- fileOut.close();
+ // Write the output to a file
+ FileOutputStream fileOut = new FileOutputStream("fill_colors.xlsx");
+ wb.write(fileOut);
+ fileOut.close();
}
}
Modified: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/IterateCells.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/IterateCells.java?rev=708982&r1=708981&r2=708982&view=diff
==============================================================================
--- poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/IterateCells.java (original)
+++ poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/IterateCells.java Wed Oct 29 12:12:47 2008
@@ -28,7 +28,7 @@
public class IterateCells {
public static void main(String[] args) throws Exception {
- Workbook wb = null;
+ Workbook wb = new XSSFWorkbook(args[0]);
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
System.out.println(wb.getSheetName(i));
Added: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/LoanCalculator.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/LoanCalculator.java?rev=708982&view=auto
==============================================================================
--- poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/LoanCalculator.java (added)
+++ poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/LoanCalculator.java Wed Oct 29 12:12:47 2008
@@ -0,0 +1,312 @@
+/* ====================================================================
+ 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.xssf.usermodel.examples;
+
+import org.apache.poi.xssf.usermodel.*;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+import java.util.Map;
+import java.util.HashMap;
+import java.io.FileOutputStream;
+
+/**
+ * Simple Loan Calculator
+ *
+ * @author Yegor Kozlov
+ */
+public class LoanCalculator {
+
+ public static void main(String[] args) throws Exception {
+ XSSFWorkbook wb = new XSSFWorkbook();
+ Map<String, XSSFCellStyle> styles = createStyles(wb);
+ XSSFSheet sheet = wb.createSheet("Loan Calculator");
+ sheet.setPrintGridlines(false);
+ sheet.setDisplayGridlines(false);
+
+ XSSFPrintSetup printSetup = sheet.getPrintSetup();
+ printSetup.setOrientation(PrintOrientation.LANDSCAPE);
+ 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);
+
+ XSSFRow titleRow = sheet.createRow(0);
+ titleRow.setHeightInPoints(35);
+ for (int i = 1; i <= 7; i++) {
+ titleRow.createCell(i).setCellStyle(styles.get("title"));
+ }
+ XSSFCell titleCell = titleRow.getCell(2);
+ titleCell.setCellValue("Simple Loan Calculator");
+ sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1"));
+
+ XSSFRow row = sheet.createRow(2);
+ XSSFCell 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_$"));
+
+ 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_$"));
+
+ sheet.setActiveCell("E4");
+
+ // Write the output to a file
+ FileOutputStream out = new FileOutputStream("loan-calculator.xlsx");
+ wb.write(out);
+ out.close();
+ }
+
+ /**
+ * cell styles used for formatting calendar sheets
+ */
+ public static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
+ Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
+
+ XSSFCellStyle style;
+ XSSFFont titleFont = wb.createFont();
+ titleFont.setFontHeightInPoints((short)14);
+ titleFont.setFontName("Trebuchet MS");
+ style = wb.createCellStyle();
+ style.setFont(titleFont);
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ styles.put("title", style);
+
+ XSSFFont itemFont = wb.createFont();
+ itemFont.setFontHeightInPoints((short)9);
+ itemFont.setFontName("Trebuchet MS");
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.LEFT);
+ style.setFont(itemFont);
+ styles.put("item_left", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.RIGHT);
+ style.setFont(itemFont);
+ styles.put("item_right", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(BorderStyle.DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(BorderStyle.DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(BorderStyle.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(HorizontalAlignment.RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(BorderStyle.DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(BorderStyle.DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(BorderStyle.DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("0.000%"));
+ styles.put("input_%", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(BorderStyle.DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(BorderStyle.DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(BorderStyle.DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("0"));
+ styles.put("input_i", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setFont(itemFont);
+ style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy"));
+ styles.put("input_d", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(BorderStyle.DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(BorderStyle.DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(BorderStyle.DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00"));
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ styles.put("formula_$", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(BorderStyle.DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(BorderStyle.DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(BorderStyle.DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("0"));
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ styles.put("formula_i", style);
+
+ return styles;
+ }
+
+ //define named ranges for the inputs and formulas
+ public static void createNames(XSSFWorkbook wb){
+ XSSFName name;
+
+ name = wb.createName();
+ name.setNameName("Header_Row");
+ name.setReference("ROW('Loan Calculator'!#REF!)");
+
+ name = wb.createName();
+ name.setNameName("Interest_Rate");
+ name.setReference("'Loan Calculator'!$E$5");
+
+ name = wb.createName();
+ name.setNameName("Loan_Amount");
+ name.setReference("'Loan Calculator'!$E$4");
+
+ name = wb.createName();
+ name.setNameName("Loan_Not_Paid");
+ name.setReference("F(Payment_Number<=Number_of_Payments,1,0)");
+
+ name = wb.createName();
+ name.setNameName("Loan_Start");
+ name.setReference("'Loan Calculator'!$E$7");
+
+ name = wb.createName();
+ name.setNameName("Loan_Years");
+ name.setReference("'Loan Calculator'!$E$6");
+
+ name = wb.createName();
+ name.setNameName("Monthly_Payment");
+ name.setReference("-PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)");
+
+ name = wb.createName();
+ name.setNameName("Number_of_Payments");
+ name.setReference("'Loan Calculator'!$E$10");
+
+ name = wb.createName();
+ name.setNameName("Payment_Number");
+ name.setReference("ROW()-Header_Row");
+
+ name = wb.createName();
+ name.setNameName("Principal");
+ name.setReference("-PPMT(Interest_Rate/12,Payment_Number,Number_of_Payments,Loan_Amount)");
+
+ name = wb.createName();
+ name.setNameName("Total_Cost");
+ name.setReference("'Loan Calculator'!$E$12");
+
+ name = wb.createName();
+ name.setNameName("Total_Interest");
+ name.setReference("'Loan Calculator'!$E$11");
+
+ name = wb.createName();
+ name.setNameName("Values_Entered");
+ name.setReference("IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Start>0,1,0)");
+
+
+ }
+}
Propchange: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/LoanCalculator.java
------------------------------------------------------------------------------
svn:executable = *
Modified: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/MergingCells.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/MergingCells.java?rev=708982&r1=708981&r2=708982&view=diff
==============================================================================
--- poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/MergingCells.java (original)
+++ poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/MergingCells.java Wed Oct 29 12:12:47 2008
@@ -28,7 +28,7 @@
import java.io.FileOutputStream;
/**
- * Merging cells
+ * An example of how to merge regions of cells.
*/
public class MergingCells {
public static void main(String[] args) throws Exception {
Copied: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/NewLinesInCells.java (from r708505, poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CellNewlines.java)
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/NewLinesInCells.java?p2=poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/NewLinesInCells.java&p1=poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CellNewlines.java&r1=708505&r2=708982&rev=708982&view=diff
==============================================================================
--- poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/CellNewlines.java (original)
+++ poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/NewLinesInCells.java Wed Oct 29 12:12:47 2008
@@ -28,7 +28,7 @@
/**
* How to use newlines in cells
*/
-public class CellNewlines {
+public class NewLinesInCells {
public static void main(String[]args) throws Exception {
Workbook wb = new XSSFWorkbook();
Added: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/TimesheetDemo.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/TimesheetDemo.java?rev=708982&view=auto
==============================================================================
--- poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/TimesheetDemo.java (added)
+++ poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/TimesheetDemo.java Wed Oct 29 12:12:47 2008
@@ -0,0 +1,209 @@
+/* ====================================================================
+ 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.xssf.usermodel.examples;
+
+import org.apache.poi.xssf.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.ss.usermodel.*;
+
+import java.util.Map;
+import java.util.HashMap;
+import java.io.FileOutputStream;
+
+/**
+ * A weekly timesheet created using Apache POI.
+ *
+ * @author Yegor Kozlov
+ */
+public class TimesheetDemo {
+ private static final String[] titles = {
+ "Person", "ID", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun",
+ "Total\nHrs", "Overtime\nHrs", "Regular\nHrs"
+ };
+
+ private static Object[][] sample_data = {
+ {"Yegor Kozlov", "YK", 5.0, 8.0, 10.0, 5.0, 5.0, 7.0, 6.0},
+ {"Gisella Bronsetti", "GB", 4.0, 3.0, 1.0, 3.5, null, null, 4.0},
+ };
+
+ public static void main(String[] args) throws Exception {
+
+ XSSFWorkbook wb = new XSSFWorkbook();
+ Map<String, XSSFCellStyle> styles = createStyles(wb);
+
+ XSSFSheet sheet = wb.createSheet("Timesheet");
+ XSSFPrintSetup printSetup = sheet.getPrintSetup();
+ printSetup.setOrientation(PrintOrientation.LANDSCAPE);
+ sheet.setFitToPage(true);
+ sheet.setHorizontallyCenter(true);
+
+ //title row
+ XSSFRow titleRow = sheet.createRow(0);
+ titleRow.setHeightInPoints(45);
+ XSSFCell titleCell = titleRow.createCell(0);
+ titleCell.setCellValue("Weekly Timesheet");
+ titleCell.setCellStyle(styles.get("title"));
+ sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
+
+ //header row
+ XSSFRow headerRow = sheet.createRow(1);
+ headerRow.setHeightInPoints(40);
+ XSSFCell headerCell;
+ for (int i = 0; i < titles.length; i++) {
+ headerCell = headerRow.createCell(i);
+ headerCell.setCellValue(titles[i]);
+ headerCell.setCellStyle(styles.get("header"));
+ }
+
+ int rownum = 2;
+ for (int i = 0; i < 10; i++) {
+ XSSFRow row = sheet.createRow(rownum++);
+ for (int j = 0; j < titles.length; j++) {
+ XSSFCell cell = row.createCell(j);
+ if(j == 9){
+ //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
+ String ref = "C" +rownum+ ":I" + rownum;
+ cell.setCellFormula("SUM("+ref+")");
+ cell.setCellStyle(styles.get("formula"));
+ } else if (j == 11){
+ cell.setCellFormula("J" +rownum+ "-K" + rownum);
+ cell.setCellStyle(styles.get("formula"));
+ } else {
+ cell.setCellStyle(styles.get("cell"));
+ }
+ }
+ }
+
+ //row with totals below
+ XSSFRow sumRow = sheet.createRow(rownum++);
+ sumRow.setHeightInPoints(35);
+ XSSFCell cell;
+ cell = sumRow.createCell(0);
+ cell.setCellStyle(styles.get("formula"));
+ cell = sumRow.createCell(1);
+ cell.setCellValue("Total Hrs:");
+ cell.setCellStyle(styles.get("formula"));
+
+ for (int j = 2; j < 12; j++) {
+ cell = sumRow.createCell(j);
+ String ref = (char)('A' + j) + "3:" + (char)('A' + j) + "12";
+ cell.setCellFormula("SUM(" + ref + ")");
+ if(j >= 9) cell.setCellStyle(styles.get("formula_2"));
+ else cell.setCellStyle(styles.get("formula"));
+ }
+ rownum++;
+ sumRow = sheet.createRow(rownum++);
+ sumRow.setHeightInPoints(25);
+ cell = sumRow.createCell(0);
+ cell.setCellValue("Total Regular Hours");
+ cell.setCellStyle(styles.get("formula"));
+ cell = sumRow.createCell(1);
+ cell.setCellFormula("L13");
+ cell.setCellStyle(styles.get("formula_2"));
+ sumRow = sheet.createRow(rownum++);
+ sumRow.setHeightInPoints(25);
+ cell = sumRow.createCell(0);
+ cell.setCellValue("Total Overtime Hours");
+ cell.setCellStyle(styles.get("formula"));
+ cell = sumRow.createCell(1);
+ cell.setCellFormula("K13");
+ cell.setCellStyle(styles.get("formula_2"));
+
+ //set sample data
+ for (int i = 0; i < sample_data.length; i++) {
+ XSSFRow row = sheet.getRow(2 + i);
+ for (int j = 0; j < sample_data[i].length; j++) {
+ if(sample_data[i][j] == null) continue;
+
+ if(sample_data[i][j] instanceof String) {
+ row.getCell(j).setCellValue((String)sample_data[i][j]);
+ } else {
+ row.getCell(j).setCellValue((Double)sample_data[i][j]);
+ }
+ }
+ }
+
+ //finally set column widths
+ sheet.setColumnWidth(0, 30*256);
+ for (int i = 2; i < 9; i++) {
+ sheet.setColumnWidth(i, 6*256);
+ }
+
+ // Write the output to a file
+ FileOutputStream out = new FileOutputStream("ooxml-timesheet.xlsx");
+ wb.write(out);
+ out.close();
+ }
+
+ public static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
+ Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
+ XSSFCellStyle style;
+ XSSFFont titleFont = wb.createFont();
+ titleFont.setFontHeightInPoints((short)18);
+ titleFont.setBold(true);
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setFont(titleFont);
+ styles.put("title", style);
+
+ XSSFFont monthFont = wb.createFont();
+ monthFont.setFontHeightInPoints((short)11);
+ monthFont.setColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(102, 102, 102)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setFont(monthFont);
+ style.setWrapText(true);
+ styles.put("header", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setWrapText(true);
+ style.setBorderRight(BorderStyle.THIN);
+ style.setRightBorderColor(IndexedColors.BLACK.getIndex());
+ style.setBorderLeft(BorderStyle.THIN);
+ style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
+ style.setBorderTop(BorderStyle.THIN);
+ style.setTopBorderColor(IndexedColors.BLACK.getIndex());
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
+ styles.put("cell", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
+ styles.put("formula", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(192, 192, 192)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
+ styles.put("formula_2", style);
+
+ return styles;
+ }
+}
Propchange: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/TimesheetDemo.java
------------------------------------------------------------------------------
svn:executable = *
Modified: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithBorders.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithBorders.java?rev=708982&r1=708981&r2=708982&view=diff
==============================================================================
--- poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithBorders.java (original)
+++ poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithBorders.java Wed Oct 29 12:12:47 2008
@@ -28,7 +28,7 @@
public class WorkingWithBorders {
public static void main(String[] args) throws Exception {
Workbook wb = new XSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
+ Sheet sheet = wb.createSheet("borders");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short) 1);
@@ -50,7 +50,7 @@
cell.setCellStyle(style);
// Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("workbook_borders.xlsx");
+ FileOutputStream fileOut = new FileOutputStream("xssf-borders.xlsx");
wb.write(fileOut);
fileOut.close();
Modified: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithPageSetup.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithPageSetup.java?rev=708982&r1=708981&r2=708982&view=diff
==============================================================================
--- poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithPageSetup.java (original)
+++ poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithPageSetup.java Wed Oct 29 12:12:47 2008
@@ -71,7 +71,7 @@
wb.setPrintArea(0, 1, 2, 0, 3);
- FileOutputStream fileOut = new FileOutputStream("ooxml-printsetup.xlsx");
+ FileOutputStream fileOut = new FileOutputStream("xssf-printsetup.xlsx");
wb.write(fileOut);
fileOut.close();
}
Modified: poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithRichText.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithRichText.java?rev=708982&r1=708981&r2=708982&view=diff
==============================================================================
--- poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithRichText.java (original)
+++ poi/branches/ooxml/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithRichText.java Wed Oct 29 12:12:47 2008
@@ -22,36 +22,35 @@
import java.io.FileOutputStream;
/**
- * Demonstrates how to work with rich text
+ * Demonstrates how to work with rich text
*/
public class WorkingWithRichText {
- public static void main(String[] args)
- throws Exception
- {
- XSSFWorkbook wb = new XSSFWorkbook();
-
- XSSFSheet sheet = wb.createSheet();
- XSSFRow row = sheet.createRow((short) 2);
-
- XSSFCell cell = row.createCell(1);
- XSSFRichTextString rt = new XSSFRichTextString("The quick");
-
- XSSFFont font1 = wb.createFont();
- font1.setBold(true);
- rt.append(" brown fox", font1);
-
- XSSFFont font2 = wb.createFont();
- font2.setItalic(true);
- font2.setColor(IndexedColors.RED.getIndex());
- rt.applyFont((short)0);
- cell.setCellValue(rt);
-
- // Write the output to a file
- FileOutputStream fileOut = new FileOutputStream("rich_text.xlsx");
- wb.write(fileOut);
- fileOut.close();
+ public static void main(String[] args) throws Exception {
+
+ XSSFWorkbook wb = new XSSFWorkbook();
+
+ XSSFSheet sheet = wb.createSheet();
+ XSSFRow row = sheet.createRow((short) 2);
+
+ XSSFCell cell = row.createCell(1);
+ XSSFRichTextString rt = new XSSFRichTextString("The quick");
+
+ XSSFFont font1 = wb.createFont();
+ font1.setBold(true);
+ rt.append(" brown fox", font1);
+
+ XSSFFont font2 = wb.createFont();
+ font2.setItalic(true);
+ font2.setColor(IndexedColors.RED.getIndex());
+ rt.applyFont((short) 0);
+ cell.setCellValue(rt);
+
+ // Write the output to a file
+ FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx");
+ wb.write(fileOut);
+ fileOut.close();
- }
+ }
- }
+}
Added: poi/branches/ooxml/src/java/org/apache/poi/ss/usermodel/DataFormatter.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/ss/usermodel/DataFormatter.java?rev=708982&view=auto
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/ss/usermodel/DataFormatter.java (added)
+++ poi/branches/ooxml/src/java/org/apache/poi/ss/usermodel/DataFormatter.java Wed Oct 29 12:12:47 2008
@@ -0,0 +1,673 @@
+/* ====================================================================
+ 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.usermodel;
+
+import java.util.regex.Pattern;
+import java.util.regex.Matcher;
+import java.util.*;
+import java.text.*;
+
+/**
+ * DataFormatter contains methods for formatting the value stored in an
+ * Cell. This can be useful for reports and GUI presentations when you
+ * need to display data exactly as it appears in Excel. Supported formats
+ * include currency, SSN, percentages, decimals, dates, phone numbers, zip
+ * codes, etc.
+ * <p>
+ * Internally, formats will be implemented using subclasses of {@link Format}
+ * such as {@link DecimalFormat} and {@link SimpleDateFormat}. Therefore the
+ * formats used by this class must obey the same pattern rules as these Format
+ * subclasses. This means that only legal number pattern characters ("0", "#",
+ * ".", "," etc.) may appear in number formats. Other characters can be
+ * inserted <em>before</em> or <em> after</em> the number pattern to form a
+ * prefix or suffix.
+ * </p>
+ * <p>
+ * For example the Excel pattern <code>"$#,##0.00 "USD"_);($#,##0.00 "USD")"
+ * </code> will be correctly formatted as "$1,000.00 USD" or "($1,000.00 USD)".
+ * However the pattern <code>"00-00-00"</code> is incorrectly formatted by
+ * DecimalFormat as "000000--". For Excel formats that are not compatible with
+ * DecimalFormat, you can provide your own custom {@link Format} implementation
+ * via <code>DataFormatter.addFormat(String,Format)</code>. The following
+ * custom formats are already provided by this class:
+ * </p>
+ * <pre>
+ * <ul><li>SSN "000-00-0000"</li>
+ * <li>Phone Number "(###) ###-####"</li>
+ * <li>Zip plus 4 "00000-0000"</li>
+ * </ul>
+ * </pre>
+ * <p>
+ * If the Excel format pattern cannot be parsed successfully, then a default
+ * format will be used. The default number format will mimic the Excel General
+ * format: "#" for whole numbers and "#.##########" for decimal numbers. You
+ * can override the default format pattern with <code>
+ * DataFormatter.setDefaultNumberFormat(Format)</code>. <b>Note:</b> the
+ * default format will only be used when a Format cannot be created from the
+ * cell's data format string.
+ *
+ * @author James May (james dot may at fmr dot com)
+ *
+ */
+public class DataFormatter {
+
+ /** Pattern to find a number format: "0" or "#" */
+ private static final Pattern numPattern = Pattern.compile("[0#]+");
+
+ /** Pattern to find days of week as text "ddd...." */
+ private static final Pattern daysAsText = Pattern.compile("([d]{3,})", Pattern.CASE_INSENSITIVE);
+
+ /** Pattern to find "AM/PM" marker */
+ private static final Pattern amPmPattern = Pattern.compile("((A|P)[M/P]*)", Pattern.CASE_INSENSITIVE);
+
+ /** A regex to find patterns like [$$-1009] and [$?-452]. */
+ private static final Pattern specialPatternGroup = Pattern.compile("(\\[\\$[^-\\]]*-[0-9A-Z]+\\])");
+
+ /** <em>General</em> format for whole numbers. */
+ private static final Format generalWholeNumFormat = new DecimalFormat("#");
+
+ /** <em>General</em> format for decimal numbers. */
+ private static final Format generalDecimalNumFormat = new DecimalFormat("#.##########");
+
+ /** A default format to use when a number pattern cannot be parsed. */
+ private Format defaultNumFormat;
+
+ /**
+ * A map to cache formats.
+ * Map<String,Format> formats
+ */
+ private final Map formats;
+
+ /**
+ * Constructor
+ */
+ public DataFormatter() {
+ formats = new HashMap();
+
+ // init built-in formats
+
+ Format zipFormat = ZipPlusFourFormat.instance;
+ addFormat("00000\\-0000", zipFormat);
+ addFormat("00000-0000", zipFormat);
+
+ Format phoneFormat = PhoneFormat.instance;
+ // allow for format string variations
+ addFormat("[<=9999999]###\\-####;\\(###\\)\\ ###\\-####", phoneFormat);
+ addFormat("[<=9999999]###-####;(###) ###-####", phoneFormat);
+ addFormat("###\\-####;\\(###\\)\\ ###\\-####", phoneFormat);
+ addFormat("###-####;(###) ###-####", phoneFormat);
+
+ Format ssnFormat = SSNFormat.instance;
+ addFormat("000\\-00\\-0000", ssnFormat);
+ addFormat("000-00-0000", ssnFormat);
+ }
+
+ /**
+ * Return a Format for the given cell if one exists, otherwise try to
+ * create one. This method will return <code>null</code> if the any of the
+ * following is true:
+ * <ul>
+ * <li>the cell's style is null</li>
+ * <li>the style's data format string is null or empty</li>
+ * <li>the format string cannot be recognized as either a number or date</li>
+ * </ul>
+ *
+ * @param cell The cell to retrieve a Format for
+ * @return A Format for the format String
+ */
+ private Format getFormat(Cell cell) {
+ if ( cell.getCellStyle() == null) {
+ return null;
+ }
+
+ int formatIndex = cell.getCellStyle().getDataFormat();
+ String formatStr = cell.getCellStyle().getDataFormatString();
+ if(formatStr == null || formatStr.trim().length() == 0) {
+ return null;
+ }
+ return getFormat(cell.getNumericCellValue(), formatIndex, formatStr);
+ }
+
+ private Format getFormat(double cellValue, int formatIndex, String formatStr) {
+ Format format = (Format)formats.get(formatStr);
+ if (format != null) {
+ return format;
+ }
+ if (formatStr.equals("General")) {
+ if (DataFormatter.isWholeNumber(cellValue)) {
+ return generalWholeNumFormat;
+ }
+ return generalDecimalNumFormat;
+ }
+ format = createFormat(cellValue, formatIndex, formatStr);
+ formats.put(formatStr, format);
+ return format;
+ }
+
+ /**
+ * Create and return a Format based on the format string from a cell's
+ * style. If the pattern cannot be parsed, return a default pattern.
+ *
+ * @param cell The Excel cell
+ * @return A Format representing the excel format. May return null.
+ */
+ public Format createFormat(Cell cell) {
+
+ int formatIndex = cell.getCellStyle().getDataFormat();
+ String formatStr = cell.getCellStyle().getDataFormatString();
+ return createFormat(cell.getNumericCellValue(), formatIndex, formatStr);
+ }
+
+ private Format createFormat(double cellValue, int formatIndex, String sFormat) {
+ // remove color formatting if present
+ String formatStr = sFormat.replaceAll("\\[[a-zA-Z]*\\]", "");
+
+ // try to extract special characters like currency
+ Matcher m = specialPatternGroup.matcher(formatStr);
+ while(m.find()) {
+ String match = m.group();
+ String symbol = match.substring(match.indexOf('$') + 1, match.indexOf('-'));
+ if (symbol.indexOf('$') > -1) {
+ StringBuffer sb = new StringBuffer();
+ sb.append(symbol.substring(0, symbol.indexOf('$')));
+ sb.append('\\');
+ sb.append(symbol.substring(symbol.indexOf('$'), symbol.length()));
+ symbol = sb.toString();
+ }
+ formatStr = m.replaceAll(symbol);
+ m = specialPatternGroup.matcher(formatStr);
+ }
+
+ if(formatStr == null || formatStr.trim().length() == 0) {
+ return getDefaultFormat(cellValue);
+ }
+
+
+ if(DateUtil.isADateFormat(formatIndex,formatStr) &&
+ DateUtil.isValidExcelDate(cellValue)) {
+ return createDateFormat(formatStr, cellValue);
+ }
+ if (numPattern.matcher(formatStr).find()) {
+ return createNumberFormat(formatStr, cellValue);
+ }
+ // TODO - when does this occur?
+ return null;
+ }
+
+ private Format createDateFormat(String pFormatStr, double cellValue) {
+ String formatStr = pFormatStr;
+ formatStr = formatStr.replaceAll("\\\\-","-");
+ formatStr = formatStr.replaceAll("\\\\,",",");
+ formatStr = formatStr.replaceAll("\\\\ "," ");
+ formatStr = formatStr.replaceAll(";@", "");
+ boolean hasAmPm = false;
+ Matcher amPmMatcher = amPmPattern.matcher(formatStr);
+ while (amPmMatcher.find()) {
+ formatStr = amPmMatcher.replaceAll("@");
+ hasAmPm = true;
+ amPmMatcher = amPmPattern.matcher(formatStr);
+ }
+ formatStr = formatStr.replaceAll("@", "a");
+
+
+ Matcher dateMatcher = daysAsText.matcher(formatStr);
+ if (dateMatcher.find()) {
+ String match = dateMatcher.group(0);
+ formatStr = dateMatcher.replaceAll(match.toUpperCase().replaceAll("D", "E"));
+ }
+
+ // Convert excel date format to SimpleDateFormat.
+ // Excel uses lower case 'm' for both minutes and months.
+ // From Excel help:
+ /*
+ The "m" or "mm" code must appear immediately after the "h" or"hh"
+ code or immediately before the "ss" code; otherwise, Microsoft
+ Excel displays the month instead of minutes."
+ */
+
+ StringBuffer sb = new StringBuffer();
+ char[] chars = formatStr.toCharArray();
+ boolean mIsMonth = true;
+ List ms = new ArrayList();
+ for(int j=0; j<chars.length; j++) {
+ char c = chars[j];
+ if (c == 'h' || c == 'H') {
+ mIsMonth = false;
+ if (hasAmPm) {
+ sb.append('h');
+ } else {
+ sb.append('H');
+ }
+ }
+ else if (c == 'm') {
+ if(mIsMonth) {
+ sb.append('M');
+ ms.add(
+ new Integer(sb.length() -1)
+ );
+ } else {
+ sb.append('m');
+ }
+ }
+ else if (c == 's' || c == 'S') {
+ sb.append('s');
+ // if 'M' precedes 's' it should be minutes ('m')
+ for (int i = 0; i < ms.size(); i++) {
+ int index = ((Integer)ms.get(i)).intValue();
+ if (sb.charAt(index) == 'M') {
+ sb.replace(index, index+1, "m");
+ }
+ }
+ mIsMonth = true;
+ ms.clear();
+ }
+ else if (Character.isLetter(c)) {
+ mIsMonth = true;
+ ms.clear();
+ if (c == 'y' || c == 'Y') {
+ sb.append('y');
+ }
+ else if (c == 'd' || c == 'D') {
+ sb.append('d');
+ }
+ else {
+ sb.append(c);
+ }
+ }
+ else {
+ sb.append(c);
+ }
+ }
+ formatStr = sb.toString();
+
+ try {
+ return new SimpleDateFormat(formatStr);
+ } catch(IllegalArgumentException iae) {
+
+ // the pattern could not be parsed correctly,
+ // so fall back to the default number format
+ return getDefaultFormat(cellValue);
+ }
+
+ }
+
+ private Format createNumberFormat(String formatStr, double cellValue) {
+ StringBuffer sb = new StringBuffer(formatStr);
+ for (int i = 0; i < sb.length(); i++) {
+ char c = sb.charAt(i);
+ //handle (#,##0_);
+ if (c == '(') {
+ int idx = sb.indexOf(")", i);
+ if (idx > -1 && sb.charAt(idx -1) == '_') {
+ sb.deleteCharAt(idx);
+ sb.deleteCharAt(idx - 1);
+ sb.deleteCharAt(i);
+ i--;
+ }
+ } else if (c == ')' && i > 0 && sb.charAt(i - 1) == '_') {
+ sb.deleteCharAt(i);
+ sb.deleteCharAt(i - 1);
+ i--;
+ // remove quotes and back slashes
+ } else if (c == '\\' || c == '"') {
+ sb.deleteCharAt(i);
+ i--;
+
+ // for scientific/engineering notation
+ } else if (c == '+' && i > 0 && sb.charAt(i - 1) == 'E') {
+ sb.deleteCharAt(i);
+ i--;
+ }
+ }
+
+ try {
+ return new DecimalFormat(sb.toString());
+ } catch(IllegalArgumentException iae) {
+
+ // the pattern could not be parsed correctly,
+ // so fall back to the default number format
+ return getDefaultFormat(cellValue);
+ }
+ }
+
+ /**
+ * Return true if the double value represents a whole number
+ * @param d the double value to check
+ * @return <code>true</code> if d is a whole number
+ */
+ private static boolean isWholeNumber(double d) {
+ return d == Math.floor(d);
+ }
+
+ /**
+ * Returns a default format for a cell.
+ * @param cell The cell
+ * @return a default format
+ */
+ public Format getDefaultFormat(Cell cell) {
+ return getDefaultFormat(cell.getNumericCellValue());
+ }
+ private Format getDefaultFormat(double cellValue) {
+ // for numeric cells try user supplied default
+ if (defaultNumFormat != null) {
+ return defaultNumFormat;
+
+ // otherwise use general format
+ }
+ if (isWholeNumber(cellValue)){
+ return generalWholeNumFormat;
+ }
+ return generalDecimalNumFormat;
+ }
+
+ /**
+ * Returns the formatted value of an Excel date as a <tt>String</tt> based
+ * on the cell's <code>DataFormat</code>. i.e. "Thursday, January 02, 2003"
+ * , "01/02/2003" , "02-Jan" , etc.
+ *
+ * @param cell The cell
+ * @return a formatted date string
+ */
+ private String getFormattedDateString(Cell cell) {
+ Format dateFormat = getFormat(cell);
+ Date d = cell.getDateCellValue();
+ if (dateFormat != null) {
+ return dateFormat.format(d);
+ }
+ return d.toString();
+ }
+
+ /**
+ * Returns the formatted value of an Excel number as a <tt>String</tt>
+ * based on the cell's <code>DataFormat</code>. Supported formats include
+ * currency, percents, decimals, phone number, SSN, etc.:
+ * "61.54%", "$100.00", "(800) 555-1234".
+ *
+ * @param cell The cell
+ * @return a formatted number string
+ */
+ private String getFormattedNumberString(Cell cell) {
+
+ Format numberFormat = getFormat(cell);
+ double d = cell.getNumericCellValue();
+ if (numberFormat == null) {
+ return String.valueOf(d);
+ }
+ return numberFormat.format(new Double(d));
+ }
+
+ /**
+ * Formats the given raw cell value, based on the supplied
+ * format index and string, according to excel style rules.
+ * @see #formatCellValue(Cell)
+ */
+ public String formatRawCellContents(double value, int formatIndex, String formatString) {
+ // Is it a date?
+ if(DateUtil.isADateFormat(formatIndex,formatString) &&
+ DateUtil.isValidExcelDate(value)) {
+
+ Format dateFormat = getFormat(value, formatIndex, formatString);
+ Date d = DateUtil.getJavaDate(value);
+ if (dateFormat == null) {
+ return d.toString();
+ }
+ return dateFormat.format(d);
+ }
+ // else Number
+ Format numberFormat = getFormat(value, formatIndex, formatString);
+ if (numberFormat == null) {
+ return String.valueOf(value);
+ }
+ return numberFormat.format(new Double(value));
+ }
+
+ /**
+ * <p>
+ * Returns the formatted value of a cell as a <tt>String</tt> regardless
+ * of the cell type. If the Excel format pattern cannot be parsed then the
+ * cell value will be formatted using a default format.
+ * </p>
+ * <p>When passed a null or blank cell, this method will return an empty
+ * String (""). Formulas in formula type cells will not be evaluated.
+ * </p>
+ *
+ * @param cell The cell
+ * @return the formatted cell value as a String
+ */
+ public String formatCellValue(Cell cell) {
+ return formatCellValue(cell, null);
+ }
+
+ /**
+ * <p>
+ * Returns the formatted value of a cell as a <tt>String</tt> regardless
+ * of the cell type. If the Excel format pattern cannot be parsed then the
+ * cell value will be formatted using a default format.
+ * </p>
+ * <p>When passed a null or blank cell, this method will return an empty
+ * String (""). Formula cells will be evaluated using the given
+ * {@link FormulaEvaluator} if the evaluator is non-null. If the
+ * evaluator is null, then the formula String will be returned. The caller
+ * is responsible for setting the currentRow on the evaluator
+ *</p>
+ *
+ * @param cell The cell (can be null)
+ * @param evaluator The FormulaEvaluator (can be null)
+ * @return a string value of the cell
+ */
+ public String formatCellValue(Cell cell,
+ FormulaEvaluator evaluator) throws IllegalArgumentException {
+
+ if (cell == null) {
+ return "";
+ }
+
+ int cellType = cell.getCellType();
+ if (evaluator != null && cellType == Cell.CELL_TYPE_FORMULA) {
+ try {
+ cellType = evaluator.evaluateFormulaCell(cell);
+ } catch (RuntimeException e) {
+ throw new RuntimeException("Did you forget to set the current" +
+ " row on the FormulaEvaluator?", e);
+ }
+ }
+ switch (cellType)
+ {
+ case Cell.CELL_TYPE_FORMULA :
+ // should only occur if evaluator is null
+ return cell.getCellFormula();
+
+ case Cell.CELL_TYPE_NUMERIC :
+
+ if (DateUtil.isCellDateFormatted(cell)) {
+ return getFormattedDateString(cell);
+ }
+ return getFormattedNumberString(cell);
+
+ case Cell.CELL_TYPE_STRING :
+ return cell.getRichStringCellValue().getString();
+
+ case Cell.CELL_TYPE_BOOLEAN :
+ return String.valueOf(cell.getBooleanCellValue());
+ case Cell.CELL_TYPE_BLANK :
+ return "";
+ }
+ throw new RuntimeException("Unexpected celltype (" + cellType + ")");
+ }
+
+
+ /**
+ * <p>
+ * Sets a default number format to be used when the Excel format cannot be
+ * parsed successfully. <b>Note:</b> This is a fall back for when an error
+ * occurs while parsing an Excel number format pattern. This will not
+ * affect cells with the <em>General</em> format.
+ * </p>
+ * <p>
+ * The value that will be passed to the Format's format method (specified
+ * by <code>java.text.Format#format</code>) will be a double value from a
+ * numeric cell. Therefore the code in the format method should expect a
+ * <code>Number</code> value.
+ * </p>
+ *
+ * @param format A Format instance to be used as a default
+ * @see java.text.Format#format
+ */
+ public void setDefaultNumberFormat(Format format) {
+ Iterator itr = formats.entrySet().iterator();
+ while(itr.hasNext()) {
+ Map.Entry entry = (Map.Entry)itr.next();
+ if (entry.getValue() == generalDecimalNumFormat
+ || entry.getValue() == generalWholeNumFormat) {
+ entry.setValue(format);
+ }
+ }
+ defaultNumFormat = format;
+ }
+
+ /**
+ * Adds a new format to the available formats.
+ * <p>
+ * The value that will be passed to the Format's format method (specified
+ * by <code>java.text.Format#format</code>) will be a double value from a
+ * numeric cell. Therefore the code in the format method should expect a
+ * <code>Number</code> value.
+ * </p>
+ * @param excelFormatStr The data format string
+ * @param format A Format instance
+ */
+ public void addFormat(String excelFormatStr, Format format) {
+ formats.put(excelFormatStr, format);
+ }
+
+ // Some custom formats
+
+ /**
+ * @return a <tt>DecimalFormat</tt> with parseIntegerOnly set <code>true</code>
+ */
+ /* package */ static DecimalFormat createIntegerOnlyFormat(String fmt) {
+ DecimalFormat result = new DecimalFormat(fmt);
+ result.setParseIntegerOnly(true);
+ return result;
+ }
+ /**
+ * Format class for Excel's SSN format. This class mimics Excel's built-in
+ * SSN formatting.
+ *
+ * @author James May
+ */
+ private static final class SSNFormat extends Format {
+ public static final Format instance = new SSNFormat();
+ private static final DecimalFormat df = createIntegerOnlyFormat("000000000");
+ private SSNFormat() {
+ // enforce singleton
+ }
+
+ /** Format a number as an SSN */
+ public static String format(Number num) {
+ String result = df.format(num);
+ StringBuffer sb = new StringBuffer();
+ sb.append(result.substring(0, 3)).append('-');
+ sb.append(result.substring(3, 5)).append('-');
+ sb.append(result.substring(5, 9));
+ return sb.toString();
+ }
+
+ public StringBuffer format(Object obj, StringBuffer toAppendTo, FieldPosition pos) {
+ return toAppendTo.append(format((Number)obj));
+ }
+
+ public Object parseObject(String source, ParsePosition pos) {
+ return df.parseObject(source, pos);
+ }
+ }
+
+ /**
+ * Format class for Excel Zip + 4 format. This class mimics Excel's
+ * built-in formatting for Zip + 4.
+ * @author James May
+ */
+ private static final class ZipPlusFourFormat extends Format {
+ public static final Format instance = new ZipPlusFourFormat();
+ private static final DecimalFormat df = createIntegerOnlyFormat("000000000");
+ private ZipPlusFourFormat() {
+ // enforce singleton
+ }
+
+ /** Format a number as Zip + 4 */
+ public static String format(Number num) {
+ String result = df.format(num);
+ StringBuffer sb = new StringBuffer();
+ sb.append(result.substring(0, 5)).append('-');
+ sb.append(result.substring(5, 9));
+ return sb.toString();
+ }
+
+ public StringBuffer format(Object obj, StringBuffer toAppendTo, FieldPosition pos) {
+ return toAppendTo.append(format((Number)obj));
+ }
+
+ public Object parseObject(String source, ParsePosition pos) {
+ return df.parseObject(source, pos);
+ }
+ }
+
+ /**
+ * Format class for Excel phone number format. This class mimics Excel's
+ * built-in phone number formatting.
+ * @author James May
+ */
+ private static final class PhoneFormat extends Format {
+ public static final Format instance = new PhoneFormat();
+ private static final DecimalFormat df = createIntegerOnlyFormat("##########");
+ private PhoneFormat() {
+ // enforce singleton
+ }
+
+ /** Format a number as a phone number */
+ public static String format(Number num) {
+ String result = df.format(num);
+ StringBuffer sb = new StringBuffer();
+ String seg1, seg2, seg3;
+ int len = result.length();
+ if (len <= 4) {
+ return result;
+ }
+
+ seg3 = result.substring(len - 4, len);
+ seg2 = result.substring(Math.max(0, len - 7), len - 4);
+ seg1 = result.substring(Math.max(0, len - 10), Math.max(0, len - 7));
+
+ if(seg1 != null && seg1.trim().length() > 0) {
+ sb.append('(').append(seg1).append(") ");
+ }
+ if(seg2 != null && seg2.trim().length() > 0) {
+ sb.append(seg2).append('-');
+ }
+ sb.append(seg3);
+ return sb.toString();
+ }
+
+ public StringBuffer format(Object obj, StringBuffer toAppendTo, FieldPosition pos) {
+ return toAppendTo.append(format((Number)obj));
+ }
+
+ public Object parseObject(String source, ParsePosition pos) {
+ return df.parseObject(source, pos);
+ }
+ }
+}
Propchange: poi/branches/ooxml/src/java/org/apache/poi/ss/usermodel/DataFormatter.java
------------------------------------------------------------------------------
svn:executable = *
Modified: poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/CellStyle.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/CellStyle.java?rev=708982&r1=708981&r2=708982&view=diff
==============================================================================
--- poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/CellStyle.java (original)
+++ poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/CellStyle.java Wed Oct 29 12:12:47 2008
@@ -324,7 +324,10 @@
short getAlignment();
/**
- * set whether the text should be wrapped
+ * Set whether the text should be wrapped.
+ * Setting this flag to <code>true</code> make all content visible
+ * whithin a cell by displaying it on multiple lines
+ *
* @param wrapped wrap text or not
*/
Modified: poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Sheet.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Sheet.java?rev=708982&r1=708981&r2=708982&view=diff
==============================================================================
--- poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Sheet.java (original)
+++ poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Sheet.java Wed Oct 29 12:12:47 2008
@@ -167,21 +167,6 @@
void setDefaultRowHeightInPoints(float height);
/**
- * get whether gridlines are printed.
- * @return true if printed
- */
-
- boolean isGridsPrinted();
-
- /**
- * set whether gridlines printed.
- * @param value false if not printed.
- */
-
- void setGridsPrinted(boolean value);
-
-
- /**
* adds a merged region of cells (hence those cells form one)
* @param region (rowfrom/colfrom-rowto/colto) to merge
* @return index of this region
@@ -229,20 +214,6 @@
Iterator<Row> rowIterator();
/**
- * whether alternate expression evaluation is on
- * @param b alternative expression evaluation or not
- */
-
- void setAlternativeExpression(boolean b);
-
- /**
- * whether alternative formula entry is on
- * @param b alternative formulas or not
- */
-
- void setAlternativeFormula(boolean b);
-
- /**
* show automatic page breaks or not
* @param b whether to show auto page breaks
*/
Modified: poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java?rev=708982&r1=708981&r2=708982&view=diff
==============================================================================
--- poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java (original)
+++ poi/branches/ooxml/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java Wed Oct 29 12:12:47 2008
@@ -358,4 +358,44 @@
* classes.
*/
CreationHelper getCreationHelper();
+
+ /**
+ * Check whether a sheet is hidden.
+ * Note that a sheet could instead be
+ * set to be very hidden, which is different
+ * ({@link #isSheetVeryHidden(int)})
+ * @param sheetIx Number
+ * @return True if sheet is hidden
+ */
+ public boolean isSheetHidden(int sheetIx) ;
+
+ /**
+ * Check whether a sheet is very hidden.
+ * This is different from the normal
+ * hidden status
+ * ({@link #isSheetHidden(int)})
+ * @param sheetIx Number
+ * @return True if sheet is very hidden
+ */
+ public boolean isSheetVeryHidden(int sheetIx);
+
+ /**
+ * Hide or unhide a sheet
+ *
+ * @param sheetIx The sheet index
+ * @param hidden True to mark the sheet as hidden, false otherwise
+ */
+ public void setSheetHidden(int sheetIx, boolean hidden);
+
+ /**
+ * Hide or unhide a sheet.
+ * 0 = not hidden
+ * 1 = hidden
+ * 2 = very hidden.
+ *
+ * @param sheetIx The sheet number
+ * @param hidden 0 for not hidden, 1 for hidden, 2 for very hidden
+ */
+ public void setSheetHidden(int sheetIx, int hidden);
+
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org