【Excel Addin】Writeback data to the database

[Sharperlight](https://www.sharperlight.com/) basically extracts data from data sources (databases, Excel worksheets, API endpoints, etc.), but if you enable write-back settings, data can be written back to the data source . (this is called writeback in Sharperlight)
This time, I would like to use an Excel add-in to write the data on the sheet back to the database.
### Database/Table Preparation ###
I created some kind of Ledger table in an existing test database. I would like to write data from an Excel worksheet to this table.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/203e3fb5-63eb-f08b-c6ac-7bb4b8f4a220.png)
### Preparing the Datamodel ###
First create a data model so that Sharperlight can interact with the data source.
I have already created a datamodel for this test database, so I would like to reflect only the information of the table added this time.
Launch **Studio** from the Sharperlight application menu.
Locate the target data model with the `folder` icon.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/bde5108c-6e69-69a5-c3c6-aba971787931.png)
Select the `Studio Connection` sub-menu of the `Tools` menu and check if it is properly connected to the target database.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/34c1ba56-b7e2-8db0-4666-f08680f3e066.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/38b2cd6c-ed47-d5ec-c5cb-797f742bb402.png)
After checking, select the `Import Tables` menu from the `Schema` menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f94cdb0e-76f4-9b22-0009-56ad7728ebba.png)
Clicking the `Get Tables` button will load the schema information from the connected database. Tables that have already been loaded into the data model are shown in red, new tables that have not yet been loaded are shown in black and ticked.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3e176bbb-6dba-946d-a7dc-be1f5d0779fd.png)
When you press the `OK` button, the ticked table information will be imported into the data model.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4d93f36c-1b0a-6b06-82f1-d6107e6fdd5a.png)
The newly imported table is displayed under the `All Tables` node.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/06ceb97d-bf14-05e6-2f14-4463c290f145.png)
In this state, the newly added table cannot be seen from the query builder etc. Move the new **Ledger** table to the `Table Display` node. You can move it by dragging and dropping.
If you select the **Ledger** table that has been moved to the `Table Display` node, the attribute list of the table will be displayed on the right side. Find the `Allow Table Writeback` attribute in it and set it to **True**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8588e404-3809-1d45-7bfe-d508d0ccaa31.png)
You can now write data back to this table. This is simply a permission to write back to this table, and you can also set permission/non-permission on a user-by-user basis.

Now double-click on the **Ledger** table to display the field list in the center, and select that field to display its properties on the right.
Check the description is correct of those fields.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e81c1bb0-ee7c-db90-bc2a-a3e74abb507f.png)
After all is done, save the data model with the `OK` button.
### Data Preparation ###
Prepare such data on an Excel worksheet.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a49f62be-7150-e6a6-27f5-839519bca56e.png)
It's a good idea to match the column name with the field Description in the data model.
### Create a Writeback Definition ###
Now let's define writeback. Select Writeback from the Sharperlight Excel add-in ribbon.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/aa3f63f4-b52e-8552-7f1d-80f7383517e5.png)
The writeback definition dialog is displayed. Select the datamodel just editted for `Product` and the ledger table for `Table`.
The field list of the ledger table is displayed in the lower `Fields` tab. Here we will map the data and fields on the Excel sheet.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/191848bb-c6ef-b746-1c8f-9de7efd05b52.png)
With the input text field of the `Code` field selected, double-click the column header of the table on the Excel sheet. Then the automatic mapping function will work and you will see a message like this. Select **Yes** to proceed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/bc25aa93-dcdc-523f-c747-a17868fc4ee1.png)
Mapping is set only if the column name of the data table and the description of the data model match.
This time they all match.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e50f9a5c-ecc5-0a4b-60db-4c4c0a895d6b.png)
If there is a field that has no mapping set, with the input text field selected, double-click the header of the column you want to create a mapping on the Excel sheet. Then the same message as before will be displayed, so select the **No** button this time. That way you can map individual fields.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/76885fdd-d98b-c537-61ce-20ff1bd8f651.png)
The `Id` field is an IDENTITY field, so the Id is automatically calculated by the database and no mapping is required.
There is a star to the left of the ID. This is because the Sharperlight engine uses the data in this column as a guide to check how many rows the table on the sheet has. However, this feature cannot be set for unmapped fields. So this time we set this function to the `Code` field. 

Select the `Code` field and select **Driver** from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f1944981-8e12-0560-4c65-1499f33579d3.png)
Finally, enter **Ledger** for the writeback name. When you save the definition with the `Close` button, it will be saved with this name.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/017c7214-2bae-6739-4507-871c64d9a0ac.png)
The definition is now complete.
### Verification ###
Press the `Preview` button to check if the engine is picking up the data correctly according to the mapping settings.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/596a2205-ea5e-1586-b4a8-88981603e47b.png)
A preview and log are displayed. If you look at the log, you can see that no writes have been made. This function is only for checking whether the data is correctly picked up.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/699107ef-5eb4-2c0a-3b19-23cc7f21f86c.png)
The `Validate` button is a data collection check without preview. Only logs are displayed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2a17a854-1b0e-3c54-6818-c3374e2fdb2c.png)
Since it was confirmed that the data was successfully picked up, execute the write back with the `Execute` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ac930571-1d75-ade5-9cc4-a01d5d7f6f0f.png)
I could see the logs and it ran fine.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0cdda741-3bb9-8d35-11ab-da2601db3394.png)
Let's check the database table. Writeback was done well. :thumbsup_tone1:
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e1f3d8bf-fc1f-6b83-944a-574dcfbfbf14.png)
### Updating and Inserting Data ###
Update the data on the Excel sheet.
Add a new record with code **S012** at the bottom line. Also update the Payment amount and Balance of the record with code **S006**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/11c9ff27-d2e4-3b47-db22-c43017fbe89b.png)
If you write back again in this state, new records will be added and changed records will be updated.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/229384c2-4619-58e3-3235-29f6c0fb0372.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d47e4a5d-0385-29ff-62ed-d84abf623b90.png)
### Creating Writeback Button ###
This time, instead of opening the writeback dialog and pressing the Execute button, we'll add a button to perform the writeback.
Select **Buttons**->**Writeback**->**mdWriteBack** on the Sharperlight Ribbon with the cell where you want to place the button selected.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b08e4d5f-42ec-93ae-cd3a-36916d4b931d.png)
A list of writeback definitions will be displayed. Select the definition **Ledger** created this time, and press the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/aa5bf980-ffba-0d38-79ea-b6c92011b037.png)
The writeback button was created.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8d32921f-155d-947f-eb39-24191b91ee2a.png)
Add a new record at the bottom and press this button to perform a writeback.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3cde4f5c-8068-6e9d-2c8f-8d1f3cdadba9.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ebb55497-95d2-2e68-dc54-550cc349abd1.png)
Checked the database immediately and saw it was updated correctly.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5f488c83-8d56-42fd-e3d1-1abda89842fc.png)
This concludes the brief explanation of the Sharperlight Writeback function.

Thank you for reading this article.