Append Sheets from different workbook into Single Sheet

Aspose.Cells Product Family
1

Hello Aspose Family,

I have requirement to Append 2 sheets from different workbooks into single worksheet. The 2 sheets have some data and sheet2 data should get appended with the sheet1 data.
Is it possible through Aspose??? If possible can u provide sample code to achieve this. Thank you in advance.

2

@vvibhuti,

Thanks for your query.

You can copy or move worksheets with Aspose.Cells for .NET. See the document with example code for your reference:

Hope, this helps.

3

Thank you for the reply, but I am working on Java language and wanted to append the values which is present in sheet2 to the values present in sheet1 and not copying!!! Copying may overwrite the existing data. do you please provide sample code for Java for my above requirement? Thank you in advance.

4

@vvibhuti,

Below is the link for Aspose.Cells for Java.

5

Hello Ahsan,

I tried the solution provided by you it didnt work!! :frowning:

image.png (90.9 KB)

Above attached snap is a sheet present in one of the workbook and i want to append the values present in another sheet from 19th row, at present row is 19th but it may different in differ in different scenarios. i.e i want to append values from next free cell in a worksheet. Please help me with this.

6

@vvibhuti,

Thanks for the screenshot.

Well, you got to devise an approach using Range.copy() method for your requirements. I have written the following sample code to accomplish the task for your needs. You may refer to it and test with the attached files (in the zipped archive) for your reference, the output file is also attached. The sample code appends two worksheets data from the source workbook to a sheet in the destination workbook:
e.g
Sample code:

Workbook workbook = new Workbook("f:\\files\\Bk_sourceappend1.xlsx");

        Workbook destWorkbook = new Workbook("f:\\files\\Book1_new1.xlsx");

        Worksheet destSheet = destWorkbook.getWorksheets().get(0);
        
        //Get the last row in the destination worksheet.
        int endRow = destSheet.getCells().getMaxDataRow()+1;

        int totalRowCount = 0;

        for (int i = 0; i < workbook.getWorksheets().getCount(); i++)
        {

            Worksheet sourceSheet = workbook.getWorksheets().get(i);

            Range sourceRange = sourceSheet.getCells().getMaxDisplayRange();

            Range destRange = destSheet.getCells().createRange(sourceRange.getFirstRow() + endRow + totalRowCount, sourceRange.getFirstColumn(), sourceRange.getRowCount(), sourceRange.getColumnCount());

            destRange.copy(sourceRange);

            totalRowCount = sourceRange.getRowCount() + totalRowCount;

        }
        
        destWorkbook.save("f:\\files\\out1.xlsx");

Hope, this helps a bit.
files1.zip (24.1 KB)

7

WOW!!! :blush: Thats amazing!!! Your code is just a spot on. Its exactly what I wanted :slight_smile: Thanks a lot for the help!!

8

@vvibhuti,

Good to know that your issue is sorted out by the suggested sample code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

9

Hello Amjad,

I have one more requirement which is part of above requirement, when we append the sheets the header of the sheet2 also get appended to sheet1 i.e we get 2 headers, but i want to delete the headers which are generated after appending, i want to retain only parent sheet header. Please have look at the Image attached for better understanding.

image.png (122.6 KB)

In the image attached I want to delete rows 71, 79 and 81 which are dynamically generated after appending different sheets. I want to retain only row1 and delete the rows 71, 79 and 81. Is it possible through Aspose? If possible what are the changes need to be done for the above code which you provided??

Thanks in advance.

10

@vvibhuti,

This query is replied in your other thread here.