Convert number to Excel column name

[nolme]

Hi,

I'm not sure this function is implemented in Spire.XLS but it's very usefull.

Source code is coming from StackOverFlow
http://stackoverflow.com/questions/837155/fastest-function-to-generate-excel-column-letters-in-c-sharp

Code: Select all

public static string ExcelColumnFromNumber(int column)
        {
            string columnString = "";
            decimal columnNumber = column;
            while (columnNumber > 0)
            {
                decimal currentLetterNumber = (columnNumber - 1) % 26;
                char currentLetter = (char)(currentLetterNumber + 65);
                columnString = currentLetter + columnString;
                columnNumber = (columnNumber - (currentLetterNumber + 1)) / 26;
            }
            return columnString;
        }

And the opposite :

Code: Select all

public static int NumberFromExcelColumn(string column)
        {
            int retVal = 0;
            string col = column.ToUpper();
            for (int iChar = col.Length - 1; iChar >= 0; iChar--)
            {
                char colPiece = col[iChar];
                int colNum = colPiece - 64;
                retVal = retVal + colNum * (int)Math.Pow(26, col.Length - (iChar + 1));
            }
            return retVal;
        }

Excel 2013 can manage 16384 columns but is there a constant somewhere to avoid hardcoded integer value ?

Excel 2007 limits: https://support.office.com/en-us/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa
Excel 2010 limits : https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3?ui=en-US&rs=en-NZ&ad=NZ&fromAR=1
Excel 2013 limits : https://support.office.com/en-us/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c00b05040f

[sweety1]

Hello,

Thanks for your inquiry.
So sorry that our product doesn't support this feature at this stage.

Best Regards,
Sweety

E-iceblue support team

[nolme]

here's the code :

Code: Select all

ExcelVersion version = ExcelVersion.Version2007;
for (int col = 0; col < ExcelRequirements.MaxColumns (version); col++)
{
  // Do something
}


Code: Select all

using Spire.Xls;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Spire.Utilities
{
    public sealed class ExcelUtility
    {
        private ExcelUtility() { }

        /// <summary>
        /// Duplicate Excel row style
        /// </summary>
        /// <param name="sheet">Excel document</param>
        /// <param name="sourceLine">Source line to copy</param>
        /// <param name="destinationLine">Destination line for copy</param>
        public static void DuplicateRowStyle(Worksheet sheet, int sourceLine, int destinationLine, ExcelVersion version)
        {
            int t = 0;
            for (int col = 0; col < ExcelRequirements.MaxColumns (version); col++)
            {
                char colName = (char)('A' + t);
                string sourceCellName = colName.ToString() + sourceLine.ToString();
                string destinationCellName = colName.ToString() + destinationLine.ToString();
                CellRange sourceCell = sheet.Range[sourceCellName];
                CellRange destinationCell = sheet.Range[destinationCellName];

                if (sourceCell.HasMerged == true)
                {
                    destinationCell.NumberFormat = sourceCell.NumberFormat;
                    destinationCell.HorizontalAlignment = sourceCell.HorizontalAlignment;
                    destinationCell.VerticalAlignment = sourceCell.VerticalAlignment;

                    while (sourceCell.HasMerged == true)
                    {
                        t++;
                        colName = (char)('A' + t);
                        sourceCellName = colName.ToString() + sourceLine.ToString();
                        sourceCell = sheet.Range[sourceCellName];
                    }
                }
                else
                {
                    destinationCell.Style = sourceCell.Style;
                    destinationCell.NumberFormat = sourceCell.NumberFormat;
                    destinationCell.HorizontalAlignment = sourceCell.HorizontalAlignment;
                    destinationCell.VerticalAlignment = sourceCell.VerticalAlignment;

                    t++;
                }
            }
        }

        /// <summary>
        /// Convert a column number to an Excel column name
        /// </summary>
        /// <param name="column">Column number [1..16384]</param>
        /// <returns>Excel column name</returns>
        public static string ColumnNameFromNumber(int column)
        {
            if (column <= 0) throw new ArgumentNullException("column");

            string columnString = String.Empty;
            decimal columnNumber = column;
            while (columnNumber > 0)
            {
                decimal currentLetterNumber = (columnNumber - 1) % 26;
                char currentLetter = (char)(currentLetterNumber + 65);
                columnString = currentLetter + columnString;
                columnNumber = (columnNumber - (currentLetterNumber + 1)) / 26;
            }
            return columnString;
        }

        /// <summary>
        /// Convert an Excel column name to number
        /// </summary>
        /// <param name="column">Excel column name</param>
        /// <returns>Column number</returns>
        public static int NumberFromColumnName(string column)
        {
            if (String.IsNullOrEmpty(column)) throw new ArgumentNullException("column");

            int retVal = 0;
            string col = column.ToUpper();
            for (int iChar = col.Length - 1; iChar >= 0; iChar--)
            {
                char colPiece = col[iChar];
                int colNum = colPiece - 64;
                retVal = retVal + colNum * (int)Math.Pow(26, col.Length - (iChar + 1));
            }
            return retVal;
        }
    }

    /// <summary>
    ///
    /// </summary>
    /// <url>https://support.office.com/en-us/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa</url>
    /// <url>https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3?ui=en-US&rs=en-NZ&ad=NZ&fromAR=1</url>
    /// <url>https://support.office.com/en-us/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c00b05040f</url>
    public sealed class ExcelRequirements
    {
        /// <summary>
        /// Get Worksheet max rows
        /// </summary>
        /// <param name="version">Document version</param>
        /// <returns>Number of rows</returns>
        public static int MaxRows(ExcelVersion version)
        {
            int result;

            switch (version)
            {
                default:
                case ExcelVersion.Version2007:
                case ExcelVersion.Version2010:
                case ExcelVersion.Version2013:
                    result = 1048576;
                break;
            }
            return result;
        }

        /// <summary>
        /// Get Worksheet max columns
        /// </summary>
        /// <param name="version">Document version</param>
        /// <returns>Number of columns</returns>
        public static int MaxColumns(ExcelVersion version)
        {
            int result;

            switch (version)
            {
                default:
                case ExcelVersion.Version2007:
                case ExcelVersion.Version2010:
                case ExcelVersion.Version2013:
                    result = 16384;
                    break;
            }
            return result;
        }

        /// <summary>
        /// Get column max characters
        /// </summary>
        /// <param name="version">Document version</param>
        /// <returns>Number of characters for columns</returns>
        public static int ColumnWidth(ExcelVersion version)
        {
            int result;

            switch (version)
            {
                default:
                case ExcelVersion.Version2007:
                case ExcelVersion.Version2010:
                case ExcelVersion.Version2013:
                    result = 255;
                    break;
            }
            return result;
        }

        /// <summary>
        /// Get number of points for row height
        /// </summary>
        /// <param name="version">Document version</param>
        /// <returns>Number of points</returns>
        public static int RowHeight(ExcelVersion version)
        {
            int result;

            switch (version)
            {
                default:
                case ExcelVersion.Version2007:
                case ExcelVersion.Version2010:
                case ExcelVersion.Version2013:
                    result = 409;
                    break;
            }
            return result;
        }

        /// <summary>
        /// Get number of page breaks
        /// </summary>
        /// <param name="version">Document version</param>
        /// <returns>Number of page breaks in horizontal and vertical</returns>
        public static int PageBreaks(ExcelVersion version)
        {
            int result;

            switch (version)
            {
                default:
                case ExcelVersion.Version2007:
                case ExcelVersion.Version2010:
                case ExcelVersion.Version2013:
                    result = 1026;
                    break;
            }
            return result;
        }

        /// <summary>
        /// Total number of characters that a cell can contain
        /// </summary>
        /// <param name="version">Document version</param>
        /// <returns>Number of characters</returns>
        public static int CellSize(ExcelVersion version)
        {
            int result;

            switch (version)
            {
                default:
                case ExcelVersion.Version2007:
                case ExcelVersion.Version2010:
                case ExcelVersion.Version2013:
                    result = 32767;
                    break;
            }
            return result;
        }

        /// <summary>
        /// Characters in a header or footer
        /// </summary>
        /// <param name="version">Document version</param>
        /// <returns>Number of characters</returns>
        public static int HeaderFooterSize(ExcelVersion version)
        {
            int result;

            switch (version)
            {
                default:
                case ExcelVersion.Version2007:
                case ExcelVersion.Version2010:
                case ExcelVersion.Version2013:
                    result = 255;
                    break;
            }
            return result;
        }

        /// <summary>
        /// Unique cell formats or styles
        /// </summary>
        /// <param name="version">Document version</param>
        /// <returns>Number of formats or styles</returns>
        public static int UniqueCellFormats(ExcelVersion version)
        {
            int result;

            switch (version)
            {
                default:
                case ExcelVersion.Version2007:
                case ExcelVersion.Version2010:
                case ExcelVersion.Version2013:
                    result = 64000;
                    break;
            }
            return result;
        }

        /// <summary>
        /// Hyperlinks in a worksheet
        /// </summary>
        /// <param name="version">Document version</param>
        /// <returns>Number of hyperlinks</returns>
        public static int HyperLinks(ExcelVersion version)
        {
            int result;

            switch (version)
            {
                default:
                case ExcelVersion.Version2007:
                case ExcelVersion.Version2010:
                case ExcelVersion.Version2013:
                    result = 66530;
                    break;
            }
            return result;
        }
    }
}


[sweety1]

Hello,

Thanks for your reply. Has your issue been resolved?

Best Regards,
Sweety
E-iceblue support team

[nolme]

yes.

I've justed shared the code. I think it can be useful for many users.

[sweety1]

Hello,

Thanks for your shareing.
Please feel free to contact us, if you have any questions or needs. We are here for help.

Best Regards,
Sweety

E-iceblue support team