Hello,
I am trying to create a calendar report in Excel using Smart Markers.
So my data has to appear in different parts of the Worksheet based on Date and Time.
Is there a way to do that?
Thanks for your query.
Well, Smart markers are pointers or some means placed in cell(s) which are filled with data/results from your data source you specify. The data is filled vertically(by default) and horizontally offsetting from the maker cell. You may skip n number of rows (check “skip” parameter). Please see the document for your complete reference:
Although I am not entirely certain about your scenario but Smart Markers can be placed dynamically in the Excel spreadsheets in code (via Cell.PutValue() method). I think you may write your own logic and code by yourselves to conditionally (you may try to use “if…else” or switch case, etc.) insert markers based on specific data in the cells in different parts of the worksheet and then process the markers accordingly.
Let’s see if I can clarify my scenario -
Say I have a set of events for a week. Appointments, meetings, etc. I want to display them in a 5x18 grid which represents a working week. (each row is 30min) Each event would go into a cell that is based on the Date/DayOfWeek and Time.
The dataset would look something like this:
Mon 3/11/19 - 9am - meeting with the Bobs
Tue 3/12/19 - 10am - Debugging discussion
Weds 3/13/19 - 8am - Breakfast at Tiffany’s
Weds 3/13/19 - 2pm - Gym
Thurs 3/14/19 - 8:30am - Dentist appt
Fri 3/15/19 - 9am - Gym
Fri 3/15/19 - 7pm - Happy Hour
Is there a way to achieve this without using code?
About using code - I am not sure I would need Smart Markers then. I would just populate the spreadsheet directly. What I am trying to do here is avoid writing code for every report and just limit myself to an excel template
Well, I find there may not be any automatic way to achieve your custom oriented report in Ms Excel using any option. I think you may first simply create your grid (5x18) in a template file (either by using Aspose.Cells APIs or MS Excel manually). This would be a blank grid like table where you may apply your desired formattings (e.g set captions for the columns, apply borders, apply background/foreground colors, etc.). Now you will take it as model or template and (you may open the template file via Aspose.Cells APIs) and fill the grid/table with your desired data via Aspose.Cells APIs and then save it to generate the report. Even if need multiple grids in a single worksheet, you may create a range based on your grid/matrix, then create your destination range (in accordance with your source range). Now try using Range.Copy() method to copy data/formatting,etc…
Hope, this helps a bit.
We think you may arrange names of your smartmarkers according to the time grid of your data, and then determine which marker should be used for the data according to the data’s time value. For example, your markers maybe named as
“M1-1”,”M1-2”,…,”M1-18”
…
“M5-1”,”M5-2”,…,”M5-18”
Where the first number represents the weekday and the second represents the 30minutes sequence.
Then, when filling data into the designer, you may set the data source by code like:
workbookdesigner.SetDataSource(“M” + GetWeekday(data) + “-“ + GetMinuteBlock(data), data);
Where the methods of GetWeekday() and GetMinuteBlock() is your implementation to determine which cell in the 5x18 grid your “data” should be in.