Солнечный город
Работа с книгами Excel из скриптов на JScript (01.04.2017 13:13:23)

В операционной системе Microsoft Windows имеется компонент Windows Script Host (WBH), предназначенный, в основном, для запуска сценариев на скриптовых языках JScript и VBScript.

Возможности сценариев WSH значительно превосходят возможности командных файлов. В частности, имеется возможность взаимодействия с программами, реализующими объектный скриптовый интерфейс (Active Scripting или OLE Automation), доступ к методам и свойствам их объектов.

В данной публикации рассмотривается взаимодействие скрипта на языке JScript с программой Microsoft Excel. Странным образом описание приемов такого взаимодействия слабо представлено в Сети.

Изложение не претендует на полноту, системность и литературность :) Однако, как мне кажется, приведенные фрагменты из конкретных рабочих скриптов могут оказаться полезны читателям.

Синтаксис JScript во многом аналогичен языку JavaScript компании Netscape, что позволяет писать скрипты на нем без особой дополнительной подготовки, Microsost Excel в вообще представлении не нуждается.

Сценарии JScript запускаются с помощью одной из двух программ-активаторов: wscript.exe (с графическим интерфейсом) или cscript.exe (с консольным интерфейсом).

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

WScript.exe Script.js param1 param2

    WScript.exe Script.js param1 param2

Чтобы получить доступ к переданным параметрам внутри скрипта необходимо выполнить операцию:

var argv = WScript.Arguments;

    var argv = WScript.Arguments;

После этого доступ к параметрам осуществляется как

argv(0),argv(1),...

    argv(0),argv(1),...

То есть в описываемом случае argv(0) будет строкой "param1", а argv(1) – строкой "param2".

Вывод сообщений из скрипта осуществляется следующей командой:

WScript.echo(query);

    WScript.echo(query);

Из скрипта есть возможность подключиться к SQL серверу, например Microsoft SQL Server.

Подключение к базе данных, выполнение процедуры и получение результатов запроса могут быть выполнены с помощью следующей последовательности команд:

var adUseClient = 0x03; var conn = new ActiveXObject("ADODB.Connection"); conn.Open("Provider=MSDASQL;Driver={SQL Server};Server=KK\SQLEXPRESS;Database=...;UID=sa;PWD=...;"); conn.CursorLocation = adUseClient; var lres = 0; var query; query = "exec Norm_journal " + argv(0); rs = conn.Execute(query, lres, adCmdUnknown); while (!rs.Eof) { . ... rs.Fields(0).Value ... . rs.moveNext; } rs.close();

    var adUseClient     = 0x03;

    var conn = new ActiveXObject("ADODB.Connection");
    conn.Open("Provider=MSDASQL;Driver={SQL Server};Server=KK\SQLEXPRESS;Database=...;UID=sa;PWD=...;");
    conn.CursorLocation	= adUseClient;
    var lres  = 0;
    var query;
    query = "exec Norm_journal " + argv(0);
    rs = conn.Execute(query, lres, adCmdUnknown);
    while (!rs.Eof) {
        .
        ... rs.Fields(0).Value ...
        .
        rs.moveNext;
    }
    rs.close();

Создание книги Excel по шаблону и получение объекта первого листа книги осуществляется следующей последовательностью команд:

var xla = new ActiveXObject("Excel.Application"); var xlb = xla.Workbooks.Add("D:\...\Order.xltx"); var xls = xlb.Worksheets(1);

    var xla = new ActiveXObject("Excel.Application");
    var xlb = xla.Workbooks.Add("D:\...\Order.xltx");
    var xls = xlb.Worksheets(1);

Для работы с объектом Excel будет необходим следующий минимальный набор констант:

var xlNone = -4142; var xlContext = -5002; var xlAbove = 0; var xlBottom = -4107; var xlCenter = -4108; var xlMedium = -4138; var xlRight = -4152; var xlLeft = -4131; var xlJustify = -4130; var xlThin = 2; var xlContinuous = 1; var xlAutomatic = -4105; var xlEdgeLeft = 7; var xlEdgeTop = 8; var xlEdgeBottom = 9; var xlEdgeRight = 10; var xlInsideVertical = 11; var xlInsideHorizontal = 12; var xlPageBreakManual = -4135; var xlExpression = 2;

    var xlNone             = -4142;
    var xlContext          = -5002;
    var xlAbove            = 0;
    var xlBottom           = -4107;
    var xlCenter           = -4108;
    var xlMedium           = -4138;
    var xlRight            = -4152;
    var xlLeft             = -4131;
    var xlJustify          = -4130;
    var xlThin             = 2;
    var xlContinuous       = 1;
    var xlAutomatic        = -4105;
    var xlEdgeLeft         = 7;
    var xlEdgeTop          = 8;
    var xlEdgeBottom       = 9;
    var xlEdgeRight        = 10;
    var xlInsideVertical   = 11;
    var xlInsideHorizontal = 12;
    var xlPageBreakManual  = -4135;
    var xlExpression       = 2;

Если окажется, что какой-либо константы нет в этом наборе, ее можно добавить. Информацию о значениях констант можно получить по ссылке Constants enumeration

На время выполнения скриптом операций над книгой ее лучше сделать невидимой:

xla.Visible = false;

    xla.Visible = false;

Перед завершением скрипта чтобы сделать книгу видимой этому свойству нужно присвоить значение true.

Если книгу оставить видимой во время выполнения скрипта, пользователь сможет наблюдать изменения, вносимые скриптом, но если он параллельно попытается сам вносить в книгу какие-нибудь изменения, то скрипт попросту аварийно завершится.

Вывод значения в ячейку в особых комментариях не нуждается:

xls.Cells(i, 1).Value = j;

    xls.Cells(i, 1).Value = j;

В ячейку можно прописать также любые формулы:

xls.Cells(i, 6).Formula = "=SUM(F16:F"+(i-1)+")";

    xls.Cells(i, 6).Formula = "=SUM(F16:F"+(i-1)+")";

Единственное замечание – независимо от языка установленного офиса следует писать формулы в английской нотации. Возможно из этого не следует делать далеко идущих выводов, но у меня формулы в русской нотации не всегда корректно срабатывали даже в русскоязычной версии офиса. Глубинные причины исследованию не подвергались :)

Пример:

xls.Cells(2,1).Formula="=DATE("+argv(1)+","+argv(0)+"+1,0)"; xls.Cells(2,1).NumberFormat = "[$-419]mmmm yyyy";

xls.Cells(2,1).Formula="=DATE("+argv(1)+","+argv(0)+"+1,0)";
xls.Cells(2,1).NumberFormat = "[$-419]mmmm yyyy";

Здесь в ячейку записывается дата, сконструированная из года argv(1) и месяца argv(0) и представляющая собой последнее число указанного месяца. Ячейка отформатирована таким образом, что выводит название месяца на русском языке вне зависимости от версии офиса и год цифрами.

Скрипт как правило оперирует с индексами ячеек, в формулах для обозначения столбцов нужны буквенные обозначения. Для перевода индекса столбца в буквенную форму можно воспользоваться следующими функциями:

function numToChar(number) { // преобразование индекса в буквенную форму // number – значение индекса var numeric = (number - 1) % 26; var letter = chr(65 + numeric); var number2 = parseInt((number - 1) / 26); if (number2 > 0) return numToChar(number2) + letter; else return letter; } function chr(codePt) { if (codePt > 0xFFFF) { codePt -= 0x10000; return String.fromCharCode(0xD800 + (codePt >> 10), 0xDC00 + (codePt & 0x3FF)); } return String.fromCharCode(codePt); }

    function numToChar(number) {
        // преобразование индекса в буквенную форму
        // number – значение индекса
        var numeric = (number - 1) % 26;
        var letter = chr(65 + numeric);
        var number2 = parseInt((number - 1) / 26);
        if (number2 > 0)
            return numToChar(number2) + letter;
        else
            return letter;
    }

    function chr(codePt) {
        if (codePt > 0xFFFF) { 
            codePt -= 0x10000;
            return String.fromCharCode(0xD800 + (codePt >> 10), 0xDC00 + (codePt & 0x3FF));
        }
        return String.fromCharCode(codePt);
    }

Для облегчения оформления документа границами можно воспользоваться следующими функциями:

function edge(rect,type) { // простановка одиночной границы // rect – диапазон ячеек // type – расположение границы (например, xlEdgeBottom) rect.Borders(type).LineStyle = xlContinuous; rect.Borders(type).Weight = xlThin; rect.Borders(type).ColorIndex = xlAutomatic; } function border(rect,vgrid,hgrid) { // простановка границ // rect – диапазон ячеек // vgrid – проставлять ли внутренние границы по вертикали // hgrid – проставлять ли внутренние границы по гризонтали rect.Borders(xlEdgeLeft).LineStyle = xlContinuous; rect.Borders(xlEdgeLeft).Weight = xlThin; rect.Borders(xlEdgeLeft).ColorIndex = xlAutomatic; rect.Borders(xlEdgeTop).LineStyle = xlContinuous; rect.Borders(xlEdgeTop).Weight = xlThin; rect.Borders(xlEdgeTop).ColorIndex = xlAutomatic; rect.Borders(xlEdgeBottom).LineStyle = xlContinuous; rect.Borders(xlEdgeBottom).Weight = xlThin; rect.Borders(xlEdgeBottom).ColorIndex = xlAutomatic; rect.Borders(xlEdgeRight).LineStyle = xlContinuous; rect.Borders(xlEdgeRight).Weight = xlThin; rect.Borders(xlEdgeRight).ColorIndex = xlAutomatic; if(hgrid) { rect.Borders(xlInsideHorizontal).LineStyle = xlContinuous; rect.Borders(xlInsideHorizontal).Weight = xlThin; rect.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic; } if(vgrid) { rect.Borders(xlInsideVertical).LineStyle = xlContinuous; rect.Borders(xlInsideVertical).Weight = xlThin; rect.Borders(xlInsideVertical).ColorIndex = xlAutomatic; } }

    function edge(rect,type) {
        // простановка одиночной границы
        // rect – диапазон ячеек
        // type – расположение границы (например, xlEdgeBottom)
        rect.Borders(type).LineStyle = xlContinuous;
        rect.Borders(type).Weight = xlThin;
        rect.Borders(type).ColorIndex = xlAutomatic;
    }

    function border(rect,vgrid,hgrid) {
        // простановка границ
        // rect – диапазон ячеек
        // vgrid – проставлять ли внутренние границы по вертикали
        // hgrid – проставлять ли внутренние границы по гризонтали
        rect.Borders(xlEdgeLeft).LineStyle = xlContinuous;
        rect.Borders(xlEdgeLeft).Weight = xlThin;
        rect.Borders(xlEdgeLeft).ColorIndex = xlAutomatic;

        rect.Borders(xlEdgeTop).LineStyle = xlContinuous;
        rect.Borders(xlEdgeTop).Weight = xlThin;
        rect.Borders(xlEdgeTop).ColorIndex = xlAutomatic;

        rect.Borders(xlEdgeBottom).LineStyle = xlContinuous;
        rect.Borders(xlEdgeBottom).Weight = xlThin;
        rect.Borders(xlEdgeBottom).ColorIndex = xlAutomatic;

        rect.Borders(xlEdgeRight).LineStyle = xlContinuous;
        rect.Borders(xlEdgeRight).Weight = xlThin;
        rect.Borders(xlEdgeRight).ColorIndex = xlAutomatic;

        if(hgrid) {
            rect.Borders(xlInsideHorizontal).LineStyle = xlContinuous;
            rect.Borders(xlInsideHorizontal).Weight = xlThin;
            rect.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic;
        }
        if(vgrid) {
            rect.Borders(xlInsideVertical).LineStyle = xlContinuous;
            rect.Borders(xlInsideVertical).Weight = xlThin;
            rect.Borders(xlInsideVertical).ColorIndex = xlAutomatic;
        }
    }

Пример использования функции border (внутренние границы в диапазоне проставляются и по вертикали и по гризонтали):

border(xls.range(xls.Cells(4,1), xls.Cells(i-1,j-1)), true, true);

    border(xls.range(xls.Cells(4,1), xls.Cells(i-1,j-1)), true, true);

Для ячейки или диапазона ячеек можно установить выравнивание хранящегося значения по вертикали и горизонтали, например:

xls.range(xls.Cells(4, k), xls.Cells(4, j-1)).HorizontalAlignment = xlCenter;

    xls.range(xls.Cells(4, k), xls.Cells(4, j-1)).HorizontalAlignment = xlCenter;

Для ячейки или диапазона ячеек можно установить перенос текста по словам:

xls.Cells(i, 2).WrapText=true;

    xls.Cells(i, 2).WrapText=true;

Числовое содержимое ячейки может быть соответствующим образом отформатировано, например:

xls.Cells(i, 6).NumberFormat = "#,##0.00;"; xls.Cells(i+2,k).NumberFormat = "#,##0.00;#,##0.00"; xls.Cells(i+3,k).NumberFormat = "0.0%";

    xls.Cells(i, 6).NumberFormat = "#,##0.00;";
    xls.Cells(i+2,k).NumberFormat = "#,##0.00;#,##0.00";	
    xls.Cells(i+3,k).NumberFormat = "0.0%";	

Для содержимого ячейки можно установить параметры шрифта, например:

xls.Range(xld.Cells(1,1),xld.Cells(2,1)).Font.Size=16; xls.Cells(i,1).Font.Bold=true;

    xls.Range(xld.Cells(1,1),xld.Cells(2,1)).Font.Size=16;
    xls.Cells(i,1).Font.Bold=true;

Для формирования цвета из компонент можно воспользоваться следующей функцией:

function rgb(r,g,b){ return b*65536+g*256+r; }

    function rgb(r,g,b){ return b*65536+g*256+r; }

Цвет фона ячейки можно установить либо сформировав цвет из компонент:

xls.range(xls.Cells(6,1), xls.Cells(6,j-1)).Interior.Color=rgb(150,150,255);

    xls.range(xls.Cells(6,1), xls.Cells(6,j-1)).Interior.Color=rgb(150,150,255);

либо указав индекс цвета в палитре:

xls.Range(sheet.Cells(i-1,2),sheet.Cells(i+1,7)).Interior.ColorIndex = 17;

    xls.Range(sheet.Cells(i-1,2),sheet.Cells(i+1,7)).Interior.ColorIndex = 17;

Объединить ячейки диапазона можно следующим образом:

xls.range(xls.Cells(3, 1), xls.Cells(3, 2)).MergeCells = true;

    xls.range(xls.Cells(3, 1), xls.Cells(3, 2)).MergeCells = true;

По завершении заполнения листа к нему можно применить метод автоматической подгонки размеров ячеек по содержимому. Для объединенных ячеек метод не работает.

xls.Cells.EntireColumn.AutoFit;

    xls.Cells.EntireColumn.AutoFit;

Строка или столбец в целом могут быть удалены с листа по индексу, например:

xls.Columns('H').Delete;

    xls.Columns('H').Delete;

Для предотвращения редактирования таблицы можно установить защиту на лист:

xls.Protect;

    xls.Protect;

Если некоторые ячейки все-таки должны редактироваться, для них можно установить индивидуальные исключения:

xls.Cells(i, 10).Locked=false;

    xls.Cells(i, 10).Locked=false;

Строка или столбец в целом могут быть скрыты на листе по индексу, например:

xls.Columns("F").Hidden=true;

    xls.Columns("F").Hidden=true;

Книга может быть защищена от модификации паролем с помощью вызова следующего метода:

xls.Protect("Password",true,true);

    xls.Protect("Password",true,true);

На столбцы диапазона ячеек может быть установлен автофильтр:

xls.range(xls.Cells(12, 10), xls.Cells(i - 1, 10)).AutoFilter(1,"");

    xls.range(xls.Cells(12, 10), xls.Cells(i - 1, 10)).AutoFilter(1,"");

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

Строки или столбцы листа могут быть сгруппированы, например, с помощью следующего вызова:

xls.range(xls.Cells(6,1), xls.Cells(6,j-1)).Rows.Group;

    xls.range(xls.Cells(6,1), xls.Cells(6,j-1)).Rows.Group;

Этот вызов позволяет удобно установить группировку на одном уровне.

Если нужна более сложная группировка строк или столбцов, удобнее воспользоваться свойством уровня группировки:

xls.Rows(i).OutlineLevel=rs.Fields(0).Value+1;

    xls.Rows(i).OutlineLevel=rs.Fields(0).Value+1;

Положением итоговой строки группировки можно управлять с помощью установки следующего свойства:

xls.Outline.SummaryRow = xlAbove;

    xls.Outline.SummaryRow = xlAbove;

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

Свойство Zoom при этом непременно должно быть установлено в false. Обратите, также, внимание, что свойству FitToPageTail нужно присвоить именно false, а никак не нуль.

xls.PageSetup.Zoom=false; xls.PageSetup.FitToPagesWide=1; xls.PageSetup.FitToPagesTall=false; xls.ResetAllPageBreaks();

    xls.PageSetup.Zoom=false;
    xls.PageSetup.FitToPagesWide=1;
    xls.PageSetup.FitToPagesTall=false;
    xls.ResetAllPageBreaks();

Для ячеек может быть установлено условное форматирование:

xls.Cells(j, 2).FormatConditions.Delete(); xls.Cells(j, 2).FormatConditions.Add(xlExpression,null,"=AND($B$"+j+"=0,NOT($C$"+j+"=0))"); xls.Cells(j, 2).FormatConditions(1).Interior.Color = 255;

    xls.Cells(j, 2).FormatConditions.Delete();
    xls.Cells(j, 2).FormatConditions.Add(xlExpression,null,"=AND($B$"+j+"=0,NOT($C$"+j+"=0))");
    xls.Cells(j, 2).FormatConditions(1).Interior.Color = 255;

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

Собственно, пока все.

По долгу службы :) мне и сейчас время от времени приходится сталкиваться с формированием таблиц Excel из скриптов на JScript. Если придется столкнуться еще с какими-нибудь интересными нюансами, о такой встрече я сообщу дополнительно :)

Комментарии
Комментарии

герб Азербайджанской ССР
Азербайджанская
ССР
герб Украинской ССР
Украинская
ССР
герб Грузинской ССР
Грузинская
ССР
герб Эстонской ССР
Эстонская
ССР
герб Латвийской ССР
Латвийская
ССР
герб Узбекской ССР
Узбекская
ССР
герб Литовской ССР
Литовская
ССР
герб Белорусской ССР
Белорусская
ССР

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

Учебно-методическая литература, размещенная в разделе Библиотека, предназначена исключительно для предварительного личного ознакомления. Авторские права на произведения, имеющие конкретных правообладателей, сохраняются за последними. Если правообладатель сообщит о своем несогласии с размещением материала, таковой будет немедленно удален.


Flag Counter
руб.
Рейтинг.ru


Сообщение
 
 
Понял
Запрос
 
 
Да Нет
Регистрация
получать личные сообщения
  Ваш образ 
файл не выбран
Завершить регистрацию
Забыли пароль?
Получить пароль
Редактирование заголовка статьи
  Титул статьи 
файл не выбран
видима для всех
Сохранить Отмена
Редактирование абзаца статьи
Иллюстрация 
файл не выбран
видим для всех
Сохранить Отмена
Редактирование заголовка альбома
видим для всех
Сохранить Отмена
Добавление изображения в альбом
  Новое 
файл не выбран
Сохранить Отмена
Редактирование реквизитов изображения
видима для всех
Метки проекта

Метки фотографии
Новая
Сброс Сохранить
Связь с Автором
Отправить Отмена
Сообщение администрации сайта
Отправить Отмена