= Excel Importer =
[[TOC]]

Excel can be used to build a more complex front-end for importable spreadsheets than is possible with just CSV.

The Excel importer currently will import a worksheet called '!SahanaData', or the first worksheet if none with the correct name exist.

Normally this is a hidden worksheet with a user-visible UI worksheet where data is entered & 1 or more lookup worksheets.

== Data Validation ==
- Data | Data Validation
- Custom | Formula:

Email Address:
{{{
=ISNUMBER(MATCH("*@*.?*",A2,0))
}}}

Phone Number (10 or 11 digits, however loses leading zero as can't then format as text):
{{{
=AND(ISNUMBER(A2),LEN(A2)>9,LEN(A2)<12)
}}}
== Lookup Lists ==
Simple L1s
- define a Named Range
- select the column
- deselect the header
- Data | Data Validation
- List | Source:

{{{
=L1s
}}}

Simple L2s based on a single L1
* NB Dependent Lists must be sorted by dependency
- L2_Start is the column header on the lookups sheet
- L1_Selected is the single field where L1 is selected (make sure that the row has no $ in front of it)
- L1s_for_L2 is the list of L1s to the left of the list of associated L2s
{{{
=OFFSET(L2_Start,MATCH(L1_Selected,L1s_for_L2,0),0,COUNTIF(L1s_for_L2,L1_Selected),1)
}}}

Simple L3s based on a single L2
* !ToDo: Multi-level lookups (Where L2s not Unique!)
{{{
=OFFSET(L3_Start,MATCH(L2_Selected,L2s_for_L3,0),0,COUNTIF(L2s_for_L3,L2_Selected),1)
}}}

Simple L4s based on a single L3
* !ToDo: Multi-level lookups (Where L2s not Unique!)
{{{
=OFFSET(L4_Start,MATCH(L3_Selected,L3s_for_L4,0),0,COUNTIF(L3s_for_L4,L3_Selected),1)
}}}

Simple L4s based on a varying L3 in row
- same as above, set L3_Selected = UI$C6

To clear dependent entries when a Higher Lx is changed then can use the technique outlined here (although needs modification as we're not using tables):
 * https://www.contextures.com/exceldatavaldependindextablesindirect.html#clear
 * Full, modified, example: https://github.com/sahana/eden/raw/master/static/themes/CumbriaEAC/xls/Client_Registration.xlsm
== Multi-Select Dropdowns ==
* http://www.contextures.com/excel-data-validation-multiple.html
{{{
Private Sub Worksheet_Change(ByVal Target As Range)
' Developed by Contextures Inc.
' www.contextures.com
'code runs on protected sheet
Dim oldVal As String
Dim newVal As String
Dim strSep As String
Dim strType As Long

'add comma and space between items
strSep = ", "

If Target.Count > 1 Then GoTo exitHandler

'checks validation type of target cell
'type 3 is a drop down list
On Error Resume Next
strType = Target.Validation.Type

If Target.Column = 3 And strType = 3 Then
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  If oldVal = "" Or newVal = "" Then
    Target.Value = newVal
  Else
    Target.Value = oldVal _
      & strSep & newVal
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub
}}}

== Dropdown or manual selection ==
* [https://support.office.com/en-us/article/add-a-list-box-or-combo-box-to-a-worksheet-in-excel-579e1958-f7f6-41ae-ba0c-c83cc6e40878 ComboBox]

== Examples ==
* [http://eden.sahanafoundation.org/raw-attachment/wiki/UserGuidelines/Importer/Excel/Volunteer%20Import%20Template%20(mg).xlsm Volunteers for Malagasy Red Cross]

== Using Python ==
It is possible to use Python to develop in Excel, e.g.
* [http://docs.xlwings.org/index.html XLWings]
 * Can deploy standalone folders

However, is it worth the deployment complexity for the ease of coding?

NB Office 365 can be downloaded with a free month's trial to allow developing a template

== See Also ==
* [wiki:BluePrint/Importer/Excel BluePrint for Excel Importer]
* [wiki:S3#ExportImport DeveloperGuidelines for Imports]
* https://www.ablebits.com/office-addins-blog/2014/09/24/excel-drop-down-list/
* https://www.ablebits.com/office-addins-blog/2014/09/30/dependent-cascading-dropdown-lists-excel/
* http://www.excelcampus.com/tables/dependent-drop-lists/
* [https://trevoreyre.com/portfolio/excel-datepicker/ Popup Date Picker]
* [http://www.appspro.com/Utilities/CodeCleaner.htm VBA Code Cleaner] (32-bit Excel only unfortunately)