Forum Stats

  • 3,851,350 Users
  • 2,263,966 Discussions
  • 7,904,686 Comments

Discussions

How to wrap text automatically while export data from oracle to excel?

668816
668816 Member Posts: 30
edited Nov 13, 2009 12:56AM in SQL & PL/SQL
Hello,

I am exporting data from oracle to excel.
My code is written in psp. i have written following code to export xls file
<%
owa_util.mime_header('application/vnd.ms-excel',FALSE);
htp.p('Content-Disposition: attachment; filename="excel.xls"');
owa_Util.Http_Header_Close;
%>

Now in xls file i want to wrap the text automatically while export the data.
Can you plz tell me what to do?

Thanks...
Tagged:

Answers

  • Marwim
    Marwim Member Posts: 3,653 Gold Trophy
    Hello 10540254,

    do you really generate a binary Excel file as your mime type suggests or do you generate a csv?

    If you have csv you can enclose the data within double quotes. Within these quotes you can use line breaks.

    Regards
    Marcus
  • 668816
    668816 Member Posts: 30
    What PSP code can I use, to force a cell
    to wrap if the content is longer than the width cell in xls file?
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,860 Red Diamond
    user10540254 wrote:
    What PSP code can I use, to force a cell
    to wrap if the content is longer than the width cell in xls file?
    How do you write the XLS file? As already commented by Marcus, your Mime tag indicates that you are using PL/SQL to return a binary XLS file.

    Are you? Do you know how to create a binary XLS structure in PL/SQL and return that via http? If so, then surely you should also know how to wrap a cell!

    But if you are returning a CSV file, your code is wrong. Mime type must be text/csv (e.g. owa_util.mime_header('text/csv'); ). And in this case, you cannot format an Excel cell as a CSV file does not contain any formatting data (as after all, it is a list of comma separated values only - without any data about the structure of the file, formatting to use, etc).

    If you do want to return formatting data, you need to look at XML and what XML structure Excel supports that allows you to specify both data values and structure/formatting.
  • Marwim
    Marwim Member Posts: 3,653 Gold Trophy
    And whenever XML and Excel is mentioned to format the output I link to 2516584 ;-)

    Regards
    Marcus
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,860 Red Diamond
    Nice links. Thanks.

    But why anyone want to use Excel (and MS Office) when there is Open Office...? ;-)
  • Marwim
    Marwim Member Posts: 3,653 Gold Trophy
    edited Nov 12, 2009 8:12AM
    Hello Billy,

    when I first wrote the package (second link) I took care that the output could be read by MS and OO. But when I tried to add new features I had the problem that OO does not tell you why it won't open the XML, contrary to MS that creates a log file with a hint about what is wrong.

    I even started to write a package with Open Document compatible output, since at home I only use OO. But there's a long way to go because of the much more complex format.

    Regards
    Marcus
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,860 Red Diamond
    Marwim wrote:

    when I first wrote the package (second link) I took care that the output could be read by MS and OO. But when I tried to add new features I had the problem that OO does not tell you why it won't open the XML, contrary to MS that creates a log file with a hint about what is wrong.

    I even started to write a package with Open Document compatible output, since at home I only use OO. But there's a long way to go because of the much more complex format.
    Yeah.. always a problem when trying to support multiple clients out there, when there's a lack of a common standard.. or worse, an unwillingness to adhere to the standard (like IE and W3C standards).

    I prefer to keep it simple - and output CSV only for reporting purposes. Let the end-user decide how he/she wants to format it. It should be trivial for such a user to create their own worksheet templates and simply copying and pasting the contents of the CSV into it.

    If they want to see graphics, charts and tables produced and have the Oracle db instance provide it, then that is web browser based using something like APEX.
  • Marwim
    Marwim Member Posts: 3,653 Gold Trophy
    edited Nov 12, 2009 10:49AM
    Yeah.. always a problem when trying to support multiple clients out there, when there's a lack of a common standard.. or worse, an unwillingness to adhere to the standard (like IE and W3C standards).
    I might have used sylk but it doesn't support many features I needed.
    I prefer to keep it simple - and output CSV only for reporting purposes. Let the end-user decide how he/she wants to format it. It should be trivial for such a user to create their own worksheet templates and simply copying and pasting the contents of the CSV into it.
    We needed a ready to print file with formatted print-headers and customized margins. The file will be sent directly from the application by mail to our inhouse customers.
    If they want to see graphics, charts and tables produced and have the Oracle db instance provide it, then that is web browser based using something like APEX.
    I would be glad if I could use APEX here. I think they don't beliefe Larry that it will stay an addon without extra costs. We had to learn the hard way when Forms became web based with many $$ for the OAS.


    Regards
    Marcus

    P.S.: And - most important - it was fun to do it
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,860 Red Diamond
    Marwim wrote:

    P.S.: And - most important - it was fun to do it
    And that's the reason behind many of the best software around. :-)
This discussion has been closed.