Merging Excel cells with NPOI

I often get messages like this:

“Scott, you are the best, most awesome guy in the whole world. Every day, I think of only you and your cool coding site, and eagerly await each and every post. How did you get so awesome?”

So of course, I wanted to put this into an Excel sheet. Usually, I use Microsoft Excel Object Library for my Excel needs, but in this case, I’m using NPOI because the client wants to use it to say how great I am. (This happens a lot.)

HSSFWorkbook hssfworkbook = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet WS = hssfworkbook.CreateSheet("Sheet1");
Row row1 = WS.CreateRow(0);
Cell r1c1 = row1.CreateCell(0);
r1c1.SetCellValue("Scott, you are the best, most awesome guy in the whole world. Every day, I think of only you and your cool coding site, and eagerly await each and every post. How did you get so awesome?");
r1c1.CellStyle = red10;
r1c1.CellStyle.WrapText = true;
r1c1.Row.Height = 800;
NPOI.SS.Util.CellRangeAddress cra = new NPOI.SS.Util.CellRangeAddress(0,0,0,5);
WS.AddMergedRegion(cra);

OK, a little explanation of what just happened.

The first four rows created the sheet, row, and cell to hold my ego-boosting string. The next four rows set the value and style the cell. An item of interest is row 8, which reads:

r1c1.Row.Height = 800;

It doesn’t seem that interesting, but when a cell is merged using the NPOI code, it doesn’t automatically resize the row to show the string, so that has to be done manually. Otherwise, some of the cell text could be hidden.

The last two lines create a cell range. The first of these holds the CellRangeAddress function, which takes four parameters. In order, they are the row to start the range, the row to end the range, the column to start the range, and the column to end the range. This sets the area of cells that you want to merge into one cell. And then the last line  adds the range to the worksheet.

So there we have it, merging cells with NPOI.

Advertisements
  1. #1 by vikram kumar on June 23, 2011 - 11:26 am

    Thanks a lot scott. It worked!! cheers…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: