http://qs1969.pair.com?node_id=137812

dragonchild has asked for the wisdom of the Perl Monks concerning the following question:

I'm working on creating on-the-fly Excel spreadsheets that are already converted to HTML. Well, I've already got that done. However, I didn't use CGI;, so I'm trying to get with the program. Except, I'm not that good with CGI (or HTML post-2.0, for that matter), so I'm a little confused.

How would I convert the following code snippet to use CGI? (Assume that all relevant variables have been set to the appropriate values. I'm not looking for advice save for how to remove the embedded HTML.)

print $file "<html xmlns:o=\"urn:schemas-microsoft-com:office:offi +ce\"\n"; print $file " xmlns:x=\"urn:schemas-microsoft-com:office:exce +l\"\n"; print $file " xmlns=\"http://www.w3.org/TR/REC-html40\">\n\n" +; print $file "<head>\n\n"; # In here are a few <META> and <LINK> tags I've already converted ... print $file "<style>\n"; print $file "<!--table\n"; print $file "{\n"; print $file " mso-displayed-decimal-separator:\"\.\";\n"; print $file " mso-displayed-thousand-separator:\"\,\";\n"; print $file "}\n"; print $file "\@page\n"; print $file "{\n"; print $file " margin:1.0in .75in 1.0in .75in;\n"; print $file " mso-header-margin:.5in;\n"; print $file " mso-footer-margin:.5in;\n"; print $file "}\n"; print $file "-->\n"; print $file "</style>\n"; print $file "<![if !supportTabStrip]><script language=\"JavaScript +\">\n"; print $file "<!--\n"; print $file " function fnUpdateTabs() {\n"; print $file " if (parent.window.g_iIEVer >= 4) {\n"; print $file " if (parent.document.readyState == \"compl +ete\" &amp;&amp;\n "; print $file " parent.frames['frTabs'].document.read +yState == \"complete\")\n"; print $file " parent.fnSetActiveSheet($sheet_num);\ +n"; print $file " else\n"; print $file " window.setTimeout(\"fnUpdateTabs();\" +, 150);\n" ; print $file " }\n"; print $file " }\n"; print $file "\n"; print $file " if (window.name != \"frSheet\")\n"; print $file " window.location.replace(\"../$source_doc\");\ +n"; print $file " else\n"; print $file " fnUpdateTabs();\n"; print $file "//-->\n"; print $file "</script>\n"; print $file "<![endif]>\n"; print $file "<!--[if gte mso 9]>\n"; print $file "<xml>\n"; print $file " <x:WorksheetOptions>\n"; print $file " <x:FreezePanes/>\n"; print $file " <x:FrozenNoSplit/>\n"; print $file " <x:SplitHorizontal>1</x:SplitHorizontal>\n"; print $file " <x:TopRowBottomPane>1</x:TopRowBottomPane>\n"; print $file " <x:ActivePane>2</x:ActivePane>\n"; print $file " <x:Selected/>\n" if $selected; print $file " <x:DoNotDisplayGridlines/>\n"; print $file " <x:ProtectContents>True</x:ProtectContents>\n"; print $file " <x:ProtectObjects>True</x:ProtectObjects>\n"; print $file " <x:ProtectScenarios>True</x:ProtectScenarios>\n"; print $file " </x:WorksheetOptions>\n"; print $file "</xml>\n"; print $file "<![endif]-->\n"; print $file "</head>\n\n";

------
We are the carpenters and bricklayers of the Information Age.

Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

Replies are listed 'Best First'.
Re: Non-standard options to tags for CGI.pm
by merlyn (Sage) on Jan 10, 2002 at 22:52 UTC
    A quick start would be to run the program, take the output, and use my html2cgi program to convert it to the equivalent CGI.pm-based program. You'll have to go back in and edit anything that was variable, but this should do 90% of the work for you.

    -- Randal L. Schwartz, Perl hacker

      Very cool script. I did notice that either CGI.pm or html2cgi hiccup a bit with lists, whether or not the <li> tag is closed with a </li>.

      The following HTML chunk

      <html> <head> <title>There is no title</title> </head> <body> <ul> <li>First item <li>Second item </ul> </body> </html>
      becomes
      use CGI "-no_debug", ":html", "comment", "header"; print header, start_html("-title" => "There is no title"), ul ( ul ( li ( "First item ", ), "\n", li ( "Second item ", ), "\n", ), "\n", ), "\n", end_html, ;
      I have not run this back through CGI.pm to see the output, but it seems that something is adding an extra <ul> in there. (It adds an extra one for each <li> tag.)

      --t. alex

      "Excellent. Release the hounds." -- Monty Burns.

      Cool script, but there's only one problem:
      scorpion:-> html2cgi.pl tucson.htm Subroutine HTML::TreeBuilder::comment redefined at html2cgi.pl line 11 +9. ## unknown head tag: <meta name="Excel Workbook Frameset"> ## unknown head tag: <meta content="text/html; charset=us-ascii" http- +equiv="Content-Type"> ## unknown head tag: <meta content="Excel.Sheet" name="ProgId"> ## unknown head tag: <meta content="Microsoft Excel 9" name="Generator +"> ## unknown head tag: <link href="tucson_files/filelist.xml" rel="File- +List"> ## unknown head tag: <comment>[if gte mso 9]&gt; &lt;xml&gt; &lt;o:DocumentProperties&gt; &lt;o:LastAuthor&gt;kinyoro&lt;/o:LastAuthor&gt; &lt;o:Created&gt;2001-06-25T12:34:28Z&lt;/o:Created&gt; &lt;o:LastSaved&gt;2001-06-25T12:34:28Z&lt;/o:LastSaved&gt; &lt;o:Version&gt;9.2720&lt;/o:Version&gt; &lt;/o:DocumentProperties&gt; &lt;o:OfficeDocumentSettings&gt; &lt;o:DownloadComponents/&gt; &lt;/o:OfficeDocumentSettings&gt; &lt;/xml&gt; &lt;![endif]</comment> ## unknown head tag: <link href="tucson_files/sheet001.htm" id="shLink +"> ## unknown head tag: <link href="tucson_files/sheet002.htm" id="shLink +"> ## unknown head tag: <link href="tucson_files/sheet003.htm" id="shLink +"> ## unknown head tag: <link id="shLink"> ## unknown head tag: <script language="JavaScript">
      Ummm... help?

      ------
      We are the carpenters and bricklayers of the Information Age.

      Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

        Somewhere recently I ran across something about a tool that strips out junk from Microsoft Office generated HTML (all that 'mso...' stuff). Come to think of it, it was HTML Tidy, which comes as an integral part of HTML Kit, a freely downloadable tool.

        If you have no intention of ever converting these files (or the output of your CGI-based perl code) BACK to Excel, it might be a viable option to download the command-line version of HTML Tidy and run it from your script before html2cgi.pl.

        dmm

        You can give a man a fish and feed him for a day ...
        Or, you can
        teach him to fish and feed him for a lifetime
Re: Non-standard options to tags for CGI.pm
by $code or die (Deacon) on Jan 11, 2002 at 21:02 UTC

    I wouldn't use CGI for the HTML generation, but that's just my preference. I prefer to use Templates. You could store the template as a scalar or in <DATA> if you don't want the seperate file.I would use either HTML::Template or Template Toolkit.

    Although if I wanted to output both to Excel and to HTML, I would probably have seperate routines to display in HTML and Excel rather than use Excel's HTML output (but then I don't know what you're doing so maybe I would do it that way). I'd probably try out Spreadsheet::WriteExcel or one of its relations. Spreadsheet::WriteExcel runs on non-Windows machines.

    Searching CPAN also reveals Spreadsheet::ParseExcel_XLHTML, which looks interesting. Check out xlhtml.org

    Just my 2 pennys worth.

    Simon Flack ($code or die)
    $,=reverse'"ro_';s,$,\$,;s,$,lc ref sub{},e;$,
    =~y'_"' ';eval"die";print $_,lc substr$@,0,3;
(tye)Re: Non-standard options to tags for CGI.pm
by tye (Sage) on Jan 12, 2002 at 00:34 UTC

    There is almost nothing above that CGI supports. For example, one of your last lines would be done with something like:   print $file "    ",$q->x:ProtectScenarios("True"),"\n"; but CGI doesn't support that (and Perl can't parse that syntax). Even if you go out or your way:

    use CGI qw( -any ); my $q= CGI->new(); my $tag= "x:ProtectScenarios"; print $q->NoSuchHtmlTag("False"), $/; print $q->$tag("True"), $/;
    you get:
    <NOSUCHHTMLTAG>False</NOSUCHHTMLTAG> Goto undefined subroutine &main::CGI::
    So, unless I'm missing some feature of CGI, I think you are mostly out of luck here.

    (Note that the last error is due to CGI not being able to parse a function name out of "CGI::x:ProtectScenarios" which gets passed to its AUTOLOAD routine, and not from the strange syntax that I used.)

            - tye (but my friends call me "Tye")