博客


Build templates with EnterpriseSheet

Instead of creating an online spreadsheet from scratch, you can create a sheet based on a template. As required, we are starting to build online spreadsheet templates available, waiting to be used.

For more detail about templates, please visit the following link:

You can also create your own template and share with your end-users. To create a template, execute the following steps.

  • Create an Excel file with your required function
  • Put the excel file under folder, such as: sheet\src\main\resources\xlsx\youTemplate.xlsx
  • Go to Java class: SheetTemplateController and follow index to create new sheet file based on youTemplate.xlsx file

Handle 3 million cells in one worksheet with EnterpriseSheet

Big data, it is a big volume of data as we know. However, What makes big data complicated is just how "big" it is and also the challenges that analysis and usage present.

Data hypergrowth has pretty much become a cliche for our customer. With more and more data added to the customer system, our customer need an analyze tool with great user interface to help them handle those data. EnterpriseSheet is chosen by our customers to handle their big data and display their data with browser.



Open the following file (3 million cells in one worksheet) and experience the performance of EnterpriseSheet.

Define sheet column data type with customer defined dropdown list

Sheet allows user to configure URL in the column to load their defined dropdown list data into the cells under this column. When user double click the cell, sheet load data from customer server by the defined URL and render data to the page.



Select "Date type configure" from column context menu, user can choose "Custom Dropdown List" item and process their configuration. Here is the json result which return from the custom defined URL.


{"results":[{"id":1,"value":"HR Dept"},{"id":2,"value":"QA Dept"},{"id":3,"value":"Sale Dept"}],"success":"true"}

See the following example for the column data type.

New feature: manually configure column data type

From column title menu list, user can select "Date type configure" and define different kinds of column data type. Once the data type is defined, user can enter the related data in the cells under this column.



The following column data type can be defined manually:

  • Number
  • Text
  • Date
  • Phone
  • Email
  • URL
  • Checkbox
  • Radio button
  • Dropdown list
  • Attachments
  • Currency
  • Percent
  • Long text
  • Custom dropdown list

See the following example for the column data type.

Set column data type through APIs

EnterpriseSheet provides an API solution for customer to define their column data type. Once column data type is defined, user need enter data with defined type for all the cells in this column.



Here lists the example code to define dropdown list, date type, checkbox etc.


{ sheet: 1, row: 0, col: 7, json: { data: "Gender", width: 100, drop: Ext.encode({data: "Male, Female"}) } },
{ sheet: 1, row: 0, col: 8, json: { data: "Birth date", width: 130, drop: "date", fm: "date", va: "middle", dfm: "F d, Y", ticon:"calendar"  } },
{ sheet: 1, row: 0, col: 10, json: { data: "Manager?", it: "checkbox", itchk: false, ta: "center" } },


Run the following example to see more detail how to defined column data type.

Dynamic form builder: create survey with EnterpriseSheet

When you need to collect information, learn people's preferences or gather feedback, there's nothing better than a form. EnterpriseSheet allows you to create dynamic form which includes dropdown list, radio button, checkbox, text field, table etc.



Once this survey is created and published online. The end-user can enter data in the form, click "Submit" button, and send data to the customer defined callback function.



Run the following survey example which was build with EnterpriseSheet.

Data binding with variables

EnterpriseSheet provides data communication between sheet and customer existing data source. Customer data object can be binded to the cell through variable defined in the cells.



The following figure shows the steps to process general actions.

Click the following link to see more details information.

EnterpriseSheet architecture
The EnterpriseSheet application is built with the following technologies:
  • JavaScript (ExtJS 4.2.1 based)
  • Java (Spring framework)
  • MySQL
The following figure shows the idea about code level integrate EnterpriseSheet into customer existing application.

sheet

You will be able to access all of those source code after you get a commercial license. For more detail about EnterpriseSheet architecture, please visit:
Cell 2-way data binding

EnterpriseSheet provides a 2-way data binding between sheet and customer data source. Customer data object can be binded to the cell through custom defined function.

sheet

Process the following steps to achieve this function:
  • Defined a custom function which will be called during custom data binding.
  • Include the above defined custom function in the json data which will be consumed by the sheet.
  • End-user trigger this custom function with mouse move over the cell.
  • Cell editor in the custom function is popup for end-user to process actions.
  • Cell editor and sheet process data communication.
See the following documents for more detail information.
SUMPRODUCT() - Sum values based on conditions

Sometimes we want to sum values from a table based on conditions that we input into another cell. Take a look at the image below. Here we have a table of sale prices for the different item.

sheet


Now we can apply criteria to the data and calculate product result by using SUMPRODUCT. For example, in this sheet we want to calculate the total price which unit price is more than $5. We can use the following function to calculate:

=SUMPRODUCT(B3:B8*C3:C8*(B3:B8>5))

We can also apply more than one criteria to the list data.
Top 5 benefits of integrating EnterpriseSheet into your system

Here are five of the top features and benefits of integrating EnterpriseSheet:

It is YOUR online spreadsheet. EnterpriseSheet provides an enterprise solution to integrate and build your business spreadsheet. It adds a powerful and valuable tool in your system to analysis and manage your customer's data. And you control all levels of your customer data.

Dynamically bring data together. By calling the EnterpriseSheet APIs, you can dynamically bring different kinds of data from your system and combine them into one workSheet in different kinds of format.

Display the power of your data to your customers. EnterpriseSheet charts, conditional format, validation, table, filter, group etc unlock the potential of your data, highlight the most important data and bring the power of you data to your customers.

Give your customers great user experience. EnterpriseSheet simple and intuitive user interface design bring the great user experience to your customers.

Standby support team. EnterpriseSheet's developing team is always on standby to assist any of your issues and give you the best customer service with the fastest response time.

Apply conditional formatting Icon Set to the cells

EnterpriseSheet conditional formatting icon sets will help you visually represent your data with arrows, shapes, check marks, flags, rating starts and other objects.

You apply the icon sets to your data by clicking Conditional management ... > Icon Set, and the icons appear inside selected cells straight away.

sheet

In the screenshot above, you can see different values are displayed in different icons. By clicking Other rule... underneath the icon sets list to bring up the New Formatting Rule dialog. From here you are able to choose other icons and assign them to different values.

Tips:

  • To change the order of icons, click the Reverse Icon Set check box.
  • To hide the cells' values, select the Show Icon Only check box.
  • To assign icons based on a cell's value instead of specifying a number or percent, type the cell's address in the Value box or click the selection range icon to select a cell.
Apply Data Validation to Cells

Data validation is an EnterpriseSheet feature that you can use to define restrictions on what data can or should be entered in a cell. You can configure data validation to prevent users from entering data that is not valid. If you like, you can allow users to enter invalid data but warn them when they try to type something in the cell. You can also provide messages to define what input you expect for the cell, and instructions to help users correct any errors.

To add validation to the selected cells, you can select Data -> Validation... from title bar. EnterpriseSheet supports 4 type of data validation

  • Number validation
  • Date validation
  • Item list
  • Text validation
sheet

Click the following link to see sample code which let your create validation for your data range from EnterpriseSheet API.

Add Customized Calculate Function through API

Even when EnterpriseSheet has a lot of built in functions like SUM, VLOOKUP, LEFT, and so on, once your end-user start using EnterpriseSheet for more complicated tasks, they will possible ask your to add a function that doesn't exist in the system. Don't worry, you're not lost at all, all you need is to create the function yourself by calling EnterpriseSheet API.

EnterpriseSheet API allows you to create your customized calculate function. Follow the example code style, enter your function calculate logic, call EnterpriseSheet API, you can easily build your function as your like.

sheet

Click the following link to see sample code which let your create customized function from EnterpriseSheet API.

Chart Types: Bar, Column, Pie, Line, Area, Scatter

EnterpriseSheet allows you to create charts in a variety of types: Bar Charts, Column Charts, Line Charts, Area Charts, Scatter Charts, Pie Charts. Follow the steps to create the chart for your selected data series.

  • Select the range of data series for generate chart.
  • From title bar, click menu Edit -> Chart button. You can also click chart icon from side bar to process.
  • Chart wizard side bar is displayed. You can select one of charts to generate your chart.

EnterpriseSheet APIs bring your a convenience way to generate the desired chart based on your data and pop it into your sheet.

sheet

Click the following link to see sample code which let your add chart to your data from EnterpriseSheet API.

Adding Conditional Formatting to Highlight Your Data

How to make your data visualization easier? How to let user find the key information from your huge data? Are there some values that are different from the rest? Fortunately, EnterpriseSheet APIs already does some of this for your. EnterpriseSheet conditional feature allows your to apply a defined format to your data that meet specific criteria, and then popup those data into your online spreadsheet for user to view and edit.

Conditional formatting makes it easy to highlight certain values or make particular cells easy to identify. This changes the appearance of a cell range based on a condition. You can use conditional formatting to highlight cells that contain values which meet a certain condition. Or you can format a whole cell range and vary the exact format as the value of each cell varies.

sheet

Click the following link to see detail sample code to add conditional formatting to your data from EnterpriseSheet API.