Skip to Content

Correct formatting of decimals whne donloading data to Excel from BSP

Hi everybody,

I've got a problem with the correct formatting of prices when downloading data from BSP to an Excel file: E.g. '14,67' is displayed properly as '14,67' in Excel, but e.g. '12,50' is displayed as '12,5' or '13,00' as '13'.

The way that was chosen to create this Excel file is (not my decision, I've inherited this topic from my predecessor - as it works 'almost' to the customers expectations I don't want to change the whole work):

After pressing the corresponding link in a BSP page ('View.htm') the same page is processed again. In 'onInitialization' there is the command

navigation->goto_page( 'Export.xls' ).

This opens a new browser window and the user is asked to confirm the download.

The BSP page 'Export.xls' contains in layout basically this:

<html>

<body>

<table>

<tr><td>Preis</td></tr>

<tr><td><%=x_value1%></td></tr>

<tr><td><%=x_value2%></td></tr>

...

</table>

</body>

</html>

x_value1 is defined as string and is filled with e.g. '12.50'.

onManipulation ('Excel.xls'):

call method response->set_header_field

exporting name = 'Content-Disposition'

value = 'attachment; filename=Testprogramm.xls'.

  • Tells the browser to open excel

call method response->set_header_field

exporting name = 'Content-Type'

value = 'application/vnd.ms-excel'.

Is there an easy way to force Excel to display the prices properly?

Thanks for Your help in advance,

Markus

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    May 04, 2006 at 01:55 PM

    I don't think this has anything to do with BSP. This is an Excel "Feature".

    Open Excel and type the same numbers in a column. I did and they did the exact same thing you described. This is because the default cell format is general. In this format ending zeros are dropped. If you want to change this select the column and choose Format->Cells. You can then choose another format. For instance try Number instead of General. This allws you to choose the number of decimal places that you want.

    Add comment
    10|10000 characters needed characters exceeded

    • Not with the way that your are creating the Excel File. You would have to use one of two other methods that allow you more interaction with Excel.

      First you could use ActiveX (or Office Web Controls) to instantiate Excel and pass the data directly to the cells. This is considerably more difficult to code and requires the use of ActiveX - which many people like to avoid for security reasons.

      The other approach is to use the Excel XML format to pass the data to the client. The XML format allows for setting formatting parameters in addition to passing the data. This is what is used internally in Web Dynpro ABAP for exporting ALV tables to Excel. The XML format is documented by Microsoft on their Website. However it is a rather complex XML schema. You can create a sample XML file by opening Excel and formatting it a sample spreadsheet the way you want it. Then do a save as and choose XML. This will give you an idea of the format you would have to create.

      However either of these approaches requires a complete redesign of how you creating your Excel file.

  • May 04, 2006 at 11:46 AM

    Hi,

    Chack in Control Panel>-Country settings how many decimals are set.

    Alternatively you could qoute the values in order that it takes it as-is.

    Check also these web logs

    /people/thomas.jung3/blog/2005/02/14/bsp-and-microsoft-excel--learning-to-live-together

    /people/thomas.jung3/blog/2005/02/23/bsp-and-microsoft-excel--learning-to-live-together-part-2

    Eddy

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 04, 2006 at 02:29 PM

    Hi,

    Just put the following around the data you want to be displayed as text by Excel : <b>="xxx"</b> , where xxx is the data.

    Of course, it means mass-modifying the table before export to Excel, but it will solve the problem of display format.

    Best regards,

    Guillaume

    Add comment
    10|10000 characters needed characters exceeded