Thinking Outside the Spreadsheet Box: Unexpected Uses of OfficeWriter

November 5, 2013 kate

If you leave me alone for too long, I do foolish things like make a mini-paint app that writes the image to 1×1 cells in Excel. And so can you! But before I show you how to make a mini-paint app (the easiest thing you will ever do in your life), let me explain to you what part of me said, “Yeah, Champ, this is a great idea! Who wouldn’t want to make a weird spreadsheet that contains an image made with the background color on cells?” It all began when I was thinking about this awesome idea by my art crush, Evan Roth (*swoon*). He said, “Hey world! I want work to be art,” and decided to make a double-mouse that outputs to an art program on one screen and his regular work on the other. So I figured, why not put some art in Excel too?

Then I got more and more excited as I realized there are actually a ton of cool things you can do…

Use the formulas in Excel as transformations on your picture.
Randomize cell size to introduce weirdness.
Flip the project to input an Excel template and output an image, using Excel calculations and System.Draw to make a simple ray tracer.
Do some stuff with conditional formats, or all those light transform equations you learned in high school for no reason…until now!
Make a mock tool that lets users draw a spreadsheet design and output it to a real spreadsheet! (I thought a lot about this, and it’s not necessarily simple, but it is a really cool idea. If anyone puts that together, please let me know. I mean you, Evan Roth….Call me?)

The Mini-Paint

For the sake of simplicity, I’m showing you the randomized cell idea. I’ve made it an optional setting in this example, but, you know, make it your own and all. Oh, also, get at me with your art! For this app you’ll need WinForms and OfficeWriter.

I started by making a pretty basic app that has some simple actions. The “canvas” is a PictureBox that I save and resave some drawing objects to on each mouse event. The color selector is just 8 flat buttons set to be the color they represent. The shapes are again flat buttons with Wingdings images because I was too lazy to find a relevant .res file with some neat picture icons. Can you guess what the size toggles are? Flat buttons. Generic plus/minus from your plain boring keyboard. Okay. Basic functions.

MouseDown sets a pointer location.
MouseUp nullifies the pointer location.
MouseMoved is a basic drawing function. (You can find a ton of examples, but I like this one from good ol’ SO.)
Color buttons set a global variable, _selectedColor, to the color of the button.
Shape button clicks set a global variable called _selectedShape. I set it to an enum of possible shape options and I use a switch statement in the draw method. Instead of calling DrawLine or DrawRectangle, I call DrawShape(x,y) with x and y being current mouse x and y.

DrawShape is roughly this: Continue reading Thinking Outside the Spreadsheet Box: Unexpected Uses of OfficeWriter →

Events, Featured, Truth in Tech

Weekend Reading: What You Should Be Reading this Weekend

November 1, 2013 elisek

News that should be taking up space on your online reader, Kindle, iPad, or other miscellaneous gadgetry.

Data, Data, Data
Public Health: How Does the UK Compare with Europe? Interactive Map
Source: The Guardian
The Guardian‘s Data Blog produces interactive maps and images to visualize interesting data sets. This week they tackled life expectancies and overall health in the UK relative to Europe.

Gadgets
A Gestural Interface for Smart Watches
Source: MIT Technology Review
Researchers at the University of California at Berkeley and the University of California at Davis are developing a tiny chip that detects gestures in 3D. The chip is called Chirp, and its goal is to bring gesture controls to an array of devices, including wearable technology such as smart watches.

Startups
Circle Raises $9 Million Series A from Accel and General Catalyst to Make Bitcoins Mainstream
Source: TechCrunch
Will Bitcoins make it after all? Circle, a Boston-based company, is a platform designed to make trading Bitcoin and other digital currencies easier. This is among the highest Series A funding received by startups in the digital currency space so far. See who invested and what this means for the company.

Events
One Million Kids Have Signed Up for the Hour of Code. Let the Geeking Out Commence.
Source: PandoDaily
Mark your calendars for December 9th – 15th, as the week of Computer Science Education begins. During that time, Continue reading Weekend Reading: What You Should Be Reading this Weekend →

Office Life, Programming

5 Ways to Make Programming Less of a Nightmare

October 30, 2013 Gabe Goodwin

Programming can be scary. There are plenty of things that can go wrong, from haunting errors to seemingly supernatural result inconsistencies. To help keep the ghosts in the machine from getting the best of you, here are some tips my co-workers and I have picked up over the years.

1) Make a rough draft

Just like writing a paper, programming requires some forethought. When you’re given a new programming problem it is always tempting to start coding right away, especially if you are in a rush. However, taking the time to make an outline of your program first will save you lots of headaches later. Even if it is a seemingly simple problem, think about what methods and helper methods you are going to need, what variables they will take, and how they will interact. It is generally easier to debug code errors than logic errors, so work out the logic as much as possible before starting to code.

2) Listen to music

Having also tried TV, audio books, and silence, the best way I have found to stay productive while coding is to listen to some upbeat music. It seems I am not alone in this opinion, as the internet has many playlists labeled specifically for programming. (In fact, we have a few of our own you might like.) Music drowns out background noise but can easily be tuned out, keeping you focused and helping the time go faster. So to prepare for your next coding marathon just put on your headphones. Before you know it you will be halfway through your code and typing in beat to the music. Continue reading 5 Ways to Make Programming Less of a Nightmare →

Truth in Tech

Truth in Tech Ep. 30: Crowdsourcing a Cure for Cancer

October 29, 2013 elisek

The power of digital crowds extends beyond kickstarting and vigilanteism. As National Breast Cancer Month draws to a close, we take a look at how scientists are using social platforms and gaming technology to further cancer research. Plus: Oregon Ducks, hackathons, and the origin of symbolic pink.

Links:

zalez

Big Data

Creative ways companies are making use of Big Data

October 21, 2013 elisek

From art to cancer patient care, consumer goods to the NBA, Big Data is piling up and these companies are finding ways to make sense of it all. Scroll through the slideshow below to find out how.

Through the above examples of striking visualizations to interactive user experiences, we’re seeing companies and individuals find unique ways to leverage the data and insights being collected daily. How are you seeing Big Data used within your industry? Do you have any examples? Let us know!

Continue reading →

Featured, Meet the Team, Office Life

Meet the Team: Seth

October 11, 2013 elisek

Hello and welcome to our Meet the Team series, in which we aim to give you deeper insight into the minds and personalities of those who make up this eclectic, close-knit group. We are developers, marketers, and technical support engineers, and at work we craft everything from Microsoft reporting APIs to mobile email applications. And outside of work? Let’s just say racing against the machine during hackathons, building architecturally sound beer towers during retros, and paddling down the Charles during the warmer months are simply the beginning.

Meet Seth – our quality control man, subscriber of (and living) This Developer’s Life, most closely personified by a strong spring lager, and powerless to the smile a clever line of code brings.

1. What do you do?

I’m a software engineer on the OfficeWriter team, so I spend my days hunting bugs, implementing cool new features, and trying to come up with new products.

2. What are you listening to right now?

Lake Street Dive – Clear a Space

3. If you could build any app, what would it be and why?

A contact de-duplication/management app that actually works well.

Also a bus/train app that just shows all departure times at the nearest stop…why do I need to choose a stop? My phone knows where I am!

4. When you were 5 what did you want to be and why?

In my early years I wanted to be a nature and wildlife photographer, working for National Geographic. I was always going around taking pictures of landscapes and animals. On our family trips to national parks, my younger brother and I would always get as close as we could to the wildlife (including a grizzly bear once) to get a good photo.

5. If you were a beer what would you be and why? Continue reading Meet the Team: Seth →

OfficeWriter, SSRS, Webinars

Webinar: Ghouls, Goblins, and Data

October 9, 2013 elisek

Learn how OfficeWriter can put data in the hands of your business users.

When: Friday, October 18th at 1 P.M.

In this webinar:

We’re looking at what the average American spends on Halloween each year from costumes to candy to decorations. Importing this data into Excel using OfficeWriter, we’ll see which costumes outrank the rest. Is it Miley Cyrus, your favorite decade garb, or the ever-popular witches and ghosts? We’ll find out, along with what Americans spend on these once-a-year ensembles, mounds of candy, and gobs of decorations. You’ll take away new tricks for creating reports in Excel and maybe a DIY Halloween costume idea or two.

What we’ll cover:

Building a report in SSRS from start to finish
Using charts and sparklines to display your data
Plotting multiple data sets on one graph
OfficeWriter’s new .NET designer ribbon for Excel

Truth in Tech

Truth in Tech Ep 27: The Future of Advertising

October 7, 2013 elisek

Native ads. Brands as publishers. #BreakingBad. Target telling your dad you’re pregnant. Advertising may have lost its 3 martini lunches, but it’s gained a position at the forefront of both high-tech and narrative innovation. On this episode of Truth in Tech, we talk to Hill Holiday‘s Austin Gardner-Smith about the industry’s future, and the cultural and technological shifts behind it.

Want to guest star? Have an interesting tech topic, startup, or story of the week? Email us at elisek@softartisans.com! Or find us roaming the Twittosphere @elisekovi and @clairedwillett.

Women In Tech

Stories from the WIT Trenches: Laura Wallendal

October 4, 2013 elisek

[Stories from the Women in Tech Trenches is a series of posts exploring the personal stories of real women in technology. Every woman in tech overcame, at the very least, statistical odds to be here; this blog series aims to find out why, and what they found along the way. This week we met up with Laura Wallendal, Co-Founder and COO of EdTrips. EdTrips is a travel company centered around the school field-trip process and keeping the focus on learning rather than logistics. With a background in languages and art history, Laura found that fusing the tech world with her passions of travel and education could create meaningful strides in the classroom. Laura shows that the tech trenches are not solely relegated to those within the math and science fields. If reading her story inspires you to share yours, please email me.]

Laura Wallendal

I’m the Co-Founder and COO of EdTrips, an edtech and travel company. Originally from Friendship, Wisconsin, I moved to Boston in 2004 and co-founded EdTrips in 2011 after years of experience in the educational travel industry. At EdTrips, I head up strategic partnerships and business development.

Questions:

1. Can you take us back to your “eureka!” moment—a particular instance or event that got you interested in technology?

It wasn’t until after training for my first mud run obstacle course in 2010 that I became interested in solving problems with technology. I wanted a way to coordinate with other people who had the same interest in crazy obstacle course events. That led me to create my first website. At the same time, I was working in the educational travel industry and seeing a lot of ways that things could be done better—for instance, the basic ways in which travel was booked. Solving a problem using technology for my personal interests gave me the confidence to begin tackling those problems I saw in educational travel, and begin seeing that I could start to solve them.

2. Growing up, did you have any preconceived perceptions of the tech world and the kinds of people who lived in it?

No. I wasn’t really exposed to the tech world until I got to college. Growing up in rural Wisconsin, I was one of the first kids in my grade to have a home computer, and our family had internet access before my elementary school even did, so technology wasn’t a focus in the community. Given my tech-sheltered past, I never imagined starting a tech company.

3. You co-founded EdTrips in 2011, and since it’s been a way to integrate technology within the classroom. Can you tell us a little about EdTrips and the company’s overall mission?

EdTrips is a tool to streamline planning and managing school travel. Whether a field trip, overnight trip or overseas trip, EdTrips is an open and flexible platform that decreases the workload for the trip organizer. It handles everything from payment collection to forms, so travelers and educators can focus on learning, not logistics. It’s our mission to make travel a part of everyone’s education.

4. What was the inspiration behind EdTrips?

After working for a large educational travel company, I moved on to do consulting for 3 small travel abroad companies. Doing sales and business development for both large and small companies, it was evident that trip organizers were in need of a solution to help manage travel online, the way individual travelers have so many tools to use. Doing payment collection by hand, managing accounting and recruiting travelers with spreadsheets and inviting people with emails, phone calls and flyers is time consuming and we knew there had to be a better way.

5. What led you to this career path? When did you first start working with tech? Was it by choice?

After realizing the work I was doing with teachers and schools could be done better with technology, I immediately called a friend from college for advice. He recommended I look into building a platform using Ruby and made introductions to several development shops around town to get quotes and find that sweet spot for our Minimum Viable Product. I gave myself a crash course in a new way of thinking and solving problems. I was hooked! It was absolutely by choice and now I could not see myself doing anything else.

6. Did you experience any personal or systemic setbacks at any point of your academic or professional career?

Yes, I have faced both personal and systematic setbacks in my academic and professional career.

When I was in college, my younger sister passed away of Leukemia and my academic career was severely disrupted. I took a semester off and transferred schools to be closer to home and got my higher education back on track.

When it came to looking for a job, like many recent grads, I found it a challenge to find what was right for me. Eventually I landed in sales for an educational travel company and found something I was truly successful at, enjoyed, and found rewarding. I was the top performing sales person on my team, but found the politics a turn-off. I switched teams and eventually decided to leave altogether and work for myself. Continue reading Stories from the WIT Trenches: Laura Wallendal →

Excel, ExcelWriter, Knowledge Base

How to Sort Locked Cells in Protected Worksheets

October 1, 2013 Stephanie Rosario

Problem

When creating workbooks with protected sheets, it is common to want to allow users to get different views of the data by sorting and filtering, without allowing them to change the data. However, while filtering works fine on locked cells, sorting does not. Even if Sort is enabled in the worksheet protection settings, if a user attempts to sort locked cells when a worksheet is protected, Excel throws the error “the cell or chart you are trying to change is protected and therefore read-only.” There is no obvious way to allow users to sort data on a protected worksheet.

Details

Locked vs. Unlocked Cells in Excel
About Worksheet Protection Properties
How Protection Properties are Affected by Locked Cells

Locked VS. Unlocked Cells in Excel

The purpose of locking a cell is to prevent a user from editing the content of a cell when a worksheet is protected. This means when worksheet protection is turned off, a locked cell is no different from an unlocked cell. By default, all cells in an Excel worksheet are locked.

In order to unlock cells in Excel:

Unprotect the sheet you are working on
Right-click the cell, select “Format Cells”
Select the “Protections” tab
Uncheck the “Locked” checkbox property
Click “OK”

Lock Cells Dialog

It is also possible to unlock specific ranges of cells in Excel using the Allow Users to Edit Ranges feature in the Review tab. Making ranges editable for ranges of cells makes the cells behave like unlocked cells for the most part (e.g. their content is editable). However, the cell’s official locked/unlocked status does not actually change. See this Microsoft Excel article for more details about how to unlock ranges of cells.

About Worksheet Protection Properties

When you protect a sheet, Excel allows you to select from 15 different permissions you want to give to all viewers of the worksheet. You can allow users of the worksheet to:

Select Locked Cells
Select Unlocked Cells
Format Cells
Format Columns
Format Rows
Insert Columns
Insert Rows
Insert Hyperlinks
Delete Columns
Delete Rows
Sort
Use Autofilter
Use PivotTable Reports
Edit Objects
Edit Scenarios

Protect Sheet Dialog

How Protection Properties are Affected By Locked Cells

When a cell is locked, not all worksheet protection properties operate as you’d expect. Four of the properties do not work when a cell is locked:

AllowSort
AllowDeleteColumns
AllowDeleteRows
AllowInsertHyperlinks

This is because in order to use these features the affected cell’s content must be changed. For example, using “Sort” does not just change the order of how the cells are viewed, it actually changes the values of the cells so that they are sorted. Due to this implementation of “Sort,” this worksheet protection property does not work when the cells are locked.

Solution

There are two approaches we can take to solve this issue:

Solution 1: Allow Users to Edit Ranges
Solution 2: Macros

Solution 1: Using “Allow Users to Edit Ranges” to Allow Locked Cell Sorting (RECOMMENDED)

This solution takes advantage of how allowing users to edit ranges makes locked cells behave like unlocked cells.

Step 1: Make cells editable so that sorting will work.

Add cells we want to sort to a range and make that range editable in “Allow Users to Edit Ranges.” This allows users to edit these cells when the worksheet is protected, even if they are locked cells.

Select all the cells you would like the user to be able to sort, including their column headings.
Go to the Data tab and click Filter. An arrow should appear next to each column header.
Go to Review tab-> Allow Users to Edit Ranges
1. Click “New…”
2. Give the range a title.
3. “Refers to Cells” should already contain the cells you want to allowing sorting on.
4. If you want to allow only certain people to sort, give the range a password.
5. Click “OK”

Step 2: Prevent users from editing these cells

When protecting the worksheet, uncheck “Select Locked Cells” worksheet protection property. This will prevent users from editing the cells.

In the “Allow Users to Edit Ranges” dialog:
1. Click “Protect Sheet…”
2. Give the worksheet a password
3. Uncheck the worksheet protection property called “Select Locked Cells”
4. Check the “Sort” property and the “AutoFilter” properties
5. Click “OK”

This solution allows users to use the Auto Filter arrows in the column names or the Sort buttons in the Data tab to sort data. Another benefit is that you have the option of allowing only certain users to sort by giving the range a password. Please note that this range password is separate from the password you set to protect the sheet.

Solution 2: Using Macros to Allow Locked Cell Sorting

To use a Macro to allowed the sorting of locked cells, you will need to make a macro for every sort operation you would like to allow. For example, ascending sorts and descending sorts would have to be written in separate macros. In addition, not all users have macros enabled because they are a security risk. However, the advantage of macros is that you don’t need to configure your template in any special way.

The macro we will write unprotects the sheet, selects a range called “range1,” sorts range1, and protects the sheet again:

Sub Macro1() 
' 
' Macro1 Macro 
' 
' Keyboard Shortcut: Ctrl+d 
'    
     //unprotect sheet 
     ActiveSheet.Unprotect
     //selects range     
     Application.Goto Reference:="range1"     
     //clears sort
     ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear     
     //does descending sort, sets sort properties 
     ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
         SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
         .SetRange Range("A1:A4")
         .Header = xlNo
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
     End With
     //protects the work sheet again
     ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
         False
 End Sub

SoftArtisans