Read sheetnames from an excelsheet

Read sheetnames from an excelsheet

  
For an application I need to import excel sheets to add data to my application. For the filename I use the Input Filename Widget. Then I use the "Excel To Record List" to put the Exceldata into the database, but here I need also the sheetname. The sheetname is now given by typing it in a textbox. But to know the sheetname the excel file has to be opened because the sheetname can differ. Then the correct sheetname has to be typed in the textbox (Case sensitive). Then the sheet can be imported correctly. The case sensitive sheetname goes wrong very often.

What I want is after selecting an excel file, feed the available sheets, in that file, to a combobox so the user can select it.

How can I achieve this.

Tanks in advance

Dick Dokter
Hi Dick,

That is, indeed, a great question - and sadly, one that we do not support out of the box.

One easy way to do it through an extension, though, should be this:

http://www.codeproject.com/KB/aspnet/getsheetnames.aspx

Sorry it's not the answer you're looking for, I hope it's a helpful answer nonetheless!

Let us know if this solves your problem - and if you happen to follow this route and create an extension, feel free to publish it as a component, I'm sure it'll help others :)

Regards,

Paulo Tavares
Paulo,

Thanks for the quick answer. Actualy I followed this route and partly with succes. The succes part is: I created an extension which gets the sheetnames from the excel sheet. The problem is that the input file widgets needs a submit to get the content of the selected file. When I come back, after the screen is reloaded (coused by submit) I lose the InputFileWidget.content. and the texbox of the InputFileWidget is empty.

Any idea how to solve this?

Regards,

Dick Dokter
Hi Dick,

I'm glad you were able to read the sheet names. In  regards to your current situation, and thinking a bit aloud about it, it's not a real problem that it doesn't keep the value in the input box.

Actually, since you have already uploaded the file content, you could avoid doing it again, and store it in a local variable, using it again after the user has selected the sheet name. I would, then, replace the section with the "upload file" to a "select sheet" - the way to do it will depend whether or not you're using Ajax, or a local action and reloading the screen.

Here's a quick mockup of what it could be:



Hope this helps!

Regards,

Paulo Tavares
Hi Paulo,

Thanks for youre suggestions. I would like to do it the way you suggest with an ajax popup to select the sheet. But I tried to implement this for the last hour and can't get it done. Could you help me.

I attach the ExcelExtension, maybe it helps.

Thanks in advance.

Dick Dokter
Hi Dick,

Actually, I wasn't suggesting doing it on a popup, I just drew those boxes to "sectionize" what I wanted to explain. But for me, they would be done in a normal webpage - especially because sometimes there are some problems with file uploading, popups, and Ajax.

Let me know if you can get this to work in a normal webpage, at first glance I'm not seeing much problems there.

If you can't, send over the eSpace, and I can try to look at it.

Regards,

Paulo Tavares
Hi Paulo.

Sorry for the thread digging and offtopic, but what software did you use for that mockup? Is that Microsoft SketchFlow?

Thanks.

Hi Carlos,

I'm pretty sure Paulo's image was done with Balsamiq Mockups - pretty good software, actually.



Miguel
Thank you Miguel.
Yup, it was Balsamiq Mockups. I highly recommend it.

You can try it here, I believe the web version is still free.

Cheers,

Paulo