Hi
Is it possible to create named ranges for groups created using the smart markers feature?
We need dynamic/named references to the group sums for further evaluations. Unfortunately the number of rows varies depending on the report type so we cannot use static cell names like C5
See images.
Or is there another possibility to achieve this?
Using Aspose.Cells 7.2.2.0
Hi,
Thanks for your posting and using Aspose.Cells for .NET.
Please see the following sample demo and let us know if it is helpful for you.
Here, you do not need to worry about the number of your data rows.
See the following code and source xlsx file which has a smartmarker tag and the output file generated by the code. As you can see, in output file, not only all the data rows has been output but also their sum is output in last row.
I have used a dummy list data source, definitely, you can use dataset.
Please also see the screenshot.
C#
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Aspose.Cells;
namespace TestSmartMarker
{
class Business
{
<span class="kwrd">int</span> m_unit;<br>
public int Unit
{
get { return m_unit; }
set { m_unit = <span class="kwrd">value</span>; }<br>
}<br>
internal Business(int unit)
{
m_unit = unit;
}<br>
}<br>
class Program
{
public static void SmartMarkerExample()
{<br>
<span class="kwrd">string</span> smartMarkerfile = @"F:\Shak-Data-RW\Downloads\SmartFile.xlsx";<br>
IList lst = new List();
lst.Add(<span class="kwrd">new</span> Business(324));<br>
lst.Add(<span class="kwrd">new</span> Business(325));<br>
lst.Add(<span class="kwrd">new</span> Business(326));<br>
lst.Add(<span class="kwrd">new</span> Business(327));<br>
WorkbookDesigner report = new WorkbookDesigner();
report.Workbook = new Workbook(smartMarkerfile);
report.SetDataSource(“Business”, lst);
report.Process(false);
report.Workbook.CalculateFormula();
report.Workbook.Save(smartMarkerfile + “.out.xlsx”, SaveFormat.Xlsx);
}
}
}
Screenshot:
Thank you very much.
The problem is that I have to reference the sum cell (cell A2 in your template, and cell A5 in your output file) from another sheet. I cannot reference with A5 or A2 as this reference points to the wrong cell when the report is filled with data.
Hi,
Thanks for your feedback.
Please provide us the xls/xlsx files as you have shown in the images.
Attachment: 07.08.png
Attachment: 07.08_required.png
You can create them manually using Ms-Excel 2010 and attach here. It will help us look into your problems and we will be able to provide you a solution or workaround.
Here are the xlsx files. As you can see in the file TFS_Manhours_2012_output in the sheet Overview I want to reference the group sums.
Thanks
Hi,
Thanks for the files.
Well, I am afraid if there are multiple groups of data to be sorted out, then assigning named cells/ranges in the template file might not work as you may understand it. If you have only one group of data, then assigning named cells and add reference to other worksheets in the template file might work for you.
Also, we have investigated it a bit it's impossible to support your needs accordingly. You have to use your own code to get the summary cells in other worksheets accordingly. I can see one option though, you have to do it by yourself using your codes, you will not assign named cells in the template file and also you will not add references for those cells in the other worksheets in the template file. The only thing you may do is you will do it after processing the markers in the cells and then finding the cells having e.g "=Subtotal()" formula string (you may use Cells.FindFormula, FindFormulaContains or Find methods etc. --- See the topic: http://www.aspose.com/docs/display/cellsnet/Find+or+Search+Data) to first find your desired formula cells and then add reference to those cells in other worksheets dynamically if it fits his needs. Note, in that case, adding reference to other worksheets would be dynamic and you cannot specify the references for the cells/ranges in the template file.
I have created a sample code for your reference. I assumed that you have processed the smart markers and the data is filled into the first worksheet "month" already, now you may refer to my sample code to add references to the summary cells in other worksheets accordingly. I used your filled workbook and performed the task. I have also attached the output file here.
Sample code:
//
// Your code goes here to process the smart markers in the template file
// Once you process all the markers and all the data is filled into the "month" worksheet.
// Now you may do it dynamically i.e. to specify references to those Subtotal cells in other worksheets.
//
Workbook wb = new Workbook(@"e:\test2\TFS_Manhours_2012_output.xlsx");
//First you need to find out the cell that contains "Man-hours Worked" string.
Aspose.Cells.Cell mcell = wb.Worksheets[0].Cells.Find("Man-hours Worked", null, new FindOptions());
//Get the column
int mcol = mcell.Column;
FindOptions findOptions = new FindOptions();
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = mcol;
ca.EndRow = wb.Worksheets[0].Cells.MaxDataRow;
ca.EndColumn = mcol;
findOptions.SetRange(ca);
findOptions.LookAtType = LookAtType.StartWith;
findOptions.LookInType = LookInType.OnlyFormulas;
Aspose.Cells.Cell foundcell;
Aspose.Cells.Cell prevcell;
wb.CalculateFormula();
foundcell = null;
prevcell = null;
int i = 2;
do
{
foundcell = wb.Worksheets[0].Cells.Find("=SubTotal(", prevcell, findOptions);
if (foundcell == null)
{
break;
}
else
{
wb.Worksheets[1].Cells[i, 2].Formula= "="+foundcell.Worksheet.Name + "!" + foundcell.Name;
i++;
}
prevcell = foundcell;
} while (foundcell != null);
wb.Save(@"e:\test2\newTFS_Manhours_2012_output1.xlsx", SaveFormat.Xlsx);
Hope, this helps and you may write your own codes to achieve your tasks.
thank you.
Thank you very much for your quick help.
I think we will use the Find() features to get the subtotal cells.
Kind regards,
Stephan
Hi,
Good to know that the workaround works for you.
Have a good day!