Repeating parts of sheet. based on input data

fredrikg · July 4, 2011, 11:13am

Here's what I want to do:

Given a rowset of data (like in a DataTable), I want to repeat parts of a sheet, once per row in the input data. I know that smart markers can be used to repeat the cell that contains the smart marker, but can I use it to repeat entire areas (including formatting, other fields containing descriptive texts, etc)?

As an example of what I want to do, consider the attached document, with the following sheets:
Designer - the template of the document, containing the smart markers, etc
Input data - This is how the input could look like
Result - The result that I want to accomplish after applying the input data.

Many thanks in advance!

/Fredrik

shakeel.faiz · July 4, 2011, 1:20pm

Hi,

I think, you should try horizontal parameter.

Please refer to following section in this document: Smart Markers

Smart Marker
Options

&=DataSource.FieldName

&=[Data Source].[Field Name]

&=$VariableName

&=$VariableArray

&==DynamicFormula

&=&=RepeatDynamicFormula

Parameters

The following parameters are allowed:

noadd

Do not add extra rows to fit data

skip:n

Skip n number of rows for each row of data

ascending:n / descending:n

Sort data in smart markers. If n is 1, then the column is the first key of the sorter. The data is sorted after processing the data source. E.g &=Table1.Field3(ascending:1)
horizontal
Write data left-to-right, instead of top-to-bottom.
numeric

Convert text to number if possible.Only supported in .NET version.

shift

Shift down or right extrs rows/columns to fit data.

fredrikg · July 11, 2011, 6:35am

Thanks for your response.

I know about the horizontal option.

Attached is an example of what I want to do. The document has three sheets: Horizontal, Vertical and Input data.

Both "Horizontal and "Vertical" has three sections:
* Template section
This is the "designer" part, containing the smart markers.Note the (somewhat ugly) formatting, which I need to be included in the result.
* Expected result
This is what I would like the result to be
* Result
This is the actual result, when applying the data, described in the "Input data" sheet.

So, when using vertical layout mode, i.e. not specifying the "horizontal" option, the result is close to the result I am after (See Vertical sheet).
The only problem is that the skipped rows (rows 25 and 29) are formatted (light red), they should be white (non-formatted), since they have been skipped.

When using the horizontal layout mode (which is what I really need), the result if far from close... (See Horizontal sheet).

So, two questions:

1. For vertical layout mode, is there a way to get rid of the formatting of the skipped rows?
2. (More importantly) For horizontal mode, it seem as is Aspose works differently. How can I accomplish what I want?

Thanks!

/Fredrik

This is using the horizontal option. The sheet has t

shakeel.faiz · July 11, 2011, 9:57am

Hi,

Thanks for your smartmarker files and illustration of the problem.

We will look into it soon and update you asap.

shakeel.faiz · July 12, 2011, 3:08am

Hi,

Please change your smart marker template file as the attached
file.

We have added a new property “copystyle”. If it’s true, we will copy
base cell’s style to each cell.

C#

Workbook wb = new Workbook(@“D:\FileTemp\Book1.xlsx”);

WorkbookDesigner d = new WorkbookDesigner();

d.Workbook = wb;

DataTable dt = new DataTable(“MyDataSource”);

dt.Columns.Add(“ProjectID”);

dt.Columns.Add(“ProjectName”);

dt.Columns.Add(“ProjectStatus”);

dt.Rows.Add(new object[] { “1”, “2”, “3” });

dt.Rows.Add(new object[] { “1”, “2”, “3” });

dt.Rows.Add(new object[] { “1”, “2”, “3” });

d.SetDataSource(dt);

d.Process();

wb.Save(@“D:\FileTemp\dest.xlsx”

fredrikg · July 12, 2011, 3:50am

Thanks, I will try this.

In what version of Aspose was copystyle introduced?

/Fredrik

shakeel.faiz · July 12, 2011, 3:58am

Hi,

Please download the latest version: Aspose.Cells
for .NET v6.0.0.4

fredrikg · July 12, 2011, 4:24am

Still doesn't work unfortunately.

Let's focus on the horizontal sheet for a while.

As you can see in the attached example, the result is far from the expected result. I am using the same properties, except that I didn't add a "noadd" to the last column (I read somewhere that this is the way to do it). The result for this is under "The result" on the Horizontal sheet.

But even with noadd to all the columns the result is not the expected.See "The result with noadd on rightmost column".

Have you managed to get this to work? You sent me a sample of the designer Excel document. Can you send me the resulting Excel document?

Thanks!

/Fredrik

fredrikg · July 12, 2011, 4:27am

As a side-note: I can't find the release notes for different versions of the Aspose components, but I am sure I have seen them before. Where are they located?

Thanks again!

/Fredrik

fredrikg · July 12, 2011, 4:29am

Sorry, I just tried your markup and it works. I will get back when I know what the difference is!

/Fredrik

shakeel.faiz · July 12, 2011, 9:56am

Hi,

It is great your issue is now getting resolved. For release notes, please see the following text.

At first, you should check the release notes for different
official versions of Aspose.Cells for .NET (especially v5.0.0 and 5.1.0

etc.) for API changes.

See Release Notes Aspose.Cells for .NET 5.0.0
See Release Notes Aspose.Cells for .NET 5.1.0

Note: Check the description under “Notable Changes for Existing Users” for API changes.

For older Workbook.Open and Workbook.Save methods, please see the documents for your reference to check what is updated now:

Opening Files
Saving Files

Moreover, please check the documentation of the product for complete details:

Programmer’s Guide

Let me come towards your significant errors that you may encounter, so you can fix them accordingly:

1)
You need to import the relevant namesapaces to your project pages or
use fully qualified naming when declaring objects for classes etc.:

e.g
 using System; 
 using System.Web; 
 using Aspose.Cells; 

 using Aspose.Cells.Pivot; 
 using Aspose.Cells.Charts; 
 using Aspose.Cells.Drawing; 

 using Aspose.Cells.Properties;
etc.

2) As I said above, some classes have been renamed.

e.g

i) Validations --> ValidationCollection
ii) PivotTable --> PivotTableCollection

etc.

3) Aspose.Cells.Style property is eliminated/obsoleted now, you should adjust your code to use Aspose.Cells.GetStyle() and
Aspose.Cells.SetStyle() methods, it will also enhance the performance to
certain extent:

e.g

Your sample code using Style attribute should be updated accordingly, e.g
Style style = wsNdaa.Cells[0, 0].GetStyle();

style.Font.IsBold = true;
 wsNdaa.Cells[0, 0].SetStyle(style); 
 Style style2 = cells["I1"].GetStyle(); 
 style2.HorizontalAlignment = TextAlignmentType.Center; 
 style2.BackgroundColor = System.Drawing.Color.Navy; 
 style2.Font.IsBold = true; 

 cells["I1"].SetStyle(style2);
For complete reference about Cell.GetStyle/SetStyle approach, please see the documents in the section:

Working with Data Formatting

4) And above all, please check Aspose.Cells for .NET API Reference:

fredrikg · July 29, 2011, 12:08pm

Hi again, and thanks a lot for your help on this so far.

I've finally had time to continue my work with this, and although I am getting closer to what I want to do, I've still get a fair piece left...

I have spent many hours with trial-and-error, to no avail, so I thought I should send you an example of what I want to accomplish. Can you please tell me how to go about to do this, or if not possible, tell me so?

The attached Excel-document has four sheets:

Designer
This is how my current design looks like. It requires some explanations:
The green area is where the smart markers will insert the data.
"Some data here" simply means that there will be things to the left and right of the area where the smart markers are. In other words, the content that is generated by the smart markers should not overwrite the existing content,
"MyFunc" is my own UDF. It's actual implementation is not important, but it takes two parameters.

Wanted result
This is what I want the result to look like when the data is applied to the Designer sheet.

Actual result
This is what the result looks like currently

Input data
This is what the input data looks like (I add it using a DataTable in .NET).
Of course, there could be more rows than in this example.

Please advice, many thanks in advance!

/Fredrik

shakeel.faiz · August 2, 2011, 6:41am

Hi,

Thanks for your feedback. I have logged your comment inside our database. Once we will get any update we will let you know.

This issue has been logged as CELLSNET-29580.

amjad.sahi · August 5, 2011, 5:01am

Hi,

If you do not set the smart marker for Cell B2, we cannot know how many times the formula should be repeated.

Please try the following codes with the attached file:

public void CellsNet29580()

{

WorkbookDesigner designer = new WorkbookDesigner();

Workbook workbook = new Workbook(Constants.sourcePath +"CellsNet29580.xlsx");

designer.Workbook = workbook;

DataTable dt = CreateValidTable1();

designer.SetDataSource(dt);

designer.Process();

workbook.Save(Constants.destPath + "CellsNet29580.xlsx");

}

internal static DataTable CreateValidTable1()

{

DataTable dt = new DataTable("MyDataSource");

dt.Columns.Add("ProjectID", typeof(int));

dt.Columns.Add("Additional", typeof(string));

dt.Rows.Add(1001, null);

dt.Rows.Add(1002, null);

dt.Rows.Add(1003, null);

// dt.Rows.Add("val2", "val2", 4);

//dt.Rows.Add("val3", "val3", 4);

return dt;

}

Also, you may download latest version/fix v6.0.1.5: Aspose.Cells for .NET v6.0.1.5.

fredrikg · August 7, 2011, 7:27am

Yes, that works perfectly, thanks a lot!

1. I had to use the fix version. How do I know if and when this will make it into the official release?

2. For a possible future improvement, you might want to consider a special keyword (like "null"). Now, I need to add a new column (in your example it is "Additional") just to be able to indicate how many times a formula should be repeated.

Anyway, great work, great support!

/Fredrik

amjad.sahi · August 7, 2011, 10:07am

Hi,

1) You may use this fixed version as long as you wish, it will behave like an official version (it contains all the functionality/enhancements of the previous versions/fixes - no problem to use it). We are scheduled to release our next official version of the product in every month, we might release our next official release of the product in the second half of this month.

2) We will check your requested feature and might take it into our considerations if its feasibility report is OK to us.

Thank you.

fredrikg · August 8, 2011, 2:00am

Hello again!

I noticed one more thing. It's not critical, but it would certainly help for us.

Smart markers don't handle merged cells the way we expect.

See the attached document. It is identical to the one you sent to me, but with two new sheets.

Some of the cells (column C and D) have been merged. (See sheet Designer (2))

When the data is applied, the result doesn't look like we expect (see sheet Actual Result (2)) - the formatting is brought forward, but not the merge state.

Can you give som advice on this?

Thanks!

/Fredrik

fredrikg · August 8, 2011, 2:15am

Sorry for the spam :)

It realized that it is critical for us for the columns that are added keep when the smart markers are added keep the column width as the original column of the cell that cause the insertion of columns.

In other words, in the CellsNet29580.xlsx, after the input data has been applied, the columns E and H should have the same width as B. Column F and I should have the same width as C.

Any ideas how I can accomplish this (without macros?)

Thanks!

/Fredrik

amjad.sahi · August 8, 2011, 2:34am

Hi,

"In other words, in the CellsNet29580.xlsx, after the input data has been applied, the columns E and H should have the same width as B. Column F and I should have the same width as C."

Please simplify your template file to show this issue only (attach the file here). Also, give us your sample code (same as we provided you in one of the previous post) and output file, we will check it soon.

Thank you.

shakeel.faiz · August 8, 2011, 9:15pm

Hi,

1. For a possible future improvement, you might want to consider a special keyword (like “null”). Now, I need to add a new column (in your example it is “Additional”) just to be able to indicate how many times a formula should be repeated.

Could you explain more about “null”? We have to know how many times (which based on the number of records in the datatable) a formula should be repeated .

2. Added keep the column width as the original column

Did you just want to copy the column width ? If the column has style (border, font, background color setting), did you want to copy them too?