Win a copy of Java 9 Modularity: Patterns and Practices for Developing Maintainable Applications this week in the Java 9 forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

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

 
krishnadhar Mellacheruvu
Ranch Hand
Posts: 118
Android Java Objective C
  • Mark post as helpful
  • send pies
  • 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
 
Tim Moores
Saloon Keeper
Posts: 3953
94
  • Mark post as helpful
  • send pies
  • 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 Java Objective C
  • Mark post as helpful
  • send pies
  • 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: 3953
94
  • Mark post as helpful
  • send pies
  • 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 Java Objective C
  • Mark post as helpful
  • send pies
  • 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: 3953
94
  • Mark post as helpful
  • send pies
  • 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 Java Objective C
  • Mark post as helpful
  • send pies
  • 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: 3953
94
  • Mark post as helpful
  • send pies
  • 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 Java Objective C
  • Mark post as helpful
  • send pies
  • 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.

 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!