Skip to Content
-1

How to Read Excel column properties in ABAP Program?

Mar 06 at 03:38 AM

137

avatar image
Former Member

Hi,

I have a custom program where I am uploading an excel sheet and creating posting documents. My requirement is....I want to Read the Amount column properties and raise an error message if the property is 'General'. The amount column property must always be 'Number'. Is there any way to read the property of the column when uploading the file?

Regards,

Dileep.

10 |10000 characters needed characters left characters exceeded

It would be nice that all uploaded documents are XLSX because you can easily read their contents, even in background (while OLE-based solutions can't read in background). Are they always XLSX?

0
Former Member

Hi,

Thanks for your reply, it could be .xls or .xlsx. The requirement is... the decimal values will behave differently when the excel cell property is formatted as 'General' than 'Number'. So I need to check whether the cell property is General or number.

Regards,

Dileep.

1
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Matthew Billingham
Mar 21 at 08:30 AM
2

If the amount column must always contain a number, instead of checking the column property, why not check the column contents and see whether the value is a valid number or not. Seems a simpler approach.

Show 4 Share
10 |10000 characters needed characters left characters exceeded

Hi,

I believe that this is the correct way to go. When uploading data it does not matter if a cell has an incorrect format, only if the value is one that you expect. After uploading the excel file looses its validity. Rejecting an excel file even when the data it contains is correct will result in frustration for the parties involved in delivering said excel file.

Kind regards, Rob Dielemans

2

I agree too . it will be simple Regrex validation for the content of data but however if the file has 1m record ,it may affect performance.

1

I'm not sure checking the column will help anyway. I can set a column to be numeric. And then type "Any old rubbish" into a cell in that column.

Anyway - if you're uploading a million records from an Excel spreadsheet, I would question your sanity...

1
Former Member

Hi,

Thank you all for your reply,

I agree with you all, no need to check the property of the excel cell. checking the value and number of decimals after uploading the value into internal table is the correct way. My problem was rounding the value to two decimals in all the cases was my issue, this is not a problem when the cell is Number but when it is 'General' then the problem arises. I wrote the code to handle this when the decimal has 1 digit, 2 digits and more then two digits. I am using FM 'MOVE_CHAR_TO_NUM' to convert the character value into numeric. This solved my issue.

Regards,

Dileep.

1
Sandra Rossi Mar 21 at 08:25 AM
0

If it's XLS (old Excel "BIFF" format), then you can only use OLE (so, again, it will only work in dialog). To know which properties contain the "cell format", the best way is to use VBA (alt+F11) and "spy" the contents of a given cell, for instance the cell "A1" : activesheet.range("A1").

The property names are the same names when you use OLE from ABAP.

Of course, you can use the same OLE for XLSX documents, but it has a very bad performance. Prefer to use abap2xlsx instead. Anyway the old XLS format tends to disappear.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Sandra,

Thank you for your reply, I thought I might need to write the OLE code to solve my issue, but came to know that I need to handle this in my program based on number of decimals in the value.

Regards,

Dileep.

0