Hide Subtotal symbol in grouped rows columns using C#.NET

Aspose.Cells Product Family
1
Hi Aspose support team,

My requirement is grouping few set of records with sub total and grand total. I achieved this one. but i need to change the following:
- Need to hide the subtotal and grand total symbol(Expand/Colapse)(Please refer the attached screen shot issue:1)
- Need to show the the sub total in another one cell(Eg: In attached screenshot Cell:C10)(Please refer the attached screen shot issue:2)
- Need to change the caption of the subtotal(Please refer the attached screen shot issue:2)
- Need to hide the symbol like (1,2,3) (Please refer the attached screen shot issue:3)

Please help me how to achive the above features...

Thanks in advance

Regards,
Saravanan
2
Hi,

kssaranmca:
- Need to hide the subtotal and grand total symbol(Expand/Colapse)(Please refer the attached screen shot issue:1)

Well, if you need to hide the "+/-" buttons or group level buttons (e.g 1,2,3), you may ungroup the rows accordingly. Once we have created the Subtotals using Aspose.Cells APIs (see the topic here: http://www.aspose.com/docs/display/cellsnet/Creating+Subtotals), then you may choose the UngroupRows() overload which has three parameters in it, please set the last boolean parameter to "true" for your needs. See the sample code below:
e.g
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells.UngroupRows(0, worksheet.Cells.MaxDataRow, true);
workbook.Save("e:\\test2\\outputsubt1.xlsx");

Now when you open the output file, it will not display "+/-" buttons or group level buttons (e.g 1,2,3).

kssaranmca:
- Need to show the the sub total in another one cell(Eg: In attached screenshot Cell:C10)(Please refer the attached screen shot issue:2)
Well, this is not feasible by default. You may check it in MS Excel, MS Excel will implement the Subtotals order by a column, e.g first, second or third etc. The Summary text will be displayed in the same column where you specify the order by column while specifying the Subtotals. We follows rather have to follow MS Excel standards. For your custom needs, you have to find the summary text in the the cell and remove it and put the summary text into your desired column cell by using your own code. See the document on how to find/search data in the cells:
http://www.aspose.com/docs/display/cellsnet/Find+or+Search+Data


kssaranmca:
- Need to change the caption of the subtotal(Please refer the attached screen shot issue:2)

This is again not feasible as MS Excel does not allow this by default when you create subtotals. You have to update the caption manually of summary rows accordingly, see the above point for reference.

kssaranmca:
- Need to hide the symbol like (1,2,3) (Please refer the attached screen shot issue:3)

Please see the first point for your reference.

Thank you.
3
Hi Aspose Support Team,
My requirement is replacing the default subtotal caption to custom caption(in our example "subtotal"). We have achived this feature with the below mentioned sample code
Cells newcells = obj.ExcelWorkBook.Worksheets[0].Cells;
for (IEnumerator ie = newcells.GetEnumerator(); ie.MoveNext(); )
{
Cell cell = (Cell)ie.Current;

if (cell.StringValue.Contains("Grand Total"))
{
cell.PutValue(String.Empty);
newCellName = cell.Name.Replace('A', 'L');
newcells[newCellName].PutValue("Grand total");
newcells[newCellName].SetStyle(strCellSummationStyle);
newCellGrandTotalValue = cell.Name.Replace('A', 'N');
newcells[newCellGrandTotalValue].SetStyle(strCellSummationValueStyle);
}
else if (cell.StringValue.EndsWith("Total"))
{
cell.PutValue(String.Empty);
newCellName = cell.Name.Replace('A', 'L');
newcells[newCellName].PutValue("Sub total");
newcells[newCellName].SetStyle(strCellSummationStyle);
newCellSubTotalvalue = cell.Name.Replace('A', 'N');
newcells[newCellSubTotalvalue].SetStyle(strCellSummationValueStyle);
}
}

We don't want to search all the cells in the work sheet. but we only want to search the first column(Column A) in the worksheet. How to achive this functionality with simple way?
Please provide the sample code to achive this functionality.

Regards,
Saravanan K
4

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

You can use Find/Search API of Aspose.Cells to find your desired cell and replace it with new text.


For example, in the following code, the code looks for a cell with text Grand Total and replace it with New Total.

I have attached the source and output file used in this code and screenshot for your reference.

C#

string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


string stringToFind = “Grand”; //You can change it as per your needs


FindOptions opts = new FindOptions();

opts.LookAtType = LookAtType.Contains; //use as per your needs

opts.LookInType = LookInType.Values; //change if you want to search in formulas


Cell foundCell = worksheet.Cells.Find(stringToFind, null, opts);


foundCell.PutValue(“New Total”);


workbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);


Screenshot:
5
Hi,

kssaranmca:
We don't want to search all the cells in the work sheet. but we only want to search the first column(Column A) in the worksheet. How to achive this functionality with simple way?
Please provide the sample code to achive this functionality.

As I suggested you to kindly use Find/Search options provided by Aspose.Cells APIs to find your desired content in the cells (http://www.aspose.com/docs/display/cellsnet/Find+or+Search+Data). You may even search in your specific range of cells e.g Column A etc. See the sample code below in which only first column cells (which contains data) will be scanned only.

Sample code:

//..............

//Get Cells collection
Cells cells = workbook.Worksheets[0].Cells;

//Instantiate FindOptions Object
FindOptions findOptions = new FindOptions();

//Create a Cells Area based on the A column only
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 0;
ca.EndRow = cells.MaxDataRow;
ca.EndColumn = 0;

//Set cells area for find options
findOptions.SetRange(ca);

//Set the lookintype, you may specify, values, formulas, comments etc.
findOptions.LookInType = LookInType.Values;

//Set the lookattype, you may specify Match entire content, contains, endswith, starwith etc.
findOptions.LookAtType = LookAtType.Contains;

//Find the cell having "Total" in it.
Cell cell = cells.Find("Total", null, findOptions);

Row foundRow = cells.Rows[cell.Row];
Column foundColumn = cells.Columns[cell.Column];
Debug.WriteLine(row.Index.ToString());

Hope, this helps you.

Thank you.
6
Hi Aspose Support Team,
Thanks for the quick reply. I need to replace the caption of the sub total also. It has occurs in multiple rows. (Eg: We need to change 23Total and 34Total as "SubTotal")

Please provide the sample piece of code to achieve this feature.

Regards,
Saravanan K
7

Hi,


Please check the following sample code to accomplish your needs. I have updated the code segment shared by my fellow colleague and I used the similar template file here.

Sample code:

string filePath = @“e:\test2\source.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.Worksheets[0];

Cells cells = worksheet.Cells;
Aspose.Cells.Cell prevcell = null;
Aspose.Cells.Cell foundCell = null;
string stringToFind = “Total”; //You can change it as per your needs
FindOptions opts = new FindOptions();
//Create a Cells Area based on the A column only
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 0;
ca.EndRow = cells.MaxDataRow;
ca.EndColumn = 0;

//Set cells area for find options
opts.SetRange(ca);
opts.LookAtType = LookAtType.Contains; //use as per your needs
opts.LookInType = LookInType.Values; //change if you want to search in formulas


do
{
foundCell = worksheet.Cells.Find(stringToFind, prevcell, opts);
if (foundCell == null)
break;

if (foundCell.StringValue != “Grand Total”)
{
foundCell.PutValue(“SubTotal”);
}
prevcell = foundCell;

} while (foundCell != null);
workbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);


Hope, this helps.

Thank you.