Sub-total help

Aspose.Cells Product Family
1

Hi Team,



I am displaying sub-totals in my report and it is showing as formula when I click on the sub-total cell. Is there any way to display value as text?



Thanks in advance.

2

Hi,


Thanks for providing some details.

Well, it looks you have set Subtotal formulas as text/string and have not specified formula(s) to those cells. How do you apply sub-totals or apply formulas to the cells? We appreciate if you could provide us a simple console demo application (runnable) to show the issue on our end. You may zip the project prior attaching here. Also provide your template file(s) if you have any, we will check it soon.

Thank you.
3

Hi,



I used below code to display sub-totals.



xlWs.Cells.Subtotal(cellarea, intColIndex - 1, ConsolidationFunction.Sum, totalList:=strList, replace:=True, pageBreaks:=True, summaryBelowData:=True)



After getting sub-totals I am checking cell value using below code inside a loop to check the value of cell.



Suppose if I have a value 150 in cell (7,8)

then below condition is showing as blank.



Below code is working fine as it is working for other cells… due to formula inside it is not showing the value. Is there a way to convert formula field to text value while checking the cell value?



xlWs.Cells(totRows , iCol - 1).Value

4

Hi,


Please see the document for reference:
http://www.aspose.com/docs/display/cellsnet/Calculating+Formulas

Please call Workbook.CalculateFormula() method before retrieving cells’ value, you will get calculated value against formula(s) inside the cells.

Hope, this helps a bit.

Thank you.

5

Excellent Amjad Sahi , I am very happy with your response.

I have now small issue after fixing big issue. Please suggest as well.

I am loosing sub-total row style when I am displaying indicators in one of the sub-total row.

Why I am loosing my sub-total row color?

Please provide your valuable suggestion.

Thanks.

6

Hi,


Well, when you apply sub-totals to certain data set, Aspose.Cells does insert new rows, so the summary rows are dynamic. I think you should set formattings/ styles for certain rows dynamically but after you have applied subtotals in your code. To find out summary/ subtotals rows, you may use Find and Search options provided by Aspose.Cells and then apply formattings to those rows, see the document for your reference here:
http://www.aspose.com/docs/display/cellsnet/Find+or+Search+Data

Thank you.

7

Hi,

Is there a way to apply style to particular cell directly.

Example :xlws.cells(3,5) --> in Green color.

Thanks

8

Hi,


Well, whenever you need to apply style to a particular cell, you will first create the Style object (either by using Cell.GetStyle() method or using Workbook.CreateStyle() method) and specify your desired formatting attributes and then apply the style to the cell (via using Cell.SetStyle() method). Please see the sample code segment for your reference:
e.g
Sample code:

…

//Define a Style and get the F4 cell style

Style style = xlws.Cells[3,5].GetStyle();

//Setting the foreground color to yellow

style.ForegroundColor = Color.Yellow;


//Setting the background pattern

style.Pattern = BackgroundType.Solid;


//Apply the style to F4 cell

xlws.Cells[3,5].SetStyle(style);


…

Hope, this helps a bit.

Thank you.


9

Thanks for response.

Actually I have range for column “I”(I2 to I60) but I want to provide style based on first cell value of that particular row.

Currently I don’t have anything to access rownum, is there a way to identify the value of row’s first cell based on I8 cell.

in this case I want to know the 8th row first cell value.

Please advise.

10

Hi,


Well, I am not entirely certain about your requirements or issue you are finding with Aspose.Cells APIs. Could you elaborate your requirements and provide us more details and sample Excel file, so we could understand you and help you through.

Thank you.