com.acesoft.aceoffix.excelreader
Class Sheet

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

public class Sheet
extends java.lang.Object

Sheet class represents a 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.util.ArrayList<Cell>	getCells() Gets the Cells named by DefinedName.
java.lang.String	getName() Gets the name of the Sheet.
java.util.ArrayList<Table>	getTables() Gets the Tables named by DefinedName.
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 the specified name defined in Excel and returns a Cell object.
Table	openTable(java.lang.String rangeAddress) Opens the specified table and returns a Table object.
Table	openTableByDefinedName(java.lang.String definedName) Opens the table with the specified name defined in Excel and returns the Table 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 Sheet.

Throws:

java.io.IOException

getCells

public java.util.ArrayList<Cell> getCells()
                                   throws java.io.IOException,
                                          java.lang.Exception

Gets the Cells named by DefinedName.

Throws:

java.io.IOException

java.lang.Exception

getTables

public java.util.ArrayList<Table> getTables()
                                     throws java.io.IOException,
                                            java.lang.Exception

Gets the Tables named by DefinedName.

Throws:

java.io.IOException

java.lang.Exception

openCell

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

Opens the specified cell and returns a Cell object.

If the specified Cell exists, this method will return the Aceoffix.ExcelReader.Cell object.

The following code example shows how to use the OpenCell method to get the value of the specified cell.

 Workbook wb = new Workbook(request, response);
 Sheet sheet1 = wb.openSheet("sheet1");
 String strCompanyName = sheet1.openCell("D1").getValue();
 String strProductName = sheet1.openCell("D2").getValue();
 String strProductCode = sheet1.openCell("D3").getValue();
 
 String strAddress = sheet1.openCell("F6").getValue();
 
 //After you get the value, you can save them to the database.
 
 wb.close();
 

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

java.io.IOException

openCellByDefinedName

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

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

The following code example shows how to use the openCellByDefinedName method to get the value of the specified cell.

 Workbook wb = new Workbook(request, response);
 Sheet sheet1 = wb.openSheet("sheet1");
 String strCompanyName = sheet1.openCellByDefinedName("CompanyName").getValue();
 String strProductName = sheet1.openCellByDefinedName("ProductName").getValue();
 String strProductCode = sheet1.openCellByDefinedName("ProductCode").getValue();
 
 //After you get the value, you can save them to the database.
 
 wb.close();
 

Parameters:

The - name defined in Excel can be a global name or a local name. This method will only be valid when the Cell has a DefinedName.

Returns:

Returns a Cell object.

Throws:

java.lang.Exception

java.io.IOException

openTable

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

Opens the specified table and returns a Table object.

If the specified Table exists, this method will return the Aceoffix.ExcelReader.Table object.

The following code example shows how to use the OpenTable method to get the value of the specified table.

 Workbook wb = new Workbook(request, response);
 Sheet sheet1 = wb.openSheet("Sheet1");
 String strCompanyName = sheet1.openCell("CompanyName").getValue(); //Get the data of a cell
 Table table1 = sheet1.openTable("B5:F16");//Get the data of a table  
 while (!table1.getEOF()) {
                String strValues = "";
                if (!table1.getDataFields().getIsEmpty()){
                        for (int i = 0; i < table1.getDataFields().size(); i++)
                                strValues = strValues + table1.getDataFields().get(i).getText() + "  ";
                        // Output the data to the current page. Typically, you can save them to the database.
                        out.print(strValues + "<br>\r\n");
                }
                table1.nextRow();
 }
 table1.close();
 out.print("table1.RowCount = " + table1.getRowCount());
 wb.showPage(800, 600);// Pop up a HTML dialog to show the data of table. 
 wb.close();
 

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

java.io.IOException

openTableByDefinedName

public Table openTableByDefinedName(java.lang.String definedName)
                             throws java.lang.Exception,
                                    java.io.IOException

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

The following code example shows how to use the openTableByDefinedName to get the value of the specified table.

 Workbook wb = new Workbook(request, response);
 Sheet sheet1 = wb.openSheet("sheet1");
 //Get the data of a table
 Table table1 = sheet1.openTableByDefinedName("SalesInfo", 7, 9);
 while (!table1.getEOF()){
                String strValues = "";
                if (!table1.getDataFields().getIsEmpty()){
                        for (int i = 0; i < table1.getDataFields().size(); i++)
                                strValues = strValues + table1.getDataFields().get(i).getValue() + "&nbsp;&nbsp;";
                        // Output the data to the current page. Typically, you can save them to the database.
                        out.println(strValues + "<br>\r\n");
                }
                table1.nextRow();
 }
 table1.close();
 out.println("table1.RowCount = " + table1.getRowCount());
 wb.showPage(800, 600); //  Pop up a HTML dialog to show the data of table.
 wb.close();
 

Parameters:

definedName - The name defined in Excel can be a global name or a local name. This method will only be valid when the table has a DedinedName.

Returns:

Returns a Table object.

Throws:

java.lang.Exception

java.io.IOException