aspose file tools*
The moose likes Other Open Source Projects and the fly likes Copying a sheet from an excel file to another excel file using Apache POI Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Copying a sheet from an excel file to another excel file using Apache POI" Watch "Copying a sheet from an excel file to another excel file using Apache POI" New topic
Author

Copying a sheet from an excel file to another excel file using Apache POI

Srikanth Kumar
Ranch Hand

Joined: Jun 04, 2008
Posts: 36
Hey,
I hava a requirement to copy from an excel file to another excel file where i need to copy an excel files sheet to another excel sheet. i'm able to achieve this using cell-to-cell copying but its consuming time. Is there any way to copy directly from sheet to sheet?


Srikanth Kumar
SCJP 5, SCWCD 5, SCDJWS 5
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42944
    
  68
No, POI does not have such convenience methods.

Note that -if you end up writing code that copies a complete sheet, including the cell styles- this would make a good addition to POI, so you might think about donating that code to the project (or at least posting it here so that people can find it).
Srikanth Kumar
Ranch Hand

Joined: Jun 04, 2008
Posts: 36
Thanks for reply Ulf.
I have taken your word into consideration and tried to implement that. somehow i could able to achieve this(copying sheet-to-sheet). However it works with sheets that contains integers and decimals. If any cell contains varchar then its replacing it with #VALUE!. And while opening the merged file i'm getting an alert "File error: data may have been lost."

I'm posting the classes involved in this.






Here, book1 contains 1 sheet with integers and decimals and book2 contains varchar. i need create a merged file with two tabs numbers and varchars.

Anybody help on this.
Thanks in advance.
[ October 23, 2008: Message edited by: Martijn Verburg ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42944
    
  68
I'm surprised it works like that at all (well, it seems it doesn't :-). I would have thought that one would need to create cells, cell contents and cell styles for each cell in each sheet. That's what I suggested.
Martijn Verburg
author
Bartender

Joined: Jun 24, 2003
Posts: 3274
    
    5

Hi Srikanth,

Thanks for changing the display name, much appreciated! I just thought I'd point out a handy tip for you since you are new to Javaranch. You can UseCodeTags to make your code examples stand out more. I've done this for you in this instance.

Happy questioning!


Cheers, Martijn - Blog,
Twitter, PCGen, Ikasan, My The Well-Grounded Java Developer book!,
My start-up.
Alex Krenvalk
Greenhorn

Joined: Dec 06, 2008
Posts: 5
For work with excel files advise use-Excel file error: data may have been lost,because as far as i know tool is freeware,it save important information,such as graphics,statistic and mathematics,program will help you to recover valuable information and avoid its losses,tool scans your broken worksheet,then gets the data from this document,will help you to repair damaged files in Microsoft Excel sheet recognizable format,repair file Excel this file is not in a recognizable format, Excel showing this file does not in a recognizable format, or Microsoft Excel worksheet this file is not in a recognizeable format, it is an Excel file error: data may have been lost: Microsoft
Excel impossible read file,tool performs a scan of your corrupt excel files not recognizable format and attempts to recover all available data.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42944
    
  68
In the interest of full disclosure it should be noted that Alex Krenvalk seems somehow involved with the commercial tool he recommends.
Sivaraman Lakshmanan
Ranch Hand

Joined: Aug 02, 2003
Posts: 231
Hi,
Hope this code can help you in copying the sheets.
Copy Sheets


Regards,
Sivaraman.L
Evgeniy Bulanov
Greenhorn

Joined: Dec 19, 2005
Posts: 23
Thanks 4 all replays in the topic. (Personally to Srikanth Kumar and Sivaraman Lakshmanan)
I modify a bit code posted by Sivaraman Lakshmanan. Now it supports copying cellstyles.


I hope it will be helpful for somebody ;)


Computers are like air conditioners - They stop working properly when you open Windows
Ph. Loep
Greenhorn

Joined: Sep 11, 2010
Posts: 1
Hi,

I found this old thread via Google and I am happy that the code posted by Evgeniy Bulanov works well.
But I modified the code a little bit to get rid of the deprecated classes and methods and changed the raw-types to generic-types. Now there are no warnings left and still it works fine!



The "Add a way to copy sheets"-feature ist still on the Todo List of the Apache POI project, may we send them our code as a proposition?
Lester Burnham
Rancher

Joined: Oct 14, 2008
Posts: 1337
I'm sure they welcome code contributions.
Evgeniy Bulanov
Greenhorn

Joined: Dec 19, 2005
Posts: 23
Hi Ph. Loep,
I am very glad that my code was helpful for you. Thank you for code contributions ;)
Srikanth Kumar
Ranch Hand

Joined: Jun 04, 2008
Posts: 36
Thanks a lot for all your replies.
Martijn Verburg
author
Bartender

Joined: Jun 24, 2003
Posts: 3274
    
    5

And welcome to Javaranch Ph. Loep!
jesper johnsen
Greenhorn

Joined: Jan 08, 2011
Posts: 1
Hi all

I would like to copy a sheet as well, and I have done this with 1 VBA line, can it really be true that I need 100+ lines of JAVA to do this

Here is my VBA line:
Workbooks("source.xls").Sheets(1).Move Before:=Workbooks("Destination.xls").Sheets(2)

both files must be open, so that makes it 3 lines of code...
I started out using JACOB, but I could not understand the dispatch syntax, so I started looking at POI, so can any of you convert this single line of code, into POI, or JACOB?
the good thing about the VBA code, is that it takes everything including graphs...

Regards
Jesper Johnsen
Martijn Verburg
author
Bartender

Joined: Jun 24, 2003
Posts: 3274
    
    5

Hi Jesper and welcome to Javaranch!

It does seem strange that so much code is needed in the POI library - hopefully someone more knowledgeable than I can come up with an answer!
amanda jones
Greenhorn

Joined: Jul 22, 2005
Posts: 2
Hi Ph. Loep

when you change the deprecated method you forget that CellRangeAddress does not implement Comparable so the method isNewMergedRegion does not work...
Thanks
Pierre Guilbert
Greenhorn

Joined: Apr 14, 2011
Posts: 1
Hi,

As Amanda Jones pointed out, there is a problem. I spent some time this morning to fix some of it.

Maybe it's not pretty, but it's doing the job.




Plus you have to change this part in the copyRow method. CellRangeAddress constructor is called with the wrong arguments



Ps: It fixes some issues, but not all of them.
Ernesto Esteban
Greenhorn

Joined: May 02, 2011
Posts: 1
Hi all,

I spent some days trying to get have something working fine, thanks to all your post. Here is what I've done :



and



I created a wrapper for the CellRangeAddress class that implements Comparable. This allows the set to work.

++
kins
hamza abd
Greenhorn

Joined: Jun 08, 2011
Posts: 1
Hello Ernesto,
Thank you very much for your code. I was able to copy one sheet to another. Do you know however how to preserve sheet aspect ?
my src sheet has 35% zoom and perfectly fits on an A4 page. Once copied into the new workbook it does not fit (zoom, print margins, row breaks all change)

Thanks for your help
omar kamil
Greenhorn

Joined: Sep 23, 2011
Posts: 2
Hi,

i 'm getting this error when i copy a sheet from workbook (XSSF) to another workbook using poi3.7 and this util class.

error :
java.lang.IllegalArgumentException: Cell index must be >= 0

Please any Help.

How to copy XSSF Sheet from workbook to another.

thanks in advance.
omar kamil
Greenhorn

Joined: Sep 23, 2011
Posts: 2
Any update on this issue.

Please Help.


Thanks in advance.
Amit Raphael
Greenhorn

Joined: Nov 27, 2011
Posts: 1
While setting something like color for a whole row by pressing on the row number,
the "firstCellNum" is -1.

I have changed line 70 on class Util from:
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {

to:
int j = srcRow.getFirstCellNum();
if(j<0){j=0;}
for (; j <= srcRow.getLastCellNum(); j++) {

and the problem stoped.
fabio biscaro
Greenhorn

Joined: Mar 13, 2012
Posts: 1
I found also another improvement to the code: if you copy rows in a different row number merged regions are still copied to the original row numbers.

I modified slightly the code adding "deltaRows"
Sartner huang
Greenhorn

Joined: Apr 19, 2012
Posts: 1
There is some problems with the solution.

The Excel sheet render is with order;


when sheet1 finished rendering the sheet2 rendering is just started;
so
if sheet1 has a formula with relevance to sheet2. the formula will be #REF!

We need some extra code to refresh the formula when the whole excel is finished rendering
Here, I give a solution

We need a POJO to store the formula information,
like: sheet name , row index, cell index and the formula
and then
when we copy a cell to another sheet cell
if the cell type is HSSFCell.CELL_TYPE_FORMULA
we need to add the cell information to a collection as you like (of course you need to create a collection in the class)
OK~ excuse my poor English

I give the solution in the following

1. we need a POJO to save the formula infomation
I create a private inner static class named FormulaInfo


2. create a formulaInfo Collection in the class


3. in copyCell(), when you detected the cell is a FormulaType m you need to add informatiton to the formulaInfoList




4. after create the whole WorkBook, we need to refresh the formula






















Nicolas Dupont
Greenhorn

Joined: Jun 21, 2012
Posts: 1

I use your work to copy sheets. Thanks for that.
I have improved it a little. I hope it will help you.

I need a metho to copy print title.
This method is a little handmade, but I don't find a better way to do that:


Then I need a method to copy print settings:


In fact my need was to merge 5 identicals sheet into one. The 5 sheet have the same style, only data were different.
The method to find existing cellstyle doesn't work for me so I create a method to compare cellstyle.
So I use a List<CellStyle> instead of Map<Integer, HSSFCellStyle> styleMap.



The method to clone cell style, clone all the time the Font. It was a problem for me.
So I made my one way to clone cell style. I put it in method to copy cell:



The last method I need was to copy pictures. It was not possible to make the same code for Excel 2003 and Excel 2007.


I change the rest of the code to remove HSSF to have a unique way to copy Excel 2003 and Excel 2007.
I don't put it to avoid to have very long post.
Chinh Nguyen Van
Greenhorn

Joined: Jan 02, 2013
Posts: 2
Hi all,
Thank you very much for this method, I make a translation to C#, is there any one interested so I will post it here?
harry dawson
Greenhorn

Joined: Nov 15, 2013
Posts: 2
Can you please post c# equivalent of this. I will really appreciate

Thanks,
harry
Chinh Nguyen Van
Greenhorn

Joined: Jan 02, 2013
Posts: 2
Below is C# version for CopySheet, all credit given to above member, I just merge them all together
harry dawson
Greenhorn

Joined: Nov 15, 2013
Posts: 2
Thanks Chinh and above members
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Copying a sheet from an excel file to another excel file using Apache POI