Please find attached sampe
Sample.zip (6.4 KB)
I have two requirements which I cant get to work.
First: when the smart markers in column B2 and C2 evaluate with skip:1, i was hoping the columns will be added sequentially one after the other (i.e.country, city, country city, country city). However skip:1 is adding a blank column before the next set (my output is country,country, city, city).How do i avoid this?
Second: how do i get content in B4 (smart marker) and in B7 (excel formula) to replicate in added columns.?
Thanks for the template file and details.
Seeing your template file, it looks like you are mixing the markers. Your data for markers would be filled in the column and you are inserting two markers (in alternative rows) in the same column. If you are using “skip” parameter, it will surely insert blank rows alternatively for the marker, may be you could also add “noadd” parameter with it. Please refer to the documents with examples in the section for your reference on Smart Markers first:
Please note, Smart Markers would not always do according to your custom needs, so you got to understnad the Smart Markers feature with its attributes. Please update your markers accordingly first and then retry. Also, I am not sure about your requirements. If you still have any confusion or other issue, kindly do share your sample code (runnable), output file and your expected file, we will check it soon.
Sample.zip (14.0 KB)
Attached the input and output. I am processing the sheet twice. First to generate the dynamic columns and then second for the actual data. I updated the markers now.
When i use noadd the column D (total gets overwritten). I also tried with shift but that adds new blanks columns. I need the columns to replicate as France,Paris,Germany,Berlin and then Total without spaces. Also need the total column and the border formatting to be preserved when the new columns get added.
Below is the code:
var dt = new DataTable("Details");
dt.Columns.Add("France", typeof(Int32));
dt.Columns.Add("Germany", typeof(Int32));
dt.Columns.Add("Paris", typeof(Int32));
dt.Columns.Add("Berlin", typeof(Int32));
dt.Columns.Add("Total", typeof(Int32));
dt.Rows.Add(new object[] { 10, 30, 40, 60, 140 });
dt.Rows.Add(new object[] { 20, 30, 50, 60, 160 });
dt.Rows.Add(new object[] { 20, 40, 50, 60, 170 });
var country = new List<string>() { "France", "Germany"};
var city = new List<string>() { "Paris", "Berlin" };
WorkbookDesigner wd = new WorkbookDesigner();
wd.Workbook = new Workbook(@"I:\Input.xlsx");
wd.SetDataSource("Country", country.ToArray());
wd.SetDataSource("City", city.ToArray());
wd.SetDataSource("CountryMarker", city.Select(x=>string.Concat("&=Details.",x)).ToArray());
wd.SetDataSource("CityMarker", city.Select(x => string.Concat("&=Details.", x)).ToArray());
wd.Process(true);
wd.SetDataSource(dt);
wd.Process(false);Thanks for the template file and sample code.
Please also provide an Excel file containing your expected data formulated in order, you may create/update the file manually in MS Excel. We will check if this can be done in Smart Markers or you have to accomplish the task manually.
Thanks for the file.
Well, your custom needs cannot be performed accurately via Smart Markers as Smart Markers has its own attributes and other limitations. We have to edit your template file for smart markers to accommodate your requirements accordingly. See the updated template file (attached), it will work ok for your requirements.
files1.zip (6.6 KB)
Hope, this helps a bit.
I was looking for a more generic solution in case another country,city gets added. See code below. If there is something please let me know.
Assuming that it is not possible i updated the template with a workaround which is a bit messy but works.
files.zip (14.9 KB)
The only issue i am facing is replicating the excel formula in the row 8 (grand total). Need either the total in B2,C2 to replicate or the ones in D2, E2. I think that should be possible via smart marker.
Updated code below.
var dt = new DataTable("Details");
dt.Columns.Add("France", typeof(Int32));
dt.Columns.Add("Germany", typeof(Int32));
dt.Columns.Add("Italy", typeof(Int32));
dt.Columns.Add("Paris", typeof(Int32));
dt.Columns.Add("Berlin", typeof(Int32));
dt.Columns.Add("Venice", typeof(Int32));
dt.Columns.Add("Total", typeof(Int32));
dt.Rows.Add(new object[] { 10, 30, 5, 40, 60, 140, 5 });
dt.Rows.Add(new object[] { 20, 30, 5, 50, 60, 160, 5 });
dt.Rows.Add(new object[] { 20, 40, 5, 50, 60, 170, 5 });
var country = new List<string>() { "France", "Germany", "Italy"};
var city = new List<string>() { "Paris", "Berlin", "Venice" };
var countryBlanks = new List<string>() { "", "" };
var cityBlanks = new List<string>() { "", "" };
WorkbookDesigner wd = new WorkbookDesigner();
wd.Workbook = new Workbook(@"I:\Input.xlsx");
wd.SetDataSource("CountryBlanks", countryBlanks.ToArray());
wd.SetDataSource("CityBlanks", cityBlanks.ToArray());
wd.Process(true);
wd.SetDataSource("Country", country.ToArray());
wd.SetDataSource("City", city.ToArray());
wd.SetDataSource("CountryMarker", city.Select(x=>string.Concat("&=Details.",x)).ToArray());
wd.SetDataSource("CityMarker", city.Select(x => string.Concat("&=Details.", x)).ToArray());
wd.SetDataSource("CountryBlanks", city.Select(x => x.Replace(x, string.Empty)).ToArray());
wd.SetDataSource("CityBlanks", city.Select(x => x.Replace(x, string.Empty)).ToArray());
wd.Process(true);
wd.SetDataSource(dt);
wd.Process(false);
wd.Workbook.Settings.ReCalculateOnOpen = true;Thanks for the sample files.
As we told you earlier that your custom requirements might not be accomplished with Smart Markers precisely, so as long as you can accommodate it using both Smart Markers and manual way, it will be ok for you.
I am afraid, there is no better way to cope with it. I guess you might add formulas manually (after processing smart markers) for those missing sections to specify Grand totals accordingly.