Digital Media Manipulation Using Spreadsheets

John Cigas, Rockhurst University, cigas@acm.org

Meta Information

Summary	Digital Media Manipulation Using Spreadsheets - Let students see and manipulate digital images and sounds using a spreadsheet framework.
Topics	Digital storage (sampling, quantizing, formats), spreadsheet formulas, and spreadsheet manipulations
Audience	Appropriate for CS0. Basic familiarity with a spreadsheet is helpful, but not essential.
Difficulty	This is an easy assignment, though it may take some students a few tries to get through all the steps correctly the first time.
Strengths	Students manipulate numbers to change images and sounds. Not only that, but they see the lists of numbers that they are manipulating in the spreadsheet! Spreadsheets are also a familiar tool to most of them.
Weaknesses	Often there are a lot of steps to each assignment. This is not a problem when done in the lab with an instructor present, but may be a problem if students are expected to complete everything on their own.
Dependencies	The image exercises require 1) an image manipulation tool, like the GIMP, that can create and read ASCII .ppm image formats, 2) a short program to reformat the output from the GIMP into 3 columns, and 3) any spreadsheet that can read/import ASCII text files. The sound exercises currently only work using Microsoft Excel, since the macro to create a .wav file is written in VBA. We also use the program Audacity to visualize sound waves, though this isn't exactly essential to the assignment.
Variants	Given the basic framework for manipulating images and sounds, many other extensions and modifications are possible.

Manipulating Images with the GIMP and Excel

The idea behind this assignment is to give the students an image that is encoded in ASCII format (.ppm for color or .pgm for gray scale). This can be viewed as an image using the GIMP or directly manipulated by any spreadsheet that can import ASCII text files. My students then use Excel to make simple changes to the image, like turning off all red and green color components of the image, and doing computation to generate a gray-scale image from the original color one.

One of my goals with spreadsheet assignments is to give students enough data so that they aren't tempted to do all the work by hand and type in the answers instead of using formulas (or retype formulas instead of using absolute addresses). This seems common with a lot of the toy spreadsheet assignments we give.

Many other manipulations are possible. If you are willing to forgo color, then you can use a .pgm gray-scale encoding, and instead of three columns of numbers, have the text file in an actual 2D grid. This allows for easy cropping and other such manipulations. It is also possible to write filters for the image, such as blurring or brightening, but this seems better with a macro, which is too much for my CS0 students to be fooling with.

Handouts

Assignment (for the student)

Sample Screenshots



Original Image	Only Blue Component	Gray image from maximum pixel value

Instructor Info

From the instructor's end, it requires just a little bit of initial setup.

Using the GIMP, save an image using File/Save As, expand the Select File by Type options, and then choose PPM image. You will then get a new popup window, and select Data Formatting - Ascii.

This file will have a small header which indicates the type of file, the number of columns and rows, and finally the largest individual pixel value. This header is followed by each pixel's red, green, and blue value, all in a single column of ASCII numbers. To facilitate our laboratory exercises, we have a short program that reformats this file into the header and then 3 columns of values, one for each of the RGB components.

From the GIMP

After reformatting

P3
# The GIMP
256	169
255
# Do not edit above this line!! 
# Below this line are columns of Red, Green and Blue values.
118	111	21
158	152	62
92	106	6
...

This reformatted file is what we provide to the students.

Manipulating Sound with Excel

The purpose of this exercise is to have students use a spreadsheet to generate discrete points on a sine wave, then save those points as a sound file, and finally listen to the sound. After changing the frequencies and generating a few different notes, I have them compute the average values of all their notes, which forms a chord that they also listen to (and view visually).

For spreadsheet practice, I also have the students modify the original spreadsheet so that their formulas use absolute addresses.

The slick part of this is an Excel spreadsheet from Demos with Positive Impact that has a macro for creating a .WAV file from a column of data points. I've made several modifications to the macro and spreadsheet to fix some bugs, change the scenario, shrink the overall size of the spreadsheet, and make the creation of time points automatic and consistent with the sampling rate for the .wav file.

Handouts

Assignment

Screenshots

Creating data points and then generating sine waves

One easy addition to this spreadsheet is to graph the data and view the resulting waveform directly within Excel.

Instructor Info

The Excel spreadsheet is downloadable and ready to use. The generated sounds are 1.5 seconds long, with 4,098 samples per second. If you want a different length or sampling rate, then

Start the Visual Basic editor, Tools/Macro/Visual Basic Editor
Change the values of SoundDuration or SampleRate
Save the changes
Run the macro, MakeTable, Tools/Macro/Macros, then click on the Run button

This will generate a new grid within the spreadsheet and fill it with the correct t values.

Also note that the sounds are stored in the spreadsheet itself, and they are cumulative - I don't have a clue how to purge them. This means that students may end up with files that are 2-4 Mbyte by the time they are finished, if they try a lot of things. It also means that I keep a clean copy of my original, and always use clean copy when making any modifications. This keeps the file size from getting out of hand.

Extra info about this assignment: