How to skip or delete Dynamically changing rows from the excel file using Aspose

vvibhuti · November 12, 2018, 1:20pm

Hello Aspose,

I have an excel sheet which contains multiple headers rows which gets dynamically generated whenever we append 2 Excel sheets. I just want to delete the repeatative rows except for the first header row. In attached Image i want to delete rows 71, 79 and 81 except for deleting 1st row. Is it possible using Aspose? If so can u please provide sample code in Java.

image.png (122.6 KB)

ahsaniqbalsidiqui · November 12, 2018, 2:41pm

@vvibhuti,

Thanks for your query.

Please share your sample Excel file( and runnable console application for creating this file if created using Aspose.Cells) with us for our testing. We will reproduce the problem and provide our feedback after analysis.

vvibhuti · November 12, 2018, 2:46pm

Hello Ahsan,

Please find the attached excel file for your perisul.

New Compressed (zipped) Folder.zip (164.5 KB)

I used the below code in my eclipse to append 2 sheets. The resultant sheet is attached in the .zip file

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");

ahsaniqbalsidiqui · November 12, 2018, 6:18pm

@vvibhuti,

You may please try following sample code and provide your feedback.

Workbook workbook = new Workbook("source copy.xlsx");

Workbook destWorkbook = new Workbook("dest.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 maxRange = sourceSheet.getCells().getMaxDisplayRange();
    Range sourceRange = sourceSheet.getCells().createRange(maxRange.getFirstRow() + 1, maxRange.getFirstColumn(), maxRange.getRowCount() - 1, maxRange.getColumnCount());

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

    destRange.copy(sourceRange);

    totalRowCount = sourceRange.getRowCount() + totalRowCount;
}

destWorkbook.save("out2.xlsx");

vvibhuti · November 13, 2018, 9:50am

Hello Ahsan,

I tried the above code provided by you and I am getting below error after executing the code

Getting error in this line :
Range sourceRange = sourceSheet.getCells().createRange(maxRange.getFirstRow() + 1, maxRange.getFirstColumn(), maxRange.getRowCount() - 1, maxRange.getColumnCount());

image.png (6.9 KB)

amjad.sahi · November 13, 2018, 10:21am

@vvibhuti,

Thanks for the screenshot.

Please paste your sample code (runnable) and attach your template files (you may zip the files prior attaching here), we will evaluate your code with respect to sample worksheets data and may figure it out accordingly.

vvibhuti · November 13, 2018, 10:29am

Please find the below runnable sample code for ur reference,

Also I have attached template file “Efficiency Test Data.xlsx” and “Efficiency Test Data1.xlsx”
Here I am appending the data present in the file “Efficiency Test Data1.xlsx” to the data present in the file “Efficiency Test Data.xlsx” and storing the resultant data in the file “Efficiency Test Data.xlsx”

package com.sikuli.www;

import com.aspose.cells.Range;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class AsposeCombineSheets1 {

public static void main(String[] args) throws Exception {
	

	Workbook workbook = new Workbook("C:\\Users\\vinay_m_vibhuti\\Desktop\\New folder (5)\\Efficiency Test Data.xlsx");

    Workbook destWorkbook = new Workbook("C:\\Pendrive\\Automation\\Efficiency Test Data1.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 maxRange = sourceSheet.getCells().getMaxDisplayRange();
		Range sourceRange = sourceSheet.getCells().createRange(maxRange.getFirstRow() + 1, maxRange.getFirstColumn(), maxRange.getRowCount() - 1, maxRange.getColumnCount());

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

    destRange.copy(sourceRange);

    totalRowCount = sourceRange.getRowCount() + totalRowCount;

    }
    
    destWorkbook.save("C:\\Users\\vinay_m_vibhuti\\Desktop\\New folder (5)\\Efficiency Test Data.xlsx");
}

}Efficiency Test Data.zip (11.3 KB)
Efficiency Test Data1.zip (11.3 KB)

amjad.sahi · November 13, 2018, 11:06am

@vvibhuti,

Thanks for the sample code and template files.

Since other worksheets (except first worksheet) in the source workbook are empty, so you need to refine your code segment and add an if condition to skip those empty worksheets when merging data to the first worksheet in the destination file, see the updated sample code which works fine as I tested:
e.g
Sample code:

Workbook workbook = new Workbook("f:\\files\\Efficiency Test Data.xlsx");

	    Workbook destWorkbook = new Workbook("f:\\files\\Efficiency Test Data1.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 maxRange = sourceSheet.getCells().getMaxDisplayRange();
			System.out.println("Worksheet " + sourceSheet.getName() + ": " + sourceSheet.getCells().getMaxDataRow());
			if(sourceSheet.getCells().getMaxDataRow()!= -1)
			{
				Range sourceRange = sourceSheet.getCells().createRange(maxRange.getFirstRow() + 1, maxRange.getFirstColumn(), maxRange.getRowCount() - 1, maxRange.getColumnCount());
				
				Range destRange = destSheet.getCells().createRange(
	            sourceRange.getFirstRow() + endRow + totalRowCount - 1,
	            sourceRange.getFirstColumn(),
	            sourceRange.getRowCount(),
	            sourceRange.getColumnCount());

				destRange.copy(sourceRange);

				totalRowCount = sourceRange.getRowCount() + totalRowCount;
			}

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

Hope, this helps a bit.

vvibhuti · November 13, 2018, 1:21pm

Thanks alot Amjad for the code, it works!! Even I also modified earlier code and it works as well. Below is the modified code,

Workbook workbook = new Workbook(“C:\Users\Efficiency Test Data.xlsx”);

Workbook destWorkbook = new Workbook("C:\\Efficiency Test Data1.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 maxRange = sourceSheet.getCells().getMaxDisplayRange();
    Range sourceRange = sourceSheet.getCells().createRange(maxRange.getFirstRow()+1, maxRange.getFirstColumn(), maxRange.getRowCount()+1, maxRange.getColumnCount()+1);

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

    destRange.copy(sourceRange);

    totalRowCount = sourceRange.getRowCount() + totalRowCount;
}

destWorkbook.save("C:\\Users\\Efficiency Test Data.xlsx");

ahsaniqbalsidiqui · November 13, 2018, 2:14pm

@vvibhuti,

Good to know that your issue is sorted out by this code segment. 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.