• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Apache POI: how to split an excel file into multiple workbooks basing on the number of worksheets

 
Ranch Hand
Posts: 118
Android Objective C Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi

I just started working on the apache poi i.e. working with excels, i came across many articles of how to split the excel in to multiple files basing on row count,size etc but i was wondering as to how to split a workbook basing on the number of worksheets. Suppose say i have a workbook with 3 sheets, i have to split the workbook into 3 workbooks with 3 sheet's i.e. 1 sheet per workbook (3 sheets are in the initial workbook). Can some through light on this as to how to go about it.

Thanks
 
Saloon Keeper
Posts: 7176
166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What does it matter on what criteria the file is split? Wouldn't the mechanics be exactly the same? Where, exactly, are you stuck adapting the code to your purposes?
 
krishnadhar Mellacheruvu
Ranch Hand
Posts: 118
Android Objective C Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi

Tim,

Thanks for the reply, i am not stuck anywhere as i just started working with Apache POI, i was going through their documentation of splitting the excel file basing on the number of rows that is when i had this doubt as to how to split the file into multiple files basing on the sheets(irrespective of the cell type,format, etc.).

Thanks

Krishnadhar
 
Tim Moores
Saloon Keeper
Posts: 7176
166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I see. So is the problem splitting a file into pieces, or implementing the logic of how to split the file? If the former, point us to the article you are following so we know what to advise. If the latter, provide some more detail on where you're stuck.
 
krishnadhar Mellacheruvu
Ranch Hand
Posts: 118
Android Objective C Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi

Tim,

The problem is splitting a file into pieces and how to implement the logic(splitting the file basing on number of sheets in a work-book) for the same. I have just started with POI of how to read and write files. But it would be great if you can point me out in the right direction in my quest.

Thanks

 
Tim Moores
Saloon Keeper
Posts: 7176
166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I might make as many copies of the file as there are sheets, each named "filename-N.xls", with 1<=N<=number of sheets. (Obviously, you'd have to open the file first to find out how many sheets it has, and then make copies as needed. The org.apache.poi.ss.usermodel.Workbook class has a getNumberOfSheets method.). Then I'd loop through all files, and delete all sheets that are not sheet N from each one.
 
krishnadhar Mellacheruvu
Ranch Hand
Posts: 118
Android Objective C Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


how to use the clone method to clone the sheets in the same location as that of the original file location.

Thanks
 
Tim Moores
Saloon Keeper
Posts: 7176
166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I seem to recall that POI does not support anything like duplicating sheets. I advise to go with the approach I outlined earlier.
 
krishnadhar Mellacheruvu
Ranch Hand
Posts: 118
Android Objective C Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi

I have written the below code to generate excel files from 1 workbook. The initial workbook contains 20 sheets, i have used file copy to copy the contents of the main sheet to new created workbooks(the new workbooks are created basing on the sheet names in the main workbook. I wanted to create a new workbook with only one sheet each from the main workbook. In that process i have arrived till the below code. Can any one throw some light as to how i can achieve that.

 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic