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?
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).
Joined: Jun 04, 2008
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 ]
Joined: Mar 22, 2005
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.
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.
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.
Joined: Mar 22, 2005
In the interest of full disclosure it should be noted that Alex Krenvalk seems somehow involved with the commercial tool he recommends.
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!
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:
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...
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)
when sheet1 finished rendering the sheet2 rendering is just started;
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
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
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.