How to read, write and modify spreadsheets

In this video, you will learn how to read, write and modify spreadsheets in Centrldesk.

Spreadsheets like Excel files are useful for many use cases and can be found in almost every company worldwide. In this example, we will read an existing Excel file, add a new column with content in Centrldesk and generate a new Excel file for downloading. So let's start building our application:

Steps:

1. First, we go to the page editor by clicking on the Edit Page button.
2. Add a file upload element using drag and drop.
3. Change the placeholder name of the file upload element to "Upload an Excel file".
4. Add a button using drag and drop.
5. Change the name of the button to "Save modified Excel file", and choose an icon and a color.
6. With Centrldesk, you can easily customize your applications and graphical user interfaces to meet your needs.
7. Next, we add an action to the button by clicking the "Add" function on the "On Click" event.
8. The Flow Editor opens.
9. On the left side, under Libraries, we now drag and drop the "File Processing" element into the center of the Flow Editor.
10. The "File Processing" dialog opens.
11. Select "Read Spreadsheet File" on the left side and confirm with OK.
12. On the left side, under General, we now drag and drop the "For Each" element into the center of the Flow Editor.
13. On the left side, under Libraries, we now drag and drop the "File Processing" element into the center of the Flow Editor.
14. The "File Processing" dialog opens.
15. Select "Write Spreadsheet File" on the left side and confirm with OK.
16. On the left side, under Libraries, we now drag and drop the "File Processing" element into the center of the Flow Editor.
17. The "File Processing" dialog opens.
18. Select "Download Files" on the left side and confirm with OK.
19. Connect the output of the Read Spreadsheet File block to the input of the For Each block.
20. Connect the output of the For Each block to the input of the Write Spreadsheet File block.
21. Connect the output of the Write Spreadsheet File block to the input of the Download Files block.
22. Select the For Each block.
23. Select List as Item Type on the right side.
24. Select Mixed for the second Item Type on the right side.
25. On the right side at List select "Custom".
26. The logic editor opens.
27. Drag and drop the spreadsheet workbook block from the left side to the center of the Logic Editor.
28. On the right side, under Libraries, we now drag and drop the "System Object" element into the center of the Flow Editor.
29. The "System Object" dialog opens.
30. Scroll Down and select "Split Spreadsheet Workbook" on the left side and confirm with OK.
31. On the right side, under Libraries, we now drag and drop the "List" element into the center of the Flow Editor.
32. The "List" dialog opens.
33. Select "Get First Item From List" on the left side and confirm with OK.
34. On the right side, under Libraries, we now drag and drop the "System Object" element into the center of the Flow Editor.
35. The "System Object" dialog opens.
36. Scroll Down and select "Split Spreadsheet" on the left side and confirm with OK.
37. Now connect all outputs to the corresponding inputs.
38. Close the Logic Editor by clicking close in the upper right corner.
39. Select the For Each block.
40. Click on the "Edit Sub flow" Button below the for each block.
41. On the left side, under General, we now drag and drop the "Logic" element into the center of the Flow Editor.
42. Select the "Logic" block and click on the "Edit" button.
43. Drag and drop the Item block from the left side to the center of the Logic Editor.
44. On the right side, under Libraries, we now drag and drop the "List" element into the center of the Flow Editor.
45. The "List" dialog opens.
46. Select "Insert List Item" on the left side and confirm with OK.
47. Connect the output of the Item block to the list input of the Insert List Item block.
48. Now we want to add to the existing data from our imported Excel spreadsheet a new column at index one with the value "Active User" for each row.
49. On the right side, under Libraries, we now drag and drop the "General" element into the center of the Flow Editor.
50. The "General" dialog opens.
51. Select "Value" on the left side and confirm with OK.
52. Select "Number" as "Type" and one as value and confirm with OK.
53. On the right side, under Libraries, we now drag and drop the "General" element into the center of the Flow Editor.
54. The "General" dialog opens.
55. Select "Value" on the left side and confirm with OK.
56. Select "Text" as "Type" and the string "Active User" as value and confirm with OK.
57. Connect the output of the number value block to the index input of the Insert List Item block.
58. Connect the output of the text value block to the Item one input of the Insert List Item block.
59. Close the Logic Editor by clicking close in the upper right corner.
60. Navigate back in the Flow Editor by clicking back in the left upper corner.
61. Select the Write Spreadsheet File block.
62. On the right side at Spreadsheet Workbook select "Spreadsheet Workbook".
63. On the right side at Filename select "Enter Value" and enter a filename.
64. Select the Download files block.
65. On the right side at Source One select "Spreadsheet File".
66. Close the Flow Editor by clicking close in the upper right corner.
67. Close the Page Editor by clicking Close in the upper left corner.
68. Now we can start using our application.
69. The Excel file we import looks as follows.
70. In our flow, we have defined that we will insert an additional column after column A and fill it with the text value "Active User".
71. Clicking on the "File Upload" button opens a file chooser dialog.
72. Select the Excel file in the file system and confirm it with open.
73. Clicking on the "Save modified Excel file" generates our modified new Excel File.
74. The Downloads dialog with the automatically created Excel file is displayed.
75. Clicking on the Excel file will open a save-to-file system dialog.
76. Confirm with save.
77. Now we can see our newly created Excel file with the inserted column and values.