2

While working with Excel Interop, i come up with an issue. Before Saving I Autofit columns of worksheet with the following code.

Excel.Worksheet curSheet = (Excel.Worksheet)wsEnumerator.Current;
curSheet.UsedRange.EntireColumn.AutoFit();

And it works fine for me. Then i encountered an issue with some specific Excel sheet. If i autofit it says AutoFit method of Range class failed.

The excel sheet which cause the issue is available here

Now what i want to know is, whether there is some way to check if an excel sheet support Autofit or not. Something like

if(sheet.CanAutoFit())
    curSheet.UsedRange.EntireColumn.AutoFit();

Any help would be appreciated.

Mirza Bilal
  • 891
  • 11
  • 34
  • 1
    According to http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.autofit(v=office.11).aspx#Y0 it generates an error if it's not a row or range of rows, or a column or range of columns. Is there something different about the "specific Excel sheet" that would cause this? – SpaceBison Nov 16 '12 at 13:08
  • Actually if you open this Excel Document in Microsoft Excel, you will notice, you cant even autofit cells that are available for input. Its some fixed type of excel sheet. – Mirza Bilal Nov 16 '12 at 13:13
  • 1
    Are you sure it isn't because your sheet is protected? You can't resize columns on a protected sheet. Once I'd unprotected your example Excel sheet I was able to autofit all of the columns. – Sid Holland Nov 16 '12 at 15:55
  • yeah you are right it was protected... – Mirza Bilal Nov 17 '12 at 23:04

2 Answers2

1

If there's no sheet.CanAutoFit() then you can always do this:

try {
    curSheet.UsedRange.EntireColumn.AutoFit();
} catch (TheSpecificExcelException exc)
{ ... }
Adrian Ciura
  • 1,102
  • 1
  • 9
  • 14
  • 2
    But that would not be a good thing to do, to Create exception for something you know will not work. There must be some proper solution for that. And moreover if Mircosoft Excel knows it can not autofit, then there must be some way to do it. – Mirza Bilal Nov 16 '12 at 13:32
1

The issue was sheet was protected. And to handle that i have used try catch exception handling. So it has been resolved.

Mirza Bilal
  • 891
  • 11
  • 34