I recently did a Proof of Concept (PoC) at one of the largest plantation company in Indonesia. The PoC was about creating an Excel-based Office Business Application (OBA) using VSTO for managing their vehicles usage. Can’t say much about the app. One thing I can tell you about and I’ll write in this post is there’s a need to select/pick date and time from within Excel worksheet. Off course, you can just type in the date and time value and Excel will validate it, and certainly it will tell you if there’s error in typed value. But, what about if I want to select the value from a date/time picker, how do I create that functionality using VSTO?
The easiest way is using Windows Forms control’s DateTimePicker. I assume you’ve create the VSTO Excel project. Open Excel worksheet, and drag the control. Then, you have the picker. Let’s run it.
Well, it works.
Now, let’s change zoom level to a bigger or less than 100%, then click the DateTimePicker, what happens? I’m sure you’ll get this message.
As far as my VSTO kung-fu goes, there’s nothing we can do about it. If somebody knows how to cheat it, please share. Then your kung-fu is certainly better than mine :)
So, should we give up? No way. Instead, let’s change the way to accomplish the same things and start working the workarounds. I’ve managed to create a some kind of control for this purpose. I’ll explain some main parts of the control, and you can always skip it to download the source code.
The Control
The control is a date time picker for selecting a date/time and filling the selected date/time to a specified target cell. The control will be displayed by double clicking the cell, like the nature of Excel behavior if you want to edit a cell value. Off course, the control should be displayed at exact position as the target cell. The control is basically a Windows Form like below.
Let’s see what is behind the scene. Make sure you add reference to .NET libraries shown above.
Properties and Constructors
Some properties and constructors added in the form class:
1: /// <summary>
2: /// Get the selected date time value
3: /// </summary>
4: public DateTime SelectedDateTime
5: { 6: get { return TheDateTimePicker.Value; } 7: }
8:
9: /// <summary>
10: /// Reference to worksheet. Used for getting some values when positioning this form
11: /// </summary>
12: public Tools.Worksheet Worksheet { get; set; } 13:
14: /// <summary>
15: /// Reference to target cell/range where the control should be displayed.
16: /// </summary>
17: public Excel.Range TargetCell { get; set; } 18:
19: public DateTimePickerForm()
20: { 21: InitializeComponent();
22: //Default format
23: TheDateTimePicker.CustomFormat = System.Threading.Thread.CurrentThread.CurrentUICulture.DateTimeFormat.ShortDatePattern + " HH:mm";
24: this.Load += new EventHandler(DateTimePickerForm_Load);
25: }
26:
27: public DateTimePickerForm(Tools.Worksheet Worksheet, Excel.Range TargetCell): this() { 28: this.Worksheet = Worksheet;
29: this.TargetCell = TargetCell;
30: }
Location, location, location
I tell you something, creating the control is easy. When it come to position it on Excel worksheet, it is the real challenge. I want the control to be positioned at exact position where the target cell is located. How do I do that?
The logic should be simple. Just get the Left (x) and Top (y) position of the target cell, and assign them to form’s Left and Top properties. It’s quite right, but let’s change the zoom level, you’ll get unexpected result. For that, we need to take zoom value into account, as shown in below code.
1: void DateTimePickerForm_Load(object sender, EventArgs e)
2: { 3: ChangeFormPosition();
4: }
5:
6: private void ChangeFormPosition()
7: { 8: int relativeLeft = 0;
9: int relativeTop = 0;
10: //Get current worksheet zoom, so the form will be positioned correctly againts any zoom value
11: int zoom = Convert.ToInt32(Worksheet.Application.ActiveWindow.Zoom);
12:
13: for (int index = 1; index < TargetCell.Column; index++)
14: { 15: relativeLeft += Convert.ToInt32(Math.Round((double)((Excel.Range)Worksheet.Cells[1, index]).Width * 4 * zoom / 300,
16: MidpointRounding.AwayFromZero));
17: }
18:
19: //Convert cell left position (in point) to pixel
20: int left = Worksheet.Application.ActiveWindow.PointsToScreenPixelsX(relativeLeft);
21:
22: for (int index = 1; index < TargetCell.Row; index++)
23: { 24: relativeTop += Convert.ToInt32(Math.Round((double)((Excel.Range)Worksheet.Cells[index, 1]).Height * 4 * zoom / 300,
25: MidpointRounding.AwayFromZero));
26: }
27:
28: int top = Worksheet.Application.ActiveWindow.PointsToScreenPixelsY(relativeTop);
29:
30: this.Left = left;
31: this.Top = top;
32: }
Event handlers
The rest of the control’s code is about handling events, especially ‘Enter’ and ‘Escape’ key press. Here we go:
1: private void TheDateTimePicker_KeyDown(object sender, KeyEventArgs e)
2: { 3: DateTimePickerForm_KeyDown(sender, e);
4: }
5:
6: private void DateTimePickerForm_KeyDown(object sender, KeyEventArgs e)
7: { 8: //Detect 'Enter' key press. If it does, set dialog result as OK and close the form.
9: if (e.KeyData == Keys.Enter)
10: { 11: this.DialogResult = DialogResult.OK;
12: this.Close();
13: }
14: else if (e.KeyData == Keys.Escape)
15: { 16: this.DialogResult = DialogResult.Cancel;
17: this.Close();
18: }
19: }
20:
21: private void linkLabelOK_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
22: { 23: this.DialogResult = DialogResult.OK;
24: this.Close();
25: }
26:
27: private void linkLabelCancel_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
28: { 29: this.DialogResult = DialogResult.Cancel;
30: this.Close();
31: }
How to Use It?
The control should be usable in any VSTO Excel project. Here is the walkthrough to use it.
1. Create a Excel project, let’s say a Excel Workbook project. A Excel workbook should be created and opened. Add reference to the control assembly, in this case I name it “DyCode.VSTO.Controls”. Yes, DyCode is my company :)
2. Click a target cell where you want to date/time value to fill and where the DatePickerControl to show. Then name it as “DateTime_Cell”, for example.
3. With the target cell is still selected, look at the Properties window, switch to event view. Double click to BeforeDoubleClick event to add code for its handler
4. Add code to implement BeforeDoubleClick event handler, something like this. The code should be self-described.
1: private void DateTime_Cell_BeforeDoubleClick(Microsoft.Office.Interop.Excel.Range Target, ref bool Cancel)
2: { 3: //Cancel it.
4: Cancel = true;
5:
6: //Instantiate the date picker form and pass the Worksheet reference and target cell
7: DateTimePickerForm f = new DateTimePickerForm(this, Target);
8:
9: //If target cell contains value, assign it to the picker.
10: DateTime currentValue = DateTime.Now;
11: if (DateTime.TryParse((String)Target.Text, out currentValue))
12: { 13: f.TheDateTimePicker.Value = currentValue;
14: }
15:
16: //Get the selected date/time value
17: DialogResult r = f.ShowDialog();
18: if (r == DialogResult.OK)
19: { 20: Target.Value2 = f.SelectedDateTime;
21: }
22: }
5. Let’s run the project. Double click to the target cell (DateTime_Cell) and you’ll get:
Not bad, isn’t it? Just grab the source code and play with it and let me know what you think. The source code for both control and project sample is here:
That’s it.