Особенности работы с "русским" Excel'ем
Особенности работы с "русским" Excel'ем
Александр Шабля, Королевствo Delphi
Написанное приложение, прекрасно работающие с 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Английская версия Excel (English United States) вернет 1033 (или $0409), немецкая (German Standard) - $0407. Значения соответствуют LCID, описанным в MS SDK Help "Language Identifiers". LCID интерфейса пользователя и файла Excel.exe файла может быть неодинаковым (например, после установки MUI). Константа msoLanguageIDUI находится в модуле Office2000.pas и описана так:
{или $0419}
then { Excel имеет русский интерфейс пользователя };
constПримечание:
msoLanguageIDUI = $00000002;
в Office97 свойство LanguageSettings отсутствует
Далее мы рассмотрим приемы работы с "русским" Excel'ем.Работа со свойством объекта Range NumberFormat
NumberFormat и NumberFormatLocal четко работают в VBA и полностью соответствуют своему содержанию в названиях, но только не при работе из Delphi. В Excel2000.pas (D7) они описаны какExcelRange = dispinterfaceНо, при попытке записи форматов из Delphi, выясняется, что NumberFormat и NumberFormatLocal ведут себя идентично, причем NumberFormat соответствует NumberFormatLocal (лучше было бы наоборот :). Т.е. в русской версии все форматы нужно писать "по-русски" (можно прямо в NumberFormat, в VBA - нельзя).
['{00020846-0000-0000-C000-000000000046}']
...
property NumberFormat: OleVariant dispid 193;
property NumberFormatLocal: OleVariant dispid 1097;
Формат даты
Код на VBA (эталон):Sub Test1()Код на Delphi:
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
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()Код на Delphi:
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
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Тот же принцип можно применить к времени и другим типам. Также смотрите другие индексы для свойства International (их там много) в справке VBA. Например, получить "основной" (general) формат можно так:
(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;
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. Но, если мы хотим вставлять формулы прямо из массива, все они должны быть только русскими! Вот здесь то и всплывает необходимость определения наличия русской версии Excel'я (впрочем, это уже касалось задания цвета в свойстве NumberFormat).Код на VBA:
Sub TestVariant()Код на Delphi (тут мы применим знание написания русских формул, описанный выше, а именно ListSeparator):
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)' ' ошибка #ИМЯ?
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)' ' ошибка #ИМЯ?
MyVar(1, 2) = "=округл(A2*B2; 2)" '
работает для стандартных настроек
' итог
' MyVar(2, 2) = '=sum(C1:C2)' ' ошибка #ИМЯ?
' 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
varПримечание:
MyVar: Variant;
IsRusXL: Boolean;
begin
...
MyVar := VarArrayCreate([0, 2, 0, 2], varVariant); //
3 строки, 3 колонки
// определим, русский ли у нас Excel
IsRusXL := XL.LanguageSettings.LanguageID[msoLanguageIDUI]
= $0419;
// первая строка массива
MyVar[0, 0] := 10.72;
MyVar[0, 1] := 3.05;
if IsRusXL
// стиль 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
then MyVar[1, 2] := Format('=округл(A2*B2%s 2)',
[ListSeparator]) // стиль A1
else MyVar[1, 2] := '=round(A2*B2, 2)';
// итог
if IsRusXL
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 := MyVar; // работает
// FormulaLocal := MyVar; // работает
// FormulaR1C1 := MyVar; //
не работает, если есть ссылки в стиле A1
// FormulaR1C1Local := MyVar; //
не работает, если есть ссылки в стиле A1
// Value := MyVar; // работает
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 :=Проверяем в Excel'е "Предварительный просмотр" - оба, и не работает! А как же должно работать? Припоминая русификацию еще Excel'я 4-й версии, напишем русские эквиваленты:
'&"Arial"&8Лист &"Arial,полужирный"&P' +
'&"Arial,обычный" из &"Arial,полужирный"&N';
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'я и действовать соответственно. |