NPOI WrapText isn't working?

Question

I am trying to create a column with WrapText set to true for an XLSX file using NPOI.

The following does not seem to work:

            var workbook = new XSSFWorkbook();
            var headerRow = sheet.CreateRow(0);
            var cellType = CellType.String;
            var colStyle = sheet.GetColumnStyle(3);
            colStyle.WrapText = true;

            sheet.SetDefaultColumnStyle(3, colStyle);

            headerRow.CreateCell(0, cellType).SetCellValue("Name");
            headerRow.CreateCell(3, cellType).SetCellValue("Comments");

            var font = workbook.CreateFont();
            font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
            style.SetFont(font);
            style.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
            style.FillForegroundColor = IndexedColors.Grey25Percent.Index;
            style.IsLocked = false;
            style.WrapText = true;

            for (int i = 0; i < 6; i++)
            {
                var cell = headerRow.Cells[i];
                cell.CellStyle = style;
            }
            headerRow.RowStyle = style;


            sheet.SetColumnWidth(0, 30 * 256);
            sheet.SetColumnWidth(1, 20 * 256);
            sheet.SetColumnWidth(2, 20 * 256);
            sheet.SetColumnWidth(3, 50 * 256);
            sheet.SetColumnWidth(4, 30 * 256);
            sheet.SetColumnWidth(5, 50 * 256);

            int rowNumber = 1;
            style = (XSSFCellStyle)workbook.CreateCellStyle();
            style.IsLocked = false;
            style.WrapText = true;
            foreach (MemberListViewModel member in members)
            {
                var row = sheet.CreateRow(rowNumber++);

                row.CreateCell(0).SetCellValue(member.FullName);
                row.CreateCell(3).SetCellValue(member.endowed_professorship);
            }

            workbook.Write(output);

Naturally, the documentation is a little light for this one.

Any suggestions on changing the code? Otherwise, it seems to work fine.

I just can't get the wraptext to work.

EDIT:

After posting the question, I revised my code to use Reflection to detach the code from the individual classes (there are several I work with). This uses a modified Kendo interface that has the Excel Export button on the grid.

The revised code includes modifications included in the answer:

                rowNumber = 1;
                var cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
                cellStyle.WrapText = true;
                foreach (var member in members)
                {
                    String value = "";
                    var aType = member.GetType();
                    var real = aType.GetProperty(headings[i]);
                    if (real != null)
                    {
                        value = (real.GetValue(member) != null) ? String.Format(format, real.GetValue(member)) : "";
                    }
                    var row = sheet.GetRow(rowNumber++);
                    cell = row.CreateCell(i);
                    cell.SetCellValue(new XSSFRichTextString(value));
                    cell.CellStyle = cellStyle;
                }

This code now produces the desired result. I also added the XSSRichTextString to the formatting just for good measure.

Boopathi.Indotnet · Accepted Answer · 2016-03-02T06:50:04.267

6

"CellStyle.WrapText" set After Binding CellStyle ,its working For Me.

Solution:

 ICellStyle CellCentertTopAlignment = workbook.CreateCellStyle();
 CellCentertTopAlignment = workbook.CreateCellStyle();
 CellCentertTopAlignment.SetFont(fontArial16);
 CellCentertTopAlignment.Alignment = HorizontalAlignment.Center;
 CellCentertTopAlignment.VerticalAlignment = VerticalAlignment.Top;

 ICell Row1 = Row1.CreateCell(1); 
 Row1.SetCellValue(new HSSFRichTextString("I find a solution for this Problem.."));
 Row1.CellStyle = CellCentertTopAlignment; 
 Row1.CellStyle.WrapText = true;

edited Mar 02 '16 at 06:50

answered Mar 02 '16 at 06:23

Boopathi.Indotnet

1,280
15
18

I marked this one as the solution although both have the correct answer (they don't allow us to mark a second one). The solution was that the cell needed to be re-styled after the value was put into it. I was at one point styling cells individually but before the cells had a value placed into them. – Timothy Dooling Mar 02 '16 at 21:56

score 2 · Answer 2 · answered Feb 29 '16 at 18:25

I think you forgot to change the cell's style. You're setting it only on the header.

This:

row.CreateCell(0).SetCellValue(member.FullName);
row.CreateCell(3).SetCellValue(member.endowed_professorship);

Should be something like:

var cell0 = row.CreateCell(0);
cell0.SetCellValue(member.FullName);
cell0.Style = style;

var cell3 = row.CreateCell(3);
cell3.SetCellValue(member.endowed_professorship);
cell3.Style = style;

There's probably a better way of doing this; I'm no NPOI specialist. =)

NPOI WrapText isn't working?

2 Answers2