Using a Character count past "Z" in C#

Question

I have a Data Set dump that uses a Character set type loop find the first column letter to drop it in. This works great for A-Z, but when I need AA, or AB, I am at a loss. Here is what I am using, reworked from some code I found on the web. Can i do anything?

public void ExportToExcel(DataSet dataSet, string templatePath, int i, int h)
    {
        Excel.Application excelApp = new Excel.Application();
        excelApp.Visible = true;
        FileInfo excelFileInfo = new FileInfo(templatePath);
        Boolean fileOpenTest = IsFileLocked(excelFileInfo);


        Excel.Workbook templateBook;
        if (!fileOpenTest)
        { templateBook = excelApp.Workbooks.Open(templatePath); }
        else
        { templateBook = (Excel.Workbook)System.Runtime.InteropServices.Marshal.BindToMoniker(templatePath); }
        string tabName = lstQueryDumpSheet.Items[i].ToString();
        Excel.Worksheet templateSheet = templateBook.Sheets[tabName];

        // Copy DataTable
        foreach (System.Data.DataTable dt in dataSet.Tables)
        {
            // Copy the DataTable to an object array
            object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];

            // Copy the values to the object array
            for (int col = 0; col < dt.Columns.Count; col++)
            {
                for (int row = 0; row < dt.Rows.Count; row++)
                { rawData[row, col] = dt.Rows[row].ItemArray[col]; }
            }

            // Calculate the final column letter
            string finalColLetter = string.Empty;
            string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            int colCharsetLen = colCharset.Length;

            if (dt.Columns.Count > colCharsetLen)
            { finalColLetter = colCharset.Substring((dt.Columns.Count - 1) / colCharsetLen - 1, 1); }

            finalColLetter += colCharset.Substring((dt.Columns.Count - 1) % colCharsetLen, 1);

            // Fast data export to Excel
            string dumpCellString = lstQueryDumpText.Items[i].ToString();
            string dumpCell = dumpCellString.Split('=').Last();
            string excelRange = string.Format(dumpCell + ":{0}{1}", finalColLetter, dt.Rows.Count + 1);

            templateSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
        }

[http://stackoverflow.com/a/667902/2237785](http://stackoverflow.com/a/667902/2237785) — Bond, Jun 26 '15 at 18:49

score 0 · Answer 1 · answered Jun 26 '15 at 18:56

You can use Excel to convert a column index to letter or vice versa.

Here's some VBA code to do it. You can adapt to c#:

' Letter (AA) to index (27)...
MsgBox Sheet1.Columns("AA").Column

' Index (27) to letter (AA)...
MsgBox Mid$(Sheet1.Columns(27).Address, 2, 2)

Using a Character count past "Z" in C#

1 Answers1