Win a copy of Machine Learning with R: Expert techniques for predictive modeling this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Ganesh Patekar

POI: Excel Read and Write

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello. I am able to read one sheet from one excel and write in another with this. However now i wanna read through a folder of excel files. How do i do that?
this program is for one particular file. I wanna read from multiple xls files extract sheet number 1 from each and print in a new file only the first sheet.
 
Rancher
Posts: 4281
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you want to put all the data into a single sheet or multiple sheets (one per source file)?

If a single sheet, how do you want to layout the data from each source sheet?
 
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Do you want to put all the data into a single sheet or multiple sheets (one per source file)?

If a single sheet, how do you want to layout the data from each source sheet?


i would want to put all the data in a new file with multiple sheets.. and the sheet name should be the file name to make it easier to read and understand
 
Dave Tolls
Rancher
Posts: 4281
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, The simplest thing would be to change the above code to accept a Workbook as a parameter, which will be your target Workbook, and a File, which would be the next Workbook to copy from.

Write something else to call this method.
That "something else" would be the part that creates the target Workbook, and would loop over the files you need to process, calling your copy method for each file.
 
Sukuma Karan
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:OK, The simplest thing would be to change the above code to accept a Workbook as a parameter, which will be your target Workbook, and a File, which would be the next Workbook to copy from.

Write something else to call this method.
That "something else" would be the part that creates the target Workbook, and would loop over the files you need to process, calling your copy method for each file.


I didnt quite understand. Can you show me how do i accept Workbook and file as parameters?
 
Dave Tolls
Rancher
Posts: 4281
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Sukuma Karan
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:


how willit scan the whole directory?
 
Saloon Keeper
Posts: 6258
58
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is a way to do it. You'd have to supply a "processFile()" method and you might want to also pass in a destination Workbook as a parameter to both methods.
 
Dave Tolls
Rancher
Posts: 4281
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That lot really should not be all in a single method.
It makes the code far more complicated, and makes it harder to test the different parts.

As a pseudo code:

1. Get List of Excel files to process
2. Create target Workbook
3. For each file
   4. Copy first sheet to target Workbook
5. Save target workbook


The above can be a single method, but 1 and 4 ought to be calls to other methods that do that work.
Working on Carey's example:


I would consider wrapping the listFiles and null check into a method, which I would ensure returns an empty list if listFiles returns null, or throws a suitable exception.
Indeed, I would probably use the Files.list method instead.
Either way, that should make the above a bit neater.
 
look! it's a bird! it's a plane! It's .... a teeny tiny ad
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!