How can i merge the data in excel using aspose.cells

Aspose.Cells Product Family
1

Lets say i have data like
id col1 col2 col3
1 F
2 F
3 F
1 F
2 F

i want to merge it like this
id col1 col2 col3
1 F F
2 F F
3 F

is it possible using aspose?
Note:Please see the raw form of this query, as the details getting formatted

2

@ksupriya,
Could you please try this code snippet and let us know your feedback.

Workbook workbook = new Workbook(@"c:\test\book1.xlsx");
Worksheet ws = workbook.Worksheets[0];
int maxRows = ws.Cells.MaxDataRow;
List<int> data = new List<int>();

for(int iRow = 1; iRow <= maxRows; iRow++)
    data.Add(ws.Cells[iRow, 0].IntValue); 
int[] numbers = data.ToArray();
var count = numbers.GroupBy(e => e).OrderBy(e => e.Key);

int iRow2 = 1;
foreach (var element in count)
{
    ws.Cells[iRow2, 4].Value = element.Key;
    for (int i = 0; i < element.Count(); i++)
        ws.Cells[iRow2, i + 5].Value = "F";
    iRow2++;
}
workbook.Save("output.xlsx");

Book1.zip (6.3 KB)

3

Hi ,thanks for the response, but i need the Java code and i am not able to write equivalent code for var count = numbers.GroupBy(e => e).OrderBy(e => e.Key); in JAVA.

Please help regarding this.

4

@ksupriya,
This query is not relevant to Aspose.Cells, however you may find many samples using Google. For example following sample performs the same task i.e. returns the occurrence count of each number in the sorted array.

int[] a = { 1, 9, 8, 8, 7, 6, 5, 4, 3, 3, 2, 1,23,4567,34,23,4567,4567,1,3,4 };

Arrays.sort(a);

int nbOccurences = 1;

for (int i = 0, length = a.length; i < length; i++) {
    if (i < length - 1) {
        if (a[i] == a[i + 1]) {
            nbOccurences++;
        }
    } else {
        System.out.println(a[i] + " occurs " + nbOccurences
                + " time(s)"); //end of array
    }

    if (i < length - 1 && a[i] != a[i + 1]) {
        System.out.println(a[i] + " occurs " + nbOccurences
                + " time(s)"); //moving to new element in array
        nbOccurences = 1;
    }

}

Please note that the previous sample code was shared as no builtin feature is present to achieve your requirement, so you need to devise your own logic. It is just a sample code and not necessary to follow the same.