This week's book giveaway is in the Other Languages forum.
We're giving away four copies of Functional Reactive Programming and have Stephen Blackheath and Anthony Jones on-line!
See this thread for details.
Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Srikanth Kumar
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Ulf Dittmer
Rancher
Posts: 42968
73
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3275
5
Eclipse IDE Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
Alex Krenvalk
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 231
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Hope this code can help you in copying the sheets.
Copy Sheets
 
Evgeniy Bulanov
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ;)
 
Ph. Loep
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1337
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm sure they welcome code contributions.
 
Evgeniy Bulanov
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ph. Loep,
I am very glad that my code was helpful for you. Thank you for code contributions ;)
 
Srikanth Kumar
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot for all your replies.
 
Martijn Verburg
author
Bartender
Posts: 3275
5
Eclipse IDE Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And welcome to Javaranch Ph. Loep!
 
jesper johnsen
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3275
5
Eclipse IDE Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Any update on this issue.

Please Help.


Thanks in advance.
 
Amit Raphael
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you please post c# equivalent of this. I will really appreciate

Thanks,
harry
 
Chinh Nguyen Van
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Below is C# version for CopySheet, all credit given to above member, I just merge them all together
 
harry dawson
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Chinh and above members
 
Mahantesh Halakatti
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
*******Sample Client Code for Util class which copies multiple sheets from different XLS files and create a single XLS file with the all the sheets in it.******

I just added client code to invoke the Util.class

 
Prejith George
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot members, the above code I have modified in general way to support XLS and XLSX excel format.

But after the copy process (one XLSX to another XLSX), when I open the newly created XLSX file it is giving me the error "Removed Records: Merge cells from /xl/worksheets/sheet1.xml part"

I'm using POI 3.11 lib jars set


public static void copySheets(Sheet newSheet, Sheet sheet, boolean copyStyle) {
removeRow(newSheet)
int maxColumnNum = 0;
Map<Integer, CellStyle> styleMap = (copyStyle) ? new HashMap<Integer, CellStyle>() : null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
Row srcRow = sheet.getRow(i);
Row destRow = newSheet.createRow(i);
if (srcRow != null) {
copyRow(sheet, newSheet, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}



public static void copyRow(Sheet srcSheet, Sheet destSheet, Row srcRow, Row destRow, Map<Integer, CellStyle> styleMap) {
// manage a list of merged zone in order to not insert two times a merged zone
Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
destRow.setHeight(srcRow.getHeight());
// pour chaque row
int j = srcRow.getFirstCellNum();
if (j < 0) {j = 0;}
for (; j <= srcRow.getLastCellNum(); j++) {
Cell oldCell = srcRow.getCell(j); // ancienne cell
Cell newCell = destRow.getCell(j); // new cell
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
// copy chaque cell
copyCell(oldCell, newCell, styleMap);
// copy les informations de fusion entre les cellules
//System.out.println("row num: " + srcRow.getRowNum() + " , col: " + (short)oldCell.getColumnIndex());
CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

if (mergedRegion != null) {
//System.out.println("Selected merged region: " + mergedRegion.toString());
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
//System.out.println("New merged region: " + newMergedRegion.toString());
CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
if (isNewMergedRegion(wrapper, mergedRegions)) {
mergedRegions.add(wrapper);
destSheet.addMergedRegion(wrapper.range);
}
}
}
}
}


 
oleksii ka
Greenhorn
Posts: 1
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you Nicolas Dupont, Sartner huang, Ernesto Esteban

all the end result is:

 
Wolfgang Woelfel
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First at all, it's a very nive work, thanks to all!
I tested oleksii ka's version, and here is my feedback.

1)
Line 169

should be replaced by


Otherwise height inherited from fonts will always be overwriten with default row height.
So fonts higher than the default row height, will be truncated.

2)
Line 465 to 494
The compare operators need to be '!=' instead of '=='.
Otherwise a lot of duplicate CellStyles will be created.

3)
Line 474
Strings need to be compared by "equals" operator.


And some improvements I made:
a)
I used this code to copy a sheet to another workbook and not to convert a HSSFWorkbook to a XSSFWorkbook.
If you don't want styles already existing in the target workbook to be duplicated, you have to initialize styleMap2 in line 134 with the target workbooks styles
Use somthing like the following:


b)
I copied a XSSFSheet to the XSSFWorkbook and not a HSSFSheet.
If you do so, colors are a problem. HSSF uses a limited amount of indexed colors, and XSSF supports rgb colors.
In order to copy this colors you need to change some code parts.
Here an example for the background color of cells (line 356/357 in the original code)
 
Varun Chawla
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anybody please help me to copy xlsx file from a xlsx..
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic