From ea3f60410340fbcf64ed75cf855475a119a203b1 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Wed, 29 Oct 2008 19:12:47 +0000 Subject: [PATCH] more cleanup and refactoring of ooxml code,added more unit test and 3 rich examples: LoanCalculator, CalendarDemo and TimesheetDemo, numerous odds and ends improvements git-svn-id: https://svn.apache.org/repos/asf/poi/branches/ooxml@708982 13f79535-47bb-0310-9956-ffa450edef68 --- .../usermodel/examples/AligningCells.java | 74 +- .../xssf/usermodel/examples/CalendarDemo.java | 224 ++++++ .../xssf/usermodel/examples/CreateCell.java | 2 +- .../examples/CreateNewSpreadsheet.java | 61 -- .../usermodel/examples/FillsAndColors.java | 50 +- .../xssf/usermodel/examples/IterateCells.java | 2 +- .../usermodel/examples/LoanCalculator.java | 312 ++++++++ .../xssf/usermodel/examples/MergingCells.java | 2 +- ...CellNewlines.java => NewLinesInCells.java} | 2 +- .../usermodel/examples/TimesheetDemo.java | 209 ++++++ .../examples/WorkingWithBorders.java | 4 +- .../examples/WorkingWithPageSetup.java | 2 +- .../examples/WorkingWithRichText.java | 45 +- .../poi/ss/usermodel/DataFormatter.java | 673 ++++++++++++++++++ .../apache/poi/ss/usermodel/CellStyle.java | 5 +- .../org/apache/poi/ss/usermodel/Sheet.java | 29 - .../org/apache/poi/ss/usermodel/Workbook.java | 40 ++ .../apache/poi/xssf/model/StylesTable.java | 131 ++-- .../poi/xssf/usermodel/XSSFActiveXData.java | 3 +- .../apache/poi/xssf/usermodel/XSSFCell.java | 9 +- .../poi/xssf/usermodel/XSSFCellStyle.java | 59 +- .../usermodel/{extensions => }/XSSFColor.java | 4 +- .../apache/poi/xssf/usermodel/XSSFFont.java | 29 +- .../apache/poi/xssf/usermodel/XSSFName.java | 11 +- .../poi/xssf/usermodel/XSSFPrintSetup.java | 3 +- .../apache/poi/xssf/usermodel/XSSFSheet.java | 312 ++++---- .../poi/xssf/usermodel/XSSFWorkbook.java | 135 ++-- .../usermodel/extensions/XSSFCellBorder.java | 1 + .../usermodel/extensions/XSSFCellFill.java | 1 + .../xssf/eventusermodel/TestXSSFReader.java | 2 +- .../poi/xssf/model/TestStylesTable.java | 20 +- .../poi/xssf/usermodel/TestSheetHiding.java | 99 +++ .../poi/xssf/usermodel/TestXSSFCellStyle.java | 57 +- .../poi/xssf/usermodel/TestXSSFFont.java | 12 +- .../poi/xssf/usermodel/TestXSSFName.java | 8 - .../poi/xssf/usermodel/TestXSSFSheet.java | 43 +- .../poi/xssf/usermodel/TestXSSFWorkbook.java | 18 +- .../poi/hssf/data/TwoSheetsNoneHidden.xlsx | Bin 0 -> 7938 bytes .../poi/hssf/data/TwoSheetsOneHidden.xlsx | Bin 0 -> 7962 bytes 39 files changed, 2103 insertions(+), 590 deletions(-) create mode 100755 src/examples/src/org/apache/poi/xssf/usermodel/examples/CalendarDemo.java delete mode 100644 src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateNewSpreadsheet.java create mode 100755 src/examples/src/org/apache/poi/xssf/usermodel/examples/LoanCalculator.java rename src/examples/src/org/apache/poi/xssf/usermodel/examples/{CellNewlines.java => NewLinesInCells.java} (98%) create mode 100755 src/examples/src/org/apache/poi/xssf/usermodel/examples/TimesheetDemo.java create mode 100755 src/java/org/apache/poi/ss/usermodel/DataFormatter.java rename src/ooxml/java/org/apache/poi/xssf/usermodel/{extensions => }/XSSFColor.java (98%) create mode 100755 src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetHiding.java create mode 100755 src/testcases/org/apache/poi/hssf/data/TwoSheetsNoneHidden.xlsx create mode 100755 src/testcases/org/apache/poi/hssf/data/TwoSheetsOneHidden.xlsx diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/AligningCells.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/AligningCells.java index 3237f5a14d..a896a6bcce 100755 --- a/src/examples/src/org/apache/poi/xssf/usermodel/examples/AligningCells.java +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/AligningCells.java @@ -23,49 +23,51 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.STHorizontalAlignment 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(); + public static void main(String[] args) throws Exception { + Workbook wb = new XSSFWorkbook(); - Sheet sheet = wb.createSheet("new sheet"); - Row row = sheet.createRow((short) 2); + 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); + } - 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); + 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(); + // 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); - } + /** + * 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); + } } diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/CalendarDemo.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/CalendarDemo.java new file mode 100755 index 0000000000..298b63f533 --- /dev/null +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/CalendarDemo.java @@ -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. + * 
+ * Usage:
+ * CalendarDemo 
+ *
+ * + * @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 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 createStyles(XSSFWorkbook wb){ + Map styles = new HashMap(); + + 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; + } +} diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateCell.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateCell.java index 8cf56c675d..1a79b6ae0b 100755 --- a/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateCell.java +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateCell.java @@ -25,7 +25,7 @@ import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** - * + * Illustrates how to create cell and set values of different types. */ public class CreateCell { diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateNewSpreadsheet.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateNewSpreadsheet.java deleted file mode 100644 index 70f892c21b..0000000000 --- a/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateNewSpreadsheet.java +++ /dev/null @@ -1,61 +0,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.xssf.usermodel.examples; - -import java.io.FileOutputStream; - -import org.apache.poi.hssf.usermodel.HSSFClientAnchor; -import org.apache.poi.hssf.usermodel.HSSFPatriarch; -import org.apache.poi.ss.usermodel.*; -import org.apache.poi.xssf.usermodel.*; - -public class CreateNewSpreadsheet { - public static void main(String[] args) throws Exception { - XSSFWorkbook wb = new XSSFWorkbook(); - CreationHelper createHelper = wb.getCreationHelper(); - - XSSFSheet s1 = wb.createSheet("Sheet One"); - XSSFSheet s2 = wb.createSheet("Sheet Two"); - - // Create a few cells - s1.createRow(0); - s1.createRow(1); - s1.createRow(2); - s1.createRow(3); - s2.createRow(2); - - s1.getRow(0).createCell(0).setCellValue(1.2); - s1.getRow(0).createCell(1).setCellValue(createHelper.createRichTextString("Sheet 1 text")); - s1.getRow(1).createCell(0).setCellValue(4.22); - s1.getRow(2).createCell(0).setCellValue(5.44); - s1.getRow(3).createCell(0).setCellFormula("SUM(A1:A3)"); - - s2.getRow(2).createCell(1).setCellValue(createHelper.createRichTextString("Sheet 2")); - - s1.groupRow(0, 3); - - s1.getRow(1).setHeightInPoints(10.4f); - //s1.setActiveCell("A2"); - //s2.setSelected(true); - - // Save - FileOutputStream fout = new FileOutputStream("NewFile.xlsx"); - wb.write(fout); - fout.close(); - System.out.println("Done"); - } -} diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/FillsAndColors.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/FillsAndColors.java index eb4ce69c94..5c5b623b8e 100755 --- a/src/examples/src/org/apache/poi/xssf/usermodel/examples/FillsAndColors.java +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/FillsAndColors.java @@ -29,31 +29,31 @@ import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class FillsAndColors { public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); - 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); - - // 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); - - // Write the output to a file - FileOutputStream fileOut = new FileOutputStream("fill_colors.xlsx"); - wb.write(fileOut); - fileOut.close(); + 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); + + // 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); + + // Write the output to a file + FileOutputStream fileOut = new FileOutputStream("fill_colors.xlsx"); + wb.write(fileOut); + fileOut.close(); } } diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/IterateCells.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/IterateCells.java index f644a58f51..fc41e27042 100755 --- a/src/examples/src/org/apache/poi/xssf/usermodel/examples/IterateCells.java +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/IterateCells.java @@ -28,7 +28,7 @@ import org.apache.poi.ss.usermodel.Row; 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)); diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/LoanCalculator.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/LoanCalculator.java new file mode 100755 index 0000000000..7d3f9d80eb --- /dev/null +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/LoanCalculator.java @@ -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 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 createStyles(XSSFWorkbook wb){ + Map styles = new HashMap(); + + 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)"); + + + } +} diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/MergingCells.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/MergingCells.java index 3113497d0b..853fef1765 100755 --- a/src/examples/src/org/apache/poi/xssf/usermodel/examples/MergingCells.java +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/MergingCells.java @@ -28,7 +28,7 @@ import org.apache.poi.hssf.util.Region; 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 { diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/CellNewlines.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/NewLinesInCells.java similarity index 98% rename from src/examples/src/org/apache/poi/xssf/usermodel/examples/CellNewlines.java rename to src/examples/src/org/apache/poi/xssf/usermodel/examples/NewLinesInCells.java index b9b96e3a83..07045df568 100755 --- a/src/examples/src/org/apache/poi/xssf/usermodel/examples/CellNewlines.java +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/NewLinesInCells.java @@ -28,7 +28,7 @@ import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * How to use newlines in cells */ -public class CellNewlines { +public class NewLinesInCells { public static void main(String[]args) throws Exception { Workbook wb = new XSSFWorkbook(); diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/TimesheetDemo.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/TimesheetDemo.java new file mode 100755 index 0000000000..45580fffc6 --- /dev/null +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/TimesheetDemo.java @@ -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 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 createStyles(XSSFWorkbook wb){ + Map styles = new HashMap(); + 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; + } +} diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithBorders.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithBorders.java index ee8e172d5e..825febde75 100755 --- a/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithBorders.java +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithBorders.java @@ -28,7 +28,7 @@ import java.io.FileOutputStream; 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 @@ public class WorkingWithBorders { 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(); diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithPageSetup.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithPageSetup.java index 2e589484d9..137379e574 100755 --- a/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithPageSetup.java +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithPageSetup.java @@ -71,7 +71,7 @@ public class WorkingWithPageSetup { 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(); } diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithRichText.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithRichText.java index 34cb556c30..6070caa135 100755 --- a/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithRichText.java +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/WorkingWithRichText.java @@ -22,36 +22,35 @@ import org.apache.poi.ss.usermodel.*; 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(); + public static void main(String[] args) throws Exception { + + XSSFWorkbook wb = new XSSFWorkbook(); - XSSFSheet sheet = wb.createSheet(); - XSSFRow row = sheet.createRow((short) 2); + XSSFSheet sheet = wb.createSheet(); + XSSFRow row = sheet.createRow((short) 2); - XSSFCell cell = row.createCell(1); - XSSFRichTextString rt = new XSSFRichTextString("The quick"); + XSSFCell cell = row.createCell(1); + XSSFRichTextString rt = new XSSFRichTextString("The quick"); - XSSFFont font1 = wb.createFont(); - font1.setBold(true); - rt.append(" brown fox", font1); + 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); + 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(); + // Write the output to a file + FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx"); + wb.write(fileOut); + fileOut.close(); - } + } - } +} diff --git a/src/java/org/apache/poi/ss/usermodel/DataFormatter.java b/src/java/org/apache/poi/ss/usermodel/DataFormatter.java new file mode 100755 index 0000000000..a8f01ca720 --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/DataFormatter.java @@ -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. + *

+ * 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 before or after the number pattern to form a + * prefix or suffix. + *

+ *

+ * For example the Excel pattern "$#,##0.00 "USD"_);($#,##0.00 "USD")" + * will be correctly formatted as "$1,000.00 USD" or "($1,000.00 USD)". + * However the pattern "00-00-00" 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 DataFormatter.addFormat(String,Format). The following + * custom formats are already provided by this class: + *

+ * 
+ * 
  • SSN "000-00-0000"
    • 
+ *     
  • Phone Number "(###) ###-####"
    • 
+ *     
  • Zip plus 4 "00000-0000"
    • 
+ * 

+ *
+ *

+ * 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 + * DataFormatter.setDefaultNumberFormat(Format). Note: 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]+\\])"); + + /** General format for whole numbers. */ + private static final Format generalWholeNumFormat = new DecimalFormat("#"); + + /** General 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 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 null if the any of the + * following is true: + *

    + *
  • the cell's style is null
    • + *
  • the style's data format string is null or empty
    • + *
  • the format string cannot be recognized as either a number or date
    • + *
+ * + * @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 -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 true 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 String based + * on the cell's DataFormat. 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 String + * based on the cell's DataFormat. 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)); + } + + /** + *

+ * Returns the formatted value of a cell as a String regardless + * of the cell type. If the Excel format pattern cannot be parsed then the + * cell value will be formatted using a default format. + *

+ *

When passed a null or blank cell, this method will return an empty + * String (""). Formulas in formula type cells will not be evaluated. + *

+ * + * @param cell The cell + * @return the formatted cell value as a String + */ + public String formatCellValue(Cell cell) { + return formatCellValue(cell, null); + } + + /** + *

+ * Returns the formatted value of a cell as a String regardless + * of the cell type. If the Excel format pattern cannot be parsed then the + * cell value will be formatted using a default format. + *

+ *

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 + *

+ * + * @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 + ")"); + } + + + /** + *

+ * Sets a default number format to be used when the Excel format cannot be + * parsed successfully. Note: This is a fall back for when an error + * occurs while parsing an Excel number format pattern. This will not + * affect cells with the General format. + *

+ *

+ * The value that will be passed to the Format's format method (specified + * by java.text.Format#format) will be a double value from a + * numeric cell. Therefore the code in the format method should expect a + * Number value. + *

+ * + * @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. + *

+ * The value that will be passed to the Format's format method (specified + * by java.text.Format#format) will be a double value from a + * numeric cell. Therefore the code in the format method should expect a + * Number value. + *

+ * @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 DecimalFormat with parseIntegerOnly set true + */ + /* 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); + } + } +} diff --git a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/CellStyle.java b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/CellStyle.java index 49e67fcb64..ec4fd3b377 100644 --- a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/CellStyle.java +++ b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/CellStyle.java @@ -324,7 +324,10 @@ public interface CellStyle { short getAlignment(); /** - * set whether the text should be wrapped + * Set whether the text should be wrapped. + * Setting this flag to true make all content visible + * whithin a cell by displaying it on multiple lines + * * @param wrapped wrap text or not */ diff --git a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Sheet.java b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Sheet.java index b29d1d037e..911f232111 100644 --- a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Sheet.java +++ b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Sheet.java @@ -166,21 +166,6 @@ public interface Sheet extends Iterable { 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 @@ -228,20 +213,6 @@ public interface Sheet extends Iterable { */ Iterator 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 diff --git a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java index c7bd0f8ac2..82ef894821 100644 --- a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java +++ b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java @@ -358,4 +358,44 @@ public interface Workbook { * 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); + } diff --git a/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java b/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java index bd33b80d3e..872eadb44d 100644 --- a/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java +++ b/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java @@ -20,14 +20,9 @@ package org.apache.poi.xssf.model; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; -import java.util.ArrayList; -import java.util.Enumeration; -import java.util.Hashtable; -import java.util.List; +import java.util.*; import java.util.Map.Entry; -import org.apache.poi.ss.usermodel.CellStyle; -import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.FontFamily; import org.apache.poi.ss.usermodel.FontScheme; import org.apache.poi.xssf.usermodel.XSSFCellStyle; @@ -63,7 +58,7 @@ import org.openxml4j.opc.PackageRelationship; * @author ugo */ public class StylesTable extends POIXMLDocumentPart { - private final Hashtable numberFormats = new Hashtable(); + private final Map numberFormats = new LinkedHashMap(); private final List fonts = new ArrayList(); private final List fills = new ArrayList(); private final List borders = new ArrayList(); @@ -101,7 +96,7 @@ public class StylesTable extends POIXMLDocumentPart { * @param is The input stream containing the XML document. * @throws IOException if an error occurs while reading. */ - public void readFrom(InputStream is) throws IOException { + protected void readFrom(InputStream is) throws IOException { try { doc = StyleSheetDocument.Factory.parse(is); // Grab all the different bits we care about @@ -151,11 +146,11 @@ public class StylesTable extends POIXMLDocumentPart { public String getNumberFormatAt(int idx) { return numberFormats.get(idx); } - public synchronized int putNumberFormat(String fmt) { + + public int putNumberFormat(String fmt) { if (numberFormats.containsValue(fmt)) { // Find the key, and return that - for(Enumeration keys = numberFormats.keys(); keys.hasMoreElements();) { - int key = keys.nextElement(); + for(Integer key : numberFormats.keySet() ) { if(numberFormats.get(key).equals(fmt)) { return key; } @@ -173,15 +168,15 @@ public class StylesTable extends POIXMLDocumentPart { } public XSSFFont getFontAt(int idx) { - return fonts.get((int)idx); + return fonts.get(idx); } - public int putFont(Font font) { + public int putFont(XSSFFont font) { int idx = fonts.indexOf(font); if (idx != -1) { return idx; } - fonts.add((XSSFFont)font); + fonts.add(font); return fonts.size() - 1; } @@ -189,15 +184,14 @@ public class StylesTable extends POIXMLDocumentPart { int styleXfId = 0; // 0 is the empty default - if(xfs.get((int) idx).getXfId() > 0) { - styleXfId = (int) xfs.get((int) idx).getXfId(); + if(xfs.get(idx).getXfId() > 0) { + styleXfId = (int) xfs.get(idx).getXfId(); } - return new XSSFCellStyle((int) idx, styleXfId, this); + return new XSSFCellStyle(idx, styleXfId, this); } - public synchronized int putStyle(CellStyle style) { - XSSFCellStyle xStyle = (XSSFCellStyle)style; - CTXf mainXF = xStyle.getCoreXf(); + public int putStyle(XSSFCellStyle style) { + CTXf mainXF = style.getCoreXf(); if(! xfs.contains(mainXF)) { xfs.add(mainXF); @@ -218,18 +212,26 @@ public class StylesTable extends POIXMLDocumentPart { return borders.size() - 1; } + public XSSFCellFill getFillAt(int idx) { + return fills.get(idx); + } + public List getBorders(){ return borders; } - public XSSFCellFill getFillAt(int idx) { - return fills.get(idx); - } - public List getFills(){ return fills; } + public List getFonts(){ + return fonts; + } + + public Map getNumberFormats(){ + return numberFormats; + } + public int putFill(XSSFCellFill fill) { int idx = fills.indexOf(fill); if (idx != -1) { @@ -240,7 +242,7 @@ public class StylesTable extends POIXMLDocumentPart { } public CTXf getCellXfAt(int idx) { - return xfs.get((int) idx); + return xfs.get(idx); } public int putCellXf(CTXf cellXf) { xfs.add(cellXf); @@ -248,7 +250,7 @@ public class StylesTable extends POIXMLDocumentPart { } public CTXf getCellStyleXfAt(int idx) { - return styleXfs.get((int) idx); + return styleXfs.get(idx); } public int putCellStyleXf(CTXf cellStyleXf) { styleXfs.add(cellStyleXf); @@ -260,36 +262,14 @@ public class StylesTable extends POIXMLDocumentPart { public int getNumCellStyles(){ return styleXfs.size(); } - /** - * get the size of fonts - */ - public int getNumberOfFonts(){ - return this.fonts.size(); - } + /** * For unit testing only */ public int _getNumberFormatSize() { return numberFormats.size(); } - /** - * For unit testing only - */ - public int _getFontsSize() { - return fonts.size(); - } - /** - * For unit testing only - */ - public int _getFillsSize() { - return fills.size(); - } - /** - * For unit testing only - */ - public int _getBordersSize() { - return borders.size(); - } + /** * For unit testing only */ @@ -305,7 +285,7 @@ public class StylesTable extends POIXMLDocumentPart { /** * For unit testing only! */ - public CTStylesheet _getRawStylesheet() { + public CTStylesheet getCTStylesheet() { return doc.getStyleSheet(); } @@ -333,7 +313,7 @@ public class StylesTable extends POIXMLDocumentPart { } doc.getStyleSheet().setNumFmts(formats); - int idx = 0; + int idx; // Fonts CTFonts ctFonts = CTFonts.Factory.newInstance(); ctFonts.setCount(fonts.size()); @@ -421,7 +401,7 @@ public class StylesTable extends POIXMLDocumentPart { xfs.add(xf); } - private CTXf createDefaultXf() { + private static CTXf createDefaultXf() { CTXf ctXf = CTXf.Factory.newInstance(); ctXf.setNumFmtId(0); ctXf.setFontId(0); @@ -429,7 +409,7 @@ public class StylesTable extends POIXMLDocumentPart { ctXf.setBorderId(0); return ctXf; } - private CTBorder createDefaultBorder() { + private static CTBorder createDefaultBorder() { CTBorder ctBorder = CTBorder.Factory.newInstance(); ctBorder.addNewBottom(); ctBorder.addNewTop(); @@ -440,14 +420,14 @@ public class StylesTable extends POIXMLDocumentPart { } - private CTFill[] createDefaultFills() { + private static CTFill[] createDefaultFills() { CTFill[] ctFill = new CTFill[]{CTFill.Factory.newInstance(),CTFill.Factory.newInstance()}; ctFill[0].addNewPatternFill().setPatternType(STPatternType.NONE); ctFill[1].addNewPatternFill().setPatternType(STPatternType.DARK_GRAY); return ctFill; } - private XSSFFont createDefaultFont() { + private static XSSFFont createDefaultFont() { CTFont ctFont = CTFont.Factory.newInstance(); XSSFFont xssfFont=new XSSFFont(ctFont, 0); xssfFont.setFontHeightInPoints(XSSFFont.DEFAULT_FONT_SIZE); @@ -458,15 +438,48 @@ public class StylesTable extends POIXMLDocumentPart { return xssfFont; } - public CTDxf getDxf(int idx) { + protected CTDxf getDxf(int idx) { if(dxfs.size()==0) return CTDxf.Factory.newInstance(); else - return dxfs.get((int) idx); + return dxfs.get(idx); } - public int putDxf(CTDxf dxf) { + protected int putDxf(CTDxf dxf) { this.dxfs.add(dxf); return this.dxfs.size(); } + + public XSSFCellStyle createCellStyle() { + CTXf xf = CTXf.Factory.newInstance(); + xf.setNumFmtId(0); + xf.setFontId(0); + xf.setFillId(0); + xf.setBorderId(0); + xf.setXfId(0); + int xfSize = styleXfs.size(); + int indexXf = putCellXf(xf); + return new XSSFCellStyle(indexXf - 1, xfSize - 1, this); + } + + /** + * Finds a font that matches the one with the supplied attributes + */ + public XSSFFont findFont(short boldWeight, short color, short fontHeight, String name, boolean italic, boolean strikeout, short typeOffset, byte underline) { + for (XSSFFont font : fonts) { + if ( (font.getBold() == (boldWeight == XSSFFont.BOLDWEIGHT_BOLD)) + && font.getColor() == color + && font.getFontHeightInPoints() == fontHeight + && font.getFontName().equals(name) + && font.getItalic() == italic + && font.getStrikeout() == strikeout + && font.getTypeOffset() == typeOffset + && font.getUnderline() == underline) + { + return font; + } + } + return null; + } + } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFActiveXData.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFActiveXData.java index 5faed67e93..300f27cb9d 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFActiveXData.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFActiveXData.java @@ -6,6 +6,7 @@ import java.io.OutputStream; import org.apache.poi.ss.usermodel.PictureData; import org.apache.poi.util.IOUtils; import org.apache.poi.xssf.model.XSSFWritableModel; +import org.apache.poi.POIXMLException; import org.openxml4j.opc.PackagePart; public class XSSFActiveXData implements PictureData, XSSFWritableModel { @@ -40,7 +41,7 @@ public class XSSFActiveXData implements PictureData, XSSFWritableModel { try { return IOUtils.toByteArray(packagePart.getInputStream()); } catch(IOException e) { - throw new RuntimeException(e); + throw new POIXMLException(e); } } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java index b7e77095ec..926b41ee50 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java @@ -27,6 +27,7 @@ import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.model.StylesTable; import org.apache.poi.xssf.model.SharedStringsTable; +import org.apache.poi.POIXMLException; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula; import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType; @@ -175,6 +176,7 @@ public final class XSSFCell implements Cell { * @return the value of the cell as a number * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is CELL_TYPE_STRING * @exception NumberFormatException if the cell value isn't a parsable double. + * @see DataFormatter for turning this number into a string similar to that which Excel would render this number as. */ public double getNumericCellValue() { int cellType = getCellType(); @@ -395,7 +397,7 @@ public final class XSSFCell implements Cell { */ public void setCellStyle(CellStyle style) { if(style == null) { - cell.unsetS(); + if(cell.isSetS()) cell.unsetS(); } else { XSSFCellStyle xStyle = (XSSFCellStyle)style; xStyle.verifyBelongsToStylesSource(stylesSource); @@ -455,6 +457,7 @@ public final class XSSFCell implements Cell { * @return the value of the cell as a date * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is CELL_TYPE_STRING * @exception NumberFormatException if the cell value isn't a parsable double. + * @see DataFormatter for formatting this date into a string similar to how excel does. */ public Date getDateCellValue() { int cellType = getCellType(); @@ -746,10 +749,10 @@ public final class XSSFCell implements Cell { */ private static void checkBounds(int cellNum) { if (cellNum > MAX_COLUMN_NUMBER) { - throw new RuntimeException("You cannot have more than "+MAX_COLUMN_NUMBER+" columns " + + throw new POIXMLException("You cannot have more than "+MAX_COLUMN_NUMBER+" columns " + "in a given row because Excel can't handle it"); } else if (cellNum < 0) { - throw new RuntimeException("You cannot reference columns with an index of less then 0."); + throw new POIXMLException("You cannot reference columns with an index of less then 0."); } } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java index fb8c2f770b..74baa5f2e6 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCellStyle.java @@ -22,7 +22,6 @@ import org.apache.poi.xssf.model.StylesTable; import org.apache.poi.xssf.usermodel.extensions.XSSFCellAlignment; import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder; import org.apache.poi.xssf.usermodel.extensions.XSSFCellFill; -import org.apache.poi.xssf.usermodel.extensions.XSSFColor; import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide; import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; @@ -36,7 +35,7 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; * @see org.apache.poi.xssf.usermodel.XSSFWorkbook#getCellStyleAt(short) * @see org.apache.poi.xssf.usermodel.XSSFCell#setCellStyle(org.apache.poi.ss.usermodel.CellStyle) */ -public class XSSFCellStyle implements CellStyle, Cloneable { +public class XSSFCellStyle implements CellStyle { private int cellXfId; private StylesTable stylesSource; @@ -76,7 +75,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * Creates an empty Cell Style */ public XSSFCellStyle(StylesTable stylesSource) { - this.stylesSource = (StylesTable)stylesSource; + this.stylesSource = stylesSource; // We need a new CTXf for the main styles // TODO decide on a style ctxf cellXf = CTXf.Factory.newInstance(); @@ -112,15 +111,11 @@ public class XSSFCellStyle implements CellStyle, Cloneable { */ public void cloneStyleFrom(CellStyle source) { if(source instanceof XSSFCellStyle) { - this.cloneStyleFrom((XSSFCellStyle)source); + this.cloneStyleFrom(source); } throw new IllegalArgumentException("Can only clone from one XSSFCellStyle to another, not between HSSFCellStyle and XSSFCellStyle"); } - public void cloneStyleFrom(XSSFCellStyle source) { - throw new IllegalStateException("TODO"); - } - /** * Get the type of horizontal alignment for the cell * @@ -312,7 +307,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * @see IndexedColors */ public short getBottomBorderColor() { - XSSFColor clr = getBottomBorderRgbColor(); + XSSFColor clr = getBottomBorderXSSFColor(); return clr == null ? IndexedColors.BLACK.getIndex() : (short)clr.getIndexed(); } @@ -321,7 +316,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * * @return the used color or null if not set */ - public XSSFColor getBottomBorderRgbColor() { + public XSSFColor getBottomBorderXSSFColor() { if(!cellXf.getApplyBorder()) return null; int idx = (int)cellXf.getBorderId(); @@ -359,7 +354,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * @see IndexedColors */ public short getFillBackgroundColor() { - XSSFColor clr = getFillBackgroundRgbColor(); + XSSFColor clr = getFillBackgroundXSSFColor(); return clr == null ? IndexedColors.AUTOMATIC.getIndex() : (short)clr.getIndexed(); } @@ -369,10 +364,10 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * Note - many cells are actually filled with a foreground * fill, not a background fill - see {@link #getFillForegroundColor()} *

- * @see org.apache.poi.xssf.usermodel.extensions.XSSFColor#getRgb() + * @see org.apache.poi.xssf.usermodel.XSSFColor#getRgb() * @return XSSFColor - fill color or null if not set */ - public XSSFColor getFillBackgroundRgbColor() { + public XSSFColor getFillBackgroundXSSFColor() { if(!cellXf.getApplyFill()) return null; int fillIndex = (int)cellXf.getFillId(); @@ -391,7 +386,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * @return fill color, default value is {@link IndexedColors.AUTOMATIC} */ public short getFillForegroundColor() { - XSSFColor clr = getFillForegroundRgbColor(); + XSSFColor clr = getFillForegroundXSSFColor(); return clr == null ? IndexedColors.AUTOMATIC.getIndex() : (short)clr.getIndexed(); } @@ -400,7 +395,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * * @return XSSFColor - fill color or null if not set */ - public XSSFColor getFillForegroundRgbColor() { + public XSSFColor getFillForegroundXSSFColor() { if(!cellXf.getApplyFill()) return null; int fillIndex = (int)cellXf.getFillId(); @@ -508,7 +503,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * @see IndexedColors */ public short getLeftBorderColor() { - XSSFColor clr = getLeftBorderRgbColor(); + XSSFColor clr = getLeftBorderXSSFColor(); return clr == null ? IndexedColors.BLACK.getIndex() : (short)clr.getIndexed(); } @@ -518,7 +513,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * @return the index of the color definition or null if not set * @see IndexedColors */ - public XSSFColor getLeftBorderRgbColor() { + public XSSFColor getLeftBorderXSSFColor() { if(!cellXf.getApplyBorder()) return null; int idx = (int)cellXf.getBorderId(); @@ -543,7 +538,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * @see IndexedColors */ public short getRightBorderColor() { - XSSFColor clr = getRightBorderRgbColor(); + XSSFColor clr = getRightBorderXSSFColor(); return clr == null ? IndexedColors.BLACK.getIndex() : (short)clr.getIndexed(); } /** @@ -551,7 +546,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * * @return the used color or null if not set */ - public XSSFColor getRightBorderRgbColor() { + public XSSFColor getRightBorderXSSFColor() { if(!cellXf.getApplyBorder()) return null; int idx = (int)cellXf.getBorderId(); @@ -586,7 +581,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * @see IndexedColors */ public short getTopBorderColor() { - XSSFColor clr = getTopBorderRgbColor(); + XSSFColor clr = getTopBorderXSSFColor(); return clr == null ? IndexedColors.BLACK.getIndex() : (short)clr.getIndexed(); } @@ -595,7 +590,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * * @return the used color or null if not set */ - public XSSFColor getTopBorderRgbColor() { + public XSSFColor getTopBorderXSSFColor() { if(!cellXf.getApplyBorder()) return null; int idx = (int)cellXf.getBorderId(); @@ -824,7 +819,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * * @param border the type of border to use */ - public void setBorderTopEnum(BorderStyle border) { + public void setBorderTop(BorderStyle border) { setBorderTop((short)border.ordinal()); } @@ -874,7 +869,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * For example: * 
      * cs.setFillPattern(XSSFCellStyle.FINE_DOTS );
-     * cs.setFillBackgroundRgbColor(new XSSFColor(java.awt.Color.RED));
+     * cs.setFillBackgroundXSSFColor(new XSSFColor(java.awt.Color.RED));
      *
* optionally a Foreground and background fill can be applied: * Note: Ensure Foreground color is set prior to background @@ -915,7 +910,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * For example: * 
      * cs.setFillPattern(XSSFCellStyle.FINE_DOTS );
-     * cs.setFillBackgroundRgbColor(IndexedColors.RED.getIndex());
+     * cs.setFillBackgroundXSSFColor(IndexedColors.RED.getIndex());
      *
* optionally a Foreground and background fill can be applied: * Note: Ensure Foreground color is set prior to background @@ -946,7 +941,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { *
* Note: Ensure Foreground color is set prior to background color. * @param color the color to use - * @see #setFillBackgroundColor(org.apache.poi.xssf.usermodel.extensions.XSSFColor) ) + * @see #setFillBackgroundColor(org.apache.poi.xssf.usermodel.XSSFColor) ) */ public void setFillForegroundColor(XSSFColor color) { CTFill ct = getCTFill(); @@ -1241,7 +1236,11 @@ public class XSSFCellStyle implements CellStyle, Cloneable { } /** - * Set whether the text should be wrapped + * Set whether the text should be wrapped. + *

+ * Setting this flag to true make all content visible + * whithin a cell by displaying it on multiple lines + *

* * @param wrapped a boolean value indicating if the text in a cell should be line-wrapped within the cell. */ @@ -1258,13 +1257,13 @@ public class XSSFCellStyle implements CellStyle, Cloneable { public XSSFColor getBorderColor(BorderSide side) { switch(side){ case BOTTOM: - return getBottomBorderRgbColor(); + return getBottomBorderXSSFColor(); case RIGHT: - return getRightBorderRgbColor(); + return getRightBorderXSSFColor(); case TOP: - return getTopBorderRgbColor(); + return getTopBorderXSSFColor(); case LEFT: - return getLeftBorderRgbColor(); + return getLeftBorderXSSFColor(); default: throw new IllegalArgumentException("Unknown border: " + side); } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFColor.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFColor.java similarity index 98% rename from src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFColor.java rename to src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFColor.java index 56a1312a4e..9bd38c019f 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFColor.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFColor.java @@ -14,7 +14,7 @@ See the License for the specific language governing permissions and limitations under the License. ==================================================================== */ -package org.apache.poi.xssf.usermodel.extensions; +package org.apache.poi.xssf.usermodel; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTColor; @@ -206,7 +206,7 @@ public class XSSFColor { } public boolean equals(Object o){ - if(!(o instanceof XSSFColor)) return false; + if(o == null || !(o instanceof XSSFColor)) return false; XSSFColor cf = (XSSFColor)o; return ctColor.toString().equals(cf.getCTColor().toString()); diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFont.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFont.java index 4424f6ec30..d24b264515 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFont.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFont.java @@ -17,8 +17,8 @@ package org.apache.poi.xssf.usermodel; import org.apache.poi.ss.usermodel.*; -import org.apache.poi.xssf.usermodel.extensions.XSSFColor; import org.apache.poi.xssf.model.StylesTable; +import org.apache.poi.POIXMLException; import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; /** @@ -127,7 +127,7 @@ public class XSSFFont implements Font { * * @return XSSFColor - rgb color to use */ - public XSSFColor getRgbColor() { + public XSSFColor getXSSFColor() { CTColor ctColor = ctFont.sizeOfColorArray() == 0 ? null : ctFont.getColorArray(0); return ctColor == null ? null : new XSSFColor(ctColor); } @@ -221,7 +221,7 @@ public class XSSFFont implements Font { case STVerticalAlignRun.INT_SUPERSCRIPT: return Font.SS_SUPER; default: - throw new RuntimeException("Wrong offset value " + val); + throw new POIXMLException("Wrong offset value " + val); } } else return Font.SS_NONE; @@ -292,7 +292,7 @@ public class XSSFFont implements Font { charsetProperty.setVal(FontCharset.DEFAULT.getValue()); break; default: - throw new RuntimeException("Attention: an attempt to set a type of unknow charset and charset"); + throw new POIXMLException("Attention: an attempt to set a type of unknow charset and charset"); } } @@ -327,9 +327,18 @@ public class XSSFFont implements Font { ctColor.setIndexed(color); } } + + /** + * set the color for the font in Standard Alpha Red Green Blue color value + * + * @param color - color to use + */ public void setColor(XSSFColor color) { if(color == null) ctFont.setColorArray(null); - else ctFont.setColorArray(new CTColor[]{color.getCTColor()}); + else { + CTColor ctColor = ctFont.sizeOfColorArray() == 0 ? ctFont.addNewColor() : ctFont.getColorArray(0); + ctColor.setRgb(color.getRgb()); + } } /** @@ -360,16 +369,6 @@ public class XSSFFont implements Font { setFontHeight(height); } - /** - * set the color for the font in Standard Alpha Red Green Blue color value - * - * @param color - color to use - */ - public void setRgbColor(XSSFColor color) { - CTColor ctColor = ctFont.sizeOfColorArray() == 0 ? ctFont.addNewColor() : ctFont.getColorArray(0); - ctColor.setRgb(color.getRgb()); - } - /** * set the theme color for the font to use * diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java index b5188e4d76..4142bf3361 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java @@ -18,6 +18,8 @@ package org.apache.poi.xssf.usermodel; import org.apache.poi.ss.usermodel.Name; import org.apache.poi.ss.util.AreaReference; +import org.apache.poi.util.POILogger; +import org.apache.poi.util.POILogFactory; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName; /** @@ -49,6 +51,7 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName; * @author Yegor Kozlov */ public class XSSFName implements Name { + private static POILogger logger = POILogFactory.getLogger(XSSFWorkbook.class); /** * A built-in defined name that specifies the workbook's print area @@ -166,8 +169,12 @@ public class XSSFName implements Name { * @throws IllegalArgumentException if the specified reference is unparsable */ public void setReference(String ref) { - String normalizedRef = AreaReference.isContiguous(ref) ? new AreaReference(ref).formatAsString() : ref; - ctName.setStringValue(normalizedRef); + try { + ref = AreaReference.isContiguous(ref) ? new AreaReference(ref).formatAsString() : ref; + } catch (IllegalArgumentException e){ + logger.log(POILogger.WARN, "failed to parse cell reference. Setting raw value"); + } + ctName.setStringValue(ref); } /** diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPrintSetup.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPrintSetup.java index 03d95e52ea..0e70db6051 100755 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPrintSetup.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPrintSetup.java @@ -18,6 +18,7 @@ package org.apache.poi.xssf.usermodel; import org.apache.poi.ss.usermodel.*; +import org.apache.poi.POIXMLException; import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; @@ -63,7 +64,7 @@ public class XSSFPrintSetup implements PrintSetup { * @param scale the scale to use */ public void setScale(short scale) { - if (scale < 10 || scale > 400) throw new RuntimeException("Scale value not accepted: you must choose a value between 10 and 400."); + if (scale < 10 || scale > 400) throw new POIXMLException("Scale value not accepted: you must choose a value between 10 and 400."); pageSetup.setScale(scale); } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java index dd983d6735..6174f6d6b9 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java @@ -231,7 +231,7 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { } /** - * Sdds a merged region of cells (hence those cells form one) + * Adds a merged region of cells (hence those cells form one). * * @param cra (rowfrom/colfrom-rowto/colto) to merge * @return index of this region @@ -324,8 +324,25 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { * @param leftmostColumn Left column visible in right pane. */ public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow) { - this.createFreezePane(colSplit, rowSplit); - this.showInPane((short)topRow, (short)leftmostColumn); + CTPane pane = getPane(); + if (colSplit > 0) pane.setXSplit(colSplit); + if (rowSplit > 0) pane.setYSplit(rowSplit); + pane.setState(STPaneState.FROZEN); + if (rowSplit == 0) { + pane.setTopLeftCell(new CellReference(0, topRow).formatAsString()); + pane.setActivePane(STPane.TOP_RIGHT); + } else if (colSplit == 0) { + pane.setTopLeftCell(new CellReference(leftmostColumn, 64).formatAsString()); + pane.setActivePane(STPane.BOTTOM_LEFT); + } else { + pane.setTopLeftCell(new CellReference(leftmostColumn, topRow).formatAsString()); + pane.setActivePane(STPane.BOTTOM_RIGHT); + } + + CTSheetView ctView = getDefaultSheetView(); + ctView.setSelectionArray(null); + CTSelection sel = ctView.addNewSelection(); + sel.setPane(pane.getActivePane()); } /** @@ -334,10 +351,7 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { * @param rowSplit Vertical position of split. */ public void createFreezePane(int colSplit, int rowSplit) { - getPane().setXSplit(colSplit); - getPane().setYSplit(rowSplit); - // make bottomRight default active pane - getPane().setActivePane(STPane.BOTTOM_RIGHT); + createFreezePane( colSplit, rowSplit, colSplit, rowSplit ); } /** @@ -419,7 +433,7 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { return breaks; } - protected CTPageBreak getSheetTypeColumnBreaks() { + private CTPageBreak getSheetTypeColumnBreaks() { if (worksheet.getColBreaks() == null) { worksheet.setColBreaks(CTPageBreak.Factory.newInstance()); } @@ -452,11 +466,11 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { * Note, this value is different from {@link #getColumnWidth(int)}. The latter is always greater and includes * 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines. *

- * @return default column width + * @return column width, default value is 8 */ public int getDefaultColumnWidth() { - CTSheetFormatPr pr = getSheetTypeSheetFormatPr(); - return (int)pr.getBaseColWidth(); + CTSheetFormatPr pr = worksheet.getSheetFormatPr(); + return pr == null ? 8 : (int)pr.getBaseColWidth(); } /** @@ -466,7 +480,7 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { * @return default row height */ public short getDefaultRowHeight() { - return (short) (getSheetTypeSheetFormatPr().getDefaultRowHeight() * 20); + return (short)(getDefaultRowHeightInPoints() * 20); } /** @@ -475,10 +489,11 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { * @return default row height in points */ public float getDefaultRowHeightInPoints() { - return (float)getSheetTypeSheetFormatPr().getDefaultRowHeight(); + CTSheetFormatPr pr = worksheet.getSheetFormatPr(); + return (float)(pr == null ? 0 : pr.getDefaultRowHeight()); } - protected CTSheetFormatPr getSheetTypeSheetFormatPr() { + private CTSheetFormatPr getSheetTypeSheetFormatPr() { return worksheet.isSetSheetFormatPr() ? worksheet.getSheetFormatPr() : worksheet.addNewSheetFormatPr(); @@ -528,14 +543,14 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { return psSetup.getFitToPage(); } - protected CTSheetPr getSheetTypeSheetPr() { + private CTSheetPr getSheetTypeSheetPr() { if (worksheet.getSheetPr() == null) { worksheet.setSheetPr(CTSheetPr.Factory.newInstance()); } return worksheet.getSheetPr(); } - protected CTHeaderFooter getSheetTypeHeaderFooter() { + private CTHeaderFooter getSheetTypeHeaderFooter() { if (worksheet.getHeaderFooter() == null) { worksheet.setHeaderFooter(CTHeaderFooter.Factory.newInstance()); } @@ -617,15 +632,12 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { } + /** + * Determine whether printed output for this sheet will be horizontally centered. + */ public boolean getHorizontallyCenter() { - return getSheetTypePrintOptions().getHorizontalCentered(); - } - - protected CTPrintOptions getSheetTypePrintOptions() { - if (worksheet.getPrintOptions() == null) { - worksheet.setPrintOptions(CTPrintOptions.Factory.newInstance()); - } - return worksheet.getPrintOptions(); + CTPrintOptions opts = worksheet.getPrintOptions(); + return opts != null && opts.getHorizontalCentered(); } public int getLastRowNum() { @@ -732,9 +744,17 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { return hyperlinks.size(); } + /** + * Returns the information regarding the currently configured pane (split or freeze). + * + * @return null if no pane configured, or the pane information. + */ public PaneInformation getPaneInformation() { - // TODO Auto-generated method stub - return null; + CTPane pane = getPane(); + CellReference cellRef = pane.isSetTopLeftCell() ? new CellReference(pane.getTopLeftCell()) : null; + return new PaneInformation((short)pane.getXSplit(), (short)pane.getYSplit(), + (short)(cellRef == null ? 0 : cellRef.getRow()),(cellRef == null ? 0 : cellRef.getCol()), + (byte)pane.getActivePane().intValue(), pane.getState() == STPaneState.FROZEN); } /** @@ -905,7 +925,8 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { * @return whether printed output for this sheet will be vertically centered. */ public boolean getVerticallyCenter() { - return getSheetTypePrintOptions().getVerticalCentered(); + CTPrintOptions opts = worksheet.getPrintOptions(); + return opts != null && opts.getVerticalCentered(); } /** @@ -997,16 +1018,37 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { } /** - * Gets the flag indicating whether this sheet should display gridlines. + * Gets the flag indicating whether this sheet displays the lines + * between rows and columns to make editing and reading easier. * - * @return true if this sheet should display gridlines. + * @return true if this sheet displays gridlines. + * @see #isPrintGridlines() to check if printing of gridlines is turned on or off */ public boolean isDisplayGridlines() { return getSheetTypeSheetView().getShowGridLines(); } + /** + * Sets the flag indicating whether this sheet should display the lines + * between rows and columns to make editing and reading easier. + * To turn printing of gridlines use {@link #setPrintGridlines(boolean)} + * + * + * @param show true if this sheet should display gridlines. + * @see #setPrintGridlines(boolean) + */ + public void setDisplayGridlines(boolean show) { + getSheetTypeSheetView().setShowGridLines(show); + } + /** * Gets the flag indicating whether this sheet should display row and column headings. + *

+ * Row heading are the row numbers to the side of the sheet + *

+ *

+ * Column heading are the letters or numbers that appear above the columns of the sheet + *

* * @return true if this sheet should display row and column headings. */ @@ -1014,12 +1056,40 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { return getSheetTypeSheetView().getShowRowColHeaders(); } - public boolean isGridsPrinted() { - return isPrintGridlines(); + /** + * Sets the flag indicating whether this sheet should display row and column headings. + *

+ * Row heading are the row numbers to the side of the sheet + *

+ *

+ * Column heading are the letters or numbers that appear above the columns of the sheet + *

+ * + * @param show true if this sheet should display row and column headings. + */ + public void setDisplayRowColHeadings(boolean show) { + getSheetTypeSheetView().setShowRowColHeaders(show); } + /** + * Returns whether gridlines are printed. + * + * @return whether gridlines are printed + */ public boolean isPrintGridlines() { - return getSheetTypePrintOptions().getGridLines(); + CTPrintOptions opts = worksheet.getPrintOptions(); + return opts != null && opts.getGridLines(); + } + + /** + * Turns on or off the printing of gridlines. + * + * @param value boolean to turn on or off the printing of gridlines + */ + public void setPrintGridlines(boolean value) { + CTPrintOptions opts = worksheet.isSetPrintOptions() ? + worksheet.getPrintOptions() : worksheet.addNewPrintOptions(); + opts.setGridLines(value); } /** @@ -1103,6 +1173,11 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { } } + /** + * @return an iterator of the PHYSICAL rows. Meaning the 3rd element may not + * be the third row if say for instance the second row is undefined. + * Call getRowNum() on each row if you care which one it is. + */ public Iterator rowIterator() { return rows.values().iterator(); } @@ -1115,16 +1190,6 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { return rowIterator(); } - public void setAlternativeExpression(boolean b) { - // TODO Auto-generated method stub - - } - - public void setAlternativeFormula(boolean b) { - // TODO Auto-generated method stub - - } - /** * Flag indicating whether the sheet displays Automatic Page Breaks. * @@ -1148,6 +1213,11 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { psSetup.setAutoPageBreaks(value); } + /** + * Sets a page break at the indicated column + * + * @param column the column to break + */ public void setColumnBreak(short column) { if (! isColumnBroken(column)) { CTBreak brk = getSheetTypeColumnBreaks().addNewBrk(); @@ -1160,6 +1230,11 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { } + public void setRowGroupCollapsed(int row, boolean collapse) { + // TODO Auto-generated method stub + + } + /** * Get the visibility state for a given column. * @@ -1225,31 +1300,13 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { getSheetTypeSheetView().setShowFormulas(show); } - protected CTSheetView getSheetTypeSheetView() { + private CTSheetView getSheetTypeSheetView() { if (getDefaultSheetView() == null) { getSheetTypeSheetViews().setSheetViewArray(0, CTSheetView.Factory.newInstance()); } return getDefaultSheetView(); } - /** - * Sets the flag indicating whether this sheet should display gridlines. - * - * @param show true if this sheet should display gridlines. - */ - public void setDisplayGridlines(boolean show) { - getSheetTypeSheetView().setShowGridLines(show); - } - - /** - * Sets the flag indicating whether this sheet should display row and column headings. - * - * @param show true if this sheet should display row and column headings. - */ - public void setDisplayRowColHeadings(boolean show) { - getSheetTypeSheetView().setShowRowColHeaders(show); - } - /** * Flag indicating whether the Fit to Page print option is enabled. * @@ -1259,30 +1316,26 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { getSheetTypePageSetUpPr().setFitToPage(b); } - public void setGridsPrinted(boolean value) { - setPrintGridlines(value); - } - /** * Center on page horizontally when printing. * * @param value whether to center on page horizontally when printing. */ public void setHorizontallyCenter(boolean value) { - getSheetTypePrintOptions().setHorizontalCentered(value); - } - - public void setPrintGridlines(boolean newPrintGridlines) { - getSheetTypePrintOptions().setGridLines(newPrintGridlines); - } - - public void setRowGroupCollapsed(int row, boolean collapse) { - // TODO Auto-generated method stub - + CTPrintOptions opts = worksheet.isSetPrintOptions() ? + worksheet.getPrintOptions() : worksheet.addNewPrintOptions(); + opts.setHorizontalCentered(value); } + /** + * Whether the output is vertically centered on the page. + * + * @param value true to vertically center, false otherwise. + */ public void setVerticallyCenter(boolean value) { - getSheetTypePrintOptions().setVerticalCentered(value); + CTPrintOptions opts = worksheet.isSetPrintOptions() ? + worksheet.getPrintOptions() : worksheet.addNewPrintOptions(); + opts.setVerticalCentered(value); } /** @@ -1316,77 +1369,13 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { * Current view can be Normal, Page Layout, or Page Break Preview. * * @param scale window zoom magnification + * @throws IllegalArgumentException if scale is invalid */ public void setZoom(int scale) { + if(scale < 10 || scale > 400) throw new IllegalArgumentException("Valid scale values range from 10 to 400"); getSheetTypeSheetView().setZoomScale(scale); } - /** - * Zoom magnification to use when in normal view, representing percent values. - * Valid values range from 10 to 400. Horizontal & Vertical scale together. - * - * For example: - * 
-     * 10 - 10%
-     * 20 - 20%
-     * …
-     * 100 - 100%
-     * …
-     * 400 - 400%
-     *
- * - * Applies for worksheet sheet type only; zero implies the automatic setting. - * - * @param scale window zoom magnification - */ - public void setZoomNormal(int scale) { - getSheetTypeSheetView().setZoomScaleNormal(scale); - } - - /** - * Zoom magnification to use when in page layout view, representing percent values. - * Valid values range from 10 to 400. Horizontal & Vertical scale together. - * - * For example: - * 
-     * 10 - 10%
-     * 20 - 20%
-     * …
-     * 100 - 100%
-     * …
-     * 400 - 400%
-     *
- * - * Applies for worksheet sheet type only; zero implies the automatic setting. - * - * @param scale - */ - public void setZoomPageLayoutView(int scale) { - getSheetTypeSheetView().setZoomScalePageLayoutView(scale); - } - - /** - * Zoom magnification to use when in page break preview, representing percent values. - * Valid values range from 10 to 400. Horizontal & Vertical scale together. - * - * For example: - * 
-     * 10 - 10%
-     * 20 - 20%
-     * …
-     * 100 - 100%
-     * …
-     * 400 - 400%
-     *
- * - * Applies for worksheet only; zero implies the automatic setting. - * - * @param scale - */ - public void setZoomSheetLayoutView(int scale) { - getSheetTypeSheetView().setZoomScaleSheetLayoutView(scale); - } - /** * Shifts rows between startRow and endRow n number of rows. * If you use a negative number, it will shift rows up. @@ -1414,7 +1403,6 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { * Additionally shifts merged regions that are completely defined in these * rows (ie. merged 2 cells on a row to be shifted). *

- * TODO Might want to add bounds checking here * @param startRow the row to start shifting * @param endRow the row to end shifting * @param n the number of rows to shift @@ -1457,33 +1445,33 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { } public void ungroupColumn(short fromColumn, short toColumn) { - CTCols cols=worksheet.getColsArray(0); - for(int index=fromColumn;index<=toColumn;index++){ - CTCol col=columnHelper.getColumn(index, false); - if(col!=null){ - short outlineLevel=col.getOutlineLevel(); - col.setOutlineLevel((short)(outlineLevel-1)); - index=(int)col.getMax(); - - if(col.getOutlineLevel()<=0){ - int colIndex=columnHelper.getIndexOfColumn(cols,col); + CTCols cols = worksheet.getColsArray(0); + for (int index = fromColumn; index <= toColumn; index++) { + CTCol col = columnHelper.getColumn(index, false); + if (col != null) { + short outlineLevel = col.getOutlineLevel(); + col.setOutlineLevel((short) (outlineLevel - 1)); + index = (int) col.getMax(); + + if (col.getOutlineLevel() <= 0) { + int colIndex = columnHelper.getIndexOfColumn(cols, col); worksheet.getColsArray(0).removeCol(colIndex); } } } - worksheet.setColsArray(0,cols); + worksheet.setColsArray(0, cols); setSheetFormatPrOutlineLevelCol(); } public void ungroupRow(int fromRow, int toRow) { - for(int i=fromRow;i<=toRow;i++){ - XSSFRow xrow=getRow(i-1); - if(xrow!=null){ - CTRow ctrow=xrow.getCTRow(); - short outlinelevel=ctrow.getOutlineLevel(); - ctrow.setOutlineLevel((short)(outlinelevel-1)); + for (int i = fromRow; i <= toRow; i++) { + XSSFRow xrow = getRow(i - 1); + if (xrow != null) { + CTRow ctrow = xrow.getCTRow(); + short outlinelevel = ctrow.getOutlineLevel(); + ctrow.setOutlineLevel((short) (outlinelevel - 1)); //remove a row only if the row has no cell and if the outline level is 0 - if(ctrow.getOutlineLevel()==0 && xrow.getFirstCellNum()==-1){ + if (ctrow.getOutlineLevel() == 0 && xrow.getFirstCellNum() == -1) { removeRow(xrow); } } @@ -1621,7 +1609,7 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { * Returns the sheet's comments object if there is one, * or null if not */ - protected CommentsTable getCommentsSourceIfExists() { + protected CommentsTable getCommentsTable() { return sheetComments; } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java index cc252d57d9..de59bb3fa0 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -169,7 +169,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable(shIdMap.size()); for (CTSheet ctSheet : this.workbook.getSheets().getSheetArray()) { XSSFSheet sh = shIdMap.get(ctSheet.getId()); @@ -189,7 +189,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable(); - if(workbook.getDefinedNames() != null) { + if(workbook.isSetDefinedNames()) { for(CTDefinedName ctName : workbook.getDefinedNames().getDefinedNameArray()) { namedRanges.add(new XSSFName(ctName, this)); } @@ -381,16 +381,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterablenull if it does not exist */ public XSSFSheet getSheet(String name) { - CTSheet[] ctSheets = this.workbook.getSheets().getSheetArray(); - for (int i = 0 ; i < ctSheets.length ; ++i) { - if (name.equalsIgnoreCase(ctSheets[i].getName())) { - return sheets.get(i); + for (XSSFSheet sheet : sheets) { + if (name.equalsIgnoreCase(sheet.getSheetName())) { + return sheet; } } return null; @@ -669,9 +643,9 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable-1 Short.MAX_VALUE) { - throw new IllegalArgumentException("Sheet number [" + sheetNumber + "]is not valid "); - } + validateSheetIndex(sheetNumber); CTDefinedNames names = workbook.getDefinedNames() == null ? workbook.addNewDefinedNames() : workbook.getDefinedNames(); CTDefinedName nameRecord = names.addNewDefinedName(); @@ -995,7 +969,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable