C#基于NPOI操作Excel

目录

• 常见问题解决
• NPOI 导出添加批注功能
• POI导出Excel时下拉列表值超过255的问题
• 日期格式导入混乱
• 注意

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);

ISheet hidden = workbook.CreateSheet(columnName);
IRow row = null;
ICell cell = null;
for (int i = 0; i 

HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
// 这个代码参数不要写成固定的，它用来定位你的批注的位置和大小。
HSSFComment comment = 
    (HSSFComment)patriarch.CreateCellComment(new HSSFFClientAnchor(0, 0, 255,255, col1, 0, col1 + 2, 5));
comment.Author = "Dison";
comment.String = new HSSFRichTextString($"内容");
cell.CellComment = comment;

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

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

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;
    }
}

//添加批注
HSSFPatriarch patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFComment comment12 = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 2, 3));//批注显示定位
comment12.String = new HSSFRichTextString("请填写完整部门名称！");
HSSFCell cell12 = (HSSFCell)headerRow.CreateCell(12);//将批注给予单元格
cell12.CellComment = comment12;

HSSFComment comment1 = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(255, 125, 1023, 150, colindex + 1, rowIndex - 1,  colindex + 2, rowIndex + 4))；

//创建Excel工作薄对象

Workbook workbook = new HSSFWorkbook();

//生成一个表格 设置：页签

Sheet sheet = workbook.createSheet("sheet1");

 

//去数据库中查询我们想要的数据

List productList = Ebean.getServer(GlobalDBControl.getDB()) .createQuery(Product.class, "find product where 1 = 1 and status = 0 and producttype is not null ") .findList();

//创建一个数组 用来存放 我们取出来的数据

String[] productNameArray = new String[productList.size()];

//遍历每个peoduct对象，来获取productName属性并添加到数组中

for (int i = 0; i 

/// 

        /// 获取单元格类型
        /// 
        /// 
        /// 
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    short format = cell.CellStyle.DataFormat;
                    if (format != 0) { return cell.DateCellValue; } else { return cell.NumericCellValue; }
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                default:
                    return "=" + cell.CellFormula;
            }
        }