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
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.
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
Hi,
Thanks for your smartmarker files and illustration of the problem.
We will look into it soon and update you asap.
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”
Thanks, I will try this.
In what version of Aspose was copystyle introduced?
/Fredrik
Hi,
Please download the latest version: Aspose.Cells
for .NET v6.0.0.4
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
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
Sorry, I just tried your markup and it works. I will get back when I know what the difference is!
/Fredrik
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
<span class="kwrd">using</span> System;<br>
<span class="kwrd">using</span> System.Web;<br>
<span class="kwrd">using</span> Aspose.Cells;<br>
<span class="kwrd">using</span> Aspose.Cells.Pivot;<br>
<span class="kwrd">using</span> Aspose.Cells.Charts;<br>
<span class="kwrd">using</span> Aspose.Cells.Drawing;<br>
<span class="kwrd">using</span> Aspose.Cells.Properties;
etc.
2) As I said above, some classes have been renamed.
e.g
i)
Validations -->
ValidationCollection ii)
PivotTable -->
PivotTableCollectionetc.
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);<br>
Style style2 = cells["I1"].GetStyle();<br>
style2.HorizontalAlignment = TextAlignmentType.Center;<br>
style2.BackgroundColor = System.Drawing.Color.Navy;<br>
style2.Font.IsBold = <span class="kwrd">true</span>;<br>
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:
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
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.
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.
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
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.
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
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
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.
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?