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.
"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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With