none
Excel: How to find the index of a Spreadsheet Column? RRS feed

  • Question

  • I am trying to determine the Style of each column, in order to apply it to the data that is inserted in each cell.

    The confusing part is that instead of an index, the Column class provides a Min and a Max value. Sometimes they are the same, sometimes they are 1 unit apart.

    Related Code:

    Worksheet worksheet = worksheetPart.Worksheet;
    Columns   columns   = worksheet.GetFirstChild<Columns>();
    
    for (Column column = (Column) columns.FirstChild; column != null; column = (Column) column.NextSibling())
    {
        UInt32 Style;
    
        if (column.Style == null)
        {
            Style = 0;
        }
        else
        {
            Style = column.Style.Value;
        }
        var Min = column.Min.Value;
        var Max = column.Max.Value;
        Console.WriteLine("Column Min=" + Min + ", Max=" + Max + ", Style: " + Style);
    }
    

    What is the deal with that Min-Max?

    TIA

    Friday, December 27, 2013 1:25 AM

Answers

  • See this page

    http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column(v=office.14).aspx

    The Column information basically defines the formatting for an entire column. Min specifies the first column index to which the formatting is applied; Max specifies the last column to which the formatting is applied.

    The new open xml file format is a bit of a problem for Excel as the file sizes can get very large, which affects performance. In order to optimize as much as possible, Excel "concatenates" as much information as possible. So columns that are formatted the same are in the same listing and aren't written out individually.


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by Travis Banger Saturday, December 28, 2013 10:38 PM
    Saturday, December 28, 2013 4:00 PM
    Moderator