Use Excel sheets as styling templates for ZK Spreadsheet"

From Documentation
(Created page with '{{Template:Smalltalk_Author| |author=Phoenix Wu, Engineer, Potix Corporation |date=November 03, 2010 |version=Applicable to ZK 5.0.5 and later ::Applicable to ZK Spreadsheet 2.…')
 
 
(6 intermediate revisions by 4 users not shown)
Line 1: Line 1:
 
{{Template:Smalltalk_Author|
 
{{Template:Smalltalk_Author|
 
|author=Phoenix Wu, Engineer, Potix Corporation
 
|author=Phoenix Wu, Engineer, Potix Corporation
|date=November 03, 2010  
+
|date=November, 2010
|version=Applicable to ZK 5.0.5 and later
+
|version=Applicable to ZK 5.0.4 and later
  
::Applicable to ZK Spreadsheet 2.0.0
+
::Applicable to ZK Spreadsheet 2.0.0 and later
 
}}
 
}}
 +
 
__TOC__
 
__TOC__
  
Line 11: Line 12:
 
In this article, we will demonstrate how to build a rich Excel report with ZK Spreadsheet. If you'd like to know more about ZK Spreadsheet, please refer to the following URLs:
 
In this article, we will demonstrate how to build a rich Excel report with ZK Spreadsheet. If you'd like to know more about ZK Spreadsheet, please refer to the following URLs:
  
* [http://docs.zkoss.org/wiki/New_Features_of_ZK_Spreadsheet_2.0.0_RC New_Features_of_ZK_Spreadsheet_2.0.0_RC]
+
* [[Small_Talks/2010/December/New_Features_of_ZK_Spreadsheet_2.0.0_RC |New Features of ZK Spreadsheet 2.0.0 RC]]
 
 
* [http://docs.zkoss.org/wiki/A_Preview_of_ZK_Spreadsheet_Component A Preview of ZK Spreadsheet Component]
 
 
 
* [http://docs.zkoss.org/wiki/A_Sip_of_the_ZK_Spreadsheet_Component A Sip of the ZK Spreadsheet Component]
 
 
 
 
 
=Introduction=
 
 
 
With ZK Spreadsheet, data from excel can be integrated into ZK framework and vice versa. For example, you can alter formats easily when using Excel sheets, such as border, font, color, size and set variable values. ZK Spreadsheet will display those format settings and variables from server side will show in the right cells. If you would like to update variable values, you only need to update the data source (ex:database). Although the layout (ex: cell position) and content are modified frequently, variable values can be calculated correctly.
 
 
 
Besides, you can also use ZK components to change cell contents as often as you like. Furthermore, you can access any cell of ZK Spreadsheet from the scope of ZK components.
 
 
 
Let's start to build an application for calculating balance sheet.
 
 
 
 
 
  
 
=Live Demo=
 
=Live Demo=
Line 80: Line 66:
 
</source>
 
</source>
  
We have to create a Java class extends '''org.zkoss.zk.ui.util.GenericForwardComposer'''. More detail about relation of ZUL and composer class, please refer [http://docs.zkoss.org/wiki/MVC_in_ZK MVC in ZK].
+
We have to create a Java class extends '''org.zkoss.zk.ui.util.GenericForwardComposer'''. More detail about relation of ZUL and composer class, please refer [[ZK_Developer%27s_Reference/MVC| MVC in ZK]].
  
 
Then, we declare a few variables and methods in this class. Let's see section of Java source code.
 
Then, we declare a few variables and methods in this class. Let's see section of Java source code.
Line 160: Line 146:
 
ZK developers should have a general idea about new features of ZK Spreadsheet. From now on, ZK developers are able to access the data from ZK Spreadsheet easily. If you have any questions or suggestions, please feel free to leave comments here or post to [http://sourceforge.net/forum/forum.php?forum_id=510209 ZK forum].  
 
ZK developers should have a general idea about new features of ZK Spreadsheet. From now on, ZK developers are able to access the data from ZK Spreadsheet easily. If you have any questions or suggestions, please feel free to leave comments here or post to [http://sourceforge.net/forum/forum.php?forum_id=510209 ZK forum].  
  
 +
<comment>/smalltalks/zssVarRes2/index.dsp</comment>
  
 
[[Category:ZK Spreadsheet]]
 
[[Category:ZK Spreadsheet]]

Latest revision as of 03:30, 15 March 2011

DocumentationSmall Talks2010NovemberUse Excel sheets as styling templates for ZK Spreadsheet
Use Excel sheets as styling templates for ZK Spreadsheet

Author
Phoenix Wu, Engineer, Potix Corporation
Date
November, 2010
Version
Applicable to ZK 5.0.4 and later
Applicable to ZK Spreadsheet 2.0.0 and later


In this article, we will demonstrate how to build a rich Excel report with ZK Spreadsheet. If you'd like to know more about ZK Spreadsheet, please refer to the following URLs:

Live Demo

Before introduce this subject, we can watch the following video of demo.

Prepare a Template File

Before implementing ZK application, we have to prepare a template file. The following video will guide you how to:

* Define Name Ranges.
* Set formula on Excel.

About naming ranges, you can refer Naming Ranges.

Load Template File into ZK

ZUL Side

Now, we begin to use this template file in our ZK application. Of course, we have to create a new zul file at this time.

The defining way of ZK Spreadsheet in ZUL file is very simple, just like following ZUL code:

<?page title="Auto Generated index.zul"?>
<window apply="org.zkdemo.controller.SpreadSheetDemoComposer">

	<spreadsheet id="balance" src="/SpreadSheetDemo.xls" maxrows="20"
		maxcolumns="20" height="600px" width="800px" />

</window>

Please note the line 4. It difines a few properties of Spreadsheet. The most important one at this time for us is src. If you want to load outside file -- .xls, .xlsx --, you have to define loading path on this property.

Now, we have finished definition on ZUL file at here. We will see how to operate Spreadsheet on Java side at next step.

Java Side (Composer Side)

Let's view the ZUL code again.

<?page title="Auto Generated index.zul"?>
<window apply="org.zkdemo.controller.SpreadSheetDemoComposer">

	<spreadsheet id="balance" src="/SpreadSheetDemo.xls" maxrows="20"
		maxcolumns="20" height="600px" width="800px" />

</window>

We have to create a Java class extends org.zkoss.zk.ui.util.GenericForwardComposer. More detail about relation of ZUL and composer class, please refer MVC in ZK.

Then, we declare a few variables and methods in this class. Let's see section of Java source code.

public class SpreadSheetDemoComposer extends GenericForwardComposer {

    private Spreadsheet balance;

    public void doAfterCompose(Component comp) throws Exception {
        super.doAfterCompose(comp);
        FinancialQuarterDAO quarterDAO = new FinancialQuarterDAO();
        List<QuarterRecord> qs = quarterDAO.findAll();

        for (QuarterRecord q : qs) {
            drawQuarterRecord(q);
        }
    }

    private void drawQuarterRecord(QuarterRecord record) {
        int colIdx = record.getQuarterNo();
        setCellValue(QuarterRecord.FIELD_QUARTER, colIdx, record.getQuarterTitle());
        ...... // Set cell values
    }

    private void setCellValue(String rangeName, int col, Object value) {
        // Get range by range name(Naming Range Feature of Microsoft Excel).
        Range range = Ranges.range(balance.getSelectedSheet(), rangeName);
        
        // ZSS is 1 based, so if you want to get one cell of your range, 
        // you have to count by starting with 1.
        Range cell = range.getCells(1, col);
        cell.setEditText(value.toString());
    }

}

We opreated ZK Spreadsheet in Java code. We get range from ZK Spread sheet and set value that is from our data layer into the range. About Range, it represents a cell, a row, a column, or selection of cells containing one or more contiguous blocks of cells, or a 3-D blocks of cells.

org.zkoss.zss.model.Ranges is a utility class for operating range in ZK Spreadsheet. We can get range by range reference in Ranges. Range reference is the area the Range will refer to (e.g. "A1:D4" or naming "MyCells").

Cell Mapping

In following images, you can see every naming range in my sample has 4 cells. So, why do these values can be output at correct position?

Name Range: Quarter

RangeQuarter.png

Name Range: LiquidAssets

RangeLiquidAssets.png

Let's see method setCellValue(). It finds out range that we want to operate, and set values to cells of this range.

    private void setCellValue(String rangeName, int col, Object value) {
        // Get range by range name(Naming Range Feature of Microsoft Excel).
        Range range = Ranges.range(balance.getSelectedSheet(), rangeName);
        
        // ZSS is 1 based, so if you want to get one cell of your range, 
        // you have to count by starting with 1.
        Range cell = range.getCells(1, col);
        cell.setEditText(value.toString());
    }

By earlier images and description, we know the range may have several cells. If you want to set value to each cell of a specified range, you have to get cell(s) from this range by position. In ZK Spreadsheet, position of cell in range is started on 1. Just like following image.

Rangeposition.png

Download

Conclusion

ZK developers should have a general idea about new features of ZK Spreadsheet. From now on, ZK developers are able to access the data from ZK Spreadsheet easily. If you have any questions or suggestions, please feel free to leave comments here or post to ZK forum.



Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.