【Scheduler】Is it necessary to have Excel installed?

# Introduction #
I was not sure that I need Microsoft Excel installed on the application server, when a scheduled task uses an Excel workbook for reading data.
Therefore, I took place a test.

# Preparation #
I prepared the following things;
+ A Secure Folder where the excel workbook is placed
+ A database and a table where the data is written to
+ A Excel Workbook which is the data source read from
+ A Sharperlight Scheduled Task

### Secure Folder ###
Created a folder C:\QiitaSampleTemp\, and register the folder to Sharperlight Site Setup as Secure Folders.
**Secure Folders** in Site Setup must be configured.
By default, Sharperlight doesn't access to any private folders on the application server with security reason.
Multiple folders can be specified by using a pipe separater like this.
```
C:\QiitaSampleTemp\|C:\QiitaSampleCopy\
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1968b06d-b2cd-7600-1aa1-5f12eb6a7a60.png)

### Database and Table ###
I prepared a database and a table with Sharperlight Materialised Query (MQ).
Because it is created into the same database server where Sharperlight system database is in, and Sharperlight Engine can read and write data from/to the database.

##### Database #####
I opened Sharperlight Application Menu, and selected `Materialised Query`.
Created a new MQ database by clicking the New icon. Sharperlight engine knows about where the new MQ database should be created as it uses the same database connection as the Sharperlight System database.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/63d0497b-409c-1083-e6b2-8c62e78fb22a.png)

:::note
The code says **Qiita** so the physical database name must be **SLMQ_Qiita**. 
:::

##### Table #####
Started creating a table, once the MQ database was created.
Chosen the newly created database in the list, and click `Edit Tables` button.
Right-clicked on the list of tables, and selected `New`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/02ed88b5-a952-b221-b995-b6e6b3763a86.png)

:::note warn
You can design a table by using a query, because MQ is designed to be refreshed by the defined query at a scheduled time and its frequency.
However, you can lock it to prevent from being refreshed.
:::
See how I designed a query.
I wanted to create a just empty table without feeding any data so used Sharperlight Expressions to design the columns.
However, we need at least one record needs to be returned to populate a table into the MQ.
Therefore, I used `System` as Product, and `User Accounts` as table so that it returned just a record.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/27c52765-e477-ae4b-24a3-26cc7afcfa10.png)
Then I designed three columns for the table.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2237921e-9576-6849-56fb-2424e7fc2a49.png)

This is the definition for **Code** column.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/52ed04b2-6249-cf87-0ac6-4d446c49c4f1.png)
For **Name** column.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/98ef8d21-d933-21e2-617a-157e5dbecdc0.png)
For **Address** column as well.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0d166072-7b7d-bbd2-41fe-4279a90e0841.png)

Clicked `Preview` button to check it.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/92c91b9f-251c-be44-67db-fd8022ca5ded.png)
It looked fine, clicked `OK` to generate a table.
It's created.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/669de9b8-e210-2864-f8a3-bca42104882b.png)
Double-clicked it to open, and checked and gave it a use-friendly name.
Amended the `Code` and `Title` like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/022d8521-eed1-63f4-7340-f72612cdf58e.png)
Locked it to prevent from being refreshed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0d0792c4-3bac-d5b3-be0c-14ba5e82a817.png)
As you can see, the columns were created well, and their data type and length were set as per design.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1e8e673e-982b-2e4f-e2d2-bad75d238e8c.png)
The table name was still a GUID so renamed it.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7cec4c22-bd78-36ea-2f18-2b67e4d00dc6.png)
Renamed as `QiitaSample_Contacts`
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3ab6d609-309c-3e26-51fe-67915a554bf1.png)
One more important attribute needed to be amended. I write data into the table so the table must be `Writeback` enabled.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/145219a2-1f1e-fade-34cc-f24fad88ed78.png)

Clicked `OK` to apply the changes.

I confirmed that the table has been created into the MQ database (SLMQ_Qiita) with SQL Management Studio.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f8cddbbf-6f62-c27c-ce08-8354c6feffd3.png)

### Excel Workbook ###
I created a list of contacts' name and their addresses as a table.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3dc8235f-2337-1304-65e7-1954c78d8dac.png)
Placed it into the folder configured as Secure Folder on the application server.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8b2a37c7-8a87-402a-c9c3-f6e5d7412919.png)

### Scheduled Task ###
I defined a scheduled task which has the following two actions;
+ SQL Statement: This truncates the table first
+ Writeback: This reads the data from the specified excel workbook, and insert to the target table in the specified database

##### Action: SQL Statement #####
Added `SQL Statement` to clean the table.
Made sure the right connection to the database.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/622b1215-982e-ae7a-7f2c-78f33a1202df.png)

##### Action: Writeback #####
Selected the right database (Product) and the table, Set **Excel** as the source type, specified the path and the name of the Excel workbook created above and set its sheet name.
Column mapping was not required as they are exactly the same between the table and the Excel sheet in this case.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/338be9c3-948a-4804-c870-3ab11cea9d51.png)

Saved and closed it by clicking `OK` button, and closed Scheduler as well.



# Verification #
Opened Scheduler from Sharperlight Application Menu, and selected the task and executed manually.
I checked the table by issuing the sql statement 
SELECT * FROM [SLMQ_Qiita].[dbo].[QiitaSample_Contacts]
Then I confirmed the data was written well.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0836a3a5-f1c8-5d6b-5f68-419a606b3221.png)


# Conclusion #
I confirmed that Microsoft Excel is not required on the Application Server, even when a Sharperlight Scheduler writeback task is defined with an Excel workbook as the data source.