Copy paste range of rows from one sheet to another sheet between workbooks java

Aspose.Cells Product Family
1

Hi,

I have two workbooks A and B. Workbook A has X sheet. Workbook B has M and N sheets. I need to copy A7::M7 contents of sheet X in workbook A to A7::M7 range of sheet N in workbook B.

I have macros written in sheet M which has to be retained.
sheet N has already contents in it. Need to replace contents with new one.

Could someone please help?

2

@archanadbreddy,

Thanks for your query.

Please see the sample code to accomplish your task, you may refer to the code segment and may add/update lines of code by yourselves for your needs accordingly:
e.g
Sample code:

 Workbook sourceWorkbook = new Workbook("e:\\test2\\BookA1.xlsx");
 Workbook destWorkbook = new Workbook("e:\\test2\\BookB1.xlsx");
            Worksheet sourceSheet = sourceWorkbook.Worksheets["XX"];
            Worksheet destSheet = destWorkbook.Worksheets["NN"];

            Aspose.Cells.Range sourceRange = sourceSheet.Cells.CreateRange("A7:M7");
            Aspose.Cells.Range destRange = destSheet.Cells.CreateRange(sourceRange.FirstRow, sourceRange.FirstColumn, sourceRange.RowCount, sourceRange.ColumnCount);

            PasteOptions options = new PasteOptions();
            options.PasteType = PasteType.All;

            destRange.Copy(sourceRange, options);

            destWorkbook.Save("e:\\test2\\out1.xlsx");

Also, see the document on how to copy ranges for your further reference:

Hope, this helps a bit.

3

Hi Amjad,

Thanku for the sample code.

m using the latest version of aspose.cells , however m getting errors at Worksheets, PasteType.

Using eclipse to write the java code.

Can you please help

4

@archanadbreddy,

I provide the .NET sample code, so you should easily convert it to Java by yourselves. Let me try to paste the updated Java code:
e.g
Sample code:

Workbook sourceWorkbook = new Workbook("e:\\test2\\BookA1.xlsx");
	 Workbook destWorkbook = new Workbook("e:\\test2\\BookB1.xlsx");
	            Worksheet sourceSheet = sourceWorkbook.getWorksheets().get("XX");
	            Worksheet destSheet = destWorkbook.getWorksheets().get("NN");

	            Range sourceRange = sourceSheet.getCells().createRange("A7:M7");
	            Range destRange = destSheet.getCells().createRange(sourceRange.getFirstRow(), sourceRange.getFirstColumn(), sourceRange.getRowCount(), sourceRange.getColumnCount());

	            PasteOptions options = new PasteOptions();
	            options.setPasteType(PasteType.ALL);

	            destRange.copy(sourceRange, options);

	            destWorkbook.save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.

5

@Amjad_Sahi

Thank you Amjad, its amazing.works fine.

however after copying to Sheet N, that is linked to Sheet M. ie…sheet M populates data by fetching inputs from Sheet N.

So that part was not working. Can you please help

6

@archanadbreddy,
Please share your sample file and runnable code snippet with us for our testing. We will reproduce the problem and provide our feedback after analysis.

7

I was working on a remote machine which had .odt file which is similar to xlsx.

So tried in local which has microsoft excel and it worked fine.

Thanks a lot!!!

8

@archanadbreddy,

It looks like you have sorted it out now. Feel free to contact us any time if you have further queries or issue, we will be happy to assist you soon.