运维开发网

C# 基于NPOI操作Excel

运维开发网 https://www.qedev.com 2021-06-17 11:25 出处:网络 作者: DisonTangor
目录常见问题解决NPOI 导出添加批注功能POI导出Excel时下拉列表值超过255的问题日期格式导入混乱注意1 单元格下拉框
目录
  • 常见问题解决
    • NPOI 导出添加批注功能
    • POI导出Excel时下拉列表值超过255的问题
    • 日期格式导入混乱
    • 注意

1 单元格下拉框

在开发中我们会遇到为单元格设置下拉框。一般可以编写如下:

var cellRanges = new CellRangeAddressList(firstRow, lastRow, firstCol, latsCol);
DVConstrwww.cppcns.comaint constraint = DVConstraint.CreateExplicitListConstraint(stringArray);
HSSFDataValidation validate = new HSSFDataValidation(cellRanges, constraint);
validate.ShowProptBox = true;
sheet.AddValidationData(validate);

但是,如果字符串数组存在长度限制,如NPOI导出Excel时下拉列表值超过255的问题(String literals in formulas can't be bigger than 255 characters ASCII)

解决方案

通过额外新建Excel的Sheet页保存下拉内容,并转换为下拉框数据。

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;
// 注意下面的语法是Excel的公式,建议不要落掉一个`$`,很多文档都要所遗漏。
namedCell.RefersToFormula = $"{columnName}!$A$1:$A${stringArray.Length}";
DVConstraint constraint =  DVConstraint.CreateFormulaListConstraint(columnName);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, latsCol);
HSSFDataValidation validate = new HSSFDataValidation(addressList, constraint);
sheet.AddValidationData(dataValidate);

2 添加批注

代码如下:

HSSFPatriarch patriarch = (HSSFPatriarch)sheet.Cr编程客栈eateDrawingPatriarch();
// 这个代码参数不要写成固定的,它用来定位你的批注的位置和大小。
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;

3 读取数据

如何解析公式的结果

代码如下:

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

如果希望读取公式也可以如下:

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

但是需要注意结果没有等号“=”, 这里我是演示,所以写了局部变量。

日期格式 MM-dd-yy 转 yyyy-MM-dd

由于Excel的数字和日期都是Numeric格式,;处理如下:

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

结语

NPOI还是一个相对成熟的Excel操作库。网上的资料确实写的比较潦草。但是作为程序员,必须学会耐心,尤其是debug。

常见问题解决

NPOI 导出添加批注功能

//添加批注
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;

但是有个比较重要的地方需要澄清下,就是批注的位置和大小,这是由HSSFClientAnchor八个参数控制的,千万不能简单的写HSSFClientAnchor(0, 0, 0, 0, 1, 2, 2, 3),

因为每个单元格的批注的位置都是不一样的(编辑批注时的位置)。那么怎么办呢,当然是需要了解参数的意思:

简单说来:

关于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数,有必要在这里说明一下:

  • dx1:起始单元格的x偏移量;
  • dy1:起始单元格的y偏移量;
  • dx2:终止单元格的x偏移量;
  • dy2:终止单元格的y偏移量;
  • col1:起始单元格列序号;
  • row1:起始单元格行序号;
  • col2:终止单元格列序号;
  • row2:终止单元格行序号;

其实主要是前四个是偏移量,后四个关系到批注的位置和大小。

以我自己做的一个例子来说:

HSSFComment comment1 = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(255, 125, 1023, 150, colindex + 1, rowIndex - 1,  colindex + 2, rowIndex + 4));
  • rowIndex 是当前单元格是第几行,colindex 是当前单元格是第几列。通过行列是可以定位到当前的单元格的。
  • colindex + 1 对应上面的参数是col1  表示批注起始的位置是当前单元格的列数的下一列,即原来是第5列,则批注起在第6列。
  • rowIndex - 1 对应上面的参数是row1  表示皮质起始的位置是当前单元格行数的上一行,即原来是第2行,则批注起在第1行。
  • colindex + 2, rowIndex + 4  这两个参数则是单元格终止的位置   +2  +4  则是决定了批注的大小,道理同colindex + 1,rowIndex - 1 。

但是NPOI导出有个坑   就是批注大小会随着所在位置的单元格大小变动  这个影响不大  如果想解决这个问题   只能换导出方法了。。。

千万别按照网上人家写的(0, 0, 0, 0, 1, 2, 2, 3),这会坑死的,批注位置一直不变  任何单元格的批注都在同一个位置,坑死。

POI导出Excel时下拉列表值超过255的问题

//创建Excel工作薄对象

Workbook workbook = new HSSFWorkbook();

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

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

 

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

List<Product> 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 < productList.size(); i++)

{ Product product = productList.get(i);

productNameArray[i] = product.getTitle(); }

//将下拉框数据http://www.cppcns.com放到新的sheet里,然后excle通过新的sheet数据加载下拉框数据

Sheet hidden = workbook.createSheet("hidden");

//创建单元格对象 Cell cell = null;

//遍历我们上面的数组,将数据取出来放到新sheet的单元格中

for (int i = 0, length = productNameArray.length; i < length; i++)

{ //取出数组中的每个元素

String name = productNameArray[i];

//根据i创建相应的行对象(说明我们将会把每个元素单独放一行)

Row row = hidden.createRow(i);

//创建每一行中的第一个单元格

cell = row.createCell(0);

//然后将数组中的元素赋值给这个单元格

cell.setCellValue(name); }

 

// 创建名称,可被其他单元格引用

Name namedCell = workbook.createName(); namedCell.setNameName("hidden");

// 设置名称引用的公式

namedCell.setRefersToFormula("hidden!$A$1:$A$" + productNameArray.length);

//加载数据,将名称为hidden的sheet中的数据转换为List形式

DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");

// 设置第一列的3-65534行为下拉列表

// (3, 65534, 0, 0) ====> (起始行,结束行,起始列,结束列)

CellRangeAddressList regions = new CellRangeAddressList(3, 65534, 0, 0);

// 将设置下拉选的位置和数据的对应关系 绑定到一起

DataValidation dataValidation = new HSSFDataValidation(regions, constraint);

//将第二个sheet设置为隐藏 workbook.setSheetHidden(1, true);

//将数据赋给下拉列表 sheet.addValidationData(dataValidation);

//最后将文件导出就可以了,后面的代码就不写了,我只写一些这个问题相关的代码

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

   如果出现多列情况,可复用下面方法

private void ExcelTo255(Workbook workbook,String sheetName,int sheetNameIndex,String[] sheetData,int firstRow,int lastRow,int firstCol,int lastCol){
    //将下拉框数据放到新的sheet里,然后excle通过新的sheet数据加载下拉框数据
    Sheet hidden = workbook.createSheet(sheetName);

    //创建单元格对象
    Cell cell =null;
    //遍历我们上面的数组,将数据取出来放到新sheet的单元格中
    for (int i = 0, length = sheetData.length; i < length; i++){
        //取出数组中的每个元素
        String name = sheetData[i];
        //根据i创建相应的行对象(说明我们将会把每个元素单独放一行)
        Row row = hidden.createRow(i);
        //创建每一行中的第一个单元格
        cell = row.createCell(0);
        //然后将数组中的元素赋值给这个单元格
        cell.setCellValue(name);
    }
    // 创建名称,可被其他单元格引用
    Name namedCell = workbook.createName();
    namedCell.setNameName(sheetName);
    // 设置名称引用的公式
    namedCell.setRefersToFormula(sheetName+"!$A$1:$A$" + sheetData.length);
    //加载数据,将名称为hidden的sheet中的数据转换为List形式
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(sheetName);

    // 设置第一列的3-65534行为下拉列表
    // (3, 65534, 2, 2) ====> (起始行,结束行,起始列,结束列)
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    // 将设置下拉选的位置和数据的对应关系 绑定到一起
    DataValidation dataValidation = new HSSFDataValidation(regions, constraint);

    //将第二个sheet设置为隐藏
    workbook.setSheetHidden(sheetNameIndex, true);
    //将数据赋给下拉列表
    workbook.getSheetAt(0).addValidationData(dataValidation);
}

日期格式导入混乱

原因

大概是NPOI导入时会大概判断一下Excel文档里面的单元格是什么格式的内容,

有Blank,Boolean,Numeric,String,Error,Formula 等几种,

但是就是没有日期的,日期的单元格会被判断成Numeric(数字)类型,

所以日期格式的单元格就按数字类型来取其中的值,

所以单元格被判断成数字的之后还要再判断一下是否为日期格式。

/// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellTypwww.cppcns.come)
            {
                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;
            }
        }

注意

使用时Excel里的长数字类型,否则这类数据可能会被误判为日期类型

如:0000123,2017001等这类型的需要处理一下单元格格式->设置成"常规"类型

C# 基于NPOI操作Excel

以上就是C# 基于NPOI操作Excel的详细内容,更多关于C# NPOI操作Excel的资料请关注我们其它相关文章!

0

精彩评论

暂无评论...
验证码 换一张
取 消