Geek Answers Handbook

CellStyle applies unexpectedly to all cells in a worksheet - NPOI?

I don’t understand why this can happen, first I tried to apply bold text to the column headings in the first row, then I want to set the borders of my MEDIUM header cells, but this MEDIUM border style applies to all cells in the sheet. There are more problems in the same code:

  • The text in the column headings (in the first row) is not bold as I want.
  • The color of the text in the column headings is not red as I want.

Here is my code (processing with NPOI library):

private void CreateATest(string filename) { FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write); HSSFWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet("NPOI"); IRow row = sheet.CreateRow(0); row.RowStyle = wb.CreateCellStyle(); row.RowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; row.RowStyle.VerticalAlignment = VerticalAlignment.CENTER; row.RowStyle.WrapText = true; IFont font = wb.CreateFont(); font.Boldweight = 3; font.Color = (short) ColorTranslator.ToWin32(Color.Red); font.FontHeight = 30; row.RowStyle.SetFont(font); int i = 0; foreach (string header in new string[] { "ID", "Name", "Age" }) { row.CreateCell(i++).SetCellValue(header); row.Cells[i - 1].CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.MEDIUM; row.Cells[i - 1].CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.MEDIUM; row.Cells[i - 1].CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.MEDIUM; } row.Cells[i - 1].CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.MEDIUM; Random rand = new Random(); for (i = 1; i < 1000; i++) { IRow row1 = sheet.CreateRow(i); for (int j = 0; j < 3; j++) { row1.CreateCell(j).SetCellValue(rand.Next(100)); } } wb.Write(fs); fs.Close(); }

Please fix this for me, I am very new to NPOI, just tried using it. Your help would be greatly appreciated. Thank you (<--- I don’t know why this "Thank you" cannot go to the next line, even if I typed Enter before entering it)

+4
source share
2 answers

The formatting issue is related to how Excel formatted formatted strings. They take style information from the line above. You can verify this by formatting the line in bold and then inserting the line right away - the new line is also shown in bold. You can try to insert the rest of the lines first and then format the title bar. Unfortunately, I do not have enough reputation to make this a comment rather than an answer, because I cannot help you with two other problems.

+6
source

Here is the solution - I had the same problem. You need to create a discrete ICellStyle and assign it to the CellStyle cell instead of just calling "SetFont ()" on the current CellStyle.

I had the same problem when the style was applied to all cells. I commented on the line that called it, the next line is the working one (and part of ICellStyle):

  let workbook:HSSFWorkbook = new HSSFWorkbook() let worksheet:ISheet = workbook.CreateSheet(sheetName) let fontbold = workbook.CreateFont() fontbold.Boldweight <- (int16 FontBoldWeight.Bold) fontbold.FontHeightInPoints <- 10s fontbold.FontName <- "Arial" let cellstylebold:ICellStyle = workbook.CreateCellStyle() cellstylebold.SetFont(fontbold) let row:IRow = worksheet.CreateRow(0) for h in 0..headers.Length-1 do let cell = row.CreateCell(h) cell.SetCellValue(headers.[h]) //cell.CellStyle.SetFont(fontbold) cell.CellStyle <- cellstylebold
+1
source

More articles:


All Articles