NPOI XSSF INDIRECT

          

      private string GetExcelColumnName(int columnNumber)
        {

            int dividend = columnNumber;

            string columnName = String.Empty;

            int modulo;
            if (dividend == 0)
                columnName = Convert.ToChar(65).ToString() + columnName;

            while (dividend >0)
            {

                modulo = (dividend) % 26;//求余

                //ASCCI A 为65 
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;

                dividend = (int)((dividend - modulo) / 26);//商

            }
            return columnName;

        }

          //定义Cell范围，参数1：起始行数，参数2：结束行数，参数3：起始列数，参数4：结束列数
                                        
           XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)currSheet);

          //这里的范围是单个单元格，因为我们的公式用到了具体的单元格地址
           CellRangeAddressList regions = new CellRangeAddressList(rowIndex,rowIndex,intColumnContent, intColumnContent);
           var colName = GetExcelColumnName(intColumnContent-1);
            //使用INDIRECT函数，这里指定了具体地址

             //设置验证方式
           XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.
           CreateFormulaListConstraint(string.Format("INDIRECT({0}{1})", colName, rowIndex + 1));

           //CreateFormulaListConstraint(string.Format("INDIRECT(${0}${1})", colName, rowIndex + 1));

           XSSFDataValidation dataValidate = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, regions);

           currSheet.AddValidationData(dataValidate);