This project has moved and is read-only. For the latest updates, please go here.

Automatically choose a NumberFormat based on datatype of the column in LoadFromDataTable procedure.

Jan 4, 2014 at 11:20 PM
Hello everybody!

I would like to share the code I made to automatically select a mask of number format based on the datatype of the column.

Changed code that follows:
#region LoadFromDataTable
        /// <summary>
        /// Load the data from the datatable starting from the top left cell of the range
        /// </summary>
        /// <param name="Table">The datatable to load</param>
        /// <param name="PrintHeaders">Print the column caption property (if set) or the columnname property if not, on first row</param>
        /// <param name="TableStyle">The table style to apply to the data</param>
        /// <param name="AutoNumberFormat">Inserts the value as the field type of column</param>
        /// <returns>The filled range</returns>
        public ExcelRangeBase LoadFromDataTable(DataTable Table, bool PrintHeaders, TableStyles TableStyle, bool AutoNumberFormat = false)
        {
            var r = LoadFromDataTable(Table, PrintHeaders, AutoNumberFormat);

            int rows = Table.Rows.Count + (PrintHeaders ? 1 : 0) - 1;
            if (rows >= 0 && Table.Columns.Count > 0)
            {
                var tbl = _worksheet.Tables.Add(new ExcelAddressBase(_fromRow, _fromCol, _fromRow + (rows == 0 ? 1 : rows), _fromCol + Table.Columns.Count - 1), Table.TableName);
                tbl.ShowHeader = PrintHeaders;
                tbl.TableStyle = TableStyle;
            }
            return r;
        }
        /// <summary>
        /// Load the data from the datatable starting from the top left cell of the range
        /// </summary>
        /// <param name="Table">The datatable to load</param>
        /// <param name="PrintHeaders">Print the caption property (if set) or the columnname property if not, on first row</param>
        /// <param name="AutoNumberFormat">Inserts the value as the field type of column</param>
        /// <returns>The filled range</returns>
        public ExcelRangeBase LoadFromDataTable(DataTable Table, bool PrintHeaders, bool AutoNumberFormat = false)
        {
            if (Table == null)
            {
                throw (new ArgumentNullException("Table can't be null"));
            }

            int col = _fromCol, row = _fromRow;
            if (PrintHeaders)
            {
                foreach (DataColumn dc in Table.Columns)
                {
                    if (AutoNumberFormat)
                    {
                        _worksheet.Column(col).Style.Numberformat.Format = GetAutoNumberFormat(dc.DataType);
                    }
                    
                    // If no caption is set, the ColumnName property is called implicitly.
                    _worksheet.Cell(row, col++).Value = dc.Caption;
                }

                row++;
                col = _fromCol;
            }
            foreach (DataRow dr in Table.Rows)
            {
                foreach (object value in dr.ItemArray)
                {
                    _worksheet.Cell(row, col++).Value = value;
                }
                row++;
                col = _fromCol;
            }
            return _worksheet.Cells[_fromRow, _fromCol, row - 1, _fromCol + Table.Columns.Count - 1];
        }

        private string GetAutoNumberFormat(Type type)
        {
            string format = "General";
            if (type == typeof(int))
                format = "0";
            else if (type == typeof(uint))
                format = "0";
            else if (type == typeof(long))
                format = "0";
            else if (type == typeof(ulong))
                format = "0";
            else if (type == typeof(short))
                format = "0";
            else if (type == typeof(ushort))
                format = "0";
            else if (type == typeof(double))
                format = "0.00";
            else if (type == typeof(float))
                format = "0.00";
            else if (type == typeof(decimal))
                format = NumberFormatInfo.CurrentInfo.CurrencySymbol + " #,##0.00";
            else if (type == typeof(DateTime))
                format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern + " " + DateTimeFormatInfo.CurrentInfo.LongTimePattern;
            else if (type == typeof(string))
                format = "@";
            else if (type == typeof(bool))
                format = "\"" + bool.TrueString + "\";\"" + bool.TrueString + "\";\"" + bool.FalseString + "\"";

            return format;

        }
        #endregion