Exporting people to CSV file and adding user pictures

The program users-in-course.py Download users-in-course.py

produces a spreadsheet of information about users in a course. It is called as:

users-in-course.py course_id

The program outputs a CSV file with column headings = ['user_id', 'role', 'course section', 'sort last_name', 'sort first_name', 'e-mail', 'html_url', 'picture']

Import (using the Text IMport Wizard) into a blank Excel spreadsheet using the Data->Get External Data->From Text.
Make sure to specify that the import and specify that the File Origin is "UTF-8".

If you want to sort by section number or some other fields, do so now.

In Excel one can selected the picture column  (actually the URL to the user's icon/picture, then you can select this column and they use the following Vistual Basic subroutine (The code below is adapted from the response by "teylyn" Apr 18 '13 at 21:11 on the page http://superuser.com/questions/584650/how-do-i-load-external-images-into-excel Links to an external site.):

Sub InsertPicturesViaURL()
    For Each cel In Selection
        cel.Offset(0, 1).Select
        ActiveSheet.Pictures.Insert(cel.Value).Select
    Next cel
End Sub

The above will insert the pictures. Now you can use the Excel menu "Find & Select" -> "Go To Special" choose "Object". This selects all of the picture objects, now you can use the "Picture Tools" menu to set the size of the images (for example to 1 cm). Now use the Cells Format menu to set the row height to 75.

You now have small versions of the picture in spreadsheet.

Note that since the actual graphical images are in an overlay, they will not sort with the entries in the spreadsheet.

However, if you use the following VBA code, the images will move when you sort and the will a be sized to 100 units high (for more information about these changes to the code see http://stackoverflow.com/questions/12936646/how-to-insert-a-picture-into-excel-at-a-specified-cell-position-with-vba Links to an external site.):

Sub InsertPicturesViaURL()
    For Each cel In Selection
        cel.Offset(0, 1).Select
        ActiveSheet.Pictures.Insert(cel.Value).Select
        Selection.Placement = 2 'xlMove
        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.Height = 100
        Selection.PrintObject = True
    Next cel
End Sub

The resulting spreadsheet will be similar to the image below:

Also note that if you have created sections in a course, you will get two outputs for each user: one for the course and one for the specific section that they are in.

---

Note that there are other and better methods to select all of the pictures, see http://professor-excel.com/how-to-select-all-pictures/ Links to an external site. for more information.

The page http://superuser.com/questions/584650/how-do-i-load-external-images-into-excel Links to an external site. also has a description of how to add the image as a comment upon a cell.