Excel. Длинные формулы.

[v00d00]

Ситуация такая.

Есть формула в которой используются именованные диапазоны.

При копировании такой длинной формулы в другой файл, вместе с именем диапазона Excel вставляет ссылку на файл, из которого она была скопирована.
Причем путь к файлу выходит достаточно длинным. Что-то типа

PHP код:

"C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]" 


И новая формула перестает помещаться в редактор (2003 Excel, 1024 символа на формулу).

Однако если вызвать окно "показать этапы вычислений", то в нем виден текст всей формулы. ВТФ?

Пытаешься редактировать формулу, вылетает ошибка "слишком длинная формула"

Если пытаться удалить последний символ в формуле, то теряется та часть формулы, которая видна в окне "показать этапы вычислений".

При попытке программного чтения формулы из этой ячейки

PHP код:

formula = (object)cell.Formula; 


вылетает ексепшн на уровне COM интерфейса. Hresult0x__________.
Этот эксепшен (после гугления по нему) возникает во множестве случаев, причем все случаи разные, похоже что это внутрення ошибка Екселя указывающая на проблемы с памятью


Как прочесть формулу правильно?

Можно ли отключить эту фичу в Exсel(авто вставка ссылки на файл)? т.к. копирование данных происходит в идентичных по своей структуре и содержанию файлах.

[EducatedFool]

При копировании такой длинной формулы в другой файл, вместе с именем диапазона Excel вставляет ссылку на файл, из которого она была скопирована.

А Вы не копируйте ячейку - копируйте только формулу.
Тогда проблем не будет.

То есть вместо такого кода: Ячейка1.copy Ячейка2

используйте что-то вроде этого:
формула = Ячейка1.Formula
Ячейка2.Formula = формула

ну или проще: Ячейка2.Formula = Ячейка1.Formula

При попытке программного чтения формулы из этой ячейки
formula = (object)cell.Formula;
вылетает ексепшн на уровне COM интерфейса.

Вы уверены, что в Вашем коде (object)cell - это ссылка на ОДНУ ячейку, а не на диапазон ячеек?

Ошибка возникает при чтении формулы из исходной книги, или из той, куда была скопирована ячейка вместе с формулой?

Этот эксепшен (после гугления по нему) возникает во множестве случаев, причем все случаи разные, похоже что это внутрення ошибка Екселя указывающая на проблемы с памятью

Сомневаюсь, что проблема в excel. Скорее, в коде.
Попробуйте вместо свойства Formula считать аналогичные свойства ячейки - FormulaLocal, FormulaR1C1, FormulaR1C1Local

[v00d00]

Спасибо за развернутый ответ. Попробую последовать вашему совету.

На сколько я понимаю, ячейка это объект класса Range. Извените за шарповый код, но смысл думаю будет понятен

PHP код:

            int iRows;
            int iCols;

            iRows = range.Rows.Count;
            iCols = range.Columns.Count;

            if (iRows==1 && iCols==1)
            {
                object formula = new object();
                try
                {
                    formula = (object)range.Formula;
                    //formula = (object)((Range)range.Cells[1,1]).Formula;

                }
                catch (System.Exception e)
                {
                    throw new ExcellCellFormulaTooLongException(Properties.Resources.cStrTooLongFormula, e);
                }
                
                string[,] result = new string[1,1];
                result[0,0] = DeleteLinks((string)formula);
                return result;
            } 


Я работаю с ячейкой по этой ветке if т.к. в нормально ситуации свойство Formula возвращает объект, а не массив.


FormulaR1C1
FormulaR1C1Local
FormulaLocal

Все кидаются тем же ексепшеном.

Возможно описание ситуации в которой это происходит поможет дать совет.

Есть большой и сложный файл Excel, который содержит набор именованных диапазонов. Это файл шаблон.

Есть копия этого файла с измененными значениями некоторых полей (для другого клиента допустим). В этом файле есть такие же диапазоны как и в файле шаблоне.

Когда происходит копирование ячеек между шаблоном и рабочей копией, формулы с именованными диапазонами сохраняются с ссылками на файл, откуда они были скопированы. Ссылки вставляются перед именем диапазона.

[EducatedFool]

Я работаю с ячейкой по этой ветке if т.к. в нормально ситуации свойство Formula возвращает объект, а не массив.

Не совсем понял...
В случае, если (iRows==1 && iCols==1), т.е. range состоит из ОДНОЙ ячейки,
функция .Formula возвращает не массив значений, а обычную текстовую строку.

Почему Вы в этом случае объявляете formula с типом object?
object formula = new object();

PS: Я, конечно, совсем незнаком с шарпом, но мне кажется, ошибка именно в этом.

[v00d00]

Возможно действительно я делаю что-то не так в коде. Возможно не хватает опыта работы с COM Excel. Но приведенный код работает для любой ячейки кроме условно испорченных (те ячейки в которых длинна формул превышает 1024 символа. Как так получается я написал выше)

Еще раз спасибо за ваши ответы. Возможно вы натолкнете меня на правильную реализацию.

Я не предоставил важную информацию в первом посте, возможно это натолкнет вас на догадку.

Excel 2003
Каждый раз когда я открываю проблемный файл мастер предлагает обновить ссылки. Когда мастер просит указать вместо ссылки на несуществующий файл указать ссылку на существующий, я подсовываю короткий путь к заведомо правильному файлу и окно мастера закрывается с ошибкой "Слишком сложная формула"

Excel 2007 (В этой версии длинна формулы увеличена до 8к символов)
Книга открывается с ошибкой "Данные могут быть утеряны". Однако в редакторе формул я вижу всю формулу целиком.


Кусок формулы видимый в редакторе Excel 2003

PHP код:

="Using an estimated downtime of "&
ЕСЛИ('C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!C35='C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!D35;

'C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!C35;

'C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!C35&" to "&'C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!D35)&" months and a "&ТЕКСТ('C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!C36;"0.00%")?" renewal probability, projected 


Формула видимая в редакторе Excel 2007

PHP код:

[B]=("Using an estimated downtime of "&ЕСЛИ('C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!C35='C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!D35;

'C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!C35;

'C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!C35&" to "&'C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!D35)&" months and a "&ТЕКСТ('C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!C36;"0.00%")&" renewal probability, projected[/B] stabilized vacancy rates ranging from "&ТЕКСТ(+'C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!$C$38;"0.00%")&" to "&ТЕКСТ(+'C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!$D$38;"0.00%")&", assuming typical "&ЕСЛИ(+'C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!C37=+'C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!D37;

ТЕКСТ(+'C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!C37;"0");ТЕКСТ(+'C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!C37;"0")&" to "&ТЕКСТ(+'C:\Documents and Settings\User\Рабочий стол\2k91207 Mission Trail MTR (S), Lake Elsinore\[2k91207 Mission Trail MTR (S), Lake Elsinore.xls]CAP-MT'!D37;"0"))&"-month lease terms.  ") 


Файл создан в 2003 Excel. Видно что формула хранится целиком, но интерпретатор формул 2003 Excel, наверно, просто не может ее скушать целиком.

В тоже время в 2007 Excel с увеличенным буфером для формул это все работает. Я пишу addin по технологии VSTO. Самое печальное то, что код, который работает с ошибками в Excel.Application.2003 совершенно нормально выполняется в Excel.Application.2007