cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Correct formatting of decimals whne donloading data to Excel from BSP

Go to solution
markus_jarasch
Explorer

on ‎05-04-2006 12:21 PM

0 Kudos

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

Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

thomas_jung
Developer Advocate
Developer Advocate
‎05-04-2006 2:55 PM
0 Kudos

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.

Show replies
Show replies
markus_jarasch
Explorer
‎05-04-2006 3:09 PM
0 Kudos

Hi Thomas,

You are right, that's the standard behaviour of Excel.

Is there any way to tell Excel, that either the standard format of the cells (or rather of certain cells) is 'Number with two decimals' or to display the passed values generally as text? In html it is properly written: '<td>12,50<td>'.

Greetings from Munich,

Markus

thomas_jung
Developer Advocate
Developer Advocate
‎05-04-2006 3:16 PM
0 Kudos

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.

Answers (2)

Answers (2)

guillaume-hrc
Active Contributor
‎05-04-2006 3:29 PM
0 Kudos

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

Show replies
Show replies
eddy_declercq
Active Contributor
‎05-04-2006 12:46 PM
0 Kudos

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

Show replies
Show replies
markus_jarasch
Explorer
‎05-04-2006 3:02 PM
0 Kudos

Hi Eddy,

thanks for the quick answer.

The country settings have already been correct. And the two weblogs are not directly related to my problem.

Could You explain me how to quote the values? What I do is to create a html-response with a header containing two header fields and a body containing a table that contains my prices and so an as cell-values (<td><%=x_value%></td>). I don't think <td>"<%=x_value%>"</td> would solve the problem or am I wrong?

Greetings, Markus

eddy_declercq
Active Contributor
‎05-04-2006 3:06 PM
0 Kudos

Hi,

I was more thinking of more a CSV type of format. I didn't try the HTML table version.

Eddy

Ask a Question