A data import requirement was recently encountered in C# of the.net framework 4.7. However, this time around the NPOI experience, the principle is forward compatibility. So use.XLS support. From the Internet, I put together a little bit.

1. The cell drop – down box

In development we come across drop-down boxes for cells. It can generally be written as follows:

var cellRanges = new CellRangeAddressList(firstRow, lastRow, firstCol, latsCol);

DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(stringArray);

HSSFDataValidation validate = new HSSFDataValidation(cellRanges, constraint);

validate.ShowProptBox = true;

sheet.AddValidationData(validate);

However, if there is a length limit for the String array, as in the case of the drop-down list value of NPOI exported to Excel that exceeds 255, String literals in formulas can’t be bigger than 255 characters ASCII.

The solution

Save the drop-down content by creating an extra Excel Sheet and convert it to the drop-down data.

ISheet hidden = workbook.CreateSheet(columnName); IRow row = null; ICell cell = null; for (int i = 0; i < stringArray.Length; i++) { row = hidden.CreateRow(i); cell = row.CreateCell(0); cell.SetCellValue(stringArray[i]); } IName namedCell = workbook.CreateName(); namedCell.NameName = column.ColumnName; // Note that the following syntax is the Excel formula, it is recommended not to leave out a '$', many documents will be missing. namedCell.RefersToFormula = $"{columnName}! $A$1:$A${stringArray.Length}"; DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(columnName); CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); HSSFDataValidation validate = new HSSFDataValidation(addressList, constraint); sheet.AddValidationData(dataValidate);Copy the code

2 Add comments

The code is as follows:

HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); // This code argument is not written as fixed, it is used to locate the position and size of your comment. HSSFComment comment = (HSSFComment) patriarch. CreateCellComment (new HSSFFClientAnchor (0, 0, 255255, col1, 0, col1 + 2, 5)); comment.Author = "Dison"; Comment. String = new HSSFRichTextString($" content "); cell.CellComment = comment;Copy the code

3 Reading Data

How do I parse the results of the formula

The code is as follows:

if (row.GetCell(i).CellType.Equals(CellType.Formula))
{
    var data = row.GetCell(i).RichStringCellValue;
}
Copy the code

If you want to read the formula, you can also read it as follows:

var data = row.GetCell(i).ToString();

But notice that the result doesn’t have an equal sign “=”, so I’m demonstrating here, so I’m writing local variables.

Date format FROM MM-DD-YY to YYYY-MM-DD

Since both the numbers and dates in Excel are in Numeric format, the treatment is as follows:

if (row.GetCell(i).CellType.Equals(CellType.Numeric))
{
    ICell cell = row.GetCell(i);
    short format = cell.CellStyle.DataFormat;
    if (format != 0)
    {
        var data = cell.DateCellValue.ToString("yyyy-MM-dd");
    }
    else 
    {
        var data = cell.NumericCellValue;
    }
}
Copy the code

conclusion

NPOI is also a relatively mature Excel operation library. The online material is indeed rather sloppy. But as a programmer, you must learn patience, especially debug.

Reference documentation

  • String literals in formulas can’t be bigger than 255 characters ASCII
  • Add annotation function to NPOI export
  • C# NPOI date format