Any Grouping function for Aspose.Cells?

Aspose.Cells Product Family
1

I am using the Aspose.Cell to construct excel report in .Net environment. However, I would like to do something like total, sub total. Are there any grouping function for Aspose.Cells?

Thanks.

2

Hi,

Thanks for considering Aspose.

Well, you may group rows and columns in the Worksheet using Cells.GroupRows() and Cells.GroupColumns() methods. You can also ungroup rows and columns using Cells.UngroupRows() and Cells.UngroupColumns() methods easily.

For complete reference please see our Wiki docs:

Grouping rows and columns

Regards

Amjad Sahi

Aspose Nanjing Team

3

Hi,

Thanks for reply. However, I would like to have something like Crystal Report. For example, I have a table salesorder. columns include clientID, salesVolumn. And I want to display the sum of sales volumn by client.

ClientID Date SalesVolumn

C0001 1Jan06 1,000

C0001 2Jan06 1,100

C0001 3Jan06 2,000

SubTotal 4,100

C0002 1Feb06 2,000

C0002 2Feb06 2,100

SubTotal 4,100

Total 8,200

What you suggest me is using the Excel grouping function, however, I need to know the exact row and column number and I also dont want to present the information under a group.

Any suggestion to solve this problem?

Thanks.

4

Sorry, the example is unclear. let me do it again.

ClientID Date SalesVolumn

C0001 1Jan06 1,000

C0001 2Jan06 1,100

C0001 3Jan06 2,000

SubTotal 3,100

C0002 1Feb06 2,000

C0002 2Feb06 2,100

SubTotal 4,100

Total 7,200

5

You can easily use Excel formulas to make it. Please check the attached file.

For how to make this file from scratch, please check Aspose.Cells .

6

It is easily use Excel formulas to make it if know exactly which row will be sum. In case after i use aposecells to export data to excel like this

ClientID Date SalesVolumn

C0001 1Jan06 1,000

C0001 2Jan06 1,100

C0001 3Jan06 2,000

C0002 1Feb06 2,000

C0002 2Feb06 2,100

How do i know use subtotal after row C0001 3Jan06 2,000.

I hope you understand what i ask.
Thanks in advance.
7

Hi,

Thanks for considering Aspose.

I think you may implement your task using some API of Aspose.Cells. I have tried to mimic your demand.

May the following sample code helps you for your need. Also, attached are the template file "stbook.xls" + output file "stbook2.xls" (kindly check to run it.....since I used a simple logic to implement the required functionality):

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\stbook.xls");
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
Cell cell,prevcell=null, lastcell;
Style style = workbook.Styles[workbook.Styles.Add()];
style.Font.IsBold = true;
style.Custom = "#,##0";
int frow=0;
string []arr = {"C0001","C0002","C0003"};
string []tcells = new string[3];
for(int i = 0;i<arr.Length;i++)
{
do
{
cell = cells.FindString(arr[i],prevcell);
if (frow == 0)
{
frow = cell.Row;
}
if (cell == null)
{
lastcell = prevcell;
int row = lastcell.Row;
cells.InsertRow(row+1);
cells[row+1,0].PutValue("SubTotal");
cells[row+1,0].Style =style;
string fcell = CellsHelper.CellIndexToName(frow,2);
string lcell = CellsHelper.CellIndexToName(row,2);
cells[row+1,2].Formula = "=SUM("+ fcell + ":" + lcell + ")";
cells[row+1,2].Style =style;
tcells[i] = cells[row+1,2].Name;
frow = 0;
break;
}
prevcell = cell;
}while(cell!=null);
}

int maxrow = cells.MaxDataRow;
cells[maxrow+1,0].PutValue("Total");
cells[maxrow+1,0].Style = style;
cells[maxrow+1,2].Formula = "=" + tcells[0] + "+" + tcells[1] + "+" + tcells[2];
cells[maxrow+1,2].Style = style;
workbook.Save("d:\\test\\stbook2.xls");
Hopefully, the code will give you some insight.
Thank you.