Create a text area for a spreadsheet using NPOI

I've been doing a lot of work lately for a client that requires the exporting of large amounts of data into Excel spreadsheet format. After a fair bit of research into the more flexible .NET-based packages available, I decided on NPOI.

Most of the questions on NPOI are answerable from research, but here's one I found tricky to find. Creating a proper text box on a spreadsheet that you can add formatting to, and include massive amounts of text in.

To create a textbox, do the following.

Create a patriarch object

This is your base to create any drawing object. I created mine as a member at the top of the class so that I can re-use it class-wide as I please.

Create an anchor point

This is the area that your textbox will sit on. You need to change startingRowNum and endRowNum to be the start and end rows (height) of your textbox. The width is determined by startingColNum and endColNum.

Create the textbox, and put something in it.

You have to create a rich text string to put in there, but on the plus side, that gives you the opportunity to play with formatting and so forth.

I found this particular bit of functionality to be quite well hidden in the documentation, so if you take this any further or do anything interesting, please feel free to comment and let me know!

Creating Excel files using NPOI - Getting a cell's co-ordinates by ICell

The greatest thing about exporting data to Excel is the ability to create formulae that will do a lot of the user's hard work for them later on.

I've been working with the .NET port of Java's POI called NPOI to allow me to create a quite complicated spreadsheet from scratch, and I've noticed that there are quite a few holes in the library that (unless I'm mistaken and the functionality exists) need to be filled.

My next few blog posts will probably be spent documenting some of this functionality, and I'm going to start with getting a cell reference by the cell object itself. For the purposes of this blog post, I'm going to refer to the co-ordinates of a cell "A1, B2, C3" as a cell reference.

I couldn't find a way in NPOI to allow me to get a cell reference from a cell. I found that I had to piece this information together myself, and I used the CellReference class to do it. Once I had the elements, I just joined them together.

This function is particularly useful to me, because while I'm iterating through a loop for a collection of data, I can grab the cell reference as soon as I create a cell, and keep a note of it to create subtotals and totals farther down the line. You may want to wrap some error trapping around this code and catch the right exceptions.