asked on

C# Excel Lookup

Hi

I currently have a web form built in Visual Web Developer with c# code behind. The user fills in the form and when they click submit the results are wrote to an Excel spreadsheet.

We are getting issues were users are submitting the form multiple times which is causing problems when analysing the spreadsheet data.

What I would like to do is have the form 'lookup' the spreadsheet and see if there has been a submission in the past 4weeks. If so it would then display that information to the user saying something like 'You cannot submit the form because an entry was made on dd/mm/yyy'

Could anyone tell me if this is possible? if not the excel lookup maybe a constraint on the submit button to allow each user to only submit once a month?

All ideas are welcome!

Thanks

Here is the code behind;

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Templates
{
public partial class WebForm36 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void Submit_Click(object sender, EventArgs e)
{
string[] username = Request.ServerVariables["LOGON_USER"].ToString().Split('\\');
globalfunctions getAttribute = new globalfunctions();

//Build comma seperated line
string responseline = username[1].Remove(3);
responseline = responseline + "," + getAttribute.getADattribute(username[1], "displayName");
responseline = responseline + "," + completedBy.Text;
responseline = responseline + "," + jobTitle.Text;
responseline = responseline + "," + checkComp.Text;
responseline = responseline + "," + DateTime.Now;

//append to csv file
StreamWriter sw = File.AppendText("e:\\results\\StockRotation4W.csv");
sw.WriteLine(responseline);
sw.Close();
Response.Redirect("thanks.aspx");
}
}
}

AndyAinscow

Are you writing to an excel file as your question asks or a csv file as your code shows?

Gaz124

ASKER

sorry, Yes its a .csv

AndyAinscow

So is the issue the csv file being overwritten with different data that is then analysed, appended with extra data or that excel should not reload a csv file during a certain time period since the last load/analysis ?

Gaz124

ASKER

Issue is that users are submitting data to the .csv two or three times a month and should only be once.

I want to be able to look into the .csv, see if there has been a submission for that user (coloum name is Store_number) and if so stop the

StreamWriter sw = File.AppendText("e:\\results\\StockRotation4W.csv");
sw.WriteLine(responseline);
sw.Close();
Response.Redirect("thanks.aspx");

and Redirect to another page '"Duplicate.aspx"

Thanks

AndyAinscow

OK.
When the user clicks the submit button.
Use StreamReader to read the complete csv file in line by line.
In a loop for each line check if the details have already been appended, store in a boolean variable
Close the StreamReader.

Now check the boolean and have something like:

if(AlreadyWritten)
{
Response.Redirect("Duplicate.aspx");
}
else
{ //append to csv file
StreamWriter sw = File.AppendText("e:\\results\\StockRotation4W.csv");
sw.WriteLine(responseline);
sw.Close();
Response.Redirect("thanks.aspx");
}

Gaz124

ASKER

"OK.
When the user clicks the submit button.
Use StreamReader to read the complete csv file in line by line.
In a loop for each line check if the details have already been appended, store in a boolean variable
Close the StreamReader"

This is the part i am unsure about could you please explain a little more or show some example code?

Thanks

AndyAinscow

From the help files:

using System;
using System.IO;

class Test
{
public static void Main()
{
try
{
// Create an instance of StreamReader to read from a file.
// The using statement also closes the StreamReader.
using (StreamReader sr = new StreamReader("TestFile.txt"))
{
String line;
// Read and display lines from the file until the end of
// the file is reached.
while ((line = sr.ReadLine()) != null)
{
Console.WriteLine(line); <<-------------------- HERE
}
}
}
catch (Exception e)
{
// Let the user know what went wrong.
Console.WriteLine("The file could not be read:");
Console.WriteLine(e.Message);
}
}
}

at HERE you parse the line to extract the information to identify if it is a duplicate

Gaz124

ASKER

I have already found that but I dont know how to check against the file, which im guessing is where you have place HERE.

Sorry it seems im not explaining too clear on this, I havent been using C# long.

AndyAinscow

You have this for writing
//Build comma seperated line
string responseline = username[1].Remove(3);
responseline = responseline + "," + getAttribute.getADattribute(username[1], "displayName");
responseline = responseline + "," + completedBy.Text;
responseline = responseline + "," + jobTitle.Text;
responseline = responseline + "," + checkComp.Text;
responseline = responseline + "," + DateTime.Now;

What you need to do is at 'HERE' take the line of comma separated values apart and check for a 'duplicate'

Gaz124

ASKER

OK but how do i do that?

AndyAinscow

Here for example (you have to know what is a duplicate):
https://www.experts-exchange.com/questions/26169514/parse-csv-file.html

Gaz124

ASKER

Dont really understand the example, cant see how it relates because i need to check user input against exsisting data before insertion

AndyAinscow

That splits a line into the values separated by commas - so you can compare the values in the csv file with the values the user is attempting to append (because i need to check user input against exsisting data before insertion) which is what you want to do.

Gaz124

ASKER

How would i do that?

ASKER CERTIFIED SOLUTION

AndyAinscow

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial