Generate an Excel file based on a quesionaire

nellocono

Hello,

I'm not a programmer myself but did play with asp.net in college. Anyway, I have a requirement at work to create an application that a user can answer a list of questions (using radio buttons or drop downs etc) and when click submit I would like to generate an excel file with a list of materials and prices.

Obviously there are many ways to achieve this so I am really looking for some idea as to the easiest way to do it. Is there any link online that has a guide on this for example.

Would really appreciate your thoughts on this

srsly78

Just spit the answers into a CSV (comma seperated value) file. Excel can open that straight up as a simple spreadsheet, or another sheet can import the values. Then some simple macro stuff can use that input to generate fancy stuff.

There are probably very easy api calls to write directly to a sheet in asp.net but I haven't used this.

1,sample,answers,are,here
2,even,more,sample,answers

TonyStark

Use something like this:

http://www.google.com/google-d-s/forms/

Aswerty

The first thing to pop into my head was to just do what srsly78 said.

If you need to create a .xls or .xlsx then you can use the Jet.OLEDB driver. The following article will give you one approach of how to use it http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB. Note that that you will have to compile the application as 32-bit using this driver. I believe the Ace.OLEDB driver can be used if you need to compile as 64-bit.

The following is a good resource with regards building the connection string to connect with Excel http://www.connectionstrings.com/excel.

The following snippet of code is from an ASP.NET MVC with C# application of mine that made use of Oledb to connect to an excel file:

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

...............

// where file is file name + file path
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
    file + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

string query = "select * from [Sheet1$]";

DataTable dt;

try
{
    OleDbConnection conn = new OleDbConnection(connString);
    OleDbDataAdapter cmd = new OleDbDataAdapter(query, conn);
    DataSet data = new DataSet();
    cmd.Fill(data);
    dt = data.Tables[0];
    conn.Close();
}

With regards to the rest of the application you can just use some basic html/css with ASP.NET as the back end. I'd advise you to exhaust pre packaged solutions before custom building in any case, see: http://help.surveymonkey.com/app/answers/detail/a_id/272/~/you-can-download-your-data-into-several-excel-spreadsheet-format-options. or http://www.surveygizmo.com/survey-software-support/tutorials./survey-responses-reporting/csvexcel-exports-exporting-your-raw-data-tutorial/

Procasinator

If a basic CSV is satisfactory, go with that (as suggested by srsly78).

If you need something more advanced, the EPPlus package has worked well for me.

Using OLE DB will treat excel as a database, as the name suggests. You won't be able to do any visual things like colour or similar. Just basic CRUD operations.

Aswerty

I've not come across EPPlus before, it looks like a fantastic package and it's LGPL to boot!

DublinDilbert

Google docs has an online survey type form, which i'm sure could be used. And it puts the results into a google docs spreadsheet.