Версия для печати
Особенности работы с «Русским» Excel'ем
http://www.delphikingdom.com/asp/viewitem.asp?catalogID=920Александр Шабля
дата публикации 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;Примечание:Далее мы рассмотрим приемы работы с "русским" Excel'ем.
в Office97 свойство LanguageSettings отсутствует
Работа со свойством объекта 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'; // работает для стандартных настроекПримечание:Решения (с использованием ExcelApplication.International):
в примерах значения записываются в Value2 для предотвращения форматирования "на лету" самим Excel'ем. Так число 123.45, записанное в Value будет автоматически отформатировано Excel'ем в формат валюты, а присвоение Value = Date будет автоматически переведено в формат даты. Запись в Value2 "воспринимает" значение как Double. Подробнее смотрите в справке VBA для Excel'я.
Для получения формата даты можно написать функцию: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.Запись в свойство Formula, FormulaLocal, Value, Value2 из Variant-ного массива идентична в русском Excel'е и при работе из Delphi. Но, если мы хотим вставлять формулы прямо из массива, все они должны быть только русскими!
Запись формул из Variant-ного массива Примечание:
спасибо фирме 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:Код на Delphi
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(тут мы применим знание написания русских формул, описанный выше, а именно 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"), можно успешно задавать английские форматы...
К материалу прилагаются файлы:
- Примеры к статье (4.6 K) обновление от 2/11/2004 1:20:00 PM