Designer files with placeholders and range stretching?

Aspose.Cells Product Family
1

I am about to evaluate Aspose.Excel and came across my first question.

Can I design an Excel file with placeholders and have any needed extra rows to be automatically inserted? I tried the “Summary of Sales by Year” demo and if you have more than four rows that replace %Summary, it will overwrite the rows that follow.

The feature I’m looking for is similiar to http://excelspeedgen.com/docs/Source_Workbook_(Template)/Formulas_and_Ranges/1.default.htm. A simple example is where there is a title at the top, data in the middle and totals at the bottom. The formulas for the totals would be in the designer file, not created by code.

Purpose:
I need to be able to have a “template” Excel file in which all formatting is done and only placeholders exist. No manipulation of the Excel file will be done in code, with the exception of loading the data from a .NET dataset into the places marked by the placeholders.

This will allow me to dump a lot of information to the Excel file and my customers to choose what data they want shown by adding/removing placeholders.

Example of template file:

MONTHLY SALES REPORT
%DataPlaceholder
=SUM(A1:A{automatically expanded end of range})

Thanks,

Tomi B.

2

Hi Tomi,

You can use the Cells.InsertRow method to push down the cells according to lines of data. Then use Replace method to replace the placeholder.

3

In other words I would first have to calculate how many rows of data I have, then search for the correct cell/row, move there, insert the required amount of rows and then do the replace?

Will Cells.InsertRow adjust all formulas in the sheet, including ones not supported by Cell.Formula?

The other product I’m looking at also allows the user to specify, in the template Excel, if the data from the dataset should be written across, left to right. Would I have to have this hardcoded with Aspose.Excel?

If so, what is the fastest way to loop through all the cells in order to implement my own placeholder/datamarker solution? It would be similar to the solution of another non 100% managed .NET solution I am looking at:

Insert by datasouce/fieldname: %%=DataSource.FieldName
Insert by index: %%=#X.#Y
Insert variable: %%=$VariableName

I might also implement an optional option such as Across to allow data to be filled left to right: %%=MyTable.FieldName(Across)

4

I tried looping through a sample sheet that had 6697 rows and 255 columns. If I try to retrieve any cell data, the loop comes to a literal standstill from a performace point of view. Most of the sheet is empty, but I will need to check every cell to see if it contains a marker (so I can add the needed row. Inserting rows in a loop seems reasonably fast, however it would be nice to be able to say Insert(NumberOfRows, StartAt).

However, using the .Replace method is almost instantaneous, so internally Aspose.Excel seems to be able to access the data in some other way.

Any ideas on how to loop through everything real quick?

5

I will supply a Find method to enable you to search the marker within one week. Now thanks for your patience.

6

That sounds great. The reason I was thinking about still looping through the whole set of data was that I don’t know if the Find method will let me search for substrings. Since the markers will begin with the same few characters, but the rest will be different in each, the find method would have to support something like:
- Find(StringLike)
- Find(StringExact)
- Find(RegularExpression)
- Find(StartsWith)
- Find(EndWith)
- Find(Contains)
- Select(SQL statement)

or whatever gives the possibilty to define the search more accurately.

7

Well, the following methods will be supplied quickly.
- Find(StringExact)
- Find(StartsWith)
- Find(EndWith)
- Find(Contains)

But Find(RegularExpression) and Select(SQL statement) will take much more time. And what’s the difference with Find(StringLike) and Find(Contains)?

8

Hi Laurence,

Those were just a few options that came to mind as examples, and like you mentioned there are a few duplicates there. All of them might not be worth the trouble to implement. What will be the result of this call, an array of cells that match?

9

Hi Tomi,

It will return a cell object. And you can repeately call this method to iterate the result.

public Cell FindString(string inputString, Cell previousCell)

At start, you can set the second paramter to null. And if no match is found, it will return null.

10

Hi Laurence,

are there any news on the suggestions to add methods like

- Find(StartsWith)
- Find(EndWith)
- Find(Contains)

I am especially interested in -Find(StartsWith).

Thanks
Kai

11

Hi Kai,

It will available in the next hotfix within this week. Now thanks for your patience.

12

@KaiSchrumpf,
Aspose.Excel is discontinued and no more continued. We have introduced a new a product Aspose.Cells which is much more advanced and better in performance than Aspose.Excel. This new product supports all the latest features of different versions of MS Excel. You can find or search data using it as demonstrated in the following sample code:

// Instantiate the workbook object
Workbook workbook = new Workbook(sourceDir + "sampleFindingDataOrFormulasUsingFindOptions.xlsx");

workbook.CalculateFormula();

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

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

// Create a Cells Area
CellArea ca = new CellArea();
ca.StartRow = 8;
ca.StartColumn = 2;
ca.EndRow = 17;
ca.EndColumn = 13;

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

// Set searching properties
findOptions.SearchBackward = false;
findOptions.SeachOrderByRows = true;

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

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

// Find the cell with value
Cell cell = cells.Find(341, null, findOptions);

if (cell != null)
{
    Console.WriteLine("Name of the cell containing the value: " + cell.Name);
}
else
{
    Console.WriteLine("Record not found ");
}

Following document contains detailed description of this feature:
Find or Search Data

Download free trial version of this new product here:
Aspose.Cells for .NET (Latest Version)

You can test all the features of this product by downloading a runnable solution here.