News in version
TreeGrid Gantt chart SpreadSheet Examples Documentation Download Development Licensing Prices References Contacts Buy Try it free
Live examples
Grids and tree grids

Complex grid - the first one Large table with paging Large table without paging Multiline & images - books Positions and spanning Photo album - images Dynamic Adobe Flash Dynamic MS SilverLight PivotTable - 2D grouping Schools - list and ratings File and directory browser Extended JavaScript API Sharing & synchronisation 1 000 000 rows, 1 000 cols

Sheets

Microsoft Excel sheet TreeGrid sheet

Gantt and bar charts

Gantt & Network chart Simple Gantt chart Big Gantt chart - 1000 rows Gantt chart tree by grouping Resources usage chart Run chart - bar chart Grouping bars - run groups Line chart and XY charts

Live grid tutorials
Basic (rows and columns)

1. Basic grid 2. Rows 3. Columns 4. Default rows, columns 5. Rows manipulation 6. Rows identification

Cells (types, formats, edit)

1. Edits - inputs & multiline 2. Combos & checkboxes 3. Html, links, imgs, buttons 4. Space buttons & tabs 5. Permissions & validation 6. Cell CSS style & color 7. Conditional formatting 8. Custom controls & editing 9. Events & custom dialogs 10. Column & row spanning 11. Dynamic cell spanning

Tree (rows, columns, groups)

1. Row tree 2. Column tree 3. Grouping rows to tree

Sorting rows

1. Sorting rows

Filter and search

1. Filtering rows 2. Filtering in tree 3. Search & advanced filters

Dragging

1. Dragging rows 2. Dragging between grids 3. Dragging external tag

Paging (rows, tree, columns)

1. Client root paging 2. Client child paging 3. Client column paging 4. All client paging 5. Adding pages dynamically 6. Server root paging 7. Server child paging 8. All server paging

Master and detail

1. Nested detail simple 2. Nested detail tables 3. Master detail simple 4. Master detail tables 5. Master detail more tables 6. Master detail in tree 7. Master detail trees

Calculations (cell formulas)

1. Calculated columns 2. Calculated rows 3. Calculation order 4. Calculated rows in tree 5. Calculation order in tree 6. Calculated attributes 7. Editable calculated cells

Live Sheet tutorials
Sheet features

1. Row and column Index 2. Auto size (auto adding pages) 3. Row&column manipulation 4. Cell manipulation 5. Cell style 6. Editable formulas (ids) 7. Editable formulas (indexes) 8. Cell format 9. Outside edit

Live Gantt tutorials
Main bar (Gantt chart)

1. Main bar 2. Discrete main bar 3. Main bar plans 4. Summary tasks

Run bar (Bar chart)

1. Run array definition 2. Run JSON definition 3. Run summary definition 4. Run drag 5. Run groups & API 6. Run bar as task 7. Run box as task 8. Network diagram 9. Run join and drag 11. 2 fixed levels containers

Flags and points

1. Flags 2. Points

Display, zoom, paging

1. Header & background 2. Zoom 3. Paging 4. Synchronizing more Gantts

Dependencies & scheduling

1. Constraints 2. Dependencies 3. Schedule tasks 4. Critical path

Calendars

1. Calendars exclude

Resources

1. Resources 2. Availability

TreeGrid JSP Framework

The framework contains support Java functions to help you with generating and parsing TreeGrid XML data and reading it from and upating it to database in server side script.
You need not use this framework, it contains only helper functions to simplify writting your code.
The framework is located in single file TreeGridFramework.jsp and can be included into your JSP pages by standard include mechanism <%@include file="TreeGridFramework.jsp"%>.
Remember, if you are generating XML files, be careful to not generate any white space before the first < character.



Parsing uploaded XML

Specific functions for parsing TreeGrid output XML changes

  • Document parseXML (String XML)
    Returns XML Document from String XML.
    Returns null when XML is empty or not valid.
    XML is complete data XML uploaded by TreeGrid.

  • Element[] getChanges (String XML)
  • Element[] getChanges (Document XML)
    Returns all changed rows - array of XML <I> elements in <Changes> tag.
    XML is complete data XML uploaded by TreeGrid.

  • int getPagePos (String XML)
  • int getPagePos (Document XML)
    Returns page number (<B Pos='000'/>) from input XML. By this number is page identified for server paging.
    XML is complete data XML uploaded by TreeGrid.

  • String getPageId (String XML)
  • String getPageId (Document XML)
    Returns page id attribute (<B id='xxx'/>) from input XML. By this id is row identified to get its children when server child paging is used.
    XML is complete data XML uploaded by TreeGrid.

  • String[] getSortCols (String XML)
  • String[] getSortCols (Document XML)
    Returns array of columns according to is grid sorted. Returns null if there are no columns.
    XML is complete data XML uploaded by TreeGrid.

  • int[] getSortTypes (String XML)
  • int[] getSortTypes (Document XML)
    Returns array of sorting types for columns according to is grid sorted. Returns null if there are no columns.
    XML is complete data XML uploaded by TreeGrid.

  • String[] getGroupCols (String XML)
  • String[] getGroupCols (Document XML)
    Returns array of columns according to is grid grouped. Returns null if there are no columns.
    XML is complete data XML uploaded by TreeGrid.

  • int[] getGroupTypes (String XML)
  • int[] getGroupTypes (Document XML)
    Returns array of grouping types for columns according to is grid grouped. Returns null if there are no columns.
    XML is complete data XML uploaded by TreeGrid.

  • boolean isAttribute (Element I, String name, String value)
    Compares value of row I attribute name with given value.

  • boolean isDeleted (Element I)
  • boolean isAdded (Element I)
  • boolean isChanged (Element I)
  • boolean isMoved (Element I)
    Tests if given row I has set the flag. For Moved tests equality to "2" (moved to another parent).

  • boolean getParameter (HttpServletRequest request,String name)
    Returns request parameter value, for null returns "".



    Generating SQL commands

    Universal functions for generating SQL commands from strings or values in XML attributes

  • String toSQL (String value)
    Returns string in quotes (') and with doubled all quotes.

  • String toSQL (Element I, String name)
    Returns value of XML attribute in quotes (') and with doubled all quotes.
    If attribute does not exist, returns two single quotes ('').

  • String toSQLUpdateString (String name, String value)
    Returns one item for UPDATE command, for string types. Returns "name='value',".
    If value is null, returns empty string.

  • String toSQLUpdateString (String name, Element I, String attributeName)
    Returns one item for UPDATE command, for string types. Returns "name='I.getAttribute(attributeName)',". For example "A='Peter''s bug',".
    If attribute does not exist, returns empty string.

  • String toSQLUpdateNumber (String name, String value)
    Returns one item for UPDATE command, for number types. Returns "name=value,".
    If value is null or empty string, returns empty string.

  • String toSQLUpdateNumber (String name, Element I, String attributeName)
    Returns one item for UPDATE command, for number types. Returns "name=I.getAttribute(attributeName),". For example "A=3,".
    If attribute does not exist, returns empty string.

  • String trimSQL (String value)
    Deletes all commas on the end of string value. Useful when building comma separated list in loop, call it after list is built to strip ending comma(s).

  • String toSQLInsert (Element I, String[] attrNames, boolean[] attrIsString)
    Returns values of row (XML element) I attributes in attrNames, separated by comma.
    The string never ends by comma.
    If the attribute does not exists, it is not included.
    If attrIsString[n] is true, it encloses attribute value by ' '.
    This function can be used for SQL INSERT command generating. Example:
    Element I;    // I is XML for example "<I id='7' Project='Main' Resource='This' Week='4' Hours='13'/>
    String[] Names = {"Project","Resource","Week","Hours"}; 
    boolean[] IsString = {false,true,true,false,false};
    String SQL = "INSERT INTO TableData(ID,Project,Resource,Week,Hours) VALUES(" + getAttributes(I,Names,IsString) + ")";
    

  • String toSQLUpdate (Element I, String[] attrNames, String[] outNames, boolean[] attrIsString)
    Returns row (XML element) I attribute values in format "outNames[0]=value_of_attrNames[0],outNames[1]=value_of_attrNames[1], ...".
    The string never ends by comma.
    If the attribute does not exists, it is not included.
    If attrIsString[n] is true, it encloses attribute value by ' '.
    This function can be used for SQL UPDATE command generating. Example:
    Element I;    // I is XML for example "<I id='7' Project='Main' Hours='13'/>
    String[] Names = {"Project","Resource","Week","Hours"}; 
    boolean[] IsString = {true,true,false,false};
    String SQL = "UPDATE TableData SET " + getAttributes(I,Names,Names,IsString) + " WHERE ID=" + getAttribute(I,"id");
    



    Updating changes in XML to database

    Advanced specific functions for updating changes got in TreeGrid XML to database table.

  • boolean saveTree (Statement Cmd, String table, String idCol, String parentCol, String parentBody) throws SQLException
    Saves changes in XML to database table by SQL statement Cmd.
    idCol is database table column name where is stored id attribute.
    parentCol is database table column name where is stored Parent attribute, for Parent<-Child relation. In Parent column the row has value of id column of parent row => All parent's children have its id in their Parent column.
    bodyParent is value of Parent for new added root rows.
    This function expects that all other attribute names are the same as column names in database table.



    Creating XML

    Universal functions for creating XML.

  • String toXMLString (String value)
    Returns value with replaced & ' < by XML entities

  • String fromXMLString (String value)
    Returns value with replaced XML entities by & ' < " > characters.

  • String toHTMLString (String value)
    Returns value with replaced & " by XML entities.
    Useful for direct writing generated XML to hidden input value on HTML page. Don't forget to use double quotes to enclose the string.

  • String toXML (String value)
  • void toXML (StringBuffer S, String value)
    Returns value in single quotes (') with replaced XML entities by & ' < characters.

  • String toXML (short value)
  • String toXML (int value)
  • String toXML (long value)
  • String toXML (boolean value)
  • String toXML (float value)
  • String toXML (double value)
    Returns value in single quotes (').

  • String toXML (String name, String value)
  • void toXML (StringBuffer S, String name, String value)
    Returns " value='value'", value with replaced XML entities by & ' < characters.

  • String toXML (String name, ResultSet R, String columnName)
  • void toXML (StringBuffer S, String name, ResultSet R, String columnName)
    Returns " name='R.getString(columnName)'", value with replaced XML entities by & ' < characters.
    ResultSet must be opened and cursor must be on some row.



    Advanced creating XML

    Specific functions for creating TreeGrid XML.

  • String getRowXML (String[] names, String[] values)
  • String getRowXML (String[] names, String[] values,String end)
  • String getRowXML (ResultSet R, String[] names, String[] colNames) throws SQLException
  • String getRowXML (ResultSet R, String[] names, String[] colNames, String end) throws SQLException
  • String getRowXML (ResultSet R, String[] names, int[] colIndexes) throws SQLException
  • String getRowXML (ResultSet R, String[] names, int[] colIndexes, String end) throws SQLException
  • String getRowXML (ResultSet R, String[] names) throws SQLException
  • String getRowXML (ResultSet R, String[] names, String end) throws SQLException
    Returns XML representation of one row with given attribute names and their values in array or from ResultSet R in columns colNames or colIndexes or in columns in order according to names.
    end is the string appended after row, for rows with children use ">", without children "/>" (default), or you can set own attributes.
    Does not move cursor in ResultSet.
    Remember when colIndexes are used, columns are indexed from 1 !
    Example:
    String[] Names = {"id","Project","Resource","Week","Hours"}; 
    String[] Values = {"7", "Main","This","4","13"};
    String XML = getRowXML(Names,Values); // The result will be: "<I id='7' Project='Main' Resource='This' Week='4' Hours='13'/>"
    

  • String getTableXML (ResultSet R, String[] names, String[] colNames) throws SQLException
  • String getTableXML (ResultSet R, String[] names, String[] colIndexes) throws SQLException
  • String getTableXML (ResultSet R, String[] names) throws SQLException
    Returns complete (include <Grid> tag) XML representation of all rows with given attribute names and their values from ResultSet R in columns colNames or colIndexes or in columns in order according to names.
    Remember when colIndexes are used, columns are indexed from 1 !
    Example result:
    <Grid><Body><B>
       <I id='7' Project='Main' Resource='This' Week='4' Hours='13'/>
       <I id='8' Project='Main' Resource='That' Week='3' Hours='8'/>
    </B></Body><Grid>
    

  • String getTableXML (ResultSet R) throws SQLException
  • String getTableXML (ResultSet R, String idCol) throws SQLException
    Returns complete (include <Grid> tag) XML representation of all rows with given attribute names and their values from ResultSet R in the same named attributes.
    Remember, some SQL databases have case insensitive column names and TreeGrid XML has always case-sensitive names.
    idCol is name of database column that will be stored to id attribute.

  • String getTreeXML (Statement Cmd, String table, String[] names, String[] colNames, String bodyParent) throws SQLException
  • String getTreeXML (Statement Cmd, String table, String[] names, String[] colNames, String bodyParent, String headParent, String footParent, boolean deep) throws SQLException
  • String getTreeXML (Statement Cmd, String table, String[] names, String bodyParent) throws SQLException
  • String getTreeXML (Statement Cmd, String table, String[] names, String bodyParent, String headParent, String footParent, boolean deep) throws SQLException
    Returns complete (include <Grid> tag) XML representation of all rows with given attribute names and their values from ResultSet R in columns indexed as attribute names or when used colNames returns only columns in this array in the same order as names attributes.
    It is used for generating tree from one table that has parent->id relation.
    table is name of database table names are TreeGrid attribute names in order in that are filled from columns from database table colNames are names of columns in database used instead of indexes. Must be in the same order as names array. Can contain only some columns from database table.
    names must contain name "id", this is identity attribute to unique identify row when uploading and in tree.
    names must also contain name "Parent", this is column related to id in Parent<-Child. In Parent column the row has value of id column of parent row => All parent's children have its id in their Parent column.
    For deep==false reads only one level of tree, for server side child paging.

  • String getTreeXML (Statement Cmd, String table, String[] names, String[] colNames, String parentVal, boolean deep) throws SQLException
  • String getTreeXML (Statement Cmd, String table, String[] names, String parentVal boolean deep) throws SQLException
    Returns partial XML, returns children of row with id = parentVal => returns all rows that have in their Parent column value parentVal.
    Other parameters are the same as in previous function getTreeXML.



    Support for examples

  • Statement getHsqlStatement (HttpServletRequest request, JspWriter out, String dbPath, String user, String pass) throws Exception
    Creates SQL statement for SQL database HSQLDB that is used for examples.
    HSQLDB is simple database in text files without need of any driver instalation. Just copy file hsqldb.jar file to your shared lib directory and restart http server.
    request and out are just the variables in main servlet functions.
    dbPath is path and name of database file(s), relative to location of the script file.
    user and pass are login information to database.
    If database driver cannot be executed, because hsqldb.jar file is missing or not accessible it writes this information to output, closes it and raises Exception.