diff options
39 files changed, 2103 insertions, 590 deletions
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.
+ * <pre>
+ * Usage:
+ * CalendarDemo <year>
+ * </pre>
+ *
+ * @author Yegor Kozlov
+ */
+public class CalendarDemo {
+
+ private static final String[] days = {
+ "Sunday", "Monday", "Tuesday",
+ "Wednesday", "Thursday", "Friday", "Saturday"};
+
+ private static final String[] months = {
+ "January", "February", "March","April", "May", "June","July", "August",
+ "September","October", "November", "December"};
+
+ public static void main(String[] args) throws Exception {
+
+ Calendar calendar = Calendar.getInstance();
+ if(args.length > 0) calendar.set(Calendar.YEAR, Integer.parseInt(args[0]));
+
+ int year = calendar.get(Calendar.YEAR);
+
+ XSSFWorkbook wb = new XSSFWorkbook();
+ Map<String, XSSFCellStyle> styles = createStyles(wb);
+
+ for (int month = 0; month < 12; month++) {
+ calendar.set(Calendar.MONTH, month);
+ calendar.set(Calendar.DAY_OF_MONTH, 1);
+ //create a sheet for each month
+ XSSFSheet sheet = wb.createSheet(months[month]);
+
+ //turn off gridlines
+ sheet.setDisplayGridlines(false);
+ sheet.setPrintGridlines(false);
+ XSSFPrintSetup printSetup = sheet.getPrintSetup();
+ printSetup.setOrientation(PrintOrientation.LANDSCAPE);
+ sheet.setFitToPage(true);
+ sheet.setHorizontallyCenter(true);
+
+ //the header row: centered text in 48pt font
+ XSSFRow headerRow = sheet.createRow(0);
+ headerRow.setHeightInPoints(80);
+ XSSFCell titleCell = headerRow.createCell(0);
+ titleCell.setCellValue(months[month] + " " + year);
+ titleCell.setCellStyle(styles.get("title"));
+ sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));
+
+ //header with month titles
+ XSSFRow monthRow = sheet.createRow(1);
+ for (int i = 0; i < days.length; i++) {
+ //for compatibility with HSSF we have to set column width in units of 1/256th of a character width
+ sheet.setColumnWidth(i*2, 5*256); //the column is 5 characters wide
+ sheet.setColumnWidth(i*2 + 1, 13*256); //the column is 13 characters wide
+ sheet.addMergedRegion(new CellRangeAddress(1, 1, i*2, i*2+1));
+ XSSFCell monthCell = monthRow.createCell(i*2);
+ monthCell.setCellValue(days[i]);
+ monthCell.setCellStyle(styles.get("month"));
+ }
+
+ int cnt = 1, day=1;
+ int rownum = 2;
+ for (int j = 0; j < 6; j++) {
+ XSSFRow row = sheet.createRow(rownum++);
+ row.setHeightInPoints(100);
+ for (int i = 0; i < days.length; i++) {
+ XSSFCell dayCell_1 = row.createCell(i*2);
+ XSSFCell dayCell_2 = row.createCell(i*2 + 1);
+
+ int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
+ if(cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
+ dayCell_1.setCellValue(day);
+ calendar.set(Calendar.DAY_OF_MONTH, ++day);
+
+ if(i == 0 || i == days.length-1) {
+ dayCell_1.setCellStyle(styles.get("weekend_left"));
+ dayCell_2.setCellStyle(styles.get("weekend_right"));
+ } else {
+ dayCell_1.setCellStyle(styles.get("workday_left"));
+ dayCell_2.setCellStyle(styles.get("workday_right"));
+ }
+ } else {
+ dayCell_1.setCellStyle(styles.get("grey_left"));
+ dayCell_2.setCellStyle(styles.get("grey_right"));
+ }
+ cnt++;
+ }
+ if(calendar.get(Calendar.MONTH) > month) break;
+ }
+ }
+
+ // Write the output to a file
+ FileOutputStream out = new FileOutputStream("calendar-"+year+".xlsx");
+ wb.write(out);
+ out.close();
+ }
+
+ /**
+ * cell styles used for formatting calendar sheets
+ */
+ public static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
+ Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
+
+ XSSFCellStyle style;
+ XSSFFont titleFont = wb.createFont();
+ titleFont.setFontHeightInPoints((short)48);
+ titleFont.setColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFont(titleFont);
+ styles.put("title", style);
+
+ XSSFFont monthFont = wb.createFont();
+ monthFont.setFontHeightInPoints((short)12);
+ monthFont.setColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
+ monthFont.setBold(true);
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setFont(monthFont);
+ styles.put("month", style);
+
+ XSSFFont dayFont = wb.createFont();
+ dayFont.setFontHeightInPoints((short)14);
+ dayFont.setBold(true);
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.LEFT);
+ style.setVerticalAlignment(VerticalAlignment.TOP);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(228, 232, 243)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setBorderLeft(BorderStyle.THIN);
+ style.setLeftBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setFont(dayFont);
+ styles.put("weekend_left", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.TOP);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(228, 232, 243)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setBorderRight(BorderStyle.THIN);
+ style.setRightBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ styles.put("weekend_right", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.LEFT);
+ style.setVerticalAlignment(VerticalAlignment.TOP);
+ style.setBorderLeft(BorderStyle.THIN);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setLeftBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setFont(dayFont);
+ styles.put("workday_left", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.TOP);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setBorderRight(BorderStyle.THIN);
+ style.setRightBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ styles.put("workday_right", style);
+
+ style = wb.createCellStyle();
+ style.setBorderLeft(BorderStyle.THIN);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ styles.put("grey_left", style);
+
+ style = wb.createCellStyle();
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setBorderRight(BorderStyle.THIN);
+ style.setRightBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(new XSSFColor(new java.awt.Color(39, 51, 89)));
+ styles.put("grey_right", style);
+
+ return styles;
+ }
+}
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<String, XSSFCellStyle> styles = createStyles(wb);
+ XSSFSheet sheet = wb.createSheet("Loan Calculator");
+ sheet.setPrintGridlines(false);
+ sheet.setDisplayGridlines(false);
+
+ XSSFPrintSetup printSetup = sheet.getPrintSetup();
+ printSetup.setOrientation(PrintOrientation.LANDSCAPE);
+ sheet.setFitToPage(true);
+ sheet.setHorizontallyCenter(true);
+
+ sheet.setColumnWidth(0, 3*256);
+ sheet.setColumnWidth(1, 3*256);
+ sheet.setColumnWidth(2, 11*256);
+ sheet.setColumnWidth(3, 14*256);
+ sheet.setColumnWidth(4, 14*256);
+ sheet.setColumnWidth(5, 14*256);
+ sheet.setColumnWidth(6, 14*256);
+
+ createNames(wb);
+
+ XSSFRow titleRow = sheet.createRow(0);
+ titleRow.setHeightInPoints(35);
+ for (int i = 1; i <= 7; i++) {
+ titleRow.createCell(i).setCellStyle(styles.get("title"));
+ }
+ XSSFCell titleCell = titleRow.getCell(2);
+ titleCell.setCellValue("Simple Loan Calculator");
+ sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1"));
+
+ XSSFRow row = sheet.createRow(2);
+ XSSFCell cell = row.createCell(4);
+ cell.setCellValue("Enter values");
+ cell.setCellStyle(styles.get("item_right"));
+
+ row = sheet.createRow(3);
+ cell = row.createCell(2);
+ cell.setCellValue("Loan amount");
+ cell.setCellStyle(styles.get("item_left"));
+ cell = row.createCell(4);
+ cell.setCellStyle(styles.get("input_$"));
+
+ row = sheet.createRow(4);
+ cell = row.createCell(2);
+ cell.setCellValue("Annual interest rate");
+ cell.setCellStyle(styles.get("item_left"));
+ cell = row.createCell(4);
+ cell.setCellStyle(styles.get("input_%"));
+
+ row = sheet.createRow(5);
+ cell = row.createCell(2);
+ cell.setCellValue("Loan period in years");
+ cell.setCellStyle(styles.get("item_left"));
+ cell = row.createCell(4);
+ cell.setCellStyle(styles.get("input_i"));
+
+ row = sheet.createRow(6);
+ cell = row.createCell(2);
+ cell.setCellValue("Start date of loan");
+ cell.setCellStyle(styles.get("item_left"));
+ cell = row.createCell(4);
+ cell.setCellStyle(styles.get("input_d"));
+
+ row = sheet.createRow(8);
+ cell = row.createCell(2);
+ cell.setCellValue("Monthly payment");
+ cell.setCellStyle(styles.get("item_left"));
+ cell = row.createCell(4);
+ cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")");
+ cell.setCellStyle(styles.get("formula_$"));
+
+ row = sheet.createRow(9);
+ cell = row.createCell(2);
+ cell.setCellValue("Number of payments");
+ cell.setCellStyle(styles.get("item_left"));
+ cell = row.createCell(4);
+ cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")");
+ cell.setCellStyle(styles.get("formula_i"));
+
+ row = sheet.createRow(10);
+ cell = row.createCell(2);
+ cell.setCellValue("Total interest");
+ cell.setCellStyle(styles.get("item_left"));
+ cell = row.createCell(4);
+ cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")");
+ cell.setCellStyle(styles.get("formula_$"));
+
+ row = sheet.createRow(11);
+ cell = row.createCell(2);
+ cell.setCellValue("Total cost of loan");
+ cell.setCellStyle(styles.get("item_left"));
+ cell = row.createCell(4);
+ cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")");
+ cell.setCellStyle(styles.get("formula_$"));
+
+ sheet.setActiveCell("E4");
+
+ // Write the output to a file
+ FileOutputStream out = new FileOutputStream("loan-calculator.xlsx");
+ wb.write(out);
+ out.close();
+ }
+
+ /**
+ * cell styles used for formatting calendar sheets
+ */
+ public static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
+ Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
+
+ XSSFCellStyle style;
+ XSSFFont titleFont = wb.createFont();
+ titleFont.setFontHeightInPoints((short)14);
+ titleFont.setFontName("Trebuchet MS");
+ style = wb.createCellStyle();
+ style.setFont(titleFont);
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ styles.put("title", style);
+
+ XSSFFont itemFont = wb.createFont();
+ itemFont.setFontHeightInPoints((short)9);
+ itemFont.setFontName("Trebuchet MS");
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.LEFT);
+ style.setFont(itemFont);
+ styles.put("item_left", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.RIGHT);
+ style.setFont(itemFont);
+ styles.put("item_right", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(BorderStyle.DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(BorderStyle.DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(BorderStyle.DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
+ styles.put("input_$", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(BorderStyle.DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(BorderStyle.DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(BorderStyle.DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("0.000%"));
+ styles.put("input_%", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(BorderStyle.DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(BorderStyle.DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(BorderStyle.DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("0"));
+ styles.put("input_i", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setFont(itemFont);
+ style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy"));
+ styles.put("input_d", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(BorderStyle.DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(BorderStyle.DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(BorderStyle.DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00"));
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ styles.put("formula_$", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.RIGHT);
+ style.setFont(itemFont);
+ style.setBorderRight(BorderStyle.DOTTED);
+ style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderLeft(BorderStyle.DOTTED);
+ style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setBorderTop(BorderStyle.DOTTED);
+ style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setDataFormat(wb.createDataFormat().getFormat("0"));
+ style.setBorderBottom(BorderStyle.DOTTED);
+ style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ styles.put("formula_i", style);
+
+ return styles;
+ }
+
+ //define named ranges for the inputs and formulas
+ public static void createNames(XSSFWorkbook wb){
+ XSSFName name;
+
+ name = wb.createName();
+ name.setNameName("Header_Row");
+ name.setReference("ROW('Loan Calculator'!#REF!)");
+
+ name = wb.createName();
+ name.setNameName("Interest_Rate");
+ name.setReference("'Loan Calculator'!$E$5");
+
+ name = wb.createName();
+ name.setNameName("Loan_Amount");
+ name.setReference("'Loan Calculator'!$E$4");
+
+ name = wb.createName();
+ name.setNameName("Loan_Not_Paid");
+ name.setReference("F(Payment_Number<=Number_of_Payments,1,0)");
+
+ name = wb.createName();
+ name.setNameName("Loan_Start");
+ name.setReference("'Loan Calculator'!$E$7");
+
+ name = wb.createName();
+ name.setNameName("Loan_Years");
+ name.setReference("'Loan Calculator'!$E$6");
+
+ name = wb.createName();
+ name.setNameName("Monthly_Payment");
+ name.setReference("-PMT(Interest_Rate/12,Number_of_Payments,Loan_Amount)");
+
+ name = wb.createName();
+ name.setNameName("Number_of_Payments");
+ name.setReference("'Loan Calculator'!$E$10");
+
+ name = wb.createName();
+ name.setNameName("Payment_Number");
+ name.setReference("ROW()-Header_Row");
+
+ name = wb.createName();
+ name.setNameName("Principal");
+ name.setReference("-PPMT(Interest_Rate/12,Payment_Number,Number_of_Payments,Loan_Amount)");
+
+ name = wb.createName();
+ name.setNameName("Total_Cost");
+ name.setReference("'Loan Calculator'!$E$12");
+
+ name = wb.createName();
+ name.setNameName("Total_Interest");
+ name.setReference("'Loan Calculator'!$E$11");
+
+ name = wb.createName();
+ name.setNameName("Values_Entered");
+ name.setReference("IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Start>0,1,0)");
+
+
+ }
+}
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 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<String, XSSFCellStyle> styles = createStyles(wb);
+
+ XSSFSheet sheet = wb.createSheet("Timesheet");
+ XSSFPrintSetup printSetup = sheet.getPrintSetup();
+ printSetup.setOrientation(PrintOrientation.LANDSCAPE);
+ sheet.setFitToPage(true);
+ sheet.setHorizontallyCenter(true);
+
+ //title row
+ XSSFRow titleRow = sheet.createRow(0);
+ titleRow.setHeightInPoints(45);
+ XSSFCell titleCell = titleRow.createCell(0);
+ titleCell.setCellValue("Weekly Timesheet");
+ titleCell.setCellStyle(styles.get("title"));
+ sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
+
+ //header row
+ XSSFRow headerRow = sheet.createRow(1);
+ headerRow.setHeightInPoints(40);
+ XSSFCell headerCell;
+ for (int i = 0; i < titles.length; i++) {
+ headerCell = headerRow.createCell(i);
+ headerCell.setCellValue(titles[i]);
+ headerCell.setCellStyle(styles.get("header"));
+ }
+
+ int rownum = 2;
+ for (int i = 0; i < 10; i++) {
+ XSSFRow row = sheet.createRow(rownum++);
+ for (int j = 0; j < titles.length; j++) {
+ XSSFCell cell = row.createCell(j);
+ if(j == 9){
+ //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
+ String ref = "C" +rownum+ ":I" + rownum;
+ cell.setCellFormula("SUM("+ref+")");
+ cell.setCellStyle(styles.get("formula"));
+ } else if (j == 11){
+ cell.setCellFormula("J" +rownum+ "-K" + rownum);
+ cell.setCellStyle(styles.get("formula"));
+ } else {
+ cell.setCellStyle(styles.get("cell"));
+ }
+ }
+ }
+
+ //row with totals below
+ XSSFRow sumRow = sheet.createRow(rownum++);
+ sumRow.setHeightInPoints(35);
+ XSSFCell cell;
+ cell = sumRow.createCell(0);
+ cell.setCellStyle(styles.get("formula"));
+ cell = sumRow.createCell(1);
+ cell.setCellValue("Total Hrs:");
+ cell.setCellStyle(styles.get("formula"));
+
+ for (int j = 2; j < 12; j++) {
+ cell = sumRow.createCell(j);
+ String ref = (char)('A' + j) + "3:" + (char)('A' + j) + "12";
+ cell.setCellFormula("SUM(" + ref + ")");
+ if(j >= 9) cell.setCellStyle(styles.get("formula_2"));
+ else cell.setCellStyle(styles.get("formula"));
+ }
+ rownum++;
+ sumRow = sheet.createRow(rownum++);
+ sumRow.setHeightInPoints(25);
+ cell = sumRow.createCell(0);
+ cell.setCellValue("Total Regular Hours");
+ cell.setCellStyle(styles.get("formula"));
+ cell = sumRow.createCell(1);
+ cell.setCellFormula("L13");
+ cell.setCellStyle(styles.get("formula_2"));
+ sumRow = sheet.createRow(rownum++);
+ sumRow.setHeightInPoints(25);
+ cell = sumRow.createCell(0);
+ cell.setCellValue("Total Overtime Hours");
+ cell.setCellStyle(styles.get("formula"));
+ cell = sumRow.createCell(1);
+ cell.setCellFormula("K13");
+ cell.setCellStyle(styles.get("formula_2"));
+
+ //set sample data
+ for (int i = 0; i < sample_data.length; i++) {
+ XSSFRow row = sheet.getRow(2 + i);
+ for (int j = 0; j < sample_data[i].length; j++) {
+ if(sample_data[i][j] == null) continue;
+
+ if(sample_data[i][j] instanceof String) {
+ row.getCell(j).setCellValue((String)sample_data[i][j]);
+ } else {
+ row.getCell(j).setCellValue((Double)sample_data[i][j]);
+ }
+ }
+ }
+
+ //finally set column widths
+ sheet.setColumnWidth(0, 30*256);
+ for (int i = 2; i < 9; i++) {
+ sheet.setColumnWidth(i, 6*256);
+ }
+
+ // Write the output to a file
+ FileOutputStream out = new FileOutputStream("ooxml-timesheet.xlsx");
+ wb.write(out);
+ out.close();
+ }
+
+ public static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
+ Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
+ XSSFCellStyle style;
+ XSSFFont titleFont = wb.createFont();
+ titleFont.setFontHeightInPoints((short)18);
+ titleFont.setBold(true);
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setFont(titleFont);
+ styles.put("title", style);
+
+ XSSFFont monthFont = wb.createFont();
+ monthFont.setFontHeightInPoints((short)11);
+ monthFont.setColor(new XSSFColor(new java.awt.Color(255, 255, 255)));
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(102, 102, 102)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setFont(monthFont);
+ style.setWrapText(true);
+ styles.put("header", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setWrapText(true);
+ style.setBorderRight(BorderStyle.THIN);
+ style.setRightBorderColor(IndexedColors.BLACK.getIndex());
+ style.setBorderLeft(BorderStyle.THIN);
+ style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
+ style.setBorderTop(BorderStyle.THIN);
+ style.setTopBorderColor(IndexedColors.BLACK.getIndex());
+ style.setBorderBottom(BorderStyle.THIN);
+ style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
+ styles.put("cell", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(234, 234, 234)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
+ styles.put("formula", style);
+
+ style = wb.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ style.setFillForegroundColor(new XSSFColor(new java.awt.Color(192, 192, 192)));
+ style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
+ style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
+ styles.put("formula_2", style);
+
+ return styles;
+ }
+}
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.
+ * <p>
+ * Internally, formats will be implemented using subclasses of {@link Format}
+ * such as {@link DecimalFormat} and {@link SimpleDateFormat}. Therefore the
+ * formats used by this class must obey the same pattern rules as these Format
+ * subclasses. This means that only legal number pattern characters ("0", "#",
+ * ".", "," etc.) may appear in number formats. Other characters can be
+ * inserted <em>before</em> or <em> after</em> the number pattern to form a
+ * prefix or suffix.
+ * </p>
+ * <p>
+ * For example the Excel pattern <code>"$#,##0.00 "USD"_);($#,##0.00 "USD")"
+ * </code> will be correctly formatted as "$1,000.00 USD" or "($1,000.00 USD)".
+ * However the pattern <code>"00-00-00"</code> is incorrectly formatted by
+ * DecimalFormat as "000000--". For Excel formats that are not compatible with
+ * DecimalFormat, you can provide your own custom {@link Format} implementation
+ * via <code>DataFormatter.addFormat(String,Format)</code>. The following
+ * custom formats are already provided by this class:
+ * </p>
+ * <pre>
+ * <ul><li>SSN "000-00-0000"</li>
+ * <li>Phone Number "(###) ###-####"</li>
+ * <li>Zip plus 4 "00000-0000"</li>
+ * </ul>
+ * </pre>
+ * <p>
+ * If the Excel format pattern cannot be parsed successfully, then a default
+ * format will be used. The default number format will mimic the Excel General
+ * format: "#" for whole numbers and "#.##########" for decimal numbers. You
+ * can override the default format pattern with <code>
+ * DataFormatter.setDefaultNumberFormat(Format)</code>. <b>Note:</b> the
+ * default format will only be used when a Format cannot be created from the
+ * cell's data format string.
+ *
+ * @author James May (james dot may at fmr dot com)
+ *
+ */
+public class DataFormatter {
+
+ /** Pattern to find a number format: "0" or "#" */
+ private static final Pattern numPattern = Pattern.compile("[0#]+");
+
+ /** Pattern to find days of week as text "ddd...." */
+ private static final Pattern daysAsText = Pattern.compile("([d]{3,})", Pattern.CASE_INSENSITIVE);
+
+ /** Pattern to find "AM/PM" marker */
+ private static final Pattern amPmPattern = Pattern.compile("((A|P)[M/P]*)", Pattern.CASE_INSENSITIVE);
+
+ /** A regex to find patterns like [$$-1009] and [$?-452]. */
+ private static final Pattern specialPatternGroup = Pattern.compile("(\\[\\$[^-\\]]*-[0-9A-Z]+\\])");
+
+ /** <em>General</em> format for whole numbers. */
+ private static final Format generalWholeNumFormat = new DecimalFormat("#");
+
+ /** <em>General</em> format for decimal numbers. */
+ private static final Format generalDecimalNumFormat = new DecimalFormat("#.##########");
+
+ /** A default format to use when a number pattern cannot be parsed. */
+ private Format defaultNumFormat;
+
+ /**
+ * A map to cache formats.
+ * Map<String,Format> formats
+ */
+ private final Map formats;
+
+ /**
+ * Constructor
+ */
+ public DataFormatter() {
+ formats = new HashMap();
+
+ // init built-in formats
+
+ Format zipFormat = ZipPlusFourFormat.instance;
+ addFormat("00000\\-0000", zipFormat);
+ addFormat("00000-0000", zipFormat);
+
+ Format phoneFormat = PhoneFormat.instance;
+ // allow for format string variations
+ addFormat("[<=9999999]###\\-####;\\(###\\)\\ ###\\-####", phoneFormat);
+ addFormat("[<=9999999]###-####;(###) ###-####", phoneFormat);
+ addFormat("###\\-####;\\(###\\)\\ ###\\-####", phoneFormat);
+ addFormat("###-####;(###) ###-####", phoneFormat);
+
+ Format ssnFormat = SSNFormat.instance;
+ addFormat("000\\-00\\-0000", ssnFormat);
+ addFormat("000-00-0000", ssnFormat);
+ }
+
+ /**
+ * Return a Format for the given cell if one exists, otherwise try to
+ * create one. This method will return <code>null</code> if the any of the
+ * following is true:
+ * <ul>
+ * <li>the cell's style is null</li>
+ * <li>the style's data format string is null or empty</li>
+ * <li>the format string cannot be recognized as either a number or date</li>
+ * </ul>
+ *
+ * @param cell The cell to retrieve a Format for
+ * @return A Format for the format String
+ */
+ private Format getFormat(Cell cell) {
+ if ( cell.getCellStyle() == null) {
+ return null;
+ }
+
+ int formatIndex = cell.getCellStyle().getDataFormat();
+ String formatStr = cell.getCellStyle().getDataFormatString();
+ if(formatStr == null || formatStr.trim().length() == 0) {
+ return null;
+ }
+ return getFormat(cell.getNumericCellValue(), formatIndex, formatStr);
+ }
+
+ private Format getFormat(double cellValue, int formatIndex, String formatStr) {
+ Format format = (Format)formats.get(formatStr);
+ if (format != null) {
+ return format;
+ }
+ if (formatStr.equals("General")) {
+ if (DataFormatter.isWholeNumber(cellValue)) {
+ return generalWholeNumFormat;
+ }
+ return generalDecimalNumFormat;
+ }
+ format = createFormat(cellValue, formatIndex, formatStr);
+ formats.put(formatStr, format);
+ return format;
+ }
+
+ /**
+ * Create and return a Format based on the format string from a cell's
+ * style. If the pattern cannot be parsed, return a default pattern.
+ *
+ * @param cell The Excel cell
+ * @return A Format representing the excel format. May return null.
+ */
+ public Format createFormat(Cell cell) {
+
+ int formatIndex = cell.getCellStyle().getDataFormat();
+ String formatStr = cell.getCellStyle().getDataFormatString();
+ return createFormat(cell.getNumericCellValue(), formatIndex, formatStr);
+ }
+
+ private Format createFormat(double cellValue, int formatIndex, String sFormat) {
+ // remove color formatting if present
+ String formatStr = sFormat.replaceAll("\\[[a-zA-Z]*\\]", "");
+
+ // try to extract special characters like currency
+ Matcher m = specialPatternGroup.matcher(formatStr);
+ while(m.find()) {
+ String match = m.group();
+ String symbol = match.substring(match.indexOf('$') + 1, match.indexOf('-'));
+ if (symbol.indexOf('$') > -1) {
+ StringBuffer sb = new StringBuffer();
+ sb.append(symbol.substring(0, symbol.indexOf('$')));
+ sb.append('\\');
+ sb.append(symbol.substring(symbol.indexOf('$'), symbol.length()));
+ symbol = sb.toString();
+ }
+ formatStr = m.replaceAll(symbol);
+ m = specialPatternGroup.matcher(formatStr);
+ }
+
+ if(formatStr == null || formatStr.trim().length() == 0) {
+ return getDefaultFormat(cellValue);
+ }
+
+
+ if(DateUtil.isADateFormat(formatIndex,formatStr) &&
+ DateUtil.isValidExcelDate(cellValue)) {
+ return createDateFormat(formatStr, cellValue);
+ }
+ if (numPattern.matcher(formatStr).find()) {
+ return createNumberFormat(formatStr, cellValue);
+ }
+ // TODO - when does this occur?
+ return null;
+ }
+
+ private Format createDateFormat(String pFormatStr, double cellValue) {
+ String formatStr = pFormatStr;
+ formatStr = formatStr.replaceAll("\\\\-","-");
+ formatStr = formatStr.replaceAll("\\\\,",",");
+ formatStr = formatStr.replaceAll("\\\\ "," ");
+ formatStr = formatStr.replaceAll(";@", "");
+ boolean hasAmPm = false;
+ Matcher amPmMatcher = amPmPattern.matcher(formatStr);
+ while (amPmMatcher.find()) {
+ formatStr = amPmMatcher.replaceAll("@");
+ hasAmPm = true;
+ amPmMatcher = amPmPattern.matcher(formatStr);
+ }
+ formatStr = formatStr.replaceAll("@", "a");
+
+
+ Matcher dateMatcher = daysAsText.matcher(formatStr);
+ if (dateMatcher.find()) {
+ String match = dateMatcher.group(0);
+ formatStr = dateMatcher.replaceAll(match.toUpperCase().replaceAll("D", "E"));
+ }
+
+ // Convert excel date format to SimpleDateFormat.
+ // Excel uses lower case 'm' for both minutes and months.
+ // From Excel help:
+ /*
+ The "m" or "mm" code must appear immediately after the "h" or"hh"
+ code or immediately before the "ss" code; otherwise, Microsoft
+ Excel displays the month instead of minutes."
+ */
+
+ StringBuffer sb = new StringBuffer();
+ char[] chars = formatStr.toCharArray();
+ boolean mIsMonth = true;
+ List ms = new ArrayList();
+ for(int j=0; j<chars.length; j++) {
+ char c = chars[j];
+ if (c == 'h' || c == 'H') {
+ mIsMonth = false;
+ if (hasAmPm) {
+ sb.append('h');
+ } else {
+ sb.append('H');
+ }
+ }
+ else if (c == 'm') {
+ if(mIsMonth) {
+ sb.append('M');
+ ms.add(
+ new Integer(sb.length() -1)
+ );
+ } else {
+ sb.append('m');
+ }
+ }
+ else if (c == 's' || c == 'S') {
+ sb.append('s');
+ // if 'M' precedes 's' it should be minutes ('m')
+ for (int i = 0; i < ms.size(); i++) {
+ int index = ((Integer)ms.get(i)).intValue();
+ if (sb.charAt(index) == 'M') {
+ sb.replace(index, index+1, "m");
+ }
+ }
+ mIsMonth = true;
+ ms.clear();
+ }
+ else if (Character.isLetter(c)) {
+ mIsMonth = true;
+ ms.clear();
+ if (c == 'y' || c == 'Y') {
+ sb.append('y');
+ }
+ else if (c == 'd' || c == 'D') {
+ sb.append('d');
+ }
+ else {
+ sb.append(c);
+ }
+ }
+ else {
+ sb.append(c);
+ }
+ }
+ formatStr = sb.toString();
+
+ try {
+ return new SimpleDateFormat(formatStr);
+ } catch(IllegalArgumentException iae) {
+
+ // the pattern could not be parsed correctly,
+ // so fall back to the default number format
+ return getDefaultFormat(cellValue);
+ }
+
+ }
+
+ private Format createNumberFormat(String formatStr, double cellValue) {
+ StringBuffer sb = new StringBuffer(formatStr);
+ for (int i = 0; i < sb.length(); i++) {
+ char c = sb.charAt(i);
+ //handle (#,##0_);
+ if (c == '(') {
+ int idx = sb.indexOf(")", i);
+ if (idx > -1 && sb.charAt(idx -1) == '_') {
+ sb.deleteCharAt(idx);
+ sb.deleteCharAt(idx - 1);
+ sb.deleteCharAt(i);
+ i--;
+ }
+ } else if (c == ')' && i > 0 && sb.charAt(i - 1) == '_') {
+ sb.deleteCharAt(i);
+ sb.deleteCharAt(i - 1);
+ i--;
+ // remove quotes and back slashes
+ } else if (c == '\\' || c == '"') {
+ sb.deleteCharAt(i);
+ i--;
+
+ // for scientific/engineering notation
+ } else if (c == '+' && i > 0 && sb.charAt(i - 1) == 'E') {
+ sb.deleteCharAt(i);
+ i--;
+ }
+ }
+
+ try {
+ return new DecimalFormat(sb.toString());
+ } catch(IllegalArgumentException iae) {
+
+ // the pattern could not be parsed correctly,
+ // so fall back to the default number format
+ return getDefaultFormat(cellValue);
+ }
+ }
+
+ /**
+ * Return true if the double value represents a whole number
+ * @param d the double value to check
+ * @return <code>true</code> if d is a whole number
+ */
+ private static boolean isWholeNumber(double d) {
+ return d == Math.floor(d);
+ }
+
+ /**
+ * Returns a default format for a cell.
+ * @param cell The cell
+ * @return a default format
+ */
+ public Format getDefaultFormat(Cell cell) {
+ return getDefaultFormat(cell.getNumericCellValue());
+ }
+ private Format getDefaultFormat(double cellValue) {
+ // for numeric cells try user supplied default
+ if (defaultNumFormat != null) {
+ return defaultNumFormat;
+
+ // otherwise use general format
+ }
+ if (isWholeNumber(cellValue)){
+ return generalWholeNumFormat;
+ }
+ return generalDecimalNumFormat;
+ }
+
+ /**
+ * Returns the formatted value of an Excel date as a <tt>String</tt> based
+ * on the cell's <code>DataFormat</code>. i.e. "Thursday, January 02, 2003"
+ * , "01/02/2003" , "02-Jan" , etc.
+ *
+ * @param cell The cell
+ * @return a formatted date string
+ */
+ private String getFormattedDateString(Cell cell) {
+ Format dateFormat = getFormat(cell);
+ Date d = cell.getDateCellValue();
+ if (dateFormat != null) {
+ return dateFormat.format(d);
+ }
+ return d.toString();
+ }
+
+ /**
+ * Returns the formatted value of an Excel number as a <tt>String</tt>
+ * based on the cell's <code>DataFormat</code>. Supported formats include
+ * currency, percents, decimals, phone number, SSN, etc.:
+ * "61.54%", "$100.00", "(800) 555-1234".
+ *
+ * @param cell The cell
+ * @return a formatted number string
+ */
+ private String getFormattedNumberString(Cell cell) {
+
+ Format numberFormat = getFormat(cell);
+ double d = cell.getNumericCellValue();
+ if (numberFormat == null) {
+ return String.valueOf(d);
+ }
+ return numberFormat.format(new Double(d));
+ }
+
+ /**
+ * Formats the given raw cell value, based on the supplied
+ * format index and string, according to excel style rules.
+ * @see #formatCellValue(Cell)
+ */
+ public String formatRawCellContents(double value, int formatIndex, String formatString) {
+ // Is it a date?
+ if(DateUtil.isADateFormat(formatIndex,formatString) &&
+ DateUtil.isValidExcelDate(value)) {
+
+ Format dateFormat = getFormat(value, formatIndex, formatString);
+ Date d = DateUtil.getJavaDate(value);
+ if (dateFormat == null) {
+ return d.toString();
+ }
+ return dateFormat.format(d);
+ }
+ // else Number
+ Format numberFormat = getFormat(value, formatIndex, formatString);
+ if (numberFormat == null) {
+ return String.valueOf(value);
+ }
+ return numberFormat.format(new Double(value));
+ }
+
+ /**
+ * <p>
+ * Returns the formatted value of a cell as a <tt>String</tt> regardless
+ * of the cell type. If the Excel format pattern cannot be parsed then the
+ * cell value will be formatted using a default format.
+ * </p>
+ * <p>When passed a null or blank cell, this method will return an empty
+ * String (""). Formulas in formula type cells will not be evaluated.
+ * </p>
+ *
+ * @param cell The cell
+ * @return the formatted cell value as a String
+ */
+ public String formatCellValue(Cell cell) {
+ return formatCellValue(cell, null);
+ }
+
+ /**
+ * <p>
+ * Returns the formatted value of a cell as a <tt>String</tt> regardless
+ * of the cell type. If the Excel format pattern cannot be parsed then the
+ * cell value will be formatted using a default format.
+ * </p>
+ * <p>When passed a null or blank cell, this method will return an empty
+ * String (""). Formula cells will be evaluated using the given
+ * {@link FormulaEvaluator} if the evaluator is non-null. If the
+ * evaluator is null, then the formula String will be returned. The caller
+ * is responsible for setting the currentRow on the evaluator
+ *</p>
+ *
+ * @param cell The cell (can be null)
+ * @param evaluator The FormulaEvaluator (can be null)
+ * @return a string value of the cell
+ */
+ public String formatCellValue(Cell cell,
+ FormulaEvaluator evaluator) throws IllegalArgumentException {
+
+ if (cell == null) {
+ return "";
+ }
+
+ int cellType = cell.getCellType();
+ if (evaluator != null && cellType == Cell.CELL_TYPE_FORMULA) {
+ try {
+ cellType = evaluator.evaluateFormulaCell(cell);
+ } catch (RuntimeException e) {
+ throw new RuntimeException("Did you forget to set the current" +
+ " row on the FormulaEvaluator?", e);
+ }
+ }
+ switch (cellType)
+ {
+ case Cell.CELL_TYPE_FORMULA :
+ // should only occur if evaluator is null
+ return cell.getCellFormula();
+
+ case Cell.CELL_TYPE_NUMERIC :
+
+ if (DateUtil.isCellDateFormatted(cell)) {
+ return getFormattedDateString(cell);
+ }
+ return getFormattedNumberString(cell);
+
+ case Cell.CELL_TYPE_STRING :
+ return cell.getRichStringCellValue().getString();
+
+ case Cell.CELL_TYPE_BOOLEAN :
+ return String.valueOf(cell.getBooleanCellValue());
+ case Cell.CELL_TYPE_BLANK :
+ return "";
+ }
+ throw new RuntimeException("Unexpected celltype (" + cellType + ")");
+ }
+
+
+ /**
+ * <p>
+ * Sets a default number format to be used when the Excel format cannot be
+ * parsed successfully. <b>Note:</b> This is a fall back for when an error
+ * occurs while parsing an Excel number format pattern. This will not
+ * affect cells with the <em>General</em> format.
+ * </p>
+ * <p>
+ * The value that will be passed to the Format's format method (specified
+ * by <code>java.text.Format#format</code>) will be a double value from a
+ * numeric cell. Therefore the code in the format method should expect a
+ * <code>Number</code> value.
+ * </p>
+ *
+ * @param format A Format instance to be used as a default
+ * @see java.text.Format#format
+ */
+ public void setDefaultNumberFormat(Format format) {
+ Iterator itr = formats.entrySet().iterator();
+ while(itr.hasNext()) {
+ Map.Entry entry = (Map.Entry)itr.next();
+ if (entry.getValue() == generalDecimalNumFormat
+ || entry.getValue() == generalWholeNumFormat) {
+ entry.setValue(format);
+ }
+ }
+ defaultNumFormat = format;
+ }
+
+ /**
+ * Adds a new format to the available formats.
+ * <p>
+ * The value that will be passed to the Format's format method (specified
+ * by <code>java.text.Format#format</code>) will be a double value from a
+ * numeric cell. Therefore the code in the format method should expect a
+ * <code>Number</code> value.
+ * </p>
+ * @param excelFormatStr The data format string
+ * @param format A Format instance
+ */
+ public void addFormat(String excelFormatStr, Format format) {
+ formats.put(excelFormatStr, format);
+ }
+
+ // Some custom formats
+
+ /**
+ * @return a <tt>DecimalFormat</tt> with parseIntegerOnly set <code>true</code>
+ */
+ /* package */ static DecimalFormat createIntegerOnlyFormat(String fmt) {
+ DecimalFormat result = new DecimalFormat(fmt);
+ result.setParseIntegerOnly(true);
+ return result;
+ }
+ /**
+ * Format class for Excel's SSN format. This class mimics Excel's built-in
+ * SSN formatting.
+ *
+ * @author James May
+ */
+ private static final class SSNFormat extends Format {
+ public static final Format instance = new SSNFormat();
+ private static final DecimalFormat df = createIntegerOnlyFormat("000000000");
+ private SSNFormat() {
+ // enforce singleton
+ }
+
+ /** Format a number as an SSN */
+ public static String format(Number num) {
+ String result = df.format(num);
+ StringBuffer sb = new StringBuffer();
+ sb.append(result.substring(0, 3)).append('-');
+ sb.append(result.substring(3, 5)).append('-');
+ sb.append(result.substring(5, 9));
+ return sb.toString();
+ }
+
+ public StringBuffer format(Object obj, StringBuffer toAppendTo, FieldPosition pos) {
+ return toAppendTo.append(format((Number)obj));
+ }
+
+ public Object parseObject(String source, ParsePosition pos) {
+ return df.parseObject(source, pos);
+ }
+ }
+
+ /**
+ * Format class for Excel Zip + 4 format. This class mimics Excel's
+ * built-in formatting for Zip + 4.
+ * @author James May
+ */
+ private static final class ZipPlusFourFormat extends Format {
+ public static final Format instance = new ZipPlusFourFormat();
+ private static final DecimalFormat df = createIntegerOnlyFormat("000000000");
+ private ZipPlusFourFormat() {
+ // enforce singleton
+ }
+
+ /** Format a number as Zip + 4 */
+ public static String format(Number num) {
+ String result = df.format(num);
+ StringBuffer sb = new StringBuffer();
+ sb.append(result.substring(0, 5)).append('-');
+ sb.append(result.substring(5, 9));
+ return sb.toString();
+ }
+
+ public StringBuffer format(Object obj, StringBuffer toAppendTo, FieldPosition pos) {
+ return toAppendTo.append(format((Number)obj));
+ }
+
+ public Object parseObject(String source, ParsePosition pos) {
+ return df.parseObject(source, pos);
+ }
+ }
+
+ /**
+ * Format class for Excel phone number format. This class mimics Excel's
+ * built-in phone number formatting.
+ * @author James May
+ */
+ private static final class PhoneFormat extends Format {
+ public static final Format instance = new PhoneFormat();
+ private static final DecimalFormat df = createIntegerOnlyFormat("##########");
+ private PhoneFormat() {
+ // enforce singleton
+ }
+
+ /** Format a number as a phone number */
+ public static String format(Number num) {
+ String result = df.format(num);
+ StringBuffer sb = new StringBuffer();
+ String seg1, seg2, seg3;
+ int len = result.length();
+ if (len <= 4) {
+ return result;
+ }
+
+ seg3 = result.substring(len - 4, len);
+ seg2 = result.substring(Math.max(0, len - 7), len - 4);
+ seg1 = result.substring(Math.max(0, len - 10), Math.max(0, len - 7));
+
+ if(seg1 != null && seg1.trim().length() > 0) {
+ sb.append('(').append(seg1).append(") ");
+ }
+ if(seg2 != null && seg2.trim().length() > 0) {
+ sb.append(seg2).append('-');
+ }
+ sb.append(seg3);
+ return sb.toString();
+ }
+
+ public StringBuffer format(Object obj, StringBuffer toAppendTo, FieldPosition pos) {
+ return toAppendTo.append(format((Number)obj));
+ }
+
+ public Object parseObject(String source, ParsePosition pos) {
+ return df.parseObject(source, pos);
+ }
+ }
+}
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 <code>true</code> 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 @@ -167,21 +167,6 @@ public interface Sheet extends Iterable<Row> { void setDefaultRowHeightInPoints(float height); /** - * get whether gridlines are printed. - * @return true if printed - */ - - boolean isGridsPrinted(); - - /** - * set whether gridlines printed. - * @param value false if not printed. - */ - - void setGridsPrinted(boolean value); - - - /** * adds a merged region of cells (hence those cells form one) * @param region (rowfrom/colfrom-rowto/colto) to merge * @return index of this region @@ -229,20 +214,6 @@ public interface Sheet extends Iterable<Row> { Iterator<Row> rowIterator(); /** - * whether alternate expression evaluation is on - * @param b alternative expression evaluation or not - */ - - void setAlternativeExpression(boolean b); - - /** - * whether alternative formula entry is on - * @param b alternative formulas or not - */ - - void setAlternativeFormula(boolean b); - - /** * show automatic page breaks or not * @param b whether to show auto page breaks */ 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<Integer, String> numberFormats = new Hashtable<Integer,String>(); + private final Map<Integer, String> numberFormats = new LinkedHashMap<Integer,String>(); private final List<XSSFFont> fonts = new ArrayList<XSSFFont>(); private final List<XSSFCellFill> fills = new ArrayList<XSSFCellFill>(); private final List<XSSFCellBorder> borders = new ArrayList<XSSFCellBorder>(); @@ -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<Integer> 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<XSSFCellBorder> getBorders(){ return borders; } - public XSSFCellFill getFillAt(int idx) { - return fills.get(idx); - } - public List<XSSFCellFill> getFills(){ return fills; } + public List<XSSFFont> getFonts(){ + return fonts; + } + + public Map<Integer, String> 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 <code>double</code>. + * @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 <code>double</code>. + * @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 <code>null</code> 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()} * </p> - * @see org.apache.poi.xssf.usermodel.extensions.XSSFColor#getRgb() + * @see org.apache.poi.xssf.usermodel.XSSFColor#getRgb() * @return XSSFColor - fill color or <code>null</code> 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 <code>null</code> 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 <code>null</code> 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 <code>null</code> 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 <code>null</code> 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: * <pre> * cs.setFillPattern(XSSFCellStyle.FINE_DOTS ); - * cs.setFillBackgroundRgbColor(new XSSFColor(java.awt.Color.RED)); + * cs.setFillBackgroundXSSFColor(new XSSFColor(java.awt.Color.RED)); * </pre> * optionally a Foreground and background fill can be applied: * <i>Note: Ensure Foreground color is set prior to background</i> @@ -915,7 +910,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * For example: * <pre> * cs.setFillPattern(XSSFCellStyle.FINE_DOTS ); - * cs.setFillBackgroundRgbColor(IndexedColors.RED.getIndex()); + * cs.setFillBackgroundXSSFColor(IndexedColors.RED.getIndex()); * </pre> * optionally a Foreground and background fill can be applied: * <i>Note: Ensure Foreground color is set prior to background</i> @@ -946,7 +941,7 @@ public class XSSFCellStyle implements CellStyle, Cloneable { * <br/> * <i>Note: Ensure Foreground color is set prior to background color.</i> * @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. + * <p> + * Setting this flag to <code>true</code> make all content visible + * whithin a cell by displaying it on multiple lines + * </p> * * @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 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()); + } } /** @@ -361,16 +370,6 @@ public class XSSFFont implements Font { } /** - * 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 * * @param theme - theme color 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. * </p> - * @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 <code>true</code> if this sheet should display gridlines. + * @return <code>true</code> 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 <code>true</code> 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. + * <p> + * Row heading are the row numbers to the side of the sheet + * </p> + * <p> + * Column heading are the letters or numbers that appear above the columns of the sheet + * </p> * * @return <code>true</code> 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. + * <p> + * Row heading are the row numbers to the side of the sheet + * </p> + * <p> + * Column heading are the letters or numbers that appear above the columns of the sheet + * </p> + * + * @param show <code>true</code> 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<Row> 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,7 +1300,7 @@ 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()); } @@ -1233,24 +1308,6 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { } /** - * Sets the flag indicating whether this sheet should display gridlines. - * - * @param show <code>true</code> 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 <code>true</code> 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. * * @param b <code>true</code> if 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,78 +1369,14 @@ 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: - * <pre> - * 10 - 10% - * 20 - 20% - * … - * 100 - 100% - * … - * 400 - 400% - * </pre> - * - * 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: - * <pre> - * 10 - 10% - * 20 - 20% - * … - * 100 - 100% - * … - * 400 - 400% - * </pre> - * - * 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: - * <pre> - * 10 - 10% - * 20 - 20% - * … - * 100 - 100% - * … - * 400 - 400% - * </pre> - * - * 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. * Code ensures that rows don't wrap around. @@ -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). * <p> - * 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<X } } - // Load individual sheets. The order of sheets is defined by the order of CTSheet beans in the workbook + // Load individual sheets. The order of sheets is defined by the order of CTSheet elements in the workbook sheets = new ArrayList<XSSFSheet>(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<X // Process the named ranges namedRanges = new ArrayList<XSSFName>(); - 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, Iterable<X * @return the new XSSFCellStyle object */ 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=(stylesSource)._getStyleXfsSize(); - int indexXf=(stylesSource).putCellXf(xf); - XSSFCellStyle style = new XSSFCellStyle(indexXf-1, xfSize-1, stylesSource); - return style; + return stylesSource.createCellStyle(); } /** @@ -448,7 +439,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X */ public XSSFSheet createSheet(String sheetname) { if (containsSheet( sheetname, sheets.size() )) - throw new IllegalArgumentException( "The workbook already contains a sheet of this name" ); + throw new IllegalArgumentException( "The workbook already contains a sheet of this name"); CTSheet sheet = addSheet(sheetname); @@ -479,23 +470,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X * 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) { - short fontNum = getNumberOfFonts(); - for (short i = 0; i < fontNum; i++) { - XSSFFont xssfFont = getFontAt(i); - - if ( (xssfFont.getBold() == (boldWeight == XSSFFont.BOLDWEIGHT_BOLD)) - && xssfFont.getColor() == color - && xssfFont.getFontHeightInPoints() == fontHeight - && xssfFont.getFontName().equals(name) - && xssfFont.getItalic() == italic - && xssfFont.getStrikeout() == strikeout - && xssfFont.getTypeOffset() == typeOffset - && xssfFont.getUnderline() == underline) - { - return xssfFont; - } - } - return null; + return stylesSource.findFont(boldWeight, color, fontHeight, name, italic, strikeout, typeOffset, underline); } /** @@ -599,7 +574,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X * @return number of fonts */ public short getNumberOfFonts() { - return (short)(stylesSource).getNumberOfFonts(); + return (short)stylesSource.getFonts().size(); } /** @@ -617,7 +592,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X * @return number of worksheets */ public int getNumberOfSheets() { - return this.sheets.size(); + return sheets.size(); } /** @@ -640,10 +615,9 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X * @return XSSFSheet with the name provided or <code>null</code> 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<X * @return index of the sheet (0 based) or <tt>-1</tt if not found */ public int getSheetIndex(String name) { - CTSheet[] sheets = this.workbook.getSheets().getSheetArray(); - for (int i = 0 ; i < sheets.length ; ++i) { - if (name.equalsIgnoreCase(sheets[i].getName())) { + for (int i = 0 ; i < sheets.size() ; ++i) { + XSSFSheet sheet = sheets.get(i); + if (name.equalsIgnoreCase(sheet.getSheetName())) { return i; } } @@ -686,7 +660,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X */ public int getSheetIndex(Sheet sheet) { int idx = 0; - for(XSSFSheet sh : this){ + for(XSSFSheet sh : sheets){ if(sh == sheet) return idx; idx++; } @@ -701,7 +675,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X */ public String getSheetName(int sheetIx) { validateSheetIndex(sheetIx); - return this.workbook.getSheets().getSheetArray(sheetIx).getName(); + return sheets.get(sheetIx).getSheetName(); } /** @@ -981,11 +955,11 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X * Generates a NameRecord to represent a built-in region * * @return a new NameRecord + * @throws IllegalArgumentException if sheetNumber is invalid + * @throws POIXMLException if such a name already exists in the workbook */ private XSSFName createBuiltInName(String builtInName, int sheetNumber) { - if (sheetNumber < 0 || sheetNumber + 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<X XSSFName name = new XSSFName(nameRecord, this); for (XSSFName nr : namedRanges) { if (nr.equals(name)) - throw new RuntimeException("Builtin (" + builtInName + throw new POIXMLException("Builtin (" + builtInName + ") already exists for sheet (" + sheetNumber + ")"); } @@ -1006,8 +980,8 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X * We only set one sheet as selected for compatibility with HSSF. */ public void setSelectedTab(short index) { - for (int i = 0 ; i < this.sheets.size() ; ++i) { - XSSFSheet sheet = this.sheets.get(i); + for (int i = 0 ; i < sheets.size() ; ++i) { + XSSFSheet sheet = sheets.get(i); sheet.setSelected(i == index); } } @@ -1025,7 +999,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X validateSheetName(name); if (containsSheet(name, sheet )) throw new IllegalArgumentException( "The workbook already contains a sheet of this name" ); - this.workbook.getSheets().getSheetArray(sheet).setName(name); + workbook.getSheets().getSheetArray(sheet).setName(name); } /** @@ -1038,9 +1012,9 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X int idx = getSheetIndex(sheetname); sheets.add(pos, sheets.remove(idx)); // Reorder CTSheets - XmlObject cts = this.workbook.getSheets().getSheetArray(idx).copy(); - this.workbook.getSheets().removeSheet(idx); - CTSheet newcts = this.workbook.getSheets().insertNewSheet(pos); + XmlObject cts = workbook.getSheets().getSheetArray(idx).copy(); + workbook.getSheets().removeSheet(idx); + CTSheet newcts = workbook.getSheets().insertNewSheet(pos); newcts.set(cts); } @@ -1218,4 +1192,63 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X } return embedds; } + + /** + * 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 + * @throws IllegalArgumentException if sheetIx is invalid + */ + public boolean isSheetHidden(int sheetIx) { + validateSheetIndex(sheetIx); + CTSheet ctSheet = sheets.get(sheetIx).sheet; + return ctSheet.getState() == STSheetState.HIDDEN; + } + + /** + * 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 + * @throws IllegalArgumentException if sheetIx is invalid + */ + public boolean isSheetVeryHidden(int sheetIx) { + validateSheetIndex(sheetIx); + CTSheet ctSheet = sheets.get(sheetIx).sheet; + return ctSheet.getState() == STSheetState.VERY_HIDDEN; + } + + /** + * Hide or unhide a sheet + * + * @param sheetIx The sheet index + * @param hidden True to mark the sheet as hidden, false otherwise + * @throws IllegalArgumentException if sheetIx is invalid + */ + public void setSheetHidden(int sheetIx, boolean hidden) { + validateSheetIndex(sheetIx); + CTSheet ctSheet = sheets.get(sheetIx).sheet; + ctSheet.setState(hidden ? STSheetState.HIDDEN : STSheetState.VISIBLE); + } + + /** + * Hide or unhide a sheet. + * <pre> + * 0 = not hidden + * 1 = hidden + * 2 = very hidden. + * </pre> + * + * @param sheetIx The sheet number + * @param hidden 0 for not hidden, 1 for hidden, 2 for very hidden + * @throws IllegalArgumentException if sheetIx is invalid + */ + public void setSheetHidden(int sheetIx, int hidden) { + validateSheetIndex(sheetIx); + CTSheet ctSheet = sheets.get(sheetIx).sheet; + ctSheet.setState(STSheetState.Enum.forInt(hidden)); + } + } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellBorder.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellBorder.java index b3eeb0e365..91cd0ab27c 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellBorder.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellBorder.java @@ -18,6 +18,7 @@ package org.apache.poi.xssf.usermodel.extensions; import org.apache.poi.ss.usermodel.BorderStyle; +import org.apache.poi.xssf.usermodel.XSSFColor; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorder; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorderPr; import org.openxmlformats.schemas.spreadsheetml.x2006.main.STBorderStyle; diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellFill.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellFill.java index 58c26c5ff1..e9d3834425 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellFill.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellFill.java @@ -20,6 +20,7 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTColor; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFill; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPatternFill; import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPatternType; +import org.apache.poi.xssf.usermodel.XSSFColor; /** * This element specifies fill formatting. diff --git a/src/ooxml/testcases/org/apache/poi/xssf/eventusermodel/TestXSSFReader.java b/src/ooxml/testcases/org/apache/poi/xssf/eventusermodel/TestXSSFReader.java index 5a552df4f6..f2eccca857 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/eventusermodel/TestXSSFReader.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/eventusermodel/TestXSSFReader.java @@ -67,7 +67,7 @@ public class TestXSSFReader extends TestCase { XSSFReader r = new XSSFReader(pkg); - assertEquals(3, r.getStylesTable()._getFontsSize()); + assertEquals(3, r.getStylesTable().getFonts().size()); assertEquals(0, r.getStylesTable()._getNumberFormatSize()); } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/model/TestStylesTable.java b/src/ooxml/testcases/org/apache/poi/xssf/model/TestStylesTable.java index fc16beaf77..7aebf26385 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/model/TestStylesTable.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/model/TestStylesTable.java @@ -17,8 +17,6 @@ package org.apache.poi.xssf.model; -import java.io.ByteArrayInputStream; -import java.io.ByteArrayOutputStream; import java.io.File; import org.apache.poi.xssf.usermodel.XSSFCellStyle; @@ -42,7 +40,7 @@ public class TestStylesTable extends TestCase { StylesTable st = new StylesTable(); // Check defaults - assertNotNull(st._getRawStylesheet()); + assertNotNull(st.getCTStylesheet()); assertEquals(1, st._getXfsSize()); assertEquals(1, st._getStyleXfsSize()); assertEquals(0, st._getNumberFormatSize()); @@ -52,14 +50,14 @@ public class TestStylesTable extends TestCase { XSSFWorkbook wb = new XSSFWorkbook(); StylesTable st = wb.getStylesSource(); - assertNotNull(st._getRawStylesheet()); + assertNotNull(st.getCTStylesheet()); assertEquals(1, st._getXfsSize()); assertEquals(1, st._getStyleXfsSize()); assertEquals(0, st._getNumberFormatSize()); st = XSSFTestDataSamples.writeOutAndReadBack(wb).getStylesSource(); - assertNotNull(st._getRawStylesheet()); + assertNotNull(st.getCTStylesheet()); assertEquals(1, st._getXfsSize()); assertEquals(1, st._getStyleXfsSize()); assertEquals(0, st._getNumberFormatSize()); @@ -85,14 +83,14 @@ public class TestStylesTable extends TestCase { } public void doTestExisting(StylesTable st) throws Exception { // Check contents - assertNotNull(st._getRawStylesheet()); + assertNotNull(st.getCTStylesheet()); assertEquals(11, st._getXfsSize()); assertEquals(1, st._getStyleXfsSize()); assertEquals(8, st._getNumberFormatSize()); - assertEquals(2, st._getFontsSize()); - assertEquals(2, st._getFillsSize()); - assertEquals(1, st._getBordersSize()); + assertEquals(2, st.getFonts().size()); + assertEquals(2, st.getFills().size()); + assertEquals(1, st.getBorders().size()); assertEquals("yyyy/mm/dd", st.getNumberFormatAt(165)); assertEquals("yy/mm/dd", st.getNumberFormatAt(167)); @@ -113,7 +111,7 @@ public class TestStylesTable extends TestCase { XSSFWorkbook wb = new XSSFWorkbook(); StylesTable st = wb.getStylesSource(); - assertNotNull(st._getRawStylesheet()); + assertNotNull(st.getCTStylesheet()); assertEquals(1, st._getXfsSize()); assertEquals(1, st._getStyleXfsSize()); assertEquals(0, st._getNumberFormatSize()); @@ -127,7 +125,7 @@ public class TestStylesTable extends TestCase { // Save and re-load st = XSSFTestDataSamples.writeOutAndReadBack(wb).getStylesSource(); - assertNotNull(st._getRawStylesheet()); + assertNotNull(st.getCTStylesheet()); assertEquals(2, st._getXfsSize()); assertEquals(1, st._getStyleXfsSize()); assertEquals(2, st._getNumberFormatSize()); diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetHiding.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetHiding.java new file mode 100755 index 0000000000..6bd3d40967 --- /dev/null +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetHiding.java @@ -0,0 +1,99 @@ +/* ====================================================================
+ 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;
+
+import junit.framework.TestCase;
+import org.apache.poi.xssf.XSSFTestDataSamples;
+
+/**
+ * @author Yegor Kozlov
+ */
+public class TestSheetHiding extends TestCase {
+ private XSSFWorkbook wbH;
+ private XSSFWorkbook wbU;
+
+ protected void setUp() {
+ wbH = XSSFTestDataSamples.openSampleWorkbook("TwoSheetsOneHidden.xlsx");
+ wbU = XSSFTestDataSamples.openSampleWorkbook("TwoSheetsNoneHidden.xlsx");
+ }
+
+ /**
+ * Test that we get the right number of sheets,
+ * with the right text on them, no matter what
+ * the hidden flags are
+ */
+ public void testTextSheets() throws Exception {
+ // Both should have two sheets
+ assertEquals(2, wbH.getNumberOfSheets());
+ assertEquals(2, wbU.getNumberOfSheets());
+
+ // All sheets should have one row
+ assertEquals(0, wbH.getSheetAt(0).getLastRowNum());
+ assertEquals(0, wbH.getSheetAt(1).getLastRowNum());
+ assertEquals(0, wbU.getSheetAt(0).getLastRowNum());
+ assertEquals(0, wbU.getSheetAt(1).getLastRowNum());
+
+ // All rows should have one column
+ assertEquals(1, wbH.getSheetAt(0).getRow(0).getLastCellNum());
+ assertEquals(1, wbH.getSheetAt(1).getRow(0).getLastCellNum());
+ assertEquals(1, wbU.getSheetAt(0).getRow(0).getLastCellNum());
+ assertEquals(1, wbU.getSheetAt(1).getRow(0).getLastCellNum());
+
+ // Text should be sheet based
+ assertEquals("Sheet1A1", wbH.getSheetAt(0).getRow(0).getCell(0).getRichStringCellValue().getString());
+ assertEquals("Sheet2A1", wbH.getSheetAt(1).getRow(0).getCell(0).getRichStringCellValue().getString());
+ assertEquals("Sheet1A1", wbU.getSheetAt(0).getRow(0).getCell(0).getRichStringCellValue().getString());
+ assertEquals("Sheet2A1", wbU.getSheetAt(1).getRow(0).getCell(0).getRichStringCellValue().getString());
+ }
+
+ /**
+ * Check that we can get and set the hidden flags
+ * as expected
+ */
+ public void testHideUnHideFlags() throws Exception {
+ assertTrue(wbH.isSheetHidden(0));
+ assertFalse(wbH.isSheetHidden(1));
+ assertFalse(wbU.isSheetHidden(0));
+ assertFalse(wbU.isSheetHidden(1));
+ }
+
+ /**
+ * Turn the sheet with none hidden into the one with
+ * one hidden
+ */
+ public void testHide() throws Exception {
+ wbU.setSheetHidden(0, true);
+ assertTrue(wbU.isSheetHidden(0));
+ assertFalse(wbU.isSheetHidden(1));
+ XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wbU);
+ assertTrue(wb2.isSheetHidden(0));
+ assertFalse(wb2.isSheetHidden(1));
+ }
+
+ /**
+ * Turn the sheet with one hidden into the one with
+ * none hidden
+ */
+ public void testUnHide() throws Exception {
+ wbH.setSheetHidden(0, false);
+ assertFalse(wbH.isSheetHidden(0));
+ assertFalse(wbH.isSheetHidden(1));
+ XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wbH);
+ assertFalse(wb2.isSheetHidden(0));
+ assertFalse(wb2.isSheetHidden(1));
+ }
+}
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCellStyle.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCellStyle.java index ca9abd2d17..0e7b8be807 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCellStyle.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCellStyle.java @@ -26,7 +26,6 @@ import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.xssf.model.StylesTable; 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.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; @@ -48,7 +47,7 @@ public class TestXSSFCellStyle extends TestCase { protected void setUp() { stylesTable = new StylesTable(); - ctStylesheet = stylesTable._getRawStylesheet(); + ctStylesheet = stylesTable.getCTStylesheet(); ctBorderA = CTBorder.Factory.newInstance(); XSSFCellBorder borderA = new XSSFCellBorder(ctBorderA); @@ -223,7 +222,7 @@ public class TestXSSFCellStyle extends TestCase { public void testGetSetBottomBorderColor() { //defaults assertEquals(IndexedColors.BLACK.getIndex(), cellStyle.getBottomBorderColor()); - assertNull(cellStyle.getBottomBorderRgbColor()); + assertNull(cellStyle.getBottomBorderXSSFColor()); int num = stylesTable.getBorders().size(); @@ -232,7 +231,7 @@ public class TestXSSFCellStyle extends TestCase { //setting indexed color cellStyle.setBottomBorderColor(IndexedColors.BLUE_GREY.getIndex()); assertEquals(IndexedColors.BLUE_GREY.getIndex(), cellStyle.getBottomBorderColor()); - clr = cellStyle.getBottomBorderRgbColor(); + clr = cellStyle.getBottomBorderXSSFColor(); assertTrue(clr.getCTColor().isSetIndexed()); assertEquals(IndexedColors.BLUE_GREY.getIndex(), clr.getIndexed()); //a new border was added to the styles table @@ -249,21 +248,21 @@ public class TestXSSFCellStyle extends TestCase { num = stylesTable.getBorders().size(); clr = new XSSFColor(java.awt.Color.CYAN); cellStyle.setBottomBorderColor(clr); - assertEquals(clr.getCTColor().toString(), cellStyle.getBottomBorderRgbColor().getCTColor().toString()); - byte[] rgb = cellStyle.getBottomBorderRgbColor().getRgb(); + assertEquals(clr.getCTColor().toString(), cellStyle.getBottomBorderXSSFColor().getCTColor().toString()); + byte[] rgb = cellStyle.getBottomBorderXSSFColor().getRgb(); assertEquals(java.awt.Color.CYAN, new java.awt.Color(rgb[0] & 0xFF, rgb[1] & 0xFF, rgb[2] & 0xFF)); //another border was added to the styles table assertEquals(num + 1, stylesTable.getBorders().size()); //passing null unsets the color cellStyle.setBottomBorderColor(null); - assertNull(cellStyle.getBottomBorderRgbColor()); + assertNull(cellStyle.getBottomBorderXSSFColor()); } public void testGetSetTopBorderColor() { //defaults assertEquals(IndexedColors.BLACK.getIndex(), cellStyle.getTopBorderColor()); - assertNull(cellStyle.getTopBorderRgbColor()); + assertNull(cellStyle.getTopBorderXSSFColor()); int num = stylesTable.getBorders().size(); @@ -272,7 +271,7 @@ public class TestXSSFCellStyle extends TestCase { //setting indexed color cellStyle.setTopBorderColor(IndexedColors.BLUE_GREY.getIndex()); assertEquals(IndexedColors.BLUE_GREY.getIndex(), cellStyle.getTopBorderColor()); - clr = cellStyle.getTopBorderRgbColor(); + clr = cellStyle.getTopBorderXSSFColor(); assertTrue(clr.getCTColor().isSetIndexed()); assertEquals(IndexedColors.BLUE_GREY.getIndex(), clr.getIndexed()); //a new border was added to the styles table @@ -289,21 +288,21 @@ public class TestXSSFCellStyle extends TestCase { num = stylesTable.getBorders().size(); clr = new XSSFColor(java.awt.Color.CYAN); cellStyle.setTopBorderColor(clr); - assertEquals(clr.getCTColor().toString(), cellStyle.getTopBorderRgbColor().getCTColor().toString()); - byte[] rgb = cellStyle.getTopBorderRgbColor().getRgb(); + assertEquals(clr.getCTColor().toString(), cellStyle.getTopBorderXSSFColor().getCTColor().toString()); + byte[] rgb = cellStyle.getTopBorderXSSFColor().getRgb(); assertEquals(java.awt.Color.CYAN, new java.awt.Color(rgb[0] & 0xFF, rgb[1] & 0xFF, rgb[2] & 0xFF)); //another border was added to the styles table assertEquals(num + 1, stylesTable.getBorders().size()); //passing null unsets the color cellStyle.setTopBorderColor(null); - assertNull(cellStyle.getTopBorderRgbColor()); + assertNull(cellStyle.getTopBorderXSSFColor()); } public void testGetSetLeftBorderColor() { //defaults assertEquals(IndexedColors.BLACK.getIndex(), cellStyle.getLeftBorderColor()); - assertNull(cellStyle.getLeftBorderRgbColor()); + assertNull(cellStyle.getLeftBorderXSSFColor()); int num = stylesTable.getBorders().size(); @@ -312,7 +311,7 @@ public class TestXSSFCellStyle extends TestCase { //setting indexed color cellStyle.setLeftBorderColor(IndexedColors.BLUE_GREY.getIndex()); assertEquals(IndexedColors.BLUE_GREY.getIndex(), cellStyle.getLeftBorderColor()); - clr = cellStyle.getLeftBorderRgbColor(); + clr = cellStyle.getLeftBorderXSSFColor(); assertTrue(clr.getCTColor().isSetIndexed()); assertEquals(IndexedColors.BLUE_GREY.getIndex(), clr.getIndexed()); //a new border was added to the styles table @@ -329,21 +328,21 @@ public class TestXSSFCellStyle extends TestCase { num = stylesTable.getBorders().size(); clr = new XSSFColor(java.awt.Color.CYAN); cellStyle.setLeftBorderColor(clr); - assertEquals(clr.getCTColor().toString(), cellStyle.getLeftBorderRgbColor().getCTColor().toString()); - byte[] rgb = cellStyle.getLeftBorderRgbColor().getRgb(); + assertEquals(clr.getCTColor().toString(), cellStyle.getLeftBorderXSSFColor().getCTColor().toString()); + byte[] rgb = cellStyle.getLeftBorderXSSFColor().getRgb(); assertEquals(java.awt.Color.CYAN, new java.awt.Color(rgb[0] & 0xFF, rgb[1] & 0xFF, rgb[2] & 0xFF)); //another border was added to the styles table assertEquals(num + 1, stylesTable.getBorders().size()); //passing null unsets the color cellStyle.setLeftBorderColor(null); - assertNull(cellStyle.getLeftBorderRgbColor()); + assertNull(cellStyle.getLeftBorderXSSFColor()); } public void testGetSetRightBorderColor() { //defaults assertEquals(IndexedColors.BLACK.getIndex(), cellStyle.getRightBorderColor()); - assertNull(cellStyle.getRightBorderRgbColor()); + assertNull(cellStyle.getRightBorderXSSFColor()); int num = stylesTable.getBorders().size(); @@ -352,7 +351,7 @@ public class TestXSSFCellStyle extends TestCase { //setting indexed color cellStyle.setRightBorderColor(IndexedColors.BLUE_GREY.getIndex()); assertEquals(IndexedColors.BLUE_GREY.getIndex(), cellStyle.getRightBorderColor()); - clr = cellStyle.getRightBorderRgbColor(); + clr = cellStyle.getRightBorderXSSFColor(); assertTrue(clr.getCTColor().isSetIndexed()); assertEquals(IndexedColors.BLUE_GREY.getIndex(), clr.getIndexed()); //a new border was added to the styles table @@ -369,21 +368,21 @@ public class TestXSSFCellStyle extends TestCase { num = stylesTable.getBorders().size(); clr = new XSSFColor(java.awt.Color.CYAN); cellStyle.setRightBorderColor(clr); - assertEquals(clr.getCTColor().toString(), cellStyle.getRightBorderRgbColor().getCTColor().toString()); - byte[] rgb = cellStyle.getRightBorderRgbColor().getRgb(); + assertEquals(clr.getCTColor().toString(), cellStyle.getRightBorderXSSFColor().getCTColor().toString()); + byte[] rgb = cellStyle.getRightBorderXSSFColor().getRgb(); assertEquals(java.awt.Color.CYAN, new java.awt.Color(rgb[0] & 0xFF, rgb[1] & 0xFF, rgb[2] & 0xFF)); //another border was added to the styles table assertEquals(num + 1, stylesTable.getBorders().size()); //passing null unsets the color cellStyle.setRightBorderColor(null); - assertNull(cellStyle.getRightBorderRgbColor()); + assertNull(cellStyle.getRightBorderXSSFColor()); } public void testGetSetFillBackgroundColor() { assertEquals(IndexedColors.AUTOMATIC.getIndex(), cellStyle.getFillBackgroundColor()); - assertNull(cellStyle.getFillBackgroundRgbColor()); + assertNull(cellStyle.getFillBackgroundXSSFColor()); XSSFColor clr; @@ -392,7 +391,7 @@ public class TestXSSFCellStyle extends TestCase { //setting indexed color cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex()); assertEquals(IndexedColors.RED.getIndex(), cellStyle.getFillBackgroundColor()); - clr = cellStyle.getFillBackgroundRgbColor(); + clr = cellStyle.getFillBackgroundXSSFColor(); assertTrue(clr.getCTColor().isSetIndexed()); assertEquals(IndexedColors.RED.getIndex(), clr.getIndexed()); //a new fill was added to the styles table @@ -409,15 +408,15 @@ public class TestXSSFCellStyle extends TestCase { num = stylesTable.getFills().size(); clr = new XSSFColor(java.awt.Color.CYAN); cellStyle.setFillBackgroundColor(clr); - assertEquals(clr.getCTColor().toString(), cellStyle.getFillBackgroundRgbColor().getCTColor().toString()); - byte[] rgb = cellStyle.getFillBackgroundRgbColor().getRgb(); + assertEquals(clr.getCTColor().toString(), cellStyle.getFillBackgroundXSSFColor().getCTColor().toString()); + byte[] rgb = cellStyle.getFillBackgroundXSSFColor().getRgb(); assertEquals(java.awt.Color.CYAN, new java.awt.Color(rgb[0] & 0xFF, rgb[1] & 0xFF, rgb[2] & 0xFF)); //another border was added to the styles table assertEquals(num + 1, stylesTable.getFills().size()); //passing null unsets the color cellStyle.setFillBackgroundColor(null); - assertNull(cellStyle.getFillBackgroundRgbColor()); + assertNull(cellStyle.getFillBackgroundXSSFColor()); assertEquals(IndexedColors.AUTOMATIC.getIndex(), cellStyle.getFillBackgroundColor()); } @@ -427,7 +426,7 @@ public class TestXSSFCellStyle extends TestCase { XSSFCellStyle style1 = wb1.createCellStyle(); assertEquals(IndexedColors.AUTOMATIC.getIndex(), style1.getFillBackgroundColor()); - assertNull(style1.getFillBackgroundRgbColor()); + assertNull(style1.getFillBackgroundXSSFColor()); //compatibility with HSSF HSSFWorkbook wb2 = new HSSFWorkbook(); @@ -457,7 +456,7 @@ public class TestXSSFCellStyle extends TestCase { XSSFCellStyle defaultStyle = wb.getCellStyleAt((short)0); assertEquals(IndexedColors.AUTOMATIC.getIndex(), defaultStyle.getFillForegroundColor()); - assertEquals(null, defaultStyle.getFillForegroundRgbColor()); + assertEquals(null, defaultStyle.getFillForegroundXSSFColor()); assertEquals(CellStyle.NO_FILL, defaultStyle.getFillPattern()); XSSFCellStyle customStyle = wb.createCellStyle(); diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFont.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFont.java index 3a60bd1907..edfc97f596 100755 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFont.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFont.java @@ -4,7 +4,7 @@ import junit.framework.TestCase; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.XSSFTestDataSamples; -import org.apache.poi.xssf.usermodel.extensions.XSSFColor; +import org.apache.poi.xssf.usermodel.XSSFColor; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBooleanProperty; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTColor; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFont; @@ -160,14 +160,14 @@ public final class TestXSSFFont extends TestCase{ ctFont.setColorArray(0,color); XSSFFont xssfFont=new XSSFFont(ctFont); - assertEquals(ctFont.getColorArray(0).getRgb()[0],xssfFont.getRgbColor().getRgb()[0]); - assertEquals(ctFont.getColorArray(0).getRgb()[1],xssfFont.getRgbColor().getRgb()[1]); - assertEquals(ctFont.getColorArray(0).getRgb()[2],xssfFont.getRgbColor().getRgb()[2]); - assertEquals(ctFont.getColorArray(0).getRgb()[3],xssfFont.getRgbColor().getRgb()[3]); + assertEquals(ctFont.getColorArray(0).getRgb()[0],xssfFont.getXSSFColor().getRgb()[0]); + assertEquals(ctFont.getColorArray(0).getRgb()[1],xssfFont.getXSSFColor().getRgb()[1]); + assertEquals(ctFont.getColorArray(0).getRgb()[2],xssfFont.getXSSFColor().getRgb()[2]); + assertEquals(ctFont.getColorArray(0).getRgb()[3],xssfFont.getXSSFColor().getRgb()[3]); color.setRgb(Integer.toHexString(0xF1F1F1).getBytes()); XSSFColor newColor=new XSSFColor(color); - xssfFont.setRgbColor(newColor); + xssfFont.setColor(newColor); assertEquals(ctFont.getColorArray(0).getRgb()[2],newColor.getRgb()[2]); } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java index 1ba9064e91..e5267688a2 100755 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFName.java @@ -54,14 +54,6 @@ public class TestXSSFName extends TestCase { assertEquals("'Testing Named Ranges'!$A$1:$B$1", name1.getReference());
assertEquals("Testing Named Ranges", name1.getSheetName());
- //setting invalid reference should throw IllegalArgumentException
- try {
- name1.setReference("invalid");
- fail("expected exception");
- } catch (IllegalArgumentException e){
- ;
- }
-
assertEquals(-1, name1.getLocalSheetId());
name1.setLocalSheetId(1);
assertEquals(1, name1.getLocalSheetId());
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java index 8144a38984..62d2b7d989 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java @@ -24,7 +24,6 @@ import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; -import org.apache.poi.ss.util.Region; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.model.CommentsTable; import org.apache.poi.xssf.model.StylesTable; @@ -616,7 +615,7 @@ public class TestXSSFSheet extends TestCase { XSSFComment comment = sheet.createComment(); Cell cell = sheet.createRow(0).createCell((short)0); - CommentsTable comments = (CommentsTable)sheet.getCommentsSourceIfExists(); + CommentsTable comments = sheet.getCommentsTable(); CTComments ctComments = comments.getCTComments(); sheet.setCellComment("A1", comment); @@ -710,9 +709,6 @@ public class TestXSSFSheet extends TestCase { sheet.setDefaultColumnStyle((short) 3, cellStyle); assertEquals(1, ctWorksheet.getColsArray(0).getColArray(0).getStyle()); - XSSFRow row = sheet.createRow(0); - XSSFCell cell = sheet.getRow(0).createCell(3); - } @@ -772,7 +768,7 @@ public class TestXSSFSheet extends TestCase { assertEquals(7,cols.sizeOfColArray()); colArray=cols.getColArray(); assertEquals(3, colArray[1].getOutlineLevel()); - assertEquals(3,sheet.getSheetTypeSheetFormatPr().getOutlineLevelCol()); + assertEquals(3,sheet.getCTWorksheet().getSheetFormatPr().getOutlineLevelCol()); sheet.ungroupColumn((short)8,(short) 10); colArray=cols.getColArray(); @@ -782,7 +778,7 @@ public class TestXSSFSheet extends TestCase { sheet.ungroupColumn((short)2,(short)2); colArray=cols.getColArray(); assertEquals(4, colArray.length); - assertEquals(2,sheet.getSheetTypeSheetFormatPr().getOutlineLevelCol()); + assertEquals(2,sheet.getCTWorksheet().getSheetFormatPr().getOutlineLevelCol()); } @@ -798,7 +794,7 @@ public class TestXSSFSheet extends TestCase { assertNotNull(ctrow); assertEquals(9,ctrow.getR()); assertEquals(1, ctrow.getOutlineLevel()); - assertEquals(1,sheet.getSheetTypeSheetFormatPr().getOutlineLevelRow()); + assertEquals(1,sheet.getCTWorksheet().getSheetFormatPr().getOutlineLevelRow()); //two level sheet.groupRow(10,13); @@ -807,26 +803,37 @@ public class TestXSSFSheet extends TestCase { assertNotNull(ctrow); assertEquals(10,ctrow.getR()); assertEquals(2, ctrow.getOutlineLevel()); - assertEquals(2,sheet.getSheetTypeSheetFormatPr().getOutlineLevelRow()); + assertEquals(2,sheet.getCTWorksheet().getSheetFormatPr().getOutlineLevelRow()); sheet.ungroupRow(8, 10); assertEquals(4,sheet.getPhysicalNumberOfRows()); - assertEquals(1,sheet.getSheetTypeSheetFormatPr().getOutlineLevelRow()); + assertEquals(1,sheet.getCTWorksheet().getSheetFormatPr().getOutlineLevelRow()); sheet.ungroupRow(10,10); assertEquals(3,sheet.getPhysicalNumberOfRows()); - assertEquals(1,sheet.getSheetTypeSheetFormatPr().getOutlineLevelRow()); + assertEquals(1,sheet.getCTWorksheet().getSheetFormatPr().getOutlineLevelRow()); } - public void testSetZoom() { - Workbook workBook = new XSSFWorkbook(); - XSSFSheet sheet1 = (XSSFSheet) workBook.createSheet("new sheet"); - sheet1.setZoom(3,4); // 75 percent magnification - long zoom = sheet1.getSheetTypeSheetView().getZoomScale(); - assertEquals(zoom, 75); - } + public void testSetZoom() { + XSSFWorkbook workBook = new XSSFWorkbook(); + XSSFSheet sheet1 = workBook.createSheet("new sheet"); + sheet1.setZoom(3,4); // 75 percent magnification + long zoom = sheet1.getCTWorksheet().getSheetViews().getSheetViewArray(0).getZoomScale(); + assertEquals(zoom, 75); + + sheet1.setZoom(200); + zoom = sheet1.getCTWorksheet().getSheetViews().getSheetViewArray(0).getZoomScale(); + assertEquals(zoom, 200); + + try { + sheet1.setZoom(500); + fail("Expecting exception"); + } catch (IllegalArgumentException e){ + assertEquals("Valid scale values range from 10 to 400", e.getMessage()); + } + } public void testOutlineProperties() { XSSFWorkbook wb = new XSSFWorkbook(); diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java index 3628d99566..b5694adf71 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java @@ -326,8 +326,8 @@ public final class TestXSSFWorkbook extends TestCase { //get custom style StylesTable styleSource = workbook.getStylesSource(); - CellStyle customStyle = new XSSFCellStyle(styleSource); - Font font = new XSSFFont(); + XSSFCellStyle customStyle = new XSSFCellStyle(styleSource); + XSSFFont font = new XSSFFont(); font.setFontName("Verdana"); customStyle.setFont(font); int x = styleSource.putStyle(customStyle); @@ -344,7 +344,7 @@ public final class TestXSSFWorkbook extends TestCase { assertNotNull(fontAt); //get customized font - Font customFont = new XSSFFont(); + XSSFFont customFont = new XSSFFont(); customFont.setItalic(true); int x = styleSource.putFont(customFont); fontAt = workbook.getFontAt((short)x); @@ -430,11 +430,11 @@ public final class TestXSSFWorkbook extends TestCase { // Has 8 number formats assertEquals(8, st._getNumberFormatSize()); // Has 2 fonts - assertEquals(2, st._getFontsSize()); + assertEquals(2, st.getFonts().size()); // Has 2 fills - assertEquals(2, st._getFillsSize()); + assertEquals(2, st.getFills().size()); // Has 1 border - assertEquals(1, st._getBordersSize()); + assertEquals(1, st.getBorders().size()); // Add two more styles assertEquals(StylesTable.FIRST_CUSTOM_STYLE_ID + 8, @@ -453,9 +453,9 @@ public final class TestXSSFWorkbook extends TestCase { assertNotNull(ss); assertEquals(10, st._getNumberFormatSize()); - assertEquals(2, st._getFontsSize()); - assertEquals(2, st._getFillsSize()); - assertEquals(1, st._getBordersSize()); + assertEquals(2, st.getFonts().size()); + assertEquals(2, st.getFills().size()); + assertEquals(1, st.getBorders().size()); } public void testNamedRanges() { diff --git a/src/testcases/org/apache/poi/hssf/data/TwoSheetsNoneHidden.xlsx b/src/testcases/org/apache/poi/hssf/data/TwoSheetsNoneHidden.xlsx Binary files differnew file mode 100755 index 0000000000..46f61473a5 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/data/TwoSheetsNoneHidden.xlsx diff --git a/src/testcases/org/apache/poi/hssf/data/TwoSheetsOneHidden.xlsx b/src/testcases/org/apache/poi/hssf/data/TwoSheetsOneHidden.xlsx Binary files differnew file mode 100755 index 0000000000..c92febb0a7 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/data/TwoSheetsOneHidden.xlsx |