Index out of Range exception

[NOLONGERWITHCOMPANYAmanda Kabak]

I've got some code that compares the length of the newly added content in a cell to the width of the column that holds that cell. Most of the time it is working properly, but sometimes I'm getting an out of range exception when trying to access the column by index. Here's a sample of the code:

private void PopulateCellWithValue(IWorksheet Sheet, int RowIndex, int ColumnIndex, ...)
{
IRange cell = Sheet[RowIndex, ColumnIndex];
cell.Value = ...;
cell.HorizontalAlignment = ExcelHAlign.HAlignRight;
//the line below gives an IndexOutOfRangeException on the
//cell.Worksheet.Columns[ColumnIndex - 1] portion
if (cell.Value.Length > cell.Worksheet.Columns[ColumnIndex - 1].ColumnWidth)
{
cell.Worksheet.Columns[ColumnIndex - 1].ColumnWidth = cell.Value.Length;
}
...

I'm not sure why I can't get a reference to the column off the worksheet when I already have a cell populated in that column. As I understand it, ColumnIndex is a 1-based index when used in the Range function (Sheet[RowIndex, ColumnIndex]) but is a zero-based index when used on collections like cell.Worksheet.Columns[ColumnIndex - 1].

I realize you probably need more details, but my code is thousands of lines long with an external database reference. What I'm looking for is some idea of why I can put value in a cell in column G (ColumnIndex = 7) and why I can navigate around in cell.Worksheet and see values in ranges [x, 7] but when I try to reference the column via the collection on the worksheet, I only see indexes 0-5 instead of 0-6.

Thanks.


amanda

[John Bowlin Bosco K]

Hi Amanda,

Thank you very much for your interest in Syncfusion Products.

The code that you specified in the update (i.e.)
cell.Worksheet.Columns[ColumnIndex - 1].ColumnWidth
indicates the column width of one of the columns in the worksheet whose value is specified as index. So, if there are 3 columns used in the worksheet and the ColumnIndex is specified as 5 means, then the above code will take the 4th column of the columns that has not been present in the worksheet which will throw exception.

So, if you want to check the column width of the particular cell, you can use directly the below code (i.e)
//The width of the column having the specified cell value is checked
if (cell.Value.Length > cell.ColumnWidth)
{
cell.ColumnWidth = cell.Value.Length;
}
which will set the column width to the length of the value in that cell.

For your reference, we have attached the sample below. Please try the sample from the link and let us know if this helps you.

Sample Link:
http://www.syncfusion.com/uploads/redirect.aspx?&team=support&file=ColumnWidthSample_99056-15554609.zip

Please let us know if you need any further assistance.

Thanks,
Pauline Bosco

[NOLONGERWITHCOMPANYAmanda Kabak]

You didn't answer my question. As I said, "I've got some code that compares the length of the newly added content in a cell to the width of the column that holds that cell." I'm essentially doing a "size to fit" as I add values into cells in a specific column. If the value is larger than the width of the column so far, I want to increase the width of the column to the latest value added, which I believe the code showed.

So I'm not interested in finding the width of the cell, I'm interested in finding the width of the column that the cell is in.

My question is, "I'm not sure why I can't get a reference to the column off the worksheet when I already have a cell populated in that column." And then I gave specifics of the actual condition I'm seeing: "What I'm looking for is some idea of why I can put value in a cell in column G (ColumnIndex = 7) and why I can navigate around in cell.Worksheet and see values in ranges [x, 7] but when I try to reference the column via the collection on the worksheet, I only see indexes 0-5 instead of 0-6."

Not sure how much more specific I could have been in my question.

I'd appreciate it if you could address the actual question I asked. I wouldn't be so annoyed about the back and forth if it didn't always take overnight to get a new answer. Thank you.

[NOLONGERWITHCOMPANYAmanda Kabak]

I've answered my own question.

The worksheet columns collection includes only columns in which there are data. In this instance, the first column on the worksheet doesn't get filled with data until at the bottom when I output summary rows. So when I am at the top of the document, the column collection in the worksheet doesn't hold a column for A but instead starts with column B.

In other words, the worksheet column collection isn't just a map of the columns in the Excel spreadsheet, it is a collection of the columns in which at least one cell has been populated with data. In this way, it may not correspond one-to-one to the index used in the range function.

[Sridhar]

Hi Amanda,

Thank you very much for the update.

We have forwarded to the development for more analysis. We will get back to you with more details once we hear from them.

Thanks,
Sridhar.S

[John Bowlin Bosco K]

Hi Amanda,

Thank you very much for your patience.

The column width of the particular column can be set automatically by specifying the column index in AutoFitColumn(colIndex) method. Also, the column width can be increased to the latest value added in the particular cell. These are illustrated in the sample that we have created and it has been linked below. Could you please find the sample and let us know if this helps you?

Sample Link:
http://www.syncfusion.com/uploads/redirect.aspx?&team=support&file=ColumnWidthSample_99056-289191618.zip

For more information, please find the below documentation to know how to change the size of the cell.

Documentation Link:
http://help.syncfusion.com/ug_91/Reporting/XlsIO/Windows%20Forms/default.htm?turl=Documents%2Fchangingcellsize.htm

Please let us know if you require any further clarifications.

Thanks,
Pauline Bosco