8 replies to this topic

[shinygold]

Hi there all,

Thanks in advance for your help with this.

I have a summer assignment to create a program that analyzes a data from an Excel spreadsheet. I'm familiar with a few programming languages (I would still list myself as a beginner, though), but I actually don't have that much experience with Excel and its capabilities.

I was thinking of exporting the spreadsheet data as an xml file and creating a simple VB program to analyze and do calculations on it. Does this sound like something VB is suited for? I'm a little lost on what would be the best way to do this, I'd appreciate any input you might have.

By the way, I know that Excel has built-in support for performing data analysis, but I was specifically told to create a separate program.

[marwex89]

VB should be fine, I've done something similar (in 2 different languages, actually, one of the being VB6), though I think we exported the spreadsheet data as tab-separated data in text files. I'm not familiar with the details of VB.NET, but file parsing should really not be a problem. Doing calculations on the data can however be an issue, if it needs to be done very fast? I doubt that is an issue in a summer assignment, though..

[WingedPanther]

It will depend a little on the types of calculations to be done. Exporting the data to CSV may be a useful first step, though I would think VB.NET would be able to read directly from Excel.

[marwex89]

Directly from Excel files or from an open spreadsheet? What is OP's assignment requirements for this?

[shinygold]

Thank you for your answers.

marwex89 said:

Directly from Excel files or from an open spreadsheet? What is OP's assignment requirements for this?

You know, I don't think it even matters to him. I spent a few days copying the data from a hand-written spreadsheet into an Excel file since I figured that would be easiest. I can transfer it to something else if it's easier.

There really aren't any requirements besides what I listed. The description was something like "Put the data into a spreadsheet, and then I need a way to get averages and stuff. Make it a separate program." Pretty vague. I think I basically know what he wants, though. It's data about the number of people using a resource over a period of time, and he needs to see averages.

My question is just about "How should I export the data and what language should I use to write the application?" It feels like it should be simple, I'm just not quite sure what I'm doing.

[WingedPanther]

After a quick search on Google....
opening an Excel file in VB.NET - VB.NET
How to open or read an existing Excel 2007 file in VB.NET 2005
How to read entire worksheet in an Excel workbook
How to read data from excel using OLEDB
How to insert cell data in an Excel file using OLEDB
How to update cell data in an Excel file using OLEDB

You could also use Java and the POI library.

[marwex89]

I think VB.NET is a good choice, especially after the stuff WP dug up about its possibilities with Excel. Most popular languages will be able to parse (say a CSV) file and do easy calculations based on the data. Computing averages with VB is no problem, that's for sure. I'd pick that, it's fast and simple to program in, and you'll get a lot done with relatively little effort. How to export spreadsheet data? I don't know what would be most easy.. CSV is fine, easy to parse. We used tab-separated, but that was because of compatibility with an older tool.

[shinygold]

Thank you for your responses, guys. :)

[marwex89]

Let us know your professor's response, if you remember :P