|
|
-
Sergei Gundorov (whom you may remember from a previous post) has published a paper on MSDN where he talks about a very interesting solution his team wrote in the Microsoft IT department. The solution is probably one of the most advanced implementation of Excel Services I have seen to date and includes lots of ingenious customizations - the white paper, specifically, talks about how to use Excel Services as a landing pad for data and then get the file stream from it, customize it (add stuff into it) and serve it up to the user. You can find the paper on MSDN.
|
-
We have a surplus computer store near my house which I like mostly because their pricing on cables is not equivalent to the GDP of a small island state. They have some real wonky stuff in there - very old printers, monitors, etc. I passed through there today and saw this: 
Most-awesome-mouse-pad evah! It is so much better than the competition because it has space not for two, but FIVE 3.5 diskettes. I think the only way this could be even awesommer is if it had space for 5.25 disks.
|
-
I finally made the jump to Windows Media Center 2 months ago or so and, overall, I am much happier for it. I have had TiVos in my house since 2001 and it has gotten to be that I just never watch live TV anymore. I adore the concept of the DVR and am greatly annoyed when I dont have it at my fingertips. Except for a few hiccups when I started (some due to me and others due to the fact that I bought the Dell 420 when it just came out, before Dell has fully gotten rid of the kinks), the machine has been working famously for about 2 months now. And it's just great. There are a bunch of things I don't like about it, but for the most part they revolve around the programmability model. The software itself, out of the box, is absolutely awesome - it is SO much more responsive than TiVo (a Quad processor helps!) and the fact that you can upgrade HDDs and hardware generally is great too. The menu system is also miles ahead of TiVo, not to talk about integration with my home network and the Internet - totally and utterly sweet. The Movie Guide is a real nice touch and very easy to use. So is conflict resolution - I find it much better presented and handled. The one feature I really miss though is grouped-shows by title ordered by date.
|
-
My previous blog entry (which was completely misunderstood by some people who commented - I blame the fact that English is not my native tongue) reminded me of another poor coding behavior. Just so we don't have a repeat of that last post, let me clarify that I have nothing against the as operator - I use it all the time - I just have a problem with certain usage patterns that employ it. For whatever reason, people tend to use the "as" operator instead of the cast operator. A lot of times you will see code like this: MyClass c = o as MyClass;
c.DoSomething();
Where the following code makes MUCH more sense: MyClass c = (MyClass)o;
c.DoSomething();
I have two theories as to why people do it this way:
1. People are afraid of Exceptions.
2. People are used to dynamic_cast<>() from C++ which is the easiest way to check if a class is of a certain runtime type.
The reason the example above with the"as" operator is a Bad Thing is because it defers the exception to a later point in your code. If the class is not what you expect it to be, your cast will succeed (resulting in NULL) only to fail a few lines later on a NullReferenceException - which in turn makes debugging and logging harder to understand because you are not really seeing the correct exception.
Now, there are many valid usages of the as operator - but they almost always rely on the fact that you actually do something with the fact that the class is different than what you expect. So for example, if you want your Equals method to be able to handle classes different than your own, that's a GREAT place to use as: public override bool Equals(object o)
{
MyClass other = o as MyClass;
if (other == null) return false;
//....
}However, if you do not intend for your .Equals method to support comparison with other types (and have no other usage for the knowledge that the reference you got is not what you expected), you should definitely use the regular cast operator.
|
-
EDIT: Contrary to what people understood from this post, it is NOT a "Do not use Generics" post - I think Generics are a GREAT addition to the language/framework and I use them all the time. It is against a very specific usage of Generics
Short Answer: Rarely.
Slightly longer answer: When you are aware of the alternatives.
Yet longer answer:
I have seen this a couple of times recently and thought I'd drop a line about it. As always, take it with a grain of salt, but here goes...
You find yourself wanting to write a method that returns a value to the caller. Said value can be of many types and you decide to create a template out of it - a recent example I saw (which is very typical to the misuse of this mechanism):
T GetRegValue<T>(string path); // Get the value of a registry path
This method calls into the Win32 wrappers and gets back an Object, the code then returns it to the caller in the following manner:
T result = (T)objectResult;
I have heard many reasons as to why people choose to use this mechanism - the two that come up most often:
1. It's easier to understand/write/maintain or it is "cleaner".
2. It's more type-safe.
Lets go over those one by one...
It's easier to understand/write/maintain or it is "cleaner"
The amount of code that needs typing in this case is exactly the same as if you had returned an object. Compare and contrast:
string result = (string)GetRegValue("path"); // This is when using the "old and boring" way
string result = GetRegValue<string>("path"); // This is when using the "new and shiny" way
It's EXACTLY the same amount of code characters and I am pretty sure more people know how to read/parse casting operators (the (string) bit) than generic type parameters (the <string> bit). So it's the same amount of code (not easier to write), it's using a mechanism that less people know (not easier to understand). That leaves "easier to maintain" which I usually equate to "easier to understand".
We are left with it's "cleaner" but that's a whole new blog entry - to me both mechanisms look alike from the cleanliness point of view.
It's more type-safe
This is just not true. In the typical example, a cast is made to the requested type (T in this generic method) - casts in .NET are safe (that is to say, they will throw an exception of they do not work and if they do work they are guaranteed to work properly). The code doesn't even eliminate the cast - it just moves it to the inside of the method.
Here's my rationale behind why such a mechanism should not be used:
1. It's harder to debug.
2. It doesn't contribute anything.
It's harder to debug
In the cases where the type of the retrieved cannot be cast, your exception will be thrown inside the inner method which may throw you off when you are trying to figure out what's wrong. It's not a big deal, especially if you own the library that is making the call, but it's just a little harder to understand what went wrong.
It doesn't contribute anything
Generics are a more advanced language construct that casting and as such is harder to understand. At the end of the day, you are getting the same result and so should be using the simplest mechanism possible.
So when should I use this?
There are cases where this can be useful - for example, when some property of the return type is used to make the method smarter. This is where you go onto the gray line. The above snippet, when presented to me, actually looked a little different:
T result = Convert.ChangeType(objectResult, typeof(T)); // Used to be T result = (T)objectResult;
This is a subtle difference, but an important one. Here we are asking the runtime to force one type unto another. To understand the difference, consider the following call (first one is the generic version and the second the non-generic):
double d = GetRegValue<double>("path");
double d = (double)GetRegValue("path");
In this case, the fancy version will actually work whereas the non-generic one will fail with an InvalidCastException. The reason behind this is simple - the API method used to fetch the value from the registry returns a boxed integer. A boxed integer can only be cast into two things - an Object or an Integer - it can't be cast to any other type. For this to work, the caller would have had to do one of the following:
double d = (double)(int)GetRegValue("path"); // Cast the object into an int and then the int into a double.
double d = Conver.ChangeType(GetRegValue("path"), typeof(double)); // Coerce the value.
Both these choices ARE indeed somewhat less readable and perhaps less maintainable than the generic alternative - I personally could go both ways on this - not married to either of them. In the case of Registry access, the values are usually known beforehand and thus you probably know exactly what you are looking for so I would probably go with an object retval.
|
-
Scenario: You are working on preparing dashboards or uploading workbooks to your SharePoint site for usage with Excel Services. Every now and again, even though you are clearly interacting with the session, EWA will suddenly tell you that the session has timed out. Reason: Under some circumstances, the server may decide that it needs to recycle itself or stop servicing requests targeted at a specific workbook. In those cases, the server has no way of distinguishing between a timed-out session, and invalid session (a session identified by an invalid string internally - the same thing used by Excel Web Services) or a session that died because of the reasons just cited. Because of those reasons, the server gives the most generic session-related error it sees. What to do: Nine times out of ten, this occurs because a workbook is corrupt in some manner or contains something that Excel Services otherwise does not expect. Identifying the problematic workbook and then using the forums to send it (scrubbed of any private info of course) would be a great first step at trying to resolve the issue. Also, if the workbook has recently changed, it could be useful to go back to an earlier version and see what change actually caused the issue.
|
-
In the ODC 2008, I gave a demo of how to use Excel Web Services from a VBA client - the demo was basically just a managed library that wrapped a generated Web Services proxy. Since Microsoft no longer seems to supply a SOAP toolkit for office, this is the easiest way of achieving access to Excel Web Services. The library gives access to all of the important methods exposed by EWS and will allow users to read data/set data and otherwise manipulate an Excel Services session. The following VBA example shows how to use the library: Sub DoIt() Dim Es As New EwsCom.ExcelServicesSession ' Open the workbook. Es.Open "http://bluemonster/_vti_bin/ExcelService.asmx", "http://bluemonster/Test/Shared Documents/Wow.xlsx" Debug.Print "Session is:" & Es.SessionId ' Get a single cell result. Dim R R = Es.GetCell("Range2", True) Debug.Print "GetCell of Range2 got:" & R ' Set a single cell back. Es.SetCell "Range2", "My New Value" ' Get a range and fill the workbook with the results gotten from it. Dim MyRange As EwsCom.ExcelServicesRange Set MyRange = Es.GetRange("Range1", False) For Row = 0 To MyRange.RowCount - 1 For Column = 0 To MyRange.ColumnCount - 1 ActiveCell.Offset(Row, Column).Value2 = MyRange(Column, Row) Next Next ' Save the session workbook into a local file. Es.SaveWorkbook WorkbookType_FullWorkbook, "c:\temp\MyFile.xlsx" ' Close the session. Es.Close End Sub As you can see, the major difference between the way the library works and the EWS works is that the Session ID is wrapped so that it doesn't need to be used over and over again - as well as that annoying status array that's usually ignored. I have published the library on MSDN Code Samples - it's called EwsCom - enjoy.
|
-
MSDN has just published a chapter from my book "Professional Excel Services". You can find it on this link. This wouldn't happened without the great work of Siew Moi Kohr. Thanks Siew!
|
-
A long long time ago, in a building far far away, two Jedis developers called Sreepada Santhegudda and Sergei Gundorov showed me a solution they came up with for a single-select Analysis Services filter web part. While SharePoint ships with a set of filters that allow users to build interesting dashboards, and while one of them allows you to select dimension members for filtering, the solution is by no means a one-size-fits-all solution. Apart from the issue of having a somewhat convoluted way of doing single select (you essentially need to un-select all elements one way or another and then select the item you want - and then, when you want to switch from one member to another, you need to remember to un-select the previously selected one). They have sent me the solution eons ago and one way or another, I kept procrastinating and never posted it - Mike Reese prodded me enough that I felt totally ashamed of myself and so I finally got off my fat behind and posted it. Anyway - here's the solution, including source code, for your usage. They also have a document that explain usage and installation. Here's a screen-shot that shows the simplified selection mechanism: 
I have created an MSDN Code Sample library and have uploaded it to there. Enjoy!!!
|
-
Gabhan Berry (read his blog - he has lots of Excel programmability related information in there!) wrote a piece about how to make using optional parameters a little less painful when using C# to program against the Excel (and any other) COM PIA object model. While his solution makes life much easier in some scenarios, after reading the post, there were a few things that bothered me from a fragility of code point of view about his solution - specifically, the various issues with type safety (parameters and method names). While the mechanism he presents will work properly, I was thinking if I could come up with something that will not have those issues. I think I was able to come up with something, but it has it's own set of issues. The biggest one is the fact that it only works with C# 3.0 - not with 2.0. That said, here's the solution: OverGen is a tool I wrote that will take an assembly and find all methods in it that are optional and then generate overloads for all those methods that will allow the user to pass in a smaller amount of parameters. For each optional parameter a method has, an extension method will be generated. Extension Methods are basically a very simple compiler construct that allows a class to extend another class by way of adding methods to it - the extension is very superficial - no methods are actually added to the class but rather, the compiler knows that when such a method is called on class, it will try and find it in supporting classes. Here's an example of one of the methods generated by the tool: public static Microsoft.Office.Interop.Excel.Workbook Open(this Microsoft.Office.Interop.Excel.Workbooks @__thisParam, string Filename) { return @__thisParam.Open( Filename, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing); } Notice the "this" keyword on the first parameter? That's what makes this method an extension method. If you did not have this guy, this is what ANY call to .Open would have to look like, even if you only use the first parameter: app.Workbooks.Open("c:\\temp\\file.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); And here's what the code looks like if you are using the extension method shown above: app.Workbooks.Open("c:\\temp\\file.xlsx"); That's about it - you should be able to do this for every method that takes optional parameters. You can download the OverGen tool to enable you to create such extension method for every PIA you use. You can also download ready-made extension methods for Excel and Word.
|
-
Excel Services comes with a few performance counters designed to let an administrator know the load under which Excel Services is operating. When all performance counters are added, the list inside the management console looks like this: 
- Active Requests: This is the number of requests Excel Services is currently servicing. This number does not include requests that are queued in IIS or ASP.NET. Almost any request that Excel Services looks at will be accounted for here. The only cases where they are not is when the request is malformed in one way or another - in these cases it will be rejected before it's counted (and thus will not show).
- Active Sessions: This corresponds to the amount of sessions that are currently opened in Excel Services. Whenever a user starts communicating with Excel Services, a session is created (this will increase this counter). Whenever a session times out or gets explicitly closed, this number will decrease by one. A very large number (1000s) that never goes down probably indicates an issue with the server (it really depends on usage pattern, on how strong the machine is, etc). Note: When explicitly closing a workbook, the number may not fall immediately. It may take the performance counter up to 5 seconds to actually reflect the fact that the session has been closed.
- Average Request Processing Time: This number represents the average amount of time Excel Services has taken to process each request. A high number here (in the 10s of seconds) on a server probably indicates some issue.
- Average Session Time: This indicates the average life-time of sessions in the system. This number is really dependant on usage patterns - if users in the org tend to analyze and work with workbooks for a long time, then this number can reach quite high. If users tend to just look at dashboard information, it will probably be equal to the average Short Session Timeout value of the Trusted Locations used.
- Rendered Charts Requested/sec and Cached Charts Requested/sec: Whenever a user requests a chart from Excel Services (for example, by viewing an area in a workbook that contains a chart), Excel Services needs to look at the data and generate the chart image. Since this is a relatively expensive operation, the chart is then stored on disk. The "Rendered Charts" counter specifies how many times per second Excel Services actually went ahead and generated a chart image from data. The second number ("Cached Charts") specifies how many times a second an already-prepared chart was used.
- Current size of memory cache: This is probably one of the least useful and more confusing performance counter for Excel Services. It contains the value of certain types of memory allocations. The problem is that for performance reasons, the number does not contain all allocation originating from the various sub-systems of Excel Services and as such is not very useful.
- Excel Calculation Services Workbook Cache Size : This over-long name corresponds to the amount of bytes taken on disk for caching workbooks. When a workbook is first opened on the server, it is fetched from the source (SharePoint, UNC etc) and placed on the hard-drive of the Excel Services machine - this perf counter shows how much disk space these workbooks occupy.
|
-
Got this question from somebody who just installed the new version.. Previous builds did not know how to auto-fix invalid characters - with this version you can turn on auto-fix - but it will only work in some cases... 
When click on the Invalid Character Analysis, you get a widget at the bottom allowing you to tell ESCC how to fix invalid characters. When an invalid character is encountered, it will be replaced by the setting in the "Replace invalid chars with..." text box. The only caveat is that this only works on cells that contains values rather than formulas. So, if A1 contains the value Char(1), it will be auto-fixed and the value will change to whatever the replace text-box says. However, if A1 contains the formula "=Char(1)", ESCC will not automatically fix the problem but instead bring up the manual fix pane.
|
-
I uploaded version 914 onto the download page. This build adds two new features: Finer control over which features are available (and which are autofixed) as well as adding the ability to "fix" external references/RTD functions. The ribbon changed a bit, it now has a "Settings" button (notice that I removed the checkbox I added in build 731): 
When clicked, the user is presented with a dialog box for modifying the settings for the compatibility checker: 
Each available feature-check the compatibility check can do is listed here, with two checkboxes. Only the features that have the left checkbox checked will actually run when analyzing a workbook. Of those executed, only the ones that have "Allow autofix" checked will actually attempt to autofix the problem. To keep functionality the same, when you run the new version, the "Invalid Formulas" analysis (that's the thing that finds External References) does not have AutoFix turned on. Similarly, Invalid Character check is completely turned off. Autofixing external references/RTD Due to popular demand, the tool also knows how to fix External References and RTD functions. The way it does that is by checking what the value that resulted from the incompatible formula would have been and then just shoving that value into the cell/named range.
|
-
Towards the end of the development cycle of Excel Services, we saw some incredibly intermittent, incredibly rare issues with Internet Explorer crashing when doing certain operations. There were also a few people complaining about this on our forums. Apparently, there is a simple fix out there and it is described in this post.
|
-
Deepak posted a question on one of my posts and I thought that it was worth posting a blog entry about. I will try to break this down as simply as I can, since there are many permutations and it is not always obvious what's going on. There are also a couple of things one needs to understand before going forward: Managed Assemblies: When compiling a managed assembly, the developer has two basic options - they can either compile the DLL as platform agnostic or they can target it to a specific platform. Platform Agnostic: When compiled thusly, the runtime will JIT the assembly into what it deems to be the "correct" platform. For example, if the assembly is an executable and a user double-clicks it on an x64 machine, it will run as a 64bit application. If the assembly is a DLL and is loaded into a 64bit process, it will be JITted into x64 as well. Similarly, if the process is a 32bit process running on an x64 machine, and it loads a managed DLL, the runtime will correctly JIT it to x86, allowing the process to use it. Platform Specific: When compiled to a specific platform, the DLL will fail to load in any platform that it does not fit. In the 3 examples above, say we decided to target our binary at x86 instead of platform agnostic. The first example (double-clicking on an executable) will run our process in 32 bit compatibility mode. The second example (loading the assembly into a 64bit process) will fail because Windows does not support mixing binaries targeting different platform. The 3rd option (a 32 bit process loading the assembly) will work as expected. Unmanaged Assemblies: For unmanaged assemblies, the story is simpler - there is no agnostic setting* - all binaries are compiled to one platform or the other. And the same limitation as before applies - no mixing of platforms. So if you process is 64bit, you will only be able to load 64bit native DLLs. If your process is 32bit, you will only be able to load 32bit native DLLs. Okay, but how does this apply to UDFs? When Excel Services loads a UDF, it runs under the same limitation as any process does. That is to say, if your Excel Services process is running as a 64bit process, it will only be able to load 64bit DLLs. Since most .NET assemblies are agnostic, this is not an issue. Similarly, if your Excel Services process is running as a 32bit process, it will only be able to load 32bit processes (even when you are running on the x64 platform). So what's the problem? This story becomes a little hairy when your UDF DLL tries to load native DLLs. While your UDF DLL is (most probably) agnostic and, thus, will load for you seamlessly no matter what platform you are on, the DLLs it relies on may not be. Typically, this is what users go through when they hit these issues: 1. Users install Excel Services on a 32bit machine to play around with. 2. They absolutely adore Excel Services and thus start looking for ways to play with it. They discover UDFs. 3. The user now realizes that they can use older native DLLs to make their life easier (since the code is already written). 4. Wrapping the native old 32bit DLL with some managed code is easy and works just great. 5. The user decides that they want to use the full power of the x64 platform and install the x64 version of Excel Services. 6. Once there, the UDFs suddenly stop working. 7. Users get (rightfully) upset. What happens is that the native old 32bit DLLs will not be able to load into the 64bit Excel Services process. When Excel Services loads the UDF DLL, it gets JITted to 64bit properly. However, when that DLL tries to load the 32bit DLLs, it fails to load and cannot be used by Excel Services. I am one of these users. What can I do then? There are very few things you can do, sadly. Usually, none of them are easy. 1. You can recompile the C/C++ DLL into x64 and load that version from your managed UDF: A lot of times this is impossible to do (sometimes you dont have the code or the expertise). 2. You can rewrite the library in managed: This will make sure the problem is no longer there, but it also means potentially a lot of work (and is sometimes impossible because you may not know what's really going on in there). 3. Out Of Proc solutions: Multiple solutions can be thought up of how to activate the 32bit library in a separate process. However, this can be a relatively expensive thing to do and, depending on the scenario, can be too slow to be a realistic solution. ------ * Well, you can make an EXE that will load natively on both x64 and x86, but I don't think you can apply the same trick to DLLs.
|
|
|
|