Curious to know more about how OfficeWriter fits in with the Microsoft Application Platform? We’ve partnered with Andrew Brust from Blue Badge Insights to bring you an overview of the additive value OfficeWriter provides to the Microsoft stack.
In this powerpoint:
With the holidays just around the corner, schedules are becoming increasingly packed. Maybe you’re on the hunt for the perfect gift, or entertaining the slightly overbearing but well-meaning in-laws, or maybe, just maybe, you need to make those last minute travel plans. With all the hustle and bustle, it’s easy to overlook those seemingly insignificant but entirely necessary holiday cards.
As a developer I’m always looking to save time and automate just about everything…even my holiday cards. (Don’t tell my mother she still thinks I spend hours every year making them.) So in the spirit of the giving season, I wanted to share my sample code with you, to get you on your way to automating your holiday cards and back to searching for the perfect stocking stuffers.
To get started, you will need to have OfficeWriter installed in order to be able to use the sample code below. In my solution, I used OfficeWriter and its built-in WordTemplate functionality to do a server-side mail merge. If you do not have OfficeWriter, you can download a free evaluation here.
Next, you will need the sample code. Download the sample solution here. This solution comes bundled with:
Open the solution and run the generator to see sample holiday cards and mailing labels like:
Looking at the sample code you will see that with just about 10 lines of OfficeWriter code I can generate both holiday cards and mailing labels. Continue reading Automate Your Holiday Cards with OfficeWriter
OfficeWriter 8.3 is filled with exciting new features. Here are some of our favorites:
Auto filters are an easy way to view and filter data in a worksheet, and now you can add them programmatically with ExcelApplication in XLSX and XLSM files!
All you need to do is create an area on the worksheet where you want the auto filter to be, then apply the auto filters to that area. This works on data sets that already have data, and data sets that just have ExcelTemplate data markers.
wksht.AutoFilter.Area = wksht.CreateArea("A6:D14");
Remember, you only get one set of auto filters per worksheet, so if you set the auto filter to a new area, you’ll lose your original filters. Continue reading New in OfficeWriter 8.3: Auto Filters and More!
ASP.NET Web API is functionality that was released as a part of ASP.NET MVC 4 that lets developers easily build HTTP-based web APIs on top of the .NET framework. It is available for Visual Studio 2010 SP1 or Visual Studio 2012.
Web APIs are gaining more traction because of their ability to reach a wide variety of platforms. Unlike SOAP or WSDL-based services that use HTTP has a transfer protocol, web APIs fully embrace HTTP to convey information. It’s ridiculously easy to generate HTTP requests, which makes it simple to develop across different types of applications, operating systems, and platforms. The success of using an HTTP-based web API is clear when you look at the companies that are already operating under this model, such as: Facebook, Twitter, LinkedIn, Flickr, and Netflix. These organizations have hundreds of developers creating applications, particularly for mobile devices.
It’s not hard to see why ASP.NET would want to jump on this bandwagon.
I watched Scott Guthrie‘s talk about ASP.NET MVC 4. Scott Guthrie is the Corporate VP in Microsoft’s Server and Tools Business division, which manages tools such as Azure, IIS, and ASP.NET.
In the latest version of SQL Server, SQL Server 2012, Reporting Services now supports the Office 2007\2010 XLSX file format (aka Office Open XML file format) in its Excel renderer. In fact that is the default format for the Excel renderer. The old renderer for the legacy Excel 2003 XLS format has been named to “Excel 2003” but it has been deprecated and is not visible in the available export options list by default. For reference see the related section in the MSDN documentation. Although SSRS 2012 has now the ability to export an RDL report into an Excel XLSX workbook, the Excel renderer still has certain limitations. In this blog post, we will discuss some of these limitations and compare them to the OfficeWriter renderer in SSRS.
Probably one of the major limitations is that charts will be exported as pictures. In the related section in the MSDN documentation it states:
“Charts, sparklines, data bars, maps, gauges, and indicators are exported as pictures. The data they depict, such as the value and member labels for a chart, is not exported with them and is not available in the Excel workbook unless it is included in a column or row in a data region within a report.”
With the OfficeWriter renderer on the other hand, the charts will be native Excel charts with live data. Let’s see the difference in the following screenshots:
“Product Sales Report.xlsx” generated using the SSRS 2012 Excel renderer (note that the chart is a picture)
Continue reading Comparing Excel Export Functionality in SSRS 2012 to OfficeWriter
Are you encountering an “Object can’t be created” error or “Service Unavailable” error with your web applications when using a 64-bit OS and IIS6?
I was assisting a customer recently with what appeared to be a typical installation. He had a Windows Server 2003 (x64) machine, which was running IIS6. His classic ASP application was throwing an “Object can’t be created” error (ActiveX component can’t create object). This is a fairly common configuration error for customers running applications with 32-bit COM Objects on 64-bit operating systems. The typical resolution is to double check that the customer’s DLLs are registered properly in the registry. If that doesn’t fix things, we make sure that IIS6 is running in 32-bit compatibility mode:
cd %systemdrive%\Inetpub\AdminScripts
cscript.exe adsutil.vbs set W3SVC/AppPools/Enable32BitAppOnWin64 1
Unfortunately, after changing these settings his entire site was throwing a “Service Unavailable” error message.
Continue reading One Solution for a “Service Unavailable” Error Message in IIS6
Let’s say that you have a string “Top sales person in the Central region” in your data base. You want it to look like the following in a cell in an Excel worksheet:
Highlights:
Top sales person in the Central region
How do you do this?
In order to display text on multiple lines in a cell in Excel, two conditions must be met:
If you press ALT+ENTER in Excel, Excel automatically formats the cell with “Wrap Text” and inserts a new line character into the cell. But this approach won’t work if you are importing your data from an outside source, for example, if you are importing data with OfficeWriter’s ExcelTemplate object.
Formatting the cell with “Wrap Text” is as easy as right clicking the cell, going to Format Cell > Alignment and checking off ‘Wrap Text’. The next question is how to get the new line character into the cell.
In the example, we need to append “Highlights:” and the new character line to the text that’s already there. Let’s say that the text is in cell D8. Then the formula would be =CONCATENATE("Highlights", CHAR(10), D8). If the formula had to be applied to a series of cells, where you weren’t sure if there would be a comment or not, then you could wrap that formula in an IF formula: =IF(LEN(D8)>0, CONCATENATE("Highlights:", CHAR(10), D8), ").
Let’s suppose the text in the database already contained “Highlights”: “Highlights: Top sales person in the Central region”. Then how do you break apart the strings?
First we need to grab the “Highlights:” part. We can employ Excel’s LEFT(N_chars) function, which grabs the N left-most characters:
=LEFT(D8, 11) will return “Highlights:”
Next we need to grab just the second part. We can use Excel’s MID(text, start_index, chars) function to get a specific sub-string:
=MID(D8, 13, LEN(D8)-11) will return “Top sales person in the Central region”.
We can concatenate these together with the new line character: =CONCATENATE(LEFT(D8,11), CHAR(10), MID(D8, 13, LEN(D8)-11)). We can also wrap it in the similar IF formula if we only wanted to apply this formula if there was a comment. (Otherwise you will end up with #VALUE!).
(For example, manipulating the data in .NET code before importing it into a file using ExcelTemplate).
Just add the newline character to your text: “Top sales person in the Central region” –> “Highlights: /n Top sales person in the Central region”. When the text is imported, Excel will respect the new line character. Make sure that the cell is formatted with “Wrap Text” ahead of time.
Receiving an auto-generated email is often unpleasant, but when deployed correctly, they can be useful for both customers and companies. C# and SQL make it easy to grab information from a database and send customized emails with little effort.
Before sending emails, information from the database, like email addresses, needs to be collected by a query. A SQL query is just a string that asks a database for a specific table, but users should be able to dynamically change it depending on their needs. As such, certain lines need to be flexible. In the past, I had used string formatting to insert text into the query, which looks like the following:
string text = "I ilke {0} more than {1}, honestly";
text = string.Format(text,"dogs",cats");
[Image via Fabian Williams]
Last week I was working on some ASP.NET web forms that generated internal reports against MS CRM using ExcelWriter and I wanted to port the application to one of our SharePoint instances. Though it seemed simple at first, I ran into a few issues. One of the issues happened to be authentication related. It was a typical ‘double hop’ problem where this SharePoint instance was using integrated Windows NTLM authentication and my code was trying to access the CRM SQL Server database. By nature, NTLM is unable to pass the credentials to the database thus producing access errors. (You can find more information on the NTLM issue and using Kerberos as a solution here.)
Since we don’t have Kerberos configured on this environment, our best solution was Secure Store. This service allows a user to authenticate with domain credentials and then use an account established in Secure Store to access the database. In our case, this was the read-only CRM account. This also enables easy to use and convenient access control using AD groups. Continue reading Solving the ‘double hop’ issue using Secure Store