Smart Marker Question

Hi,

Using Smart Markers, is there a way to have a Summary Row in the middle of the table based on a value?

For example:

Col1, col2, col3, col4

Have a sum based on Col1 values (for each value, have a total of Col2) - grouped by Col1

Thanks!

Hi,

Thanks for considering Aspose.

Well, I think you can manually try to insert summary row (using Cells.InsertRow() method) after processing the markers (after using WorkbookDesigner.Process() method) and apply formula (using Cell.Formula property) to it based on your condition.

Thank you.

Hi,

How would we get summaries to be in between rows that were processed?

Thanks!

Hi,

Well, In your newly inserted row(s), you may write code e.g., Cell.Formula = "=SUM()"... if you want to get the formula results at runtime, kindly call Workbook.CalcualteFormula before obtaining the formula calculated values.

Thank you.

Hi,

I think there is a misunderstanding.

Basically, here is what we want to do:

We have smart markers on Row 1.

After designer.process(), it populates 10 rows.

After row 3, we would like a Sum

After row 7, we would like a sum

and a final sum on bottom

Thanks!

Hi,

Do you mean you want SUM like running totals or want an extra row inserted and make the sum of the cells. Could you create a sample spreadsheet in MS Excel and post it here with details. We will check and try to implement your requirements.

Thank you.

Hi Amjad,

Please find a sample attached. The smart tags just have &=Type, &=Desc, &=amount.

We would like to sum up the individual types as shown in the sample.

Any suggestions?

Thanks!

Hi,

Thanks for the sample template.

I don't find any direct way to accomplish this except you have to manually insert new rows at regular data sets of cells and insert the formula for obtaining the SUM of every set. Actually this is just like a Subtotaling feature.

I have a similar example for you... Kindly consult it and understand the code below, it will give you some insight and you may build your own solution. Attached is the zip file containing the input and output excel files, so you may them first. The input file "stbook.xls" ...you may take it as a sample file with all the markers processed, so you may start your coding after workbookdesigner.Process() method.

Here is the code:

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)
{
//Get strarting cell's row index for obtaining sum for the range set.
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;
//For making grand total at the end, store individual totals to array.
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");

Thank you.

Hi,

Please find another sample attached - What is the easiest way of grouping information like the attached sample? Will Smart Markers support these types of groupings in future?

Thanks!

Hi,

Thank you.

I think you have to manually do this using Aspose.Cells API like Cell.PutValue() method and I don't find any direct way in MS Excel either. For setting formulas you may use Cell.Formula property.

Thank you.

Hi,

Will grouping like shown in the sample be implemented soon for Aspose.cells for Smart Markers?

Thanks!

We will make it in the future version but it won't be available in a short time.

Hi Laurence,

Do you have an Estimated Time for it? It's a great feature which we are looking forward to :)

Thanks!

Hopefully it will be available in 2-3 months.

Hi Laurence,

Do you have a more accurate time estimate on the above now :)? We are anxiously waiting for it.

Thanks!

Hi,

I think Laurence has already told you. We will try to make it before March 2008.

Thanks for being patient!

Hi,

Yes, I know she told me a month ago - I just wanted to know if there was a more detailed estimation a month later. Thanks for your response.

Keep up the great work!

Hi,

Well, he (Laurence) told you not she..........Never Mind! :)

Thank you.

Hi,

I figured I will let you know some of our ideas of how it could work while you are developing it:

1. There should be an unlimited number of sub-groups and each sub-group could have their own sub-group

2. We should be able to put some kind of code or tag or id of the parent in the excel sheet to link the two -that way we will be able to format everything in excel and place the main table and all subs whereever we would like in excel

Thanks!

Hi,

I see that you released a new version for Aspose.Cells --- does it have this feature we were speaking about? (I cannot find the updates on the documentation)

Thanks!!!