WorksheetWrapper.ColumnSets question for setting column width

Mar 3, 2010 at 9:58 PM
Edited Mar 3, 2010 at 9:59 PM

I have a scenario where I have to create new worksheets and I have worked around the issue of setting styles by having an existing worksheet with cells styled as reference styles.  I then use the StyleId for those references cells as the StyleId for any cell I need styled.  That works fine, however, I tried to do a similar thing with column widths but the issue I am running into is that WorksheetWrapper.ColumnSets.Count() is zero in every case except for when I actually open the file in excel and adjust a column.  This doesn't work if the sheet is newly created via the ExtremeML api.  Since my reference columns exist in my reference sheet that one is fine.  How can I set a column with for columns in a sheet where I have specifically set some cell formulas and cell values in those columns when it appears that the only way to get to the ColumnWrapperCollection is via the ColumnsWrapperCollection (ColumnSets property) which is zero.

Is this because the Column really doesn't exist because no specific width has been set?

How can I set the width of a column if I cannot get a reference to the ColumnWrapper?

Coordinator
Mar 4, 2010 at 8:28 AM
Edited Mar 4, 2010 at 8:29 AM

Your analysis is right - no column definitions exist unless you explicitly assign non-default column properties in your template. This behavior is native to Excel, which you can see for yourself by reflecting on your template using the SDK Tool.

To manipulate column properties in your code, you need to work with the ColumnSets property. For a programmatically created worksheet this means adding a new member to the ColumnSetWrappers collection and then creating and assigning property values to its relevant sub-objects. However, as you have seen, this is not possible, because there is no Add() method.

It is apparently very simple to extend ExtremeML to include an Add() method for columns, but I have hesitated to implement this functionality because there is a contradiction between the object model used by the underlying SDK and the OpenXML specification. The SDK uses the Columns collection class (which coresponds to the OpenXML "CT_Cols" type) to contain a number of instances of the Column class (which corresponds to the OpenXML "CT_Col" type). This would suggest that each worksheet has a single Columns collection that contains zero or more Column objects. However, the OpenXML spec contradicts this, apparently allowing the "CT_Worksheet" type to have an unlimited number of contained "CT_Cols" elements.

Perhaps this is a mistake in the OpenXML spec (and I can't see any logical reason why a worksheet should require more than one Columns collection), but I don't wan't to extend the ExremeML API to support operations that might conflict with legitimate Excel data scenarios, so I am currently stalled on this issue.

The object model currently implemented in ExtremeML supports the worst case, so it should be able to handle any template data scenario that the OpenXML spec can throw at it:

    ColumnSets
        ColumnSet
            Columns
                Column

However, I don't think it's advisable to implement API functionality that allows multiple ColumnSets if Excel doesn't really like this. If you are inclined to do the research work to find out what the API should actually support, I will be happy to create the new methods that will allow you to programmatically add your own column definitions.

Beware - there is another complication. Don't expect to see a column definition for each column in your worksheet. An OpenXML column definition corresponds to a number of contiguous worksheet columns that share the same non-default properties. So, if your worksheet contains 10 columns of the same non-standard width, there will be only one column element in the document. This could also present a significant API challenge. For example, setting a new column width on column 5 of a group of 10 identical columns entails creating three separate identical column definitions and assigning the new width to the middle one.

Good luck!

Tim

Mar 4, 2010 at 3:08 PM

I have done a little more digging around in the xml files that excel has in the file to see what it is actually doing in certain circumstances.  You are right that a column definition uses the min and max values to designate a set of columns.  I took a blank sheet and set the column widths on random columns and left others alone.  Each column I set random widths on had a col entry in the cols element.  All other columns had their widths set by the defaultColWidth attribute of the sheetformatPr element.

What I have done for now is to set that defaultColWidth to an amount that is close to what is needed.  Some columns should be wider and others should be narrower but for now I think that will work.  For people who are not creating a new sheet but are dynamically using new columns that they may not have set widths on specifically in the template, the WorksheetWrapper.SheetFormatProperties.DefaultColumnWidth can be used to set the width of ALL columns that have not been specifically set in the Excel template.

I am going to see if I can find something that will work a little better for my needs at the moment.  It may not be a generic option that adheres to the spec but ifI can get something that works without breaking something else I will share it.