C++ and Excel

Kai · 11-06-2004 11:46am #1

Hello,

I have an excel sheet and i need to write a C++ program that will take in the entries in a particular column and then output them to a Html page as links.
eg
excel file has the following layout:
name date type other

so i need to be able to read all the name entries and out put them as individual links in a html file, for example the name frugu :
<html>
<body>
<a href="http://localhost/search.cgi?name=frugu">frugu</a>
......

Its for work as i have a list of a couple of hundred items that i have to sequentially open and fix and if i could get something like this going then it would save me a hell of a lot of time.

Also Excel can be changed to CVS or Access. whichever is handier.

Where the hell do i start ?

Kai · 11-06-2004 11:53am

Heres what i have so far but to be honest its a mess as im after trying to use a function i found on the net to do it and change it. I admit defeat at this so ill need some advice. Current program doesnt work at all the way i want and causes errors.

Im using Dev-C++ and i cant seem to get Strings to work either ?

[PHP]
int ConvertCSVToHTML(char CSVfn[],const char HTMLfn[])
/*
purpose: converting a CSV file into an HTML file that can display the information
inputs:
CSVfn = file containing the table data
HTMLfn = file name to store the information
outputs:
stores an HTML table in a file named HTMLfn
*/
{
FILE *CSVfp; // pointer to the CSV file
FILE *HTMLfp; // points to the html file
char c; // temperarily stores a character from the CSV file
char EndDataBlockChar; // indicates the character used to end a block of data
int count;
char g[100]; // stores words before being inputted to the html file
int column;

count=0;
column=0;

CSVfp = fopen(CSVfn,"r"); // try to open file in reading mode
if (CSVfp==NULL) // if problem opening CSV file
{
printf("unable to open file '%s' for reading\n",CSVfn);
return 0; // indicate failure
}
HTMLfp = fopen(HTMLfn,"w"); // try to open file in writing mode
if (HTMLfp==NULL) // if problem opening html file
{
fclose(CSVfp);
printf("unable to open file '%s' for writing\n",HTMLfn);
return 0; // indicate failure
}
fprintf(HTMLfp,"<HTML>\n"); // start of html document
fprintf(HTMLfp," <HEAD>\n"); // start of head section
fprintf(HTMLfp,"document created with CSV to HTML converter\n");
fprintf(HTMLfp," <TITLE>%s</TITLE>\n",ShortenFileName(CSVfn));
// note: CSVfn has been changed now and no longer stores the file name
fprintf(HTMLfp," </HEAD>\n"); // end of head section
fprintf(HTMLfp," <BODY>\n"); // start of body of document
fprintf(HTMLfp," <TABLE Border=1>\n"); // start of table
fprintf(HTMLfp," <TR>\n <TD>"); // start of table row and piece of data

for(count=0;feof(CSVfp)!=true;count++) // loop through all characters in the CSV file
{
c =fgetc(CSVfp); // read character from file

if (c=='\n')
{
fprintf(HTMLfp,"</TD>\n </TR>\n"); // end the current row
fprintf(HTMLfp," <TR>\n <TD>"); // start a new row and piece of data
}
else if (c==',')
{
if(column%4 == 0){
fprintf(HTMLfp,"<a href='http://localhost/order.pl?name=%c'>%c</a>",g,g);
}

fprintf(HTMLfp,"</TD>"); // end the current piece of table data
fprintf(HTMLfp,"<TD>"); // start a new piece of table data
strcpy(g,"");
}
g[count]= c;

} //CLOSE OF LOOP

fprintf(HTMLfp,"</TD>\n"); // end of table data
fprintf(HTMLfp," </TR>\n"); // end of table row
fprintf(HTMLfp," </TABLE>\n"); // end of table
fprintf(HTMLfp," </BODY>\n"); // end of body section of document
fprintf(HTMLfp,"</HTML>"); // end of html document
fclose(HTMLfp); // close the html file
fclose(CSVfp); // close the CSV file
return 1; // indicate success
}
[/PHP]

PhilH · 11-06-2004 2:30pm

I think there are probably easier ways to solve this problem. If you were doing it just once, I would recommend
1) create a new book, with 'AAA' in the first column
2) copy the name column into the second column
3) put 'BBB' in the third column
4) copy the name column into the fourth column
3) put 'CCC' in the third column

Save the thing as plain text and do three search-and-replaces to convert

AAA to <a href="http://localhost/search.cgi?name=
BBB to ">
CCC to </a>

Add the html and body tags by hand. It's poor man's scripting in Windows, but it would probably do the job for you. I reckon C++ is the wrong tool for this job.

If you need to do this more than once, it's probably easier to write to VBA macro to do it for you.

PHiL

sirpsycho · 11-06-2004 2:58pm

U should export the spreadsheet to ms access and then use an ASP page to read all the values into a html page. u could create a brinkster.com account and upload your database there and create your pages. should be handy enough.

Kai · 11-06-2004 3:35pm

Originally posted by sirpsycho
U should export the spreadsheet to ms access and then use an ASP page to read all the values into a html page. u could create a brinkster.com account and upload your database there and create your pages. should be handy enough.

not an option as its internal work stuff, we have a server here that could do it but to be honest there has to be a way of formatting the files more easily than uploading to a server and messing with ASP.

sirpsycho · 11-06-2004 4:50pm

why dont u just create the links in excel then export to a txt/html page?

Say Column A is where u have Name...

in another column u could put:
=CONCATENATE("<a href=http://localhost/search.cgi?name=",A2,">",A2,"</a>")

and then just fill down the whole column.

C++ and Excel

Comments