Friday, 31 May 2013

Recently I needed to update an Excel spreadsheet and then retrieve some recalculated values. The tricky part was that some cells that I needed to retrieve information from were formula cells (e.g. =A1+5). I needed to update some other cells first and then get the value of recalculated formula.

Unfortunately, this is not directly possible with OpenXML. If you simply try updating some cells and then retrieving the relying ones, you will get the original values for those cells, not recalculated. . This is because formula cells don't store any values, just... formulas. You can only force recalculation by opening your document in the Excel application.

Knowing this I implemented a Refresh method, that opens the Excel app in background and then closes it immediately and saves changes. Below I present my sample code.

Prerequisites
In order to compile the following code you will need the Microsoft.OpenXML SDK 2.0 (DocumentFormat.OpenXML NuGet package) and reference to Microsoft.Office.Interop.Excel (used for opening the Excel app to recalculate formulas).

I hope the code is self-explanatory and doesn't require more comments. You can optimize it for you needs e.g. when updating/reading multiple cells at once you may want to open the doc only once. Currently my code opens it and closes for each read/update request.

Wednesday, 22 May 2013

I'm happy to announce that today I've become a Microsoft Certified Solutions Developer (MCSD) in Web Applications area. To achieve that I needed to pass 3 exams. Here is my short summary of each of them together with some useful preparation materials:

70-480 - Programming in HTML5 with JavaScript and CSS3

This exam begins your journey to the MCSD certificate. In general it covers exactly what is named in its title: HTML5 & CSS3. You could also use some jQuery knowledge. If you are a web developer with multiple years of experience this should be a piece of cake for you. In case you need to refresh your memory on some topics I recommend watching the free video tutorial at the Microsoft Virtual Academy:

70-486 - Developing ASP.NET MVC 4 Web Applications

This exam tests your knowledge of the Asp.Net MVC4 framework. To be honest I can't really remember if it includes WebApi questions, but it's worth to learn it anyway, as it's required for the last exam. Again, if you worked on several MVC4 projects there is nothing to be afraid of.

Before I took this exam I browsed the following book to make sure I'm not missing anything:

70-487 - Developing Windows Azure and Web Services

For me this was the hardest exam. It is because it covers a wide range of different topics. All required technologies are somehow related, but at the same time they are independent frameworks:

Windows Azure

WCF

MVC4 WebApi

Entity Framework

Other Data Access

Despite the exam`s title I was under impression that there were not that many questions related to Windows Azure. The basic overview of Azure features would suffice to answer most of them. There was a lot of questions related to WCF & Data Access though. Luckily there are excellent study guides available. Here are 2 that I liked most:

Study Guide #1
My personal favourite; it relays strongly on pluralsight video trainings, which are usually very good.

Study Guide #2A nice alternative for those of you who don't have access to pluralsight. Most links are referencing free online materials.

If you read/watch all linked materials you will be good to go ;) Good luck future MCSDs!

In addition it should be displayed in the middle of the list when possible (it's not possible for the first element).

This is what I wanted to achieve:

The first requirement is quite easy to fulfill by using ListView.ScrollIntoView() method. However, this doesn't satisfy the second requirement, as you don't have any control on where exactly the item will appear.

Solution

Instead of working with the ListView directly I worked with the included ScollView control. Here is my method for scrolling with some comments:

public void ScrollToSelected(ListView list, Object selected)
{
var scrollViewer = list.GetFirstDescendantOfType<ScrollViewer>();
if (scrollViewer == null) return;
// Calculate the offset to be used for scrolling.
// In my case I use ViewPort height, index of the selected item and a fixed value 3 to adjust the result
double halfList = scrollViewer.ViewportHeight/2;
int itemIndex = list.Items.IndexOf(selected);
double scrollOffset = itemIndex - halfList + 3;
// If offset happens to be bigger than scrollable height use the scrollable height
// Possible for items from the end of the list
if (scrollOffset > scrollViewer.ScrollableHeight)
{
scrollOffset = scrollViewer.ScrollableHeight;
}
// scroll to calculated offset
scrollViewer.ScrollToVerticalOffset(scrollOffset);
}

Tuesday, 14 May 2013

Recently I uploaded my first Windows 8 app to the Windows Store. At first, my app didn't pass the certification process because of the following issue (Notes from Testers):

The app has declared access to network capabilities and no privacy statement was provided in the Description page. The app has declared access to network capabilities and no privacy statement was provided in the Windows Settings Charm.

Luckily for me the issue is widely described here. In short, if your app uses internet you need to define a privacy policy. the privacy policy needs to be linked from the app description page and also avaiable from the Settings Charm.

Rebuild your package and resubmit to the Windows Store. At this time that issue should be resolved.

When adding a Privacy Policy item to the Settings Charm you may consider using the Callisto project and its Settings Flyout support. It makes complex customization of that Settings Pane easier. In my case however it was shorter to add that single command in a regular way.

BTW I was really surprized how quick the certification process was - it took only 8h from the moment of submission! It's a huge improvment comparing to my experience with WP7 apps. I hope it stays that way.