|
|||||||||
PREV CLASS NEXT CLASS | FRAMES NO FRAMES | ||||||||
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD |
java.lang.Objectcom.acesoft.aceoffix.ExcelResSheet
public class ExcelResSheet
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.
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 |
---|
public java.lang.String getName() throws java.io.IOException
java.io.IOException
public ExcelResCell openCell(java.lang.String cellAddress) throws java.lang.Exception
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
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.
java.lang.Exception
public ExcelResCell openCellRC(int row, int col) throws java.lang.Exception
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.
row
- The index of row.col
- The index of column.
java.lang.Exception
public ExcelResTableRange openTableRange(java.lang.String rangeAddress) throws java.lang.Exception
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
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.
java.lang.Exception
public ExcelResTableRange openTableRange(java.lang.String rangeAddress, boolean autoIncrease) throws java.lang.Exception
Refer to the ExcelResSheet.openTableRange method topic to learn how to use the openTableRange method.
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.
java.lang.Exception
|
|||||||||
PREV CLASS NEXT CLASS | FRAMES NO FRAMES | ||||||||
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD |