Rambler's Top100
"Knowledge itself is power"
F.Bacon
Поиск | Карта сайта | Помощь | О проекте | ТТХ  
 Hello, World!
  
 

Фильтр по датам

 
 К н и г и
 
Книжная полка
 
 
Библиотека
 
  
  
 


Поиск
 
Поиск по КС
Поиск в статьях
Яndex© + Google©
Поиск книг

 
  
Тематический каталог
Все манускрипты

 
  
Карта VCL
ОШИБКИ
Сообщения системы

 
Форумы
 
Круглый стол
Новые вопросы

 
  
Базарная площадь
Городская площадь

 
   
С Л С

 
Летопись
 
Королевские Хроники
Рыцарский Зал
Глас народа!

 
  
ТТХ
Конкурсы
Королевская клюква

 
Разделы
 
Hello, World!
Лицей

Квинтана

 
  
Сокровищница
Подземелье Магов
Подводные камни
Свитки

 
  
Школа ОБЕРОНА

 
  
Арсенальная башня
Фолианты
Полигон

 
  
Книга Песка
Дальние земли

 
  
АРХИВЫ

 
 

Сейчас на сайте присутствуют:
 
  
 
Во Флориде и в Королевстве сейчас  23:20[Войти] | [Зарегистрироваться]

Особенности работы с «Русским» Excel'ем

Александр Шабля
дата публикации 11-02-2004 13:19

Особенности работы с "русским" Excel'ем

Написанное приложение, прекрасно работающее с Excel'ем на собственном компьютере, часто, после переноса приложения на другой компьютер, оказывается неработоспособным! Отчего так происходит? В этой статья я собираюсь описать разницу в работе русской версии Excel'я из VBA и через COM интерфейс (библиотеку типов, TLB) из Delphi. Почему возникли расхождения? Ответа на эти вопросы у Microsoft я не нашел…

Примечание:
сравнивались только русская и английская (American English) версии Excel с номером версии 9.0 (MS Office 2000) и выше. Другие версии не рассматривались.

Описание типов объектов, применяемых в примерах:
XL: TExcelApplication;
WB: TExcelWorkbook;
ASheet: TExcelWorksheet;
R: Range; // ExcelRange - для Delphi7


Используемые в примерах "дополнительные" модули:
OleServer, Excel2000, Office2000 из стандартной поставки Delphi Enterprise версии 6 и выше.

У вас русская версия Excel?

Определить наличие русской версии Excel возможно так:
if XL.LanguageSettings.LanguageID[msoLanguageIDUI] = 1049 {или $0419}
  then { Excel имеет русский интерфейс пользователя };

Английская версия Excel (English United States) вернет 1033 (или $0409), немецкая (German Standard) - $0407. Значения соответствуют LCID, описанным в MS SDK Help "Language Identifiers". LCID интерфейса пользователя и файла Excel.exe файла может быть неодинаковым (например, после установки MUI). Константа msoLanguageIDUI находится в модуле Office2000.pas и описана так:

const
  msoLanguageIDUI = $00000002;

Примечание:
в Office97 свойство LanguageSettings отсутствует

Далее мы рассмотрим приемы работы с "русским" Excel'ем.

Работа со свойством объекта Range NumberFormat

NumberFormat и NumberFormatLocal четко работают в VBA и полностью соответствуют своему содержанию в названиях, но только не при работе из Delphi. В Excel2000.pas (D7) они описаны как

ExcelRange = dispinterface
    ['{00020846-0000-0000-C000-000000000046}']
    ...
    property NumberFormat: OleVariant dispid 193;
    property NumberFormatLocal: OleVariant dispid 1097;

Но, при попытке записи форматов из Delphi, выясняется, что NumberFormat и NumberFormatLocal ведут себя идентично, причем NumberFormat соответствует NumberFormatLocal (лучше было бы наоборот :). Т.е. в русской версии все форматы нужно писать "по-русски" (можно прямо в NumberFormat, в VBA - нельзя).

Формат даты

Код на VBA (эталон):
Sub Test1()
  Dim R As Range
  Set R = Range("a1")
  R.Clear ' очистим формулы и форматы
  R.Value2 = Date ' запишем текущую дату
  R.NumberFormat = "d/mm/yy"   ' работает
  R.NumberFormatLocal = "ДД.ММ.ГГ" ' работает
  ' дальше не работает
  R.NumberFormat = "ДД.ММ.ГГ"  ' не работает
  R.NumberFormatLocal = "d/mm/yy" ' ОШИБКА!
  Set R = Nothing
End Sub
Код на Delphi:
R := ASheet.Range['A1', EmptyParam];
R.Value2 := Date;
R.NumberFormat := 'd/mm/yy'; // ОШИБКА!
R.NumberFormat := 'ДД.ММ.ГГ'; // работает
R.NumberFormatLocal := 'ДД.ММ.ГГ'; // работает
R.NumberFormatLocal := 'd/mm/yy'; // ОШИБКА

Формат чисел. Разделители. (DecimalSeparator, ThousendSeparator)

Почитайте "диалог" на Круглом столе http://www.delphikingdom.com/asp/answer.asp?IDAnswer=15340 - вроде бы все понятно ("а все и делов то в запятой")! А нет, не все! В "International" (в русском "Язык и стандарты") можно установить любые DecimalSeparator и ThousandsSeparator, отличные от принятых по-умолчанию фирмой Microsoft для русской версии Windows. Я, например, всегда меняю принятые по-умолчанию десятичную точку "," на "." и разделитель тысяч с " " (пробел) на "'" (апостроф, как в калькуляторе). Так формат "# ##0,00" у меня работать не будет...

И это еще не все! Заходим в настройки Excel'я "Сервис/Параметры" переходим на закладку "Международные" и видим опять "Разделитель целой и дробной части", "Разделитель разрядов" и чекбокс "Использовать системные разделители". Т.е. использование системных разделителей не может гарантировать правильного применения при форматировании чисел в Excel'е. Решение: использовать свойство ExcelApplication.International (о нем дальше). Причем, даже при установленном свойстве ExcelApplication.UseSystemSeparators = False и отличных от системных ExcelApplication.DecimalSeparator и ExcelApplication.ThousandsSeparator, ExcelApplication.International отработает корректно.

Далее рассмотрим примеры работы (или не работы), приняв "стандартные" настройки для русских Windows: Код на VBA (эталон):
Sub Test2()
  Dim R As Range
  Set R = Range("a1")
  R.Clear
  R.Value = 1234567.89
  R.NumberFormat = "#,##0.00" ' работает
  R.NumberFormatLocal = "# ##0,00" ' работает для стандартных настроек
  R.NumberFormat = "# ##0,00"  ' не работает
  R.NumberFormatLocal = "#,##0.00" ' не работает
  Set R = Nothing
End Sub
Код на Delphi:
R := ASheet.Range['A1', EmptyParam];
R.Value2 := 1234567.89;
R.NumberFormat := '#,##0.00'; // не работает
R.NumberFormatLocal := '# ##0,00'; // работает для стандартных настроек
R.NumberFormat := '# ##0,00'; // работает для стандартных настроек

Примечание:
в примерах значения записываются в Value2 для предотвращения форматирования "на лету" самим Excel'ем. Так число 123.45, записанное в Value будет автоматически отформатировано Excel'ем в формат валюты, а присвоение Value = Date будет автоматически переведено в формат даты. Запись в Value2 "воспринимает" значение как Double. Подробнее смотрите в справке VBA для Excel'я.

Решения (с использованием ExcelApplication.International):

Для получения формата даты можно написать функцию:
function XL_GetShortDateFormat(XLApp: ExcelApplication): String;
var d, m, y: Integer;
begin
  if XLApp.International[xlDayLeadingZero, lcid]
    then d := 2 else d := 1;
  if XLApp.International[xlMonthLeadingZero, lcid]
    then m := 2 else m := 1;
  if XLApp.International[xl4DigitYears, lcid]
    then y := 4 else y := 2;
  Result := Format('%1:s%0:s%2:s%0:s%3:s', [
    DateSeparator,
    StringOfChar(VarToStr(XLApp.International[xlDayCode, lcid])[1], d),
    StringOfChar(VarToStr(XLApp.International[xlMonthCode, lcid])[1], m),
    StringOfChar(VarToStr(XLApp.International[xlYearCode, lcid])[1], y)
  ]);
end;

Для формата чисел:
function XL_GetNumberFormat(XLApp: ExcelApplication): String;
begin
  Result := Format('#%s##0%s%s', [
    XLApp.International[xlThousandsSeparator, lcid],
    XLApp.International[xlDecimalSeparator, lcid],
    StringOfChar('0', Integer(XLApp.International[xlCurrencyDigits, lcid]))
  ]);
end;
Для формата валюты:
function XL_GetCurrencyFormat(XLApp: ExcelApplication): String;
begin
  Result := Format('%s "%s"', [
    XL_GetNumberFormat(XLApp),
    XLApp.International[xlCurrencyCode, lcid]
  ]);
end;

Тот же принцип можно применить к времени и другим типам. Также смотрите другие индексы для свойства International (их там много) в справке VBA. Например, получить "основной" (general) формат можно так:

GenFmt := XL.International[xlGeneralFormatName, lcid];

Примечание:
установить основной формат еще можно установить, записав в NumberFormat "пустую" строку, т.е. указать, что нет форматирования для чисел (даты): Range.NumberFormat := '';

Цвет в формате

К сожалению, не лучше обстоит дело и с цветом в форматах. Т.е. цвет в Delphi можно задавать только по-русски:

R.NumberFormat := 'Основной;[красный]-Основной';
Перечень цветов по-русски, которые можно задавать в формате: черный, красный, зеленый, синий, фиолетовый, желтый, белый. Список небогатый.

Формулы на листе

К счастью, работа со свойствами Formula и FormulaLocal в VBA и Delphi идентична и соответствуют своим названиям. Хочется отметить только один нюанс (это, кстати, действительно и для VBA) - при написании "русских" формул нужно учитывать системную переменную ListSeparator. Так, если на другом компьютере пользователь изменит его со стандартного для русской версии Windows символа ";" на "," (например, как это делаю я :), то присвоение Range.FormulaLocal := '=округл(A1*B1; 2)'; вызовет ошибку! Поэтому, с учетом "разделителя элементов списка" нужно писать так:

Range.FormulaLocal := Format('=округл(A1*B1%s 2)', [ListSeparator]);
или
Range.Formula := '=round(A1*B1, 2)';

Здесь приятней и проще пользоваться английскими формулами. Но, иногда, существует необходимость писать формулы из вариантного массива…

Примечание:
системные переменные ListSeparator, DateSeparator описаны в модуле System.

Запись формул из Variant-ного массива

Запись в свойство Formula, FormulaLocal, Value, Value2 из Variant-ного массива идентична в русском Excel'е и при работе из Delphi. Но, если мы хотим вставлять формулы прямо из массива, все они должны быть только русскими!

Примечание:
спасибо фирме Microsoft, которая в версии Excel 2003 все это исправило. Теперь, при работе из Delphi, свойство Formula для вариантных массивов полностью соответствует формулам на английском, а FormulaLocal - на русском языке. Однако новые заботы - необходимо проверять, что у нас Excel версии 9 или 10 (2000 или XP), а не 11 (2003) или всегда записывать в FormulaLocal :o). Но вот что самое удивительное - непосредственно из VBA работают только английские формулы!

Здесь всплывает необходимость определения наличия русской версии Excel'я (только для Excel2000/XP - Excel2003 отрабатывает правильно). Данный пример на VBA будет работать только для Excel2000/XP со стандартными настройками "Региональных параметров" Windows. Для работы примера в Excel2003 нужно оставить только английские формулы и запись в свойство Value или Formula и... никаких Local.

Код на VBA:

Sub TestVariant()

  Dim MyVar(2, 2) As Variant ' 3 строки, 3 колонки
  Dim R As Long, C As Byte

  ' первая строка
  MyVar(0, 0) = 10.72
  MyVar(0, 1) = 3.05
'  MyVar(0, 2) = "=round(RC[-1]*RC[-2], 2)" ' ошибка #ИМЯ, для Excel2003 - работает
  MyVar(0, 2) = "=округл(RC[-1]*RC[-2]; 2)" ' работает для стандартных настроек
  ' вторая строка
  MyVar(1, 0) = 4.57
  MyVar(1, 1) = 7.23
'  MyVar(1, 2) = "=round(A2*B2, 2)" ' ошибка #ИМЯ, для Excel2003 - работает
  MyVar(1, 2) = "=округл(A2*B2; 2)" ' работает для стандартных настроек
  ' итог
'  MyVar(2, 2) = "=sum(C1:C2)" ' ошибка #ИМЯ, для Excel2003 - работает '
  MyVar(2, 2) = "=сумм(C1:C2)" ' работает
'  MyVar(2, 2) = "=сумм(R[-2]C:R[-1]C)" ' работает
  
  With Range("A1:C3")
    .Clear ' чистим область ячеек от формул и форматов
    .Value = MyVar  
'    .Value2 = MyVar ' работает
'    .Formula = MyVar ' работает
'    .FormulaLocal = MyVar ' работает
  End With
End Sub

Код на Delphi

(тут мы применим знание написания русских формул, описанный выше, а именно ListSeparator): Чтоб код, приведенный ниже, работал, необходимо проверять, что у нас Excel версии 9 или 10 (2000 или XP) или писать только в FormulaLocal.

var
  MyVar: Variant;
  IsRusXL, IsExcel2kXP: Boolean;
begin
...
  MyVar := VarArrayCreate([0, 2, 0, 2], varVariant); // 3 строки, 3 колонки
  // определим, русский ли у нас Excel
  IsRusXL := XL.LanguageSettings.LanguageID[msoLanguageIDUI] = $0419;
  // Проверим версию: Excel версии 9 или 10?
  IsExcel2kXP := InRange(StrToFloatDef(StringReplace(XL.Version[lcid],
    '.', DecimalSeparator, []), 0), 9, 10);

  // первая строка массива
  MyVar[0, 0] := 10.72;
  MyVar[0, 1] := 3.05;
  if IsRusXL and IsExcel2kXP
    // стиль R1C1
    then MyVar[0, 2] := Format('=округл(RC[-1]*RC[-2]%s 2)', [ListSeparator])
    else MyVar[0, 2] := '=round(RC[-1]*RC[-2], 2)';
  // вторая строка массива
  MyVar[1, 0] := 4.57;
  MyVar[1, 1] := 7.23;
  if IsRusXL and IsExcel2kXP
    // стиль A1
    then MyVar[1, 2] := Format('=округл(A2*B2%s 2)', [ListSeparator])
    else MyVar[1, 2] := '=round(A2*B2, 2)';
  // итог
  if IsRusXL and IsExcel2kXP
    then MyVar[2, 2] := '=сумм(C1:C2)' // '=сумм(R[-2]C:R[-1]C)'
    else MyVar[2, 2] := '=sum(C1:C2)';

  with ASheet.Range['A1:C3', EmptyParam] do begin
    Clear;
      { Formula - работает для русских формул для Excel2000/XP, и
        английских для Excel2003 }
      Formula := MyVar; // работает
      { FormulaLocal - работает для русских формул для Excel2000/XP/2003 }
//      FormulaLocal := MyVar; // работает
      { FormulaR1C1 - работает для русских формул для Excel2000/XP, и
        английских для Excel2003, заданных только в стиле R1C1 }
//      FormulaR1C1 := MyVar; // не работает, если есть ссылки в стиле A1
      { FormulaR1C1Local - работает для русских формул для Excel2000/XP/2003,
        заданных только в стиле R1C1 }
//      FormulaR1C1Local := MyVar;
      { Value - работает для русских формул для Excel2000/XP, и
        английских для Excel2003 }
//      Value := MyVar;
      { Value2 - работает для русских формул для Excel2000/XP, и
        английских для Excel2003 }
//      Value2 := MyVar; // работает
  end;
...

Примечание:
из примера видно, что при записи из Variant-ного массива в Formula, FormulaLocal, Value, Value2 не имеет значения, какой стиль ссылок используется: A1 и R1C1 работают идентично. Но это не относится к свойствам FormulaR1C1 и FormulaR1C1Local, которые принимают формулы ТОЛЬКО в стиле R1C1.

Создание колонтитулов

Давайте запустим запись макроса создания колонтитула (меню в Excel "Сервис/Макрос/Начать запись…"). Теперь откроем параметры страницы (меню "Файл/Параметры страницы…"). Создадим центральный нижний колонтитул "Лист &[Страница] из &[Страниц]" шрифтом "Arial", "полужирный" и размером 8pt. Слова "Лист" и "из" с начертанием "обычный". После "сокращения" макроса получим:

Sub Макрос1()
'
  ActiveSheet.PageSetup.CenterFooter = _
    "&""Arial""&8Лист &""Arial,полужирный""&P" & _
    "&""Arial,обычный"" из &""Arial,полужирный""&N"
End Sub
Т.е. при выводе на печать мы хотим, чтоб в нижний колонтитул по центру выводился текст, к примеру "Лист 1 из 5".

Примечание:
если вы хотите увидеть работу вашего макроса в действии (чтоб работал PrintPreview), обязательно внесите на лист хоть какие-нибудь данные.

Внимание! Суммарная длина текста в нижнем или верхнем (левый + по_центру + правый) колонтитулах не должна превышать 250 символов (как и в ячейке).
Вроде бы все ясно, осталось только переписать его под Delphi:
ASheet.PageSetup.CenterFooter :=
  '&"Arial"&8Лист &"Arial,полужирный"&P' +
  '&"Arial,обычный" из &"Arial,полужирный"&N';
Проверяем в Excel'е "Предварительный просмотр" - оба, и не работает! А как же должно работать? Припоминая русификацию еще Excel'я 4-й версии, напишем русские эквиваленты:
ASheet.PageSetup.CenterFooter :=
    '&"Arial"&8Лист &"Arial,полужирный"&С' + // Страница - Page
   '&"Arial,обычный" из &"Arial,полужирный"&К'; // Количество - Number
Сработало! Ну, и теперь добавим распознавание русской версии:

if XL.LanguageSettings.LanguageID[msoLanguageIDUI] = $0419
  then ASheet.PageSetup.CenterFooter := // русские коды форматирования
    '&"Arial"&8Лист &"Arial,полужирный"&С' +
    '&"Arial,обычный" из &"Arial,полужирный"&К'
  else ASheet.PageSetup.CenterFooter := // английские коды форматирования
    '&"Arial"&8Лист &"Arial,bold"&P' +
    '&"Arial,normal" из &"Arial,bold"&N';

Вывод: при вставке кодов форматирования из Delphi в русский Excel должны вставляться только русские коды форматирования. А где их взять? Вот список кодов форматирования, полученные методом пробы:

Format codeРусский код форматирования Описание
&L Выравнивает последующие символы влево.
&C -"- по центру.
&R -"- вправо.
&E Двойное подчеркивание (double-underline) вкл. или выкл.
&X Верхний индекс (superscript) вкл. или выкл.
&Y Нижний индекс (subscript) вкл. или выкл.
&B Жирный (bold) вкл. или выкл.
&I Наклонный (italic) вкл. или выкл.
&U Подчеркнутый (underline) вкл. или выкл.
&S Зачеркнутый (strikethrough) вкл. или выкл.
&D Текущая дата.
&T Текущее время.
&F Имя документа (книги).
&A Имя листа.
&P Номер страницы.
&P+number &С+число Номер страницы + указанное число.
&P-number &С-число Номер страницы - указанное число.
&& && Одиночный ampersand.
& "fontname" &"ИмяШрифта[,начертание]" Печать указанным шрифтом [и начертанием] (не обязательно). Обязательно указывать в двойных кавычках.
&nn &nn Печать шрифтом указанного размера.
&N Общее количество страниц.


И еще один опыт:
ASheet.PageSetup.CenterFooter :=
      '&"Arial"&8Лист &"Arial,bold"&С&"Arial,normal" из &"Arial,bold"&К';

Работает! Т.е. начертания (Style у класса TFont в Delphi) шрифтов можно уверенно писать по-английски. Или заменить на коды форматирования:

ASheet.PageSetup.CenterFooter := '&"Arial"&8Лист &Ж&С&Ж из &Ж&К';

Примечание:
для перевода строки в колонтитуле или ячейке используйте симол LF, ASCI код 10 (#10):

ASheet.PageSetup.CenterFooter := 'Первая строка'#10'Вторая строка';
ASheet.Range['A1', EmptyParam].Value := 'Первая строка'#10'Вторая строка';

Выводы

При работе с русским Excel'ем из Delphi необходимо соблюдать следующие правила:
  • при задании форматов использовать только русские форматы чисел и даты;
  • при цветном форматировании чисел указывать цвета только на русском языке;
  • при записи формул из вариантного массива использовать только русские формулы;
  • при создании колонтитулов использовать только русские коды форматирования;
  • для совместимости с английской версий необходимо проверять LCID интрефейса пользователя Excel'я и действовать соответственно.

Мне не удалось найти документацию, касающуюся моментов описанных выше. Весь материал построен чисто на собственном опыте. И еще: не было возможности проверить на полностью английских версиях Windows и Office.


Все примеры тестировались на Delphi 6, Delphi 7, на русских версиях WindowsXP + Office2003 , WindowsXP + OfficeXP, Windows98SE + Office2000.

Примечание:
Проблему работы со свойством NumberFormat ячейки (объект Range) в русских версиях Excel2000/XP/2003 решает модуль Евгения Федорова. Подключив этот модуль (см. пример в статье "Русский Excel и установка NumberFormat"), можно успешно задавать английские форматы...



К материалу прилагаются файлы:


Смотрите также материалы по темам:
[Локализация, проблемы с кодировками] [Формулы, макросы] [Таблицы, оформление областей]

 Обсуждение материала [ 23-05-2008 04:36 ] 26 сообщений
  
Время на сайте: GMT минус 5 часов

Если вы заметили орфографическую ошибку на этой странице, просто выделите ошибку мышью и нажмите Ctrl+Enter.
Функция может не работать в некоторых версиях броузеров.

Web hosting for this web site provided by DotNetPark (ASP.NET, SharePoint, MS SQL hosting)  
Software for IIS, Hyper-V, MS SQL. Tools for Windows server administrators. Server migration utilities  

 
© При использовании любых материалов «Королевства Delphi» необходимо указывать источник информации. Перепечатка авторских статей возможна только при согласии всех авторов и администрации сайта.
Все используемые на сайте торговые марки являются собственностью их производителей.

Яндекс цитирования