2. PHPRunner General questions
  3. [SOLVED]  Importing from Excel
This topic is locked
[SOLVED]

 Importing from Excel

 		12/8/2020 9:29:55 PM
PHPRunner General questions
L
 leon author

The deafault behaviour of importing a Excel file seems to be that the whole workbook is imported.

Is there any way that I can specify that only a certain (say Worksheet 2) worksheet should be imported?

Any advice would be greatly appreciated.
M
 		Mark Kramer 12/9/2020



The deafault behaviour of importing a Excel file seems to be that the whole workbook is imported.

Is there any way that I can specify that only a certain (say Worksheet 2) worksheet should be imported?

Any advice would be greatly appreciated.



The cut and paste method works well for this. Just go to the page you want to import and select all cells, then ctrl + v to copy them. Past them on the import screen. Then just make sure you colums line up with your fields.
Sergey Kornilov admin 12/9/2020

There is no way to select a certain worksheet while importing. We recommend using Excel file that only have a single worksheet while importing.
L
 		leon author 12/10/2020



There is no way to select a certain worksheet while importing. We recommend using Excel file that only have a single worksheet while importing.


Thanks Sergey,

The other spreadsheets unfortunately have data validation details for the 1st worksheet and even though they are hidden the data pulls through to the import page.

I know that I can "Copy and Paste" but that defeats the object of your GREAT automated import process. Is there any chance that this will be looked at in future releases?

Regards,

Leon.
Sergey Kornilov admin 12/12/2020

Well, it is not so much automated if you need to select a correct sheet number.
To be honest, this is not a popular request and I'm not sure if we are going to implement it.
D
 		drochel 1/4/2021

I once needed a way to import only the first worksheet and was provided this tip. And although it doesn't allow the selection of a specific worksheet, it will work if the worksheet to import is the first worksheet.


[font="Calibri"][size="3"]Open <ASPRunner installationdirectory>\source\include\ImportFunctions.cs file with a text editor.[/size]


[font="Calibri"][size="3"]--------------------------[/size]



[font="Calibri"][size="3"]{[/size]



[font="Calibri"][size="3"]foreach(var worksheet in xlsPack.Workbook.Worksheets)[/size]



[font="Calibri"][size="3"].......[/size]



[font="Calibri"][size="3"]}[/size]


[font="Calibri"][size="3"]--------------------------[/size]
[font="Calibri"][size="3"]--------------------------[/size]


[font="Calibri"][size="3"]{[/size]



[font="Calibri"][size="3"]foreach(var worksheet in xlsPack.Workbook.Worksheets)[/size]



[font="Calibri"][size="3"].......[/size]



[font="Calibri"][size="3"]break;[/size]


[font="Calibri"][size="3"]}[/size]

--------------------------[sub][/sub][sup][/sup]
L
 		leon author 1/4/2021



I once needed a way to import only the first worksheet and was provided this tip. And although it doesn't allow the selection of a specific worksheet, it will work if the worksheet to import is the first worksheet.


[font="Calibri"][size="3"]Open <ASPRunner installationdirectory>\source\include\ImportFunctions.cs file with a text editor.[/size]


[font="Calibri"][size="3"]--------------------------[/size]



[font="Calibri"][size="3"]{[/size]



[font="Calibri"][size="3"]foreach(var worksheet in xlsPack.Workbook.Worksheets)[/size]



[font="Calibri"][size="3"].......[/size]



[font="Calibri"][size="3"]}[/size]


[font="Calibri"][size="3"]--------------------------[/size]
[font="Calibri"][size="3"]--------------------------[/size]


[font="Calibri"][size="3"]{[/size]



[font="Calibri"][size="3"]foreach(var worksheet in xlsPack.Workbook.Worksheets)[/size]



[font="Calibri"][size="3"].......[/size]



[font="Calibri"][size="3"]break;[/size]


[font="Calibri"][size="3"]}[/size]

--------------------------[sub][/sub][sup][/sup]
L
 		leon author 1/4/2021

Hi Duane.
Thank you SO SO MUCH!!! This is exactly what I was looking for, and could not believe that it could not be done.
Really appreciated.
Cheers,
Leon
L
 		leon author 1/4/2021

Hi Duane,
Seems I got exited too soon! I use phpRunner 10.5 and cannot find the file as specified below;
<ASPRunner installationdirectory>\source\include\ImportFunctions.cs
Any idea if this file has been renamed in Ver 10.5?
Cheers,
Leon
Sergey Kornilov admin 1/5/2021

Duane's advice, unfortunately, only applies to ASPRunner.NET only. I'm afraid that we use completely different libraries to parse Excel in PHP and in ASPRunner.NET.