Use SpreadsheetCloudAPI for creating a web application

While developing our SpreadsheetCloudAPI service, we used DevExpress Spreadsheet Document Server. In turn, guys from DevExpress used our service in one of their internal projects and shared their impressions with us. Here is their story.

Our team has a hobby — we like 3D printing. We do not just like it; we also print many models ranging from simple figures to place on a table, to custom parts and components which you cannot even buy in China. Everybody in our room printed something at least once.

Just like a regular printer requires ink, a 3D printer requires plastic. We initially used the media which came with a printer, and then we had a whip-round and bought many reels of colored plastic filament. However, these did not last long either. We noticed that the printing volume differs among users, so we decided to develop a web application to take stock of printing materials automatically. This means you can calculate how much material each user consumes, and how much they should pay.

We want our application to do the following:

• Allow users to enter and edit printing information
• Allow users to add new users
• Allow users to add information on new types of plastic (type, price)
• Automatically calculate each user’s total printing cost based on the models they printed and type of material they used

Calculate costs

The first problem is that plastic is priced per kilogram, while material consumption is calculated per meter. At first, we thought about using scales to measure the weight of each printed model. However, we realized we had to come up with a better solution. Using plastic density, filament diameter, and length, we can calculate the amount of material required to print each model.

Thus, all a user should do is to enter the printing session’s information– the type and length of plastic filament used.

Taking into account all our requirements, we decided that our application UI should include the following tabs: “Prints”, “Users” and “Materials”.

The “Material” tab lists each plastic type’s density, filament diameter and price per kilogram.

The “Users” tab contains a list of users and the automatically calculated total of each model they printed. This tab also visualizes the data via a chart.

The “Prints” tab includes information on printing sessions: a user name, plastic type and filament length used for printing a model. Printing costs are automatically calculated.

Our application should provide the logic to calculate the cost of printing each model and summarize the cost of each user’s models, create a chart to visualize data and store this data. The SpreadsheetCloudAPI service is perfect for this purpose: it allows implementing calculation logic and storing data in an Excel file without the need to write any code. All the application should do is send and receive data from the SpreadsheetCloudAPI service.

If you need to change the calculation logic in the future, you can edit the Excel file only without modifying the application. We describe how to create an Excel file for the application in this blog post.

User Interface

Our application receives data in HTML format from the SpreadsheetCloudAPI service. This data is exported as a table, which you can edit by handling a click on a cell in a table and assigning the required editor. We use “Input” to edit data values and “Select” to select existing values.

On the “Prints” tab, we provide a username, plastic type and filament length used for printing to create a new printing session:

These data values are editable:

The “Cost” column is automatically populated with data calculated in this service.

On the “Material” tab, we input the plastic type, density, filament diameter and price per kilogram:

On the “Users” tab, we input user names. The “Full Cost” column (total cost of each user’s models) is automatically calculated. This tab also visualizes the data via a chart created by Excel and sent to our application as an image.

Backend

The calculation logic is fully implemented in the Excel file. In the backend, we should enable communication between our application and the SpreadsheetCouldAPI service only. We used php.

Use the following parameters to bind the application to the service:

Service URL — “http://spreadsheetcloudapi.azurewebsites.net/api/spreadsheet”

API Key — provided by the service

File name

Specify all constants in a class:

class PrivateConst {
    const Base_Url = ‘http://spreadsheetcloudapi.azurewebsites.net/api/spreadsheet';
    const API_KEY = ‘your_api_key’;
    const File_Name = ‘3D.xlsx’;
}

Use CURL to send commands to the service. We need two request types: “PUT” and “GET”. Implement them in the application code:

function put( $params, $url )
function get( $params, $url )

$params — request parameters, $url — a part of request url (method name).

This is an example of how to implement a method to get data in HTML format:

function getSessionHtml($id, $sheetName, $rowLimit, $columnLimit){
    $params = array(
        ‘id’ => $id,
        ‘sheetname’ => $sheetName,
     );
     if($rowLimit > -1){
         $params[‘endrowindex’] = $rowLimit;
     }
     if($columnLimit > -1){
         $params[‘endcolumnindex’] = $columnLimit;
     }
     $request = get($params, ‘/exporttohtml’);
     return $request;
}

One of this method’s parameters is $id — the loaded file session’s ID. We get this parameter when opening a file on the service using the LoadDocument method:

$id = loadDocument($filename);

With this ID, the service does not use resources to open a file and can execute commands and requests in turns, saving time and requests.

The complete application code is available at this link.

Using the SpreadsheetCloudAPI service, we can create a web application based on an Excel file without writing any code to implement the calculation logic and store data.

If you have any questions, please feel free to leave you comments here or contact us at scloudapi@gmail.com.