I am exporting a mesh with NPOI v1.2.3 and I am having problems formatting the cell.
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 at the level of each level.
I read that you should not create a new style for each cell . I cannot hard code my styles, as my exporter must support any class. Instead, I wrote a small cache system that creates a new CellStyle if it has not already been created for the current cell format.
Unfortunately, this has not yet solved the problem. Formatting is incorrectly applied in the latest XLS file. In my test case, most cells in XLS use the Date format, although only a few columns are dates. However, the first column uses custom format correctly. No cells are configured for text, although this should be most of them.
What am I doing wrong?
code
The following is the AddRecords method for adding data rows (header and footer rows are added separately). The last bit of code is the method that lazy-load CellStyles is.
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]; }
source share