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.
Amjad Sahi
Support Developer,
Aspose Nanjing Team
Contact Us
