OraFAQ Forum: SQL & PL/SQL » list box in a cell of a excel sheet

Home » SQL & PL/SQL » SQL & PL/SQL » list box in a cell of a excel sheet

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

list box in a cell of a excel sheet [message #601280]

Tue, 19 November 2013 02:46

akash123
Messages: 46
Registered: May 2008
Location: india

Member

Dear All,

While creating a excel file in a PL/SQL procedure, is it possible to make a particular cell data in the sheet as a list box drop down.

Report message to a moderator

Re: list box in a cell of a excel sheet [message #601282 is a reply to message #601280]

Tue, 19 November 2013 03:25

akash123
Messages: 46
Registered: May 2008
Location: india

Member

if it is possible then please suggest an example for it..

Report message to a moderator

Re: list box in a cell of a excel sheet [message #601283 is a reply to message #601282]

Tue, 19 November 2013 03:29

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

If people know the answer, or want more info, they will post. Do not bump your post after less than an hour just because you haven't recieved a response. People have jobs, they take precedence over your query. Have patience.

Report message to a moderator

Re: list box in a cell of a excel sheet [message #601284 is a reply to message #601282]

Tue, 19 November 2013 03:30

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

And when you say, you create an Excel file, are you talking about a real Excel file with one of those xls, xlsx, xlsm file extensions (I mean a real Excel file format) or just a CSV which can be opened by Excel?

Report message to a moderator

Re: list box in a cell of a excel sheet [message #601285 is a reply to message #601284]

Tue, 19 November 2013 03:58

akash123
Messages: 46
Registered: May 2008
Location: india

Member

.csv file..

Report message to a moderator

Re: list box in a cell of a excel sheet [message #601286 is a reply to message #601285]

Tue, 19 November 2013 04:00

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You can't create special excel fields in a csv.
csv is a plain text file, it has no concept of drop downs

Report message to a moderator

Re: list box in a cell of a excel sheet [message #601289 is a reply to message #601286]

Tue, 19 November 2013 04:11

akash123
Messages: 46
Registered: May 2008
Location: india

Member

ok can we do it in excel ??

Report message to a moderator

Re: list box in a cell of a excel sheet [message #601290 is a reply to message #601289]

Tue, 19 November 2013 04:11

akash123
Messages: 46
Registered: May 2008
Location: india

Member

i mean .xls format..

Report message to a moderator

Re: list box in a cell of a excel sheet [message #601291 is a reply to message #601285]

Tue, 19 November 2013 04:18

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

akash123 wrote on Tue, 19 November 2013 10:58

.csv file..

There has to bee some VBA Excel or VBScript development to do the job and this is done at Windows level not at oracle level.

- How do you create the file? by using spool, utl_file?
- How the PL/SQL program is called? is it called automatically like a batch? or by a manual procedure call?

akash123 wrote on Tue, 19 November 2013 10:58

i mean .xls format.

First you say csv and now you're telling me xls? Finally you should make up your mind!

Report message to a moderator

Re: list box in a cell of a excel sheet [message #601293 is a reply to message #601291]

Tue, 19 November 2013 04:29

akash123
Messages: 46
Registered: May 2008
Location: india

Member

When its not possible in csv then i thought of doing it in .xls. i am creating the file using utl_file package and the procedure is manually executed.

Report message to a moderator

Re: list box in a cell of a excel sheet [message #601295 is a reply to message #601293]

Tue, 19 November 2013 05:10

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

akash123 wrote on Tue, 19 November 2013 11:29

When its not possible in csv then i thought of doing it in .xls.

I'm not sure that you see the problem in terms of file format. The file format is about the structure, and not just a file extension changing from .csv to .xls. Therefore you may have to modify your procedure code accordingly.

akash123 wrote on Tue, 19 November 2013 11:29

i am creating the file using utl_file package and the procedure is manually executed.

Then, if all is done manually, what's the point of making this automatic? why not opening manually the generated Excel file and define the list of values?

It would help if you provided a sample data, particularly the column which is to be set as list of values by describing the rules, so that we may have a clearer idea of what you're trying to do.

Report message to a moderator

Re: list box in a cell of a excel sheet [message #601296 is a reply to message #601295]

Tue, 19 November 2013 05:41

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Two quick suggestions, since you can't write "Excel files" without a framework that supports writing excel files:

1) A "Server based" Solution would be to get a frame work like Apache POI that can write "real" Excel files and thus can create drop-down boxes.

2) A "Client Based" solution where you have a prepared Excel File that does what you need inside the excel file with VBA, and loads the created .csv file via a macro.

Report message to a moderator

Re: list box in a cell of a excel sheet [message #601298 is a reply to message #601280]

Tue, 19 November 2013 06:29

akash123
Messages: 46
Registered: May 2008
Location: india

Member

i have added sample data image.

When i create a excel file the address type filed should be blank.
when you click on the addresstype column it will display list of options. User has to select the data one of it, so that the data restriction can be done.

Thomas thanks for the suggestion but please let me know what i am trying to do is possible through plsql or not!!
i mean creating this type of excel sheet(.xls).

as i need to reload this file back into my application once the user fills all the data in the addresstype fields.