Category Archives: Programming

ASP.NET, ExcelWriter, Knowledge Base, OfficeWriter, Programming

Save PDF file to HttpResponse

In a previous blog post we discussed how OfficeWriter 10.0 introduced the ability to save an Excel workbook to a PDF document. When working in a web environment it is common to want to send the generated file to the browser for your end user to download and view on their own machine.

Step 1:
Generate your workbook. A very simple example might be:

var xla = new ExcelApplication();
var wb = xla.Create(ExcelApplication.FileFormat.Xlsx);
var ws = wb[0];
ws.Cells[0, 0].Value = "Hello";
ws.Cells[0, 1].Value = "World!";

Step 2:
Define a helper method to write the file byte to the current response stream:

public static void WriteFileToResponse
(HttpContext context, byte[] bytes, string fileName)
{

var bytesLength = bytes.Length.ToString(CultureInfo.InvariantCulture);
var response = context.Response;
response.Clear();
response.Buffer = true;
response.AddHeader("Content-Length", bytesLength);
response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
response.ContentType = MimeMapping.GetMimeMapping(fileName);
response.BinaryWrite(bytes);
response.Flush();
response.End();
}

Step 3:
Save the PDF to a memory stream and call our helper method we just defined. This has the benefit of avoiding disk IO. This may vary if your application actually needs to persist the generated PDF.

using (var memoryStream = new MemoryStream())
{
var fileName = "generatedfile.pdf";
wb.SavePdf(false, memoryStream);
memoryStream.Seek(0, SeekOrigin.Begin);
WriteFileToResponse(HttpContext.Current, memoryStream.ToArray(), fileName);
}

And that’s it!

ExcelWriter, Knowledge Base, OfficeWriter, Programming, Uncategorized

How to Calculate Unsupported or Custom Formulas on the Server with ExcelWriter

Beginning in OfficeWriter 9.1, ExcelApplication’s calculation engine will offer the ability to implement custom formulas. This feature should prove helpful to users whom would like to calculate their own custom formulas or calculate formulas not currently supported by ExcelWriter on the server using ExcelWriter. This tutorial will show you how to implement these formulas.

Creating Your Own Formula

  1. Create a class that implements an interface provided by ExcelWriter called IFunction. The IFunction interface implements a method called Calculate.
  2. Inside of your class, create the Calculate method with this signature: 
    FunctionValue Calculate(IList<FunctionValue> args, Cell currentCell)
  3. Inside of the Calculate Function, code the logic of the formula you would like to implement.
    1. The first argument of Calculate is a list of FunctionValue objects. FunctionValue objects are how we pass values in and out of formulas. FunctionValues have several properties available to them, so please see the documentation for more information about how you can use the properties in your formula logic.
    2. The second argument of Calculate is the cell that contains the formula. Please see the documentation for more information about Cell objects and their available properties and methods.

Registering the Formula with ExcelWriter

  1. Once the formula is written, register the function using Workbook.RegisterCustomFunction. 
    WB.RegisterCustomFunction("MyFormula", new MyClass());

Calculating the Formula and Removing it From a Workbook

Once your formula is registered, you can use WB.CalculateFormulas to have ExcelWriter calculate the value of any cells that use the formula in your Workbook. If you are generating Excel output using ExcelApplication.Save, please note that if you have implemented a custom formula that it is not recognized by Microsoft Excel, the end user will not see the calculated values in the output workbook when opened in Excel. To get around this issue, you can use Workbook.RemoveFormulas or Worksheet.RemoveFormulas to remove the formulas from the worksheet or workbook, while leaving the last calculated values in the cells.

Here is an example of what your finished code should look like:

public class sample
{
   MyMainMethod()
   {
      ExcelApplication xla = new    ExcelApplication(ExcelApplication.FileFormat.Xlsx);
      Workbook WB = xla.Create(ExcelApplication.FileFormat.Xlsx);
      WB[0]["A1"].Formula = "=COUNTARGUMENTS(4, 5, 6)";
      WB.RegisterCustomFunction("COUNTARGUMENTS", new Formula());
      WB.CalculateFormulas();
      //Optionally remove all formulas from the workbook, so only values remain. This is good in case you are using a custom formula that Excel will not be able to calculate.
      WB.RemoveFormulas();
      xla.Save("output.xlsx");
   }
}

class Formula : IFunction
{
   public FunctionValue Calculate(IList<FunctionValue> args, Cell cell)
   {
      //Returns the number of arguments
      return new FunctionValue(args.Count);
   }
}

ASP.NET, Knowledge Base, OfficeWriter, Programming

How to Use Process Monitor (ProcMon) to Troubleshoot Web Applications

Process Monitor is a great tool that can help you troubleshoot applications when error messages alone just aren’t enough information to solve a problem. Process Monitor works by logging in-depth about the actions of particular processes. It will give you in-depth information about file access, registry access, threading, and permissions. In this how-to tutorial, we will show you how to collect information on the process that ASP and ASP.NET web applications run on – wpw3.exe.

Step 1:  Download Process Monitor

You can download Process Monitor here. http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

Step 2:  Open Your Web Application

Go to the page in your web application before your error occurs. You need to be easily able to trigger the event that causes the error while ProcMon is running to avoid collecting too much information.

Step 3:  Monitor the w3wp.exe Process

  1. Reset the filter by clicking Filter -> Reset Filter
  2. Add the w3wp.exe process to the filter by going to Filter -> Filter…
  3. A dialog box will appear.
  4. Create a rule that says “Process Name is w3wp.exe”
    Process is w3wp.exe Rule
  5. Click “Add”
  6. Click “Apply” and then “OK” to exit the Dialog

Step 4:  Collect Information from ProcMon

  1. Please make sure that the Capture icon (shaped like a magnifying class) is enabled. There should NOT be a red “X” through it.
    Capture Events
  2. Go to your web application and trigger the error.
  3. Once the error occurs, go back to ProcMon and click the Capture Icon to stop capturing events.

Step 5:  Examine the ProcMon Logs

  1. The first thing you should do when examining the logs is to see if anything in the “Result” column is not “SUCCESS”. Please take notes of any warnings or errors.
  2. Once you find the errors, determine if they are relevant to your issue.
  3. If you would like to save the logs, you can by going to File -> Save.

ExcelWriter, Knowledge Base, OfficeWriter, PowerPointWriter, Programming, Reporting Services, Uncategorized, WordWriter

How to Send OfficeWriter Output in an E-mail

Sending OfficeWriter output in an e-mail is possible while either using OfficeWriter in .NET or with SSRS integration. When using the OfficeWriter .NET API, you can use the .NET MailMessage class to send your OfficeWriter output as an attachment in an e-mail. In SSRS, you can send the output as a subscription-based e-mail.

Sending OfficeWriter Output Using the .NET MailMessage Class

In order to send OfficeWriter in an e-mail output using .NET, you need to use the MailMessage object. If you save the workbook,  document, or presentation to a stream, you can create an attachment out of the file in the stream. This attachment can be added to the MailMessage object and sent to the workbook’s or document’s end users.

//Open Word Template
WordTemplate wt= new WordTemplate();

//Do some databinding and processing here
//YOUR CODE HERE

//Create a file stream that will save the output file on the server
FileStream myStream = new FileStream(@"C:\temp\outfile.xlsx", FileMode.Create);

//Save the output to the stream
wt.Save(myStream);

//Create an Attachment of the file in the stream
Attachment myAttachment= new Attachment(myStream, "output.xlsx");

//Create a mailmessage obj
MailMessage mailObj = new MailMessage("From Address", "To Address", "Subject", "Body");

//Add the attachment
mailObj.Attachments.Add(myAttachment);

//Connect to the server that will send the email
SmtpClient SMTPServer = new SmtpClient("thenameofyourhost");

//Set credentials
SMTPServer.UseDefaultCredentials = true;

//Try sending
try
{
    SMTPServer.Send(mailObj);
}
//Basic error handling
catch (Exception ex)
{
    Response.Write(ex.ToString());
}

Sending OfficeWriter Output Using a SSRS Subscription

If you are integrating OfficeWriter with SSRS, it also possible to send OfficeWriter output by using subscriptions. Be aware that the credentials to run the report must be stored inside of the report.

  1. After uploading your report to the SSRS server, open Report Manager
  2. Find your report and from the Drop Down, select “Manage”
  3. In the left-hand menu, select “Subscriptions” and then “New Subscription”
  4. Fill out the form, including:
    1. Who you want to send the report to
    2. When you want to send it
    3. What parameters the report should run with
    4. Be sure to select either “Excel designed by OfficeWriter” or “Word designed by OfficeWriter” as the rendering format.
      RenderFormat
  5. Click “Ok”
  6. Your new subscription should now appear in the Subscriptions list for your report and will run at the time you specified.

Excel, OfficeWriter, Programming

March Madness: Using Excel to Pick the Upset

Bracket Mania

It’s that time again! Time to get out the office pool and place all of your stock in one team to take you to the finals and earn you those much sought after office bragging rights. Last year, to aid in my bracket-making decision, I decided to write a way to be able to generate a randomized bracket for the NCAA college Men’s Basketball Tournament using Excel. This year, I dug a bit more into using an alternative method, the KenPom rating, to see if I could predict a better bracket. You can dig into this stuff endlessly (or so it seems), starting out with the source of the rankings at KenPom.com. KenPom.com is a college basketball statistics site that has been featured on FiveThirtyEight, Mediaite, and The Wall Street Journal for its predictions in game outcomes. Ken Pomeroy, the creator of the site, has statistics dating back to the 2003 season, and his research is used by many college basketball teams to gain a competitive advantage.

The bracket I made this year was built in a couple of different ways. First, I used the seed strategy, where a higher seed historically wins X% of the time. Second, I used KenPom to figure out how the match-ups might work based on a random outcome. This second method does allow for some very volatile outcomes, so use at your own risk!

I am attaching a project that goes into all of the details of generating the brackets, and I have updated it from last year to get the latest attempt to win some bragging rights.

Download the Brackets and Excel Spreadsheets

To see the completed Excel spreadsheet and my 2014 bracket picks, click the links below. May the odds be ever in your favor!

The full Excel file: NCAATournamentBracket
The C# project: TournamentBracket_2014
My tournament bracket: TournamentBracket_2014_Final

Note: To save the bracket, you need to turn off the auto-calculation feature. This is due to Excel always updating the RAND() output on each change. I recommend changing it to Manual Calculation, and using F9 to run it when you want new values. Don’t forget, this may eventually run through every possible outcome; let me know if you end up winning anything from this!

Predictions

And the final prediction for this year is: Arizona wins it all.

Yes, the likelihood of me being correct is small, but given the current odds (6 / 1), I am feeling pretty good about it. Which does, of course, guarantee that this will be the incorrect bet.

Editor’s Note: Chad (the author of this post) hails from Arizona, so fair warning, there may be some home-state bias in this post.

Programming

How Not Using Stack Overflow Solves All of My Problems

1 Comment

[OfficeWriter developer and Stack Overflow user, Seth, lets us in on the thought process and psychology behind Stack Overflow, by delving into one way he uses the online community to answer his programming questions. Anything sound familiar? Or relatable? Do you use Stack Overflow in a similar fashion? Let us know in the comments section.]

stackoverflow_post_seth

Stack Overflow is a question and answer site for professional and ardent programmers. It’s a part of the Stack Exchange network of Q&A sites. Their aim is to assemble a resource of quality answers to any and all questions pertaining to programming.

There are a lot of great things about Stack Overflow:

  • It’s free.
  • The point system incentivizes people to help others.
  • The best answers have the highest visibility (usually).
  • There are experts and legendary programmers answering your questions.
  • It’s almost like a sport or competition for those answering questions.

But my favorite thing about StackOverflow is often overlooked: fear.

I can’t tell you how many times I have been so afraid that my question will get downvoted into oblivion that I end up finding the solution while writing it. (I’ll give you a hint: it’s way more than the actual number of questions I’ve posted.)

You see, posting a question on SO is like a code review to the umpteenth power. You’re opening up, not only your code, but your thoughts, explanations, and questions to be scrutinized and judged by anyone in the world. And when you throw tags on your question, you’re basically begging people who know a lot about the subject to come and take their turn.

Also, an increasing number of companies list jobs on careers.stackoverflow, and even more companies have asked me if I have an SO profile…that’s pretty daunting. Companies may be looking at and analyzing my knowledge (or lack thereof) to determine if I’m worthy of their employment!

Not only are prospective employers trolling about there, but the people on SO are my peers. They may end up being co-workers of mine one day. I don’t want them to think I’m an idiot!

And let’s not forget about ego. I worked really hard to get my measly ~2,000 points. It may not seem like much when compared to the Jon Skeet’s of the world, but I answer a lot of questions and get no points, and the questions I ask are usually really domain-specific and never get upvotes. It took me years to get that many points! Because I worked hard for my points, Continue reading How Not Using Stack Overflow Solves All of My Problems →

Knowledge Base, OfficeWriter, Programming, WordWriter

Creating Dynamic Links With MergeFields in Microsoft Word

Introduction

MergeFields allow users to import dynamic content in Microsoft Word. While this feature is very useful in many situations, it can be unclear how you must handle MergeFields nested inside of fields like hyperlinks. A user might want to have both dynamic link test and destinations for their hyperlinks. Unfortunately, how to achieve this is unclear because Hyperlink field codes only take a destination parameter. There is no place to put a merge field for your dynamic display text. In this article, I will show how to create fully dynamic links for Microsoft Word 2007 and up!

Step 1: Adding a Hyperlink Field

  • Put your cursor where you want to place the hyperlink
  • Go to Insert->Hyperlink
  • In the dialog box:
    • Next to Text to Display, enter something like “Temp Display Text”
    • Next to Address, enter something like “Temp Link”

You Should Now Have Something Like This:

KB_Step1

Step 2: Adding a Dynamic Destination Location

  • Press ALT+F9 to see the field code.
  • Delete the “Temp Link” text inside of the quotes.
  • If you have a base url location, enter it in between the quotes. Then proceed to the next step.
  • Now we must add a MergeField to get dynamic urls.
    • Put your cursor before the closing quote of your destination location.
      • Go to Insert-> Quick Parts-> Field.
      • Select MergeField from the Field Names menu.
      • Add the Field Name to identify the MergeField.
      • Click “OK” to exit the dialog.

You Should Now Have Something Like This:

KB_Step2 Continue reading Creating Dynamic Links With MergeFields in Microsoft Word →

ASP.NET, Knowledge Base, Programming

Introduction to VSTO Document-Level Customizations

For many years, the only way to add interactivity to an Office workbook or document was to use VBA Macros. However, in more recent years, Microsoft has offered a newer .NET developer tool called Visual Studio Tools for Office (VSTO). To customize a particular document or workbook using VSTO, you can use a project type called “document-level customization.” While the end-user functionality of these solutions overlap, they have very different development processes and practical implementations. In this article, we will discuss how to use VSTO document-level customizations and how they differ from their predecessor.

VSTO in a Nutshell

VSTO offers a developer two different project types: document-level customizations and application-level add-ins. Document-level customizations are projects based around a single workbook or document. Application-level add-ins, on the other hand, are add-ins affecting the UIs of Microsoft Word, Microsoft Excel, Microsoft PowerPoint, Microsoft Outlook, or Microsoft InfoPath applications themselves. In this article, we will talk about document-level customizations.

By leveraging the .NET framework, VSTO can turn a document into a full-fledged application that must be installed onto an end user’s computer. While using .NET offers a programmer nearly limitless possibilities and integration with many other services and libraries, it also means that it requires Visual Studio and strong coding skills to develop the customizations. Updates to a customization will be issued from a  server or disk where the customization is deployed. However, the updates are not without limitations. VSTO updates the code attached to a document, but it does not update the visual formatting done to workbook via Visual Studio’s Designer or Excel. All visual formatting must be done in code to be included in automatic updates. A second limitation is that a user will not be able to have more than one version of the customization on their machine at one time.

VSTO Advantages VSTO Disadvantages
  • Offers full .NET framework integration
  •  A lot of developer and client requirements
  • More robust projects
  •  Because it requires VS Premium or Ultimate, it may be more expensive to develop
  • Offers new project type: Task Pane
  •  Cannot have multiple versions of a project installed on a client machine
  •  All distributed applications will be updated to be the most recent version of the application
  • Must have strong coding skills to use
  •  Uses Visual Studio to develop code
  •  VSTO will update the code attached  to a document, but it will not update designer or template formatting
  •  Can use VBA macros inside of documents containing VSTO document-level customizations
  •  VSTO code is not located within the document, but is installed separately

Compared with VBA

VBA has been around since 1993 and is the simplest way to add interactivity to an Office document. The basic advantages and disadvantages of using VBA are outlined below: Continue reading Introduction to VSTO Document-Level Customizations →

ASP.NET, OfficeWriter, Programming

Released in OfficeWriter 8.6.1 – IEnumerable Feature

In OfficeWriter 8.6.1, we added a new feature that may have been flying under your radar, and I wanted to let you in on the story of how it came about.

When I first arrived at SoftArtisans I went through training of our entire product line.  As I was reviewing the training material, I came across the ExcelWriter Template section. I saw how to add data markers to my spreadsheet, and it seemed intuitive, so I went ahead and tried it:

ienumerable

At that moment, I was thinking “This is pretty slick. Now, I can just pass things into the spreadsheet!”  I’m not the greatest at reading documentation; I just like to learn by doing, so I opened Visual Studio and started writing code:


class Expense

{

double Amount { get; set; }

string Description = "";

bool Approved { get { return (Amount > 1000); } }

}

&nbsp;

for (int i=0; i<1000; i++)

expenses.Add(new Expense(i));

 

Looking good so far.  What I did next, however, was my downfall:


ExcelTemplate xlt = new ExcelTemplate();

xlt.BindData(expenses);

Featured, Programming

10 Tips for Handling Inherited Code

[Kate, one of our developers at SoftArtisans, was pulled onto a project to do consulting work. Below, Kate discusses her work and how she learned to handle inherited code. Read about the lessons she’s learned after being gifted a whole new project of code.]Credit: HubSpot

Recently, I inherited the code for a pretty large project, and anyone who knew the application wasn’t around when I took it over. Learning a brand new application is a daunting task – doubled if you have to learn it without any outside resources. As I tried to familiarize myself with the application, I picked up a few tricks to handling inherited code. These are a few of the things I learned along the way that will hopefully save you hours on your next undertaking.

1. Resist temptation

It can be really hard to ignore glaring implementation flaws when you look at code for the first time. Unfortunately, there are often reasons that the junky code exists in the first place. It might not be the best solution, but if it works, just leave it. Until you fully understand what each piece of code does, you might break something unintentionally by trying to fix it. Now you’ve lost time and still don’t understand the code any better. Unless it’s necessary for your current scope of work, make a tech debt note about why it’s bad code, and back away slowly.

2. Check the docs

I learn by immediately messing around in the code and doing as much damage as possible before rolling back. This isn’t really great, but hands-on learning is so natural for most technical things that we forget about reading. You. Yes, you. You are a developer so reading seems really unnecessary. Unfortunately, we still haven’t found a way to automate it, so you’ll have to do the reading yourself. Look at any documentation that might be around. It’ll suck. It won’t make a bit of sense. Then in 6 months, you’ll look at a weird method and remember that the docs said why it was like that. And you’ll think to yourself, “Ah, well, Old Fellow, looks like that reading did more than a mite bit of good!” and you’ll laugh and light up a cigar with your adoring fans swooning and giggling. Unless, you know, you’re a normal person and not a caricature of an old famous British man. Which brings me to 3…

3. Be a caricature of an old famous British man. No. Kidding. Don’t do this. (But if you do this, let’s be friends.)

3. Check the change log

It’s useful to see what might have been done recently, what kinds of issues have been encountered, and where those fixes lie. If there’s bug tracking, change logs, general progress docs, find them all. You don’t have to memorize every issue, but go over some known problems and keep yourself from making the same mistakes.

4. Be a user

It’s a lot easier to understand the code if you take some time to use the actual application. Use it like a user would, ask users (or consult the people who already did), find out why this is the way it is. There might be a good reason that all the buttons are yellow and labeled “Do not click.” It might help the users’ workflow. Make sure you know, because ultimately if the people who need this hate what you’ve done, someone else will be inheriting this code from you.

5. Just ask

When you inherit code blind, it’s awful. I mean, of course, the last ten people to look at this have all left the company/country/planet, and you’re all alone and in the dark. However, it doesn’t hurt to ask around and find out if anyone else knows what’s up. Alternately, if you have someone who knows the code, don’t get stuck in “Am I seriously doing this wrong?” hell, and just ask the question. 9 times out of 10, the code is confusing because it’s confusing code.

6. Look before you implement

It’s great when you can finally work on the code you’ve inherited. Continue reading 10 Tips for Handling Inherited Code →