topic Re: Excel Sheet problem in Application Development and Automation Discussions

Excel Sheet problem

Former Member — Fri, 27 Jul 2007 10:22:27 GMT

Hi Friends,

I am displaying the output in Excel Sheet My Material number is 0613952E23

while displaying it is displayed 6.14E+28 how to diable this property in Excel Sheet. I am using OLE technic to display the output in Excel Sheet

Thanks in Advance,

Y.Ravi kumar

Re: Excel Sheet problem

Former Member — Fri, 27 Jul 2007 10:30:13 GMT

If you prefix your field with a single quote, Excel will treat it as text.

constant: c_quote value `'`.
  
 concatentate c_quote my_field into my_new_field.

Regards,

Nick

Re: Excel Sheet problem

former_member189059 — Fri, 27 Jul 2007 10:33:00 GMT

Hello ravi

one technique you can use is to merge the cell with the next one


TYPE-POOLS ole2 .
DATA:  count TYPE i,
       application TYPE ole2_object,
       workbook TYPE ole2_object,
       excel     TYPE ole2_object,
       sheet TYPE ole2_object,
       cells TYPE ole2_object.

DATA:
      h_cell    TYPE ole2_object,        " cell
      h_f       TYPE ole2_object,        " font
      h_range   TYPE ole2_object,
      h_merge   TYPE ole2_object,


CALL METHOD OF excel 'Range' = h_range
  EXPORTING
  #1 = 'A10'
  #2 = 'K10'.  " will merge A10 to K10 in this case

 CALL METHOD OF h_range 'Merge' = h_merge .

Re: Excel Sheet problem

Former Member — Fri, 27 Jul 2007 10:35:08 GMT

Open the Excel sheet right click a cell.

select FORMAT CELLs

Now select NUMBERS instead of general.

Reward if useful

Re: Excel Sheet problem

PritamKunal — Fri, 27 Jul 2007 10:35:40 GMT

Definitely you will see the display of the number in exponential form. That is only way of the display in excel because after certain number it convert it into exponential form. but if you will click onto the cell you can see, actual value what ever material number you have given into the cell.

Reward point if useful

Thanks

Pritam

Re: Excel Sheet problem

PritamKunal — Fri, 27 Jul 2007 10:40:17 GMT

Hi Ravi

if you really need to display in digit format do the following

Right click on the cell --> Format Cell --> In general Tab Maintain category as Custom and then Type as 0. after that you can see the data will appear in cell as you want

Reward Point if helpful

Thanks

Pritam

Re: Excel Sheet problem

former_member189059 — Fri, 27 Jul 2007 11:22:56 GMT

Hello Ravi,

This is the solution to your problem

Excel OLE has a method which allows you to autofit the contents of a cell

sample is shown below

DATA:
      h_cell    TYPE ole2_object,        " cell
       h_columns TYPE ole2_object,
       h_rows TYPE ole2_object,
       h_auto TYPE ole2_object.

  CALL METHOD OF h_cell 'Columns' = h_columns .

  CALL METHOD OF h_columns 'AutoFit' = h_auto .

  CALL METHOD OF h_cell 'Rows' = h_rows .
  CALL METHOD OF h_rows 'AutoFit' = h_auto .

Re: Excel Sheet problem

former_member189059 — Fri, 27 Jul 2007 11:26:46 GMT

one more thing...

using the autofit method will give you the output as 613952E23

to get the preceding 0, append the apostrophe character to your matnr

(declare matnr as char(19) first)

concatenate '''' matnr into matnr.

'''' is the escape sequence for apostrophe