NPOI 数据格式

问题描述

我正在使用 NPOI v1.2.3 导出网格，但无法使单元格格式正常工作.

I'm exporting a grid with NPOI v1.2.3, and am having trouble getting cell formatting to work.

我有一个将对象列表导出到 XLS 文件的类.为每个对象创建一行，并为每个配置的属性添加一个单元格.可以在每个属性级别设置单元格数据格式.

I have a class that exports a list of objects to an XLS file. A row is created for each object, and a cell is added for each configured property. The cell data format can be set on a per-property level.

我读过 你不应该为每个单元格创建一个新的样式.我无法对我的样式进行硬编码，因为我的导出器需要支持任何类.相反，我编写了一个小缓存系统，如果尚未为当前单元格的格式创建一个新的 CellStyle，它只会创建一个新的 CellStyle.

I've read that you shouldn't create a new style for each cell. I can't hard-code my styles, since my exporter needs to support any class. Instead, I wrote a little cache system that only creates a new CellStyle if one hasn't already been created for the current cell's format.

不幸的是，这仍然没有解决问题.最终的 XLS 文件中未正确应用格式.在我的测试案例中，XLS 中的大多数单元格都使用日期"格式，即使只有几列是日期.但是，第一列正确使用了自定义格式.没有单元格被设置为文本，即使大部分应该是这样.

Unfortunately, this still hasn't solved the problem. The formatting is not correctly applied in the final XLS file. In my test case, most cells in the XLS are using the "Date" format, even though only a few columns are dates. The first column correctly uses a custom format, however. No cells are set to text, even though that should be most of them.

我做错了什么?

代码

下面的AddRecords"方法用于添加数据行(页眉和页脚行分别添加).最后一段代码是延迟加载 CellStyles 的方法.

The "AddRecords" method below is used to add the data rows (header and footer rows are added separately). The last bit of code is the method that lazy-loads CellStyles.

private void AddRecords( Sheet sheet, IList<T> records )
{
    foreach( var record in records )
    {
        // append row
        var row = sheet.CreateRow ( sheet.LastRowNum + 1 );

        // iterate through all configured columns
        foreach ( var column in GetColumns() )
        {
            // append cell
            Cell cell = row.CreateCell ( row.LastCellNum == -1 ? 0 : row.LastCellNum );

            // get the property value of the column from the record
            object value = GetCellValue ( column, record );

            // extension method that takes an object value and calls the appropriate type-specific SetCellValue overload
            cell.SetCellValue ( value );

            // get format from the column definition ("m/d", "##.###", etc.), or use the default
            string dataFormat = column.DataFormat ?? GetDefaultDataFormat ( value );

            // find/create cell style
            cell.CellStyle = GetCellStyleForFormat( sheet.Workbook, dataFormat );
        }
    }
}

/// <summary>
/// Returns a default format string based on the object type of value.
///
/// http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
private string GetDefaultDataFormat( object value )
{
    if( value == null )
    {
        return "General";
    }

    if( value is DateTime )
    {
        return "m/d";
    }

    if( value is bool )
    {
        return "[=0]\"Yes\";[=1]\"No\"";
    }

    if( value is byte || value is ushort || value is short ||
         value is uint || value is int || value is ulong || value is long )
    {
        return "0";
    }

    if( value is float || value is double )
    {
        return "0.00";
    }

    // strings and anything else should be text
    return "text";
}

private readonly Dictionary<string, CellStyle> _cellStyleCache = new Dictionary < string, CellStyle > ();

private CellStyle GetCellStyleForFormat( Workbook workbook, string dataFormat )
{
    if( !_cellStyleCache.ContainsKey ( dataFormat ) )
    {
        var newDataFormat = workbook.CreateDataFormat ();
        var style = workbook.CreateCellStyle ();
        style.DataFormat = newDataFormat.GetFormat ( dataFormat );

        _cellStyleCache[dataFormat] = style;
    }

    return _cellStyleCache[dataFormat];
}

推荐答案

问题似乎与创建与内置格式匹配的新格式有关.如果可用，我将我的延迟加载方法更改为使用内置格式，并且我最终 XLS 中的单元格格式现在都正确了.

It looks like the problem has something to do with creating new formats that match built-in formats. I changed my lazy-loading method to use the built-in format if available, and the cell formats in my final XLS are all correct now.

if( !_cellStyleCache.ContainsKey ( dataFormat ) )
{
    var style = workbook.CreateCellStyle ();

    // check if this is a built-in format
    var builtinFormatId = HSSFDataFormat.GetBuiltinFormat ( dataFormat );

    if( builtinFormatId != - 1)
    {
        style.DataFormat = builtinFormatId;
    }
    else
    {
        // not a built-in format, so create a new one
        var newDataFormat = workbook.CreateDataFormat ();
        style.DataFormat = newDataFormat.GetFormat ( dataFormat );
    }

    _cellStyleCache[dataFormat] = style;
}

这篇关于NPOI 数据格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！