com.acesoft.aceoffix
Class ExcelResSheet

java.lang.Object
  extended by com.acesoft.aceoffix.ExcelResSheet

public class ExcelResSheet
extends java.lang.Object

Represents a worksheet. Fills data into worksheet and controls behavior of worksheet. You can only use the ExcelResponse.openSheet method to obtain a ExcelResSheet object.

Version:
3.0
Author:
Acesoft Corporation

Method Summary
 java.lang.String getName()
          Gets the name of the worksheet.
 ExcelResCell openCell(java.lang.String cellAddress)
          Opens the specified cell and returns a ExcelResCell object.
 ExcelResCell openCellRC(int row, int col)
          Opens the specified cell and returns a ExcelResCell object.
 ExcelResTableRange openTableRange(java.lang.String rangeAddress)
          Opens the specified range of cells and returns a ExcelResTableRange object.
 ExcelResTableRange openTableRange(java.lang.String rangeAddress, boolean autoIncrease)
          Opens the specified range of cells and returns a ExcelResTableRange object.
 
Methods inherited from class java.lang.Object
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Method Detail

getName

public java.lang.String getName()
                         throws java.io.IOException
Gets the name of the worksheet.

Throws:
java.io.IOException

openCell

public ExcelResCell openCell(java.lang.String cellAddress)
                      throws java.lang.Exception
Opens the specified cell and returns a ExcelResCell object.

If the specified cell exists, this method will return a ExcelResCell object.

The following code example shows how to use the openCell method.

 ExcelResponse er = new ExcelResponse();
 ExcelResSheet sheet1 = er.openSheet("sheet1");
 
 // Output data into the cell and set the color of the cell.
 sheet1.openCell("C2").setValue("Hello");
 sheet1.openCell("C2")setBackColor(Color.red);
 sheet1.openCell("C2")setForeColor(Color.yellow);
 
 sheet1.openCellRC(2, 4).setValue("World");
 
 sheet1.openCell("B5").setValue("6");
 sheet1.openCell("C5").setValue("3");
 sheet1.openCell("D5").setFormula("B5+C5");
 
 aceCtrl1.setServerPage("../aceserver.do"); //Required
 aceCtrl1.bind(er);
 aceCtrl1.openDocument("doc/test.xls", OpenModeType.xlsReadOnly, "John Scott");
 aceCtrl1.setTagId("AceoffixCtrl1"); //Required
 

Parameters:
cellAddress - The CellAddress represents a single cell and it must use A1-style notation.

The A1-style notation is defined in Microsoft Excel. For example: the B5 cell is the cell whose row index is 5 and column index is 2.

Note: There is a limit that the maximum number of columns per worksheet is 256 and the maximum number of rows is 65,536 in Excel2003 and earlier versions.

Throws:
java.lang.Exception

openCellRC

public ExcelResCell openCellRC(int row,
                               int col)
                        throws java.lang.Exception
Opens the specified cell and returns a ExcelResCell object.

If the specified cell exists, this method will return a ExcelResCell object. Note: There is a limit that the maximum number of columns per worksheet is 256 and the maximum number of rows is 65,536 in Excel2003 and earlier versions.

Parameters:
row - The index of row.
col - The index of column.
Throws:
java.lang.Exception

openTableRange

public ExcelResTableRange openTableRange(java.lang.String rangeAddress)
                                  throws java.lang.Exception
Opens the specified range of cells and returns a ExcelResTableRange object.

In Excel worksheet, developer can define any range of cells containing one contiguous block of cells as a TableRange object. You should use TableRange object if you want to operate a range of cells.

If the number of data rows which are filled is out of range specified by RangeAddress, the TableRange will automatically expand the number of rows and apply the format of range specified by RangeAddress to new rows. If you do not want the TableRange to expand the number of rows, you should call ExcelResSheet.openTableRange method.

Code example 1: The following code example shows how to use the openTableRange method to fill data into an empty table which only has grid lines.

 ExcelResponse er = new ExcelResponse();
 ExcelResSheet sheet1 = er.openSheet("sheet1");
 ExcelResTableRange tableRange1 = sheet1.openTableRange("B5:F10");
 
 // Output data into the table.
 Random r = new Random();
 for (int j = 0; j < 4; j++){
        for (int i = 0; i < tableRange1.getDataFields().size(); i++){
                tableRange1.getDataFields().get(i).setValue(String.valueOf(r.nextInt(100))); // You can set the data from the database.
        }
        tableRange1.nextRow();
 }
 tableRange1.close(); // Required.
 
 aceCtrl1.setServerPage("../aceserver.do"); //Required
 aceCtrl1.bind(er);
 aceCtrl1.openDocument("doc/test.xls", OpenModeType.xlsReadOnly, "John Scott");
 aceCtrl1.setTagId("AceoffixCtrl1"); //Required
 

Code example 2: Draw a table on an empty worksheet and fill data into the table. The code example also shows how to alert user attention to the out-of-range data.

 ExcelResponse er = new ExcelResponse();
 ExcelResSheet sheet1 = er.openSheet("sheet1");
 ExcelResTableRange tableRange1 = sheet1.openTableRange("B5:F10");
 
 // Draw a table with full grid lines and set the color of the table.
 tableRange1.setRowHeight(20);
 tableRange1.setColumnWidth(14.25);
 tableRange1.getBorder().setBorderType(XLSBorderType.FullGrid);
 tableRange1.getBorder().setLineStyle(XLSBorderLineStyle.Continuous);
 tableRange1.getBorder().setWeight(XLSBorderWeight.Thin);
 tableRange1.getBorder().setLineColor(Color.gray);
 tableRange1.setBackColor(Color.decode("#E6E6E6"));
 
 // Draw the head of the table.
 sheet1.openTableRange("B5:F5").setBackColor(Color.blue);
 sheet1.openTableRange("B5:F5").setForeColor(Color.white);
 tableRange1.getDataFields.get(0).setValue("Head Title1");
 tableRange1.getDataFields.get(1).setValue("Head Title2");
 tableRange1.getDataFields.get(2).setValue("Head Title3");
 tableRange1.getDataFields.get(3).setValue("Head Title4");
 tableRange1.getDataFields.get(4).setValue("Head Title5");
 tableRange1.nextRow();
 
 // Output data into the table.
 Random r = new Random();
 for (int j = 0; j < 4; j++){
        for (int i = 0; i < tableRange1.getDataFields().size(); i++){
                int iValue = r.nextInt(100);
                tableRange1.getDataFields().get(i).setValue(String.valueOf(iValue)); // You can set the data from the database.
                if (iValue > 90){ // If the value is greater than 90, then the cell will display alert color to warn user.
                        tableRange1.getDataFields().get(i).setBackColor(Color.red);
                        tableRange1.getDataFields().get(i).setForeColor(Color.yellow);
                }
        }
        tableRange1.nextRow();
 }
 // Draw the bottom of the table and calculate the total.
 sheet1.openTableRange("B10:F10").setBackColor(Color.orange);
 tableRange1.getDataFields().get(0).setValue("Total");
 // You can get the total by your code. But using the formula of Excel will be more easier in most of the time. 
 tableRange1.getDataFields().get(4).setFormula = "sum(F6:F9)";
 tableRange1.nextRow();
 tableRange1.close(); // Required.
 
 // Merge cells into one cell in a table.
 sheet1.openTableRange("C10:E10").merge(); 
 
 aceCtrl1.setServerPage("../aceserver.do"); //Required
 aceCtrl1.bind(er);
 aceCtrl1.openDocument("doc/test.xls", OpenModeType.xlsReadOnly, "John Scott");
 aceCtrl1.setTagId("AceoffixCtrl1"); //Required
 

Parameters:
rangeAddress - The RangeAddress represents a range of cells containing one contiguous block of cells and it must use A1-style notation.

The A1-style notation is defined in Microsoft Excel. For example: A1:F8, it means a range of cells A1 through F8. The A1 cell is the cell whose row index is 1 and column index is 1. The F8 cell is the cell whose row index is 8 and column index is 6.

Note: There is a limit that the maximum number of columns per worksheet is 256 and the maximum number of rows is 65,536 in Excel2003 and earlier versions.

Throws:
java.lang.Exception

openTableRange

public ExcelResTableRange openTableRange(java.lang.String rangeAddress,
                                         boolean autoIncrease)
                                  throws java.lang.Exception
Opens the specified range of cells and returns a ExcelResTableRange object.

Refer to the ExcelResSheet.openTableRange method topic to learn how to use the openTableRange method.

Parameters:
rangeAddress - The RangeAddress represents a range of cells containing one contiguous block of cells and it must use A1-style notation.

The A1-style notation is defined in Microsoft Excel. For example: A1:F8, it means a range of cells A1 through F8. The A1 cell is the cell whose row index is 1 and column index is 1. The F8 cell is the cell whose row index is 8 and column index is 6.

Note: There is a limit that the maximum number of columns per worksheet is 256 and the maximum number of rows is 65,536 in Excel2003 and earlier versions.

autoIncrease - This parameter is optional. The default value is true. Set the value is false when you do not want the TableRange to expand the number of rows.
Throws:
java.lang.Exception