com.acesoft.aceoffix.excelwriter
Class Sheet

java.lang.Object
  com.acesoft.aceoffix.excelwriter.Sheet

public class Sheet
extends java.lang.Object

Sheet class represents a worksheet defined in Excel.

Sheet class represents the worksheet defined in Excel. You can only call the Workbook.openSheet(String) method to get the Sheet object.

Version:

5.0

Author:

Acesoft Corporation

Method Summary

java.lang.String	getName() Gets the name of the Sheet.
Cell	openCell(java.lang.String cellAddress) Opens the specified cell and returns a Cell object.
Cell	openCellByDefinedName(java.lang.String definedName) Opens the cell with specified name that defined in Excel and returns a Cell object.
Cell	openCellRC(int row, int col) Opens the specified cell and returns a Cell object.
Table	openTable(java.lang.String rangeAddress) Opens the specified range of the cells and returns a Table object.
Table	openTable(java.lang.String rangeAddress, boolean autoIncrease) Opens the specified range of cells and returns a Table object.
Table	openTableByDefinedName(java.lang.String definedName, int rowCount, int colCount) Opens the table with the specified name defined in Excel and returns a table object.
Table	openTableByDefinedName(java.lang.String definedName, int rowCount, int colCount, boolean autoIncrease) Opens the table with specified name that defined in Excel and returns a table object.
void	setAllowAdjustRC(boolean value) Sets a value that indicates whether users are allowed to adjust rows and column manually when the worksheet is read-only.
void	setAutoFit(boolean value) Sets a value that indicate whether the AutoFit feature will be used in the cells of the worksheet.
void	setReadOnly(boolean value) Sets a value that indicates whether the worksheet is read-only.

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 Sheet.

The name returned is the name of the current sheet to be opened.

Throws:

java.io.IOException

setReadOnly

public void setReadOnly(boolean value)
                 throws java.io.IOException

Sets a value that indicates whether the worksheet is read-only.

If the value is true, the editable Sheet range will be read-only. And if the value is false, the read-only Sheet range will be editable.

Only when OpenMode is Aceoffix.OpenModeType.xlsSubmitForm, the ReadOnly property is valid. The default value is true.

Throws:

java.io.IOException

setAutoFit

public void setAutoFit(boolean value)
                throws java.io.IOException

Sets a value that indicate whether the AutoFit feature will be used in the cells of the worksheet.

If the value is true, the cells in worksheet will be expanded to display all value automatically.

Throws:

java.io.IOException

setAllowAdjustRC

public void setAllowAdjustRC(boolean value)
                      throws java.io.IOException

Sets a value that indicates whether users are allowed to adjust rows and column manually when the worksheet is read-only.

If the value is true, users are allowed to adjust rows and columns manually.

Throws:

java.io.IOException

openCell

public Cell openCell(java.lang.String cellAddress)
              throws java.lang.Exception

Opens the specified cell and returns a Cell object.

The following code example shows how to call the OpenCell method.

 Workbook wb = new Workbook();
 Sheet sheet1 = wb.openSheet("sheet1");
 
 // Output data into the cell and set the color of the cell.
 sheet1.openCell("C2").setValue("Acesoft");
 sheet1.openCell("C2").setBackColor(Color.red);
 sheet1.openCell("C2").setForeColor(Color.yellow);
 
 sheet1.openCellRC(2, 4).setValue("Aceoffix");
 
 sheet1.openCell("B5").setValue("6");
 sheet1.openCell("C5").setValue("3");
 sheet1.openCell("D5").setFormula("B5+C5");
 
 aceCtrl1.setServerPage("server.ace"); //Required
 aceCtrl1.bind(wb);
 aceCtrl1.openDocument("doc/test.xls", OpenModeType.xlsReadOnly, "John Scott");
 aceCtrl1.setTagId("AceoffixCtrl1"); //Required
 

Parameters:

cellAddress - The A1-style notation is defined in Microsoft Excel. For example: "A1".

Note: There is a limit that the maximum number of columns per worksheet is 676 and the maximum number of rows is 65,536 in Excel2003 and earlier versions. Please do not exceed the maximum number of columns when input the parameter of CellAddress.

Returns:

Returns a Cell object.

Throws:

java.lang.Exception

openCellRC

public Cell openCellRC(int row,
                       int col)
                throws java.lang.Exception

Opens the specified cell and returns a Cell object.

Note: There is a limit that the maximum number of columns per worksheet is 676 and the maximum number of rows is 65,536 in Excel2003 and earlier versions. Please do not exceed the maximum number of columns when input the parameter of Col.

Parameters:

row - The index of row. Start from "1".

col - The index of column. Start form "1".

Returns:

Returns a Cell object.

Throws:

java.lang.Exception

See Also:

Sheet.openCell() to learn how to call the openCellRC method.

openCellByDefinedName

public Cell openCellByDefinedName(java.lang.String definedName)
                           throws java.lang.Exception

Opens the cell with specified name that defined in Excel and returns a Cell object.

If the specified name does not exist in the current worksheet, all operation to the Cell object will be ignored.

Parameters:

definedName - The name defined in Excel can be a global name or a local name. Please select single cell when you define the name. If you select cell range, you can only return the cell object on the top left corner.

Returns:

Returns a Cell object.

Throws:

java.lang.Exception

openTable

public Table openTable(java.lang.String rangeAddress)
                throws java.lang.Exception

Opens the specified range of the cells and returns a Table object.

In Excel worksheet, developer can define any range of cells containing one contiguous block of cells as a Table object. You should use Table 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 Table 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 Table to expand the number of rows, you should call the Sheet.openTable(string, bool) method.

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

 Workbook wb = new Workbook();
 Sheet sheet1 = wb.openSheet("sheet1");
 Table table1 = sheet1.openTable("B5:F10");
 
 // Output data into the table.
 Random r = new Random();
 for (int j = 0; j < 4; j++){
        for (int i = 0; i < table1.getDataFields().size(); i++){
                table1.getDataFields().get(i).setValue(String.valueOf(r.nextInt(100))); // You can set the data from database.
        }
        table1.nextRow();
 }
 table1.close(); // Required.
 
 aceCtrl1.setServerPage("server.ace"); //Required.
 aceCtrl1.bind(wb);
 aceCtrl1.openDocument("doc/test.xls", OpenModeType.xlsReadOnly, "Tom");
 aceCtrl1.setTagId("AceoffixCtrl1"); //Required.
 

Code example 2: Draws 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.

 Workbook wb = new Workbook();
 Sheet sheet1 = wb.openSheet("sheet1");
 Table table1 = sheet1.openTable("B5:F10");
 
 // Draw a table with full grid lines and set the color of the table.
 table1.setRowHeight(20);
 table1.setColumnWidth(14.25);
 table1.getBorder().setBorderType(XlBorderType.FullGrid);
 table1.getBorder().setLineStyle(XlBorderLineStyle.Continuous);
 table1.getBorder().setWeight(XlBorderWeight.Thin);
 table1.getBorder().setLineColor(Color.gray);
 table1.setBackColor(Color.decode("#E6E6E6"));
 
 // Draw the head of the table.
 sheet1.openTable("B5:F5").setBackColor(Color.blue);
 sheet1.openTable("B5:F5").setForeColor(Color.white);
 table1.getDataFields.get(0).setValue("Head Title1");
 table1.getDataFields.get(1).setValue("Head Title2");
 table1.getDataFields.get(2).setValue("Head Title3");
 table1.getDataFields.get(3).setValue("Head Title4");
 table1.getDataFields.get(4).setValue("Head Title5");
 table1.nextRow();
 
 // Output data into the table.
 Random r = new Random();
 for (int j = 0; j < 4; j++){
        for (int i = 0; i < table1.getDataFields().size(); i++){
                int iValue = r.nextInt(1000);
                table1.getDataFields().get(i).setValue(String.valueOf(iValue)); // You can set the data from the database.
                if (iValue > 800){ // If the value is greater than 800, then the cell will display alert color to warn user.
                        table1.getDataFields().get(i).setBackColor(Color.red);
                        table1.getDataFields().get(i).setForeColor(Color.yellow);
                }
        }
        table1.nextRow();
 }
 // Draw the bottom of the table and calculate the total.
 sheet1.openTable("B10:F10").setBackColor(Color.orange);
 table1.getDataFields().get(0).setValue("�计");
 // You can get the total by your code. But using the formula of Excel will be more easier in most cases.
 table1.getDataFields().get(4).setFormula = "sum(F6:F9)";
 table1.nextRow();
 table1.close(); // Required.
 
 // erge cells specified by C10:E10 into one cell in a table.
 sheet1.openTable("C10:E10").merge(); 
 
 aceCtrl1.setServerPage("server.ace"); //Required.
 aceCtrl1.bind(wb);
 aceCtrl1.openDocument("doc/test.xls", OpenModeType.xlsReadOnly, "Tom");
 aceCtrl1.setTagId("AceoffixCtrl1"); //Required.
 

Parameters:

rangeAddress - 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 676 and the maximum number of rows is 65,536 in Excel2003 and earlier versions. Please do not exceed the maximum number of columns when input the parameter of RangeAddress.

Returns:

Returns a Table object.

Throws:

java.lang.Exception

openTable

public Table openTable(java.lang.String rangeAddress,
                       boolean autoIncrease)
                throws java.lang.Exception

Opens the specified range of cells and returns a Table object.

Parameters:

rangeAddress - 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 676 and the maximum number of rows is 65,536 in Excel2003 and earlier versions. Please do not exceed the maximum number of columns when input the parameter of RangeAddress.

autoIncrease - This parameter is optional. The default value is true. Set the value to false when you do not want the table to expand new rows.

Returns:

Returns a Table object

Throws:

java.lang.Exception

openTableByDefinedName

public Table openTableByDefinedName(java.lang.String definedName,
                                    int rowCount,
                                    int colCount)
                             throws java.lang.Exception

Opens the table with the specified name defined in Excel and returns a table object.

If the specified name does not exist in the current worksheet, all operation to the table object will be ignored.

Parameters:

definedName - The name defined in Excel can be a global name or a local name.

rowCount - The number of rows in the range specified by the DefinedName.

colCount - The number of columns in the range specified by the DefinedName.

Returns:

Returns a Table object.

Throws:

java.lang.Exception

openTableByDefinedName

public Table openTableByDefinedName(java.lang.String definedName,
                                    int rowCount,
                                    int colCount,
                                    boolean autoIncrease)
                             throws java.lang.Exception

Opens the table with specified name that defined in Excel and returns a table object.

If the specified name does not exist in the current worksheet, all operation to the table object will be ignored.

Parameters:

definedName - The name defined in Excel can be a global name or a local name.

rowCount - The number of rows in the range specified by the DefinedName.

colCount - The number of columns in the range specified by the DefinedName.

autoIncrease - This parameter is optional. The default value is true. Set the value to false when you do not want the table to expand new rows.

Returns:

Returns a Table object.

Throws:

java.lang.Exception